Incorrect result of NULL <op> ALL (<empty set>)

[Philip Stoev]

The following query:

SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ;

returns 1 row for which a1.f3 = NULL with join_cache_level = 0 and 2 rows with join_cache_level = 3 . maria-5.1 returns 2 rows. Notice that the subquery returns an empty result.

Note that the server reports that NULL < ALL ( SELECT f3 FROM t3 WHERE 0 ) is TRUE

test case:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( f11 int) ;
INSERT IGNORE INTO t1 VALUES (0),(0);

CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ;
INSERT IGNORE INTO t2 VALUES (0,0),(98,0),(6,0),(5,0),(0,0),(3,0),(1,0),(1,0),(147,0),(3,0),(3,0),(NULL,NULL),(2,0),(1,0),(8,0),(8,0),(8,0),(0,0),(1,0),(8,0),(5,0);

DROP TABLE IF EXISTS t3;
CREATE TABLE t3 ( f3 int) ;
INSERT INTO t3 VALUES (0),(0);

--let $query = SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ;

SET SESSION join_cache_level = 0;
--eval CREATE TABLE r1 AS $query ;
SET SESSION join_cache_level = 3;
--eval CREATE TABLE r2 AS $query ;

SELECT COUNT(*) FROM r1 WHERE r IS NULL;
SELECT COUNT(*) FROM r2 WHERE r IS NULL;

[Timour Katchaounov (timour)]

Assigned to Sanja, as the bug seems related to MIN/MAX optimization.
If not, Sanja please reassign to Timour.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

join_cache_level == 0 return incorrect results (0-8 tested).

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

If reduce number of rows in table t2 and have NULL first in the table wrong result will be independent of join_cache_level:
...
INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0);
...

+SET SESSION join_cache_level = 0;
+SELECT a1.f3 AS r, a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) FROM t2 AS a1 , t1;
+r a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 )
+NULL 1
+NULL 1
+5 1
+5 1
+SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ;
+r
+5
+5
+SET SESSION join_cache_level = 3;
+SELECT a1.f3 AS r, a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) FROM t2 AS a1 , t1;
+r a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 )
+NULL 1
+5 1
+NULL 1
+5 1
+SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ;
+r
+5
+5

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

The problem is that if left argument is NULL (or could be other) comporison function do not check right argument (subquery) so subquery which was not executed return false on query of empty set, but ALL subquery with empty set should alway return TRUE independently on left part.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

The bug has no direct connection to join_cache_level and could be repeated on 5.1, here is simplified test suite (returns two 5 instead of two 5 and two NULL):

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( f11 int) ;
INSERT IGNORE INTO t1 VALUES (0),(0);

CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ;
INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0);

DROP TABLE IF EXISTS t3;
CREATE TABLE t3 ( f3 int) ;
INSERT INTO t3 VALUES (0),(0);

SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ;