mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-28 06:45:06 +03:00
32 lines
1.4 KiB
Plaintext
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;
|
|
--error ER_INTERNAL_ERROR
|
|
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;
|