bk commit into 4.1 tree (gshchepa:1.2636) BUG#27704

[<gshchepa]

Below is the list of changes that have just been committed into a local
4.1 repository of uchum. When uchum does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-04-19 19:40:27+05:00, gshchepa@stripped +4 -0
  Bug#27704: incorrect comparison of rows with NULL components
  Support for NULL components was incomplete for row comparison.
  Fixed.

  mysql-test/r/row.result@stripped, 2007-04-19 19:40:00+05:00, gshchepa@stripped +116 -1
    Test case update for Bug#27704 (incorrect
    comparison of rows with NULL components)

  mysql-test/r/subselect.result@stripped, 2007-04-19 19:39:44+05:00, gshchepa@stripped +2
-2
    Test case update for Bug#27704 (incorrect
    comparison of rows with NULL components)

  mysql-test/t/row.test@stripped, 2007-04-19 19:40:10+05:00, gshchepa@stripped +51 -0
    Test case update for Bug#27704 (incorrect
    comparison of rows with NULL components)

  sql/item_cmpfunc.cc@stripped, 2007-04-19 19:40:22+05:00, gshchepa@stripped +29 -4
    Bug#27704: incorrect comparison of rows with NULL components
    1. backport from 5.x for compare by <> and =;
    2. fix for <, <=, >, >=.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gshchepa
# Host:	gshchepa.loc
# Root:	/home/uchum/work/bk-trees/mysql-4.1-opt-27704

--- 1.221/sql/item_cmpfunc.cc	2007-04-11 23:41:11 +05:00
+++ 1.222/sql/item_cmpfunc.cc	2007-04-19 19:40:22 +05:00
@@ -680,17 +680,42 @@ int Arg_comparator::compare_e_int_diff_s
 int Arg_comparator::compare_row()
 {
   int res= 0;
+  bool was_null= 0;
   (*a)->bring_value();
   (*b)->bring_value();
   uint n= (*a)->cols();
   for (uint i= 0; i<n; i++)
   {
-    if ((res= comparators[i].compare()))
-      return res;
+    res= comparators[i].compare();
     if (owner->null_value)
-      return -1;
+    {
+      // NULL was compared
+      switch (owner->functype()) {
+      case Item_func::LT_FUNC:
+      case Item_func::LE_FUNC:
+      case Item_func::GT_FUNC:
+      case Item_func::GE_FUNC:
+        return -1;
+      default:
+        break;
+      }
+      was_null= 1;
+      owner->null_value= 0;
+      res= 0;  // continue comparison (maybe we will meet explicit difference)
+    }
+    else if (res)
+      return res;
+  }
+  if (was_null)
+  {
+    /*
+      There was NULL(s) in comparison in some parts, but there was not
+      explicit difference in other parts, so we have to return NULL
+    */
+    owner->null_value= 1;
+    return -1;
   }
-  return res;
+  return 0;
 }
 
 int Arg_comparator::compare_e_row()

--- 1.19/mysql-test/r/row.result	2007-04-11 23:41:11 +05:00
+++ 1.20/mysql-test/r/row.result	2007-04-19 19:40:00 +05:00
@@ -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
@@ -63,21 +63,51 @@ ROW(2,2,3)=ROW(1+1,2,3)
 SELECT ROW(1,2,3)=ROW(1+1,2,3);
 ROW(1,2,3)=ROW(1+1,2,3)
 0
+SELECT ROW(1,2,3)<ROW(1,2,3);
+ROW(1,2,3)<ROW(1,2,3)
+0
 SELECT ROW(1,2,3)<ROW(1+1,2,3);
 ROW(1,2,3)<ROW(1+1,2,3)
 1
+SELECT ROW(1,2,3)<ROW(1,2+1,3);
+ROW(1,2,3)<ROW(1,2+1,3)
+1
+SELECT ROW(1,2,3)>ROW(1,2,3);
+ROW(1,2,3)>ROW(1,2,3)
+0
 SELECT ROW(1,2,3)>ROW(1+1,2,3);
 ROW(1,2,3)>ROW(1+1,2,3)
 0
