Wrong result with derived_merge=on + right join
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Igor Babaev |
Bug Description
Even after the fix for 806504, the following query returns no rows even though there is a row that matches the WHERE predicate.
Not repeatable with derived_merge=off. Not repeatable without a derived table. Not repeatable in mysql-5.5. Not repeatable by rotating the join to a LEFT JOIN. Not repeatable with maria-5.3 before WL#106 .
explain:
| 1 | PRIMARY | t1 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | t2 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | t1 | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
minimal optimizer_switch - derived_merge=on
full optimizer_switch:
index_merge=
bzr version-info:
revision-id: <email address hidden>
date: 2011-07-11 10:56:48 -0700
build-date: 2011-07-12 11:17:24 +0300
revno: 3100
branch-nick: maria-5.3
test case:
DROP DATABASE IF NOT EXISTS bug;
CREATE DATABASE bug;
USE bug;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int, b int) ;
INSERT IGNORE INTO t1 VALUES (6,5);
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 ( a int, f4 int) ;
INSERT IGNORE INTO t2 VALUES (1,0);
SELECT alias2.a, alias2.b FROM t1 RIGHT JOIN ( ( SELECT * FROM t1 ) AS alias2 , t2) ON t2.f4 != 0 WHERE ( alias2.a , alias2.b ) NOT IN ( SELECT 7 , 5 );
Changed in maria: | |
assignee: | nobody → Igor Babaev (igorb-seattle) |
Changed in maria: | |
milestone: | none → 5.3 |
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → High |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |