List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:October 1 2009 10:19am
Subject:bzr commit into mysql-5.1-bugteam branch (jorgen.loland:3146) Bug#47280
View as plain text  
#At file:///localhome/jl208045/mysql/mysql-5.1-bugteam-46518/ based on revid:davi.arnaut@stripped

 3146 Jorgen Loland	2009-10-01
      Bug#47280 - strange results from count(*) with order by multiple 
                  columns without where/group
      
      Simple SELECT with implicit grouping returns many rows if the query
      orders by the aggregated column in the SELECT list. Queries with
      implicit grouping should only return a single record.
            
      Function opt_sum_query is called if a query performs grouping but has
      no fields in the group list. The function tries to substitute count(),
      min() and max() aggregate functions with constants. In the queries
      that failed, opt_sum_query found a non-aggregate field and gave up the
      optimization.
            
      However, non-aggregate fields can be substituted with a const in this
      case: When the select list contains non-aggregate fields not in the
      group list, the server is free to return any value from the group, so
      the value in such fields is indeterminate unless all values are the
      same.
     @ mysql-test/r/func_group.result
        Test for BUG#47280. Also modifies result from existing test.
     @ mysql-test/t/func_group.test
        Test for BUG#47280
     @ sql/opt_sum.cc
        Function opt_sum_func gave up const optimization of min/max/count if a non-aggregate field was encountered. This was incorrect since this optimization is only done if the group list is empty (hence, only zero or one row is returned), and the server is free to choose any of the field values in the group.
     @ sql/sql_select.cc
        Improved comment for why min/max/count can be optimized away

    modified:
      mysql-test/r/func_group.result
      mysql-test/t/func_group.test
      sql/opt_sum.cc
      sql/sql_select.cc
