List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:September 29 2011 12:47pm
Subject:bzr push into mysql-trunk branch (jorgen.loland:3450 to 3452) Bug#12838171
View as plain text  
 3452 Jorgen Loland	2011-09-29
      BUG#12838171:  51VS56: TRANSFORMED IN()+SUBQ QUERY PRODUCES 
                     EMPTY RESULT ON 5.6, 1 ROW ON 5.1
            
      Consider a query of the form:
            
      SELECT ... 
      WHERE outer_value_list IN (SELECT inner_value_list ... 
                                 WHERE colX IS NULL)
            
      If outer_value_list contains a NULL value, the IN predicate 
      shall evaluate to FALSE or UNKNOWN (depending on whether or 
      not the inner query returns rows). In any case the predicate 
      is not satisfied.
      
      If IN->EXISTS transformation is chosen and there is an index 
      that covers both inner_value_list and colX, the inner query 
      is resolved using subquery_uniquesubquery_engine. This engine 
      had a shortcut when copying the key part values used for 
      index lookup: if one of the key parts had a NULL value and the
      IN subquery was "top level", it would return "predicate not 
      satisfied".
      
      This shortcut would have been OK if the NULL value came from 
      the outer_value_list, but in this bug colX has the NULL 
      value. Thus, the predicate should return true whereas it 
      returned false due to the shortcut.
      
      subselect_[unique|index]subquery_engine::exec() share the code
      that has the mentioned shortcut 
      (subselect_uniquesubquery_engine::copy_ref_key()). Both
      methods are called only from Item_in_optimizer::val_int() 
      which has the same shortcut. This means that it will never be 
      the case that an outer value is NULL for a top level item in 
      copy_ref_key(). The fix is therefore to remove the shortcut.
     @ mysql-test/include/subquery.inc
        Added test for BUG#12838171
     @ mysql-test/r/subquery_nomat_nosj.result
        Added test for BUG#12838171
     @ mysql-test/r/subquery_nomat_nosj_bka.result
        Added test for BUG#12838171
     @ mysql-test/r/subquery_nomat_nosj_bka_nobnl.result
        Added test for BUG#12838171
     @ mysql-test/r/subquery_none.result
        Added test for BUG#12838171
     @ mysql-test/r/subquery_none_bka.result
        Added test for BUG#12838171
     @ mysql-test/r/subquery_none_bka_nobnl.result
        Added test for BUG#12838171
     @ sql/item_subselect.cc
        Don't assume that a top-level subquery predicate is unsatisfied
        just because a NULL key is encountered.  The NULL valued key
        may be used in a comparison that does not reject NULLs (like 
        IS NULL)
     @ sql/item_subselect.h
        null_keypart no longer member of subselect_uniquesubquery_engine

    modified:
      mysql-test/include/subquery.inc
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_nomat_nosj_bka.result
      mysql-test/r/subquery_nomat_nosj_bka_nobnl.result
      mysql-test/r/subquery_none.result
      mysql-test/r/subquery_none_bka.result
      mysql-test/r/subquery_none_bka_nobnl.result
      sql/item_subselect.cc
      sql/item_subselect.h
 3451 Jorgen Loland	2011-09-29
      BUG#12838171
      
      Preparation patch - ordinal position in
      st_table_ref::key_copy[]
      
      st_table_ref::key_copy is now an array corresponding to key
      part number. key_copy[1] is thus store_key for key part 1 of
      the index in question. If the value for key part x is constant,
      key_copy[x]==NULL. Before, key_copy[1,...,x-1] would be for
      key parts 1,...,x-1 while key_copy[x] would be for key 
      part x+1, key_copy[x+1] would be for key part x+2 etc
     @ sql/item_subselect.cc
        st_table_ref::key_copy is now an array corresponding to key
        part number. key_copy[1] is thus store_key for key part 1 of
        the index in question. If the value for key part x is constant,
        key_copy[x]==NULL. Before, key_copy[1,...,x-1] would be for
        key parts 1,...,x-1 while key_copy[x] would be for key 
        part x+1, key_copy[x+1] would be for key part x+2 etc
     @ sql/opt_explain.cc
        st_table_ref::key_copy is now an array corresponding to key
        part number. key_copy[1] is thus store_key for key part 1 of
        the index in question. If the value for key part x is constant,
        key_copy[x]==NULL. Before, key_copy[1,...,x-1] would be for
        key parts 1,...,x-1 while key_copy[x] would be for key 
        part x+1, key_copy[x+1] would be for key part x+2 etc
     @ sql/sql_select.cc
        st_table_ref::key_copy is now an array corresponding to key
        part number. key_copy[1] is thus store_key for key part 1 of
        the index in question. If the value for key part x is constant,
        key_copy[x]==NULL. Before, key_copy[1,...,x-1] would be for
        key parts 1,...,x-1 while key_copy[x] would be for key 
        part x+1, key_copy[x+1] would be for key part x+2 etc
     @ sql/sql_select.h
        st_table_ref::key_copy is now an array corresponding to key
        part number. key_copy[1] is thus store_key for key part 1 of
        the index in question. If the value for key part x is constant,
        key_copy[x]==NULL. Before, key_copy[1,...,x-1] would be for
        key parts 1,...,x-1 while key_copy[x] would be for key 
        part x+1, key_copy[x+1] would be for key part x+2 etc

    modified:
      sql/item_subselect.cc
      sql/opt_explain.cc
      sql/sql_select.cc
      sql/sql_select.h
 3450 Andrei Elkin	2011-09-29 [merge]
      merge from 5.5 bugfixing branch

    modified:
      mysql-test/suite/rpl/r/rpl_checksum.result
      mysql-test/suite/rpl/r/rpl_log_pos.result
      mysql-test/suite/rpl/r/rpl_manual_change_index_file.result
      mysql-test/suite/rpl/r/rpl_packet.result
      mysql-test/suite/rpl/r/rpl_row_event_max_size.result
      sql/rpl_master.cc
      sql/share/errmsg-utf8.txt
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc	2011-08-26 13:26:33 +0000
+++ b/mysql-test/include/subquery.inc	2011-09-29 12:47:32 +0000
@@ -5561,4 +5561,85 @@ WHERE t1.k < ALL(
 DROP TABLE t1, t2, t3; 
 
 --echo #
+--echo # Bug#12838171: 51VS56: TRANSFORMED IN()+SUBQ QUERY 
+--echo #               PRODUCES EMPTY RESULT ON 5.6, 1 ROW ON 5.1
+--echo #
+CREATE TABLE ot (
+  col_int_nokey int(11), 
+  col_varchar_nokey varchar(1)
+) ;
+
+INSERT INTO ot VALUES (1,'x');
+
+CREATE TABLE it (
+  col_int_key int(11), 
+  col_varchar_key varchar(1), 
+  KEY idx_cvk_cik (col_varchar_key,col_int_key)
+) ;
+
+INSERT INTO it VALUES (NULL,'x'), (NULL,'f');
+
+--echo
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+   (SELECT col_varchar_key
+    FROM it 
+    WHERE col_int_key IS NULL);
+
+--echo
+EXPLAIN EXTENDED
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+   (SELECT col_varchar_key
+    FROM it 
+    WHERE col_int_key IS NULL);
+
+--echo
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+   (SELECT col_varchar_key
+    FROM it 
+    WHERE coalesce(col_int_nokey, 1) );
+
+--echo
+EXPLAIN EXTENDED
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+   (SELECT col_varchar_key
+    FROM it 
+    WHERE coalesce(col_int_nokey, 1) );
+
+DROP TABLE it;
+
+CREATE TABLE it (
+   col_int_key int(11),
+   col_varchar_key varchar(1),
+   col_varchar_key2 varchar(1),
+   KEY idx_cvk_cvk2_cik (col_varchar_key, col_varchar_key2, col_int_key),
+   KEY idx_cvk_cik (col_varchar_key, col_int_key)
+);
+
+INSERT INTO it VALUES (NULL,'x','x'), (NULL,'f','f');
+
+SELECT col_int_nokey
+FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+    (SELECT col_varchar_key, col_varchar_key2
+     FROM it
+     WHERE col_int_key IS NULL);
+
+--echo
+EXPLAIN EXTENDED
+SELECT col_int_nokey
+FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+    (SELECT col_varchar_key, col_varchar_key2
+     FROM it
+     WHERE col_int_key IS NULL);
 
+--echo
+DROP TABLE it, ot;

=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result	2011-09-13 07:22:49 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result	2011-09-29 12:47:32 +0000
@@ -6733,4 +6733,95 @@ COUNT(*)
 6
 DROP TABLE t1, t2, t3;
 #
+# Bug#12838171: 51VS56: TRANSFORMED IN()+SUBQ QUERY 
+#               PRODUCES EMPTY RESULT ON 5.6, 1 ROW ON 5.1
+#
+CREATE TABLE ot (
+col_int_nokey int(11), 
+col_varchar_nokey varchar(1)
+) ;
+INSERT INTO ot VALUES (1,'x');
+CREATE TABLE it (
+col_int_key int(11), 
+col_varchar_key varchar(1), 
+KEY idx_cvk_cik (col_varchar_key,col_int_key)
+) ;
+INSERT INTO it VALUES (NULL,'x'), (NULL,'f');
+
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE col_int_key IS NULL);
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE col_int_key IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cik	idx_cvk_cik	9	func,const	2	100.00	Using index; Using where
+Warnings:
+Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>('x',<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`)))))
+
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE coalesce(col_int_nokey, 1) );
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE coalesce(col_int_nokey, 1) );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cik	idx_cvk_cik	4	func	1	100.00	Using index
+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 dual where <in_optimizer>('x',<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cik)))
+DROP TABLE it;
+CREATE TABLE it (
+col_int_key int(11),
+col_varchar_key varchar(1),
+col_varchar_key2 varchar(1),
+KEY idx_cvk_cvk2_cik (col_varchar_key, col_varchar_key2, col_int_key),
+KEY idx_cvk_cik (col_varchar_key, col_int_key)
+);
+INSERT INTO it VALUES (NULL,'x','x'), (NULL,'f','f');
+SELECT col_int_nokey
+FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2
+FROM it
+WHERE col_int_key IS NULL);
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey
+FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2
+FROM it
+WHERE col_int_key IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	13	func,const,const	2	100.00	Using index; Using where
+Warnings:
+Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>(('x','x'),<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key2`)))))
+
+DROP TABLE it, ot;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_nomat_nosj_bka.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka.result	2011-08-26 13:26:33 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka.result	2011-09-29 12:47:32 +0000
@@ -6734,5 +6734,96 @@ COUNT(*)
 6
 DROP TABLE t1, t2, t3;
 #
