Some updates were pushed. See here for the review. https://github.com/MariaDB/server/commit/d7ff97bd1343a94430152389f90c4d08f56... On Wed, Nov 18, 2020 at 8:17 PM Sergey Petrunia <sergey@mariadb.com> wrote:
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