1
0
mirror of https://github.com/mariadb-corporation/mariadb-columnstore-engine.git synced 2025-05-07 04:01:44 +03:00
Sergey Zefirov 1122b64cb1
MCOL-4234: improve GROUP BY and ORDER BY interaction ()
This patch fixes the problem in MCOL-4234 and also generally improves
behavior of GROUP BY.

It does so by introducing a "dummy" aggregate and by wrapping columns
into it. This allows for columns that are not in GROUP BY to be used
more freely, for example, in SELECT * FROM tbl GROUP BY col - all
columns that are not "col" will be wrapped into an aggregate and query
will proceed to execution.

The dummy aggregate itself does nothing more than remember last value
passed into it.

There also an additional error message that tries to explain what types
of expressions can be wrapped into an aggregate.
2024-06-17 20:00:54 +03:00

32 lines
1.4 KiB
Plaintext

#
# Test HAVING clause
# Author: Bharath, bharath.bokka@mariadb.com
#
-- source ../include/have_columnstore.inc
--disable_warnings
DROP DATABASE IF EXISTS mcs76_db;
--enable_warnings
CREATE DATABASE mcs76_db;
USE mcs76_db;
CREATE TABLE t1(col1 INT, col2 TEXT)ENGINE=Columnstore;
INSERT INTO t1 VALUES(1, repeat('s',20)),(2, repeat('o',20)),(3, 'sss'),(4, 'ooo');
INSERT INTO t1 SELECT * FROM t1;
SELECT col2 FROM t1 GROUP BY col2 HAVING col2 LIKE '%o%' ORDER BY col2;
SELECT col1 FROM t1 GROUP BY col1 HAVING col1 > 1 ORDER BY col1;
SELECT col1, col2 FROM t1 GROUP BY col1, col2 HAVING col1 > 1 AND col2 LIKE '%o%' ORDER BY col1;
SELECT col1, col2 FROM t1 GROUP BY col1, col2 HAVING col1 > 1 OR col2 LIKE '%o%' ORDER BY col1;
--sorted_result
SELECT col1, col2, SUM(LENGTH(col2)) FROM t1 GROUP BY col1 HAVING col1 > 1 AND col2 LIKE '%o%' ORDER BY col1;
CREATE TABLE t2(col1 INT, col2 DATETIME)ENGINE=Columnstore;
INSERT INTO t2 VALUES(1, '2020-2-2'),(2, '2020-3-3'),(5,'2020-6-6'),(6, '2020-7-7');
SELECT t1.col1, SUM(t1.col1*t2.col1) AS a FROM t1 JOIN t2 ON t1.col1 = t2.col1 GROUP BY t1.col1 HAVING a>1 ORDER BY t1.col1;
SELECT t1.col1, SUM(t1.col1*t2.col1) AS a FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 GROUP BY t1.col1 HAVING a>1 ORDER BY t1.col1;
SELECT t1.col1, SUM(t1.col1*t2.col1) AS a FROM t1 RIGHT JOIN t2 ON t1.col1 = t2.col1 GROUP BY t1.col1 HAVING a>1 ORDER BY t1.col1;
# Clean UP
DROP DATABASE mcs76_db;