Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Diverging results with join_cache_level=0, join_cache_level=3 in maria-5.3 with ALL() subquery + NULLs
join_cache_level == 0 return incorrect results (0-8 tested).
Re: Diverging results with join_cache_level=0, join_cache_level=3 in maria-5.3 with ALL() subquery + NULLs
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
Re: Diverging results with join_cache_level=0, join_cache_level=3 in maria-5.3 with ALL() subquery + NULLs
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.
Re: Diverging results with join_cache_level=0, join_cache_level=3 in maria-5.3 with ALL() subquery + NULLs
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 ) ;
Launchpad bug id: 780386
Re: Diverging results with join_cache_level=0, join_cache_level=3 in maria-5.3 with ALL() subquery + NULLs
Assigned to Sanja, as the bug seems related to MIN/MAX optimization.
If not, Sanja please reassign to Timour.