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