[Commits] 683d89e: MDEV-11078: NULL NOT IN (non-empty subquery) should never return results

Varun varunraiko1803 at gmail.com
Wed Mar 1 15:45:10 EET 2017


revision-id: 683d89e89bd8f609db9d15edd0d9791e69763b7e (mariadb-5.5.54-62-g683d89e)
parent(s): 75f6067e89d7a777e7c1a1580a833aacc501d290
author: Varun Gupta
committer: Varun Gupta
timestamp: 2017-03-01 19:13:59 +0530
message:

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           |  6 ++++++
 4 files changed, 66 insertions(+)

diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 89fb090..2a22967 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 7a7dd7e..056152c 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 5e00220..78dcfc4 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 *)"<result>"));
         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 *)"<list ref>"));
       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 a44503b..e5d0231 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -596,6 +596,12 @@ class Item_in_subselect :public Item_exists_subselect
   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);
   /* 


More information about the commits mailing list