#At file:///localhome/jl208045/mysql/mysql-5.1-branches/mysql-5.1/ based on revid:bjorn.munch@stripped
3180 Jorgen Loland 2009-10-26
Bug#48177 - SELECTs with NOT IN subqueries containing NULL
values return too many records
WHERE clauses with "outer_value_list NOT IN subselect" were
handled incorrectly if the outer value list contained multiple
items where at least one of these could be NULL. The first
outer record with NULL value was handled correctly, but if a
second record with NULL value existed, the optimizer would
choose to reuse the result it got on the last execution of the
subselect. This is incorrect if the outer value list has
multiple items.
The fix is to make Item_in_optimizer::val_int (in
item_cmpfunc.cc) reuse the result of the latest execution
for NULL values only if all values in the outer_value_list
are NULL.
@ mysql-test/r/subselect3.result
Added test for BUG#48177
@ mysql-test/t/subselect3.test
Added test for BUG#48177
@ sql/item_cmpfunc.cc
Make Item_in_optimizer::val_int (in item_cmpfunc.cc) reuse the result of the latest execution for NULL values only if all values in the outer_value_list are NULL.
modified:
mysql-test/r/subselect3.result
mysql-test/t/subselect3.test
sql/item_cmpfunc.cc
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result 2009-08-31 14:09:09 +0000
+++ b/mysql-test/r/subselect3.result 2009-10-26 11:49:16 +0000
@@ -895,3 +895,58 @@ t1.a < (select t4.a+10
from t4, t5 limit 2));
ERROR 21000: Subquery returns more than 1 row
drop table t0, t1, t2, t3, t4, t5;
+#
+# BUG#48177 - SELECTs with NOT IN subqueries containing NULL
+# values return too many records
+#
+CREATE TABLE t1 (
+i1 int DEFAULT NULL,
+i2 int DEFAULT NULL
+) ;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, 3);
+INSERT INTO t1 VALUES (4, NULL);
+INSERT INTO t1 VALUES (4, 0);
+INSERT INTO t1 VALUES (NULL, NULL);
+INSERT INTO t1 VALUES (NULL, NULL);
+# ^ Use Handler_read_rnd_next to check that the subquery is evaluated
+# for only the first [NULL,NULL] record in t1.
+CREATE TABLE t2 (
+i1 int DEFAULT NULL,
+i2 int DEFAULT NULL
+) ;
+INSERT INTO t2 VALUES (4, NULL);
+INSERT INTO t2 VALUES (5, 0);
+
+Data in t1
+SELECT i1, i2 FROM t1;
+i1 i2
+1 NULL
+2 3
+4 NULL
+4 0
+NULL NULL
+NULL NULL
+
+Data in subquery (should be filtered out)
+SELECT i1, i2 FROM t2 ORDER BY i1;
+i1 i2
+4 NULL
+5 0
+
+FLUSH STATUS;
+
+SELECT i1, i2
+FROM t1
+WHERE (i1, i2)
+NOT IN (SELECT i1, i2 FROM t2);
+i1 i2
+1 NULL
+2 3
+
+# Handler_read_rnd_next should be 18
+SHOW STATUS LIKE '%Handler_read_rnd_next';
+Variable_name Value
+Handler_read_rnd_next 18
+DROP TABLE t1,t2;
+End of 5.1 tests
=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test 2009-08-31 14:09:09 +0000
+++ b/mysql-test/t/subselect3.test 2009-10-26 11:49:16 +0000
@@ -728,3 +728,55 @@ where
from t4, t5 limit 2));
drop table t0, t1, t2, t3, t4, t5;
+
+--echo #
+--echo # BUG#48177 - SELECTs with NOT IN subqueries containing NULL
+--echo # values return too many records
+--echo #
+
+CREATE TABLE t1 (
+ i1 int DEFAULT NULL,
+ i2 int DEFAULT NULL
+) ;
+
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, 3);
+INSERT INTO t1 VALUES (4, NULL);
+INSERT INTO t1 VALUES (4, 0);
+INSERT INTO t1 VALUES (NULL, NULL);
+INSERT INTO t1 VALUES (NULL, NULL);
+--echo # ^ Use Handler_read_rnd_next to check that the subquery is evaluated
+--echo # for only the first [NULL,NULL] record in t1.
+
+CREATE TABLE t2 (
+ i1 int DEFAULT NULL,
+ i2 int DEFAULT NULL
+) ;
+
+INSERT INTO t2 VALUES (4, NULL);
+INSERT INTO t2 VALUES (5, 0);
+
+--echo
+--echo Data in t1
+SELECT i1, i2 FROM t1;
+
+--echo
+--echo Data in subquery (should be filtered out)
+SELECT i1, i2 FROM t2 ORDER BY i1;
+
+--echo
+FLUSH STATUS;
+
+--echo
+SELECT i1, i2
+FROM t1
+WHERE (i1, i2)
+ NOT IN (SELECT i1, i2 FROM t2);
+
+--echo
+--echo # Handler_read_rnd_next should be 18
+SHOW STATUS LIKE '%Handler_read_rnd_next';
+
+DROP TABLE t1,t2;
+
+--echo End of 5.1 tests
=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc 2009-10-05 05:27:36 +0000
+++ b/sql/item_cmpfunc.cc 2009-10-26 11:49:16 +0000
@@ -1559,61 +1559,72 @@ longlong Item_in_optimizer::val_int()
if (cache->null_value)
{
+ /*
+ We're evaluating
+ "<outer_value_list> IN (SELECT <inner_value_list>...)"
+ where one of the outer values (the cached one) is NULL.
+ */
if (((Item_in_subselect*)args[1])->is_top_level_item())
{
/*
- We're evaluating "NULL IN (SELECT ...)". The result can be NULL or
- FALSE, and we can return one instead of another. Just return NULL.
+ The case where a NULL value in the outer_value_list means that
+ the result shall be NULL/FALSE (makes no difference). The
+ cached value is NULL, so just return NULL.
*/
null_value= 1;
}
else
{
- if (!((Item_in_subselect*)args[1])->is_correlated &&
- result_for_null_param != UNKNOWN)
+ /*
+ The case where a NULL value in either the outer or inner value
+ list does not automatically mean that we can return
+ NULL/FALSE. An example of such a query is
+ "<outer_value_list> NOT IN (SELECT <inner_value_list>...)"
+ The result when there is at least one NULL value is:
+ NULL if the SELECT evaluated over the non-NULL values
+ produces at least one row,
+ FALSE otherwise
+ */
+ Item_in_subselect *item_subs=(Item_in_subselect*)args[1];
+ bool all_left_cols_null= true;
+ const uint ncols= cache->cols();
+
+ /*
+ Turn off the predicates that are based on column compares for
+ which the left part is currently NULL
+ */
+ uint i;
+ for (i= 0; i < ncols; i++)
{
- /* Use cached value from previous execution */
+ if (cache->element_index(i)->null_value)
+ item_subs->set_cond_guard_var(i, FALSE);
+ else
+ all_left_cols_null= false;
+ }
+
+ if (!((Item_in_subselect*)args[1])->is_correlated &&
+ all_left_cols_null && result_for_null_param != UNKNOWN)
+ {
+ /*
+ This is a non-correlated subquery, all values in the outer
+ value list are NULL, and we have already evaluated the
+ subquery for all NULL values: Return the same result we
+ did last time without evaluating the subquery.
+ */
null_value= result_for_null_param;
- }
- else
+ }
+ else
{
- /*
- We're evaluating "NULL IN (SELECT ...)". The result is:
- FALSE if SELECT produces an empty set, or
- NULL otherwise.
- We disable the predicates we've pushed down into subselect, run the
- subselect and see if it has produced any rows.
- */
- Item_in_subselect *item_subs=(Item_in_subselect*)args[1];
- if (cache->cols() == 1)
- {
- item_subs->set_cond_guard_var(0, FALSE);
- (void) args[1]->val_bool_result();
- result_for_null_param= null_value= !item_subs->engine->no_rows();
- item_subs->set_cond_guard_var(0, TRUE);
- }
- else
- {
- uint i;
- uint ncols= cache->cols();
- /*
- Turn off the predicates that are based on column compares for
- which the left part is currently NULL
- */
- for (i= 0; i < ncols; i++)
- {
- if (cache->element_index(i)->null_value)
- item_subs->set_cond_guard_var(i, FALSE);
- }
-
- (void) args[1]->val_bool_result();
- result_for_null_param= null_value= !item_subs->engine->no_rows();
-
- /* Turn all predicates back on */
- for (i= 0; i < ncols; i++)
- item_subs->set_cond_guard_var(i, TRUE);
- }
+ /* The subquery has to be evaluated */
+ (void) args[1]->val_bool_result();
+ null_value= !item_subs->engine->no_rows();
+ if (all_left_cols_null)
+ result_for_null_param= null_value;
}
+
+ /* Turn all predicates back on */
+ for (i= 0; i < ncols; i++)
+ item_subs->set_cond_guard_var(i, TRUE);
}
return 0;
}
Attachment: [text/bzr-bundle] bzr/jorgen.loland@sun.com-20091026114916-2wsz2v2qvn3rdovi.bundle
