Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.0, 5.5.28, 5.3.10
-
None
Description
The following test case
SET optimizer_switch = 'materialization=on,semijoin=on'; |
|
|
CREATE TABLE t1 ( a1 CHAR(1), b1 CHAR(1), KEY(b1,a1) ) ENGINE=Aria; |
INSERT INTO t1 VALUES ('f','c'),('d','m'),('g','y'); |
|
|
CREATE TABLE t2 ( a2 CHAR(1), b2 CHAR(1) ) ENGINE=Aria; |
INSERT INTO t2 VALUES ('y','y'),('y','y'),('w','w'); |
|
|
CREATE TABLE t3 (a3 INT) ENGINE=Aria; |
INSERT INTO t3 VALUES (8),(6); |
|
|
SELECT * FROM t1 LEFT JOIN t2 ON ( b1 = a2 ) WHERE ( b1, b1 ) IN ( SELECT a2, b2 FROM t2, t3 ); |
produces one row with NULLs instead of t1.* values:
a1 b1 a2 b2
|
------------------------------
|
g y NULL NULL
|
The expected result is 2 rows without NULLs:
a1 b1 a2 b2
|
------------------------------
|
g y y y
|
g y y y
|
branch: maria/5.3
|
revision-id: sergii@pisem.net-20121123121131-p4nfv8j2cbh68dbg
|
date: 2012-11-23 13:11:31 +0100
|
revno: 3605
|
Also reproducible on older versions of 5.3 (I checked down to 5.3.4).
Reproducible on maria/5.5, maria/10.0.
With the given test case, not reproducible on MyISAM or InnoDB, only on Aria.
Reproducible with minimal optimizer_switch materialization=on,semijoin=on as well as with the default optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
|
EXPLAIN with the minimal optimizer_switch (wrong result):
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3100.00
|
1 PRIMARY t1 ref b1 b1 2 test.t2.a2 1 100.00 Using index
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where
|
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where
|
Warnings:
|
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) left join `test`.`t2` on(((`test`.`t2`.`b2` = `test`.`t2`.`a2`) and (`test`.`t1`.`b1` = `test`.`t2`.`a2`) and (`test`.`t2`.`a2` = `test`.`t2`.`a2`))) where ((`test`.`t2`.`b2` = `test`.`t2`.`a2`) and (`test`.`t1`.`b1` = `test`.`t2`.`a2`))
|
EXPLAIN with the default optimizer_switch (also wrong result):
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3100.00
|
1 PRIMARY t1 ref b1 b1 2 test.t2.a2 1 100.00 Using index
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
|
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
|
Warnings:
|
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) left join `test`.`t2` on(((`test`.`t2`.`b2` = `test`.`t2`.`a2`) and (`test`.`t1`.`b1` = `test`.`t2`.`a2`) and (`test`.`t2`.`a2` = `test`.`t2`.`a2`))) where ((`test`.`t2`.`b2` = `test`.`t2`.`a2`) and (`test`.`t1`.`b1` = `test`.`t2`.`a2`))
|