You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-10-31 18:30:33 +03:00
9fe37d5919
This patch is the columnstore-part of the task. Columnstore wanted to have previous 32 depth, so this patch aims at keeping the compatibility.
304 lines
10 KiB
Plaintext
304 lines
10 KiB
Plaintext
DROP DATABASE IF EXISTS json_arrayagg_db;
|
|
Warnings:
|
|
Note 1008 Can't drop database 'json_arrayagg_db'; database doesn't exist
|
|
CREATE DATABASE json_arrayagg_db;
|
|
USE json_arrayagg_db;
|
|
CREATE TABLE t1 (a INT, b INT)ENGINE=COLUMNSTORE;
|
|
INSERT INTO t1 VALUES (1, 1);
|
|
INSERT INTO t1 VALUES (2, 1);
|
|
INSERT INTO t1 VALUES (1, 1);
|
|
INSERT INTO t1 VALUES (2, 1);
|
|
INSERT INTO t1 VALUES (3, 2);
|
|
INSERT INTO t1 VALUES (2, 2);
|
|
INSERT INTO t1 VALUES (2, 2);
|
|
INSERT INTO t1 VALUES (2, 2);
|
|
SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1;
|
|
JSON_VALID(JSON_ARRAYAGG(a))
|
|
1
|
|
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1;
|
|
JSON_ARRAYAGG(a) JSON_ARRAYAGG(b)
|
|
[1,2,1,2,3,2,2,2] [1,1,1,1,2,2,2,2]
|
|
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY b;
|
|
JSON_ARRAYAGG(a) JSON_ARRAYAGG(b)
|
|
[1,2,1,2] [1,1,1,1]
|
|
[3,2,2,2] [2,2,2,2]
|
|
DROP TABLE t1;
|
|
#
|
|
# Real aggregation
|
|
#
|
|
CREATE TABLE t1 (a FLOAT, b DOUBLE, c DECIMAL(10, 2))ENGINE=COLUMNSTORE;
|
|
INSERT INTO t1 VALUES (1.0, 2.0, 3.0);
|
|
INSERT INTO t1 VALUES (1.0, 3.0, 9.0);
|
|
INSERT INTO t1 VALUES (1.0, 4.0, 16.0);
|
|
INSERT INTO t1 VALUES (1.0, 5.0, 25.0);
|
|
SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1;
|
|
JSON_VALID(JSON_ARRAYAGG(a))
|
|
1
|
|
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b), JSON_ARRAYAGG(c) FROM t1;
|
|
JSON_ARRAYAGG(a) JSON_ARRAYAGG(b) JSON_ARRAYAGG(c)
|
|
[1,1,1,1] [2,3,4,5] [3.00,9.00,16.00,25.00]
|
|
DROP TABLE t1;
|
|
#
|
|
# Boolean aggregation
|
|
#
|
|
CREATE TABLE t1 (a BOOLEAN, b BOOLEAN)ENGINE=COLUMNSTORE;
|
|
INSERT INTO t1 VALUES (TRUE, TRUE);
|
|
INSERT INTO t1 VALUES (TRUE, FALSE);
|
|
INSERT INTO t1 VALUES (FALSE, TRUE);
|
|
INSERT INTO t1 VALUES (FALSE, FALSE);
|
|
SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1;
|
|
JSON_VALID(JSON_ARRAYAGG(a))
|
|
1
|
|
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1;
|
|
JSON_ARRAYAGG(a) JSON_ARRAYAGG(b)
|
|
[1,1,0,0] [1,0,1,0]
|
|
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY b;
|
|
JSON_ARRAYAGG(a) JSON_ARRAYAGG(b)
|
|
[1,0] [0,0]
|
|
[1,0] [1,1]
|
|
SELECT JSON_ARRAYAGG(TRUE), JSON_ARRAYAGG(FALSE) FROM t1;
|
|
JSON_ARRAYAGG(TRUE) JSON_ARRAYAGG(FALSE)
|
|
[1,1,1,1] [0,0,0,0]
|
|
DROP TABLE t1;
|
|
#
|
|
# Aggregation of strings with quoted
|
|
#
|
|
CREATE TABLE t1 (a VARCHAR(80))ENGINE=COLUMNSTORE;
|
|
INSERT INTO t1 VALUES ('"double_quoted_value"');
|
|
INSERT INTO t1 VALUES ("'single_quoted_value'");
|
|
INSERT INTO t1 VALUES ('"double_quoted_value"');
|
|
INSERT INTO t1 VALUES ("'single_quoted_value'");
|
|
SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1;
|
|
JSON_VALID(JSON_ARRAYAGG(a))
|
|
1
|
|
SELECT JSON_ARRAYAGG(a) FROM t1;
|
|
JSON_ARRAYAGG(a)
|
|
["double_quoted_value","'single_quoted_value'","double_quoted_value","'single_quoted_value'"]
|
|
DROP TABLE t1;
|
|
#
|
|
# Strings and NULLs
|
|
#
|
|
CREATE TABLE t1 (a INT, b VARCHAR(80))ENGINE=COLUMNSTORE;
|
|
INSERT INTO t1 VALUES (1, "Hello");
|
|
INSERT INTO t1 VALUES (1, "World");
|
|
INSERT INTO t1 VALUES (2, "This");
|
|
INSERT INTO t1 VALUES (2, "Will");
|
|
INSERT INTO t1 VALUES (2, "Work");
|
|
INSERT INTO t1 VALUES (2, "!");
|
|
INSERT INTO t1 VALUES (3, NULL);
|
|
INSERT INTO t1 VALUES (1, "Hello");
|
|
INSERT INTO t1 VALUES (1, "World");
|
|
INSERT INTO t1 VALUES (2, "This");
|
|
INSERT INTO t1 VALUES (2, "Will");
|
|
INSERT INTO t1 VALUES (2, "Work");
|
|
INSERT INTO t1 VALUES (2, "!");
|
|
INSERT INTO t1 VALUES (3, NULL);
|
|
SELECT JSON_VALID(JSON_ARRAYAGG(b)) FROM t1;
|
|
JSON_VALID(JSON_ARRAYAGG(b))
|
|
1
|
|
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1;
|
|
JSON_ARRAYAGG(a) JSON_ARRAYAGG(b)
|
|
[1,1,2,2,2,2,3,1,1,2,2,2,2,3] ["Hello","World","This","Will","Work","!",null,"Hello","World","This","Will","Work","!",null]
|
|
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
|
|
JSON_ARRAYAGG(a) JSON_ARRAYAGG(b)
|
|
[1,1,1,1] ["Hello","World","Hello","World"]
|
|
[2,2,2,2,2,2,2,2] ["This","Will","Work","!","This","Will","Work","!"]
|
|
[3,3] [null,null]
|
|
#
|
|
# DISTINCT and LIMIT
|
|
#
|
|
SELECT JSON_ARRAYAGG(b LIMIT 1) FROM t1;
|
|
JSON_ARRAYAGG(b LIMIT 1)
|
|
["Hello","World","This","Will","Work","!",null,"Hello","World","This","Will","Work","!",null]
|
|
SELECT JSON_ARRAYAGG(b LIMIT 2) FROM t1;
|
|
JSON_ARRAYAGG(b LIMIT 2)
|
|
["Hello","World","This","Will","Work","!",null,"Hello","World","This","Will","Work","!",null]
|
|
SELECT JSON_ARRAYAGG(b LIMIT 1) FROM t1 GROUP BY b;
|
|
JSON_ARRAYAGG(b LIMIT 1)
|
|
["!","!"]
|
|
["Hello","Hello"]
|
|
["This","This"]
|
|
["Will","Will"]
|
|
["Work","Work"]
|
|
["World","World"]
|
|
[null,null]
|
|
SELECT JSON_ARRAYAGG(b LIMIT 2) FROM t1 GROUP BY a;
|
|
JSON_ARRAYAGG(b LIMIT 2)
|
|
["Hello","World","Hello","World"]
|
|
["This","Will","Work","!","This","Will","Work","!"]
|
|
[null,null]
|
|
#
|
|
# JSON aggregation
|
|
#
|
|
SELECT JSON_VALID(JSON_ARRAYAGG(JSON_ARRAY(a, b))) FROM t1;
|
|
JSON_VALID(JSON_ARRAYAGG(JSON_ARRAY(a, b)))
|
|
1
|
|
SELECT JSON_ARRAYAGG(JSON_ARRAY(a, b)) FROM t1;
|
|
JSON_ARRAYAGG(JSON_ARRAY(a, b))
|
|
[[1, "Hello"],[1, "World"],[2, "This"],[2, "Will"],[2, "Work"],[2, "!"],[3, null],[1, "Hello"],[1, "World"],[2, "This"],[2, "Will"],[2, "Work"],[2, "!"],[3, null]]
|
|
SELECT JSON_ARRAYAGG(JSON_ARRAY(a, b)) FROM t1 GROUP BY a;
|
|
JSON_ARRAYAGG(JSON_ARRAY(a, b))
|
|
[[1, "Hello"],[1, "World"],[1, "Hello"],[1, "World"]]
|
|
[[2, "This"],[2, "Will"],[2, "Work"],[2, "!"],[2, "This"],[2, "Will"],[2, "Work"],[2, "!"]]
|
|
[[3, null],[3, null]]
|
|
SELECT JSON_VALID(JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b))) FROM t1;
|
|
JSON_VALID(JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b)))
|
|
1
|
|
SELECT JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b)) FROM t1;
|
|
JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b))
|
|
[{"a": 1, "b": "Hello"},{"a": 1, "b": "World"},{"a": 2, "b": "This"},{"a": 2, "b": "Will"},{"a": 2, "b": "Work"},{"a": 2, "b": "!"},{"a": 3, "b": null},{"a": 1, "b": "Hello"},{"a": 1, "b": "World"},{"a": 2, "b": "This"},{"a": 2, "b": "Will"},{"a": 2, "b": "Work"},{"a": 2, "b": "!"},{"a": 3, "b": null}]
|
|
SELECT JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b)) FROM t1 GROUP BY a;
|
|
JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b))
|
|
[{"a": 1, "b": "Hello"},{"a": 1, "b": "World"},{"a": 1, "b": "Hello"},{"a": 1, "b": "World"}]
|
|
[{"a": 2, "b": "This"},{"a": 2, "b": "Will"},{"a": 2, "b": "Work"},{"a": 2, "b": "!"},{"a": 2, "b": "This"},{"a": 2, "b": "Will"},{"a": 2, "b": "Work"},{"a": 2, "b": "!"}]
|
|
[{"a": 3, "b": null},{"a": 3, "b": null}]
|
|
#
|
|
# Error checks
|
|
#
|
|
SELECT JSON_ARRAYAGG(a, b) FROM t1;
|
|
ERROR 42000: Incorrect parameter count in the call to native function 'JSON_ARRAYAGG'
|
|
SELECT JSON_ARRAYAGG(JSON_ARRAYAGG(a, b)) FROM t1;
|
|
ERROR 42000: Incorrect parameter count in the call to native function 'JSON_ARRAYAGG'
|
|
SELECT JSON_ARRAYAGG(JSON_ARRAYAGG(a)) FROM t1;
|
|
ERROR HY000: Invalid use of group function
|
|
#
|
|
#
|
|
DROP TABLE t1;
|
|
#
|
|
#
|
|
CREATE TABLE t1 (a INT)ENGINE=COLUMNSTORE;
|
|
SELECT JSON_ARRAYAGG(a) FROM t1;
|
|
JSON_ARRAYAGG(a)
|
|
NULL
|
|
DROP TABLE t1;
|
|
#
|
|
#
|
|
select json_arrayagg(a) over () from (select 1 a) t;
|
|
ERROR 42000: This version of MariaDB doesn't yet support 'JSON_ARRAYAGG() aggregate as window function'
|
|
select json_objectagg(a, b) over () from (select 1 a, 2 b) t;
|
|
ERROR 42000: This version of MariaDB doesn't yet support 'JSON_OBJECTAGG() aggregate as window function'
|
|
SELECT JSON_ARRAYAGG(NULL) FROM (SELECT 1 AS t) AS A;
|
|
JSON_ARRAYAGG(NULL)
|
|
[null]
|
|
SELECT JSON_ARRAYAGG("null") FROM (SELECT 1 AS t) AS A;
|
|
JSON_ARRAYAGG("null")
|
|
["null"]
|
|
create view v as (select json_arrayagg(json_object("type", "permPeriod", "id", "asd")) as JSON_DATA);
|
|
select * from v;
|
|
JSON_DATA
|
|
[{"type": "permPeriod", "id": "asd"}]
|
|
drop view v;
|
|
select json_arrayagg(a order by a asc) from (select 1 a union select 2 a) t;
|
|
json_arrayagg(a order by a asc)
|
|
[1,2]
|
|
select json_object('x', json_arrayagg(json_object('a', 1)));
|
|
json_object('x', json_arrayagg(json_object('a', 1)))
|
|
{"x": [{"a": 1}]}
|
|
#
|
|
#
|
|
CREATE TABLE t1(a INT, b INT)ENGINE=COLUMNSTORE;
|
|
INSERT INTO t1 VALUES (1,1);
|
|
INSERT INTO t1 VALUES (2,2);
|
|
INSERT INTO t1 VALUES (3,3);
|
|
INSERT INTO t1 VALUES (1,1);
|
|
INSERT INTO t1 VALUES (2,2);
|
|
INSERT INTO t1 VALUES (3,3);
|
|
SELECT JSON_ARRAYAGG(a) FROM t1;
|
|
JSON_ARRAYAGG(a)
|
|
[1,2,3,1,2,3]
|
|
INSERT INTO t1 VALUES (NULL,NULL);
|
|
INSERT INTO t1 VALUES (NULL,NULL);
|
|
SELECT JSON_ARRAYAGG(a) FROM t1;
|
|
JSON_ARRAYAGG(a)
|
|
[1,2,3,1,2,3,null,null]
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(a VARCHAR(10), b INT)ENGINE=COLUMNSTORE;
|
|
INSERT INTO t1 VALUES (1,1);
|
|
INSERT INTO t1 VALUES (2,2);
|
|
INSERT INTO t1 VALUES (3,3);
|
|
INSERT INTO t1 VALUES (1,1);
|
|
INSERT INTO t1 VALUES (2,2);
|
|
INSERT INTO t1 VALUES (3,3);
|
|
SELECT JSON_ARRAYAGG(a) FROM t1;
|
|
JSON_ARRAYAGG(a)
|
|
[1,2,3,1,2,3]
|
|
INSERT INTO t1 VALUES (NULL,NULL);
|
|
INSERT INTO t1 VALUES (NULL,NULL);
|
|
SELECT JSON_ARRAYAGG(a) FROM t1;
|
|
JSON_ARRAYAGG(a)
|
|
[1,2,3,1,2,3,null,null]
|
|
DROP TABLE t1;
|
|
#
|
|
#
|
|
CREATE TABLE t1(a VARCHAR(255))ENGINE=COLUMNSTORE;
|
|
INSERT INTO t1 VALUES ('red');
|
|
INSERT INTO t1 VALUES ('blue');
|
|
SELECT JSON_ARRAYAGG(a) FROM t1;
|
|
JSON_ARRAYAGG(a)
|
|
["red","blue"]
|
|
SELECT JSON_ARRAYAGG(a ORDER BY a DESC) FROM t1;
|
|
JSON_ARRAYAGG(a ORDER BY a DESC)
|
|
["red","blue"]
|
|
SELECT JSON_ARRAYAGG(a ORDER BY a ASC) FROM t1;
|
|
JSON_ARRAYAGG(a ORDER BY a ASC)
|
|
["blue","red"]
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT JSON_ARRAYAGG(a) FROM t1;
|
|
JSON_ARRAYAGG(a)
|
|
["red","blue",null]
|
|
SELECT JSON_ARRAYAGG(a ORDER BY a DESC) FROM t1;
|
|
JSON_ARRAYAGG(a ORDER BY a DESC)
|
|
["red","blue",null]
|
|
SELECT JSON_ARRAYAGG(a ORDER BY a ASC) FROM t1;
|
|
JSON_ARRAYAGG(a ORDER BY a ASC)
|
|
[null,"blue","red"]
|
|
DROP TABLE t1;
|
|
set group_concat_max_len=64;
|
|
create table t1 (a varchar(254))ENGINE=COLUMNSTORE;
|
|
insert into t1 values (concat('x64-', repeat('a', 60)));
|
|
insert into t1 values (concat('x64-', repeat('b', 60)));
|
|
insert into t1 values (concat('x64-', repeat('c', 60)));
|
|
select json_arrayagg(a) from t1;
|
|
json_arrayagg(a)
|
|
["x64-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
|
|
drop table t1;
|
|
SET group_concat_max_len= default;
|
|
CREATE TABLE t1(id int, name varchar(50))ENGINE=COLUMNSTORE;
|
|
CREATE TABLE t2(id int, owner_id int)ENGINE=COLUMNSTORE;
|
|
INSERT INTO t1 VALUES (1, "name1");
|
|
INSERT INTO t1 VALUES (2, "name2");
|
|
INSERT INTO t1 VALUES (3, "name3");
|
|
INSERT INTO t2 VALUES (1, 1);
|
|
INSERT INTO t2 VALUES (2, 1);
|
|
INSERT INTO t2 VALUES (3, 2);
|
|
INSERT INTO t2 VALUES (4, 3);
|
|
SELECT t1.id, JSON_ARRAYAGG(JSON_OBJECT('id',t2.id) ORDER BY t2.id) as materials
|
|
from t1 LEFT JOIN t2 on t1.id = t2.owner_id
|
|
GROUP BY t1.id ORDER BY id;
|
|
id materials
|
|
1 [{"id": 1},{"id": 2}]
|
|
2 [{"id": 3}]
|
|
3 [{"id": 4}]
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
#
|
|
#
|
|
SELECT json_object('a', if(1, json_object('b', 'c'), json_object('e', 'f')));
|
|
json_object('a', if(1, json_object('b', 'c'), json_object('e', 'f')))
|
|
{"a": {"b": "c"}}
|
|
SELECT json_object('a', coalesce(json_object('b', 'c')));
|
|
json_object('a', coalesce(json_object('b', 'c')))
|
|
{"a": {"b": "c"}}
|
|
#
|
|
#
|
|
CREATE TABLE t (a VARCHAR(8))ENGINE=COLUMNSTORE;
|
|
CREATE VIEW v AS SELECT * FROM t;
|
|
INSERT INTO t VALUES ('foo');
|
|
INSERT INTO t VALUES ('bar');
|
|
SELECT JSON_ARRAYAGG(a) AS f FROM v;
|
|
f
|
|
["foo","bar"]
|
|
DROP VIEW v;
|
|
DROP TABLE t;
|
|
DROP DATABASE IF EXISTS json_arrayagg_db;
|