Wrong result with semijoin=off,derived_merge=on , right join and subquery
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Igor Babaev |
Bug Description
The following query
SELECT *
FROM t2
RIGHT JOIN ( SELECT * FROM t1 ) AS alias2
ON alias2.f10 != 0
WHERE alias2.f10 IN ( SELECT f11 FROM t1 );
returns no rows when executed with derived_
Explain:
| 1 | PRIMARY | t1 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 3 | SUBQUERY | t1 | system | NULL | NULL | NULL | NULL | 1 | |
minimal optimizer switch:
full optimizer switch:
index_merge=
test case:
CREATE TABLE t1 ( f10 int, f11 int) ;
INSERT IGNORE INTO t1 VALUES (0,0);
CREATE TABLE t2 ( f11 int) ;
INSERT INTO t2 VALUES (0),(0);
SELECT *
FROM t2
RIGHT JOIN ( SELECT * FROM t1 ) AS alias2
ON alias2.f10 != 0
WHERE alias2.f10 IN ( SELECT f11 FROM t1 );
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Igor Babaev (igorb-seattle) |
description: | updated |
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → High |
Changed in maria: | |
status: | Confirmed → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |