Hi, Varun! Really, copy-pasted *8* times? Why didn't you make it a method of Item_in_subselect? On Feb 27, Varun wrote:

revision-id: b13cee83f9cfcffa98d227c492411a9acec85f42 (mariadb-10.2.3-282-gb13cee8) parent(s): b513e3711743e3ff24b3d14794a0fba87fd18c31 author: Varun Gupta committer: Varun Gupta timestamp: 2017-02-27 22:10:35 +0530 message:

MDEV-11078: NULL NOT IN (non-empty subquery) should never return results

Setting the value of cond guard variables during the creation of Tricond Item for null values

--- mysql-test/r/subselect4.result | 34 +++++++++++++++++++++++++++++ mysql-test/t/subselect4.test | 20 +++++++++++++++++ sql/item_subselect.cc | 49 ++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 103 insertions(+)

diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 94bc71c..acdbabd 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2113,6 +2113,13 @@ 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. */ + if(left_expr->basic_const_item() && !left_expr->is_expensive()) + { + left_expr->is_null(); + if(left_expr->null_value) + set_cond_guard_var(0,FALSE); + } + item= new (thd->mem_root) Item_func_trig_cond(thd, item, get_cond_guard(0)); }

@@ -2137,6 +2144,12 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, having= new (thd->mem_root) Item_is_not_null_test(thd, this, having); if (left_expr->maybe_null) { + if(left_expr->basic_const_item() && !left_expr->is_expensive()) + { + left_expr->is_null(); + if(left_expr->null_value) + set_cond_guard_var(0,FALSE); + } if (!(having= new (thd->mem_root) Item_func_trig_cond(thd, having, get_cond_guard(0)))) DBUG_RETURN(true); @@ -2155,6 +2168,12 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, */ if (!abort_on_null && left_expr->maybe_null) { + if(left_expr->basic_const_item() && !left_expr->is_expensive()) + { + left_expr->is_null(); + if(left_expr->null_value) + set_cond_guard_var(0,FALSE); + } if (!(item= new (thd->mem_root) Item_func_trig_cond(thd, item, get_cond_guard(0)))) DBUG_RETURN(true); @@ -2184,6 +2203,12 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, (char *)"<result>")); if (!abort_on_null && left_expr->maybe_null) { + if(left_expr->basic_const_item() && !left_expr->is_expensive()) + { + left_expr->is_null(); + if(left_expr->null_value) + set_cond_guard_var(0,FALSE); + } if (!(new_having= new (thd->mem_root) Item_func_trig_cond(thd, new_having, get_cond_guard(0)))) DBUG_RETURN(true); @@ -2383,6 +2408,12 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, Item_cond_or(thd, item_eq, item_isnull); if (!abort_on_null && left_expr->element_index(i)->maybe_null) { + if(left_expr->element_index(i)->basic_const_item() + && !left_expr->element_index(i)->is_expensive()) + { + if(left_expr->element_index(i)->is_null()) + set_cond_guard_var(i,FALSE); + } if (!(col_item= new (thd->mem_root) Item_func_trig_cond(thd, col_item, get_cond_guard(i)))) DBUG_RETURN(true); @@ -2400,6 +2431,12 @@ 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) { + if(left_expr->element_index(i)->basic_const_item() + && !left_expr->element_index(i)->is_expensive()) + { + if(left_expr->element_index(i)->is_null()) + set_cond_guard_var(i,FALSE); + } if (!(item_nnull_test= new (thd->mem_root) Item_func_trig_cond(thd, item_nnull_test, get_cond_guard(i)))) @@ -2460,6 +2497,12 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, item= new (thd->mem_root) Item_cond_or(thd, item, item_isnull); if (left_expr->element_index(i)->maybe_null) { + if(left_expr->element_index(i)->basic_const_item() + && !left_expr->element_index(i)->is_expensive()) + { + if(left_expr->element_index(i)->is_null()) + set_cond_guard_var(i,FALSE); + } if (!(item= new (thd->mem_root) Item_func_trig_cond(thd, item, get_cond_guard(i)))) DBUG_RETURN(true); @@ -2471,6 +2514,12 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, } if (!abort_on_null && left_expr->element_index(i)->maybe_null) { + if(left_expr->element_index(i)->basic_const_item() + && !left_expr->element_index(i)->is_expensive()) + { + if(left_expr->element_index(i)->is_null()) + set_cond_guard_var(i,FALSE); + } if (!(item= new (thd->mem_root) Item_func_trig_cond(thd, item, get_cond_guard(i)))) DBUG_RETURN(true);

Regards, Sergei Chief Architect MariaDB and security@mariadb.org