You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-11-03 17:13:17 +03:00
cfa9a7ff2c
* feat(optimizer): MCOL-5250 rewrite queries with DISTINCT
... as aggregated queries.
So query
```
SELECT DISTINCT <cols list>
FROM <from list>
WHERE <where clause>
HAVING <having clause>
ORDER BY <orderby list>
LIMIT <limit>
```
will become
```
SELECT *
FROM
(
SELECT <cols list>
FROM <from list>
WHERE <where clause>
HAVING <having clause>
) a
GROUP BY 1,2,3,...,N
ORDER BY <orderby list>
LIMIT limit
```
* move ORDER BY to the outer query
* fix test
* reuse cloneWORecursiveSelects() in clone()
* fix subselect columns processing
38 lines
978 B
Plaintext
38 lines
978 B
Plaintext
#
|
|
# SELECT..DISTINCT in columnstore
|
|
# Author: Bharath, bharath.bokka@mariadb.com
|
|
#
|
|
-- source ../include/have_columnstore.inc
|
|
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mcs37_db;
|
|
--enable_warnings
|
|
|
|
CREATE DATABASE mcs37_db;
|
|
USE mcs37_db;
|
|
|
|
CREATE TABLE t1(col1 INT, col2 CHAR(5))ENGINE=Columnstore;
|
|
INSERT INTO t1 VALUES(NULL, NULL),(1,'a'),(1,'b'),(1,'c'),(2,'dd'),(3,'eee');
|
|
SELECT COUNT(DISTINCT col1) FROM t1;
|
|
SELECT COUNT(DISTINCT col2) FROM t1;
|
|
--sorted_result
|
|
SELECT DISTINCT col1 FROM t1;
|
|
SELECT DISTINCT col1 FROM t1 ORDER BY col1 DESC;
|
|
--sorted_result
|
|
SELECT DISTINCT col2 FROM t1;
|
|
|
|
CREATE TABLE t2(col1 INT)ENGINE=Columnstore;
|
|
INSERT INTO t2 SELECT DISTINCT col1 FROM t1;
|
|
--sorted_result
|
|
SELECT * FROM t2;
|
|
|
|
CREATE TABLE t3 (name varchar(255));
|
|
INSERT INTO t3 VALUES ('aa'),('ab'),('ac'),('ad'),('ae');
|
|
--sorted_result
|
|
SELECT DISTINCT * FROM t3;
|
|
SELECT DISTINCT name FROM t3 ORDER BY name LIMIT 2;
|
|
SELECT DISTINCT 1 FROM t3 LIMIT 3;
|
|
|
|
# Clean UP
|
|
DROP DATABASE mcs37_db;
|