[MDEV-30623] JSON_TABLE in subquery not correctly marked as correlated - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.6.11, 10.6, 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4
Fix Version/s: 10.6.19, 10.11.9, 11.1.6, 11.2.5, 11.4.3, 11.5.2
Component/s: JSON, Optimizer
Labels:
- JSON_TABLE
- optimizer-easy

Description

CREATE TEMPORARY TABLE t1 (t1_id int, t1_json text);

INSERT t1 VALUES (1, '[[10,20]]'), (2, '[[100,200]]'), (3, '[[1000,2000]]');

SELECT

	t1_id,

	t2_json,

	(SELECT SUM(x2)

	FROM

	  JSON_TABLE(t2_json, '$[*]' COLUMNS ( x2 int PATH '$' )) t3

	) t2_json_sum

FROM

t1

  CROSS JOIN

  JSON_TABLE(t1_json, '$[*]' COLUMNS (t2_json json PATH '$' )) t2;

Gives the result:

+-------+-------------+-------------+

| t1_id | t2_json     | t2_json_sum |

+-------+-------------+-------------+

|     1 | [10,20]     |          30 |

|     2 | [100,200]   |          30 |

|     3 | [1000,2000] |          30 |

+-------+-------------+-------------+

In the subquery the t2_json column from first row is used on every row.

Attachments

Issue Links

is duplicated by

MDEV-34284 Wrong result when using JSON_TABLE in correlated subquery

Closed

MDEV-34651 Subquery gives wrong results: using JSON_TABLE in SELECT part

Closed

links to

SO question - same problem

Activity

Ascending order - Click to sort in descending order

View 4 older comments

Marc added a comment - 2024-03-19 11:59

I've got a very similar (same) issue:
if you want to "join" JSON-Array's this is not very handy, but should work as follows:

