Wrong result with LEFT JOIN, constant table, semijoin=ON,materialization=ON

[Elena Stepanova]

The following query

SELECT * FROM t1 LEFT JOIN t2 ON ( a = b )
  WHERE a IN ( SELECT c FROM t2 )

returns one row with NULLs instead of t2 values if it's run with materialization=ON and semijoin=ON (current defaults), and 3 rows with actual values otherwise. The latter is correct.

In test data t1 contains 1 row and has to have MyISAM or Aria engine.

bzr version-info
revision-id: <email address hidden>
date: 2011-12-14 04:56:54 +0400
build-date: 2011-12-15 00:21:43 +0400
revno: 3349
branch-nick: maria-5.3

Not reproducible on 3250.

EXPLAIN with semijoin=ON, materialization=ON (wrong result):

1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 Using where
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 100.00 Using where

select 4 AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t2` on(((`test`.`t2`.`c` = 4) and (`test`.`t2`.`b` = 4))) where (`test`.`t2`.`c` = 4)
SELECT * FROM t1 LEFT JOIN t2 ON ( a = b )
WHERE a IN ( SELECT c FROM t2 );

EXPLAIN ith semijoin=OFF, materialization=ON (correct result):

1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 Using where
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 100.00

select 4 AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`b` = 4)) where <expr_cache><4>(<in_optimizer>(4,4 in ( <materialize> (select `test`.`t2`.`c` from `test`.`t2` ), <primary_index_lookup>(4 in <temporary table> on distinct_key where ((4 = `<subquery2>`.`c`))))))

EXPLAIN with materialization=OFF, semijoin=ON (correct result):

1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 Using where; Start temporary; End temporary
1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 Using where

select 4 AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t2` on(((`test`.`t2`.`c` = 4) and (`test`.`t2`.`b` = 4))) where (`test`.`t2`.`c` = 4)
SELECT * FROM t1 LEFT JOIN t2 ON ( a = b )

Minimal optimizer_switch: materialization=on,semijoin=on (default values)

Full optimizer_switch: 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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

Test case:

SET optimizer_switch = 'materialization=on,semijoin=on';

# t1 must be MyISAM or Aria
CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
INSERT INTO t1 VALUES (4);
CREATE TABLE t2 ( b INT NOT NULL, c INT );
INSERT INTO t2 VALUES (4,2),(4,2),(4,4),(1,1);

SELECT * FROM t1 LEFT JOIN t2 ON ( a = b )
  WHERE a IN ( SELECT c FROM t2 );

# Expected result:
# a b c
# 4 4 2
# 4 4 2
# 4 4 4
#
# Actual result:
# a b c
# 4 NULL NULL

[Elena Stepanova (elenst)]

Fix released with 5.3.3-rc.