mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-20 09:07:44 +03:00
b53c231ca6
This patch improves handling of NULLs in textual fields in ColumnStore. Previously empty strings were considered NULLs and it could be a problem if data scheme allows for empty strings. It was also one of major reasons of behavior difference between ColumnStore and other engines in MariaDB family. Also, this patch fixes some other bugs and incorrect behavior, for example, incorrect comparison for "column <= ''" which evaluates to constant True for all purposes before this patch.
286 lines
9.8 KiB
Plaintext
286 lines
9.8 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),(2, 1), (1, 1),(2, 1), (3, 2),(2, 2),(2, 2),(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),(1.0, 3.0, 9.0),(1.0, 4.0, 16.0),(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), (TRUE, FALSE), (FALSE, TRUE), (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"'), ("'single_quoted_value'"),
|
|
('"double_quoted_value"'), ("'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"),(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_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","!","Hello","World","This","Will","Work","!"]
|
|
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]
|
|
#
|
|
# DISTINCT and LIMIT
|
|
#
|
|
SELECT JSON_ARRAYAGG(b LIMIT 1) FROM t1;
|
|
JSON_ARRAYAGG(b LIMIT 1)
|
|
["Hello","World","This","Will","Work","!","Hello","World","This","Will","Work","!"]
|
|
SELECT JSON_ARRAYAGG(b LIMIT 2) FROM t1;
|
|
JSON_ARRAYAGG(b LIMIT 2)
|
|
["Hello","World","This","Will","Work","!","Hello","World","This","Will","Work","!"]
|
|
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"]
|
|
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","!"]
|
|
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;
|
|
JSON_ARRAYAGG(DISTINCT a)
|
|
[3,2,1]
|
|
SELECT JSON_ARRAYAGG(DISTINCT b) FROM t1;
|
|
JSON_ARRAYAGG(DISTINCT b)
|
|
["Will","World","Work","!","This","Hello"]
|
|
SELECT JSON_ARRAYAGG(DISTINCT a LIMIT 2) FROM t1;
|
|
JSON_ARRAYAGG(DISTINCT a LIMIT 2)
|
|
[3,2,1]
|
|
SELECT JSON_ARRAYAGG(DISTINCT b LIMIT 2) FROM t1;
|
|
JSON_ARRAYAGG(DISTINCT b LIMIT 2)
|
|
["Will","World","Work","!","This","Hello"]
|
|
#
|
|
# 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)
|
|
|
|
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), (2,2), (3,3);
|
|
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
|
|
SELECT JSON_ARRAYAGG(a) FROM t1;
|
|
JSON_ARRAYAGG(a)
|
|
[1,2,3,1,2,3]
|
|
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;
|
|
JSON_ARRAYAGG(DISTINCT a)
|
|
[3,2,1]
|
|
INSERT INTO t1 VALUES (NULL,NULL), (NULL,NULL);
|
|
SELECT JSON_ARRAYAGG(a) FROM t1;
|
|
JSON_ARRAYAGG(a)
|
|
[1,2,3,1,2,3]
|
|
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;
|
|
JSON_ARRAYAGG(DISTINCT a)
|
|
[3,2,1]
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(a VARCHAR(10), b INT)ENGINE=COLUMNSTORE;
|
|
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
|
|
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
|
|
SELECT JSON_ARRAYAGG(a) FROM t1;
|
|
JSON_ARRAYAGG(a)
|
|
[1,2,3,1,2,3]
|
|
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;
|
|
JSON_ARRAYAGG(DISTINCT a)
|
|
[3,2,1]
|
|
INSERT INTO t1 VALUES (NULL,NULL), (NULL,NULL);
|
|
SELECT JSON_ARRAYAGG(a) FROM t1;
|
|
JSON_ARRAYAGG(a)
|
|
[1,2,3,1,2,3]
|
|
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;
|
|
JSON_ARRAYAGG(DISTINCT a)
|
|
[3,2,1]
|
|
DROP TABLE t1;
|
|
#
|
|
#
|
|
CREATE TABLE t1(a VARCHAR(255))ENGINE=COLUMNSTORE;
|
|
INSERT INTO t1 VALUES ('red'),('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"]
|
|
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"]
|
|
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"), (2, "name2"), (3, "name3");
|
|
INSERT INTO t2 VALUES (1, 1), (2, 1), (3, 2), (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'),('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;
|