MySQL Bugs: #114897: json_table and exists make a bug

Bug #114897	json_table and exists make a bug
Submitted:	6 May 2024 16:18	Modified:	6 Feb 15:58
Reporter:	庆杰胡	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	json, json_table

Description:
when i use `json_table()` and `exists` in where clause, it cannot work,but `not exists` work well

How to repeat:
-- prepare data -----------
drop table if exists test;
create table test(id int primary key auto_increment,name varchar(50),ext json)
insert into test(name,ext) values
	('LaoWang','[{"name":"tom","age":18},{"name":"tom2","age":19}]'),
	('LaoLi','[{"name":"lisa","age":20},{"name":"lisa2","age":16}]');

-- query -----------------
select t.* from test t where exists (
	select * from json_table(t.ext,'$[*]' columns(age int path '$.age')) t3
	where t3.age > 19
)

-- out --------
out nothing!

-- other, use `not exists`, it does well  --------
select t.* from test t where not exists (
	select * from json_table(t.ext,'$[*]' columns(age int path '$.age')) t3
	where t3.age > 19
)

Suggested fix:

i'm working with orm, and i want provide function below:

```csharp
var teachers = orm.Select<Teacher>().Where(i=>i.Students.Any(i=>i.Age>19)).ToList();

```

now, i'm blocking

HI Mr. 

Thank you for your bug report.

We managed to repeat your results:

--
--
id	name	ext
1	LaoWang	[{"age": 18, "name": "tom"}, {"age": 19, "name": "tom2"}]

However, when we analyse the contents, results are good. Because, the nested query does not return any results.

Hence, this is expected behaviour.

Not a bug.

i still think it's a bug.

when table has one row,it does well:
    -- prepare data
    create table test(id int primary key auto_increment,name varchar(50),ext json)
    insert into test(name,ext) values ('LaoWang','[{"name":"tom","age":18},{"name":"tom2","age":19}]');

    -- the query
    select t.* from test t where exists (
	select * from json_table(t.ext,'$[*]' columns(age int path '$.age')) t3
	where t3.age > 16
    )
    -- out
    id|name   |ext                                                      |
    --+-------+---------------------------------------------------------+
    7|LaoWang|[{"age": 18, "name": "tom"}, {"age": 19, "name": "tom2"}]|

but, when the table has two row, it out nothing:
    -- prepare data
    create table test(id int primary key auto_increment,name varchar(50),ext json)
    insert into test(name,ext) values
	('LaoWang','[{"name":"tom","age":18},{"name":"tom2","age":19}]'),
	('LaoLi','[{"name":"lisa","age":20},{"name":"lisa2","age":16}]');

    -- the query
    select t.* from test t where exists (
	select * from json_table(t.ext,'$[*]' columns(age int path '$.age')) t3
	where t3.age > 16
    )

    -- out nothing

Hi Mr. 庆杰 胡 ,

But, we can not repeat your results with our 8.4.0  binaries. 

We have left only the SELECT with EXISTS, eliminated the one with NOT EXISTS and we get empty results with one and with two rows;

TWO ROWS
------------

ONE ROW
---------

We can't repeat it with our binary.

Hi Mr. 庆杰 胡,

Sorry, we made a mistake .....

We had both SELECTs with a condition of > 19. When we changed to > 16, the bug popped up:

TWO ROWS
ONE ROW
id	name	ext
1	LaoWang	[{"age": 18, "name": "tom"}, {"age": 19, "name": "tom2"}]

This is now a verified bug in the JSON data type.

Thank you for your contribution.

Hi,

We have checked and this bug affects 8.0 as well:

TWO ROWS
ONE ROW
id	name	ext
1	LaoWang	[{"age": 18, "name": "tom"}, {"age": 19, "name": "tom2"}]

This is now a verified bug for 8.0 and all higher versions.

Hello Team,

Is there any ETA for the fix?

Hi Mr. Dubey,

There are no ETAs for the fixing of any bugs.

Each of our Development teams have their own schedule, which is changing on the weekly basis. Hence , there are no ETAs for any bugs, except for the Security Vulnerability bugs.

We need to observe that query returns correct result set only when the table has one row.

When the table has 1 row: the query uses Nested Loop Join based access path
When the table has >=2 rows: the query uses Hash Join based access path

It happens that the optimizer uses the wrong table order during the Hash Join and thus the query results in empty result set.

However, there is a workaround for this problem, and it is to use the JOIN_ORDER optimizer hint and explicitly specifying the join order.

In fact it can also be verified using the explain query. In the above example, it uses the (t3,t) as the join order.

mysql> explain select t.* from test t where exists (select * from json_table(t.ext,'$[*]' columns(age int path '$.age')) t3 where t3.age > 19);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                                |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------------+
|  1 | SIMPLE      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Table function: json_table; Using temporary; Using where; FirstMatch |
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using join buffer (hash join)                                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

However when the join order is reversed i.e (t,t3) we get the correct result.
mysql> select /*+JOIN_ORDER(t,t3)*/ t.* from test t where exists (select * from json_table(t.ext,'$[*]' columns(age int path '$.age')) t3 where t3.age > 19);
+----+-------+-------------------------------------------------------------+
| id | name  | ext                                                         |
+----+-------+-------------------------------------------------------------+
|  2 | LaoLi | [{"age": 20, "name": "lisa"}, {"age": 16, "name": "lisa2"}] |
+----+-------+-------------------------------------------------------------+
1 row in set (0.00 sec)

Now use the join order (t3,t) and we have empty result once again.
mysql> select /*+JOIN_ORDER(t3,t)*/ t.* from test t where exists (select * from json_table(t.ext,'$[*]' columns(age int path '$.age')) t3 where t3.age > 19);
Empty set (0.00 sec)

--
Venkatesh Prasad

Thank you, Mr. Venugopal,

We shall copy your very useful  comments to our internal bugs database.

Documented fix as follows in the MySQL 9.3.0 changelog:

    A query using WHERE EXISTS( SELECT ... FROM JSON_TABLE(...) )
    did not return the expected result.

Closed.

Thank you, Jon.