Bug#27704: incorrect comparison of rows with NULL components

Support for NULL components was incomplete for row comparison,
fixed.  Added support for abort_on_null at compare_row() like
in 5.x

3 files changed, 145 insertions, 3 deletions

diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result
index 1c1c4809f36..6169619712b 100644
--- a/mysql-test/r/row.result
+++ b/mysql-test/r/row.result
@@ -53,7 +53,7 @@ SELECT (1,2,3)=(1,NULL,3);
 NULL
 SELECT (1,2,3)=(1,NULL,0);
 (1,2,3)=(1,NULL,0)
-NULL
+0
 SELECT ROW(1,2,3)=ROW(1,2,3);
 ROW(1,2,3)=ROW(1,2,3)
 1
@@ -188,3 +188,100 @@ SELECT ROW(2,1) IN (ROW(21,2),ROW(ROW(1,1,3),0));
 ERROR 21000: Operand should contain 1 column(s)
 SELECT ROW(2,1) IN (ROW(ROW(1,1,3),0),ROW(21,2));
 ERROR 21000: Operand should contain 1 column(s)
+CREATE TABLE t1(a int, b int, c int);
+INSERT INTO t1 VALUES (1, 2, 3),
+(NULL, 2, 3  ), (1, NULL, 3  ), (1, 2,   NULL),
+(NULL, 2, 3+1), (1, NULL, 3+1), (1, 2+1, NULL),
+(NULL, 2, 3-1), (1, NULL, 3-1), (1, 2-1, NULL);
+SELECT (1,2,3) = (1,   NULL, 3);
+(1,2,3) = (1,   NULL, 3)
+NULL
+SELECT (1,2,3) = (1+1, NULL, 3);
+(1,2,3) = (1+1, NULL, 3)
+0
+SELECT (1,2,3) = (1,   NULL, 3+1);
+(1,2,3) = (1,   NULL, 3+1)
+0
+SELECT * FROM t1 WHERE (a,b,c) = (1,2,3);
+a	b	c
+1	2	3
+SELECT (1,2,3) <> (1,   NULL, 3);
+(1,2,3) <> (1,   NULL, 3)
+NULL
+SELECT (1,2,3) <> (1+1, NULL, 3);
+(1,2,3) <> (1+1, NULL, 3)
+1
+SELECT (1,2,3) <> (1,   NULL, 3+1);
+(1,2,3) <> (1,   NULL, 3+1)
+1
+SELECT * FROM t1 WHERE (a,b,c) <> (1,2,3);
+a	b	c
+NULL	2	4
+1	NULL	4
+1	3	NULL
+NULL	2	2
+1	NULL	2
+1	1	NULL
+SELECT (1,2,3) < (NULL, 2,    3);
+(1,2,3) < (NULL, 2,    3)
+NULL
+SELECT (1,2,3) < (1,    NULL, 3);
+(1,2,3) < (1,    NULL, 3)
+NULL
+SELECT (1,2,3) < (1-1,  NULL, 3);
+(1,2,3) < (1-1,  NULL, 3)
+0
+SELECT (1,2,3) < (1+1,  NULL, 3);
+(1,2,3) < (1+1,  NULL, 3)
+1
+SELECT * FROM t1 WHERE (a,b,c) < (1,2,3);
+a	b	c
+1	1	NULL
+SELECT (1,2,3) <= (NULL, 2,    3);
+(1,2,3) <= (NULL, 2,    3)
+NULL
+SELECT (1,2,3) <= (1,    NULL, 3);
+(1,2,3) <= (1,    NULL, 3)
+NULL
+SELECT (1,2,3) <= (1-1,  NULL, 3);
+(1,2,3) <= (1-1,  NULL, 3)
+0
+SELECT (1,2,3) <= (1+1,  NULL, 3);
+(1,2,3) <= (1+1,  NULL, 3)
+1
+SELECT * FROM t1 WHERE (a,b,c) <= (1,2,3);
+a	b	c
+1	2	3
+1	1	NULL
+SELECT (1,2,3) > (NULL, 2,    3);
+(1,2,3) > (NULL, 2,    3)
+NULL
+SELECT (1,2,3) > (1,    NULL, 3);
+(1,2,3) > (1,    NULL, 3)
+NULL
+SELECT (1,2,3) > (1-1,  NULL, 3);
+(1,2,3) > (1-1,  NULL, 3)
+1
+SELECT (1,2,3) > (1+1,  NULL, 3);
+(1,2,3) > (1+1,  NULL, 3)
+0
+SELECT * FROM t1 WHERE (a,b,c) > (1,2,3);
+a	b	c
+1	3	NULL
+SELECT (1,2,3) >= (NULL, 2,    3);
+(1,2,3) >= (NULL, 2,    3)
+NULL
+SELECT (1,2,3) >= (1,    NULL, 3);
+(1,2,3) >= (1,    NULL, 3)
+NULL
+SELECT (1,2,3) >= (1-1,  NULL, 3);
+(1,2,3) >= (1-1,  NULL, 3)
+1
+SELECT (1,2,3) >= (1+1,  NULL, 3);
+(1,2,3) >= (1+1,  NULL, 3)
+0
+SELECT * FROM t1 WHERE (a,b,c) >= (1,2,3);
+a	b	c
+1	2	3
+1	3	NULL
+DROP TABLE t1;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index a339a139687..5bb79a53771 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -913,7 +913,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	NULL	NULL
+NULL	0	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
@@ -923,7 +923,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	NULL	NULL
+NULL	0	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 6c66d45b942..6f845607d8c 100644
--- a/mysql-test/t/row.test
+++ b/mysql-test/t/row.test
@@ -108,4 +108,49 @@ SELECT ROW(2,1) IN (ROW(21,2),ROW(ROW(1,1,3),0));
 --error 1241
 SELECT ROW(2,1) IN (ROW(ROW(1,1,3),0),ROW(21,2));
 
