Description:
Hello MySQL Team:
Here is a bug about result error when row compared with scalar subquery using '<=>'。
Can be repeated in MySQL 9.2.0.
How to repeat:
Using follow SQLs:
create table t3(c1 varchar(10), c2 int);
create table t2 like t3;
create table t1 like t3;
insert into t1 values (null, 1992), ('a', null);
insert into t2 select * from t1;
insert into t3 values ('b', 1995);
insert into t1 values ('b', null);
select/*qb1*/ c1, c2 from t1 where (c1,c2) <=> (select/*qb2*/ c1, c2 from t2 where c1 = (select/*qb3*/ c1 from t3));
+------+------+
| c1 | c2 |
+------+------+
| a | NULL |
| b | NULL |
+------+------+
The expected result is empty because scalar subquery in where cond is empty:
mysql> select c1, c2 from t2 where c1 = (select c1 from t3);
Empty set (0.00 sec)
Suggested fix:
during SQL execution, qb2 has scaned t2 and c1, c2 finally stored ('a', null).
while qb1 scans t1, actually data from t1 compared with ('a', null).
Here is my suggested fix:
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 6cc3811b444..73392465653 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -2138,6 +2138,16 @@ int Arg_comparator::compare_row() {
return 0;
}
+static bool check_value_unassigned_scalar_subquery(Item *item) {
+ if (item->type() == Item::SUBQUERY_ITEM) {
+ Item_subselect *subquery = down_cast<Item_subselect *>(item);
+ if (subquery->subquery_type() == Item_subselect::SCALAR_SUBQUERY) {
+ return !subquery->is_value_assigned();
+ }
+ }
+ return false;
+}
+
/**
Compare two argument items, or a pair of elements from two argument rows,
for NULL values.
@@ -2149,7 +2159,8 @@ int Arg_comparator::compare_row() {
@returns true if at least one of the items is NULL
*/
-static bool compare_pair_for_nulls(Item *a, Item *b, bool *result) {
+static bool compare_pair_for_nulls(Arg_comparator *comparator, Item *a, Item *b,
+ bool *result) {
if (a->result_type() == ROW_RESULT) {
a->bring_value();
b->bring_value();
@@ -2159,9 +2170,23 @@ static bool compare_pair_for_nulls(Item *a, Item *b, bool *result) {
are NULL, result is true.
*/
bool have_null_items = false;
+ Item *item_null = nullptr;
+ bool left_is_value_unassigned_scalar_subquery =
+ check_value_unassigned_scalar_subquery(a);
+ bool right_is_value_unassigned_scalar_subquery =
+ check_value_unassigned_scalar_subquery(b);
+ if (left_is_value_unassigned_scalar_subquery ||
+ right_is_value_unassigned_scalar_subquery) {
+ item_null = new Item_null();
+ }
for (uint i = 0; i < a->cols(); i++) {
- if (compare_pair_for_nulls(a->element_index(i), b->element_index(i),
- result)) {
+ if (compare_pair_for_nulls(
+ &comparator->get_child_comparators()[i],
+ left_is_value_unassigned_scalar_subquery ? item_null
+ : a->element_index(i),
+ right_is_value_unassigned_scalar_subquery ? item_null
+ : b->element_index(i),
+ result)) {
have_null_items = true;
if (!*result) return true;
}
@@ -2176,7 +2201,8 @@ static bool compare_pair_for_nulls(Item *a, Item *b, bool *result) {
*result = a_null == b_null;
return true;
}
- *result = false;
+ assert(*comparator->get_left_ptr() == a && comparator->get_right() == b);
+ *result = comparator->compare() == 0;
return false;
}
@@ -2188,7 +2214,7 @@ static bool compare_pair_for_nulls(Item *a, Item *b, bool *result) {
*/
bool Arg_comparator::compare_null_values() {
bool result;
- (void)compare_pair_for_nulls(*left, *right, &result);
+ (void)compare_pair_for_nulls(this, *left, *right, &result);
if (current_thd->is_error()) return false;
return result;
}
Description: Hello MySQL Team: Here is a bug about result error when row compared with scalar subquery using '<=>'。 Can be repeated in MySQL 9.2.0. How to repeat: Using follow SQLs: create table t3(c1 varchar(10), c2 int); create table t2 like t3; create table t1 like t3; insert into t1 values (null, 1992), ('a', null); insert into t2 select * from t1; insert into t3 values ('b', 1995); insert into t1 values ('b', null); select/*qb1*/ c1, c2 from t1 where (c1,c2) <=> (select/*qb2*/ c1, c2 from t2 where c1 = (select/*qb3*/ c1 from t3)); +------+------+ | c1 | c2 | +------+------+ | a | NULL | | b | NULL | +------+------+ The expected result is empty because scalar subquery in where cond is empty: mysql> select c1, c2 from t2 where c1 = (select c1 from t3); Empty set (0.00 sec) Suggested fix: during SQL execution, qb2 has scaned t2 and c1, c2 finally stored ('a', null). while qb1 scans t1, actually data from t1 compared with ('a', null). Here is my suggested fix: diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 6cc3811b444..73392465653 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -2138,6 +2138,16 @@ int Arg_comparator::compare_row() { return 0; } +static bool check_value_unassigned_scalar_subquery(Item *item) { + if (item->type() == Item::SUBQUERY_ITEM) { + Item_subselect *subquery = down_cast<Item_subselect *>(item); + if (subquery->subquery_type() == Item_subselect::SCALAR_SUBQUERY) { + return !subquery->is_value_assigned(); + } + } + return false; +} + /** Compare two argument items, or a pair of elements from two argument rows, for NULL values. @@ -2149,7 +2159,8 @@ int Arg_comparator::compare_row() { @returns true if at least one of the items is NULL */ -static bool compare_pair_for_nulls(Item *a, Item *b, bool *result) { +static bool compare_pair_for_nulls(Arg_comparator *comparator, Item *a, Item *b, + bool *result) { if (a->result_type() == ROW_RESULT) { a->bring_value(); b->bring_value(); @@ -2159,9 +2170,23 @@ static bool compare_pair_for_nulls(Item *a, Item *b, bool *result) { are NULL, result is true. */ bool have_null_items = false; + Item *item_null = nullptr; + bool left_is_value_unassigned_scalar_subquery = + check_value_unassigned_scalar_subquery(a); + bool right_is_value_unassigned_scalar_subquery = + check_value_unassigned_scalar_subquery(b); + if (left_is_value_unassigned_scalar_subquery || + right_is_value_unassigned_scalar_subquery) { + item_null = new Item_null(); + } for (uint i = 0; i < a->cols(); i++) { - if (compare_pair_for_nulls(a->element_index(i), b->element_index(i), - result)) { + if (compare_pair_for_nulls( + &comparator->get_child_comparators()[i], + left_is_value_unassigned_scalar_subquery ? item_null + : a->element_index(i), + right_is_value_unassigned_scalar_subquery ? item_null + : b->element_index(i), + result)) { have_null_items = true; if (!*result) return true; } @@ -2176,7 +2201,8 @@ static bool compare_pair_for_nulls(Item *a, Item *b, bool *result) { *result = a_null == b_null; return true; } - *result = false; + assert(*comparator->get_left_ptr() == a && comparator->get_right() == b); + *result = comparator->compare() == 0; return false; } @@ -2188,7 +2214,7 @@ static bool compare_pair_for_nulls(Item *a, Item *b, bool *result) { */ bool Arg_comparator::compare_null_values() { bool result; - (void)compare_pair_for_nulls(*left, *right, &result); + (void)compare_pair_for_nulls(this, *left, *right, &result); if (current_thd->is_error()) return false; return result; }