From 5a21416f240da6f068d90127606f24b79033c62b Mon Sep 17 00:00:00 2001
From: ximinliang <ximinliang@tencent.com>
Date: Tue, 18 Feb 2025 19:19:10 +0800
Subject: [PATCH] bug #117500 query result error when empty result

https://bugs.mysql.com/bug.php?id=117500
---
 mysql-test/r/subquery_bugs.result | 16 ++++++++++++++
 mysql-test/t/subquery_bugs.test   | 19 ++++++++++++++++
 sql/item_cmpfunc.cc               | 36 ++++++++++++++++++++++++++-----
 3 files changed, 66 insertions(+), 5 deletions(-)

diff --git a/mysql-test/r/subquery_bugs.result b/mysql-test/r/subquery_bugs.result
index cdbfcc8e7a49..05882bdbc429 100644
--- a/mysql-test/r/subquery_bugs.result
+++ b/mysql-test/r/subquery_bugs.result
@@ -2480,3 +2480,19 @@ UNION ALL
 SELECT 1 FROM t;
 f
 DROP TABLE t;
+#
+# Bug#117500: Subquery result error when row compared with scalar subquery using '<=>'
+#
+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 c1, c2 FROM t1 WHERE (c1, c2) <=> ( SELECT c1, c2 FROM t2 WHERE c1 = ( SELECT c1 FROM t3 ) );
+c1	c2
+SELECT c1, c2 FROM t1 WHERE (c1, c2) <=> ( SELECT c1, c2 FROM t2 ORDER BY t2.c1 LIMIT 1 );
+c1	c2
+NULL	1992
+DROP TABLE t1, t2, t3;
diff --git a/mysql-test/t/subquery_bugs.test b/mysql-test/t/subquery_bugs.test
index 6cae0b0ecd68..1b0ee4d80b2b 100644
--- a/mysql-test/t/subquery_bugs.test
+++ b/mysql-test/t/subquery_bugs.test
@@ -2103,3 +2103,22 @@ UNION ALL
 SELECT 1 FROM t;
 
 DROP TABLE t;
+
+--echo #
+--echo # Bug#117500: Subquery result error when row compared with scalar subquery using '<=>'
+--echo #
+
+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 c1, c2 FROM t1 WHERE (c1, c2) <=> ( SELECT c1, c2 FROM t2 WHERE c1 = ( SELECT c1 FROM t3 ) );  
+SELECT c1, c2 FROM t1 WHERE (c1, c2) <=> ( SELECT c1, c2 FROM t2 ORDER BY t2.c1 LIMIT 1 );
+
+DROP TABLE t1, t2, t3;
+
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 6cc3811b444b..733924656535 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;
 }