bk commit into 5.0 tree (igor:1.1903) BUG#12791

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor 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
  1.1903 05/08/26 22:25:45 igor@stripped +6 -0
  func_str.result, null.result:
    Corrected results after the fix for bug #12791.
  func_test.result, func_test.test:
    Added test cases for bug #12791.
  item_func.h, item_func.cc:
    Fixed bug #12791.
    Made LEAST/GREATES fully Oracle compliant.
    LEAST/GREATEST did not return NULL if only some
    arguments were NULLs. This did not comply with Oracle.

  mysql-test/r/func_str.result
    1.106 05/08/26 22:25:00 igor@stripped +1 -1
    Corrected results after the fix for bug #12791.

  mysql-test/r/null.result
    1.33 05/08/26 22:23:33 igor@stripped +4 -4
    Corrected results after the fix for bug #12791.

  mysql-test/r/func_test.result
    1.29 05/08/26 22:23:08 igor@stripped +12 -1
    Added test cases for bug #12791.

  mysql-test/t/func_test.test
    1.25 05/08/26 22:22:30 igor@stripped +9 -0
    Added test cases for bug #12791.

  sql/item_func.h
    1.128 05/08/26 21:34:19 igor@stripped +0 -1
    Fixed bug #12791.
    Made LEAST/GREATES fully Oracle compliant.
    LEAST/GREATEST did not return NULL if only some
    arguments were NULLs. This did not comply with Oracle.

  sql/item_func.cc
    1.245 05/08/26 21:29:32 igor@stripped +23 -25
    Fixed bug #12791.
    Made LEAST/GREATES fully Oracle compliant.
    LEAST/GREATEST did not return NULL if only some
    arguments were NULLs. This did not comply with Oracle.

# 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:	igor
# Host:	rurik.mysql.com
# Root:	/home/igor/dev/mysql-5.0-0

--- 1.244/sql/item_func.cc	Thu Aug 25 09:36:47 2005
+++ 1.245/sql/item_func.cc	Fri Aug 26 21:29:32 2005
@@ -1950,7 +1950,7 @@
   int max_int_part=0;
   decimals=0;
   max_length=0;
-  maybe_null=1;
+  maybe_null=0;
   cmp_type=args[0]->result_type();
 
   for (uint i=0 ; i < arg_count ; i++)
@@ -1958,8 +1958,8 @@
     set_if_bigger(max_length, args[i]->max_length);
     set_if_bigger(decimals, args[i]->decimals);
     set_if_bigger(max_int_part, args[i]->decimal_int_part());
-    if (!args[i]->maybe_null)
-      maybe_null=0;
+    if (args[i]->maybe_null)
+      maybe_null=1;
     cmp_type=item_cmp_type(cmp_type,args[i]->result_type());
   }
   if (cmp_type == STRING_RESULT)
@@ -2005,14 +2005,11 @@
   {
     String *res;
     LINT_INIT(res);
-    null_value=1;
+    null_value= 0;
     for (uint i=0; i < arg_count ; i++)
     {
-      if (null_value)
-      {
+      if (i == 0)
 	res=args[i]->val_str(str);
-	null_value=args[i]->null_value;
-      }
       else
       {
 	String *res2;
@@ -2023,7 +2020,11 @@
 	  if ((cmp_sign < 0 ? cmp : -cmp) < 0)
 	    res=res2;
 	}
+        else
+          res= 0;
       }
+      if ((null_value= args[i]->null_value))
+        break;
     }
     if (res)					// If !NULL
       res->set_charset(collation.collation);
@@ -2043,20 +2044,19 @@
 {
   DBUG_ASSERT(fixed == 1);
   double value=0.0;
-  null_value=1;
+  null_value= 0;
   for (uint i=0; i < arg_count ; i++)
   {
-    if (null_value)
-    {
+    if (i == 0)
       value= args[i]->val_real();
-      null_value=args[i]->null_value;
-    }
     else
     {
       double tmp= args[i]->val_real();
       if (!args[i]->null_value && (tmp < value ? cmp_sign : -cmp_sign) >
0)
 	value=tmp;
     }
+    if ((null_value= args[i]->null_value))
+      break;
   }
   return value;
 }
@@ -2066,20 +2066,19 @@
 {
   DBUG_ASSERT(fixed == 1);
   longlong value=0;
-  null_value=1;
+  null_value= 0;
   for (uint i=0; i < arg_count ; i++)
   {
-    if (null_value)
-    {
+    if (i == 0)
       value=args[i]->val_int();
-      null_value=args[i]->null_value;
-    }
     else
     {
       longlong tmp=args[i]->val_int();
       if (!args[i]->null_value && (tmp < value ? cmp_sign : -cmp_sign) >
0)
 	value=tmp;
     }
+    if ((null_value= args[i]->null_value))
+      break;
   }
   return value;
 }
