bk commit into 4.1 tree (timour:1.2421) BUG#12882

[timour]

Below is the list of changes that have just been committed into a local
4.1 repository of timka. When timka 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.2421 05/09/14 17:06:31 timour@stripped +4 -0
  Fix for BUG#12882.

  sql/opt_sum.cc
    1.43 05/09/14 17:06:28 timour@stripped +18 -2
    Do not apply MIN/MAX optimization when the operand of MIN/MAX is
    a constant because at the point of this optimization it is not
    known if the query has any result rows. If the query has result
    rows, then the result of MIN/MAX is the constant argument, but
    if the query result is empty, then the result of MIN/MAX must
    be NULL.

  sql/item_sum.cc
    1.146 05/09/14 17:06:28 timour@stripped +13 -13
    If it is known that a query has no result rows, do not unset null_value
    for Item_sum_[min | max]. As a result the MIN and MAX functions produce
    NULL when there are no result rows.

  mysql-test/t/func_group.test
    1.31 05/09/14 17:06:28 timour@stripped +25 -0
    Test for BUG#12882.

  mysql-test/r/func_group.result
    1.39 05/09/14 17:06:28 timour@stripped +44 -0
    Test for BUG#12882.

# 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:	timour
# Host:	lamia.home
# Root:	/home/timka/mysql/src/4.1-dbg

--- 1.145/sql/item_sum.cc	2005-09-08 02:33:06 +03:00
+++ 1.146/sql/item_sum.cc	2005-09-14 17:06:28 +03:00
@@ -552,8 +552,8 @@
 
 void Item_sum_hybrid::no_rows_in_result()
 {
-  Item_sum::no_rows_in_result();
   was_values= FALSE;
+  Item_sum::no_rows_in_result();
 }
 
 
