From: Ole John Aske Date: January 14 2011 8:20am Subject: bzr push into mysql-trunk branch (ole.john.aske:3502 to 3503) Bug#58628 List-Archive: http://lists.mysql.com/commits/128699 X-Bug: 58628 Message-Id: <20110114082048.7008D1818C@loki43.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3503 Ole John Aske 2011-01-14 Fix for bug#58628: Incorrect result for 'WHERE NULL NOT IN () create_ref_for_key() allowed any constant part of a REF key to be evaluated and stored into the 'key_buff' during ::optimize(). These 'store_key*' was *not* kept in ref.key_copy[] as they where constant and we assumed we would not have to reevaluate them during JOIN::exec() However, during execute NULL values in REF key has to be detected as they may need special attention - as in 'Full scan on NULL key'. This is done by subselect_uniquesubquery_engine::copy_ref_key() which check if any keyparts evaluated to a NULL-value. As we didn't keep a store_key for a constant value, a NULL-constant was not detected by subselect_uniquesubquery_engine::copy_ref_key() ! This fixs modifies create_ref_for_key() to check if a NULL-value constant was produced - In these cases it keeps the store_key, which then will be reevaluated in JOIN::exec() and trigger correct handling of NULL-valued keys. @ mysql-test/r/update.result Update of result file due to changed (improved) behaviour when constructing a REF key from 'out of bound' values. As the main fix will cause failing const key value ::copy() to be reevaluate inside JOIN::exec(), we will detect out of bound values (which will form an illegal/undefined REF key) before executing the access request. Previously these const REF keys was produced by JOIN::optimize() and any conversion/range errors was ignored. - Possibly causing undefined keys to be produced. These access request may then be ignored wo/ a Handler_read_key request being executed. modified: mysql-test/include/func_in.inc mysql-test/r/func_in_icp.result mysql-test/r/func_in_icp_mrr.result mysql-test/r/func_in_mrr.result mysql-test/r/func_in_mrr_cost.result mysql-test/r/func_in_none.result mysql-test/r/update.result sql/sql_select.cc sql/sql_select.h 3502 Serge Kozlov 2011-01-14 [merge] Merge from mysql-5.5 into mysql-trunk modified: mysql-test/include/wait_for_status_var.inc mysql-test/suite/rpl/r/rpl_heartbeat_basic.result mysql-test/suite/rpl/t/rpl_heartbeat_basic.test === modified file 'mysql-test/include/func_in.inc' --- a/mysql-test/include/func_in.inc 2010-07-23 17:51:11 +0000 +++ b/mysql-test/include/func_in.inc 2011-01-14 08:20:08 +0000 @@ -555,5 +555,47 @@ SELECT CASE a WHEN a THEN a END FROM t1 DROP TABLE t1; --echo # +--echo # Bug#58628: Incorrect result for 'WHERE NULL NOT IN () +--echo # + +CREATE TABLE t1 (pk INT NOT NULL, i INT); +INSERT INTO t1 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL); + +CREATE TABLE subq (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i,pk)); +INSERT INTO subq VALUES (0,0), (1,1), (2,2), (3,3); + +## Baseline queries: t1.i contains only NULL and should effectively +## be evaluated as 'WHERE NULL IN' +## .. These return correct resultset ! + +--sorted_result +SELECT * FROM t1 + WHERE t1.i NOT IN + (SELECT i FROM subq WHERE subq.pk = t1.pk); + +--sorted_result +SELECT * FROM t1 + WHERE t1.i IN + (SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; + +## Replaced 't1.i' with some constant expression which +## also evaluates to NULL. Expected to return same result as above: + +--sorted_result +SELECT * FROM t1 + WHERE NULL NOT IN + (SELECT i FROM subq WHERE subq.pk = t1.pk); + +--sorted_result +SELECT * FROM t1 + WHERE NULL IN + (SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; + +--sorted_result +SELECT * FROM t1 + WHERE 1+NULL NOT IN + (SELECT i FROM subq WHERE subq.pk = t1.pk); + +DROP TABLE t1,subq; --echo End of 5.1 tests === modified file 'mysql-test/r/func_in_icp.result' --- a/mysql-test/r/func_in_icp.result 2010-07-23 17:51:11 +0000 +++ b/mysql-test/r/func_in_icp.result 2011-01-14 08:20:08 +0000 @@ -771,5 +771,40 @@ CASE a WHEN a THEN a END NULL DROP TABLE t1; # +# Bug#58628: Incorrect result for 'WHERE NULL NOT IN () +# +CREATE TABLE t1 (pk INT NOT NULL, i INT); +INSERT INTO t1 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL); +CREATE TABLE subq (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i,pk)); +INSERT INTO subq VALUES (0,0), (1,1), (2,2), (3,3); +SELECT * FROM t1 +WHERE t1.i NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +SELECT * FROM t1 +WHERE t1.i IN +(SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; +pk i +0 NULL +1 NULL +2 NULL +3 NULL +SELECT * FROM t1 +WHERE NULL NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +SELECT * FROM t1 +WHERE NULL IN +(SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; +pk i +0 NULL +1 NULL +2 NULL +3 NULL +SELECT * FROM t1 +WHERE 1+NULL NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +DROP TABLE t1,subq; End of 5.1 tests set optimizer_switch=default; === modified file 'mysql-test/r/func_in_icp_mrr.result' --- a/mysql-test/r/func_in_icp_mrr.result 2010-11-30 13:55:22 +0000 +++ b/mysql-test/r/func_in_icp_mrr.result 2011-01-14 08:20:08 +0000 @@ -771,5 +771,40 @@ CASE a WHEN a THEN a END NULL DROP TABLE t1; # +# Bug#58628: Incorrect result for 'WHERE NULL NOT IN () +# +CREATE TABLE t1 (pk INT NOT NULL, i INT); +INSERT INTO t1 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL); +CREATE TABLE subq (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i,pk)); +INSERT INTO subq VALUES (0,0), (1,1), (2,2), (3,3); +SELECT * FROM t1 +WHERE t1.i NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +SELECT * FROM t1 +WHERE t1.i IN +(SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; +pk i +0 NULL +1 NULL +2 NULL +3 NULL +SELECT * FROM t1 +WHERE NULL NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +SELECT * FROM t1 +WHERE NULL IN +(SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; +pk i +0 NULL +1 NULL +2 NULL +3 NULL +SELECT * FROM t1 +WHERE 1+NULL NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +DROP TABLE t1,subq; End of 5.1 tests set optimizer_switch=default; === modified file 'mysql-test/r/func_in_mrr.result' --- a/mysql-test/r/func_in_mrr.result 2010-11-30 13:55:22 +0000 +++ b/mysql-test/r/func_in_mrr.result 2011-01-14 08:20:08 +0000 @@ -771,5 +771,40 @@ CASE a WHEN a THEN a END NULL DROP TABLE t1; # +# Bug#58628: Incorrect result for 'WHERE NULL NOT IN () +# +CREATE TABLE t1 (pk INT NOT NULL, i INT); +INSERT INTO t1 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL); +CREATE TABLE subq (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i,pk)); +INSERT INTO subq VALUES (0,0), (1,1), (2,2), (3,3); +SELECT * FROM t1 +WHERE t1.i NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +SELECT * FROM t1 +WHERE t1.i IN +(SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; +pk i +0 NULL +1 NULL +2 NULL +3 NULL +SELECT * FROM t1 +WHERE NULL NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +SELECT * FROM t1 +WHERE NULL IN +(SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; +pk i +0 NULL +1 NULL +2 NULL +3 NULL +SELECT * FROM t1 +WHERE 1+NULL NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +DROP TABLE t1,subq; End of 5.1 tests set optimizer_switch=default; === modified file 'mysql-test/r/func_in_mrr_cost.result' --- a/mysql-test/r/func_in_mrr_cost.result 2010-11-30 13:55:22 +0000 +++ b/mysql-test/r/func_in_mrr_cost.result 2011-01-14 08:20:08 +0000 @@ -771,5 +771,40 @@ CASE a WHEN a THEN a END NULL DROP TABLE t1; # +# Bug#58628: Incorrect result for 'WHERE NULL NOT IN () +# +CREATE TABLE t1 (pk INT NOT NULL, i INT); +INSERT INTO t1 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL); +CREATE TABLE subq (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i,pk)); +INSERT INTO subq VALUES (0,0), (1,1), (2,2), (3,3); +SELECT * FROM t1 +WHERE t1.i NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +SELECT * FROM t1 +WHERE t1.i IN +(SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; +pk i +0 NULL +1 NULL +2 NULL +3 NULL +SELECT * FROM t1 +WHERE NULL NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +SELECT * FROM t1 +WHERE NULL IN +(SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; +pk i +0 NULL +1 NULL +2 NULL +3 NULL +SELECT * FROM t1 +WHERE 1+NULL NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +DROP TABLE t1,subq; End of 5.1 tests set optimizer_switch=default; === modified file 'mysql-test/r/func_in_none.result' --- a/mysql-test/r/func_in_none.result 2010-07-23 17:51:11 +0000 +++ b/mysql-test/r/func_in_none.result 2011-01-14 08:20:08 +0000 @@ -770,5 +770,40 @@ CASE a WHEN a THEN a END NULL DROP TABLE t1; # +# Bug#58628: Incorrect result for 'WHERE NULL NOT IN () +# +CREATE TABLE t1 (pk INT NOT NULL, i INT); +INSERT INTO t1 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL); +CREATE TABLE subq (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i,pk)); +INSERT INTO subq VALUES (0,0), (1,1), (2,2), (3,3); +SELECT * FROM t1 +WHERE t1.i NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +SELECT * FROM t1 +WHERE t1.i IN +(SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; +pk i +0 NULL +1 NULL +2 NULL +3 NULL +SELECT * FROM t1 +WHERE NULL NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +SELECT * FROM t1 +WHERE NULL IN +(SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; +pk i +0 NULL +1 NULL +2 NULL +3 NULL +SELECT * FROM t1 +WHERE 1+NULL NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +DROP TABLE t1,subq; End of 5.1 tests set optimizer_switch=default; === modified file 'mysql-test/r/update.result' --- a/mysql-test/r/update.result 2010-08-25 19:00:38 +0000 +++ b/mysql-test/r/update.result 2011-01-14 08:20:08 +0000 @@ -410,7 +410,7 @@ user_id show status like '%Handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 1 +Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 @@ -421,7 +421,7 @@ user_id show status like '%Handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 2 +Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 @@ -431,7 +431,7 @@ UPDATE t1 SET user_id=null WHERE request show status like '%Handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 3 +Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 @@ -441,7 +441,7 @@ UPDATE t1 SET user_id=null WHERE request show status like '%Handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 3 +Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-01-13 10:48:28 +0000 +++ b/sql/sql_select.cc 2011-01-14 08:20:08 +0000 @@ -8929,29 +8929,37 @@ static bool create_ref_for_key(JOIN *joi j->ref.null_rejecting |= 1 << i; 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); + if (unlikely(!key || thd->is_fatal_error)) + DBUG_RETURN(TRUE); + if (keyuse->used_tables || thd->lex->describe) /* Comparing against a non-constant or executing an EXPLAIN query (which refers to this info when printing the 'ref' column of the query plan) */ - *ref_key++= get_store_key(thd, - keyuse,join->const_table_map, - &keyinfo->key_part[i], - key_buff, maybe_null); + *ref_key++= key; else - { // Compare against constant - store_key_item tmp(thd, keyinfo->key_part[i].field, - key_buff + maybe_null, - maybe_null ? key_buff : 0, - keyinfo->key_part[i].length, keyuse->val); - if (thd->is_fatal_error) - DBUG_RETURN(TRUE); - /* - The constant is the value to look for with this key. Copy - the value to ref->key_buff - */ - tmp.copy(); + { + /* key is const, copy value now and possibly skip it while ::exec() */ + enum store_key::store_key_result result= key->copy(); + + /* Depending on 'result' it should be reevaluated in ::exec(), if either: + * 1) '::copy()' failed, in case we reevaluate - and refail in + * JOIN::exec() where the error can be handled. + * 2) Constant evaluated to NULL value which we might need to + * handle as a special case during JOIN::exec() + * (As in : 'Full scan on NULL key') + */ + if (result!=store_key::STORE_KEY_OK || // 1) + key->null_key) // 2) + { + *ref_key++= key; // Reevaluate in JOIN::exec() + } } /* Remember if we are going to use REF_OR_NULL === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2010-12-29 00:38:59 +0000 +++ b/sql/sql_select.h 2011-01-14 08:20:08 +0000 @@ -2168,9 +2168,8 @@ public: store_key_const_item(THD *thd, Field *to_field_arg, uchar *ptr, uchar *null_ptr_arg, uint length, Item *item_arg) - :store_key_item(thd, to_field_arg,ptr, - null_ptr_arg ? null_ptr_arg : item_arg->maybe_null ? - &err : (uchar*) 0, length, item_arg), inited(0) + :store_key_item(thd, to_field_arg, ptr, + null_ptr_arg, length, item_arg), inited(0) { } const char *name() const { return "const"; } @@ -2178,27 +2177,13 @@ public: protected: enum store_key_result copy_inner() { - int res; if (!inited) { inited=1; - TABLE *table= to_field->table; - my_bitmap_map *old_map= dbug_tmp_use_all_columns(table, - table->write_set); - if ((res= item->save_in_field(to_field, 1))) - { - if (!err) - err= res < 0 ? 1 : res; /* 1=STORE_KEY_FATAL */ - } - /* - Item::save_in_field() may call Item::val_xxx(). And if this is a subquery - we need to check for errors executing it and react accordingly - */ - if (!err && to_field->table->in_use->is_error()) - err= 1; /* STORE_KEY_FATAL */ - dbug_tmp_restore_column_map(table->write_set, old_map); + int res= store_key_item::copy_inner(); + if (res && !err) + err= res; } - null_key= to_field->is_null() || item->null_value; return (err > 2 ? STORE_KEY_FATAL : (store_key_result) err); } }; No bundle (reason: useless for push emails).