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

[11 Jul 2006 19:57] Peter Gulutzan
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)
[13 Jul 2006 0:43] MySQL Verification Team
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)
[20 Jul 2006 18:48] Bugs System
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
[21 Jul 2006 13:15] Sergey Petrunya
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
[2 Aug 2006 18:39] Evgeny Potemkin
Fixed in 4.1.22, 5.0.25, 5.1.12
[7 Aug 2006 8:31] Jon Stephens
Documented bugfix in 4.1.22, 5.0.25, and 5.1.12 changelogs.