On Mon, Nov 16, 2020 at 01:53:39PM +0300, Sergey Petrunia wrote:
Input on the latest patch for MDEV-17399. .. Please find the first few cases below.
And a few more from the same source: === JSON_TABLE cannot depend on another one ? === drop table t1; CREATE TABLE t1(id int, jd varchar(100)); SELECT id, jt1.*, jt2.* FROM t1, JSON_TABLE(jd, '$' COLUMNS (data1 JSON PATH '$')) AS jt1, JSON_TABLE(data1, '$[*]' COLUMNS (id2 INT PATH '$')) AS jt2; ERROR 1054 (42S22): Unknown column 'data1' in 'JSON_TABLE argument' === Dependency caused by STRAIGHT_JOIN is not visible? === This one is interesting: it produces an error in MySQL but crashes MariaDB due to unability to pick a query plan. This is a surprise for me, too. I assumed join order dependencies created by STRAIGHT_JOIN are visible in table dependency map... drop table t1; CREATE TABLE t1(id INT, f1 JSON); INSERT INTO t1 VALUES (1, '{\"1\": 1}'), (2, '{\"1\": 2}'), (3, '{\"1\": 3}'), (4, '{\"1\": 4}'), (5, '{\"1\": 5}'), (6, '{\"1\": 6}'); ANALYZE TABLE t1; SELECT * FROM t1 as jj1, (SELECT tt2.* FROM t1 as tt2, JSON_TABLE(tt3.f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN t1 AS tt3 ) dt ORDER BY 1,3 LIMIT 10; === COLLATE clause is not supported === This fails with an error: CREATE TABLE t2 SELECT * FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10) CHARSET utf8mb4 COLLATE utf8mb4_bin PATH '$')) AS jt1; I am not sure if we need to support it (I saw the change_charset() call in the patch). What's your opinion? === CHARSET is accepted but ignored === select collation(x) from JSON_TABLE( '["abc"]', '$[*]' COLUMNS (x VARCHAR(10) CHARSET latin1 PATH '$') ) tbl; +-----------------+ | collation(x) | +-----------------+ | utf8_general_ci | +-----------------+ If we don't support columns having different charset, this should not be allowed. === Character set introducers are not supported === They are supported in the first parameter, but not in the path or default clauses. SELECT * FROM JSON_TABLE(JSON_OBJECT(), _utf8mb4'$' COLUMNS (NESTED PATH _utf8mb4'$.x' COLUMNS (y INT PATH _utf8mb4'$.y' DEFAULT _utf8mb4'1' ON EMPTY DEFAULT _utf8mb4'2' ON ERROR))) jt; BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog