mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-06-07 19:22:02 +03:00
60dc7550f1
This patch introduces an internal aggregate operator SELECT_SOME that is automatically added to columns that are not in GROUP BY. It "computes" some plausible value of the column (actually, last one passed). Along the way it fixes incorrect handling of HAVING being transferred into WHERE, window function handling and a bit of other inconsistencies.
33 lines
1.4 KiB
Plaintext
33 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;
|
|
|
|
SELECT 'some output';
|
|
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;
|