Wrong result with semijoin + "Impossible where"
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Sergey Petrunia |
Bug Description
The following query:
select * from t5 where (a) in (
SELECT t1.a FROM t1
LEFT JOIN t2 ON ( t1.a = t2.a ) WHERE t2.b NOT IN ( SELECT t4.b FROM t4 WHERE t4.b < t1.b ));
returns no rows when executed with semijoin, even though the correct result must be "8", since table t5 contains the result from the subquery.
mysql 5.5 also wrongly returns an empty result, though the plan there is different. Postgresql confirms that the correct result is "8".
explain:
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
minimal optimizer_switch: semijoin=on
full optimizer switch: index_merge=
bzr version-info:
revision-id: <email address hidden>
date: 2011-09-10 18:01:27 +0300
build-date: 2011-09-14 11:28:19 +0300
revno: 3183
branch-nick: maria-5.3
test case:
CREATE TABLE t1 ( b varchar(1), a integer) ;
INSERT INTO t1 VALUES ('z',8);
CREATE TABLE t2 ( a integer, b varchar(1)) ;
CREATE TABLE t4 ( a integer, b varchar(1)) ;
CREATE TABLE t5 ( a integer) ;
INSERT INTO t5 VALUES (8);
set session optimizer_
select * from t5 where (a) in (
SELECT t1.a FROM t1
LEFT JOIN t2 ON ( t1.a = t2.a ) WHERE t2.b NOT IN ( SELECT t4.b FROM t4 WHERE t4.b < t1.b ));
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Sergey Petrunia (sergefp) |
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → High |
Changed in maria: | |
status: | Confirmed → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
The wrong result is produced because outer join is converted into inner, which is wrong. The rewritten subquery looks like this:
Message: select 8 AS `a` from `bug849776`.`t5` semi join (`bug849776`.`t1` join `bug849776`.`t2`) where 0
the outer-to-inner conversion is made by simplify_joins(). I don't yet understand why this happens.