List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:March 15 2012 2:42pm
Subject:bzr push into mysql-trunk branch (roy.lyseng:3783 to 3784) Bug#13468414
View as plain text  
 3784 Roy Lyseng	2012-03-15
      Bug#13468414: Query shows different results when literals are selected from
                    separate table.
      
      The problem occurs when cache_const_exprs() adds cache objects to the
      query condition. It attempts to build a cache object on top of an
      Item_ref object, but ends up building it on top of the above item, which
      is an equality check, and should not be const. Hence, the evaluation
      of the first equality is used for every row in the subquery, allowing
      every IN subquery predicate to evaluate to true instead of false.
      
      This occurs because compile() returns NULL when the analyzer sets
      cache_flag=True for an Item_direct_ref object. The analyzer for the
      descendent object (an Item_cache) then returns false, which makes
      compile return NULL, which eventually makes the above object (an
      Item_func_eq) be wrapped in an Item_cache object.
      
      The fix for this is to distinguish error handling and false return
      from the analyzer function better: In compile(), when the analyzer
      returns false, return "this" (current return value is NULL).
      Also let all NULL returns represent an error.
      
      Other problems have also been fixed:
      - A cache object might be built on top of another cache, which is
        useless.
      - cache_const_exprs() was earlier called before make_join_select(),
        then was changed (in some unidentified change set) to be called
        after it, and thus, at a stage where conditions have been
        propagated to individual tables. So we should apply the transformer
        to those conditions, instead of to conds and the JOIN conditions.
      - cache_const_exprs() did not have proper error handling, it now returns
        true when out-of-memory.
      
      mysql-test/include/subquery.inc
        Test case for bug#13468414.
        Test case for bug#13735704, which was marked as a duplicate.
      
      mysql-test/r/explain_json_all.result
      mysql-test/r/explain_json_none.result
        Redundant cache entries are now removed.
      
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_bka.result
      mysql-test/r/subquery_all_bka_nixbnl.result
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_nomat_nosj_bka.result
      mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
      mysql-test/r/subquery_none.result
      mysql-test/r/subquery_none_bka.result
      mysql-test/r/subquery_none_bka_nixbnl.result
        Test case results for bug#13468414.
        Test case results for bug#13735704.
        Few minor changes for explain extended: In one case, a cache object
        had been added to an expression in conds, that already had been
        optimized away when propagating conditions to individual tables.
        Explain extended reports from the WHERE condition, and not from
        the propagated conditions. Adding the cache object was obviously
        redundant.
      
      sql/item.cc
        In Item::cache_const_expr_analyzer(), do not build a cache object
        on top of another cache object.
        In Item_ref::compile, return "this" even when analyzer reports false.
        replaced the bool flag with an Item * pointer, so that we assert
        that the Item * is equal to "this" when appropriate.
      
      sql/item.h
        In Item::compile, return "this" even when analyzer reports false.
      
      sql/item_cmpfunc.cc
        In Item_cond::compile, return "this" even when analyzer reports false.
        Return NULL when compile() on sub-item reports error.
      
      sql/item_func.cc
        In Item_func::compile, return "this" even when analyzer reports false.
        Return NULL when compile() on sub-item reports error.
      
      sql/sql_optimizer.cc
        In JOIN::cache_const_exprs(), apply transform to conditions pushed
        down to tables, instead of to conds and join conditions.
        Let the analyzer/transformer argument be an Item * instead of a bool.
        Add error handling.
      
      sql/sql_optimizer.h
        Interface change for JOIN::cache_const_exprs().

    modified:
      mysql-test/include/subquery.inc
      mysql-test/r/explain_json_all.result
      mysql-test/r/explain_json_none.result
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_bka.result
      mysql-test/r/subquery_all_bka_nixbnl.result
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_nomat_nosj_bka.result
      mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
      mysql-test/r/subquery_none.result
      mysql-test/r/subquery_none_bka.result
      mysql-test/r/subquery_none_bka_nixbnl.result
      sql/item.cc
      sql/item.h
      sql/item_cmpfunc.cc
      sql/item_func.cc
      sql/sql_optimizer.cc
      sql/sql_optimizer.h
 3783 Roy Lyseng	2012-03-15
      Bug#13773979: Missing rows on second execution of prepared statement
      
      This problem may occur with prepared statements that reference views
      and perform semi-join transformation.
      A field reference through a view is replaced during resolving for each
      execution of the prepared statement. Semi-join transformation occurs
      after resolving, so the fix_after_pullout() stage applied to all
      Item_field objects will not be carried out. fix_after_pullout() builds
      a new resolver context for the fields, but obviously this is not updated
      for the replaced field. On second resolving, the select_lex context
      refers to the deleted select_lex and it appears that the field comes
      from an outer table.
      
      The fix is to update the select_lex pointer in the context object
      that is contained in the select_lex object which will be removed,
      to point to the "parent" select_lex object.
      This context object is used for every field reference that is not
      a join condition. I checked with view references in join conditions as
      well, but apparently these do not cause any trouble.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#13773979.
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
        Added test case results for bug#13773979.
      
      sql/item.cc
        Removed an assert because it no longer holds when resolver context
        is updated earlier.
      
      sql/sql_optimizer.cc
        In convert_subquery_to_semijoin(), update the select_lex pointer in
        the context object that is contained in the deleted select_lex object,
        to point to the "parent" select_lex object.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
      sql/item.cc
      sql/sql_optimizer.cc
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc	2012-03-08 09:44:21 +0000
+++ b/mysql-test/include/subquery.inc	2012-03-15 14:41:06 +0000
@@ -5890,3 +5890,101 @@ WHERE d LIKE '_'
 ORDER BY d;
 
 DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug#13468414 Query shows different results when literals are selected