+# Bug#12838171: 51VS56: TRANSFORMED IN()+SUBQ QUERY 
+#               PRODUCES EMPTY RESULT ON 5.6, 1 ROW ON 5.1
+#
+CREATE TABLE ot (
+col_int_nokey int(11), 
+col_varchar_nokey varchar(1)
+) ;
+INSERT INTO ot VALUES (1,'x');
+CREATE TABLE it (
+col_int_key int(11), 
+col_varchar_key varchar(1), 
+KEY idx_cvk_cik (col_varchar_key,col_int_key)
+) ;
+INSERT INTO it VALUES (NULL,'x'), (NULL,'f');
+
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE col_int_key IS NULL);
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE col_int_key IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cik	idx_cvk_cik	9	func,const	2	100.00	Using index; Using where
+Warnings:
+Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>('x',<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`)))))
+
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE coalesce(col_int_nokey, 1) );
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE coalesce(col_int_nokey, 1) );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cik	idx_cvk_cik	4	func	1	100.00	Using index
+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 dual where <in_optimizer>('x',<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cik)))
+DROP TABLE it;
+CREATE TABLE it (
+col_int_key int(11),
+col_varchar_key varchar(1),
+col_varchar_key2 varchar(1),
+KEY idx_cvk_cvk2_cik (col_varchar_key, col_varchar_key2, col_int_key),
+KEY idx_cvk_cik (col_varchar_key, col_int_key)
+);
+INSERT INTO it VALUES (NULL,'x','x'), (NULL,'f','f');
+SELECT col_int_nokey
+FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2
+FROM it
+WHERE col_int_key IS NULL);
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey
+FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2
+FROM it
+WHERE col_int_key IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	13	func,const,const	2	100.00	Using index; Using where
+Warnings:
+Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>(('x','x'),<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key2`)))))
+
+DROP TABLE it, ot;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_nomat_nosj_bka_nobnl.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka_nobnl.result	2011-08-26 13:26:33 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka_nobnl.result	2011-09-29 12:47:32 +0000
@@ -6734,5 +6734,96 @@ COUNT(*)
 6
 DROP TABLE t1, t2, t3;
 #
+# Bug#12838171: 51VS56: TRANSFORMED IN()+SUBQ QUERY 
+#               PRODUCES EMPTY RESULT ON 5.6, 1 ROW ON 5.1
+#
+CREATE TABLE ot (
+col_int_nokey int(11), 
+col_varchar_nokey varchar(1)
+) ;
+INSERT INTO ot VALUES (1,'x');
+CREATE TABLE it (
+col_int_key int(11), 
+col_varchar_key varchar(1), 
+KEY idx_cvk_cik (col_varchar_key,col_int_key)
+) ;
+INSERT INTO it VALUES (NULL,'x'), (NULL,'f');
+
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE col_int_key IS NULL);
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE col_int_key IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cik	idx_cvk_cik	9	func,const	2	100.00	Using index; Using where
+Warnings:
+Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>('x',<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`)))))
+
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE coalesce(col_int_nokey, 1) );
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE coalesce(col_int_nokey, 1) );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cik	idx_cvk_cik	4	func	1	100.00	Using index
+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 dual where <in_optimizer>('x',<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cik)))
+DROP TABLE it;
+CREATE TABLE it (
+col_int_key int(11),
+col_varchar_key varchar(1),
+col_varchar_key2 varchar(1),
+KEY idx_cvk_cvk2_cik (col_varchar_key, col_varchar_key2, col_int_key),
+KEY idx_cvk_cik (col_varchar_key, col_int_key)
+);
+INSERT INTO it VALUES (NULL,'x','x'), (NULL,'f','f');
+SELECT col_int_nokey
+FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2
+FROM it
+WHERE col_int_key IS NULL);
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey
+FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2
+FROM it
+WHERE col_int_key IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	13	func,const,const	2	100.00	Using index; Using where
+Warnings:
+Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>(('x','x'),<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key2`)))))
+
+DROP TABLE it, ot;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result	2011-09-13 07:22:49 +0000
+++ b/mysql-test/r/subquery_none.result	2011-09-29 12:47:32 +0000
@@ -6732,4 +6732,95 @@ COUNT(*)
 6
 DROP TABLE t1, t2, t3;
 #
+# Bug#12838171: 51VS56: TRANSFORMED IN()+SUBQ QUERY 
+#               PRODUCES EMPTY RESULT ON 5.6, 1 ROW ON 5.1
+#
+CREATE TABLE ot (
+col_int_nokey int(11), 
+col_varchar_nokey varchar(1)
+) ;
+INSERT INTO ot VALUES (1,'x');
+CREATE TABLE it (
+col_int_key int(11), 
+col_varchar_key varchar(1), 
+KEY idx_cvk_cik (col_varchar_key,col_int_key)
+) ;
+INSERT INTO it VALUES (NULL,'x'), (NULL,'f');
+
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE col_int_key IS NULL);
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE col_int_key IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cik	idx_cvk_cik	9	func,const	2	100.00	Using index; Using where
+Warnings:
+Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>('x',<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`)))))
+
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE coalesce(col_int_nokey, 1) );
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE coalesce(col_int_nokey, 1) );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cik	idx_cvk_cik	4	func	1	100.00	Using index
+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 dual where <in_optimizer>('x',<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cik)))
+DROP TABLE it;
+CREATE TABLE it (
+col_int_key int(11),
+col_varchar_key varchar(1),
+col_varchar_key2 varchar(1),
+KEY idx_cvk_cvk2_cik (col_varchar_key, col_varchar_key2, col_int_key),
+KEY idx_cvk_cik (col_varchar_key, col_int_key)
+);
+INSERT INTO it VALUES (NULL,'x','x'), (NULL,'f','f');
+SELECT col_int_nokey
+FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2
+FROM it
+WHERE col_int_key IS NULL);
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey
+FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2
+FROM it
+WHERE col_int_key IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	13	func,const,const	2	100.00	Using index; Using where
+Warnings:
+Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>(('x','x'),<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key2`)))))
+
+DROP TABLE it, ot;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none_bka.result'
--- a/mysql-test/r/subquery_none_bka.result	2011-08-26 13:26:33 +0000
+++ b/mysql-test/r/subquery_none_bka.result	2011-09-29 12:47:32 +0000
@@ -6733,5 +6733,96 @@ COUNT(*)
 6
 DROP TABLE t1, t2, t3;
 #
+# Bug#12838171: 51VS56: TRANSFORMED IN()+SUBQ QUERY 
+#               PRODUCES EMPTY RESULT ON 5.6, 1 ROW ON 5.1
+#
+CREATE TABLE ot (
+col_int_nokey int(11), 
+col_varchar_nokey varchar(1)
+) ;
+INSERT INTO ot VALUES (1,'x');
+CREATE TABLE it (
+col_int_key int(11), 
+col_varchar_key varchar(1), 
+KEY idx_cvk_cik (col_varchar_key,col_int_key)
+) ;
+INSERT INTO it VALUES (NULL,'x'), (NULL,'f');
+
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE col_int_key IS NULL);
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE col_int_key IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cik	idx_cvk_cik	9	func,const	2	100.00	Using index; Using where
+Warnings:
+Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>('x',<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`)))))
+
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE coalesce(col_int_nokey, 1) );
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE coalesce(col_int_nokey, 1) );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cik	idx_cvk_cik	4	func	1	100.00	Using index
+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 dual where <in_optimizer>('x',<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cik)))
+DROP TABLE it;
+CREATE TABLE it (
+col_int_key int(11),
+col_varchar_key varchar(1),
+col_varchar_key2 varchar(1),
+KEY idx_cvk_cvk2_cik (col_varchar_key, col_varchar_key2, col_int_key),
+KEY idx_cvk_cik (col_varchar_key, col_int_key)
+);
+INSERT INTO it VALUES (NULL,'x','x'), (NULL,'f','f');
+SELECT col_int_nokey
+FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2
+FROM it
+WHERE col_int_key IS NULL);
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey
+FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2
+FROM it
+WHERE col_int_key IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	13	func,const,const	2	100.00	Using index; Using where
+Warnings:
+Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>(('x','x'),<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key2`)))))
+
+DROP TABLE it, ot;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none_bka_nobnl.result'
--- a/mysql-test/r/subquery_none_bka_nobnl.result	2011-08-26 13:26:33 +0000
+++ b/mysql-test/r/subquery_none_bka_nobnl.result	2011-09-29 12:47:32 +0000
@@ -6733,5 +6733,96 @@ COUNT(*)
 6
 DROP TABLE t1, t2, t3;
 #
+# Bug#12838171: 51VS56: TRANSFORMED IN()+SUBQ QUERY 
+#               PRODUCES EMPTY RESULT ON 5.6, 1 ROW ON 5.1
+#
+CREATE TABLE ot (
+col_int_nokey int(11), 
+col_varchar_nokey varchar(1)
+) ;
+INSERT INTO ot VALUES (1,'x');
+CREATE TABLE it (
+col_int_key int(11), 
+col_varchar_key varchar(1), 
+KEY idx_cvk_cik (col_varchar_key,col_int_key)
+) ;
+INSERT INTO it VALUES (NULL,'x'), (NULL,'f');
+
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE col_int_key IS NULL);
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE col_int_key IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cik	idx_cvk_cik	9	func,const	2	100.00	Using index; Using where
+Warnings:
+Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>('x',<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`)))))
+
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE coalesce(col_int_nokey, 1) );
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey 
+FROM ot 
+WHERE col_varchar_nokey IN
+(SELECT col_varchar_key
+FROM it 
+WHERE coalesce(col_int_nokey, 1) );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cik	idx_cvk_cik	4	func	1	100.00	Using index
+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 dual where <in_optimizer>('x',<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cik)))
+DROP TABLE it;
+CREATE TABLE it (
+col_int_key int(11),
+col_varchar_key varchar(1),
+col_varchar_key2 varchar(1),
+KEY idx_cvk_cvk2_cik (col_varchar_key, col_varchar_key2, col_int_key),
+KEY idx_cvk_cik (col_varchar_key, col_int_key)
+);
+INSERT INTO it VALUES (NULL,'x','x'), (NULL,'f','f');
+SELECT col_int_nokey
+FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2
+FROM it
+WHERE col_int_key IS NULL);
+col_int_nokey
+1
+
+EXPLAIN EXTENDED
+SELECT col_int_nokey
+FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2
+FROM it
+WHERE col_int_key IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	it	index_subquery	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	13	func,const,const	2	100.00	Using index; Using where
+Warnings:
+Note	1003	/* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>(('x','x'),<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key2`)))))
+
+DROP TABLE it, ot;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2011-08-29 11:57:44 +0000
+++ b/sql/item_subselect.cc	2011-09-29 12:47:32 +0000
@@ -2558,6 +2558,7 @@ bool subselect_uniquesubquery_engine::sc
 
     if (!cond || cond->val_int())
     {
+      static_cast<Item_in_subselect*>(item)->value= true;
       empty_result_set= FALSE;
       break;
     }
@@ -2568,79 +2569,86 @@ bool subselect_uniquesubquery_engine::sc
 }
 
 
-/*
+/**
   Copy ref key and check for null parts in it
 
-  SYNOPSIS
-    subselect_uniquesubquery_engine::copy_ref_key()
+  Construct a search tuple to be used for index lookup. If one of the
+  key parts have a NULL value, the following logic applies:
 
-  DESCRIPTION
-    Copy ref key and check for null parts in it.
-    Depending on the nullability and conversion problems this function
-    recognizes and processes the following states :
-      1. Partial match on top level. This means IN has a value of FALSE
-         regardless of the data in the subquery table.
-         Detected by finding a NULL in the left IN operand of a top level
-         expression.
-         We may actually skip reading the subquery, so return TRUE to skip
-         the table scan in subselect_uniquesubquery_engine::exec and make
-         the value of the IN predicate a NULL (that is equal to FALSE on
-         top level).
-      2. No exact match when IN is nested inside another predicate.
-         Detected by finding a NULL in the left IN operand when IN is not
-         a top level predicate.
-         We cannot have an exact match. But we must proceed further with a
-         table scan to find out if it's a partial match (and IN has a value
-         of NULL) or no match (and IN has a value of FALSE).
-         So we return FALSE to continue with the scan and see if there are
-         any record that would constitute a partial match (as we cannot
-         determine that from the index).
-      3. Error converting the left IN operand to the column type of the
-         right IN operand. This counts as no match (and IN has the value of
-         FALSE). We mark the subquery table cursor as having no more rows
-         (to ensure that the processing that follows will not find a match)
-         and return FALSE, so IN is not treated as returning NULL.
+  For top level items, e.g.
 
+     "WHERE <outer_value_list> IN (SELECT <inner_value_list>...)"
 
-  RETURN
-    FALSE - The value of the IN predicate is not known. Proceed to find the
-            value of the IN predicate using the determined values of
-            null_keypart and table->status.
-    TRUE  - IN predicate has a value of NULL. Stop the processing right there
-            and return NULL to the outer predicates.
+  where one of the outer values are NULL, the IN predicate evaluates
+  to false/UNKNOWN (we don't care) and it's not necessary to evaluate
+  the subquery. That shortcut is taken in
+  Item_in_optimizer::val_int(). Thus, if a key part with a NULL value
+  is found here, the NULL is either not outer or this subquery is not
+  top level. Therefore we cannot shortcut subquery execution if a NULL
+  is found here.
+
+  Thus, if one of the key parts have a NULL value there are two
+  possibilities:
+
+  a) The NULL is from the outer_value_list. Since this is not a top
+     level item (see above) we need to check whether this predicate
+     evaluates to NULL or false. That is done by checking if the
+     subquery has a row if the conditions based on outer NULL values
+     are disabled. Index lookup cannot be used for this, so a table
+     scan must be done.
+
+  b) The NULL is local to the subquery, e.g.:
+
+        "WHERE ... IN (SELECT ... WHERE inner_col IS NULL)"
+
+     In this case we're looking for rows with the exact inner_col
+     value of NULL, not rows that match if the "inner_col IS NULL"
+     condition is disabled. Index lookup can be used for this.
+
+  @see subselect_uniquesubquery_engine::exec()
+  @see Item_in_optimizer::val_int()
+
+  @param[out] require_scan   true if a NULL value is found that falls 
+                             into category a) above, false if index 
+                             lookup can be used.
+  @param[out] convert_error  true if an error occured during conversion
+                             of values from one type to another, false
+                             otherwise.
+  
 */
-
-bool subselect_uniquesubquery_engine::copy_ref_key()
+void subselect_uniquesubquery_engine::copy_ref_key(bool *require_scan, 
+                                                   bool *convert_error)
 {
   DBUG_ENTER("subselect_uniquesubquery_engine::copy_ref_key");
 
-  for (store_key **copy= tab->ref.key_copy ; *copy ; copy++)
+  *require_scan= false;
+  *convert_error= false;
+  for (uint part_no= 0; part_no < tab->ref.key_parts; part_no++)
   {
-    enum store_key::store_key_result store_res;
-    store_res= (*copy)->copy();
-    tab->ref.key_err= store_res;
+    store_key *s_key= tab->ref.key_copy[part_no];
+    if (s_key == NULL)
+      continue; // key is const and does not need to be reevaluated
 
-    /*
-      When there is a NULL part in the key we don't need to make index
-      lookup for such key thus we don't need to copy whole key.
-      If we later should do a sequential scan return OK. Fail otherwise.
+    const enum store_key::store_key_result store_res= s_key->copy();
+    tab->ref.key_err= store_res;
 
-      See also the comment for the subselect_uniquesubquery_engine::exec()
-      function.
-    */
-    null_keypart= (*copy)->null_key;
-    if (null_keypart)
+    if (s_key->null_key)
     {
-      bool top_level= ((Item_in_subselect *) item)->is_top_level_item();
-      if (top_level)
-      {
-        /* Partial match on top level */
-        DBUG_RETURN(1);
-      }
-      else
+      const bool *cond_guard= tab->ref.cond_guards[part_no];
+
+      /*
+        NULL value is from the outer_value_list if the key part has a
+        cond guard that deactivates the condition. @see
+        TABLE_REF::cond_guards
+
+      */
+      if (cond_guard && !*cond_guard)
       {
-        /* No exact match when IN is nested inside another predicate */
-        break;
+        DBUG_ASSERT(!(static_cast <Item_in_subselect*>(item)
+                      ->is_top_level_item()));
+
+        *require_scan= true;
+        DBUG_VOID_RETURN;
       }
     }
 
@@ -2659,10 +2667,11 @@ bool subselect_uniquesubquery_engine::co
        IN operand. 
       */
       tab->table->status= STATUS_NOT_FOUND;
-      break;
+      *convert_error= true;
+      DBUG_VOID_RETURN;
     }
   }
-  DBUG_RETURN(0);
+  DBUG_VOID_RETURN;
 }
 
 
@@ -2719,22 +2728,20 @@ bool subselect_uniquesubquery_engine::ex
       DBUG_RETURN(1);
   }
 
-  /* TODO: change to use of 'full_scan' here? */
-  if (copy_ref_key())
-    DBUG_RETURN(1);
-  if (table->status)
-  {
-    /* 
-      We know that there will be no rows even if we scan. 
-      Can be set in copy_ref_key.
-    */
+  /* Copy the ref key and check for nulls... */
+  bool require_scan, convert_error;
+  copy_ref_key(&require_scan, &convert_error);
+  if (convert_error)
+  {
     ((Item_in_subselect *) item)->value= 0;
     DBUG_RETURN(0);
   }
 
-  if (null_keypart)
-    DBUG_RETURN(scan_table());
- 
+  if (require_scan)
+  {
+    const bool scan_result= scan_table();
+    DBUG_RETURN(scan_result);
+  }
   if (!table->file->inited)
     table->file->ha_index_init(tab->ref.key, 0);
   error= table->file->ha_index_read_map(table->record[0],
@@ -2824,7 +2831,6 @@ bool subselect_indexsubquery_engine::exe
 
   ((Item_in_subselect *) item)->value= 0;
   empty_result_set= TRUE;
-  null_keypart= 0;
   table->status= 0;
 
   if (tl->uses_materialization() && !tl->materialized)
@@ -2848,21 +2854,19 @@ bool subselect_indexsubquery_engine::exe
   }
 
   /* Copy the ref key and check for nulls... */
-  if (copy_ref_key())
-    DBUG_RETURN(1);
-
-  if (table->status)
+  bool require_scan, convert_error;
+  copy_ref_key(&require_scan, &convert_error);
+  if (convert_error)
   {
-    /* 
-      We know that there will be no rows even if we scan. 
-      Can be set in copy_ref_key.
-    */
     ((Item_in_subselect *) item)->value= 0;
     DBUG_RETURN(0);
   }
 
-  if (null_keypart)
-    DBUG_RETURN(scan_table());
+  if (require_scan)
+  {
+    const bool scan_result= scan_table();
+    DBUG_RETURN(scan_result);
+  }
 
   if (!table->file->inited)
     table->file->ha_index_init(tab->ref.key, 1);

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2011-07-19 15:11:15 +0000
+++ b/sql/item_subselect.h	2011-09-29 12:47:32 +0000
@@ -612,7 +612,6 @@ protected:
     expression is NULL.
   */
   bool empty_result_set;
-  bool null_keypart; /* TRUE <=> constructed search tuple has a NULL */
 public:
 
   // constructor can assign THD because it will be called after JOIN::prepare
@@ -632,7 +631,7 @@ public:
                              select_result_interceptor *result);
   virtual bool no_tables() const;
   bool scan_table();
-  bool copy_ref_key();
+  void copy_ref_key(bool *require_scan, bool *convert_error);
   virtual bool no_rows() const { return empty_result_set; }
   virtual enum_engine_type engine_type() const { return UNIQUESUBQUERY_ENGINE; }
 };

=== modified file 'sql/opt_explain.cc'
--- a/sql/opt_explain.cc	2011-09-20 13:07:55 +0000
+++ b/sql/opt_explain.cc	2011-09-29 12:29:17 +0000
@@ -888,11 +888,16 @@ bool Explain_join::explain_ref()
   if (tab->ref.key_parts)
   {
     StringBuffer<512> str_ref(cs);
-    for (const store_key *const *ref= tab->ref.key_copy; *ref; ref++)
+
+    for (uint part_no= 0; part_no < tab->ref.key_parts; part_no++)
     {
+      const store_key *const s_key= tab->ref.key_copy[part_no];
+      if (s_key == NULL)
+        continue;
+
       if (str_ref.length())
         str_ref.append(',');
-      str_ref.append((*ref)->name(), strlen((*ref)->name()), cs);
+      str_ref.append(s_key->name(), strlen(s_key->name()), cs);
     }
     return col_ref.set(str_ref);
   }

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-09-28 07:42:55 +0000
+++ b/sql/sql_select.cc	2011-09-29 12:29:17 +0000
@@ -10030,7 +10030,7 @@ static bool create_ref_for_key(JOIN *joi
   j->ref.key=(int) key;
   if (!(j->ref.key_buff= (uchar*) thd->calloc(ALIGN_SIZE(length)*2)) ||
       !(j->ref.key_copy= (store_key**) thd->alloc((sizeof(store_key*) *
-						   (keyparts+1)))) ||
+                                                   (keyparts)))) ||
       !(j->ref.items=    (Item**) thd->alloc(sizeof(Item*)*keyparts)) ||
       !(j->ref.cond_guards= (bool**) thd->alloc(sizeof(uint*)*keyparts)))
   {
@@ -10044,7 +10044,6 @@ static bool create_ref_for_key(JOIN *joi
   j->ref.disable_cache= FALSE;
   keyuse=org_keyuse;
 
-  store_key **ref_key= j->ref.key_copy;
   uchar *key_buff=j->ref.key_buff, *null_ref_key= 0;
   bool keyuse_uses_no_tables= TRUE;
   if (ftkey)
@@ -10056,27 +10055,27 @@ static bool create_ref_for_key(JOIN *joi
       DBUG_RETURN(TRUE);                        // not supported yet. SerG
 
     j->type=JT_FT;
+    memset(j->ref.key_copy, 0, sizeof(j->ref.key_copy[0]) * keyparts);
   }
   else
   {
-    uint i;
-    for (i=0 ; i < keyparts ; keyuse++,i++)
+    for (uint part_no= 0 ; part_no < keyparts ; keyuse++, part_no++)
     {
-      while (keyuse->keypart != i ||
-	     ((~used_tables) & keyuse->used_tables))
-	keyuse++;				/* Skip other parts */
-
-      uint maybe_null= test(keyinfo->key_part[i].null_bit);
-      j->ref.items[i]=keyuse->val;		// Save for cond removal
-      j->ref.cond_guards[i]= keyuse->cond_guard;
+      while (keyuse->keypart != part_no ||
+             ((~used_tables) & keyuse->used_tables))
+        keyuse++;                               // Skip other parts
+
+      uint maybe_null= test(keyinfo->key_part[part_no].null_bit);
+      j->ref.items[part_no]=keyuse->val;        // Save for cond removal
+      j->ref.cond_guards[part_no]= keyuse->cond_guard;
       if (keyuse->null_rejecting) 
-        j->ref.null_rejecting |= 1 << i;
+        j->ref.null_rejecting |= 1 << part_no;
       keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables;
 
       store_key* key= get_store_key(thd,
-				    keyuse,join->const_table_map,
-				    &keyinfo->key_part[i],
-				    key_buff, maybe_null);
+                                    keyuse,join->const_table_map,
+                                    &keyinfo->key_part[part_no],
+                                    key_buff, maybe_null);
       if (unlikely(!key || thd->is_fatal_error))
         DBUG_RETURN(TRUE);
 
@@ -10086,7 +10085,7 @@ static bool create_ref_for_key(JOIN *joi
           query (which refers to this info when printing the 'ref'
           column of the query plan)
         */
-        *ref_key++= key;
+        j->ref.key_copy[part_no]= key;
       else
       {
         /* key is const, copy value now and possibly skip it while ::exec() */
@@ -10101,9 +10100,9 @@ static bool create_ref_for_key(JOIN *joi
          */
         if (result!=store_key::STORE_KEY_OK  ||    // 1)
             key->null_key)                         // 2)
-        {
-	  *ref_key++= key;  // Reevaluate in JOIN::exec() 
-        }
+          j->ref.key_copy[part_no]= key; // Reevaluate in JOIN::exec()
+        else
+          j->ref.key_copy[part_no]= NULL;
       }
       /*
 	Remember if we are going to use REF_OR_NULL
@@ -10112,10 +10111,9 @@ static bool create_ref_for_key(JOIN *joi
       */
       if ((keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL) && maybe_null)
 	null_ref_key= key_buff;
-      key_buff+=keyinfo->key_part[i].store_length;
+      key_buff+=keyinfo->key_part[part_no].store_length;
     }
   } /* not ftkey */
-  *ref_key=0;				// end_marker
   if (j->type == JT_FT)
     DBUG_RETURN(0);
   if (j->type == JT_CONST)
@@ -12142,7 +12140,6 @@ Item *create_subquery_equalities(THD *th
 
 bool setup_sj_materialization(JOIN_TAB *tab)
 {
-  uint i;
   DBUG_ENTER("setup_sj_materialization");
   TABLE_LIST *emb_sj_nest= tab->emb_sj_nest;
   Semijoin_mat_exec *sjm= emb_sj_nest->sj_mat_exec;
@@ -12193,8 +12190,7 @@ bool setup_sj_materialization(JOIN_TAB *
     if (!(tab_ref->key_buff=
           (uchar*) thd->calloc(ALIGN_SIZE(tmp_key->key_length) * 2)) ||
         !(tab_ref->key_copy=
-          (store_key**) thd->alloc((sizeof(store_key*) *
-                                    (tmp_key_parts + 1)))) ||
+          (store_key**) thd->alloc((sizeof(store_key*) * tmp_key_parts))) ||
         !(tab_ref->items=
           (Item**) thd->alloc(sizeof(Item*) * tmp_key_parts)))
       DBUG_RETURN(TRUE); /* purecov: inspected */
@@ -12207,9 +12203,10 @@ bool setup_sj_materialization(JOIN_TAB *
     uchar *cur_ref_buff= tab_ref->key_buff;
     List_iterator<Item> outer_expr(emb_sj_nest->nested_join->sj_outer_exprs);
 
-    for (i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++)
+    for (uint part_no= 0; part_no < tmp_key_parts; 
+         part_no++, cur_key_part++, ref_key++)
     {
-      tab_ref->items[i]= outer_expr++;
+      tab_ref->items[part_no]= outer_expr++;
       int null_count= test(cur_key_part->field->real_maybe_null());
       *ref_key= new store_key_item(thd, cur_key_part->field,
                                    /* TODO:
@@ -12220,10 +12217,10 @@ bool setup_sj_materialization(JOIN_TAB *
                                    */
                                    cur_ref_buff + null_count,
                                    null_count ? cur_ref_buff : 0,
-                                   cur_key_part->length, tab_ref->items[i]);
+                                   cur_key_part->length, 
+                                   tab_ref->items[part_no]);
       cur_ref_buff+= cur_key_part->store_length;
     }
-    *ref_key= NULL; /* End marker. */
     tab_ref->key_err= 1;
     tab_ref->key_parts= tmp_key_parts;
     sjm->tab_ref= tab_ref;
@@ -12234,7 +12231,7 @@ bool setup_sj_materialization(JOIN_TAB *
       sj-inner tables which are not available after the materialization
       has been finished.
     */
-    for (i= 0; i < sjm->table_count; i++)
+    for (uint i= 0; i < sjm->table_count; i++)
     {
       tab[i].set_condition(remove_sj_conds(tab[i].condition()), __LINE__);
       if (tab[i].select)
@@ -22577,9 +22574,13 @@ cp_buffer_from_ref(THD *thd, TABLE *tabl
   my_bitmap_map *old_map= dbug_tmp_use_all_columns(table, table->write_set);
   bool result= 0;
 
-  for (store_key **copy=ref->key_copy ; *copy ; copy++)
+  for (uint part_no= 0; part_no < ref->key_parts; part_no++)
   {
-    if ((*copy)->copy() & 1)
+    store_key *s_key= ref->key_copy[part_no];
+    if (!s_key)
+      continue;
+
+    if (s_key->copy() & 1)
     {
       result= 1;
       break;

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2011-09-20 13:07:55 +0000
+++ b/sql/sql_select.h	2011-09-29 12:29:17 +0000
@@ -123,7 +123,12 @@ typedef struct st_table_ref : public Sql
   int           key;                      ///< key no
   uchar         *key_buff;                ///< value to look for with key
   uchar         *key_buff2;               ///< key_buff+key_length
-  store_key     **key_copy;               //
+  /**
+     Used to store the value from each keypart field. These values are
+     used for ref access. If key_copy[key_part] == NULL it means that
+     the value is constant and does not need to be reevaluated
+  */
+  store_key     **key_copy;
   Item          **items;                  ///< val()'s for each keypart
   /*  
     Array of pointers to trigger variables. Some/all of the pointers may be

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (jorgen.loland:3450 to 3452) Bug#12838171Jorgen Loland2 Oct