Bug #44339 Wrong result with WHERE 1 IN (SELECT 1 FROM DUAL WHERE FALSE)

• Submitted: 17 Apr 2009 7:47
• Modified: 10 Feb 2018 17:25
• Reporter: Petr Neuman
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.1.33, 5.0.80-bzr, 5.0.51
• OS: Any
• CPU Architecture: Any

[17 Apr 2009 7:47] Petr Neuman

Description:
Operators in and not in do not work correctly with empty subselect from dual.

How to repeat:
Server version: 5.1.33-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select 1 from dual where false;
Empty set (0.00 sec)

mysql> select 1 from dual where 1 in (select 1 from dual where false);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select 1 from dual where 1 in (select 2 from dual where false);
Empty set (0.00 sec)

mysql> select 1 from dual where 1 in (select null from dual where false);
Empty set (0.00 sec)

mysql> select 1 from dual where 1 not in (select 1 from dual where false);
Empty set (0.00 sec)

mysql> select 1 from dual where 1 not in (select 2 from dual where false);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select 1 from dual where 1 not in (select null from dual where false);
Empty set (0.00 sec)

[17 Apr 2009 8:27] Petr Neuman

select 1 from dual where not in (select null from (select 1 from dual) t where false)
is correctly

[17 Apr 2009 13:57] Valeriy Kravchuk

Thank you for the bug report. Verified just as described also with 5.0.80 from bzr on Linux.

[10 Feb 2018 17:25] Roy Lyseng

Posted by developer:
 
Fixed in 5.7.22 and up.