Re: [Maria-developers] MDEV-17399 New patch for JSON_TABLE
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
Also attaching the test files, original and the one I have modified to get it to finish. Note the added --disable_parsing. The .result is the unmodified original On Mon, Nov 16, 2020 at 01:53:39PM +0300, Sergey Petrunia wrote:
Hi Alexey,
Input on the latest patch for MDEV-17399.
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
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
Hello, Sergei! New patch was made. I think i addressed what you've noticed in last review. https://github.com/MariaDB/server/commit/75ae330f877240e903690f282fad3ebdb02...
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?
I decided we should. Not difficult to implement and some can need it.
This is a surprise for me, too. I assumed join order dependencies created by STRAIGHT_JOIN are visible in table dependency map...
I fixed it in the Table_function_json_table::setup so it doesn't allow
dependencies to the STRAIGHT_JOIN-ed table. But probably should
be fixed in more general way.
A.
On Wed, Nov 18, 2020 at 8:17 PM Sergey Petrunia
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
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
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
Hi Alexey, On Fri, Jan 29, 2021 at 10:42:59AM +0400, Alexey Botchkov wrote:
Some updates were pushed. See here for the review. https://github.com/MariaDB/server/commit/d7ff97bd1343a94430152389f90c4d08f56...
Good. First input: MariaDB [(none)]> SELECT x, length(x) FROM JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x' DEFAULT 'abcdefg' ON EMPTY)) jt; +------+-----------+ | x | length(x) | +------+-----------+ | abc | 3 | +------+-----------+ 1 row in set (0.000 sec) Using VARACHAR(12) produces "abcd", so it seems the truncation length is one third of the N in VARCHAR(N). Is this utf8 characters vs bytes error somewhere? Please fix. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
Hi Alexey, Consider this: select * from json_table( '{"a":[1,2]}', '$' columns ( jsn_path json path '$.a' default '{}' on empty ) ) as tt; MySQL produces: +----------+ | jsn_path | +----------+ | [1, 2] | +----------+ MariaDB produces: +----------+ | jsn_path | +----------+ | NULL | +----------+ As far as I understand, MySQL is extending the standard here. The standard specifies that one can extract JSON subdocuments with "FORMAT JSON" syntax: <JSON table formatted column definition> ::= <column name> <data type> FORMAT <JSON representation> [ PATH <JSON table column path specification> ] as opposed to regular: <JSON table regular column definition> ::= <column name> <data type> [ PATH <JSON table column path specification> ] OracleDB accepts FORMAT JSON clause: https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973 select * from json_table( '{"a":[1,2]}', '$' columns ( jsn_path varchar(100) format json path '$.a' ) ) as tt; produces the same output as MySQL does. I think, MariaDB's current behavior - produce NULL when path points to a JSON sub-document, produce invalid JSON when the path points to a constant - is not acceptable. I'm fine if we just disable this and return ER_NOT_IMPLEMENTED. Alternatively, we could return JSON subdocuments like MySQL does. What do you think? BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
What do you think?
Returning the JSON subdocument would violate the standard. I think it makes
sense as
the user normally expects the scalar value and if there is the JSON
subdocument in the
searched field, it's rather an error and must be detected.
I'd add the FORMAT JSON keyword to handle this explicitly.
The Oracle implementation of the FORMAT JSON seems weird to me as it
returns NULLs
on the scalar values instead. Didn't decide to myself though if it's the
standard way to handle it.
The scalar value is actually the valid json.
Regards.
HF
On Thu, Feb 4, 2021 at 2:44 PM Sergey Petrunia
Hi Alexey,
Consider this:
select * from json_table( '{"a":[1,2]}', '$' columns ( jsn_path json path '$.a' default '{}' on empty ) ) as tt;
MySQL produces: +----------+ | jsn_path | +----------+ | [1, 2] | +----------+
MariaDB produces: +----------+ | jsn_path | +----------+ | NULL | +----------+
As far as I understand, MySQL is extending the standard here. The standard specifies that one can extract JSON subdocuments with "FORMAT JSON" syntax:
<JSON table formatted column definition> ::= <column name> <data type> FORMAT <JSON representation> [ PATH <JSON table column path specification> ]
as opposed to regular:
<JSON table regular column definition> ::= <column name> <data type> [ PATH <JSON table column path specification> ]
OracleDB accepts FORMAT JSON clause: https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973
select * from json_table( '{"a":[1,2]}', '$' columns ( jsn_path varchar(100) format json path '$.a' ) ) as tt;
produces the same output as MySQL does.
I think, MariaDB's current behavior - produce NULL when path points to a JSON sub-document, produce invalid JSON when the path points to a constant - is not acceptable.
I'm fine if we just disable this and return ER_NOT_IMPLEMENTED.
Alternatively, we could return JSON subdocuments like MySQL does.
What do you think?
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
== Short == I've discovered another mismatch between MariaDB implementation and MySQL. I don't think we need to fix it. == Long == select * from json_table( '[{"a":"aa"}, {"b":"bb"}]', '$[*]' columns ( col1 varchar(100) path '$.b' default 'bbb' on empty ) ) as tt; In MySQL, this gives an error: ERROR 3141 (22032): Invalid JSON text in argument 1 to function JSON_TABLE: "Invalid value." at position 0. and the cause is that MySQL expects the default value to be JSON. Their documentation says: DEFAULT json_string ON EMPTY I wasn't able to find the reasoning behind this. MariaDB's behavior makes more sense to me: MariaDB: +------+ | col1 | +------+ | bbb | | bb | +------+ Oracle DB produces the same. In order to get "bbb" as the default for MySQL, one needs to quote it: select * from json_table( '[{"a":"aa"}, {"b":"bb"}]', '$[*]' columns ( col1 varchar(100) path '$.b' default '"bbb"' on empty ) ) as tt; MySQL: +------+ | col1 | +------+ | bbb | | bb | +------+ MariaDB and OracleDB: +-------+ | col1 | +-------+ | "bbb" | | bb | +-------+ == Attempt to locate where this is described in the SQL Standard == Section 7.11, <JSON table>, General Rules: e, ii, 2: 2) If JTCDi is a <JSON table regular column definition>, then: A) ... let ZBi be the <JSON table column empty behavior> ... ... C) The General Rules of Subclause 9.40, “Casting an SQL/JSON sequence to an SQL type”, are applied with ST1 as STATUS IN, SEQ as SQL/JSON SEQUENCE, ZBi as EMPTY BEHAVIOR ... Looking at that section: 9.40 Casting an SQL/JSON sequence to an SQL type 4) If TEMPST is successful completion, then: ... b) If the length of SEQ is 1 (one), then let I be the SQL/JSON item in SEQ. ... iii) Otherwise, let IDT be the data type of I. ... 2) Otherwise, let X be an SQL variable whose value is I. Let V be the value of CAST (X AS DT) BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
== Short == JSON null value should be converted to SQL NULL value, but it is converted to 0 or "null" instead: == Long == select * from json_table( '[{"a":"aa"}, {"b":null}]', '$[*]' columns ( col1 int path '$.b' default '456' on empty ) ) as tt; MariaDB: +------+ | col1 | +------+ | 456 | | 0 | +------+ MySQL and OracleDB: +------+ | col1 | +------+ | 456 | | NULL | +------+ If I change "col1 int" into "col1 varchar(100)", I get a string with "null". I think this is incorrect and should be fixed. AFAIU, SQL Standard describes it here: see my previous email, then look at section 9.40, 4.b.ii: ii) If I is the SQL/JSON null, then let V be the SQL null value. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
Hi Alexey, Consider this: select * from json_table( '[{"a":"asd"}, {"a":123}, {"a":[]}, {"a":{}} ]', '$[*]' columns ( id for ordinality, intcol int path '$.a' default '1234' on empty default '5678' on error ) ) as tt; +------+--------+ | id | intcol | +------+--------+ | 1 | 0 | | 2 | 123 | | 3 | 5678 | | 4 | 5678 | +------+--------+ We can see that "default '5678' on error" clause took effect for the rows #3 and #4, but not for row #1. Also, no conversion warning was issued. MySQL and OracleDB produce this row, instead: +------+--------+ | id | intcol | +------+--------+ | 1 | 5678 | I think MariaDB should do that, too. (and if not, we need to provide a reasoning why). What do you think? BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
Hi Alexey, More input: JSON's true is converted to integer 0, not 1. SELECT * FROM JSON_TABLE('{"col": true}', '$' COLUMNS( col1 INT PATH '$.col' ERROR ON ERROR ) ) as jt; In MariaDB, this produces +------+ | col1 | +------+ | 0 | +------+ While I think it should produce what MySQL and OracleDB produce: +------+ | col1 | +------+ | 1 | +------+ BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
Hi Alexey, I have discovered another issue: JSON_TABLE's dependencies are not updated after derived merge. An example of failing query can be found below. The problem scenario is: * Table_function_json_table::setup() is called. It computes dep_tables. * The derived table is merged, mysql_derived_merge() is called. The tables are moved into the parent select and their table_map bits change. * SELECT_LEX::update_used_tables() is called to update the attributes after the change. This does nothing to the JSON_TABLE, though. * Execution proceeds with incorrect bitmap. I see that for other tables, TABLE_LIST::dep_tables is computed in simplify_joins(). simplify_joins() can remove table dependencies if it converts an outer join into inner. Can it somehow remove a dependency of JSON_TABLE(...) on its argument? I haven't been able to construct such case. (An obvious reason one cannot easily do this is that JSON_TABLE() doesn't allow to declare NOT NULL columns. I'm not sure if this a fundamental limitation or something random). I think the very least that should be done is to make a comment about this in simplify_joins(). === Testcase == create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (js json, b int); insert into t2 select '[1,2,3]',A.a from t1 A, t1 B; explain select * from t1, (select * from t2, json_table(t2.js, '$[*]' columns (o for ordinality)) as jt ) as TT2 where 1; +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | jt | ALL | NULL | NULL | NULL | NULL | 40 | Table function: json_table | | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ Note the incorrect join order: jt has t2.js as a parameter but it precedes t2. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
On Mon, Feb 08, 2021 at 08:34:15PM +0300, Sergey Petrunia wrote:
I see that for other tables, TABLE_LIST::dep_tables is computed in simplify_joins(). simplify_joins() can remove table dependencies if it converts an outer join into inner. Can it somehow remove a dependency of JSON_TABLE(...) on its argument? I haven't been able to construct such case. (An obvious reason one cannot easily do this is that JSON_TABLE() doesn't allow to declare NOT NULL columns. I'm not sure if this a fundamental limitation or something random). I think the very least that should be done is to make a comment about this in simplify_joins().
Debugging further, I see that TABLE_LIST::dep_tables only includes "direct" dependencies. That is, for t1 left join (t2, t3 ) ON ... t2->dep_tables=0 and t1->dep_tables=0, both before and after simplify_joins(). After simplify_joins, t2->embedding->dep_tables={t1}, same for t3. Well, this means the check for dependencies is incomplete. Trying to construct a testcase for this: create table t2 (a int not null, b int not null); create table t3 (a int not null, b int not null); create table t4 (a int not null, js json); explain select * from json_table(t4.js, '$[*]' columns (o for ordinality)) as jt left join (t4, t3) on js.o=t4.a ; this crashes, the stack tace is below. Please wait with fixing this. I think the fix here should be to adjust the name resolution so that this query doesn't pass the name resolution phase. Thread 17 "mysqld" received signal SIGSEGV, Segmentation fault. 0x0000555555e41206 in TABLE_LIST::get_map (this=0x7ffe9c017e00) at sql/table.h:2286 (gdb) wher #0 0x0000555555e41206 in TABLE_LIST::get_map (this=0x7ffe9c017e00) at sql/table.h:2286 #1 0x00005555560f26f5 in Table_function_json_table::setup (this=0x7ffe9c015918, thd=0x7ffe9c000d78, sql_table=0x7ffe9c0168f8) at sql/table_function.cc:1088 #2 0x0000555555ea7e16 in JOIN::prepare (this=0x7ffe9c01a2f0, tables_init=0x7ffe9c0168f8, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7ffe9c015210, unit_arg=0x7ffe9c004f18) at sql/sql_select.cc:1263 #3 0x0000555555eb4a2a in mysql_select (thd=0x7ffe9c000d78, tables=0x7ffe9c0168f8, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7ffe9c019bd0, unit=0x7ffe9c004f18, select_lex=0x7ffe9c015210) at sql/sql_select.cc:4643 #4 0x0000555555ef34e8 in mysql_explain_union (thd=0x7ffe9c000d78, unit=0x7ffe9c004f18, result=0x7ffe9c019bd0) at sql/sql_select.cc:27235 #5 0x0000555555e6775e in execute_sqlcom_select (thd=0x7ffe9c000d78, all_tables=0x7ffe9c0168f8) at sql/sql_parse.cc:6211 #6 0x0000555555e5eb75 in mysql_execute_command (thd=0x7ffe9c000d78) at sql/sql_parse.cc:3968 BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
I've also found this: CREATE FUNCTION FN_COUNT_ROWS(X JSON) RETURNS INT DETERMINISTIC RETURN ( SELECT COUNT(*) FROM JSON_TABLE( X, '$[*]' COLUMNS( I INT PATH '$')) der ); SELECT FN_COUNT_ROWS('[1, 2]') CNT; This produces +------+ | CNT | +------+ | 0 | +------+ while it should produce 2. Please investigate and fix this. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
On Fri, Feb 05, 2021 at 02:01:22PM +0300, Sergey Petrunia wrote:
I've also found this:
CREATE FUNCTION FN_COUNT_ROWS(X JSON) RETURNS INT DETERMINISTIC RETURN ( SELECT COUNT(*) FROM JSON_TABLE( X, '$[*]' COLUMNS( I INT PATH '$')) der ); SELECT FN_COUNT_ROWS('[1, 2]') CNT;
This produces +------+ | CNT | +------+ | 0 | +------+
while it should produce 2.
Ok, there is no issue with SP. A testcase without SP: CREATE TABLE t (j JSON) engine=myisam; INSERT INTO t VALUES ('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]'), ('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]'); SELECT COUNT(*) FROM t, JSON_TABLE(j, '$[*]' COLUMNS (i INT PATH '$')) AS jt; will produce "0". The problem where is that the code in opt_sum_query() assumes it can use records() to get # rows from the ha_json_table object. It needs to be changed to take into account that ha_json_table doesn't have records available, yet. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
participants (2)
-
Alexey Botchkov -
Sergey Petrunia