From 43903745e5af676db1fe813ab8e2ba7190353f83 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Sun, 5 Mar 2017 10:58:05 +0530 Subject: MDEV-11078: NULL NOT IN (non-empty subquery) should never return results Disabling the cond guards during the creation of Tricond Item for constant and NULL left expression items --- mysql-test/r/subselect4.result | 34 ++++++++++++++++++++++++++++++++++ mysql-test/t/subselect4.test | 19 +++++++++++++++++++ sql/item_subselect.cc | 7 +++++++ sql/item_subselect.h | 8 ++++++++ 4 files changed, 68 insertions(+) diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 89fb0902f53..2a229675817 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2449,5 +2449,39 @@ EXECUTE stmt; i 6 drop table t1, t2, t3; +# +# MDEV-11078: NULL NOT IN (non-empty subquery) should never return results +# +create table t1(a int,b int); +create table t2(a int,b int); +insert into t1 value (1,2); +select (NULL) in (select 1 from t1); +(NULL) in (select 1 from t1) +NULL +select (null) in (select 1 from t2); +(null) in (select 1 from t2) +0 +select 1 in (select 1 from t1); +1 in (select 1 from t1) +1 +select 1 in (select 1 from t2); +1 in (select 1 from t2) +0 +select 1 from dual where null in (select 1 from t1); +1 +select 1 from dual where null in (select 1 from t2); +1 +select (null,null) in (select * from t1); +(null,null) in (select * from t1) +NULL +select (null,null) in (select * from t2); +(null,null) in (select * from t2) +0 +select 1 from dual where null not in (select 1 from t1); +1 +select 1 from dual where null not in (select 1 from t2); +1 +1 +drop table t1,t2; SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 7a7dd7e492e..056152cc706 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -2000,5 +2000,24 @@ EXECUTE stmt; drop table t1, t2, t3; +--echo # +--echo # MDEV-11078: NULL NOT IN (non-empty subquery) should never return results +--echo # + +create table t1(a int,b int); +create table t2(a int,b int); +insert into t1 value (1,2); +select (NULL) in (select 1 from t1); +select (null) in (select 1 from t2); +select 1 in (select 1 from t1); +select 1 in (select 1 from t2); +select 1 from dual where null in (select 1 from t1); +select 1 from dual where null in (select 1 from t2); +select (null,null) in (select * from t1); +select (null,null) in (select * from t2); +select 1 from dual where null not in (select 1 from t1); +select 1 from dual where null not in (select 1 from t2); +drop table t1,t2; + SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 5e00220db55..78dcfc4215c 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2035,6 +2035,7 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, We can encounter "NULL IN (SELECT ...)". Wrap the added condition within a trig_cond. */ + disable_cond_guard_for_const_null_left_expr(0); item= new Item_func_trig_cond(item, get_cond_guard(0)); } @@ -2059,6 +2060,7 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, having= new Item_is_not_null_test(this, having); if (left_expr->maybe_null) { + disable_cond_guard_for_const_null_left_expr(0); if (!(having= new Item_func_trig_cond(having, get_cond_guard(0)))) DBUG_RETURN(true); @@ -2077,6 +2079,7 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, */ if (!abort_on_null && left_expr->maybe_null) { + disable_cond_guard_for_const_null_left_expr(0); if (!(item= new Item_func_trig_cond(item, get_cond_guard(0)))) DBUG_RETURN(true); } @@ -2103,6 +2106,7 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, (char *)"")); if (!abort_on_null && left_expr->maybe_null) { + disable_cond_guard_for_const_null_left_expr(0); if (!(new_having= new Item_func_trig_cond(new_having, get_cond_guard(0)))) DBUG_RETURN(true); @@ -2299,6 +2303,7 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, Item *col_item= new Item_cond_or(item_eq, item_isnull); if (!abort_on_null && left_expr->element_index(i)->maybe_null) { + disable_cond_guard_for_const_null_left_expr(i); if (!(col_item= new Item_func_trig_cond(col_item, get_cond_guard(i)))) DBUG_RETURN(true); } @@ -2313,6 +2318,7 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, (char *)"")); if (!abort_on_null && left_expr->element_index(i)->maybe_null) { + disable_cond_guard_for_const_null_left_expr(i); if (!(item_nnull_test= new Item_func_trig_cond(item_nnull_test, get_cond_guard(i)))) DBUG_RETURN(true); @@ -2373,6 +2379,7 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, */ if (left_expr->element_index(i)->maybe_null) { + disable_cond_guard_for_const_null_left_expr(i); if (!(item= new Item_func_trig_cond(item, get_cond_guard(i)))) DBUG_RETURN(true); if (!(having_col_item= diff --git a/sql/item_subselect.h b/sql/item_subselect.h index a44503b4471..2f166c83e8f 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -596,6 +596,14 @@ public: void set_first_execution() { if (first_execution) first_execution= FALSE; } bool expr_cache_is_needed(THD *thd); inline bool left_expr_has_null(); + void disable_cond_guard_for_const_null_left_expr(int i) + { + if (left_expr->const_item() && !left_expr->is_expensive()) + { + if (left_expr->element_index(i)->is_null()) + set_cond_guard_var(i,FALSE); + } + } int optimize(double *out_rows, double *cost); /* -- cgit v1.2.1