+SELECT ROW(1,2,3)>ROW(1,2+1,3);
+ROW(1,2,3)>ROW(1,2+1,3)
+0
+SELECT ROW(1,2,3)<=ROW(1,2,3);
+ROW(1,2,3)<=ROW(1,2,3)
+1
 SELECT ROW(1,2,3)<=ROW(1+1,2,3);
 ROW(1,2,3)<=ROW(1+1,2,3)
 1
+SELECT ROW(1,2,3)<=ROW(1,2+1,3);
+ROW(1,2,3)<=ROW(1,2+1,3)
+1
+SELECT ROW(1,2,3)>=ROW(1,2,3);
+ROW(1,2,3)>=ROW(1,2,3)
+1
 SELECT ROW(1,2,3)>=ROW(1+1,2,3);
 ROW(1,2,3)>=ROW(1+1,2,3)
 0
+SELECT ROW(1,2,3)>=ROW(1,2+1,3);
+ROW(1,2,3)>=ROW(1,2+1,3)
+0
+SELECT ROW(1,2,3)<>ROW(1,2,3);
+ROW(1,2,3)<>ROW(1,2,3)
+0
 SELECT ROW(1,2,3)<>ROW(1+1,2,3);
 ROW(1,2,3)<>ROW(1+1,2,3)
 1
+SELECT ROW(1,2,3)<>ROW(1,2+1,3);
+ROW(1,2,3)<>ROW(1,2+1,3)
+1
 SELECT ROW(NULL,2,3)=ROW(NULL,2,3);
 ROW(NULL,2,3)=ROW(NULL,2,3)
 NULL
@@ -188,3 +218,88 @@ SELECT ROW(2,1) IN (ROW(21,2),ROW(ROW(1,
 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)
+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;

--- 1.187/mysql-test/r/subselect.result	2007-01-26 06:44:31 +04:00
+++ 1.188/mysql-test/r/subselect.result	2007-04-19 19:39:44 +05:00
@@ -913,7 +913,7 @@ select a, (select a,b,c from t1 where t1
 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	(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');

--- 1.18/mysql-test/t/row.test	2007-04-11 23:41:11 +05:00
+++ 1.19/mysql-test/t/row.test	2007-04-19 19:40:10 +05:00
@@ -28,11 +28,21 @@ SELECT (1,2,3)=(1,NULL,0);
 SELECT ROW(1,2,3)=ROW(1,2,3);
 SELECT ROW(2,2,3)=ROW(1+1,2,3);
 SELECT ROW(1,2,3)=ROW(1+1,2,3);
+SELECT ROW(1,2,3)<ROW(1,2,3);
 SELECT ROW(1,2,3)<ROW(1+1,2,3);
+SELECT ROW(1,2,3)<ROW(1,2+1,3);
+SELECT ROW(1,2,3)>ROW(1,2,3);
 SELECT ROW(1,2,3)>ROW(1+1,2,3);
+SELECT ROW(1,2,3)>ROW(1,2+1,3);
+SELECT ROW(1,2,3)<=ROW(1,2,3);
 SELECT ROW(1,2,3)<=ROW(1+1,2,3);
+SELECT ROW(1,2,3)<=ROW(1,2+1,3);
+SELECT ROW(1,2,3)>=ROW(1,2,3);
 SELECT ROW(1,2,3)>=ROW(1+1,2,3);
+SELECT ROW(1,2,3)>=ROW(1,2+1,3);
+SELECT ROW(1,2,3)<>ROW(1,2,3);
 SELECT ROW(1,2,3)<>ROW(1+1,2,3);
+SELECT ROW(1,2,3)<>ROW(1,2+1,3);
 SELECT ROW(NULL,2,3)=ROW(NULL,2,3);
 SELECT ROW(NULL,2,3)<=>ROW(NULL,2,3);
 SELECT ROW(1,2,ROW(3,4,5))=ROW(1,2,ROW(3,4,5));
@@ -107,5 +117,46 @@ SELECT ROW(1,ROW(2,3)) IN ((SELECT 1,1),
 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) < (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