MySQL Lists: commits: bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3253) Bug#45174 Bug#50019

List:	Commits	« Previous Message Next Message »
From:	Roy Lyseng	Date:	September 29 2010 2:34pm
Subject:	bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3253) Bug#45174 Bug#50019
View as plain text
#At file:///home/rl136806/mysql/repo/mysql-work5/ based on revid:tor.didriksen@stripped

 3253 Roy Lyseng	2010-09-29
      Bug#45174: Incorrectly applied equality propagation caused wrong result
      on a query with a materialized semi-join.
      Bug#50019: Wrong result for IN-query with materialization.
      
      When a subquery is subject to a semijoin optimization, it's tables
      are merged to the outer query and later are treated as regular tables. 
      This allows a bunch of optimizations to be applied, equality
      propagation is among them. Equality propagation is done after query
      execution plan is chosen. It substitutes fields from tables being
      retrieved later for fields from tables being retrieved earlier.
      However, it can't be applied as is to any semijoin table.
      The semijoin materialization strategy differs from other semijoin
      strategies that the data from materialized semijoin tables isn't used
      directly but saved to a temporary table first.
      The materialization isn't isolated in a separate step, it is done
      inline within the nested loop execution.
      When it comes to fetching rows from the first table in the block of
      materialized semijoin tables, sub_select() function is called to
      materialize the result of the subquery and save it in the
      materialized table. Later, data from the materialized table is used
      as they were regular table rows.
      Due to this we can't substitute fields that belong to the semi-join
      for fields from outer query and vice versa. 
      
      Example: suppose we have a join order:
      
        ot1 ot2  SJ-Mat(it1  it2  it3)  ot3
      
      and equality ot2.col = it1.col = it2.col
      If we're looking for best substitute for 'it2.col', we should pick
      it1.col and not ot2.col.
      
      For a field that is not in a materialized semijoin we must pick a field
      that's not embedded in any materialized semijoin.
      
      Example: suppose we have a join order:
      
        SJ-Mat(it1  it2)  ot1  ot2
      
      and equality ot2.col = ot1.col = it2.col
      If we're looking for best substitute for 'ot2.col', we should pick
      ot1.col and not it2.col, because when we run a join between ot1
      and ot2, execution of SJ-Mat(...) has already finished and we can't
      rely on the value of it*.*.
      
      Now we have added another Item_equal::get_first function that accepts
      as a parameter a field being substituted and checks whether it belongs
      to a materialized semijoin.
      The field to substitute will be from the same materialized semijoin nest
      (if supplied field is within such nest), or outside any materialized
      semijoin nest (if supplied field is outside such nest).
      
      The new checks rely on the first_sj_inner_tab and first_sj_inner_tab
      fields of the join-tab. These fields are therefore set as soon as
      possible after the join strategy is fixed.
      
      mysql-test/include/subquery_sj.inc
        Bug#45174: Incorrectly applied equality propagation caused wrong result
        on a query with a materialized semi-join.
        Bug#50019: Wrong result for IN-query with materialization.
        Added new tests.
      
      mysql-test/r/subquery_mat_all.result
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
        Bug#45174: Incorrectly applied equality propagation caused wrong result
        on a query with a materialized semi-join.
        Bug#50019: Wrong result for IN-query with materialization.
        Results for two new tests added.
        Some tests using semijoin materialization show that where clause
        has moved from the outer query into the materialized inner query.
        This is caused by the changed call to get_first() in
        eliminate_item_equal().
        Ex: select * from ot where a in(select b from it where b>0);
        The clause "b>0" is now evaluated on the inner query materialization.
        Performance-wise this is never worse when using MaterializeScan and
        usually better for MaterializeLookup. For the latter strategy, the
        best possible solution is probably to evaluate the clause in both
        queries, this can be subject for a later feature development.
       
      sql/item.cc
        Bug#45174: Incorrectly applied equality propagation caused wrong result
        on a query with a materialized semi-join.
      
        Calling new get_first() function instead of old.
      
      sql/item_cmpfunc.cc
        Bug#45174: Incorrectly applied equality propagation caused wrong result
        on a query with a materialized semi-join.
      
        New function Item_equal::get_first that accepts as parameter
        a field being substituted.
      
      sql/item_cmpfunc.h
        Bug#45174: Incorrectly applied equality propagation caused wrong result
        on a query with a materialized semi-join.
      
        New function Item_equal::get_first that accepts as parameter
        a field being substituted.
      
      sql/sql_select.cc
        Bug#45174: Incorrectly applied equality propagation caused wrong result
        on a query with a materialized semi-join.
      
        Setting fields first_sj_inner_tab and last_sj_inner_tab moved from
        setup_semijoin_dups_elimination() to get_best_combination(), so they
        are set as early as possible after join order optimization.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_mat_all.result
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
      sql/item.cc
      sql/item_cmpfunc.cc
      sql/item_cmpfunc.h
      sql/sql_select.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2010-09-20 14:06:02 +0000
+++ b/mysql-test/include/subquery_sj.inc	2010-09-29 14:33:39 +0000
@@ -3326,3 +3326,64 @@ eval explain $query;
 eval $query;
 
 DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # Bug#45174: Incorrectly applied equality propagation caused wrong
