Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Trivial
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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_switch = @@session.optimizer_switch;
set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off';
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_switch;
drop table t1, t2, t3;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Unsimplified test case. Let me know if this test case will be used and I will simplify it.
bug747278-2.test
LPexportBug747278_bug747278-2.test
Re: Wrong non-null result for [NOT] IN with an empty subquery as a left operand.
Unsimplified test case. Let me know if this test case will be used and I will simplify it.
Re: Wrong non-null result for [NOT] IN with an empty subquery as a left operand.
Trigger's val_int() method which should mask pushed in->exists condition to check emptiness of original query was not called.
Launchpad bug id: 747278
Re: Wrong non-null result for [NOT] IN with an empty subquery as a left operand.
A wrong result was also observed when the subquery on the left side returns rows. On i7 test file /tmp//1313094482.test