Some updates were pushed. See here for the review. https://github.com/MariaDB/server/commit/d7ff97bd1343a94430152389f90c4d08f56fce44
On Wed, Nov 18, 2020 at 8:17 PM Sergey Petrunia <ser...@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 > > >
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp