List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:October 26 2010 10:44am
Subject:bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3269) Bug#50019
View as plain text  
#At file:///home/rl136806/mysql/repo/mysql-work5/ based on revid:tor.didriksen@stripped

 3269 Roy Lyseng	2010-10-26
      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 transformation, its tables
      are merged to the outer query and are later treated as regular tables. 
      One possible optimization to apply after semijoin transformation is
      equality propagation.
      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, equality propagation can't be applied as is when using the
      semijoin materialization strategy. In this case, data from
      materialized semijoin tables are saved to a temporary table first
      before being involved in the join.
      
      This has two consequences for equality propagation:
      1. Equalities that exist within the materialized tables must be processed
         within the materialization operation, and
      2. Equalities between the materialized table and the outer query must be
         processed like regular equalities.
      
      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, because the equality it1.col=it2.col
      can be processed during the materialization.
      
      For a field that is not in a materialized semijoin we can use any field,
      even those that are embedded in a materialized semijoin. This is because
      such fields are "copied back" to their original join-tab structures when
      the materialized temporary table is being read.
      
      Now we have added a new function Item_equal::get_subst_item() 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), otherwise it will be the first
      field in the multiple equality.
      
      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 (before they were only used
      by the DuplicateWeedout strategy, and were not needed until later).
      
      Also fixed problems with pushdown of SJM-aware predicates during
      make_join_select():
       - Wrong predicates were sometimes generated,
       - make_cond_after_sjm() was called at the wrong position in the join
         sequence.
       - make_cond_after_sjm() was never actually considering the pushed-down
         SJM predicates.
      
      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.
      
      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 three 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_subst_item() 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 than before when using
        MaterializeScan and usually better than before for MaterializeLookup.
        (For MaterializeLookup, it is worse when it is more efficient to move
         the clause to the outer query. The best possible solution for this
         case is probably to evaluate the clause in both queries, this can
         be subject for a later feature development.)
        Another test that applies the same condition to both the outer and
        the inner query is added, to show the plan for such types of queries.
        Earlier wrong semijoin materialization test results are corrected.
       
      sql/item.cc
        Bug#45174: Incorrectly applied equality propagation caused wrong result
        on a query with a materialized semi-join.
      
        Calling new get_subst_item() function instead of get_first().
      
      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_subst_item() that accepts as argument
        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_subst_item() that accepts as argument
        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.
        Bug#50019: Wrong result for IN-query with materialization.
      
        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.
        
        In make_join_select(), the test that determined when to pushdown
        SJM-specific predicates was wrong, in addition to improving the
        comments.
      
        The logic of eliminate_item_equal() has been simplified and
        adjusted so that it generates equalities that are useful also
        when the semijoin materialization strategy is being used.
        Some simplification was possible by taking advantage of the new
        Item_equal::get_subst_item() function.
      
        In make_cond_for_table_from_pred(), a number of comments has been
        added, and TAB characters are replaced by spaces.
      
        In make_cond_after_sjm(), make sure that it handles equalities
        generated for semijoin materialization (with marker=3).
        Added comments and removed TAB characters.

    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-10-13 13:27:36 +0000
+++ b/mysql-test/include/subquery_sj.inc	2010-10-26 10:43:50 +0000
@@ -393,6 +393,9 @@ insert into t3 select A.a + 10*B.a from 
 explain select * from t3 where a in (select kp1 from t1 where kp1<20);
 select * from t3 where a in (select kp1 from t1 where kp1<20);
 
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 
@@ -637,11 +640,9 @@ drop table t0, t1,t2,t3;
 
 
 --echo
---echo Test that MaterializeLookup strategy for semijoin,
+--echo Test that neither MaterializeLookup strategy for semijoin,
 --echo nor subquery materialization is used when BLOBs are involved 
 --echo (except when arguments of some functions).
---echo Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
---echo strategy is used instead, 
 --echo
 set @prefix_len = 6;
 
@@ -3371,3 +3372,64 @@ WHERE (a1, a2) IN (
 DROP TABLE t1,t2a,t2b,t2c;
 
 --echo # End BUG#52329
+
+--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-10-26 10:43:50 +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-10-15 10:32:50 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2010-10-26 10:43:50 +0000
@@ -2200,6 +2200,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index; LooseScan
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (BNL, regular buffers)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2580,14 +2606,12 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
 1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start materialize
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End materialize
+1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using index; End materialize
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -4902,7 +4926,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4911,8 +4934,6 @@ Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 pk
-1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4932,7 +4953,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4942,7 +4962,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4952,7 +4971,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4972,7 +4990,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4982,7 +4999,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 pk
 1
-1
 DROP TABLE t1, t2;
 # End of Bug#48213
 # 
@@ -5186,4 +5202,67 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-15 10:32:50 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result	2010-10-26 10:43:50 +0000
@@ -2204,6 +2204,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index; LooseScan
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (BNL, incremental buffers)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2584,14 +2610,12 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
 1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start materialize
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End materialize
+1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using index; End materialize
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -4906,7 +4930,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4915,8 +4938,6 @@ Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 pk
-1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4936,7 +4957,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4946,7 +4966,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4956,7 +4975,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4976,7 +4994,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4986,7 +5003,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 pk
 1
-1
 DROP TABLE t1, t2;
 # End of Bug#48213
 # 
@@ -5190,6 +5206,69 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-15 10:32:50 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result	2010-10-26 10:43:50 +0000
@@ -2204,6 +2204,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index; LooseScan
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (BNL, regular buffers)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2584,14 +2610,12 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
 1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start materialize
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End materialize
+1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using index; End materialize
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -4906,7 +4930,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4915,8 +4938,6 @@ Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 pk
-1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4936,7 +4957,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4946,7 +4966,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4956,7 +4975,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4976,7 +4994,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4986,7 +5003,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 pk
 1
-1
 DROP TABLE t1, t2;
 # End of Bug#48213
 # 
@@ -5190,6 +5206,69 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-15 10:32:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2010-10-26 10:43:50 +0000
@@ -2199,6 +2199,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<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	t1	ref	kp1	kp1	5	test.t3.a	1	Using index; Start temporary; End temporary
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2582,11 +2608,9 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -5184,4 +5208,67 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-15 10:32:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-10-26 10:43:50 +0000
@@ -2203,6 +2203,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<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; Start temporary
+1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using index; End temporary
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2586,11 +2612,9 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -5188,6 +5212,69 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-15 10:32:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-10-26 10:43:50 +0000
@@ -2203,6 +2203,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<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; Start temporary
+1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using index; End temporary
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2586,11 +2612,9 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -5188,6 +5212,69 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-15 10:32:50 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2010-10-26 10:43:50 +0000
@@ -2200,6 +2200,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<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	t1	ref	kp1	kp1	5	test.t3.a	1	Using index; FirstMatch(t3)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2583,11 +2609,9 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; FirstMatch(t0)
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -5186,6 +5210,69 @@ a1	a2
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
 #
+# 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-10-15 10:32:50 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2010-10-26 10:43:50 +0000
@@ -2204,6 +2204,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<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	t1	ref	kp1	kp1	5	test.t3.a	1	Using index; FirstMatch(t3)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2587,11 +2613,9 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; FirstMatch(t0)
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -5190,6 +5214,69 @@ a1	a2
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
 #
+# 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-10-15 10:32:50 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-10-26 10:43:50 +0000
@@ -2204,6 +2204,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<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	t1	ref	kp1	kp1	5	test.t3.a	1	Using index; FirstMatch(t3)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2587,11 +2613,9 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; FirstMatch(t0)
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -5190,6 +5214,69 @@ a1	a2
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
 #
+# 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-10-15 10:32:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2010-10-26 10:43:50 +0000
@@ -2200,6 +2200,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index; LooseScan
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (BNL, regular buffers)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2583,11 +2609,9 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -5189,4 +5213,67 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-15 10:32:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-10-26 10:43:50 +0000
@@ -2204,6 +2204,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index; LooseScan
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (BNL, incremental buffers)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2587,11 +2613,9 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -5193,6 +5217,69 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-15 10:32:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-10-26 10:43:50 +0000
@@ -2204,6 +2204,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index; LooseScan
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (BNL, regular buffers)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2587,11 +2613,9 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -5193,6 +5217,69 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-15 10:32:50 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2010-10-26 10:43:50 +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
@@ -2200,12 +2200,38 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<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	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) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 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 
@@ -2580,14 +2606,12 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
 1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start materialize
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End materialize
+1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using index; End materialize
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -2619,32 +2643,26 @@ 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
@@ -2749,32 +2767,26 @@ 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
@@ -2844,32 +2856,26 @@ 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 from t1_513
 where a1 in (select substring(b1,1,513) from t2_513 where b1 > '0');
@@ -2926,32 +2932,26 @@ 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
@@ -3021,32 +3021,26 @@ 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
@@ -4912,7 +4906,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4921,8 +4914,6 @@ Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 pk
-1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Start temporary
@@ -4942,7 +4933,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4952,7 +4942,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4962,7 +4951,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Start temporary
@@ -4982,7 +4970,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4992,7 +4979,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 pk
 1
-1
 DROP TABLE t1, t2;
 # End of Bug#48213
 # 
@@ -5196,4 +5182,67 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-15 10:32:50 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result	2010-10-26 10:43:50 +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
@@ -2204,12 +2204,38 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<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	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) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 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 
@@ -2584,14 +2610,12 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
 1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start materialize
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End materialize
+1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using index; End materialize
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -2623,32 +2647,26 @@ 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
@@ -2753,32 +2771,26 @@ 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
@@ -2848,32 +2860,26 @@ 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 from t1_513
 where a1 in (select substring(b1,1,513) from t2_513 where b1 > '0');
@@ -2930,32 +2936,26 @@ 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
@@ -3025,32 +3025,26 @@ 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
@@ -4916,7 +4910,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4925,8 +4918,6 @@ Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 pk
-1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Start temporary
@@ -4946,7 +4937,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4956,7 +4946,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4966,7 +4955,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Start temporary
@@ -4986,7 +4974,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4996,7 +4983,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 pk
 1
-1
 DROP TABLE t1, t2;
 # End of Bug#48213
 # 
@@ -5200,6 +5186,69 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-15 10:32:50 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result	2010-10-26 10:43:50 +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
@@ -2204,12 +2204,38 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<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	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) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 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 
@@ -2584,14 +2610,12 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
 1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start materialize
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End materialize
+1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using index; End materialize
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -2623,32 +2647,26 @@ 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
@@ -2753,32 +2771,26 @@ 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
@@ -2848,32 +2860,26 @@ 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 from t1_513
 where a1 in (select substring(b1,1,513) from t2_513 where b1 > '0');
@@ -2930,32 +2936,26 @@ 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
@@ -3025,32 +3025,26 @@ 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 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');
 left(a1,7)	left(a2,7)
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
-1 - 00x	2 - 00x
 explain extended select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
@@ -4916,7 +4910,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4925,8 +4918,6 @@ Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 pk
-1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Start temporary
@@ -4946,7 +4937,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4956,7 +4946,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4966,7 +4955,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Start temporary
@@ -4986,7 +4974,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 pk
 1
-1
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4996,7 +4983,6 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 pk
 1
-1
 DROP TABLE t1, t2;
 # End of Bug#48213
 # 
@@ -5200,6 +5186,69 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2010-10-26 10:43:50 +0000
@@ -2415,6 +2415,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<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
+2	SUBQUERY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2798,11 +2824,9 @@ id	select_type	table	type	possible_keys	
 2	SUBQUERY	t3	ref	a	a	5	test.t1.a	1	Using index
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -5407,4 +5431,67 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2010-10-26 10:43:50 +0000
@@ -2348,6 +2348,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<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
+2	DEPENDENT SUBQUERY	t1	index_subquery	kp1	kp1	5	func	1	Using index; Using where
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2731,11 +2757,9 @@ id	select_type	table	type	possible_keys	
 2	DEPENDENT SUBQUERY	t3	ref	a	a	5	test.t2.a	1	Using where; Using index
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -5333,4 +5357,67 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result	2010-10-26 10:43:50 +0000
@@ -2352,6 +2352,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<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
+2	DEPENDENT SUBQUERY	t1	index_subquery	kp1	kp1	5	func	1	Using index; Using where
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2735,11 +2761,9 @@ id	select_type	table	type	possible_keys	
 2	DEPENDENT SUBQUERY	t3	ref	a	a	5	test.t2.a	1	Using where; Using index
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -5337,6 +5361,69 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result	2010-10-26 10:43:50 +0000
@@ -2352,6 +2352,32 @@ a
 17
 18
 19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<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
+2	DEPENDENT SUBQUERY	t1	index_subquery	kp1	kp1	5	func	1	Using index; Using where
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 explain select * from t3 where a in 
@@ -2735,11 +2761,9 @@ id	select_type	table	type	possible_keys	
 2	DEPENDENT SUBQUERY	t3	ref	a	a	5	test.t2.a	1	Using where; Using index
 drop table t0, t1,t2,t3;
 
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
 nor subquery materialization is used when BLOBs are involved 
 (except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan 
-strategy is used instead, 
 
 set @prefix_len = 6;
 set @blob_len = 16;
@@ -5337,6 +5361,69 @@ a1	a2
 1 - 12	2 - 22
 DROP TABLE t1,t2a,t2b,t2c;
 # End BUG#52329
+#
+# 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-10-15 10:32:50 +0000
+++ b/sql/item.cc	2010-10-26 10:43:50 +0000
@@ -5075,8 +5075,9 @@ 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_subst_item(this);
+    DBUG_ASSERT(subst);
+    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-10-15 10:32:50 +0000
+++ b/sql/item_cmpfunc.cc	2010-10-26 10:43:50 +0000
@@ -5840,3 +5840,89 @@ void Item_equal::print(String *str, enum
   str->append(')');
 }
 
+
+/**
+  Get item that can be substituted for the supplied item.
+
+  @param field  field item to get substitution field for, which must be
+                present within the multiple equality itself.
+
+  @retval Found substitution item in the multiple equality.
+
+  @details Get the first item of multiple equality that can be substituted
+  for the given field item. 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) unconditionally.
+  Thus the field is returned according to the following rules:
+
+  1) If the given field belongs to a materialized semijoin then the
+     first field in the multiple equality which belongs 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 is returned.
+*/
+
+Item_field* Item_equal::get_subst_item(const Item_field *field)
+{
+  DBUG_ASSERT(field != NULL);
+
+  const JOIN_TAB *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.
+
+      Example: suppose we have a join order:
+
+       ot1 ot2  SJM(it1  it2  it3)  ot3
+
+      and equality ot2.col = it1.col = it2.col
+
+      If we're looking for best substitute for 'it2.col', we must pick it1.col
+      and not ot2.col. it2.col is evaluated while performing materialization,
+      when the outer tables are not available in the execution.
+    */
+    List_iterator<Item_field> it(fields);
+    Item_field *item;
+    const JOIN_TAB *first= field_tab->first_sj_inner_tab;
+    const 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 can return
+      the first field in the multiple equality.
+
+      Example: suppose we have a join order with MaterializeLookup:
+
+       ot1 ot2  SJM-Lookup(it1  it2)
+
+      Here we should always pick the first field in the multiple equality,
+      as this will be present before all other dependent fields.
+
+      Example: suppose we have a join order with MaterializeScan:
+
+          SJM-Scan(it1  it2)  ot1  ot2
+
+      and equality ot2.col = ot1.col = it2.col.
+
+      When looking for best substitute for 'ot2.col', we can pick it2.col,
+      because when we run the scan, column values from the inner materialized
+      tables will be copied back to the column buffers for it1 and it2.
+    */
+    return fields.head();
+  }
+  DBUG_ASSERT(FALSE);                          // Should never get here.
+  return NULL;
+}

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2010-10-15 10:32:50 +0000
+++ b/sql/item_cmpfunc.h	2010-10-26 10:43:50 +0000
@@ -1637,7 +1637,13 @@ public:
   void add(Item_field *f);
   uint members();
   bool contains(Field *field);
+  /**
+    Get the first field of multiple equality, use for semantic checking.
+
+    @retval First field in the multiple equality.
+  */
   Item_field* get_first() { return fields.head(); }
