Bug #114897 json_table and exists make a bug
Submitted: 6 May 16:18 Modified: 10 May 9:51
Reporter: 庆杰 胡 Email Updates:
Status: Verified 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

[6 May 16:18] 庆杰 胡
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
[9 May 10:04] MySQL Verification Team
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.
[9 May 16:04] 庆杰 胡
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
[10 May 9:45] MySQL Verification Team
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.
[10 May 9:51] MySQL Verification Team
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.
[16 May 10:51] MySQL Verification Team
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.