+--echo # result on a query with a materialized semi-join.
+--echo #
+
+CREATE TABLE t1 (
+  varchar_nokey varchar(1) NOT NULL
+);
+
+INSERT INTO t1 VALUES
+ ('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+ ('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+
+CREATE TABLE t2 (
+  pk int NOT NULL,
+  varchar_key varchar(1) NOT NULL,
+  varchar_nokey varchar(1) NOT NULL,
+  PRIMARY KEY(pk),
+  KEY varchar_key(varchar_key)
+);
+
+INSERT INTO t2 VALUES
+ (11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+ (16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+ (23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+
+let $query=
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+                                         FROM t2  
+                                         WHERE varchar_nokey < 'n' XOR pk);
+eval $query;
+eval explain $query;
+
+DROP TABLE t1, t2;
+
+--echo # End of the test for bug#45174.
+
+--echo #
+--echo # Bug#50019: Wrong result for IN-query with materialization
+--echo #
+
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+
+let $query=
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+                                FROM t2 JOIN t3
+                                WHERE t2.i + t3.i = 5);
+eval $query;
+eval explain $query;
+
+DROP TABLE t1,t2,t3;
+
+--echo # End of the test for bug#50019.

=== modified file 'mysql-test/r/subquery_mat_all.result'
--- a/mysql-test/r/subquery_mat_all.result	2010-09-08 14:39:38 +0000
+++ b/mysql-test/r/subquery_mat_all.result	2010-09-29 14:33:39 +0000
@@ -262,12 +262,12 @@ where (a1, a2) in (select b1, b2 from t2
 (a1, a2) in (select c1, c2 from t3i
 where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1i	index	it1i1,it1i2,it1i3	it1i3	18	NULL	3	100.00	Using where; Using index
-1	PRIMARY	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	100.00	Using index; Materialize
-1	PRIMARY	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	100.00	Using index; Start materialize
+1	PRIMARY	t1i	index	it1i1,it1i2,it1i3	it1i3	18	NULL	3	100.00	Using index
+1	PRIMARY	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	100.00	Using where; Using index; Materialize
+1	PRIMARY	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	100.00	Using where; Using index; Start materialize
 1	PRIMARY	t3i	index	it3i1,it3i2,it3i3	it3i3	18	NULL	4	75.00	Using where; Using index; End materialize
 Warnings:
-Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t1i`.`a1` > '0') and (`test`.`t1i`.`a2` > '0'))
+Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`test`.`t2i`.`b2` > '0'))
 select * from t1i
 where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
 (a1, a2) in (select c1, c2 from t3i
@@ -345,13 +345,13 @@ id	select_type	table	type	possible_keys	
 2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using temporary; Using filesort
 4	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
-7	UNION	t1i	index	it1i1,it1i2,it1i3	it1i3	18	NULL	3	100.00	Using where; Using index
-7	UNION	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	100.00	Using index; Materialize
-7	UNION	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	100.00	Using index; Start materialize
+7	UNION	t1i	index	it1i1,it1i2,it1i3	it1i3	18	NULL	3	100.00	Using index
+7	UNION	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	100.00	Using where; Using index; Materialize
+7	UNION	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	100.00	Using where; Using index; Start materialize
 7	UNION	t3i	index	it3i1,it3i2,it3i3	it3i3	18	NULL	4	75.00	Using where; Using index; End materialize
 NULL	UNION RESULT	<union1,7>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_
 key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`))))) and (`test`.`t1`.`a2` > '0'))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t1i`.`a1` > '0') and (`test`.`t1i`.`a2` > '0')))
+Note	1003	(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_
 key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`))))) and (`test`.`t1`.`a2` > '0'))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`test`.`t2i`.`b2` > '0')))
 (select * from t1
 where (a1, a2) in (select b1, b2 from t2
 where b2 in (select c2 from t3 where c2 LIKE '%02') or
@@ -397,7 +397,7 @@ a1 = c1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (BNL, regular buffers)
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Start materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Start materialize
 1	PRIMARY	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; End materialize
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2010-09-29 14:33:39 +0000
@@ -2277,7 +2277,7 @@ insert into t3 select A.a + 10*B.a, 'fil
 explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	Using index condition
 select * from t3 where a in (select a from t2);
 a	filler
 1	filler
@@ -2323,7 +2323,7 @@ explain select * from t1, t3 where t3.a 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using index condition
 explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
@@ -2352,13 +2352,13 @@ explain select * from t0, t3 where t3.a 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using index condition
 create table t4 as select a as x, a as y from t1;
 explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t4.x	10	Using where
+1	PRIMARY	t3	ref	a	a	5	test.t4.x	10	Using index condition; Using where
 drop table t0,t1,t2,t3,t4;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -5148,4 +5148,67 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	
+1	PRIMARY	t2	ALL	varchar_key	NULL	NULL	NULL	15	Using where; Materialize
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Start materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End materialize
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result	2010-09-29 14:33:39 +0000
@@ -2281,7 +2281,7 @@ insert into t3 select A.a + 10*B.a, 'fil
 explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	Using join buffer (BKA, incremental buffers)
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	Using index condition; Using join buffer (BKA, incremental buffers)
 select * from t3 where a in (select a from t2);
 a	filler
 1	filler
@@ -2327,7 +2327,7 @@ explain select * from t1, t3 where t3.a 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using join buffer (BKA, incremental buffers)
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using index condition; Using join buffer (BKA, incremental buffers)
 explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
@@ -2356,13 +2356,13 @@ explain select * from t0, t3 where t3.a 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using join buffer (BKA, incremental buffers)
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using index condition; Using join buffer (BKA, incremental buffers)
 create table t4 as select a as x, a as y from t1;
 explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t4.x	10	Using where; Using join buffer (BKA, incremental buffers)
+1	PRIMARY	t3	ref	a	a	5	test.t4.x	10	Using index condition; Using where; Using join buffer (BKA, incremental buffers)
 drop table t0,t1,t2,t3,t4;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -5152,6 +5152,69 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	
+1	PRIMARY	t2	ALL	varchar_key	NULL	NULL	NULL	15	Using where; Materialize
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Start materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End materialize
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result	2010-09-29 14:33:39 +0000
@@ -2281,7 +2281,7 @@ insert into t3 select A.a + 10*B.a, 'fil
 explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	Using join buffer (BKA_UNIQUE, regular buffers)
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	Using index condition; Using join buffer (BKA_UNIQUE, regular buffers)
 select * from t3 where a in (select a from t2);
 a	filler
 1	filler
@@ -2327,7 +2327,7 @@ explain select * from t1, t3 where t3.a 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using join buffer (BKA_UNIQUE, regular buffers)
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using index condition; Using join buffer (BKA_UNIQUE, regular buffers)
 explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
@@ -2356,13 +2356,13 @@ explain select * from t0, t3 where t3.a 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using join buffer (BKA_UNIQUE, regular buffers)
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using index condition; Using join buffer (BKA_UNIQUE, regular buffers)
 create table t4 as select a as x, a as y from t1;
 explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t4.x	10	Using where; Using join buffer (BKA_UNIQUE, regular buffers)
+1	PRIMARY	t3	ref	a	a	5	test.t4.x	10	Using index condition; Using where; Using join buffer (BKA_UNIQUE, regular buffers)
 drop table t0,t1,t2,t3,t4;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -5152,6 +5152,69 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	
+1	PRIMARY	t2	ALL	varchar_key	NULL	NULL	NULL	15	Using where; Materialize
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Start materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End materialize
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2010-09-29 14:33:39 +0000
@@ -5146,4 +5146,67 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	
+1	PRIMARY	t2	ref	varchar_key	varchar_key	3	test.t1.varchar_nokey	2	Using where; Start temporary; End temporary
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+4
+3
+2
+1
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-09-29 14:33:39 +0000
@@ -5150,6 +5150,69 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	Start temporary
+1	PRIMARY	t2	ref	varchar_key	varchar_key	3	test.t1.varchar_nokey	2	Using where; End temporary
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+4
+3
+2
+1
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (BNL, incremental buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-09-29 14:33:39 +0000
@@ -5150,6 +5150,69 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	Start temporary
+1	PRIMARY	t2	ref	varchar_key	varchar_key	3	test.t1.varchar_nokey	2	Using where; End temporary
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+4
+3
+2
+1
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2010-09-29 14:33:39 +0000
@@ -5148,6 +5148,69 @@ a
 1
 DROP TABLE t1,t2,t3;
 #
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	
+1	PRIMARY	t2	ref	varchar_key	varchar_key	3	test.t1.varchar_nokey	2	Using where; FirstMatch(t1)
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; FirstMatch(t1)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2010-09-29 14:33:39 +0000
@@ -5152,6 +5152,69 @@ a
 1
 DROP TABLE t1,t2,t3;
 #
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	
+1	PRIMARY	t2	ref	varchar_key	varchar_key	3	test.t1.varchar_nokey	2	Using where; FirstMatch(t1)
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; FirstMatch(t1)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-09-29 14:33:39 +0000
@@ -5152,6 +5152,69 @@ a
 1
 DROP TABLE t1,t2,t3;
 #
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	
+1	PRIMARY	t2	ref	varchar_key	varchar_key	3	test.t1.varchar_nokey	2	Using where; FirstMatch(t1)
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; FirstMatch(t1)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2010-09-29 14:33:39 +0000
@@ -5151,4 +5151,67 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	
+1	PRIMARY	t2	ref	varchar_key	varchar_key	3	test.t1.varchar_nokey	2	Using where; Start temporary; End temporary
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+4
+3
+2
+1
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-09-29 14:33:39 +0000
@@ -5155,6 +5155,69 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	Start temporary
+1	PRIMARY	t2	ref	varchar_key	varchar_key	3	test.t1.varchar_nokey	2	Using where; End temporary
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+4
+3
+2
+1
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (BNL, incremental buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-09-29 14:33:39 +0000
@@ -5155,6 +5155,69 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	Start temporary
+1	PRIMARY	t2	ref	varchar_key	varchar_key	3	test.t1.varchar_nokey	2	Using where; End temporary
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+4
+3
+2
+1
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2010-09-29 14:33:39 +0000
@@ -2176,7 +2176,7 @@ create table t3 (a int);
 insert into t3 select A.a + 10*B.a from t0 A, t0 B;
 explain select * from t3 where a in (select kp1 from t1 where kp1<20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	
 1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index; Materialize
 select * from t3 where a in (select kp1 from t1 where kp1<20);
 a
@@ -2205,7 +2205,7 @@ insert into t4 select a from t3;
 explain select * from t3 where a in 
 (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	
 1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Start materialize
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.c	1	Using index; End materialize
 select * from t3 where a in 
@@ -2277,7 +2277,7 @@ insert into t3 select A.a + 10*B.a, 'fil
 explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	Using where
 select * from t3 where a in (select a from t2);
 a	filler
 1	filler
@@ -2323,7 +2323,7 @@ explain select * from t1, t3 where t3.a 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using where
 explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
@@ -2352,7 +2352,7 @@ explain select * from t0, t3 where t3.a 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using where
 create table t4 as select a as x, a as y from t1;
 explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -2619,10 +2619,10 @@ explain extended select left(a1,7), left
 from t1_16
 where a1 in (select b1 from t2_16 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_16	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t2_16`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select b1 from t2_16 where b1 > '0');
@@ -2634,10 +2634,10 @@ explain extended select left(a1,7), left
 from t1_16
 where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_16	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t2_16`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_16
 where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
@@ -2749,10 +2749,10 @@ explain extended select left(a1,7), left
 from t1_512
 where a1 in (select b1 from t2_512 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_512	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t2_512`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select b1 from t2_512 where b1 > '0');
@@ -2764,10 +2764,10 @@ explain extended select left(a1,7), left
 from t1_512
 where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_512	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b2` = `test`.`t1_512`.`a2`) and (`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b2` = `test`.`t1_512`.`a2`) and (`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t2_512`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_512
 where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
@@ -2844,10 +2844,10 @@ explain extended select left(a1,7), left
 from t1_513
 where a1 in (select b1 from t2_513 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_513	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t1_513`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t2_513`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_513
 where a1 in (select b1 from t2_513 where b1 > '0');
@@ -2859,10 +2859,10 @@ explain extended select left(a1,7), left
 from t1_513
 where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_513	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b2` = `test`.`t1_513`.`a2`) and (`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t1_513`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b2` = `test`.`t1_513`.`a2`) and (`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t2_513`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_513
 where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
@@ -2926,10 +2926,10 @@ explain extended select left(a1,7), left
 from t1_1024
 where a1 in (select b1 from t2_1024 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t2_1024`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select b1 from t2_1024 where b1 > '0');
@@ -2941,10 +2941,10 @@ explain extended select left(a1,7), left
 from t1_1024
 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2`) and (`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2`) and (`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t2_1024`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_1024
 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
@@ -3021,10 +3021,10 @@ explain extended select left(a1,7), left
 from t1_1025
 where a1 in (select b1 from t2_1025 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t2_1025`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select b1 from t2_1025 where b1 > '0');
@@ -3036,10 +3036,10 @@ explain extended select left(a1,7), left
 from t1_1025
 where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2`) and (`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2`) and (`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t2_1025`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_1025
 where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
@@ -5158,4 +5158,67 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	
+1	PRIMARY	t2	ALL	varchar_key	NULL	NULL	NULL	15	Using where; Materialize
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Start materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End materialize
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result	2010-09-29 14:33:39 +0000
@@ -2180,7 +2180,7 @@ create table t3 (a int);
 insert into t3 select A.a + 10*B.a from t0 A, t0 B;
 explain select * from t3 where a in (select kp1 from t1 where kp1<20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	
 1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index; Materialize
 select * from t3 where a in (select kp1 from t1 where kp1<20);
 a
@@ -2209,7 +2209,7 @@ insert into t4 select a from t3;
 explain select * from t3 where a in 
 (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	
 1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Start materialize
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.c	1	Using index; End materialize
 select * from t3 where a in 
@@ -2281,7 +2281,7 @@ insert into t3 select A.a + 10*B.a, 'fil
 explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	Using where
 select * from t3 where a in (select a from t2);
 a	filler
 1	filler
@@ -2327,7 +2327,7 @@ explain select * from t1, t3 where t3.a 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using where
 explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
@@ -2356,7 +2356,7 @@ explain select * from t0, t3 where t3.a 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using where
 create table t4 as select a as x, a as y from t1;
 explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -2623,10 +2623,10 @@ explain extended select left(a1,7), left
 from t1_16
 where a1 in (select b1 from t2_16 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_16	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t2_16`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select b1 from t2_16 where b1 > '0');
@@ -2638,10 +2638,10 @@ explain extended select left(a1,7), left
 from t1_16
 where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_16	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t2_16`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_16
 where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
@@ -2753,10 +2753,10 @@ explain extended select left(a1,7), left
 from t1_512
 where a1 in (select b1 from t2_512 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_512	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t2_512`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select b1 from t2_512 where b1 > '0');
@@ -2768,10 +2768,10 @@ explain extended select left(a1,7), left
 from t1_512
 where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_512	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b2` = `test`.`t1_512`.`a2`) and (`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b2` = `test`.`t1_512`.`a2`) and (`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t2_512`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_512
 where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
@@ -2848,10 +2848,10 @@ explain extended select left(a1,7), left
 from t1_513
 where a1 in (select b1 from t2_513 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_513	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t1_513`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t2_513`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_513
 where a1 in (select b1 from t2_513 where b1 > '0');
@@ -2863,10 +2863,10 @@ explain extended select left(a1,7), left
 from t1_513
 where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_513	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b2` = `test`.`t1_513`.`a2`) and (`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t1_513`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b2` = `test`.`t1_513`.`a2`) and (`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t2_513`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_513
 where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
@@ -2930,10 +2930,10 @@ explain extended select left(a1,7), left
 from t1_1024
 where a1 in (select b1 from t2_1024 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t2_1024`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select b1 from t2_1024 where b1 > '0');
@@ -2945,10 +2945,10 @@ explain extended select left(a1,7), left
 from t1_1024
 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2`) and (`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2`) and (`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t2_1024`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_1024
 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
@@ -3025,10 +3025,10 @@ explain extended select left(a1,7), left
 from t1_1025
 where a1 in (select b1 from t2_1025 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t2_1025`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select b1 from t2_1025 where b1 > '0');
@@ -3040,10 +3040,10 @@ explain extended select left(a1,7), left
 from t1_1025
 where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2`) and (`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2`) and (`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t2_1025`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_1025
 where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
@@ -5162,6 +5162,69 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	
+1	PRIMARY	t2	ALL	varchar_key	NULL	NULL	NULL	15	Using where; Materialize
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Start materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End materialize
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result	2010-09-29 14:33:39 +0000
@@ -2180,7 +2180,7 @@ create table t3 (a int);
 insert into t3 select A.a + 10*B.a from t0 A, t0 B;
 explain select * from t3 where a in (select kp1 from t1 where kp1<20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	
 1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index; Materialize
 select * from t3 where a in (select kp1 from t1 where kp1<20);
 a
@@ -2209,7 +2209,7 @@ insert into t4 select a from t3;
 explain select * from t3 where a in 
 (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	
 1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Start materialize
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.c	1	Using index; End materialize
 select * from t3 where a in 
@@ -2281,7 +2281,7 @@ insert into t3 select A.a + 10*B.a, 'fil
 explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	Using where
 select * from t3 where a in (select a from t2);
 a	filler
 1	filler
@@ -2327,7 +2327,7 @@ explain select * from t1, t3 where t3.a 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using where
 explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
@@ -2356,7 +2356,7 @@ explain select * from t0, t3 where t3.a 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using where
 create table t4 as select a as x, a as y from t1;
 explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -2623,10 +2623,10 @@ explain extended select left(a1,7), left
 from t1_16
 where a1 in (select b1 from t2_16 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_16	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t2_16`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select b1 from t2_16 where b1 > '0');
@@ -2638,10 +2638,10 @@ explain extended select left(a1,7), left
 from t1_16
 where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_16	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t2_16`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_16
 where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
@@ -2753,10 +2753,10 @@ explain extended select left(a1,7), left
 from t1_512
 where a1 in (select b1 from t2_512 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_512	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t2_512`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select b1 from t2_512 where b1 > '0');
@@ -2768,10 +2768,10 @@ explain extended select left(a1,7), left
 from t1_512
 where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_512	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b2` = `test`.`t1_512`.`a2`) and (`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b2` = `test`.`t1_512`.`a2`) and (`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t2_512`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_512
 where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
@@ -2848,10 +2848,10 @@ explain extended select left(a1,7), left
 from t1_513
 where a1 in (select b1 from t2_513 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_513	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t1_513`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t2_513`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_513
 where a1 in (select b1 from t2_513 where b1 > '0');
@@ -2863,10 +2863,10 @@ explain extended select left(a1,7), left
 from t1_513
 where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_513	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_513	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b2` = `test`.`t1_513`.`a2`) and (`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t1_513`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b2` = `test`.`t1_513`.`a2`) and (`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t2_513`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_513
 where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
@@ -2930,10 +2930,10 @@ explain extended select left(a1,7), left
 from t1_1024
 where a1 in (select b1 from t2_1024 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t2_1024`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select b1 from t2_1024 where b1 > '0');
@@ -2945,10 +2945,10 @@ explain extended select left(a1,7), left
 from t1_1024
 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2`) and (`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2`) and (`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t2_1024`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_1024
 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
@@ -3025,10 +3025,10 @@ explain extended select left(a1,7), left
 from t1_1025
 where a1 in (select b1 from t2_1025 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t2_1025`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select b1 from t2_1025 where b1 > '0');
@@ -3040,10 +3040,10 @@ explain extended select left(a1,7), left
 from t1_1025
 where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Materialize; Scan
+1	PRIMARY	t1_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Materialize; Scan
 Warnings:
-Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2`) and (`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
+Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2`) and (`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t2_1025`.`b1` > '0'))
 select left(a1,7), left(a2,7)
 from t1_1025
 where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
@@ -5162,6 +5162,69 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	
+1	PRIMARY	t2	ALL	varchar_key	NULL	NULL	NULL	15	Using where; Materialize
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Start materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End materialize
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2010-09-29 14:33:39 +0000
@@ -5369,4 +5369,67 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	15	Using where
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	10	
+2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2010-09-29 14:33:39 +0000
@@ -5295,4 +5295,67 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	varchar_key	varchar_key	3	func	2	Using where
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result	2010-09-29 14:33:39 +0000
@@ -5299,6 +5299,69 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	varchar_key	varchar_key	3	func	2	Using where
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, incremental buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result	2010-09-29 14:33:39 +0000
@@ -5299,6 +5299,69 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2  
+WHERE varchar_nokey < 'n' XOR pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	18	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	varchar_key	varchar_key	3	func	2	Using where
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2010-09-07 19:07:18 +0000
+++ b/sql/item.cc	2010-09-29 14:33:39 +0000
@@ -5021,8 +5021,8 @@ Item *Item_field::replace_equal_field(uc
         return this;
       return const_item;
     }
-    Item_field *subst= item_equal->get_first();
-    if (subst && field->table != subst->field->table && !field->eq(subst->field))
+    Item_field *subst= item_equal->get_first(this);
+    if (field->table != subst->field->table && !field->eq(subst->field))
       return subst;
   }
   return this;

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2010-08-19 12:54:22 +0000
+++ b/sql/item_cmpfunc.cc	2010-09-29 14:33:39 +0000
@@ -5736,3 +5736,106 @@ void Item_equal::print(String *str, enum
   str->append(')');
 }
 
+
+/*
+  @brief Get the first field of multiple equality.
+
+  @retval First field in the multiple equality.
+*/
+
+Item_field* Item_equal::get_first()
+{
+  return fields.head();
+}
+
+/*
+  @brief Get the first equal field of multiple equality.
+
+  @param[in] field  the field to get equal field to, which must be
+                    present within the multiple equality itself.
+
+  @retval Found first field in the multiple equality.
+
+  @details Get the first field of multiple equality that is equal to the
+  given field. In order to make semijoin materialization strategy work
+  correctly we can't propagate equal fields between a materialized semijoin
+  and the outer query (or any other semijoin).
+  Thus the field is returned according to following rules:
+
+  1) If the given field belongs to a materialized semijoin then the
+     first field in multiple equality which belong to the same semijoin
+     is returned.
+  2) If the given field doesn't belong to a materialized semijoin then
+     the first field in the multiple equality that doesn't belong to a
+     materialized semijoin is returned.
+*/
+
+Item_field* Item_equal::get_first(Item_field *field)
+{
+  List_iterator<Item_field> it(fields);
+  Item_field *item;
+  JOIN_TAB *field_tab;
+
+  DBUG_ASSERT(field != NULL);
+  /*
+    Of all equal fields, return the first one we can use. Normally, this is the
+    field which belongs to the table that is the first in the join order.
+
+    There is one exception to this: When semijoin materialization strategy is
+    used, and the given field belongs to a table within the semijoin nest, we
+    must pick the first field in the semijoin nest.
+
+    Example: suppose we have a join order:
+
+       ot1 ot2  SJ-Mat(it1  it2  it3)  ot3
+
+    and equality ot2.col = it1.col = it2.col
+    If we're looking for best substitute for 'it2.col', we should pick it1.col
+    and not ot2.col.
+  */
+
+  field_tab= field->field->table->reginfo.join_tab;
+  if (sj_is_materialize_strategy(field_tab->get_sj_strategy()))
+  {
+    /*
+      It's a field from a materialized semijoin. We can substitute it only
+      with a field from the same semijoin.
+    */
+    JOIN_TAB *first= field_tab->first_sj_inner_tab;
+    JOIN_TAB *last=  field_tab->last_sj_inner_tab;
+
+    while ((item= it++))
+    {
+      if (item->field->table->reginfo.join_tab >= first &&
+          item->field->table->reginfo.join_tab <= last)
+      {
+        return item;
+      }
+    }
+  }
+  else
+  {
+    /*
+      The field is not in a materialized semijoin nest. We must return
+      the first field that's not embedded in a materialized semijoin nest.
+      Example: suppose we have a join order:
+
+          SJ-Mat(it1  it2)  ot1  ot2
+
+      and equality ot2.col = ot1.col = it2.col
+      If we're looking for best substitute for 'ot2.col', we should pick ot1.col
+      and not it2.col, because when we run a join between ot1 and ot2
+      execution of SJ-Mat(...) has already finished and we can't rely on the
+      value of it*.*.
+    */
+    while ((item= it++))
+    {
+      field_tab= item->field->table->reginfo.join_tab;
+      if (!sj_is_materialize_strategy(field_tab->get_sj_strategy()))
+        return item;
+    }
+  }
+  // Shouldn't get here.
+  DBUG_ASSERT(0);
+  return NULL;
+}

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2010-08-12 00:26:10 +0000
+++ b/sql/item_cmpfunc.h	2010-09-29 14:33:39 +0000
@@ -1634,7 +1634,8 @@ public:
   void add(Item_field *f);
   uint members();
   bool contains(Field *field);
-  Item_field* get_first() { return fields.head(); }
+  Item_field* get_first();
+  Item_field* get_first(Item_field *field);
   void merge(Item_equal *item);
   void update_const();
   enum Functype functype() const { return MULT_EQUAL_FUNC; }

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-09-28 12:35:50 +0000
+++ b/sql/sql_select.cc	2010-09-29 14:33:39 +0000
@@ -1414,13 +1414,16 @@ bool might_do_join_buffering(uint join_c
   setup_sj_materialization() (todo: can't we move that to here also?)
 */
 
-int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
-                                    uint no_jbuf_after)
+bool setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
+                                     uint no_jbuf_after)
 {
   uint tableno;
   THD *thd= join->thd;
   DBUG_ENTER("setup_semijoin_dups_elimination");
 
+  if (join->select_lex->sj_nests.is_empty())
+    DBUG_RETURN(FALSE);
+
   for (tableno= join->const_tables ; tableno < join->tables; )
   {
     JOIN_TAB *tab=join->join_tab + tableno;
@@ -1661,21 +1664,6 @@ int setup_semijoin_dups_elimination(JOIN
         break;
       }
     }
-    /*
-      Remember the first and last semijoin inner tables; this serves to tell
-      a JOIN_TAB's semijoin strategy (like in check_join_cache_usage()).
-    */
-    JOIN_TAB *last_sj_inner=
-      (pos->sj_strategy == SJ_OPT_DUPS_WEEDOUT) ?
-      /* Range may end with non-inner table so cannot set last_sj_inner_tab */
-      NULL : last_sj_tab;
-    for (JOIN_TAB *tab_in_range= tab; 
-         tab_in_range <= last_sj_tab; 
-         tab_in_range++)
-    {
-      tab_in_range->first_sj_inner_tab= tab;
-      tab_in_range->last_sj_inner_tab=  last_sj_inner;
-    }
   }
   DBUG_RETURN(FALSE);
 }
@@ -4239,14 +4227,18 @@ bool find_eq_ref_candidate(TABLE *table,
     using Materialization or LooseScan to execute it. 
 
   RETURN 
-    0 - OK
-    1 - Out of memory error
+    FALSE - OK
+    TRUE  - Out of memory error
 */
 
-int pull_out_semijoin_tables(JOIN *join)
+bool pull_out_semijoin_tables(JOIN *join)
 {
   TABLE_LIST *sj_nest;
   DBUG_ENTER("pull_out_semijoin_tables");
+
+  if (join->select_lex->sj_nests.is_empty())
+    DBUG_RETURN(FALSE);
+
   List_iterator<TABLE_LIST> sj_list_it(join->select_lex->sj_nests);
    
   /* Try pulling out of the each of the semi-joins */
@@ -4350,7 +4342,7 @@ int pull_out_semijoin_tables(JOIN *join)
         join->thd->restore_active_arena(arena, &backup);
     }
   }
-  DBUG_RETURN(0);
+  DBUG_RETURN(FALSE);
 }
 
 
@@ -8406,15 +8398,18 @@ prev_record_reads(JOIN *join, uint idx, 
 
 static bool fix_semijoin_strategies_for_picked_join_order(JOIN *join)
 {
-  uint tablenr;
+  uint tableno;
   table_map remaining_tables= 0;
   table_map handled_tabs= 0;
 
   DBUG_ENTER("fix_semijoin_strategies_for_picked_join_order");
 
-  for (tablenr= join->tables - 1 ; tablenr != join->const_tables - 1; tablenr--)
+  if (join->select_lex->sj_nests.is_empty())
+    DBUG_RETURN(FALSE);
+
+  for (tableno= join->tables - 1; tableno != join->const_tables - 1; tableno--)
   {
-    POSITION *pos= join->best_positions + tablenr;
+    POSITION *pos= join->best_positions + tableno;
     JOIN_TAB *s= pos->table;
     TABLE_LIST *emb_sj_nest= s->emb_sj_nest;
     uint first;
@@ -8451,7 +8446,7 @@ static bool fix_semijoin_strategies_for_
       */
       memcpy(pos - table_count + 1, emb_sj_nest->nested_join->sjm.positions, 
              sizeof(POSITION) * table_count);
-      first= tablenr - table_count + 1;
+      first= tableno - table_count + 1;
       join->best_positions[first].n_sj_tables= table_count;
       join->best_positions[first].sj_strategy= SJ_OPT_MATERIALIZE_LOOKUP;
 
@@ -8489,12 +8484,12 @@ static bool fix_semijoin_strategies_for_
       
       uint i;
       table_map rem_tables= remaining_tables;
-      for (i= tablenr; i != (first + table_count - 1); i--)
+      for (i= tableno; i != (first + table_count - 1); i--)
         rem_tables |= join->best_positions[i].table->table->map;
 
       POSITION dummy;
       join->cur_sj_inner_tables= 0;
-      for (i= first + table_count; i <= tablenr; i++)
+      for (i= first + table_count; i <= tableno; i++)
       {
         best_access_path(join, join->best_positions[i].table, rem_tables, i, FALSE,
                          prefix_rec_count, join->best_positions + i, &dummy);
@@ -8508,14 +8503,14 @@ static bool fix_semijoin_strategies_for_
     {
       first= pos->first_firstmatch_table;
       join->best_positions[first].sj_strategy= SJ_OPT_FIRST_MATCH;
-      join->best_positions[first].n_sj_tables= tablenr - first + 1;
+      join->best_positions[first].n_sj_tables= tableno - first + 1;
       POSITION dummy; // For loose scan paths
       double record_count= (first== join->const_tables)? 1.0: 
-                           join->best_positions[tablenr - 1].prefix_record_count;
+                           join->best_positions[tableno - 1].prefix_record_count;
       
       table_map rem_tables= remaining_tables;
       uint idx;
-      for (idx= first; idx <= tablenr; idx++)
+      for (idx= first; idx <= tableno; idx++)
       {
         rem_tables |= join->best_positions[idx].table->table->map;
       }
@@ -8524,7 +8519,7 @@ static bool fix_semijoin_strategies_for_
         join buffering
       */ 
       join->cur_sj_inner_tables= 0;
-      for (idx= first; idx <= tablenr; idx++)
+      for (idx= first; idx <= tableno; idx++)
       {
         if (join->best_positions[idx].use_join_buffer)
         {
@@ -8542,18 +8537,18 @@ static bool fix_semijoin_strategies_for_
       POSITION *first_pos= join->best_positions + first;
       POSITION loose_scan_pos; // For loose scan paths
       double record_count= (first== join->const_tables)? 1.0: 
-                           join->best_positions[tablenr - 1].prefix_record_count;
+                           join->best_positions[tableno - 1].prefix_record_count;
       
       table_map rem_tables= remaining_tables;
       uint idx;
-      for (idx= first; idx <= tablenr; idx++)
+      for (idx= first; idx <= tableno; idx++)
         rem_tables |= join->best_positions[idx].table->table->map;
       /*
         Re-run best_access_path to produce best access methods that do not use
         join buffering
       */ 
       join->cur_sj_inner_tables= 0;
-      for (idx= first; idx <= tablenr; idx++)
+      for (idx= first; idx <= tableno; idx++)
       {
         if (join->best_positions[idx].use_join_buffer || (idx == first))
         {
@@ -8578,7 +8573,7 @@ static bool fix_semijoin_strategies_for_
       */
       first= pos->first_dupsweedout_table;
       join->best_positions[first].sj_strategy= SJ_OPT_DUPS_WEEDOUT;
-      join->best_positions[first].n_sj_tables= tablenr - first + 1;
+      join->best_positions[first].n_sj_tables= tableno - first + 1;
     }
     
     uint i_end= first + join->best_positions[first].n_sj_tables;
@@ -8593,7 +8588,7 @@ static bool fix_semijoin_strategies_for_
       handled_tabs |= join->best_positions[i].table->table->map;
     }
 
-    if (tablenr != first)
+    if (tableno != first)
       pos->sj_strategy= SJ_OPT_NONE;
     remaining_tables |= s->table->map;
   }
@@ -8630,16 +8625,13 @@ static bool fix_semijoin_strategies_for_
 
 static bool get_best_combination(JOIN *join)
 {
-  uint i,tablenr;
   table_map used_tables;
-  JOIN_TAB *join_tab,*j;
   KEYUSE *keyuse;
-  uint table_count;
+  uint table_count= join->tables;
   THD *thd=join->thd;
   DBUG_ENTER("get_best_combination");
 
-  table_count=join->tables;
-  if (!(join->join_tab= join_tab= new (thd->mem_root) JOIN_TAB[table_count]))
+  if (!(join->join_tab= new (thd->mem_root) JOIN_TAB[table_count]))
     DBUG_RETURN(TRUE);
 
   join->full_join=0;
@@ -8649,11 +8641,12 @@ static bool get_best_combination(JOIN *j
   if (fix_semijoin_strategies_for_picked_join_order(join))
     DBUG_RETURN(TRUE);
 
-  for (j=join_tab, tablenr=0 ; tablenr < table_count ; tablenr++,j++)
+  for (uint tableno= 0; tableno < table_count; tableno++)
   {
+    JOIN_TAB *j= join->join_tab + tableno;
     TABLE *form;
-    *j= *join->best_positions[tablenr].table;
-    form=join->all_tables[tablenr]=j->table;
+    *j= *join->best_positions[tableno].table;
+    form=join->all_tables[tableno]= j->table;
     used_tables|= form->map;
     form->reginfo.join_tab=j;
     if (!*j->on_expr_ref)
@@ -8663,31 +8656,69 @@ static bool get_best_combination(JOIN *j
     if (j->type == JT_CONST)
       continue;					// Handled in make_join_stat..
 
-
     j->loosescan_match_tab= NULL;  //non-nulls will be set later
     j->ref.key = -1;
     j->ref.key_parts=0;
 
-
     if (j->type == JT_SYSTEM)
       continue;
     
-    if (j->keys.is_clear_all() || !(keyuse= join->best_positions[tablenr].key) || 
-        (join->best_positions[tablenr].sj_strategy == SJ_OPT_LOOSE_SCAN))
+    if (j->keys.is_clear_all() ||
+       !(keyuse= join->best_positions[tableno].key) || 
+        (join->best_positions[tableno].sj_strategy == SJ_OPT_LOOSE_SCAN))
     {
       j->type=JT_ALL;
-      j->index= join->best_positions[tablenr].loosescan_key;
-      if (tablenr != join->const_tables)
+      j->index= join->best_positions[tableno].loosescan_key;
+      if (tableno != join->const_tables)
 	join->full_join=1;
     }
     else if (create_ref_for_key(join, j, keyuse, used_tables))
       DBUG_RETURN(TRUE);                        // Something went wrong
   }
 
-  for (i=0 ; i < table_count ; i++)
-    join->map2table[join->join_tab[i].table->tablenr]=join->join_tab+i;
+  for (uint tableno= 0; tableno < table_count; tableno++)
+    join->map2table[join->join_tab[tableno].table->tablenr]=
+      join->join_tab + tableno;
+
   update_depend_map(join);
-  DBUG_RETURN(0);
+
+  for (uint tableno= join->const_tables; tableno < table_count; )
+  {
+    JOIN_TAB *tab= join->join_tab + tableno;
+    POSITION *pos= join->best_positions + tableno;
+
+    switch (pos->sj_strategy)
+    {
+    case SJ_OPT_NONE:
+      tableno++;
+      break;
+    case SJ_OPT_MATERIALIZE_LOOKUP:
+    case SJ_OPT_MATERIALIZE_SCAN:
+    case SJ_OPT_LOOSE_SCAN:
+    case SJ_OPT_DUPS_WEEDOUT:
+    case SJ_OPT_FIRST_MATCH:
+      /*
+        Remember the first and last semijoin inner tables; this serves to tell
+        a JOIN_TAB's semijoin strategy (like in check_join_cache_usage()).
+      */
+      JOIN_TAB *last_sj_tab= tab + pos->n_sj_tables - 1;
+      JOIN_TAB *last_sj_inner=
+        (pos->sj_strategy == SJ_OPT_DUPS_WEEDOUT) ?
+        /* Range may end with non-inner table so cannot set last_sj_inner_tab */
+        NULL : last_sj_tab;
+      for (JOIN_TAB *tab_in_range= tab; 
+           tab_in_range <= last_sj_tab; 
+           tab_in_range++)
+      {
+        tab_in_range->first_sj_inner_tab= tab;
+        tab_in_range->last_sj_inner_tab=  last_sj_inner;
+      }
+      tableno+= pos->n_sj_tables;
+      break;
+    }
+  }
+
+  DBUG_RETURN(FALSE);
 }
 
 
@@ -10857,8 +10888,7 @@ make_join_readinfo(JOIN *join, ulonglong
   uint last_sjm_table= MAX_TABLES;
   DBUG_ENTER("make_join_readinfo");
 
-  if (!join->select_lex->sj_nests.is_empty() &&
-      setup_semijoin_dups_elimination(join, options, no_jbuf_after))
+  if (setup_semijoin_dups_elimination(join, options, no_jbuf_after))
     DBUG_RETURN(TRUE); /* purecov: inspected */
 
   for (i=join->const_tables ; i < join->tables ; i++)


Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20100929143339-mfxmrihk6qf0b3tu.bundle
Thread
• bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3253) Bug#45174Bug#50019	Roy Lyseng	29 Sep
• Re: bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3253)Bug#45174 Bug#50019	Guilhem Bichot	2 Oct
• Re: bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3253)Bug#45174 Bug#50019	Roy Lyseng	5 Oct
• Re: bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3253)Bug#45174 Bug#50019	Roy Lyseng	7 Oct
• Re: bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3253)Bug#45174 Bug#50019	Guilhem Bichot	20 Oct