=== modified file 'mysql-test/r/func_group.result'
--- a/mysql-test/r/func_group.result	2009-04-01 11:10:03 +0000
+++ b/mysql-test/r/func_group.result	2009-10-01 10:18:58 +0000
@@ -1393,10 +1393,10 @@ ERROR 42000: Mixing of GROUP columns (MI
 SET SQL_MODE=DEFAULT;
 SELECT a FROM t1 HAVING COUNT(*)>2;
 a
-1
+4
 SELECT COUNT(*), a FROM t1;
 COUNT(*)	a
-4	1
+4	4
 DROP TABLE t1;
 set SQL_MODE=ONLY_FULL_GROUP_BY;
 CREATE TABLE t1 (a INT);
@@ -1477,3 +1477,57 @@ COUNT(*)
 SET SQL_MODE=default;
 DROP TABLE t1;
 End of 5.0 tests
+#
+# BUG#47280 - strange results from count(*) with order by multiple 
+#             columns without where/group
+# 
+# 
+# Initialize test
+# 
+CREATE TABLE t1 (
+pk INT NOT NULL,
+i INT,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,11),(2,12),(3,13);
+#
+# Start test
+# All the following queries shall return 1 record
+#
+
+# Query that can optimize all aggregate columns away after bugfix.
+# Masking all correct values {11...13} for column i in this result. 
+SELECT MAX(pk) as max, i
+FROM t1
+ORDER BY max;
+max	i
+3	#
+
+# Select tables should be optimized away here:
+EXPLAIN
+SELECT MAX(pk) as max, i
+FROM t1
+ORDER BY max;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+
+# Query with same plan as originally failing query; does not fail
+# Masking all correct values {11...13} for column i in this result. 
+SELECT COUNT(i) as cnt, i
+FROM t1
+ORDER BY cnt;
+cnt	i
+3	#
+
+# Select tables should NOT be optimized away here:
+EXPLAIN
+SELECT COUNT(i) as cnt, i
+FROM t1
+ORDER BY cnt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary
+#
+# Cleanup
+#
+DROP TABLE t1;
+End of 5.1 tests

=== modified file 'mysql-test/t/func_group.test'
--- a/mysql-test/t/func_group.test	2009-04-01 11:10:03 +0000
+++ b/mysql-test/t/func_group.test	2009-10-01 10:18:58 +0000
@@ -1006,3 +1006,61 @@ DROP TABLE t1;
 
 ###
 --echo End of 5.0 tests
+
+--echo #
+--echo # BUG#47280 - strange results from count(*) with order by multiple 
+--echo #             columns without where/group
+--echo # 
+
+--echo # 
+--echo # Initialize test
+--echo # 
+
+CREATE TABLE t1 (
+  pk INT NOT NULL,
+  i INT,
+  PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,11),(2,12),(3,13);
+
+--echo #
+--echo # Start test
+--echo # All the following queries shall return 1 record
+--echo #
+
+--echo
+--echo # Query that can optimize all aggregate columns away after bugfix.
+--echo # Masking all correct values {11...13} for column i in this result. 
+--replace_regex /1[1-3]/#/
+SELECT MAX(pk) as max, i
+FROM t1
+ORDER BY max;
+
+--echo
+--echo # Select tables should be optimized away here:
+EXPLAIN
+SELECT MAX(pk) as max, i
+FROM t1
+ORDER BY max;
+
+--echo
+--echo # Query with same plan as originally failing query; does not fail
+--echo # Masking all correct values {11...13} for column i in this result. 
+--replace_regex /1[1-3]/#/
+SELECT COUNT(i) as cnt, i
+FROM t1
+ORDER BY cnt;
+
+--echo
+--echo # Select tables should NOT be optimized away here:
+EXPLAIN
+SELECT COUNT(i) as cnt, i
+FROM t1
+ORDER BY cnt;
+
+--echo #
+--echo # Cleanup
+--echo #
+DROP TABLE t1;
+
+--echo End of 5.1 tests

=== modified file 'sql/opt_sum.cc'
--- a/sql/opt_sum.cc	2009-06-15 15:57:06 +0000
+++ b/sql/opt_sum.cc	2009-10-01 10:18:58 +0000
@@ -97,7 +97,8 @@ static ulonglong get_exact_record_count(
 
   @note
     This function is only called for queries with sum functions and no
-    GROUP BY part.
+    GROUP BY part. This means that the result set shall contain a single
+    record only
 
   @retval
     0                    no errors
@@ -113,7 +114,6 @@ int opt_sum_query(TABLE_LIST *tables, Li
 {
   List_iterator_fast<Item> it(all_fields);
   int const_result= 1;
-  bool recalc_const_item= 0;
   ulonglong count= 1;
   bool is_exact_count= TRUE, maybe_exact_count= TRUE;
   table_map removed_tables= 0, outer_tables= 0, used_tables= 0;
@@ -189,6 +189,13 @@ int opt_sum_query(TABLE_LIST *tables, Li
 
   while ((item= it++))
   {
+    /*
+      Only non-optimizable aggregate functions can make this
+      optimization bail out: since opt_sum_query() is only called if
+      there is no group_list, the result set shall contain no more
+      than one row. Thus, for all non-aggregate fields, any value in
+      the group is correct.
+    */
     if (item->type() == Item::SUM_FUNC_ITEM)
     {
       Item_sum *item_sum= (((Item_sum*) item));
@@ -213,7 +220,6 @@ int opt_sum_query(TABLE_LIST *tables, Li
             is_exact_count= 1;                  // count is now exact
           }
           ((Item_sum_count*) item)->make_const((longlong) count);
-          recalc_const_item= 1;
         }
         else
           const_result= 0;
@@ -363,7 +369,6 @@ int opt_sum_query(TABLE_LIST *tables, Li
         else
           ((Item_sum_min*) item_sum)->reset(); /* Set to the constant value. */
         ((Item_sum_min*) item_sum)->make_const();
-        recalc_const_item= 1;
         break;
       }
       case Item_sum::MAX_FUNC:
@@ -451,7 +456,6 @@ int opt_sum_query(TABLE_LIST *tables, Li
         else
           ((Item_sum_max*) item_sum)->reset(); /* Set to the constant value. */
         ((Item_sum_max*) item_sum)->make_const();
-        recalc_const_item= 1;
         break;
       }
       default:
@@ -459,13 +463,6 @@ int opt_sum_query(TABLE_LIST *tables, Li
         break;
       }
     }
-    else if (const_result)
-    {
-      if (recalc_const_item)
-        item->update_used_tables();
-      if (!item->const_item())
-        const_result= 0;
-    }
   }
   /*
     If we have a where clause, we can only ignore searching in the

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-09-28 13:48:40 +0000
+++ b/sql/sql_select.cc	2009-10-01 10:18:58 +0000
@@ -881,7 +881,12 @@ JOIN::optimize()
   }
 #endif
 
-  /* Optimize count(*), min() and max() */
+  /* 
+     Try to optimize count(*), min() and max() to const fields if
+     there is no group_list, i.e., the result set shall only contain
+     one row. This may not be possible if one of the aggregate
+     functions needs to table scan (e.g, sum(*))
+  */
   if (tables_list && tmp_table_param.sum_func_count && ! group_list)
   {
     int res;


Attachment: [text/bzr-bundle] bzr/jorgen.loland@sun.com-20091001101858-mefc186y35ys3qln.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (jorgen.loland:3146) Bug#47280Jorgen Loland1 Oct
  • Re: bzr commit into mysql-5.1-bugteam branch (jorgen.loland:3146)Bug#47280Jørgen Løland1 Oct
  • Re: bzr commit into mysql-5.1-bugteam branch (jorgen.loland:3146)Bug#47280Martin Hansson1 Oct