MySQL Bugs: #6247: Subqueries return wrong result if NULLs exist

Bug #6247	Subqueries return wrong result if NULLs exist
Submitted:	25 Oct 2004 16:17	Modified:	7 Dec 2004 20:03
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.7-debug	OS:	Linux (SuSE 8.2)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

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)

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)

Thank you for bugreport! Bug is fixed, bugfix is pushed to source repository.