Incorrect result of NULL <op> ALL (<empty set>)
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Oleksandr "Sanja" Byelkin |
Bug 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),(
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;
Changed in maria: | |
milestone: | none → 5.3 |
summary: |
Diverging results with join_cache_level=0, join_cache_level=3 in - maria-5.3 + maria-5.3 with ALL() subquery + NULLs |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
summary: |
- Diverging results with join_cache_level=0, join_cache_level=3 in - maria-5.3 with ALL() subquery + NULLs + Incorrect result of NULL <op> ALL (<empty set>) |
Changed in maria: | |
milestone: | 5.3 → 5.1 |
status: | Fix Committed → Confirmed |
Changed in maria: | |
milestone: | 5.1 → 5.2 |
Changed in maria: | |
status: | Confirmed → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
Assigned to Sanja, as the bug seems related to MIN/MAX optimization.
If not, Sanja please reassign to Timour.