MySQL Bugs: #111493: sql return wrong results

Bug #111493	sql return wrong results
Submitted:	20 Jun 2023 8:25	Modified:	20 Jun 2023 12:52
Reporter:	hel le	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The sql query with exists return wrong results.

How to repeat:
CREATE TABLE `test1` (
  `id` int DEFAULT NULL,
  `name` char(12) DEFAULT NULL,
  KEY `key_c` (`name`)
) ENGINE=InnoDB;
insert into test1 values(1, NULL);

CREATE TABLE `test5` (
  `c_id` int(255) unsigned zerofill NOT NULL,
  `c_state` char(255) DEFAULT NULL,
  KEY `ndx_c_state` (`c_state`)
) ENGINE=InnoDB;

insert into test5 values(1,NULL),(2,NULL),(3,NULL),(4,NULL),(5,NULL),(6,NULL),(7,NULL),(8,NULL),(9,NULL),(10,NULL),(11,2),(12,2),(13,3),(14,4);

select ref_2.c_state as c0 
from 
  test1 right join test5 as ref_2 on (
	EXISTS (
	  select ref_0.id from test1 `ref_0` inner join test1 `ref_1` on (`ref_0`.`name` is not null)
	)
  ) 
where 
  EXISTS (
	select 1 from test5 as ref_15 where (ref_15.c_state is NULL)  
  ) and ref_2.c_state is NULL group by 1;

Empty set (0.00 sec)

This query should return NULL.

Hi Mr. le,

Thank you for your bug report.

However, it is not a bug.

Your RIGHT JOIN matches zero rows, so it's output is an empty set. Hence, WHERE clause is here irrelevant, since it is applied to the empty set.

Not a bug.

when turn off materialization , result has value. trun on materialization result is empty.

mysql> set optimizer_switch='materialization=off';
Query OK, 0 rows affected (0.00 sec)

mysql> select ref_2.c_state as c0  from    test1 right join test5 as ref_2 on ( EXISTS (   select ref_0.id from test1 `ref_0` inner join sqltester.test1 `ref_1` on (`ref_0`.`name` is not null) )   )  where    EXISTS ( select 1 from test5 as ref_15 where (ref_15.c_state is NULL)     ) and ref_2.c_state is NULL group by 1;
+------+
| c0   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> set optimizer_switch='materialization=on';
Query OK, 0 rows affected (0.01 sec)

mysql> select ref_2.c_state as c0  from    test1 right join test5 as ref_2 on ( EXISTS (   select ref_0.id from test1 `ref_0` inner join sqltester.test1 `ref_1` on (`ref_0`.`name` is not null) )   )  where    EXISTS ( select 1 from test5 as ref_15 where (ref_15.c_state is NULL)     ) and ref_2.c_state is NULL group by 1;
Empty set (0.00 sec)

The query results should not be inconsistent due to different parameters. So it returns an empty error.

Hi Mr. le,

Yes, you are correct that optimiser switches should not affect results, but actually according to:

https://dev.mysql.com/doc/refman/8.0/en/subquery-materialization.html

it is quite possible that this can occur.

This means that documentation has to be amended. 

We shall verify this bug as the optimiser bug, but it will, most likely, be addressed in the Manual.

Verified.