maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi Alexey,

Input on the latest patch for MDEV-17399.

It's good to see the patch taking shape, but have you tried running MySQL's
tests for JSON_TABLE on it?

I did, and it has caught several crashes, as well as error-no-error
discrepancies and different query results. Some of these were expected, but
some show that something needs to be fixed.

Please find the first few cases below.

It seems there is something odd going odd with the name resolution, both in
MariaDB and MySQL - I don't have explanation for some of the errors. I intend
to study the issue more and elaborate in a later email.

Meanwhile, first obvious cases:

=== Crash in Name resolution ==

CREATE TABLE t1 (a INT, b INT);

CREATE VIEW v2 AS SELECT * FROM t1 LIMIT 2;

SELECT b
 FROM (SELECT * FROM v2) vq1,
 JSON_TABLE(CONCAT(vq1.b,'[{\"a\":\"3\"}]'),
            '$[*]' COLUMNS (id FOR ORDINALITY,
                            jpath VARCHAR(100) PATH '$.a',
                            JEXST INT EXISTS PATH '$.b')
            ) AS dt;

=== Crash in error reporting ===

select * from
  json_table(
    '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
    "!@#$!@#$" columns (id for ordinality,
             jpath varchar(100) path '$.a',
             jexst int exists path '$.b')
  ) as tt;

=== Item print misses quotes ===

CREATE VIEW v2 AS
  SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (
    x VARCHAR(10) PATH '$.a' DEFAULT '"isn''t here"' ON EMPTY)
  ) t;
SHOW CREATE VIEW v2;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't here"' ON EMPTY)) `t`' at line 1

=== Error for outer reference ==

# JSON_TABLE referring outer scope

CREATE TABLE t1(id int, jd JSON);
INSERT INTO t1 values (1, '[1,3,5]'),(2,'[2,4,6]');

SELECT * FROM t1 WHERE id IN
  (SELECT * FROM JSON_TABLE(t1.jd, '$[*]' COLUMNS
                            (id INT PATH '$')) AS jt);

Produces:
ERROR 1054 (42S22): Unknown column 't1.jd' in 'JSON_TABLE argument' 
Should not produce it
(Note that for some other kinds of outer references it seemed to work)

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog