[MDEV-15555] select from DUAL where false yielding wrong result when in a IN Created: 2018-03-13  Updated: 2018-03-21  Resolved: 2018-03-21

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.2.13, 10.2, 10.3
Fix Version/s: 5.5.60

Type: Bug Priority: Minor
Reporter: Rolin Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: upstream-fixed
Environment:

docker with mariadb:latest (host is a debian testing)


 Description   

10.2.13-MariaDB (tested on docker latest)

MariaDB [(none)]> SELECT 1 from DUAL WHERE 1 != 1;
 
Empty set (0.00 sec)

Ok.

Exhibit A :

MariaDB [(none)]> SELECT 2 IN (SELECT 1 from DUAL WHERE 1 != 1);
 
+----------------------------------------+
 
| 2 IN (SELECT 1 from DUAL WHERE 1 != 1) |
 
+----------------------------------------+
 
|                                      0 |
 
+----------------------------------------+
 
 
 
1 row in set (0.00 sec)

Ok.
2 is indeed not in the empty set

Exhibit B :

MariaDB [(none)]> SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1);
 
+----------------------------------------+
 
| 2 IN (SELECT 2 from DUAL WHERE 1 != 1) |
 
+----------------------------------------+
 
|                                      1 |
 
+----------------------------------------+
 
 
 
1 row in set (0.00 sec)

Not ok.
I thought 2 was not in emptyset. why changing the emptyset did affect that ?

Exhibit B looks like a DUAL bug, as :

  • it returns 0 if we replace DUAL by any other table
  • it returns 0 if I use a mysql backend instead

 Comments   
Comment by Elena Stepanova [ 2018-03-16 ]

Reproducible on all of MariaDB 5.5-10.3 and MySQL 5.5, 5.6; seems to be fixed in MySQL 5.7.

MariaDB 10.3

 
EXPLAIN EXTENDED SELECT 2 IN (SELECT 2 from DUAL WHERE 0);
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 
Warnings:
 
Note	1249	Select 2 was reduced during optimization
 
Note	1003	select 2 = 2 AS `2 IN (SELECT 2 from DUAL WHERE 0)`

MySQL 5.7

 
EXPLAIN EXTENDED SELECT 2 IN (SELECT 2 from DUAL WHERE 0);
 
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 
Warnings:
 
Warning	1681	'EXTENDED' is deprecated and will be removed in a future release.
 
Note	1003	/* select#1 */ select <in_optimizer>(2,<exists>(/* select#2 */ select 2 from DUAL  where 0)) AS `2 IN (SELECT 2 from DUAL WHERE 0)`

Comment by Sergei Petrunia [ 2018-03-20 ]

Ok to push.

Generated at Thu Feb 08 08:22:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.