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
101 lines
2.6 KiB
Plaintext
101 lines
2.6 KiB
Plaintext
DROP DATABASE IF EXISTS mcol_5394 ;
|
|
CREATE DATABASE mcol_5394 ;
|
|
USE mcol_5394 ;
|
|
CREATE TABLE t1 (a int, k int, b VARCHAR(10)) engine=columnstore;
|
|
INSERT INTO t1 VALUES
|
|
(1, 1, "alfa"),
|
|
(1, 2, null),
|
|
(2, 3, "doi"),
|
|
(1, 4, "unu"),
|
|
(3, 5, "trei"),
|
|
(4, 6, null),
|
|
(4, 7, null),
|
|
(1, 8, "one");
|
|
SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
|
|
a JSON_ARRAYAGG(b)
|
|
1 ["alfa",null,"unu","one"]
|
|
2 ["doi"]
|
|
3 ["trei"]
|
|
4 [null,null]
|
|
SELECT JSON_ARRAYAGG(b) FROM t1;
|
|
JSON_ARRAYAGG(b)
|
|
["alfa",null,"doi","unu","trei",null,null,"one"]
|
|
PREPARE p1 FROM "SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a";
|
|
EXECUTE p1;
|
|
a JSON_ARRAYAGG(b)
|
|
1 ["alfa",null,"unu","one"]
|
|
2 ["doi"]
|
|
3 ["trei"]
|
|
4 [null,null]
|
|
EXECUTE p1;
|
|
a JSON_ARRAYAGG(b)
|
|
1 ["alfa",null,"unu","one"]
|
|
2 ["doi"]
|
|
3 ["trei"]
|
|
4 [null,null]
|
|
deallocate prepare p1;
|
|
PREPARE p3 FROM
|
|
"SELECT SQL_BUFFER_RESULT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a";
|
|
EXECUTE p3;
|
|
a JSON_ARRAYAGG(b)
|
|
1 ["alfa",null,"unu","one"]
|
|
2 ["doi"]
|
|
3 ["trei"]
|
|
4 [null,null]
|
|
EXECUTE p3;
|
|
a JSON_ARRAYAGG(b)
|
|
1 ["alfa",null,"unu","one"]
|
|
2 ["doi"]
|
|
3 ["trei"]
|
|
4 [null,null]
|
|
deallocate prepare p3;
|
|
PREPARE p4 FROM "SELECT JSON_ARRAYAGG(b) FROM t1";
|
|
EXECUTE p4;
|
|
JSON_ARRAYAGG(b)
|
|
["alfa",null,"doi","unu","trei",null,null,"one"]
|
|
EXECUTE p4;
|
|
JSON_ARRAYAGG(b)
|
|
["alfa",null,"doi","unu","trei",null,null,"one"]
|
|
deallocate prepare p4;
|
|
SELECT JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]') FROM t1;
|
|
JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]')
|
|
["alfa", null, "doi", "unu", "trei", null, null, "one", true, false]
|
|
PREPARE p1 FROM
|
|
"SELECT a, JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]') FROM t1 GROUP BY a";
|
|
EXECUTE p1;
|
|
a JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]')
|
|
1 ["alfa", null, "unu", "one", true, false]
|
|
2 ["doi", true, false]
|
|
3 ["trei", true, false]
|
|
4 [null, null, true, false]
|
|
EXECUTE p1;
|
|
a JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]')
|
|
1 ["alfa", null, "unu", "one", true, false]
|
|
2 ["doi", true, false]
|
|
3 ["trei", true, false]
|
|
4 [null, null, true, false]
|
|
deallocate prepare p1;
|
|
PREPARE p4 FROM
|
|
"SELECT JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]') FROM t1";
|
|
EXECUTE p4;
|
|
JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]')
|
|
["alfa", null, "doi", "unu", "trei", null, null, "one", true, false]
|
|
EXECUTE p4;
|
|
JSON_MERGE_PRESERVE(JSON_ARRAYAGG(b), '[true, false]')
|
|
["alfa", null, "doi", "unu", "trei", null, null, "one", true, false]
|
|
deallocate prepare p4;
|
|
SELECT a, JSON_ARRAYAGG(b) as jarray
|
|
FROM t1
|
|
GROUP BY a
|
|
HAVING jarray= JSON_ARRAY("trei");
|
|
a jarray
|
|
3 ["trei"]
|
|
TRUNCATE TABLE t1;
|
|
SELECT a, JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
|
|
a JSON_ARRAYAGG(b)
|
|
SELECT JSON_ARRAYAGG(b) FROM t1;
|
|
JSON_ARRAYAGG(b)
|
|
NULL
|
|
DROP TABLE t1;
|
|
DROP DATABASE mcol_5394;
|