Wrong result with semijoin=ON, materializaiton=ON, RIGHT JOIN
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Undecided
|
Igor Babaev |
Bug Description
The following query
SELECT * FROM t1
RIGHT JOIN t2 ON a = b
WHERE b IN ( SELECT a FROM t1 )
when is run with materialization=ON and semijoin=ON (current defaults), returns NULLs instead of actual values for table t1, even although there is a match for the ON condition. When either materialization or semijoin is OFF, the result is correct.
bzr version-info
revision-id: <email address hidden>
date: 2011-12-08 04:22:38 +0400
build-date: 2011-12-08 21:55:58 +0300
revno: 3337
branch-nick: maria-5.3
EXPLAIN output with semijoin=on, materialization=on (wrong result):
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 100.00
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00
select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` semi join (`test`.`t1`) left join `test`.`t1` on((`test`.`t1`.`a` = `test`.`t2`.`b`)) where 1
EXPLAIN output with semijoin=on, materialization=off (correct result):
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary; End temporary
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` semi join (`test`.`t1`) left join `test`.`t1` on(((`test`
EXPLAIN output with semijoin=off, materialization=on (correct result):
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00
select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = `test`.`t2`.`b`)) where <expr_cache>
Minimal optimizer_switch: not required (materializatio
Full optimizer_switch: index_merge=
Test case:
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (11),(12);
CREATE TABLE t2 ( b INT );
INSERT INTO t2 VALUES (11),(12);
SELECT * FROM t1
RIGHT JOIN t2 ON a = b
WHERE b IN ( SELECT a FROM t1 );
# End of test case
# Correct (expected) result:
# a b
# 11 11
# 12 12
# Real result:
# a b
# NULL 11
# NULL 12
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Sergey Petrunia (sergefp) |
description: | updated |
Changed in maria: | |
status: | New → Fix Committed |
assignee: | Sergey Petrunia (sergefp) → Igor Babaev (igorb-seattle) |
Changed in maria: | |
status: | Fix Committed → Fix Released |
status: | Fix Released → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
Given Igor's comment in bug#901312, this one must be its duplicate.