MySQL Bugs: #61799: Wrong result with < ALL and subquery returning an empty list

Bug #61799	Wrong result with < ALL and subquery returning an empty list
Submitted:	8 Jul 2011 9:15	Modified:	5 May 2018 14:54
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1,5.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The following query

SELECT * FROM t3 WHERE f2 < ALL ( SELECT f2 FROM t1 WHERE ( 0 ) IN ( SELECT f2 FROM t2 ) );

returns no rows even though the subquery evaluates to an empty list and therefore the < ALL predicate should evaluate to TRUE.

How to repeat:
CREATE TABLE t1 ( f1 int, f2 int, KEY (f2) ) ;
INSERT INTO t1 VALUES (8,0);

CREATE TABLE t2 ( f2 int) ;

CREATE TABLE t3 ( f2 int) ;
INSERT INTO t3 VALUES (0);

SELECT *
FROM t3
WHERE f2 < ALL (
SELECT f2 FROM t1 WHERE ( 0 ) IN (
SELECT f2 FROM t2
)
);

Yes, manual (http://dev.mysql.com/doc/refman/5.1/en/all-subqueries.html) implicitly says that < ALL (<some select>) should evaluate to TRUE if <some select> returns no rows.

It is easy to verify that in your case it does not happen:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.56-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t1 ( f1 int, f2 int, KEY (f2) ) ;
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t1 VALUES (8,0);
Query OK, 1 row affected (0.11 sec)

mysql> CREATE TABLE t2 ( f2 int) ;
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE t3 ( f2 int) ;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO t3 VALUES (0);
Query OK, 1 row affected (0.06 sec)

mysql> SELECT f2 FROM t1 WHERE ( 0 ) IN (
    -> SELECT f2 FROM t2);
Empty set (0.09 sec)

mysql> SELECT *
    -> FROM t3
    -> WHERE f2 < ALL (
    -> SELECT f2 FROM t1 WHERE ( 0 ) IN (
    -> SELECT f2 FROM t2
    -> )
    -> );
Empty set (0.06 sec)

while in more simple cases it works as expected:

mysql> SELECT *
    -> FROM t3
    -> WHERE f2 < ALL (select 1 from mysql.user where 1=0);
+------+
| f2   |
+------+
|    0 |
+------+
1 row in set (0.08 sec)

So we definitely have a bug here.

Seems it was fixed in 5.7:

mysql> SELECT *
    -> FROM t3
    -> WHERE f2 < ALL (
    -> SELECT f2 FROM t1 WHERE ( 0 ) IN (
    -> SELECT f2 FROM t2
    -> )
    -> );
+------+
| f2   |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

Documented fix as follows in the MySQL 5.6.40 changelog:

    < ALL (select_expression) did not always evaluate as TRUE when the
    select_expression did not return any rows.

Closed.