+  Item_field* get_subst_item(const Item_field *field);
   void merge(Item_equal *item);
   void update_const();
   enum Functype functype() const { return MULT_EQUAL_FUNC; }
@@ -1654,8 +1660,6 @@ public:
   virtual void print(String *str, enum_query_type query_type);
   CHARSET_INFO *compare_collation() 
   { return fields.head()->collation.collation; }
-  friend Item *eliminate_item_equal(Item *cond, COND_EQUAL *upper_levels,
-                           Item_equal *item_equal);
   friend bool setup_sj_materialization(struct st_join_table *tab);
 }; 
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-10-21 15:44:35 +0000
+++ b/sql/sql_select.cc	2010-10-26 10:43:50 +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);
 }
@@ -4253,14 +4241,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 */
@@ -4343,7 +4335,12 @@ int pull_out_semijoin_tables(JOIN *join)
             pointers.
           */
           child_li.remove();
-          upper_join_list->push_back(tbl);
+          if (upper_join_list->push_back(tbl))
+          {
+            if (arena)
+              join->thd->restore_active_arena(arena, &backup);
+            DBUG_RETURN(TRUE);
+          }
           tbl->join_list= upper_join_list;
           tbl->embedding= sj_nest->embedding;
         }
@@ -4364,7 +4361,7 @@ int pull_out_semijoin_tables(JOIN *join)
         join->thd->restore_active_arena(arena, &backup);
     }
   }
-  DBUG_RETURN(0);
+  DBUG_RETURN(FALSE);
 }
 
 
