Bug #54190: Comparison to row subquery produces incorrect

            result

Row subqueries producing no rows were not handled as UNKNOWN
values in row comparison expressions.

That was a result of the following two problems:

1. Item_singlerow_subselect did not mark the resulting row
value as NULL/UNKNOWN when no rows were produced.

2. Arg_comparator::compare_row() did not take into account that
a whole argument may be NULL rather than just individual scalar
values.

Before bug#34384 was fixed, the above problems were hidden
because an uninitialized (i.e. without any stored value) cached
object would appear as NULL for scalar values in a row subquery
returning an empty result. After the fix
Arg_comparator::compare_row() would try to evaluate
uninitialized cached objects.

Fixed by removing the aforementioned problems.


mysql-test/r/row.result:
  Added a test case for bug #54190.
mysql-test/r/subselect.result:
  Updated the result for a test relying on wrong behavior.
mysql-test/t/row.test:
  Added a test case for bug #54190.
sql/item_cmpfunc.cc:
  If either of the argument rows is NULL, return NULL as the
  result of comparison.
sql/item_subselect.cc:
  Adjust null_value for Item_singlerow_subselect depending on
  whether a row has been produced by the row subquery.

3 files changed, 44 insertions, 2 deletions

diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result
index 2962123fcb2..789b9c4f383 100644
--- a/mysql-test/r/row.result
+++ b/mysql-test/r/row.result
@@ -466,3 +466,26 @@ SELECT 1 FROM t1 WHERE ROW(a, b) >=
 ROW('1', (SELECT 1 FROM t1 WHERE a > 1234));
 1
 DROP TABLE t1;
+#
+# Bug #54190: Comparison to row subquery produces incorrect result
+# 
+SELECT ROW(1,2) = (SELECT 1,2 FROM DUAL WHERE 1 = 0);
+ROW(1,2) = (SELECT 1,2 FROM DUAL WHERE 1 = 0)
+NULL
+SELECT ROW(1,2) = (SELECT 1,3 FROM DUAL WHERE 1 = 0);
+ROW(1,2) = (SELECT 1,3 FROM DUAL WHERE 1 = 0)
+NULL
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 () VALUES (1), (2), (3);
+SELECT ROW(1,2) = (SELECT 1,2 FROM t1 WHERE 1 = 0);
+ROW(1,2) = (SELECT 1,2 FROM t1 WHERE 1 = 0)
+NULL
+SELECT ROW(1,2) = (SELECT 1,3 FROM t1 WHERE 1 = 0);
+ROW(1,2) = (SELECT 1,3 FROM t1 WHERE 1 = 0)
+NULL
+SELECT i FROM t1 WHERE ROW(1,2) = (SELECT 1,2 FROM DUAL WHERE 1 = 0);
+i
+SELECT i FROM t1 WHERE ROW(1,2) = (SELECT 1,3 FROM DUAL WHERE 1 = 0);
+i
+DROP TABLE t1;
+End of 5.1 tests
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 47a89897daf..dc40e42275b 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -922,7 +922,7 @@ select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t
 a	(select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a')	(select c from t1 where a=t2.a)
 1	1	a
 2	0	b
-NULL	0	NULL
+NULL	NULL	NULL
 select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;
 a	(select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b')	(select c from t1 where a=t2.a)
 1	0	a
@@ -932,7 +932,7 @@ select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t
 a	(select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c')	(select c from t1 where a=t2.a)
 1	0	a
 2	0	b
-NULL	0	NULL
+NULL	NULL	NULL
 drop table t1,t2;
 create table t1 (a int, b real, c varchar(10));
 insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test
index cec44078279..fb49ce23644 100644
--- a/mysql-test/t/row.test
+++ b/mysql-test/t/row.test
@@ -266,3 +266,22 @@ SELECT 1 FROM t1 WHERE ROW(a, b) >=
 ROW('1', (SELECT 1 FROM t1 WHERE a > 1234));
 --enable_warnings
 DROP TABLE t1;
+
+--echo #
+--echo # Bug #54190: Comparison to row subquery produces incorrect result
+--echo # 
+
+SELECT ROW(1,2) = (SELECT 1,2 FROM DUAL WHERE 1 = 0);
+SELECT ROW(1,2) = (SELECT 1,3 FROM DUAL WHERE 1 = 0);
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 () VALUES (1), (2), (3);
+
+SELECT ROW(1,2) = (SELECT 1,2 FROM t1 WHERE 1 = 0);
+SELECT ROW(1,2) = (SELECT 1,3 FROM t1 WHERE 1 = 0);
+SELECT i FROM t1 WHERE ROW(1,2) = (SELECT 1,2 FROM DUAL WHERE 1 = 0);
+SELECT i FROM t1 WHERE ROW(1,2) = (SELECT 1,3 FROM DUAL WHERE 1 = 0);
+
+DROP TABLE t1;
+
+--echo End of 5.1 tests