Wrong result (extra row) with semijoin=ON, materialization=OFF, optimizer_prune_level=0
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Sergey Petrunia |
Bug Description
The following query
SELECT *
FROM t1 AS alias1, t1 AS alias2
WHERE ( alias1.c, alias2.c )
IN (
SELECT alias3.a, alias3.a
FROM t2 AS alias3, t2 alias4
WHERE alias3.b = alias4.b
);
produces an extra row on test data if it's run with materialization=OFF and otherwise default optimizer_switch values, while it returns the correct result set with materialization=ON (or with semijoin=OFF, or with loosescan=OFF).
EXPLAIN with materialization=OFF (wrong result):
1 PRIMARY alias2 ALL NULL NULL NULL NULL 13 100.00
1 PRIMARY alias3 ALL a NULL NULL NULL 3 66.67 Using where; LooseScan
1 PRIMARY alias4 ALL NULL NULL NULL NULL 3 100.00 Using where; FirstMatch(alias3)
1 PRIMARY alias1 ALL NULL NULL NULL NULL 13 100.00 Using where; Using join buffer (flat, BNL join)
select `test`.`alias1`.`c` AS `c`,`test`
EXPLAIN with materialization=ON (correct result):
1 PRIMARY alias1 ALL NULL NULL NULL NULL 13 100.00
1 PRIMARY alias2 ALL NULL NULL NULL NULL 13 100.00 Using where; Using join buffer (flat, BNL join)
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 100.00
2 MATERIALIZED alias3 ALL a NULL NULL NULL 3 100.00
2 MATERIALIZED alias4 ALL NULL NULL NULL NULL 3 100.00 Using where
select `test`.`alias1`.`c` AS `c`,`test`
Minimal optimizer_switch: materialization=off
Full optimizer_switch: index_merge=
Reproducible with MyISAM and InnoDB, but not Aria.
Further reducing the test data eliminates the problem as the plan is not triggered anymore.
Test case:
SET optimizer_
SET optimizer_switch = 'materializatio
CREATE TABLE t1 ( c INT ) ENGINE=MyISAM;
INSERT INTO t1 VALUES
(0),(
(6),(
CREATE TABLE t2 ( a INT, b INT, KEY(a)) ENGINE=MyISAM;
INSERT INTO t2 VALUES (3,20),
SELECT *
FROM t1 AS alias1, t1 AS alias2
WHERE ( alias1.c, alias2.c )
IN (
SELECT alias3.a, alias3.a
FROM t2 AS alias3, t2 alias4
WHERE alias3.b = alias4.b
);
# End of test case
# Notes on result:
# The correct result is 2 rows
# c c
# 2 2
# 3 3
# The test produces 3 rows
# c c
# 2 2
# 3 3
# 3 3
bzr version-info
revision-id: <email address hidden>
date: 2011-12-06 13:42:18 -0800
build-date: 2011-12-08 00:34:55 +0300
revno: 3334
branch-nick: maria-5.3
Also reproducible on revno 3315
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Sergey Petrunia (sergefp) |
description: | updated |
description: | updated |
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → High |
Changed in maria: | |
status: | Confirmed → Fix Committed |
The EXPLAIN for wrong result mentions use of join buffer, but the bug can be observed without join buffer, too:
MariaDB [j2]> set join_cache_level=0;
MariaDB [j2]> SELECT * FROM t1 AS alias1, t1 AS alias2 WHERE ( alias1.c, alias2.c ) IN ( SELECT alias3.a, alias3.a FROM t2 AS alias3, t2 alias4 WHERE alias3.b = alias4.b );
+------+------+
| c | c |
+------+------+
| 2 | 2 |
| 3 | 3 |
| 3 | 3 |
+------+------+
3 rows in set (0.00 sec)