Wrong result with derived_merge=ON, RIGHT JOIN
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Igor Babaev |
Bug Description
The following query
SELECT alias1.* FROM
( SELECT * FROM t1 ) AS alias1
RIGHT JOIN t2
ON ( b = a )
WHERE
t2.b IN ( SELECT c FROM t3 );
Returns NULLs instead of numeric column values with derived_merge=ON, and correct values with derived_merge=OFF.
EXPLAIN with derived_merge=ON:
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 5 func 1 100.00
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00
select `test`.`t1`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) left join (`test`.`t1`) on((`test`.`t1`.`a` = `test`.`t2`.`b`)) where 1
EXPLAIN with derived_merge=OFF:
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 5 func 1 100.00
1 PRIMARY <derived2> ref key0 key0 5 test.t2.b 2100.00 Using where
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00
select `alias1`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) left join (select `test`.`t1`.`a` AS `a` from `test`.`t1`) `alias1` on(((`alias1`.`a` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where 1
Minimal optimizer_switch: none needed (derived_merge=ON by default)
Full optimizer_switch: index_merge=
Test case:
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (4),(1);
CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (4),(1);
CREATE TABLE t3 (c INT);
INSERT INTO t3 VALUES (4),(1);
SELECT alias1.* FROM
( SELECT * FROM t1 ) AS alias1
RIGHT JOIN t2
ON ( b = a )
WHERE
t2.b IN ( SELECT c FROM t3 );
bzr version-info:
revision-id: <email address hidden>
date: 2011-12-06 13:42:18 -0800
build-date: 2011-12-07 20:37:21 +0300
revno: 3334
branch-nick: maria-5.3
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Igor Babaev (igorb-seattle) |
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → Critical |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
The bug can be reproduced without derived table:
MariaDB [test]> SELECT alias1.* FROM t1 AS alias1 RIGHT JOIN t2 ON ( b = a ) WHERE t2.b IN ( SELECT c FROM t3 );
+------+
| a |
+------+
| NULL |
| NULL |
+------+
2 rows in set (0.01 sec)
MariaDB [test]> SELECT * FROM t1 AS alias1 RIGHT JOIN t2 ON ( b = a ) WHERE t2.b IN ( SELECT c FROM t3);
+------+------+
| a | b |
+------+------+
| NULL | 4 |
| NULL | 1 |
+------+------+
2 rows in set (0.00 sec)