From 7d8adf6bd7875029207b721354c88ab77aac1bf8 Mon Sep 17 00:00:00 2001 From: tianfengli Date: Mon, 2 Sep 2024 17:08:25 +0800 Subject: [PATCH] row comparison decomposition for SelectList should not ignore NULL Problem: Last patch introduced a method for decomposing row comparisons. Through its implementation, all newly generated Item_cond instances have `abort_on_null` set to `true`. This causes row comparisons in SelectList that contains `Null` to treat `False` as identical to `Null`. Solution: Modify the method for creating new Item_cond instances to avoid setting `abort_on_null` to true. However, as a side effect, this may also prevent the const condition optimization such as remove_const_cond() in such cases. --- mysql-test/r/row.result | 8 ++-- mysql-test/r/row_value_comparison.result | 15 ++++++++ mysql-test/t/row_value_comparison.test | 9 +++++ sql/row_comparison_decomposer.cc | 49 +++++++++++++----------- 4 files changed, 54 insertions(+), 27 deletions(-) diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result index 708aa1d3c1a..29e42878ef8 100644 --- a/mysql-test/r/row.result +++ b/mysql-test/r/row.result @@ -574,7 +574,7 @@ EXPLAIN SELECT row( min(a), 1 ) > row( a, 1 ) AS al FROM t1 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary Warnings: -Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`),1) > (`test`.`t1`.`a`,1)) AS `al` from `test`.`t1` group by `test`.`t1`.`a` +Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`) > `test`.`t1`.`a`) or ((min(`test`.`t1`.`a`) = `test`.`t1`.`a`) and (1 > 1))) AS `al` from `test`.`t1` group by `test`.`t1`.`a` SELECT row( min(a), 1 ) > row( a, 1 ) AS al FROM t1 GROUP BY a; al 0 @@ -582,7 +582,7 @@ EXPLAIN SELECT row( min(a), 1 ) >= row( a, 1 ) AS al FROM t1 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary Warnings: -Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`),1) >= (`test`.`t1`.`a`,1)) AS `al` from `test`.`t1` group by `test`.`t1`.`a` +Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`) > `test`.`t1`.`a`) or ((min(`test`.`t1`.`a`) = `test`.`t1`.`a`) and (1 >= 1))) AS `al` from `test`.`t1` group by `test`.`t1`.`a` SELECT row( min(a), 1 ) >= row( a, 1 ) AS al FROM t1 GROUP BY a; al 1 @@ -590,7 +590,7 @@ EXPLAIN SELECT row( min(a), 1 ) < row( a, 1 ) AS al FROM t1 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary Warnings: -Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`),1) < (`test`.`t1`.`a`,1)) AS `al` from `test`.`t1` group by `test`.`t1`.`a` +Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`) < `test`.`t1`.`a`) or ((min(`test`.`t1`.`a`) = `test`.`t1`.`a`) and (1 < 1))) AS `al` from `test`.`t1` group by `test`.`t1`.`a` SELECT row( min(a), 1 ) < row( a, 1 ) AS al FROM t1 GROUP BY a; al 0 @@ -598,7 +598,7 @@ EXPLAIN SELECT row( min(a), 1 ) <= row( a, 1 ) AS al FROM t1 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary Warnings: -Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`),1) <= (`test`.`t1`.`a`,1)) AS `al` from `test`.`t1` group by `test`.`t1`.`a` +Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`) < `test`.`t1`.`a`) or ((min(`test`.`t1`.`a`) = `test`.`t1`.`a`) and (1 <= 1))) AS `al` from `test`.`t1` group by `test`.`t1`.`a` SELECT row( min(a), 1 ) <= row( a, 1 ) AS al FROM t1 GROUP BY a; al 1 diff --git a/mysql-test/r/row_value_comparison.result b/mysql-test/r/row_value_comparison.result index 47290a4d043..46699718664 100644 --- a/mysql-test/r/row_value_comparison.result +++ b/mysql-test/r/row_value_comparison.result @@ -220,4 +220,19 @@ id c1 c2 a b c 2 2 2 NULL NULL NULL 3 3 3 NULL NULL NULL drop table t2, t1; +select (1, 2, 3) = (0, NULL, 3); +(1, 2, 3) = (0, NULL, 3) +0 +select (1, 2, 3) = (1, NULL, 3); +(1, 2, 3) = (1, NULL, 3) +NULL +select (1, 2, 3) <=> (1, NULL, 3); +(1, 2, 3) <=> (1, NULL, 3) +0 +select (1, 2, 3) >= (1, NULL, 3); +(1, 2, 3) >= (1, NULL, 3) +NULL +select (1, NULL, 3) >= (4, 2, NULL); +(1, NULL, 3) >= (4, 2, NULL) +0 set row_comparison_decompose_threshold = default; diff --git a/mysql-test/t/row_value_comparison.test b/mysql-test/t/row_value_comparison.test index b50741d0165..5794371bdc8 100644 --- a/mysql-test/t/row_value_comparison.test +++ b/mysql-test/t/row_value_comparison.test @@ -170,4 +170,13 @@ select * from t1 left join t2 on ((null,t1.c1+t2.c)>(1,2)) order by 1; drop table t2, t1; +# +# BUG: skip set abort_on_null flag when create condition item. +# +select (1, 2, 3) = (0, NULL, 3); +select (1, 2, 3) = (1, NULL, 3); +select (1, 2, 3) <=> (1, NULL, 3); +select (1, 2, 3) >= (1, NULL, 3); +select (1, NULL, 3) >= (4, 2, NULL); + set row_comparison_decompose_threshold = default; \ No newline at end of file diff --git a/sql/row_comparison_decomposer.cc b/sql/row_comparison_decomposer.cc index 0be7be250a2..a68fafd7323 100644 --- a/sql/row_comparison_decomposer.cc +++ b/sql/row_comparison_decomposer.cc @@ -240,16 +240,7 @@ static inline Item *decompose_row_value_op_straight(Parse_context *pc, assert(left->type() == right->type() && left->cols() == right->cols()); - Item_cond *result; - if (type != RowValueCompareType::NE) { - result = new (pc->mem_root) Item_cond_and; - } else { - result = new (pc->mem_root) Item_cond_or; - } - if (result == nullptr) { - return nullptr; - } - + List list; uint cols = left->cols(); for (uint i = 0; i < cols; i++) { Item *item_i = nullptr; @@ -268,9 +259,15 @@ static inline Item *decompose_row_value_op_straight(Parse_context *pc, if (item_i == nullptr) { return nullptr; } - result->add(item_i); + list.push_back(item_i); } + Item_cond *result; + if (type != RowValueCompareType::NE) { + result = new (pc->mem_root) Item_cond_and(list); + } else { + result = new (pc->mem_root) Item_cond_or(list); + } return result; } @@ -282,10 +279,7 @@ static inline Item *decompose_row_value_op_combine(Parse_context *pc, assert(left->type() == right->type() && left->cols() == right->cols()); - Item_cond_or *result = new (pc->mem_root) Item_cond_or; - if (result == nullptr) { - return nullptr; - } + List list; Item_cond_and *and_prefix = nullptr; uint cols = left->cols(); @@ -321,7 +315,7 @@ static inline Item *decompose_row_value_op_combine(Parse_context *pc, if (tuple_i == nullptr) { return nullptr; } - result->add(tuple_i); + list.push_back(tuple_i); // Skip build and_prefix for the last element. if (i == cols - 1) { @@ -330,22 +324,31 @@ static inline Item *decompose_row_value_op_combine(Parse_context *pc, // build new equal function for this `left[i] = right[i]`. Item_func *eq_i = new (pc->mem_root) Item_func_eq(left->element_index(i), right->element_index(i)); + if (eq_i == nullptr) { + return nullptr; + } + // inheritate the previous and_prefix Item_cond_and *new_prefix; if (and_prefix == nullptr) { - new_prefix = new (pc->mem_root) Item_cond_and(); + List eq_list; + eq_list.push_back(eq_i); + new_prefix = new (pc->mem_root) Item_cond_and(eq_list); + if (new_prefix == nullptr) { + return nullptr; + } } else { new_prefix = new (pc->mem_root) Item_cond_and(*and_prefix->argument_list()); - } - - if (new_prefix == nullptr || eq_i == nullptr) { - return nullptr; + if (new_prefix == nullptr) { + return nullptr; + } + new_prefix->add(eq_i); } // update and_prefix with new equal function. - new_prefix->add(eq_i); and_prefix = new_prefix; } - return result; + + return new (pc->mem_root) Item_cond_or(list); } -- 2.19.1