Wrong result with RIGHT JOIN + derived_merge
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Medium
|
Sergey Petrunia |
Bug Description
The following query:
SELECT alias1.*
FROM (
SELECT t1.*
FROM t1
LEFT JOIN t2
ON ( t1.a = t2.a )
WHERE t2.a <> 0
) AS alias1
RIGHT JOIN t3
ON ( t3.a = alias1.b );
returns the following incorrect result when executed with derived_merge=on:
a b
0 g
the correct result is (NULL , NULL) as the subquery does not return any rows and therefore the RIGHT JOIN between the subquery and a table can not include any rows from the subquery.
explain:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t3 system NULL NULL NULL NULL 1 100.00
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where
select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t3` left join (`test`.`t1`) on(((`test`
minimal switch: derived_merge=on
full switch: index_merge=
bzr version-info
revision-id: <email address hidden>
date: 2011-10-28 12:38:36 +0400
build-date: 2011-10-31 13:55:00 +0200
revno: 3258
branch-nick: maria-5.3
test case:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int(11), b varchar(1)) ;
INSERT IGNORE INTO t1 VALUES (0,'g');
DROP TABLE IF EXISTS t3;
CREATE TABLE t3 ( a varchar(1)) ;
INSERT IGNORE INTO t3 VALUES ('g');
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a)) ;
SET SESSION optimizer_
SELECT alias1.*
FROM (
SELECT t1.*
FROM t1
LEFT JOIN t2
ON ( t1.a = t2.a )
WHERE t2.a <> 0
) AS alias1
RIGHT JOIN t3
ON ( t3.a = alias1.b );
Changed in maria: | |
assignee: | nobody → Igor Babaev (igorb-seattle) |
milestone: | none → 5.3 |
Changed in maria: | |
status: | New → Confirmed |
Changed in maria: | |
importance: | Undecided → Medium |
milestone: | 5.3 → 5.2 |
assignee: | Igor Babaev (igorb-seattle) → Sergey Petrunia (sergefp) |
Changed in maria: | |
status: | Confirmed → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
Changed query from RIGHT JOIN to LEFT JOIN (the bug repeats):
SELECT alias1.*
FROM
t3
LEFT JOIN
(
SELECT t1.*
FROM t1 LEFT JOIN t2 ON ( t1.a = t2.a )
WHERE t2.a <> 0
) AS alias1
ON ( t3.a = alias1.b );