+#
+# Bug#27704: erroneous comparison of rows with NULL components
+#
+CREATE TABLE t1(a int, b int, c int);
+INSERT INTO t1 VALUES (1, 2, 3),
+  (NULL, 2, 3  ), (1, NULL, 3  ), (1, 2,   NULL),
+  (NULL, 2, 3+1), (1, NULL, 3+1), (1, 2+1, NULL),
+  (NULL, 2, 3-1), (1, NULL, 3-1), (1, 2-1, NULL);
+
+SELECT (1,2,3) = (1,   NULL, 3);
+SELECT (1,2,3) = (1+1, NULL, 3);
+SELECT (1,2,3) = (1,   NULL, 3+1);
+SELECT * FROM t1 WHERE (a,b,c) = (1,2,3);
+
+SELECT (1,2,3) <> (1,   NULL, 3);
+SELECT (1,2,3) <> (1+1, NULL, 3);
+SELECT (1,2,3) <> (1,   NULL, 3+1);
+SELECT * FROM t1 WHERE (a,b,c) <> (1,2,3);
+
+SELECT (1,2,3) < (NULL, 2,    3);
+SELECT (1,2,3) < (1,    NULL, 3);
+SELECT (1,2,3) < (1-1,  NULL, 3);
+SELECT (1,2,3) < (1+1,  NULL, 3);
+SELECT * FROM t1 WHERE (a,b,c) < (1,2,3);
+
+SELECT (1,2,3) <= (NULL, 2,    3);
+SELECT (1,2,3) <= (1,    NULL, 3);
+SELECT (1,2,3) <= (1-1,  NULL, 3);
+SELECT (1,2,3) <= (1+1,  NULL, 3);
+SELECT * FROM t1 WHERE (a,b,c) <= (1,2,3);
+
+SELECT (1,2,3) > (NULL, 2,    3);
+SELECT (1,2,3) > (1,    NULL, 3);
+SELECT (1,2,3) > (1-1,  NULL, 3);
+SELECT (1,2,3) > (1+1,  NULL, 3);
+SELECT * FROM t1 WHERE (a,b,c) > (1,2,3);
+
+SELECT (1,2,3) >= (NULL, 2,    3);
+SELECT (1,2,3) >= (1,    NULL, 3);
+SELECT (1,2,3) >= (1-1,  NULL, 3);
+SELECT (1,2,3) >= (1+1,  NULL, 3);
+SELECT * FROM t1 WHERE (a,b,c) >= (1,2,3);
+
+DROP TABLE t1;
+
 # End of 4.1 tests