bzr commit into mysql-6.0-bugfixing branch (jorgen.loland:2840) Bug#46328

[Jorgen Loland]

#At file:///localhome/jl208045/mysql/mysql-6.0-codebase-bugfixing-46328/ based on revid:jon.hauglid@stripped

 2840 Jorgen Loland	2009-09-25
      Bug#46328 - Use of aggregate function without GROUP BY clause 
      returns many rows (vs. one )
                  
      If a join buffer is used to join a table the ordering by an index 
      for the first non-constant table cannot be employed unless 
      ORDER BY/GROUP BY clauses are optimized away or there is implicit 
      grouping (aggregate functions but no GROUP BY clause). 
     @ mysql-test/r/join_cache.result
        Added test case for BUG#46328
     @ mysql-test/t/join_cache.test
        Added test case for BUG#46328
     @ sql/sql_select.h
        The JOIN::get_sort_by_join_tab function now checks if the query contains implicit grouping

    modified:
      mysql-test/r/join_cache.result
      mysql-test/t/join_cache.test
      sql/sql_select.h
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2009-09-04 06:58:24 +0000
+++ b/mysql-test/r/join_cache.result	2009-09-25 12:19:01 +0000
@@ -3993,3 +3993,38 @@ pk
 NULL
 DROP TABLE IF EXISTS t1, t2, t3;
 #
+# Bug #46328: Use of aggregate function without GROUP BY clause 
+#             returns many rows (vs. one )
+#             
+CREATE TABLE t1 (
+int_key int(11) NOT NULL,
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES
+(0),(2),(2),(2),(3),(4),(5),(5),(6),(6),(8),(8),(9),(9);
+CREATE TABLE t2 (
+int_key int(11) NOT NULL,
+KEY int_key (int_key)
+);
+INSERT INTO t2 VALUES (2),(3);
+
+# The query shall return 1 record with a max value 9 and one of the 
+# int_key values inserted above (undefined which one). A changed 
+# execution plan may change the value in the second column
+SELECT  MAX(t1.int_key), t1.int_key
+FROM t1 STRAIGHT_JOIN t2  
+ORDER BY t1.int_key;
+MAX(t1.int_key)	int_key
+9	0
+
+explain 
+SELECT  MAX(t1.int_key), t1.int_key
+FROM t1 STRAIGHT_JOIN t2  
+ORDER BY t1.int_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	int_key	4	NULL	14	Using index
+1	SIMPLE	t2	index	NULL	int_key	4	NULL	2	Using index; Using join buffer
+
+DROP TABLE t1,t2;
+SET join_cache_level=default;
+#

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2009-09-04 06:58:24 +0000
+++ b/mysql-test/t/join_cache.test	2009-09-25 12:19:01 +0000
@@ -1645,6 +1645,47 @@ GROUP BY 1;
 
 DROP TABLE IF EXISTS t1, t2, t3;
 
+--echo #
+--echo # Bug #46328: Use of aggregate function without GROUP BY clause 
+--echo #             returns many rows (vs. one )
+--echo #             
+
+CREATE TABLE t1 (
+  int_key int(11) NOT NULL,
+  KEY int_key (int_key)
+);
+
+INSERT INTO t1 VALUES
+(0),(2),(2),(2),(3),(4),(5),(5),(6),(6),(8),(8),(9),(9);
+
+CREATE TABLE t2 (
+  int_key int(11) NOT NULL,
+  KEY int_key (int_key)
+);
+
+INSERT INTO t2 VALUES (2),(3);
+
+--echo
+
+--echo # The query shall return 1 record with a max value 9 and one of the 
+--echo # int_key values inserted above (undefined which one). A changed 
+--echo # execution plan may change the value in the second column
+SELECT  MAX(t1.int_key), t1.int_key
+FROM t1 STRAIGHT_JOIN t2  
+ORDER BY t1.int_key;
+
+--echo
+
+explain 
+SELECT  MAX(t1.int_key), t1.int_key
+FROM t1 STRAIGHT_JOIN t2  
+ORDER BY t1.int_key;
+
+--echo
+
+DROP TABLE t1,t2;
+
+SET join_cache_level=default;
 
 --echo #
 

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2009-09-21 09:09:24 +0000
+++ b/sql/sql_select.h	2009-09-25 12:19:01 +0000
@@ -1442,7 +1442,8 @@ public:
   uint     outer_tables;  /**< Number of tables that are not inside semijoin */
   uint     const_tables;
   uint	   send_group_parts;
-  bool	   sort_and_group,first_record,full_join,group, no_field_update;
+  bool	   sort_and_group,first_record,full_join,no_field_update;
+  bool	   group;          /**< If query contains GROUP BY clause */
   bool	   do_send_rows;
   /**
     TRUE when we want to resume nested loop iterations when
@@ -1747,12 +1748,12 @@ public:
   }
   /* 
     Return the table for which an index scan can be used to satisfy 
-    the sort order needed by the ORDER BY/GROUP BY clause 
+    the sort order needed by the ORDER BY/(implicit) GROUP BY clause 
   */
   JOIN_TAB *get_sort_by_join_tab()
   {
     return (need_tmp || !sort_by_table || skip_sort_order ||
-            (group && !group_list)) ?
+            ((group || tmp_table_param.sum_func_count) && !group_list)) ?
               NULL : join_tab+const_tables;
   }
 private:


Attachment: [text/bzr-bundle] bzr/[e-mail]