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

 3155 Jorgen Loland	2009-10-05
      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.
                  
      The problem is that the "if" in JOIN:exec() that decides if execution 
      needs to handle grouping assumes that tmp_table_param.sum_func_count==0
      means that there are no aggregate functions in the query. This is 
      incorrect if sum_func_count was recounted after optimizing the aggregate 
      functions away. Consider two queries:
      
      1) SELECT MAX(pk) max, pk FROM D ORDER BY max;
      2) SELECT MAX(pk) max, pk FROM D;
      
      max(pk) is optimized away by opt_sum_query() in JOIN::optimize for both
      queries, but the sum_func_count is only recalculated for the 
      former query in the "if(need_tmp)" block in JOIN:exec().
      
      Hence, when evaluating whether grouping is needed in JOIN::exec(),
      sum_func_count is 0 for the first query and 1 for the second 
      query. 
      
      The fix for this bug is to remove the assumption that sum_func_count==0
      means that there is no need for grouping. This is done by introducing 
      variable "bool implicit_grouping" in the JOIN object. 
     @ mysql-test/r/func_group.result
        Add test for BUG#47280
     @ mysql-test/t/func_group.test
        Add test for BUG#47280
     @ sql/sql_select.cc
        Introduce and use variable implicit_grouping instead of (!group_list && sum_func_count) in places that need to test if grouping is required.
     @ sql/sql_select.h
        Add variable implicit_grouping, which will be TRUE for queries that contain aggregate functions but no GROUP BY clause.

    modified:
      mysql-test/r/func_group.result
      mysql-test/t/func_group.test
      sql/sql_select.cc
      sql/sql_select.h
=== 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-05 12:35:54 +0000
@@ -1477,3 +1477,47 @@ 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
+#
+
+# 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	#
+
+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	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary
+
+# Only 11 is correct for collumn i in this result
+SELECT MAX(pk) as max, i
+FROM t1
+WHERE pk<2
+ORDER BY max;
+max	i
+1	11
+#
+# 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-05 12:35:54 +0000
@@ -1006,3 +1006,51 @@ 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 # 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
+EXPLAIN
+SELECT MAX(pk) as max, i
+FROM t1
+ORDER BY max;
+
+--echo
+--echo # Only 11 is correct for collumn i in this result
+SELECT MAX(pk) as max, i
+FROM t1
+WHERE pk<2
+ORDER BY max;
+
+--echo #
+--echo # Cleanup
+--echo #
+DROP TABLE t1;
+
+--echo End of 5.1 tests

=== 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-05 12:35:54 +0000
@@ -644,8 +644,11 @@ JOIN::prepare(Item ***rref_pointer_array
   this->group= group_list != 0;
   unit= unit_arg;
 
+  if (tmp_table_param.sum_func_count && !group_list)
+    implicit_grouping= TRUE;
+
 #ifdef RESTRICTED_GROUP
-  if (sum_func_count && !group_list && (func_count || field_count))
+  if (implicit_grouping)
   {
     my_message(ER_WRONG_SUM_SELECT,ER(ER_WRONG_SUM_SELECT),MYF(0));
     goto err;
@@ -882,7 +885,7 @@ JOIN::optimize()
 #endif
 
   /* Optimize count(*), min() and max() */
-  if (tables_list && tmp_table_param.sum_func_count && ! group_list)
+  if (tables_list && implicit_grouping)
   {
     int res;
     /*
@@ -2015,7 +2018,7 @@ JOIN::exec()
     count_field_types(select_lex, &curr_join->tmp_table_param, 
                       *curr_all_fields, 0);
   
-  if (curr_join->group || curr_join->tmp_table_param.sum_func_count ||
+  if (curr_join->group || curr_join->implicit_grouping ||
       (procedure && (procedure->flags & PROC_GROUP)))
   {
     if (make_group_fields(this, curr_join))

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2009-07-16 13:17:47 +0000
+++ b/sql/sql_select.h	2009-10-05 12:35:54 +0000
@@ -278,6 +278,11 @@ public:
   TABLE    **table,**all_tables,*sort_by_table;
   uint	   tables,const_tables;
   uint	   send_group_parts;
+  /**
+    TRUE if the query contains an aggregate function but has no GROUP
+    BY clause. 
+  */
+  bool	   implicit_grouping; 
   bool	   sort_and_group,first_record,full_join,group, no_field_update;
   bool	   do_send_rows;
   /**
@@ -426,6 +431,7 @@ public:
     tables= 0;
     const_tables= 0;
     join_list= 0;
+    implicit_grouping= FALSE;
     sort_and_group= 0;
     first_record= 0;
     do_send_rows= 1;


Attachment: [text/bzr-bundle] bzr/jorgen.loland@sun.com-20091005123554-nhhdyb5k79jzviy3.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (jorgen.loland:3155) Bug#47280Jorgen Loland5 Oct
  • Re: bzr commit into mysql-5.1-bugteam branch (jorgen.loland:3155)Bug#47280Martin Hansson6 Oct
    • Re: bzr commit into mysql-5.1-bugteam branch (jorgen.loland:3155)Bug#47280Jørgen Løland6 Oct