You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-07-30 19:23:07 +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.
100 lines
5.9 KiB
Plaintext
100 lines
5.9 KiB
Plaintext
--disable_warnings
|
|
DROP DATABASE IF EXISTS MCOL5776;
|
|
--enable_warnings
|
|
CREATE DATABASE MCOL5776;
|
|
USE MCOL5776;
|
|
|
|
CREATE TABLE t(x INTEGER, y INTEGER) ENGINE=Columnstore;
|
|
INSERT INTO t(x,y) VALUES (1,2), (2,3), (3,3);
|
|
SELECT COUNT(y) OVER (PARTITION BY y) FROM t GROUP BY x;
|
|
SELECT COUNT(y) OVER (PARTITION BY LEFT(y, 10)) FROM t GROUP BY x;
|
|
DROP TABLE t;
|
|
|
|
CREATE TABLE t(ci1 integer, ci2 integer) engine=Columnstore;
|
|
INSERT INTO t(ci1, ci2) VALUES (NULL, 1), (NULL, 2), (1,3), (1,4), (2,5), (2,6), (3,7), (3,8);
|
|
--sorted_result
|
|
SELECT ci1+ci2, ci1+ci2, SUM(ci2), AVG(ci2) FROM t GROUP BY ci1+ci2, ci1+ci2;
|
|
--sorted_result
|
|
SELECT CONCAT(ci1,ci2), CONCAT(ci1,ci2), SUM(ci2), AVG(ci2) FROM t GROUP BY ci1;
|
|
--sorted_result
|
|
SELECT sum(ci1), abs(ci1) FROM t GROUP BY abs(ci1), abs(ci1);
|
|
--sorted_result
|
|
SELECT sum(ci1), abs(ci1) FROM t GROUP BY abs(ci1);
|
|
DROP TABLE t;
|
|
|
|
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 * 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 col2 HAVING col1 > 1 AND col2 LIKE '%o%' ORDER BY col1;
|
|
SELECT col1, col2 FROM t1 GROUP BY col2 HAVING col2 LIKE '%o%' AND col1 > 1 ORDER BY col1;
|
|
SELECT col2, col1 FROM t1 GROUP BY col2 HAVING col1 > 1 AND col2 LIKE '%o%' ORDER BY col1;
|
|
SELECT col2, col1 FROM t1 GROUP BY col2 HAVING col2 LIKE '%o%' AND col1 > 1 ORDER BY col1;
|
|
SELECT col1, col2 FROM t1 GROUP BY col2 HAVING col2 LIKE '%o%' AND col1 > 1 ORDER BY col1;
|
|
SELECT col1, col2 FROM t1 GROUP BY col1 HAVING col1 > 1 AND col2 LIKE '%o%' ORDER BY col1;
|
|
SELECT col1, col2 FROM t1 GROUP BY col1 HAVING col2 LIKE '%o%' AND col1 > 1 ORDER BY col1;
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col1 > 1 AND col2 LIKE '%o%' ORDER BY col1;
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col2 LIKE '%o%' AND col1 > 1 ORDER BY col1;
|
|
SELECT col1, col2 FROM t1 GROUP BY col1 HAVING col2 LIKE '%o%' AND col1 > 1 ORDER BY col1;
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col1 > 1 AND col2 = 'ooo' ORDER BY col1;
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col2 = 'ooo' AND col1 > 1 ORDER BY col1;
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col1 > 1 AND col2 >= 'ooo' ORDER BY col1;
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col2 >= 'ooo' AND col1 > 1 ORDER BY col1;
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col1 > 1 AND col2 < 'ooo' ORDER BY col1;
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col2 < 'ooo' AND col1 > 1 ORDER BY col1;
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col1 > 1 AND 'ooo' < col2 ORDER BY col1;
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING 'ooo' < col2 AND col1 > 1 ORDER BY col1;
|
|
SELECT col1, col2, SUM(LENGTH(col2)) FROM t1 GROUP BY col1 HAVING SUM(LENGTH(col2)) > 10 ORDER BY col1;
|
|
SELECT col1, col2, SUM(LENGTH(col2)) a FROM t1 GROUP BY col1 HAVING a > 1 AND col2 LIKE '%o%' ORDER BY col1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE empsalary (
|
|
depname VARCHAR(100),
|
|
empno BIGINT,
|
|
salary INT,
|
|
enroll_date DATE
|
|
) ENGINE=Columnstore;
|
|
|
|
INSERT INTO empsalary VALUES ('develop' , 10, 5200, '2007-08-01');
|
|
INSERT INTO empsalary VALUES ('sales' , 1, 5000, '2006-10-01');
|
|
INSERT INTO empsalary VALUES ('personnel', 5, 3500, '2007-12-10');
|
|
INSERT INTO empsalary VALUES ('sales' , 4, 4800, '2007-08-08');
|
|
INSERT INTO empsalary VALUES ('personnel', 2, 3900, '2006-12-23');
|
|
INSERT INTO empsalary VALUES ('develop' , 7, 4200, '2008-01-01');
|
|
INSERT INTO empsalary VALUES ('develop' , 9, 4500, '2008-01-01');
|
|
INSERT INTO empsalary VALUES ('sales' , 3, 4800, '2007-08-01');
|
|
INSERT INTO empsalary VALUES ('develop' , 8, 6000, '2006-10-01');
|
|
INSERT INTO empsalary VALUES ('develop' , 11, 5200, '2007-08-15');
|
|
INSERT INTO empsalary VALUES ('develop' , 12, null, '2008-08-09');
|
|
|
|
SELECT depname, empno, MODA(salary) OVER(PARTITION BY depname ORDER BY enroll_date) FROM empsalary ORDER BY depname, empno, enroll_date;
|
|
SELECT AVG(salary),depname, MODA(salary) OVER(PARTITION BY depname ORDER BY enroll_date) FROM empsalary GROUP BY depname ORDER BY depname, AVG(salary);
|
|
DROP TABLE empsalary;
|
|
|
|
CREATE TABLE orders(o_custkey INT) ENGINE=Columnstore;
|
|
|
|
# These checks for absence of exceptions and SIGSEGV's
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) < 20 ORDER BY 1;
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) <= 20 ORDER BY 1;
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) > 20 ORDER BY 1;
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) >= 20 ORDER BY 1;
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) = 20 ORDER BY 1;
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) <> 20 ORDER BY 1;
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) IN (15, 20) ORDER BY 1;
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) NOT IN (15, 20) ORDER BY 1;
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) BETWEEN 15 AND 20 ORDER BY 1;
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) NOT BETWEEN 15 and 20 ORDER BY 1;
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) IS NULL ORDER BY 1;
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) IS NOT NULL ORDER BY 1;
|
|
|
|
DROP TABLE orders;
|
|
|
|
CREATE TABLE t(k INT) ENGINE = Columnstore;
|
|
INSERT INTO t(k) VALUES (1), (2), (2), (3), (3), (4), (4),(4),(4),(4),(995), (NULL);
|
|
--sorted_result
|
|
SELECT k + k a FROM t GROUP BY a HAVING a >= 8;
|
|
|
|
DROP DATABASE MCOL5776;
|