Wrong non-null result for [NOT] IN with an empty subquery as a left operand.
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Low
|
Oleksandr "Sanja" Byelkin |
Bug Description
The following queries produce an incorrect non-NULL result when
the left operand of [NOT] IN is a subquery with an empty result set.
Test case:
CREATE TABLE t1 (f1a int, f1b int) ;
INSERT IGNORE INTO t1 VALUES (1,1),(2,2);
CREATE TABLE t2 ( f2 int);
INSERT IGNORE INTO t2 VALUES (3),(4);
CREATE TABLE t3 (f3a int, f3b int);
INSERT INTO t3 VALUES (1,1),(2,2);
set @old_optimizer_
set @@optimizer_
SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2;
SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2;
SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);
SET @@optimizer_switch = @old_optimizer_
drop table t1, t2, t3;
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → High |
importance: | High → Medium |
assignee: | nobody → Timour Katchaounov (timour) |
milestone: | none → 5.3 |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Confirmed |
Changed in maria: | |
importance: | Medium → Low |
Changed in maria: | |
assignee: | Timour Katchaounov (timour) → Oleksandr "Sanja" Byelkin (sanja-byelkin) |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
A wrong result was also observed when the subquery on the left side returns rows. On i7 test file /tmp//131309448 2.test