@@ -569,7 +569,7 @@
   case STRING_RESULT:
   {
     String *result=args[0]->val_str(&tmp_value);
-    if (!args[0]->null_value &&
+    if (!args[0]->null_value && was_values &&
 	(null_value || sortcmp(&value,result,collation.collation) > 0))
     {
       value.copy(*result);
@@ -580,10 +580,10 @@
   case INT_RESULT:
   {
     longlong nr=args[0]->val_int();
-    if (!args[0]->null_value && (null_value ||
-				 (unsigned_flag && 
-				  (ulonglong) nr < (ulonglong) sum_int) ||
-				 (!unsigned_flag && nr < sum_int)))
+    if (!args[0]->null_value && was_values &&
+        (null_value ||
+         (unsigned_flag &&  (ulonglong) nr < (ulonglong) sum_int) ||
+         (!unsigned_flag && nr < sum_int)))
     {
       sum_int=nr;
       null_value=0;
@@ -593,7 +593,7 @@
   case REAL_RESULT:
   {
     double nr=args[0]->val();
-    if (!args[0]->null_value && (null_value || nr < sum))
+    if (!args[0]->null_value && was_values &&(null_value || nr <
sum))
     {
       sum=nr;
       null_value=0;
@@ -622,7 +622,7 @@
   case STRING_RESULT:
   {
     String *result=args[0]->val_str(&tmp_value);
-    if (!args[0]->null_value &&
+    if (!args[0]->null_value && was_values &&
 	(null_value || sortcmp(&value,result,collation.collation) < 0))
     {
       value.copy(*result);
@@ -633,10 +633,10 @@
   case INT_RESULT:
   {
     longlong nr=args[0]->val_int();
-    if (!args[0]->null_value && (null_value ||
-				 (unsigned_flag && 
-				  (ulonglong) nr > (ulonglong) sum_int) ||
-				 (!unsigned_flag && nr > sum_int)))
+    if (!args[0]->null_value && was_values &&
+        (null_value ||
+         (unsigned_flag && (ulonglong) nr > (ulonglong) sum_int) ||
+         (!unsigned_flag && nr > sum_int)))
     {
       sum_int=nr;
       null_value=0;
@@ -646,7 +646,7 @@
   case REAL_RESULT:
   {
     double nr=args[0]->val();
-    if (!args[0]->null_value && (null_value || nr > sum))
+    if (!args[0]->null_value && was_values && (null_value || nr >
sum))
     {
       sum=nr;
       null_value=0;

--- 1.42/sql/opt_sum.cc	2005-03-05 06:13:42 +02:00
+++ 1.43/sql/opt_sum.cc	2005-09-14 17:06:28 +03:00
@@ -210,8 +210,16 @@
 	  }
           removed_tables|= table->map;
         }
-        else if (!expr->const_item())		// This is VERY seldom false
+        else
         {
+          /*
+            If this is a non-constand item, then the optimization is not
+            applicable. If this is a costant item we can't apply the
+            optimization as well because we can't know at this point if
+            whether the query returns any rows, and if it returns none
+            we the result of MIN/MAX is NULL, otherwise it is the
+            constant argument of MIN/MAX.
+          */
           const_result= 0;
           break;
         }
@@ -282,8 +290,16 @@
 	  }
           removed_tables|= table->map;
         }
-        else if (!expr->const_item())		// This is VERY seldom false
+        else
         {
+          /*
+            If this is a non-constand item, then the optimization is not
+            applicable. If this is a costant item we can't apply the
+            optimization as well because we can't know at this point if
+            whether the query returns any rows, and if it returns none
+            we the result of MIN/MAX is NULL, otherwise it is the
+            constant argument of MIN/MAX.
+          */
           const_result= 0;
           break;
         }

--- 1.38/mysql-test/r/func_group.result	2005-03-23 08:36:41 +02:00
+++ 1.39/mysql-test/r/func_group.result	2005-09-14 17:06:28 +03:00
@@ -780,3 +780,47 @@
 MAX(id)
 NULL
 DROP TABLE t1;
+create table t1 (a int);
+select min(7) from t1;
+min(7)
+NULL
+select min(7) from DUAL;
+min(7)
+NULL
+select min(a) from t1;
+min(a)
+NULL
+select max(7) from t1;
+max(7)
+NULL
+select max(7) from DUAL;
+max(7)
+NULL
+select max(a) from t1;
+max(a)
+NULL
+select 1, min(1) from t1 where a=99;
+1	min(1)
+1	NULL
+select 1, min(1) from t1 where 1=99;
+1	min(1)
+1	NULL
+select 1, min(a) from t1 where a=99;
+1	min(a)
+1	NULL
+select 1, min(a) from t1 where 1=99;
+1	min(a)
+1	NULL
+select 1, max(1) from t1 where a=99;
+1	max(1)
+1	NULL
+select 1, max(1) from t1 where 1=99;
+1	max(1)
+1	NULL
+select 1, max(a) from t1 where a=99;
+1	max(a)
+1	NULL
+select 1, max(a) from t1 where 1=99;
+1	max(a)
+1	NULL
+drop table t1;

--- 1.30/mysql-test/t/func_group.test	2005-07-28 03:21:42 +03:00
+++ 1.31/mysql-test/t/func_group.test	2005-09-14 17:06:28 +03:00
@@ -527,4 +527,29 @@
 SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
 DROP TABLE t1;
 
+#
+# Bug #12882  	min/max inconsistent on empty table
+#
+
+create table t1 (a int);
+select min(7) from t1;
+select min(7) from DUAL;
+select min(a) from t1;
+
+select max(7) from t1;
+select max(7) from DUAL;
+select max(a) from t1;
+
+select 1, min(1) from t1 where a=99;
+select 1, min(1) from t1 where 1=99;
+select 1, min(a) from t1 where a=99;
+select 1, min(a) from t1 where 1=99;
+
+select 1, max(1) from t1 where a=99;
+select 1, max(1) from t1 where 1=99;
+select 1, max(a) from t1 where a=99;
+select 1, max(a) from t1 where 1=99;
+
+drop table t1;
+
 # End of 4.1 tests