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/15 16:48:28 timour@stripped +4 -0
  Fix for BUG#12882

  sql/opt_sum.cc
    1.43 05/09/15 16:48:25 timour@stripped +20 -2
    Do not apply MIN/MAX optimization when the operand of MIN/MAX is
    a constant because at this optimization stage it is not known
    if the query has any result rows. If the query has result
    rows, then the result of MIN/MAX is its constant argument, but
    if the query result is empty, then the result of MIN/MAX must
    be NULL irrespective of its argument.

  sql/item_sum.cc
    1.146 05/09/15 16:48:25 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/15 16:48:25 timour@stripped +25 -0
    Test for BUG#12882.

  mysql-test/r/func_group.result
    1.39 05/09/15 16:48:25 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-15 16:48:25 +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-15 16:48:25 +03:00
@@ -210,8 +210,17 @@
 	  }
           removed_tables|= table->map;
         }
-        else if (!expr->const_item())		// This is VERY seldom false
+        else
         {
+          /*
+            The optimization is not applicable in both cases:
+            (a) 'expr' is a non-constant expression. Then we can't
+            replace 'expr' by a constant.
+            (b) 'expr' is a costant. According to ANSI, MIN/MAX must
+            return NULL if the query does not return any rows. However,
+            at this stage we are not able to determine whether the
+            query returns any rows, so we can't apply the optimization.
+          */
           const_result= 0;
           break;
         }
@@ -282,8 +291,17 @@
 	  }
           removed_tables|= table->map;
         }
-        else if (!expr->const_item())		// This is VERY seldom false
+        else
         {
+          /*
+            The optimization is not applicable in both cases:
+            (a) 'expr' is a non-constant expression. Then we can't
+            replace 'expr' by a constant.
+            (b) 'expr' is a costant. According to ANSI, MIN/MAX must
+            return NULL if the query does not return any rows. However,
+            at this stage we are not able to determine whether the
+            query returns any rows, so we can't apply the optimization.
+          */
           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-15 16:48:25 +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-15 16:48:25 +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