Wrong result (extra rows) with loosescan and IN subquery
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Sergey Petrunia |
Bug Description
The following query
SELECT a, b, d FROM t1, t2
WHERE ( b, d ) IN
( SELECT b, d FROM t1, t2 WHERE b = c );
on the test data returns 47 rows if it's run with loosescan=ON, and 19 rows otherwise. The latter is correct.
bzr version-info
revision-id: <email address hidden>
date: 2012-01-22 12:54:30 -0800
build-date: 2012-01-23 06:15:25 +0400
revno: 3392
branch-nick: maria-5.3
Also reproducible on 5.5.
EXPLAIN with loosescan=ON (wrong result):
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00
1 PRIMARY t2 ALL c NULL NULL NULL 7 100.00 Using where; Using join buffer (flat, BNL join)
1 PRIMARY t1 ref b b 5 test.t2.c 2 100.00 Using index; LooseScan
1 PRIMARY t1 ref b b 5 test.t2.c 2 100.00
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`d` AS `d` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`d` = `test`.`t2`.`d`) and (`test`.`t1`.`b` = `test`.`t2`.`c`) and (`test`.`t1`.`b` = `test`.`t2`.`c`))
EXPLAIN with loosescan=OFF (correct result):
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00
1 PRIMARY t2 ALL c NULL NULL NULL 7 100.00 Using where; Using join buffer (flat, BNL join)
1 PRIMARY t1 ref b b 5 test.t2.c 2 100.00 Using index; Start temporary
1 PRIMARY t1 ALL b NULL NULL NULL 10 80.00 Using where; End temporary; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`d` AS `d` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`d` = `test`.`t2`.`d`) and (`test`.`t1`.`b` = `test`.`t2`.`c`) and (`test`.`t1`.`b` = `test`.`t2`.`c`))
Minimal optimizer_switch: semijoin=
Full optimizer_switch: index_merge=
Test case:
SET optimizer_switch = 'semijoin=
CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
INSERT INTO t1 VALUES
(1,2)
(6,3)
CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
INSERT INTO t2 VALUES
(1,2)
SELECT a, b, d FROM t1, t2
WHERE ( b, d ) IN
( SELECT b, d FROM t1, t2 WHERE b = c );
tags: | removed: petrunia |
Changed in maria: | |
assignee: | nobody → Sergey Petrunia (sergefp) |
Changed in maria: | |
importance: | Undecided → Critical |
Changed in maria: | |
status: | New → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |