MySQL Bugs: #20975: Subquery with NOT (ANY) differs from subquery with (ANY) IS FALSE

Bug #20975	Subquery with NOT (ANY) differs from subquery with (ANY) IS FALSE
Submitted:	11 Jul 2006 19:57	Modified:	7 Aug 2006 8:31
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.12-beta-debug/5.0/5.1BK/4.1	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
I expect that WHERE NOT (column < ANY (subquery))
should be like WHERE (column < ANY (subquery)) IS FALSE.
But the results are different.

How to repeat:
mysql> create table t11 (s1 char);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t11 values ('a'),('b');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t11 where s1 < any (select s1 from t11);
+------+
| s1   |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql> select * from t11 where (s1 < any (select s1 from t11)) is false;
+------+
| s1   |
+------+
| b    |
+------+
1 row in set (0.00 sec)

mysql> select * from t11 where not (s1 < any (select s1 from t11));
+------+
| s1   |
+------+
| a    |
+------+
1 row in set (0.00 sec)

Thank you for the bug report. On 4.1 the last query fails like 5.0/5.1:

mysql> select * from t11 where not (s1 < any (select s1 from t11));
+------+
| s1   |
+------+
| a    |
+------+
1 row in set (0.02 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.21-debug |
+--------------+
1 row in set (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9392

Notes for the changelog:
expressions in form 
 
  NOT (expr $cmp$ ALL/ANY (subquery))

where $cmp is not '=' were evaluated as if NOT was not present, and that caused wrong query results

Fixed in 4.1.22, 5.0.25, 5.1.12

Documented bugfix in 4.1.22, 5.0.25, and 5.1.12 changelogs.