List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:September 25 2009 12:48pm
Subject:bzr commit into mysql-5.1-bugteam branch (jorgen.loland:3133) Bug#46518
Bug#47280
View as plain text  
#At file:///localhome/jl208045/mysql/mysql-5.1-bugteam-46518/ based on revid:joro@stripped

 3133 Jorgen Loland	2009-09-25
      Bug#47280 - strange results from count(*) with order by multiple 
                  columns without where/group
      Bug#46518 - Removing ORDER BY from aggregate query changes result 
                  set (produces extra rows)
      
      Simple SELECT with implicit grouping returns many rows if the 
      ORDER BY clause contains the aggregate function from 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
     @ 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 optimize 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-09-25 12:48:08 +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,89 @@ 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, aka
+# 
+# 
+# Initialize test
+# 
+CREATE TABLE t1 (
+pk int(11) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES
+(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+#
+# Start test
+# All the following queries shall return 1 record
+#
+
+SELECT MAX(pk) 
+FROM t1;
+MAX(pk)
+10
+
+SELECT MAX(pk) 
+FROM t1
+ORDER BY MAX(pk), pk;
+MAX(pk)
+10
+
+EXPLAIN
+SELECT MAX(pk) 
+FROM t1
+ORDER BY MAX(pk), pk;
+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
+
+# The return value for pk may be any of the inserted values {1...10}
+SELECT MAX(pk), pk 
+FROM t1
+ORDER BY MAX(pk);
+MAX(pk)	pk
+10	10
+
+SELECT MIN(pk) 
+FROM t1
+ORDER BY MIN(pk), pk;
+MIN(pk)
+1
+
+# The return value for pk may be any of the inserted values {1...10}
+SELECT MIN(pk), pk 
+FROM t1
+ORDER BY MIN(pk);
+MIN(pk)	pk
+1	1
+
+SELECT COUNT(pk) 
+FROM t1
+ORDER BY COUNT(pk), pk;
+COUNT(pk)
+10
+
+SELECT COUNT(*) as b
+FROM t1
+ORDER BY b, pk;
+b
+10
+
+# The return value for pk may be any of the inserted values {1...10}
+SELECT COUNT(pk), pk 
+FROM t1
+ORDER BY COUNT(pk);
+COUNT(pk)	pk
+10	1
+
+# The return value for pk may be any of the inserted values {1...10}
+SELECT MAX(pk), SUM(pk), COUNT(pk), AVG(pk), pk
+FROM t1
+ORDER BY MAX(pk), pk;
+MAX(pk)	SUM(pk)	COUNT(pk)	AVG(pk)	pk
+10	55	10	5.5000	1
+#
+# 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-09-25 12:48:08 +0000
@@ -1006,3 +1006,85 @@ 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, aka
+--echo # 
+
+--echo # 
+--echo # Initialize test
+--echo # 
+
+CREATE TABLE t1 (
+  pk int(11) NOT NULL,
+  PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES
+(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+
+--echo #
+--echo # Start test
+--echo # All the following queries shall return 1 record
+--echo #
+
+--echo
+SELECT MAX(pk) 
+FROM t1;
+
+--echo
+SELECT MAX(pk) 
+FROM t1
+ORDER BY MAX(pk), pk;
+
+--echo
+EXPLAIN
+SELECT MAX(pk) 
+FROM t1
+ORDER BY MAX(pk), pk;
+
+--echo
+--echo # The return value for pk may be any of the inserted values {1...10}
+SELECT MAX(pk), pk 
+FROM t1
+ORDER BY MAX(pk);
+
+--echo
+SELECT MIN(pk) 
+FROM t1
+ORDER BY MIN(pk), pk;
+
+--echo
+--echo # The return value for pk may be any of the inserted values {1...10}
+SELECT MIN(pk), pk 
+FROM t1
+ORDER BY MIN(pk);
+
+--echo
+SELECT COUNT(pk) 
+FROM t1
+ORDER BY COUNT(pk), pk;
+
+--echo
+SELECT COUNT(*) as b
+FROM t1
+ORDER BY b, pk;
+
+--echo
+--echo # The return value for pk may be any of the inserted values {1...10}
+SELECT COUNT(pk), pk 
+FROM t1
+ORDER BY COUNT(pk);
+
+--echo
+--echo # The return value for pk may be any of the inserted values {1...10}
+SELECT MAX(pk), SUM(pk), COUNT(pk), AVG(pk), pk
+FROM t1
+ORDER BY MAX(pk), pk;
+
+--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-09-25 12:48:08 +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
@@ -461,10 +462,12 @@ int opt_sum_query(TABLE_LIST *tables, Li
     }
     else if (const_result)
     {
+      /*
+	No need to set const_result=0 because the server may choose
+	any value in the group for non-aggregate fields.
+      */
       if (recalc_const_item)
         item->update_used_tables();
-      if (!item->const_item())
-        const_result= 0;
     }
   }
   /*

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-09-23 08:40:33 +0000
+++ b/sql/sql_select.cc	2009-09-25 12:48:08 +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-20090925124808-wzs9jghjg6ttpfdx.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (jorgen.loland:3133) Bug#46518Bug#47280Jorgen Loland25 Sep
  • Re: bzr commit into mysql-5.1-bugteam branch (jorgen.loland:3133)Bug#46518 Bug#47280Alexey Kopytov29 Sep
    • Re: bzr commit into mysql-5.1-bugteam branch (jorgen.loland:3133)Bug#46518 Bug#47280Alexey Kopytov29 Sep
  • Re: bzr commit into mysql-5.1-bugteam branch (jorgen.loland:3133)Bug#46518 Bug#47280Martin Hansson29 Sep