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.


sql/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.
sql/item_func.h:
  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/t/func_test.test:
  Added test cases for bug #12791.
mysql-test/r/func_test.result:
  Added test cases for bug #12791.
mysql-test/r/null.result:
  Corrected results after the fix for bug #12791.
mysql-test/r/func_str.result:
  Corrected results after the fix for bug #12791.

6 files changed, 49 insertions, 32 deletions

diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index c7e69ae1d31..577f943ebde 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -287,7 +287,7 @@ lpad('STRING', 20, CONCAT('p','a','d') )
 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
diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result
index 32883921e70..d9437f6c515 100644
--- a/mysql-test/r/func_test.result
+++ b/mysql-test/r/func_test.result
@@ -166,7 +166,6 @@ CREATE TABLE t2 (  access_id smallint(6) NOT NULL default '0',   name varchar(20
 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 @@ select * from t1 where a not between 1 and 2 and b not between 3 and 4;
 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
diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index 4551fed2732..1425b2a7f89 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -236,10 +236,10 @@ t1	CREATE TABLE `t1` (
   `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,
diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test
index 424c0d1456b..f2ff47704c9 100644
--- a/mysql-test/t/func_test.test
+++ b/mysql-test/t/func_test.test
@@ -118,3 +118,12 @@ insert into t1 values (1,2), (2,3), (3,4), (4,5);
 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;
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 13a82fa1361..80808c0ac87 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -1950,7 +1950,7 @@ void Item_func_min_max::fix_length_and_dec()
   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 @@ void Item_func_min_max::fix_length_and_dec()
     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 *Item_func_min_max::val_str(String *str)
   {
     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 @@ String *Item_func_min_max::val_str(String *str)
 	  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 @@ double Item_func_min_max::val_real()
 {
   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 @@ longlong Item_func_min_max::val_int()
 {
   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 @@ my_decimal *Item_func_min_max::val_decimal(my_decimal *dec)
 {
   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 @@ my_decimal *Item_func_min_max::val_decimal(my_decimal *dec)
           res= tmp;
       }
     }
+    if ((null_value= args[i]->null_value))
+      break;
   }
   return res;
 }
diff --git a/sql/item_func.h b/sql/item_func.h
index 384cb486f7c..019abb0c072 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -682,7 +682,6 @@ public:
   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