Bug #6247 Subqueries return wrong result if NULLs exist
Submitted: 25 Oct 2004 18:17 Modified: 7 Dec 2004 21:03
Reporter: Peter Gulutzan
Status: Closed
Category:Server Severity:S3 (Non-critical)
Version:4.1.7-debug OS:Linux (SuSE 8.2)
Assigned to: Oleksandr Byelkin Target Version:

[25 Oct 2004 18:17] Peter Gulutzan 
Description:
For quantified comparisons with "operator ALL (SELECT columnx FROM tablex)" the result 
should be TRUE if tablex is empty, but UNKNOWN if tablex has a NULL. Therefore 
"WHERE ... column1 > ALL (SELECT columnx FROM tablex)" should return zero rows if 
there are NULLs. But in fact, it returns more than zero rows if there are NULLs in
columnx, 
or even if there are NULLs in both columnx and column1. 
 
See also: BUG#2096 ("Not a bug"). 
 
 

How to repeat:
mysql> create table t99 (s1 int); 
Query OK, 0 rows affected (0.05 sec) 
 
mysql> insert into t99 values (1),(null); 
Query OK, 2 rows affected (0.00 sec) 
Records: 2  Duplicates: 0  Warnings: 0 
 
mysql> select * from t99 where s1 < all (select s1 from t99); 
+------+ 
| s1   | 
+------+ 
| NULL | 
+------+ 
1 row in set (0.00 sec)
[18 Nov 2004 17:12] Oleksandr Byelkin 
ChangeSet 
  1.2118 04/11/18 18:10:07 bell@sanja.is.com.ua +11 -0 
  reporting empty result added in case of max/min optimisation of ALL/ANY/SOME 
subqueries 
  fixed null processing in NOT operation used in ALL subquery (Bug #6247)
[7 Dec 2004 21:03] Oleksandr Byelkin 
Thank you for bugreport! Bug is fixed, bugfix is pushed to source repository.