From: Jorgen Loland Date: September 29 2011 12:47pm Subject: bzr push into mysql-trunk branch (jorgen.loland:3450 to 3452) Bug#12838171 List-Archive: http://lists.mysql.com/commits/141206 X-Bug: 12838171 Message-Id: <20110929124753.A23DDB7@atum21.no.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ('x',((('x') in it on idx_cvk_cik where (isnull(`test`.`it`.`col_int_key`) and (('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 ('x',((('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 (('x','x'),((('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (('x') = `test`.`it`.`col_varchar_key`) and (('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 ('x',((('x') in it on idx_cvk_cik where (isnull(`test`.`it`.`col_int_key`) and (('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 ('x',((('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 (('x','x'),((('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (('x') = `test`.`it`.`col_varchar_key`) and (('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 ('x',((('x') in it on idx_cvk_cik where (isnull(`test`.`it`.`col_int_key`) and (('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 ('x',((('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 (('x','x'),((('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (('x') = `test`.`it`.`col_varchar_key`) and (('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 ('x',((('x') in it on idx_cvk_cik where (isnull(`test`.`it`.`col_int_key`) and (('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 ('x',((('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 (('x','x'),((('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (('x') = `test`.`it`.`col_varchar_key`) and (('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 ('x',((('x') in it on idx_cvk_cik where (isnull(`test`.`it`.`col_int_key`) and (('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 ('x',((('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 (('x','x'),((('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (('x') = `test`.`it`.`col_varchar_key`) and (('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 ('x',((('x') in it on idx_cvk_cik where (isnull(`test`.`it`.`col_int_key`) and (('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 ('x',((('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 (('x','x'),((('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (('x') = `test`.`it`.`col_varchar_key`) and (('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)->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 IN (SELECT ...)" - 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) + ->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 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).