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;