@@ -8426,15 +8423,19 @@ prev_record_reads(JOIN *join, uint idx, 
 
 static bool fix_semijoin_strategies_for_picked_join_order(JOIN *join)
 {
-  uint tablenr;
   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 (uint 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;
@@ -8471,7 +8472,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;
 
@@ -8507,14 +8508,13 @@ static bool fix_semijoin_strategies_for_
       /* Add materialization record count*/
       prefix_rec_count *= mat_sj_nest->nested_join->sjm.expected_rowcount;
       
-      uint i;
       table_map rem_tables= remaining_tables;
-      for (i= tablenr; i != (first + table_count - 1); i--)
+      for (uint 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 (uint 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);
@@ -8528,14 +8528,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 (uint idx= first; idx <= tableno; idx++)
       {
         rem_tables |= join->best_positions[idx].table->table->map;
       }
@@ -8544,7 +8544,7 @@ static bool fix_semijoin_strategies_for_
         join buffering
       */ 
       join->cur_sj_inner_tables= 0;
-      for (idx= first; idx <= tablenr; idx++)
+      for (uint idx= first; idx <= tableno; idx++)
       {
         if (join->best_positions[idx].use_join_buffer)
         {
@@ -8562,18 +8562,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 (uint 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 (uint idx= first; idx <= tableno; idx++)
       {
         if (join->best_positions[idx].use_join_buffer || (idx == first))
         {
@@ -8598,7 +8598,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;
@@ -8613,7 +8613,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;
   }
@@ -8650,16 +8650,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;
+  const 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;
@@ -8669,11 +8666,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)
@@ -8683,31 +8681,73 @@ 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);
+
+  /*
+    Set the first_sj_inner_tab and last_sj_inner_tab fields for all tables
+    inside the semijoin nests of the query.
+  */
+  for (uint tableno= join->const_tables; tableno < table_count; )
+  {
+    JOIN_TAB *tab= join->join_tab + tableno;
+    const 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);
 }
 
 
@@ -9350,6 +9390,22 @@ static bool pushdown_on_conditions(JOIN*
 }
 
 
+/**
+  Separates the predicates in a join condition and pushes them to the 
+  join step where all involved tables are available in the join prefix.
+  ON clauses from JOIN expressions are also pushed to the most appropriate step.
+
+  @param join Join object where predicates are pushed.
+
+  @param cond Pointer to condition which may contain an arbitrary number of
+              predicates, combined using AND, OR and XOR items.
+              If NULL, equivalent to a predicate that returns TRUE for all
+              row combinations.
+
+  @retval TRUE if condition is always false OR an error occurred.
+  @retval FALSE otherwise.
+*/
+
 static bool make_join_select(JOIN *join, Item *cond)
 {
   THD *thd= join->thd;
@@ -9445,8 +9501,7 @@ static bool make_join_select(JOIN *join,
          - If we're looking at the first SJM table, reset used_tables
            to refer to only allowed tables
       */
-      if (tab->emb_sj_nest &&
-          tab->emb_sj_nest->sj_mat_exec && 
+      if (sj_is_materialize_strategy(tab->get_sj_strategy()) &&
           !(used_tables & tab->emb_sj_nest->sj_inner_tables))
       {
         save_used_tables= used_tables;
@@ -9689,32 +9744,38 @@ static bool make_join_select(JOIN *join,
 
       DBUG_ASSERT(save_used_tables ? tab->emb_sj_nest != NULL : TRUE);
 
-      if (save_used_tables && !(used_tables & 
-                                ~(tab->emb_sj_nest->sj_inner_tables |
-                                  join->const_table_map | PSEUDO_TABLE_BITS)))
+      /*
+         1. We are inside a materialized semijoin nest, and
+         2. All inner tables of the nest are covered.
+      */ 
+      if (save_used_tables &&                                        // 1
+         !(tab->emb_sj_nest->sj_inner_tables & ~used_tables))        // 2
       {
         /*
-          We have reached the end of semi join nest. That is, the join order
-          looks like this:
+          The join order now looks like this:
 
-           outer_tbl1 SJ-Materialize(inner_tbl1 ... inner_tblN) outer_tbl ...
-                                                               ^
-                                                                \-we're here
-          At this point, we need to produce two conditions
-           - A condition that can be checked when we have all of the sj-inner
-             tables (inner_tbl1 ... inner_tblN). This will be used while doing
-             materialization.
-           - A condition that can be checked when we have all of the tables
-             in the prefix (both inner and outer).
+           ot1 ... otI SJM(it1 ... itN) otI+1 ... otM
+                                       ^
+                                        \-we're here
+          At this point, we have generated a condition that can be checked
+          when we have all of the sj-inner tables (it1 ... itN).
+          This will be used while doing materialization.
+
+          In addition, we need a condition that can be checked when we have
+          all of the tables in the prefix (both inner and outer).
+          This condition is only generated (and used) when we have an SJM-scan
+          operation. For SJM-lookup, the condition is completely fulfilled
+          through the lookup into the materialized table.
+          This constraint will last as long as we do not allow correlated
+          subqueries with materialized semijoin execution.
         */
-        tab->emb_sj_nest->sj_mat_exec->join_cond= 
-          cond ?
-             make_cond_after_sjm(cond, cond, save_used_tables, used_tables):
-            NULL;
+        if (cond && tab->emb_sj_nest->sj_mat_exec->is_scan)
+          tab->emb_sj_nest->sj_mat_exec->join_cond= 
+            make_cond_after_sjm(cond, cond, save_used_tables, used_tables);
+
         used_tables= save_used_tables | used_tables;
         save_used_tables= 0;
       }
-
     }
   }
   DBUG_RETURN(0);
@@ -10877,8 +10938,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++)
@@ -12527,17 +12587,18 @@ Item *eliminate_item_equal(Item *cond, C
                            Item_equal *item_equal)
 {
   List<Item> eq_list;
-  Item_func_eq *eq_item= 0;
+  Item_func_eq *eq_item= NULL;
   if (((Item *) item_equal)->const_item() && !item_equal->val_int())
     return new Item_int((longlong) 0,1); 
   Item *item_const= item_equal->get_const();
   Item_equal_iterator it(*item_equal);
   Item *head;
-  if (item_const)
-    head= item_const;
-  else
+  if (!item_const)
   {
-    head= item_equal->get_first();
+    /*
+      If there is a const item, match all field items with the const item,
+      otherwise match the second and subsequent field items with the first one:
+    */
     it++;
   }
   Item_field *item_field;
@@ -12548,7 +12609,7 @@ Item *eliminate_item_equal(Item *cond, C
     if (upper)
     { 
       if (item_const && upper->get_const())
-        item= 0;
+        item= NULL;
       else
       {
         Item_equal_iterator li(*item_equal);
@@ -12563,57 +12624,42 @@ Item *eliminate_item_equal(Item *cond, C
     {
       if (eq_item)
         eq_list.push_back(eq_item);
-      /*
-        item_field might refer to a table that is within a semi-join
-        materialization nest. In that case, the join order looks like this:
 
-          outer_tbl1 outer_tbl2 SJM (inner_tbl1 inner_tbl2) outer_tbl3 
+      /*
+        item_field may refer to a table that is within a semijoin
+        materialization nest. In that case, the join order may look like:
 
-        We must not construct equalities like 
+          ot1 ot2 SJM (it3 it4) ot5 
 
-           outer_tbl1.col = inner_tbl1.col 
+        If we have a multiple equality (ot1.c1, ot2.c2, it3.c3, it4.c4, ot5.c5),
+        we should generate the following equalities:
+         1. ot1.c1 = ot2.c2
+         2. ot1.c1 = it3.c3
+         3. it3.c3 = it4.c4
+         4. ot1.c1 = ot5.c5
+
+        Equalities 1) and 4) are regular equalities between two outer tables.
+        Equality 2) is an equality that matches the outer query with a
+        materialized semijoin table. It is either performed as a lookup
+        into the materialized table (SJM-lookup), or as a condition on the
+        outer table (SJM-scan).
+        Equality 3) is evaluated during semijoin materialization.
+
+        If there is a const item, match against this one.
+        Otherwise, match against the first field item in the multiple equality,
+        unless the item is within a materialized semijoin nest, where we match
+        against the first item within the SJM nest (if the item is not the first
+        item within the SJM nest), or match against the first item in the
+        list (if the item is the first one in the SJM nest).
+      */
+      head= item_const ? item_const : item_equal->get_subst_item(item_field);
+      if (head == item_field)                   // First item in SJM nest
+        head= item_equal->get_first();
 
-        because they would get attached to inner_tbl1 and will get evaluated
-        during materialization phase, when we don't have current value of
-        outer_tbl1.col.
-      */
-      TABLE_LIST *emb_nest= 
-        item_field->field->table->pos_in_table_list->embedding;
-      if (!item_const && emb_nest && emb_nest->sj_mat_exec)
-      {
-        /* 
-          Find the first equal expression that refers to a table that is
-          within the semijoin nest. If we can't find it, do nothing
-        */
-        List_iterator<Item_field> fit(item_equal->fields);
-        Item_field *head_in_sjm;
-        bool found= FALSE;
-        while ((head_in_sjm= fit++))
-        {
-          if (head_in_sjm->used_tables() & emb_nest->sj_inner_tables)
-          {
-            if (head_in_sjm == item_field)
-            {
-              /* This is the first table inside the semi-join*/
-              eq_item= new Item_func_eq(item_field, head);
-              /* Tell make_cond_for_table don't use this. */
-              eq_item->marker=3;
-            }
-            else
-            {
-              eq_item= new Item_func_eq(item_field, head_in_sjm);
-              found= TRUE;
-            }
-            break;
-          }
-        }
-        if (!found)
-          continue;
-      }
-      else
-        eq_item= new Item_func_eq(item_field, head);
+      eq_item= new Item_func_eq(item_field, head);
       if (!eq_item)
-        return 0;
+        return NULL;
+
       eq_item->set_cmp_func();
       eq_item->quick_fix_field();
     }
@@ -18840,21 +18886,23 @@ static bool replace_subcondition(JOIN *j
 }
 
 
-/*
+/**
   Extract a condition that can be checked after reading given table
   
-  SYNOPSIS
-    make_cond_for_table()
-      cond         Condition to analyze
-      tables       Tables for which "current field values" are available
-      used_table   Table that we're extracting the condition for (may 
-                   also include PSEUDO_TABLE_BITS
-      exclude_expensive_cond  Do not push expensive conditions
+  @param cond       Condition to analyze
+  @param tables     Tables for which "current field values" are available
+  @param used_table Table that we're extracting the condition for (may 
+                    also include PSEUDO_TABLE_BITS, and may be zero)
+  @param exclude_expensive_cond  Do not push expensive conditions
 
-  DESCRIPTION
+  @retval <>NULL Generated condition
+  @retval = NULL Already checked, OR error
+
+  @details
     Extract the condition that can be checked after reading the table
     specified in 'used_table', given that current-field values for tables
     specified in 'tables' bitmap are available.
+    If 'used_table' is 0, extract conditions for all tables in 'tables'.
 
     The function assumes that
       - Constant parts of the condition has already been checked.
@@ -18865,12 +18913,10 @@ static bool replace_subcondition(JOIN *j
     guaranteed to be true by employed 'ref' access methods (the code that
     does this is located at the end, search down for "EQ_FUNC").
 
-
-  SEE ALSO 
-    make_cond_for_info_schema uses similar algorithm
-
-  RETURN
-    Extracted condition
+  @note
+    Make sure to keep the implementations of make_cond_for_table() and
+    make_cond_after_sjm() synchronized.
+    make_cond_for_info_schema() uses similar algorithm as well.
 */
 
 static Item *
@@ -18880,75 +18926,80 @@ make_cond_for_table(Item *cond, table_ma
   return make_cond_for_table_from_pred(cond, cond, tables, used_table,
                                        exclude_expensive_cond);
 }
-               
+
 static Item *
 make_cond_for_table_from_pred(Item *root_cond, Item *cond,
                               table_map tables, table_map used_table,
                               bool exclude_expensive_cond)
 {
-  if (used_table && !(cond->used_tables() & used_table) &&
-      /*
-        Exclude constant conditions not checked at optimization time if
+  /*
+    Ignore this condition if
+     1. We are extracting conditions for a specific table, and
+     2. that table is not referenced by the condition, and
+     3. exclude constant conditions not checked at optimization time if
         the table we are pushing conditions to is the first one.
         As a result, such conditions are not considered as already checked
         and will be checked at execution time, attached to the first table.
-
+  */
+  if (used_table &&                                                 // 1
+      !(cond->used_tables() & used_table) &&                        // 2
+      /*
         psergey: TODO: "used_table & 1" doesn't make sense in nearly any
         context. Look at setup_table_map(), table bits reflect the order 
         the tables were encountered by the parser. Check what we should
         replace this condition with.
       */
-      !((used_table & 1) && cond->is_expensive()))
-    return (Item*) 0;				// Already checked
+      !((used_table & 1) && cond->is_expensive()))                  // 3
+    return NULL;
+
   if (cond->type() == Item::COND_ITEM)
   {
     if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
     {
       /* Create new top level AND item */
-      Item_cond_and *new_cond=new Item_cond_and;
+      Item_cond_and *new_cond= new Item_cond_and;
       if (!new_cond)
-	return (Item*) 0;			// OOM /* purecov: inspected */
+        return NULL;
       List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
       Item *item;
-      while ((item=li++))
+      while ((item= li++))
       {
-	Item *fix=make_cond_for_table_from_pred(root_cond, item, 
-                                                tables, used_table,
-                                                exclude_expensive_cond);
-	if (fix)
-	  new_cond->argument_list()->push_back(fix);
+        Item *fix= make_cond_for_table_from_pred(root_cond, item, 
+                                                 tables, used_table,
+                                                 exclude_expensive_cond);
+        if (fix)
+          new_cond->argument_list()->push_back(fix);
       }
       switch (new_cond->argument_list()->elements) {
       case 0:
-	return (Item*) 0;			// Always true
+        return NULL;                          // Always true
       case 1:
-	return new_cond->argument_list()->head();
+        return new_cond->argument_list()->head();
       default:
-	/*
-	  Item_cond_and do not need fix_fields for execution, its parameters
-	  are fixed or do not need fix_fields, too
-	*/
-	new_cond->quick_fix_field();
-	new_cond->used_tables_cache=
-	  ((Item_cond_and*) cond)->used_tables_cache &
-	  tables;
-	return new_cond;
+        /*
+          Item_cond_and do not need fix_fields for execution, its parameters
+          are fixed or do not need fix_fields, too
+        */
+        new_cond->quick_fix_field();
+        new_cond->used_tables_cache=
+          ((Item_cond_and*) cond)->used_tables_cache & tables;
+          return new_cond;
       }
     }
     else
-    {						// Or list
-      Item_cond_or *new_cond=new Item_cond_or;
+    {                                         // Or list
+      Item_cond_or *new_cond= new Item_cond_or;
       if (!new_cond)
-	return (Item*) 0;			// OOM /* purecov: inspected */
+        return NULL;
       List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
       Item *item;
-      while ((item=li++))
+      while ((item= li++))
       {
-	Item *fix=make_cond_for_table_from_pred(root_cond, item,
-                                                tables, 0L,
-                                                exclude_expensive_cond);
+        Item *fix= make_cond_for_table_from_pred(root_cond, item,
+                                                 tables, 0L,
+                                                 exclude_expensive_cond);
 	if (!fix)
-	  return (Item*) 0;			// Always true
+          return NULL;                        // Always true
 	new_cond->argument_list()->push_back(fix);
       }
       /*
@@ -18963,20 +19014,26 @@ make_cond_for_table_from_pred(Item *root
   }
 
   /*
-    Because the following test takes a while and it can be done
-    table_count times, we mark each item that we have examined with the result
-    of the test
-  */
+    Omit this condition if
+     1. It has been marked as omittable before, or
+     2. Some tables referred by the condition are not available, or
+     3. We are extracting conditions for all tables, the condition is
+        considered 'expensive', and we want to delay evaluation of such 
+        conditions to the execution phase.
+  */
+  if (cond->marker == 3 ||                                             // 1
+      (cond->used_tables() & ~tables) ||                               // 2
+      (!used_table && exclude_expensive_cond && cond->is_expensive())) // 3
+    return NULL;
 
-  if (cond->marker == 3 || (cond->used_tables() & ~tables) ||
-      /*
-        When extracting constant conditions, treat expensive conditions as
-        non-constant, so that they are not evaluated at optimization time.
-      */
-      (!used_table && exclude_expensive_cond && cond->is_expensive()))
-    return (Item*) 0;				// Can't check this yet
-  if (cond->marker == 2 || cond->eq_cmp_result() == Item::COND_OK)
-    return cond;				// Not boolean op
+  /*
+    Extract this condition if
+     1. It has already been marked as applicable, or
+     2. It is not a <comparison predicate> (=, <, >, <=, >=, <=>)
+  */
+  if (cond->marker == 2 ||                                             // 1
+      cond->eq_cmp_result() == Item::COND_OK)                          // 2
+    return cond;
 
   /* 
     Remove equalities that are guaranteed to be true by use of 'ref' access
@@ -19003,20 +19060,16 @@ make_cond_for_table_from_pred(Item *root
   {
     Item *left_item= ((Item_func*) cond)->arguments()[0]->real_item();
     Item *right_item= ((Item_func*) cond)->arguments()[1]->real_item();
-    if (left_item->type() == Item::FIELD_ITEM &&
-	test_if_ref(root_cond, (Item_field*) left_item,right_item))
+    if ((left_item->type() == Item::FIELD_ITEM &&
+         test_if_ref(root_cond, (Item_field*) left_item, right_item)) ||
+        (right_item->type() == Item::FIELD_ITEM &&
+         test_if_ref(root_cond, (Item_field*) right_item, left_item)))
     {
-      cond->marker=3;			// Checked when read
-      return (Item*) 0;
-    }
-    if (right_item->type() == Item::FIELD_ITEM &&
-	test_if_ref(root_cond, (Item_field*) right_item,left_item))
-    {
-      cond->marker=3;			// Checked when read
-      return (Item*) 0;
+      cond->marker= 3;                   // Condition can be omitted
+      return NULL;
     }
   }
-  cond->marker=2;
+  cond->marker= 2;                      // Mark condition as applicable
   return cond;
 }
 
@@ -19030,74 +19083,84 @@ make_cond_for_table_from_pred(Item *root
   @param sjm_tables Tables within the semi-join nest (the inner part).
 
   @retval <>NULL Generated condition
-  @retval = NULL Already checked, or error
+  @retval = NULL Already checked, OR error
 
   @details
-  A regular semi-join materialization is always non-correlated, ie
+  A semijoin materialization with lookup is always non-correlated, ie
   the subquery is always resolved by performing a lookup generated in
-  create_subquery_equalities, hence this function should never produce
-  any condition for regular semi-join materialization.
-  For a scan semi-join materialization, this function may return a condition
-  to be checked.
+  create_subquery_equalities, hence this function never needs to produce
+  any condition for it.
+  For a scan semijoin materialization, this function may return a condition
+  to be checked, when there are outer tables before the SJM tables in the
+  join prefix.
+
+  @note
+    Make sure to keep the implementations of make_cond_for_table() and
+    make_cond_after_sjm() synchronized.
 */
 
 static Item *
 make_cond_after_sjm(Item *root_cond, Item *cond, table_map tables,
                     table_map sjm_tables)
 {
+  /*
+    We can only test conditions that cover tables from the join prefix
+    and tables from the semijoin nest. Other conditions will be handled
+    by make_cond_for_table().
+  */
   if ((!(cond->used_tables() & ~tables) || 
        !(cond->used_tables() & ~sjm_tables)))
-    return (Item*) 0;				// Already checked
+    return NULL;
+
   if (cond->type() == Item::COND_ITEM)
   {
     if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
     {
       /* Create new top level AND item */
-      Item_cond_and *new_cond=new Item_cond_and;
+      Item_cond_and *new_cond= new Item_cond_and;
       if (!new_cond)
-	return (Item*) 0;			// OOM /* purecov: inspected */
+        return NULL;
       List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
       Item *item;
-      while ((item=li++))
+      while ((item= li++))
       {
-	Item *fix=make_cond_after_sjm(root_cond, item, tables, sjm_tables);
-	if (fix)
-	  new_cond->argument_list()->push_back(fix);
+        Item *fix= make_cond_after_sjm(root_cond, item, tables, sjm_tables);
+        if (fix)
+          new_cond->argument_list()->push_back(fix);
       }
       switch (new_cond->argument_list()->elements) {
       case 0:
-	return (Item*) 0;			// Always true
+        return NULL;                    // Always true
       case 1:
-	return new_cond->argument_list()->head();
+        return new_cond->argument_list()->head();
       default:
 	/*
-	  Item_cond_and do not need fix_fields for execution, its parameters
-	  are fixed or do not need fix_fields, too
+          Item_cond_and do not need fix_fields for execution, its parameters
+          are fixed or do not need fix_fields, too
 	*/
-	new_cond->quick_fix_field();
-	new_cond->used_tables_cache=
-	  ((Item_cond_and*) cond)->used_tables_cache &
-	  tables;
-	return new_cond;
+        new_cond->quick_fix_field();
+        new_cond->used_tables_cache=
+          ((Item_cond_and*) cond)->used_tables_cache & tables;
+        return new_cond;
       }
     }
     else
-    {						// Or list
-      Item_cond_or *new_cond=new Item_cond_or;
+    {                                          // Or list
+      Item_cond_or *new_cond= new Item_cond_or;
       if (!new_cond)
-	return (Item*) 0;			// OOM /* purecov: inspected */
+        return NULL;
       List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
       Item *item;
-      while ((item=li++))
+      while ((item= li++))
       {
-	Item *fix= make_cond_after_sjm(root_cond, item, tables, 0L);
-	if (!fix)
-	  return (Item*) 0;			// Always true
-	new_cond->argument_list()->push_back(fix);
+        Item *fix= make_cond_after_sjm(root_cond, item, tables, 0L);
+        if (!fix)
+          return NULL;                  // Always true
+        new_cond->argument_list()->push_back(fix);
       }
       /*
-	Item_cond_and do not need fix_fields for execution, its parameters
-	are fixed or do not need fix_fields, too
+        Item_cond_and do not need fix_fields for execution, its parameters
+        are fixed or do not need fix_fields, too
       */
       new_cond->quick_fix_field();
       new_cond->used_tables_cache= ((Item_cond_or*) cond)->used_tables_cache;
@@ -19107,15 +19170,22 @@ make_cond_after_sjm(Item *root_cond, Ite
   }
 
   /*
-    Because the following test takes a while and it can be done
-    table_count times, we mark each item that we have examined with the result
-    of the test
+    Omit this condition if
+     1. It has been marked as omittable before, or
+     2. Some tables referred by the condition are not available.
   */
+  if (cond->marker == 3 ||                                             // 1
+      cond->used_tables() & ~(tables | sjm_tables))                    // 2
+    return NULL;
 
-  if (cond->marker == 3 || (cond->used_tables() & ~(tables | sjm_tables)))
-    return (Item*) 0;				// Can't check this yet
-  if (cond->marker == 2 || cond->eq_cmp_result() == Item::COND_OK)
-    return cond;				// Not boolean op
+  /*
+    Extract this condition if
+     1. It has already been marked as applicable, or
+     2. It is not a <comparison predicate> (=, <, >, <=, >=, <=>)
+  */
+  if (cond->marker == 2 ||                                             // 1
+      cond->eq_cmp_result() == Item::COND_OK)                          // 2
+    return cond;
 
   /* 
     Remove equalities that are guaranteed to be true by use of 'ref' access
@@ -19123,22 +19193,18 @@ make_cond_after_sjm(Item *root_cond, Ite
   */
   if (((Item_func*) cond)->functype() == Item_func::EQ_FUNC)
   {
-    Item *left_item=	((Item_func*) cond)->arguments()[0]->real_item();
+    Item *left_item= ((Item_func*) cond)->arguments()[0]->real_item();
     Item *right_item= ((Item_func*) cond)->arguments()[1]->real_item();
-    if (left_item->type() == Item::FIELD_ITEM &&
-	test_if_ref(root_cond, (Item_field*) left_item,right_item))
+    if ((left_item->type() == Item::FIELD_ITEM &&
+	 test_if_ref(root_cond, (Item_field*) left_item, right_item)) ||
+        (right_item->type() == Item::FIELD_ITEM &&
+	 test_if_ref(root_cond, (Item_field*) right_item, left_item)))
     {
-      cond->marker=3;			// Checked when read
-      return (Item*) 0;
-    }
-    if (right_item->type() == Item::FIELD_ITEM &&
-	test_if_ref(root_cond, (Item_field*) right_item,left_item))
-    {
-      cond->marker=3;			// Checked when read
-      return (Item*) 0;
+      cond->marker= 3;                  // Condition can be omitted
+      return NULL;
     }
   }
-  cond->marker=2;
+  cond->marker= 2;                      // Mark condition as applicable
   return cond;
 }
 


Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20101026104350-eawnur5qjrsp0342.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3269) Bug#50019Roy Lyseng26 Oct