CREATE TABLE `tbl` (

  `ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

 `Tankkarten` json DEFAULT NULL

);

SELECT t.ID, t.js,

(SELECT group_concat(jt.item  SEPARATOR "\n" )

FROM JSON_TABLE(t.js, "$[*]" COLUMNS (item tinytext PATH "$")) jt ) AS tk

FROM tbl t

LIMIT 50

but the first value is repeated for every row

Marc added a comment - 2024-03-19 11:59 I've got a very similar (same) issue: if you want to "join" JSON-Array's this is not very handy, but should work as follows: CREATE TABLE `tbl` ( `ID` smallint (5) unsigned NOT NULL AUTO_INCREMENT, `Tankkarten` json DEFAULT NULL ); SELECT t.ID, t.js, ( SELECT group_concat(jt.item SEPARATOR "\n" ) FROM JSON_TABLE(t.js, "$[*]" COLUMNS (item tinytext PATH "$" )) jt ) AS tk FROM tbl t LIMIT 50 but the first value is repeated for every row

Alice Sherepa added a comment - 2024-06-03 15:56

from ~~MDEV-34284~~

MariaDB [test]> select a, b, (

    ->   select json_arrayagg(i)

    ->   from json_table(json_array(a, b), '$[*]' columns (i int path '$')) t

    -> )

    -> from (select 1 a, 2 b union all select 3 a, 4 b) as t

    -> order by a;

+---+---+------------------------------------------------------------------------------------------------------+

| a | b | (

  select json_arrayagg(i)

  from json_table(json_array(a, b), '$[*]' columns (i int path '$')) t

) |

+---+---+------------------------------------------------------------------------------------------------------+

| 1 | 2 | [1,2]                                                                                                |

| 3 | 4 | [1,2]                                                                                                |

+---+---+------------------------------------------------------------------------------------------------------+

2 rows in set (0,001 sec)

MariaDB [test]> explain extended select a, b, (   select json_arrayagg(i)   from json_table(json_array(a, b), '$[*]' columns (i int path '$')) t )  from (select 1 a, 2 b union all select 3 a, 4 b) as t order by a;

+------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------+

| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                      |

+------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------+

|    1 | PRIMARY     | <derived3> | ALL  | NULL          | NULL | NULL    | NULL | 2    |   100.00 | Using filesort             |

|    3 | DERIVED     | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used             |

|    4 | UNION       | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used             |

|    2 | SUBQUERY    | t          | ALL  | NULL          | NULL | NULL    | NULL | 40   |   100.00 | Table function: json_table |

+------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------+

4 rows in set, 3 warnings (0,001 sec)

Note (Code 1276): Field or reference 't.a' of SELECT #2 was resolved in SELECT #1

Note (Code 1276): Field or reference 't.b' of SELECT #2 was resolved in SELECT #1

Note (Code 1003): /* select#1 */ select `t`.`a` AS `a`,`t`.`b` AS `b`,(/* select#2 */ select json_arrayagg(`t`.`i`) from JSON_TABLE(json_array(`t`.`a`,`t`.`b`), '$[*]' COLUMNS (`i` int(11) PATH '$')) `t`) AS `(   select json_arrayagg(i)   from json_table(json_array(a, b), '$[*]' columns (i int path '$')) t )` from (/* select#3 */ select 1 AS `a`,2 AS `b` union all /* select#4 */ select 3 AS `a`,4 AS `b`) `t` order by `t`.`a`

MariaDB [test]> select a, b, (   select json_arrayagg(i)   from json_table(json_array(a, b), '$[*]' columns (i int path '$') ) t where rand() is not null )  from (select 1 a, 2 b union all select 3 a, 4 b) as t order by a;

+---+---+--------------------------------------------------------------------------------------------------------------------------------+

| a | b | (   select json_arrayagg(i)   from json_table(json_array(a, b), '$[*]' columns (i int path '$') ) t where rand() is not null ) |

+---+---+--------------------------------------------------------------------------------------------------------------------------------+

| 1 | 2 | [1,2]                                                                                                                          |

| 3 | 4 | [3,4]                                                                                                                          |

+---+---+--------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0,001 sec)

Alice Sherepa added a comment - 2024-06-03 15:56 from MDEV-34284 MariaDB [test]> select a, b, ( -> select json_arrayagg(i) -> from json_table(json_array(a, b), '$[*]' columns (i int path '$')) t -> ) -> from (select 1 a, 2 b union all select 3 a, 4 b) as t -> order by a; +---+---+------------------------------------------------------------------------------------------------------+ | a | b | ( select json_arrayagg(i) from json_table(json_array(a, b), '$[*]' columns (i int path '$')) t ) | +---+---+------------------------------------------------------------------------------------------------------+ | 1 | 2 | [1,2] | | 3 | 4 | [1,2] | +---+---+------------------------------------------------------------------------------------------------------+ 2 rows in set (0,001 sec) MariaDB [test]> explain extended select a, b, ( select json_arrayagg(i) from json_table(json_array(a, b), '$[*]' columns (i int path '$')) t ) from (select 1 a, 2 b union all select 3 a, 4 b) as t order by a; +------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------+ | 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using filesort | | 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 4 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 2 | SUBQUERY | t | ALL | NULL | NULL | NULL | NULL | 40 | 100.00 | Table function: json_table | +------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------+ 4 rows in set, 3 warnings (0,001 sec) Note (Code 1276): Field or reference 't.a' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 't.b' of SELECT #2 was resolved in SELECT #1 Note (Code 1003): /* select#1 */ select `t`.`a` AS `a`,`t`.`b` AS `b`,(/* select#2 */ select json_arrayagg(`t`.`i`) from JSON_TABLE(json_array(`t`.`a`,`t`.`b`), '$[*]' COLUMNS (`i` int(11) PATH '$')) `t`) AS `( select json_arrayagg(i) from json_table(json_array(a, b), '$[*]' columns (i int path '$')) t )` from (/* select#3 */ select 1 AS `a`,2 AS `b` union all /* select#4 */ select 3 AS `a`,4 AS `b`) `t` order by `t`.`a` MariaDB [test]> select a, b, ( select json_arrayagg(i) from json_table(json_array(a, b), '$[*]' columns (i int path '$') ) t where rand() is not null ) from (select 1 a, 2 b union all select 3 a, 4 b) as t order by a; +---+---+--------------------------------------------------------------------------------------------------------------------------------+ | a | b | ( select json_arrayagg(i) from json_table(json_array(a, b), '$[*]' columns (i int path '$') ) t where rand() is not null ) | +---+---+--------------------------------------------------------------------------------------------------------------------------------+ | 1 | 2 | [1,2] | | 3 | 4 | [3,4] | +---+---+--------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0,001 sec)

Rex Johnston added a comment - 2024-06-19 02:08 - edited

psergei was almost right. Outside references are correctly gathered, but then the attribute st_select_lex::is_correlated is reset from the correct value (true, set during name resolution) back to false during the optimization phase by st_select_lex::update_correlated_cache().

The fix is to update st_select_lex::update_correlated_cache() to look for a json_table function in it's TABLE_LIST and set the appropriate flag if it is correlated.

Rex Johnston added a comment - 2024-06-19 02:08 - edited psergei was almost right. Outside references are correctly gathered, but then the attribute st_select_lex::is_correlated is reset from the correct value (true, set during name resolution) back to false during the optimization phase by st_select_lex::update_correlated_cache() . The fix is to update st_select_lex::update_correlated_cache() to look for a json_table function in it's TABLE_LIST and set the appropriate flag if it is correlated.

Sergei Petrunia added a comment - 2024-07-08 13:01

Hi Johnston,
Ok to push.

Sergei Petrunia added a comment - 2024-07-08 13:01 Hi Johnston , Ok to push.

Rex Johnston added a comment - 2024-07-08 17:49

for the release notes: usage of columns from outer table references within json_table() will likely cause incorrect results.

Rex Johnston added a comment - 2024-07-08 17:49 for the release notes: usage of columns from outer table references within json_table() will likely cause incorrect results.

MariaDB Server

JSON_TABLE in subquery not correctly marked as correlated

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration