3503 Ole John Aske 2011-01-14
Fix for bug#58628: Incorrect result for 'WHERE NULL NOT IN (<subquery>)
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 (<subquery>)
+--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 (<subquery>)
+#
+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 (<subquery>)
+#
+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 (<subquery>)
+#
+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 (<subquery>)
+#
+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 (<subquery>)
+#
+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).
| Thread |
|---|
| • bzr push into mysql-trunk branch (ole.john.aske:3502 to 3503) Bug#58628 | Ole John Aske | 14 Jan |