+--echo #              from separate table
+--echo #
+
+CREATE TABLE t1 (
+  col_varchar_key varchar(1) NOT NULL,
+  col_varchar_nokey varchar(1) NOT NULL,
+  KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+ ('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+ ('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+ ('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+ ('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+
+CREATE TABLE t2 (
+  col_int_nokey INT NOT NULL,
+  col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES
+ (4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+ (3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+ (1,'q'), (6,'w'), (2,'d'), (9,'e');
+
+CREATE TABLE t0 (
+ i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+ (SELECT it2.col_int_nokey
+  FROM t2 AS it2 LEFT JOIN t1 AS it1
+       ON it2.col_varchar_nokey = it1.col_varchar_key
+ );
+
+let $query=
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+ (SELECT it2.col_int_nokey
+  FROM t2 AS it2 LEFT JOIN t1 AS it1
+       ON it2.col_varchar_nokey = it1.col_varchar_key
+ );
+
+eval $query;
+eval explain extended $query;
+
+DROP TABLE t0, t1, t2;
+
+--echo #
+--echo # Bug#13735704 Memory engine + NOT IN + subquery produces different
+--echo # result set
+--echo #
+
+CREATE TABLE t1(
+ col_varchar_key varchar(1),
+ KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+ ('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+
+CREATE TABLE t2(
+ col_int_nokey int,
+ col_int_key int,
+ KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+ (4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+ (156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+ (7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+ (6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+ (1,7), (9,2), (0,1), (6,5);
+
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+   (SELECT col_int_key,col_int_nokey FROM t2);
+
+let $query=
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+       (SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+    (SELECT col_int_key,col_int_nokey FROM t2);
+
+eval explain extended $query;
+eval $query;
+
+DROP TABLE t0, t1, t2;

=== modified file 'mysql-test/r/explain_json_all.result'
--- a/mysql-test/r/explain_json_all.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/explain_json_all.result	2012-03-15 14:41:06 +0000
@@ -750,7 +750,7 @@ EXPLAIN
                   "access_type": "ALL",
                   "rows": 2,
                   "filtered": 100,
-                  "attached_condition": "(`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, (<cache>((<cache>((select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`)) or isnull(`test`.`t3`.`e`)), true))"
+                  "attached_condition": "(`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, ((<cache>((select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or isnull(`test`.`t3`.`e`)), true))"
                 } /* table */
               } /* query_block */
             },
@@ -786,7 +786,7 @@ EXPLAIN
 }
 Warnings:
 Note	1276	Field or reference 'test.t1.b' of SELECT #4 was resolved in SELECT #1
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<in_optimizer>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`),<exists>(/* select#4 */ select 1 from `test`.`t3` where (`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, (<cache>((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`)) or isnull(`test`.`t3`.`e`)), true)) having trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`e`), true)))))
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<in_optimizer>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`),<exists>(/* select#4 */ select 1 from `test`.`t3` where (`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or isnull(`test`.`t3`.`e`)), true)) having trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`e`), true)))))
 DROP TABLE t1, t2, t3, t4;
 # semi-join materialization (if enabled)
 CREATE TABLE t1 (a INT);

=== modified file 'mysql-test/r/explain_json_none.result'
--- a/mysql-test/r/explain_json_none.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/explain_json_none.result	2012-03-15 14:41:06 +0000
@@ -761,7 +761,7 @@ EXPLAIN
                       "access_type": "ALL",
                       "rows": 2,
                       "filtered": 100,
-                      "attached_condition": "(`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, (<cache>((<cache>((select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`)) or isnull(`test`.`t3`.`e`)), true))"
+                      "attached_condition": "(`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, ((<cache>((select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or isnull(`test`.`t3`.`e`)), true))"
                     } /* table */
                   } /* query_block */
                 },
@@ -788,7 +788,7 @@ EXPLAIN
 }
 Warnings:
 Note	1276	Field or reference 'test.t1.b' of SELECT #4 was resolved in SELECT #1
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where (<nop>(<in_optimizer>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`),<exists>(/* select#4 */ select 1 from `test`.`t3` where (`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, (<cache>((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`)) or isnull(`test`.`t3`.`e`)), true)) having trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`e`), true)))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))))
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where (<nop>(<in_optimizer>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`),<exists>(/* select#4 */ select 1 from `test`.`t3` where (`test`.`t1`.`b` and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or isnull(`test`.`t3`.`e`)), true)) having trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`e`), true)))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))))
 DROP TABLE t1, t2, t3, t4;
 # semi-join materialization (if enabled)
 CREATE TABLE t1 (a INT);

=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result	2012-03-08 09:44:21 +0000
+++ b/mysql-test/r/subquery_all.result	2012-03-15 14:41:06 +0000
@@ -736,7 +736,7 @@ id	select_type	table	type	possible_keys	
 Warnings:
 Note	1249	Select 3 was reduced during optimization
 Note	1249	Select 2 was reduced during optimization
-Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	NULL	id	5	NULL	2	100.00	Using where; Using index
@@ -6789,7 +6789,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	it	ref	idx_cvk_cik	idx_cvk_cik	4	const	1	100.00	Using index; FirstMatch(ot)
 Warnings:
 Note	1276	Field or reference 'test.ot.col_int_nokey' of SELECT #2 was resolved in SELECT #1
-Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from (`test`.`it`) where ((`test`.`it`.`col_varchar_key` = 'x') and <cache>(coalesce('1',1)))
+Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from (`test`.`it`) where ((`test`.`it`.`col_varchar_key` = 'x') and coalesce('1',1))
 DROP TABLE it;
 CREATE TABLE it (
 col_int_key int(11),
@@ -7171,4 +7171,108 @@ WHERE d LIKE '_'
 ORDER BY d;
 d
 DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+#              from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where
+3	SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	20	100.00	NULL
+3	SUBQUERY	it1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	100.00	Using index
+2	SUBQUERY	t0	system	PRIMARY	NULL	NULL	NULL	1	100.00	NULL
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),(/* select#2 */ select '7' from dual where 1) in ( <materialize> (/* select#3 */ select `test`.`it2`.`col_int_nokey` from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it2`.`col_varchar_nokey` = `test`.`it1`.`col_varchar_key`)) where 1 ), <primary_index_lookup>(<cache>('7') in <temporary table> on distinct_key where ((<cache>('7') = `materialized subselect`.`col_int_nokey`)))))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+4	DEPENDENT SUBQUERY	t2	index_subquery	col_int_key	col_int_key	5	const	4	100.00	Using where; Full scan on NULL key
+3	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+2	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_all_bka.result'
--- a/mysql-test/r/subquery_all_bka.result	2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_all_bka.result	2012-03-15 14:41:06 +0000
@@ -737,7 +737,7 @@ id	select_type	table	type	possible_keys	
 Warnings:
 Note	1249	Select 3 was reduced during optimization
 Note	1249	Select 2 was reduced during optimization
-Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	NULL	id	5	NULL	2	100.00	Using where; Using index
@@ -6790,7 +6790,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	it	ref	idx_cvk_cik	idx_cvk_cik	4	const	1	100.00	Using index; FirstMatch(ot)
 Warnings:
 Note	1276	Field or reference 'test.ot.col_int_nokey' of SELECT #2 was resolved in SELECT #1
-Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from (`test`.`it`) where ((`test`.`it`.`col_varchar_key` = 'x') and <cache>(coalesce('1',1)))
+Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from (`test`.`it`) where ((`test`.`it`.`col_varchar_key` = 'x') and coalesce('1',1))
 DROP TABLE it;
 CREATE TABLE it (
 col_int_key int(11),
@@ -7172,5 +7172,109 @@ WHERE d LIKE '_'
 ORDER BY d;
 d
 DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+#              from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where
+3	SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	20	100.00	NULL
+3	SUBQUERY	it1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	100.00	Using index
+2	SUBQUERY	t0	system	PRIMARY	NULL	NULL	NULL	1	100.00	NULL
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),(/* select#2 */ select '7' from dual where 1) in ( <materialize> (/* select#3 */ select `test`.`it2`.`col_int_nokey` from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it2`.`col_varchar_nokey` = `test`.`it1`.`col_varchar_key`)) where 1 ), <primary_index_lookup>(<cache>('7') in <temporary table> on distinct_key where ((<cache>('7') = `materialized subselect`.`col_int_nokey`)))))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+4	DEPENDENT SUBQUERY	t2	index_subquery	col_int_key	col_int_key	5	const	4	100.00	Using where; Full scan on NULL key
+3	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+2	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_all_bka_nixbnl.result	2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_all_bka_nixbnl.result	2012-03-15 14:41:06 +0000
@@ -737,7 +737,7 @@ id	select_type	table	type	possible_keys	
 Warnings:
 Note	1249	Select 3 was reduced during optimization
 Note	1249	Select 2 was reduced during optimization
-Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	NULL	id	5	NULL	2	100.00	Using where; Using index
@@ -6790,7 +6790,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	it	ref	idx_cvk_cik	idx_cvk_cik	4	const	1	100.00	Using index; FirstMatch(ot)
 Warnings:
 Note	1276	Field or reference 'test.ot.col_int_nokey' of SELECT #2 was resolved in SELECT #1
-Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from (`test`.`it`) where ((`test`.`it`.`col_varchar_key` = 'x') and <cache>(coalesce('1',1)))
+Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from (`test`.`it`) where ((`test`.`it`.`col_varchar_key` = 'x') and coalesce('1',1))
 DROP TABLE it;
 CREATE TABLE it (
 col_int_key int(11),
@@ -7172,5 +7172,109 @@ WHERE d LIKE '_'
 ORDER BY d;
 d
 DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+#              from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where
+3	SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	20	100.00	NULL
+3	SUBQUERY	it1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	100.00	Using index
+2	SUBQUERY	t0	system	PRIMARY	NULL	NULL	NULL	1	100.00	NULL
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),(/* select#2 */ select '7' from dual where 1) in ( <materialize> (/* select#3 */ select `test`.`it2`.`col_int_nokey` from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it2`.`col_varchar_nokey` = `test`.`it1`.`col_varchar_key`)) where 1 ), <primary_index_lookup>(<cache>('7') in <temporary table> on distinct_key where ((<cache>('7') = `materialized subselect`.`col_int_nokey`)))))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+4	DEPENDENT SUBQUERY	t2	index_subquery	col_int_key	col_int_key	5	const	4	100.00	Using where; Full scan on NULL key
+3	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+2	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result	2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result	2012-03-15 14:41:06 +0000
@@ -736,7 +736,7 @@ id	select_type	table	type	possible_keys	
 Warnings:
 Note	1249	Select 3 was reduced during optimization
 Note	1249	Select 2 was reduced during optimization
-Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	NULL	id	5	NULL	2	100.00	Using where; Using index
@@ -7171,4 +7171,108 @@ WHERE d LIKE '_'
 ORDER BY d;
 d
 DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+#              from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where
+3	DEPENDENT SUBQUERY	it1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	100.00	Using index
+2	SUBQUERY	t0	system	PRIMARY	NULL	NULL	NULL	1	100.00	NULL
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),<exists>(/* select#3 */ select 1 from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it1`.`col_varchar_key` = `test`.`it2`.`col_varchar_nokey`)) where trigcond_if(outer_field_is_not_null, (<cache>((/* select#2 */ select '7' from dual where 1)) = `test`.`it2`.`col_int_nokey`), true)))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+4	DEPENDENT SUBQUERY	t2	index_subquery	col_int_key	col_int_key	5	const	4	100.00	Using where; Full scan on NULL key
+3	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+2	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_nomat_nosj_bka.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka.result	2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka.result	2012-03-15 14:41:06 +0000
@@ -737,7 +737,7 @@ id	select_type	table	type	possible_keys	
 Warnings:
 Note	1249	Select 3 was reduced during optimization
 Note	1249	Select 2 was reduced during optimization
-Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	NULL	id	5	NULL	2	100.00	Using where; Using index
@@ -7172,5 +7172,109 @@ WHERE d LIKE '_'
 ORDER BY d;
 d
 DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+#              from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where
+3	DEPENDENT SUBQUERY	it1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	100.00	Using index
+2	SUBQUERY	t0	system	PRIMARY	NULL	NULL	NULL	1	100.00	NULL
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),<exists>(/* select#3 */ select 1 from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it1`.`col_varchar_key` = `test`.`it2`.`col_varchar_nokey`)) where trigcond_if(outer_field_is_not_null, (<cache>((/* select#2 */ select '7' from dual where 1)) = `test`.`it2`.`col_int_nokey`), true)))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+4	DEPENDENT SUBQUERY	t2	index_subquery	col_int_key	col_int_key	5	const	4	100.00	Using where; Full scan on NULL key
+3	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+2	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result	2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result	2012-03-15 14:41:06 +0000
@@ -737,7 +737,7 @@ id	select_type	table	type	possible_keys	
 Warnings:
 Note	1249	Select 3 was reduced during optimization
 Note	1249	Select 2 was reduced during optimization
-Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	NULL	id	5	NULL	2	100.00	Using where; Using index
@@ -7172,5 +7172,109 @@ WHERE d LIKE '_'
 ORDER BY d;
 d
 DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+#              from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where
+3	DEPENDENT SUBQUERY	it1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	100.00	Using index
+2	SUBQUERY	t0	system	PRIMARY	NULL	NULL	NULL	1	100.00	NULL
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),<exists>(/* select#3 */ select 1 from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it1`.`col_varchar_key` = `test`.`it2`.`col_varchar_nokey`)) where trigcond_if(outer_field_is_not_null, (<cache>((/* select#2 */ select '7' from dual where 1)) = `test`.`it2`.`col_int_nokey`), true)))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+4	DEPENDENT SUBQUERY	t2	index_subquery	col_int_key	col_int_key	5	const	4	100.00	Using where; Full scan on NULL key
+3	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+2	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result	2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_none.result	2012-03-15 14:41:06 +0000
@@ -735,7 +735,7 @@ id	select_type	table	type	possible_keys	
 Warnings:
 Note	1249	Select 3 was reduced during optimization
 Note	1249	Select 2 was reduced during optimization
-Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	NULL	id	5	NULL	2	100.00	Using where; Using index
@@ -7170,4 +7170,108 @@ WHERE d LIKE '_'
 ORDER BY d;
 d
 DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+#              from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where
+3	DEPENDENT SUBQUERY	it1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	100.00	Using index
+2	SUBQUERY	t0	system	PRIMARY	NULL	NULL	NULL	1	100.00	NULL
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),<exists>(/* select#3 */ select 1 from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it1`.`col_varchar_key` = `test`.`it2`.`col_varchar_nokey`)) where trigcond_if(outer_field_is_not_null, (<cache>((/* select#2 */ select '7' from dual where 1)) = `test`.`it2`.`col_int_nokey`), true)))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+4	DEPENDENT SUBQUERY	t2	index_subquery	col_int_key	col_int_key	5	const	4	100.00	Using where; Full scan on NULL key
+3	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+2	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none_bka.result'
--- a/mysql-test/r/subquery_none_bka.result	2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_none_bka.result	2012-03-15 14:41:06 +0000
@@ -736,7 +736,7 @@ id	select_type	table	type	possible_keys	
 Warnings:
 Note	1249	Select 3 was reduced during optimization
 Note	1249	Select 2 was reduced during optimization
-Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	NULL	id	5	NULL	2	100.00	Using where; Using index
@@ -7171,5 +7171,109 @@ WHERE d LIKE '_'
 ORDER BY d;
 d
 DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+#              from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where
+3	DEPENDENT SUBQUERY	it1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	100.00	Using index
+2	SUBQUERY	t0	system	PRIMARY	NULL	NULL	NULL	1	100.00	NULL
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),<exists>(/* select#3 */ select 1 from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it1`.`col_varchar_key` = `test`.`it2`.`col_varchar_nokey`)) where trigcond_if(outer_field_is_not_null, (<cache>((/* select#2 */ select '7' from dual where 1)) = `test`.`it2`.`col_int_nokey`), true)))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+4	DEPENDENT SUBQUERY	t2	index_subquery	col_int_key	col_int_key	5	const	4	100.00	Using where; Full scan on NULL key
+3	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+2	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_none_bka_nixbnl.result	2012-03-07 08:01:17 +0000
+++ b/mysql-test/r/subquery_none_bka_nixbnl.result	2012-03-15 14:41:06 +0000
@@ -736,7 +736,7 @@ id	select_type	table	type	possible_keys	
 Warnings:
 Note	1249	Select 3 was reduced during optimization
 Note	1249	Select 2 was reduced during optimization
-Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))
+Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	NULL	id	5	NULL	2	100.00	Using where; Using index
@@ -7171,5 +7171,109 @@ WHERE d LIKE '_'
 ORDER BY d;
 d
 DROP TABLE t1, t2;
+#
+# Bug#13468414 Query shows different results when literals are selected
+#              from separate table
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('v','v'), ('s','s'), ('l','l'), ('y','y'), ('c','c'),
+('i','i'), ('h','h'), ('q','q'), ('a','a'), ('v','v'),
+('u','u'), ('s','s'), ('y','y'), ('z','z'), ('h','h'),
+('p','p'), ('e','e'), ('i','i'), ('y','y'), ('w','w');
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+CREATE TABLE t0 (
+i1 INTEGER NOT NULL PRIMARY KEY
+);
+INSERT INTO t0 VALUES (7);
+SELECT col_varchar_nokey
+FROM t1
+WHERE (7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+col_varchar_nokey
+explain extended SELECT col_varchar_nokey
+FROM t1
+WHERE (SELECT i1 FROM t0 WHERE i1 = 7) NOT IN
+(SELECT it2.col_int_nokey
+FROM t2 AS it2 LEFT JOIN t1 AS it1
+ON it2.col_varchar_nokey = it1.col_varchar_key
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where
+3	DEPENDENT SUBQUERY	it1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	100.00	Using index
+2	SUBQUERY	t0	system	PRIMARY	NULL	NULL	NULL	1	100.00	NULL
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` where (not(<in_optimizer>((/* select#2 */ select '7' from dual where 1),<exists>(/* select#3 */ select 1 from `test`.`t2` `it2` left join `test`.`t1` `it1` on((`test`.`it1`.`col_varchar_key` = `test`.`it2`.`col_varchar_nokey`)) where trigcond_if(outer_field_is_not_null, (<cache>((/* select#2 */ select '7' from dual where 1)) = `test`.`it2`.`col_int_nokey`), true)))))
+DROP TABLE t0, t1, t2;
+#
+# Bug#13735704 Memory engine + NOT IN + subquery produces different
+# result set
+#
+CREATE TABLE t1(
+col_varchar_key varchar(1),
+KEY col_varchar_key(col_varchar_key)
+) engine=MEMORY;
+INSERT INTO t1 VALUES
+('v'), ('s'), ('y'), ('z'), ('h'), ('p');
+CREATE TABLE t2(
+col_int_nokey int,
+col_int_key int,
+KEY col_int_key(col_int_key)
+) engine=MEMORY;
+INSERT INTO t2 VALUES
+(4,4), (2,7), (2,5), (2,3), (197,188), (4,4), (6,2), (1,4),
+(156,231), (2,1), (1,2), (80,194), (3,2), (8,3), (91,98), (6,3),
+(7,1), (3,0), (161,189), (7,8), (7,3), (213,120), (248,25), (1,1),
+(6,3), (6,1), (3,3), (140,153), (0,5), (7,9), (6,1), (1,2),
+(1,7), (9,2), (0,1), (6,5);
+CREATE TABLE t0(i1 INTEGER NOT NULL PRIMARY KEY);
+INSERT INTO t0 VALUES(7),(2);
+SELECT col_varchar_key
+FROM t1
+WHERE (7,2) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+explain extended SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+4	DEPENDENT SUBQUERY	t2	index_subquery	col_int_key	col_int_key	5	const	4	100.00	Using where; Full scan on NULL key
+3	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+2	SUBQUERY	t0	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where (not(<in_optimizer>(((/* select#2 */ select '7' from `test`.`t0` where 1),(/* select#3 */ select '2' from `test`.`t0` where 1)),<exists>(<index_lookup>(<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) in t2 on col_int_key checking NULL where (trigcond_if(outer_field_is_not_null, ((<cache>((/* select#2 */ select '7' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_key`) or isnull(`test`.`t2`.`col_int_key`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>((/* select#3 */ select '2' from `test`.`t0` where 1)) = `test`.`t2`.`col_int_nokey`) or isnull(`test`.`t2`.`col_int_nokey`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_key`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`col_int_nokey`), true)))))))
+SELECT col_varchar_key
+FROM t1
+WHERE ((SELECT i1 FROM t0 WHERE i1 = 7),
+(SELECT i1 FROM t0 WHERE i1 = 2)) NOT IN
+(SELECT col_int_key,col_int_nokey FROM t2);
+col_varchar_key
+DROP TABLE t0, t1, t2;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2012-03-15 13:15:19 +0000
+++ b/sql/item.cc	2012-03-15 14:41:06 +0000
@@ -6709,7 +6709,7 @@ bool Item::send(Protocol *protocol, Stri
 /**
   Check if an item is a constant one and can be cached.
 
-  @param arg [out] TRUE <=> Cache this item.
+  @param arg [out] != NULL <=> Cache this item.
 
   @return TRUE  Go deeper in item tree.
   @return FALSE Don't go deeper in item tree.
@@ -6717,18 +6717,19 @@ bool Item::send(Protocol *protocol, Stri
 
 bool Item::cache_const_expr_analyzer(uchar **arg)
 {
-  bool *cache_flag= (bool*)*arg;
-  if (!*cache_flag)
+  Item **cache_item= (Item **)*arg;
+  if (!*cache_item)
   {
     Item *item= real_item();
     /*
       Cache constant items unless it's a basic constant, constant field or
-      a subselect (they use their own cache).
+      a subquery (they use their own cache), or it is already cached.
     */
     if (const_item() &&
         !(basic_const_item() || item->basic_const_item() ||
           item->type() == Item::FIELD_ITEM ||
           item->type() == SUBSELECT_ITEM ||
+          item->type() == CACHE_ITEM ||
            /*
              Do not cache GET_USER_VAR() function as its const_item() may
              return TRUE for the current thread but it still may change
@@ -6736,17 +6737,32 @@ bool Item::cache_const_expr_analyzer(uch
            */
           (item->type() == Item::FUNC_ITEM &&
            ((Item_func*)item)->functype() == Item_func::GUSERVAR_FUNC)))
-      *cache_flag= TRUE;
-    return TRUE;
+      /*
+        Note that we use cache_item as a flag (NULL vs non-NULL), but we
+        are storing the pointer so that we can assert that we cache the
+        correct item in Item::cache_const_expr_transformer().
+      */
+      *cache_item= this;
+    /*
+      If this item will be cached, no need to explore items further down
+      in the tree, but the transformer must be called, so return 'true'.
+      If this item will not be cached, items further doen in the tree
+      must be explored, so return 'true'.
+    */
+    return true;
   }
-  return FALSE;
+  /*
+    An item above in the tree is to be cached, so need to cache the present
+    item, and no need to go down the tree.
+  */
+  return false;
 }
 
 
 /**
   Cache item if needed.
 
-  @param arg   TRUE <=> Cache this item.
+  @param arg   != NULL <=> Cache this item.
 
   @return cache if cache needed.
   @return this otherwise.
@@ -6754,9 +6770,15 @@ bool Item::cache_const_expr_analyzer(uch
 
 Item* Item::cache_const_expr_transformer(uchar *arg)
 {
-  if (*(bool*)arg)
+  Item **item= (Item **)arg;
+  if (*item)            // Item is to be cached, note that it is used as a flag
   {
-    *((bool*)arg)= FALSE;
+    DBUG_ASSERT(*item == this);
+    /*
+      Flag applies to present item, must reset it so it does not affect
+      the parent item.
+    */
+    *((Item **)arg)= NULL;
     Item_cache *cache= Item_cache::get_cache(this);
     if (!cache)
       return NULL;
@@ -7261,18 +7283,19 @@ Item* Item_ref::transform(Item_transform
                        nodes of the tree of the object
   @param arg_t         parameter to be passed to the transformer
 
-  @return Item returned as the result of transformation of the Item_ref object
+  @return Item returned as the result of transformation of the Item_ref object,
+          or NULL if error.
 */
 
 Item* Item_ref::compile(Item_analyzer analyzer, uchar **arg_p,
                         Item_transformer transformer, uchar *arg_t)
 {
   if (!(this->*analyzer)(arg_p))
-    return NULL;
+    return this;
 
   DBUG_ASSERT((*ref) != NULL);
   Item *new_item= (*ref)->compile(analyzer, arg_p, transformer, arg_t);
-  if (!new_item)
+  if (new_item == NULL)
     return NULL;
 
   /*

=== modified file 'sql/item.h'
--- a/sql/item.h	2012-03-06 14:29:42 +0000
+++ b/sql/item.h	2012-03-15 14:41:06 +0000
@@ -1196,19 +1196,23 @@ public:
       if (this->*some_analyzer(...))
       {
         compile children if any;
-        this->*some_transformer(...);
+        return this->*some_transformer(...);
       }
+      else
+        return this;
     }
 
     i.e. analysis is performed top-down while transformation is done
-    bottom-up.      
+    bottom-up. If no transformation is applied, the item is returned unchanged.
+    A transformation error is indicated by returning a NULL pointer. Notice
+    that the analyzer function should never cause an error.
   */
   virtual Item* compile(Item_analyzer analyzer, uchar **arg_p,
                         Item_transformer transformer, uchar *arg_t)
   {
     if ((this->*analyzer) (arg_p))
       return ((this->*transformer) (arg_t));
-    return 0;
+    return this;
   }
 
    virtual void traverse_cond(Cond_traverser traverser,

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2012-03-06 14:29:42 +0000
+++ b/sql/item_cmpfunc.cc	2012-03-15 14:41:06 +0000
@@ -4922,15 +4922,16 @@ Item *Item_cond::transform(Item_transfor
                        nodes of the tree of the object
   @param arg_t         parameter to be passed to the transformer
 
-  @return
-    Item returned as the result of transformation of the root node 
+  @return              Item returned as result of transformation of the node,
+                       the same item if no transformation applied, or NULL if
+                       transformation caused an error.
 */
 
 Item *Item_cond::compile(Item_analyzer analyzer, uchar **arg_p,
                          Item_transformer transformer, uchar *arg_t)
 {
   if (!(this->*analyzer)(arg_p))
-    return 0;
+    return this;
   
   List_iterator<Item> li(list);
   Item *item;
@@ -4942,7 +4943,9 @@ Item *Item_cond::compile(Item_analyzer a
     */   
     uchar *arg_v= *arg_p;
     Item *new_item= item->compile(analyzer, &arg_v, transformer, arg_t);
-    if (new_item && new_item != item)
+    if (new_item == NULL)
+      return NULL;
+    if (new_item != item)
       current_thd->change_item_tree(li.ref(), new_item);
   }
   return Item_func::transform(transformer, arg_t);

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2012-03-14 09:52:49 +0000
+++ b/sql/item_func.cc	2012-03-15 14:41:06 +0000
@@ -371,15 +371,16 @@ Item *Item_func::transform(Item_transfor
                        nodes of the tree of the object
   @param arg_t         parameter to be passed to the transformer
 
-  @return
-    Item returned as the result of transformation of the root node
+  @return              Item returned as result of transformation of the node,
+                       the same item if no transformation applied, or NULL if
+                       transformation caused an error.
 */
 
 Item *Item_func::compile(Item_analyzer analyzer, uchar **arg_p,
                          Item_transformer transformer, uchar *arg_t)
 {
   if (!(this->*analyzer)(arg_p))
-    return 0;
+    return this;
   if (arg_count)
   {
     Item **arg,**arg_end;
@@ -391,7 +392,9 @@ Item *Item_func::compile(Item_analyzer a
       */   
       uchar *arg_v= *arg_p;
       Item *new_item= (*arg)->compile(analyzer, &arg_v, transformer, arg_t);
-      if (new_item && *arg != new_item)
+      if (new_item == NULL)
+        return NULL;
+      if (*arg != new_item)
         current_thd->change_item_tree(arg, new_item);
     }
   }

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-03-15 13:15:19 +0000
+++ b/sql/sql_optimizer.cc	2012-03-15 14:41:06 +0000
@@ -763,7 +763,8 @@ JOIN::optimize()
   }
 
   /* Cache constant expressions in WHERE, HAVING, ON clauses. */
-  cache_const_exprs();
+  if (cache_const_exprs())
+    DBUG_RETURN(1);
 
   /*
     is this simple IN subquery?
@@ -6824,36 +6825,48 @@ void JOIN::drop_unused_derived_keys()
 
 /**
   Cache constant expressions in WHERE, HAVING, ON conditions.
+
+  @return False if success, True if error
+
+  @note This function is run after conditions have been pushed down to
+        individual tables, so transformation is applied to JOIN_TAB::condition
+        and not to the WHERE condition.
 */
 
-void JOIN::cache_const_exprs()
+bool JOIN::cache_const_exprs()
 {
-  bool cache_flag= FALSE;
-  bool *analyzer_arg= &cache_flag;
-
   /* No need in cache if all tables are constant. */
   if (const_tables == tables)
-    return;
+    return false;
 
-  if (conds)
-    conds->compile(&Item::cache_const_expr_analyzer, (uchar **)&analyzer_arg,
-                  &Item::cache_const_expr_transformer, (uchar *)&cache_flag);
-  cache_flag= FALSE;
+  for (uint i= const_tables; i < tables; i++)
+  {
+    Item *condition= join_tab[i].condition();
+    if (condition == NULL)
+      continue;
+    Item *cache_item= NULL;
+    Item **analyzer_arg= &cache_item;
+    condition=
+      condition->compile(&Item::cache_const_expr_analyzer,
+                         (uchar **)&analyzer_arg,
+                         &Item::cache_const_expr_transformer,
+                         (uchar *)&cache_item);
+    if (condition == NULL)
+      return true;
+    if (condition != join_tab[i].condition())
+      join_tab[i].set_condition(condition, __LINE__);
+  }
   if (having)
-    having->compile(&Item::cache_const_expr_analyzer, (uchar **)&analyzer_arg,
-                    &Item::cache_const_expr_transformer, (uchar *)&cache_flag);
-
-  for (JOIN_TAB *tab= join_tab + const_tables; tab < join_tab + tables ; tab++)
   {
-    if (*tab->on_expr_ref)
-    {
-      cache_flag= FALSE;
-      (*tab->on_expr_ref)->compile(&Item::cache_const_expr_analyzer,
-                                 (uchar **)&analyzer_arg,
-                                 &Item::cache_const_expr_transformer,
-                                 (uchar *)&cache_flag);
-    }
+    Item *cache_item= NULL;
+    Item **analyzer_arg= &cache_item;
+    having=
+      having->compile(&Item::cache_const_expr_analyzer, (uchar **)&analyzer_arg,
+                      &Item::cache_const_expr_transformer,(uchar *)&cache_item);
+    if (having == NULL)
+      return true;
   }
+  return false;
 }
 
 

=== modified file 'sql/sql_optimizer.h'
--- a/sql/sql_optimizer.h	2012-02-29 11:17:52 +0000
+++ b/sql/sql_optimizer.h	2012-03-15 14:41:06 +0000
@@ -563,7 +563,7 @@ public:
     return (unit == &thd->lex->unit && (unit->fake_select_lex == 0 ||
                                         select_lex == unit->fake_select_lex));
   }
-  void cache_const_exprs();
+  bool cache_const_exprs();
   bool generate_derived_keys();
   void drop_unused_derived_keys();
   bool get_best_combination();

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3783 to 3784) Bug#13468414Roy Lyseng15 Mar