@@ -2089,20 +2088,17 @@
 {
   DBUG_ASSERT(fixed == 1);
   my_decimal tmp_buf, *tmp, *res= NULL;
-  null_value=1;
+  null_value= 0;
   for (uint i=0; i < arg_count ; i++)
   {
-    if (null_value)
-    {
+    if (i == 0)
       res= args[i]->val_decimal(dec);
-      null_value= args[i]->null_value;
-    }
     else
     {
       tmp= args[i]->val_decimal(&tmp_buf);
       if (args[i]->null_value)
-        continue;
-      if ((my_decimal_cmp(tmp, res) * cmp_sign) < 0)
+        res= 0;
+      else if ((my_decimal_cmp(tmp, res) * cmp_sign) < 0)
       {
         if (tmp == &tmp_buf)
         {
@@ -2113,6 +2109,8 @@
           res= tmp;
       }
     }
+    if ((null_value= args[i]->null_value))
+      break;
   }
   return res;
 }

--- 1.127/sql/item_func.h	Fri Aug 19 15:51:36 2005
+++ 1.128/sql/item_func.h	Fri Aug 26 21:34:19 2005
@@ -682,7 +682,6 @@
   my_decimal *val_decimal(my_decimal *);
   void fix_length_and_dec();
   enum Item_result result_type () const { return cmp_type; }
-  table_map not_null_tables() const { return 0; }
 };
 
 class Item_func_min :public Item_func_min_max

--- 1.28/mysql-test/r/func_test.result	Thu Feb  3 15:06:29 2005
+++ 1.29/mysql-test/r/func_test.result	Fri Aug 26 22:23:08 2005
@@ -166,7 +166,6 @@
 INSERT INTO t2 VALUES (1,'Everyone',2),(2,'Help',3),(3,'Customer Support',1);
 SELECT f_acc.rank, a1.rank, a2.rank  FROM t1 LEFT JOIN t1 f1 ON  (f1.access_id=1 AND
f1.faq_group_id = t1.faq_group_id) LEFT JOIN t2 a1 ON (a1.access_id =  f1.access_id) LEFT
JOIN t1 f2 ON (f2.access_id=3 AND  f2.faq_group_id = t1.faq_group_id) LEFT  JOIN t2 a2 ON
(a2.access_id = f2.access_id), t2 f_acc WHERE LEAST(a1.rank,a2.rank) =  f_acc.rank;
 rank	rank	rank
-2	2	NULL
 DROP TABLE t1,t2;
 CREATE TABLE t1 (d varchar(6), k int);
 INSERT INTO t1 VALUES (NULL, 2);
@@ -193,3 +192,15 @@
 a	b
 4	5
 drop table t1;
+SELECT GREATEST(1,NULL) FROM DUAL;
+GREATEST(1,NULL)
+NULL
+SELECT LEAST('xxx','aaa',NULL,'yyy') FROM DUAL;
+LEAST('xxx','aaa',NULL,'yyy')
+NULL
+SELECT LEAST(1.1,1.2,NULL,1.0) FROM DUAL;
+LEAST(1.1,1.2,NULL,1.0)
+NULL
+SELECT GREATEST(1.5E+2,1.3E+2,NULL) FROM DUAL;
+GREATEST(1.5E+2,1.3E+2,NULL)
+NULL

--- 1.32/mysql-test/r/null.result	Fri May 13 02:08:03 2005
+++ 1.33/mysql-test/r/null.result	Fri Aug 26 22:23:33 2005
@@ -236,10 +236,10 @@
   `c06` varchar(6) character set latin2 default NULL,
   `c07` varchar(6) character set latin2 default NULL,
   `c08` varchar(6) character set latin2 default NULL,
-  `c09` varchar(6) character set latin2 NOT NULL default '',
-  `c10` varchar(6) character set latin2 NOT NULL default '',
-  `c11` varchar(6) character set latin2 NOT NULL default '',
-  `c12` varchar(6) character set latin2 NOT NULL default '',
+  `c09` varchar(6) character set latin2 default NULL,
+  `c10` varchar(6) character set latin2 default NULL,
+  `c11` varchar(6) character set latin2 default NULL,
+  `c12` varchar(6) character set latin2 default NULL,
   `c13` varchar(6) character set latin2 default NULL,
   `c14` char(0) character set latin2 default NULL,
   `c15` char(0) character set latin2 default NULL,

--- 1.24/mysql-test/t/func_test.test	Thu Jul 28 07:09:48 2005
+++ 1.25/mysql-test/t/func_test.test	Fri Aug 26 22:22:30 2005
@@ -118,3 +118,12 @@
 select * from t1 where a not between 1 and 2;
 select * from t1 where a not between 1 and 2 and b not between 3 and 4;
 drop table t1;
+
+#
+# Test for bug #12791: one of the arguments of LEAST/GREATEST is NULL
+#
+
+SELECT GREATEST(1,NULL) FROM DUAL;
+SELECT LEAST('xxx','aaa',NULL,'yyy') FROM DUAL;
+SELECT LEAST(1.1,1.2,NULL,1.0) FROM DUAL;
+SELECT GREATEST(1.5E+2,1.3E+2,NULL) FROM DUAL;

--- 1.105/mysql-test/r/func_str.result	Thu Aug 11 04:02:40 2005
+++ 1.106/mysql-test/r/func_str.result	Fri Aug 26 22:25:00 2005
@@ -287,7 +287,7 @@
 padpadpadpadpaSTRING
 select
LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD');
 LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD')	GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD')
-HAROLD	HARRY
+NULL	NULL
 select least(1,2,3) | greatest(16,32,8),
least(5,4)*1,greatest(-1.0,1.0)*1,least(3,2,1)*1.0,greatest(1,1.1,1.0),least("10",9),greatest("A","B","0");
 least(1,2,3) |
greatest(16,32,8)	least(5,4)*1	greatest(-1.0,1.0)*1	least(3,2,1)*1.0	greatest(1,1.1,1.0)	least("10",9)	greatest("A","B","0")
 33	4	1.0	1.0	1.1	9	B