MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Oystein.Grovlen Date:May 6 2010 2:11pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (oystein.grovlen:3137)
Bug#46328
View as plain text  
#At file:///home/oysteing/mysql/mysql-next-mr-opt-backporting/ based on revid:roy.lyseng@stripped

 3137 oystein.grovlen@stripped	2010-05-06
      Bug#46328 - Use of aggregate function without GROUP BY clause 
      returns many rows (vs. one )
      
      (Backporting of revid:jorgen.loland@stripped)
                    
      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	2010-05-02 18:01:00 +0000
+++ b/mysql-test/r/join_cache.result	2010-05-06 14:11:28 +0000
@@ -3966,6 +3966,41 @@ set join_buffer_size=default;
 set join_cache_level=default;
 DROP TABLE t1,t2,t3,t4,t5;
 #
+# 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;
+#
 # Bug #45019: join buffer contains two blob columns one of which is
 #             used in the key employed to access the joined table
 #

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2010-05-02 18:01:00 +0000
+++ b/mysql-test/t/join_cache.test	2010-05-06 14:11:28 +0000
@@ -1619,6 +1619,48 @@ set join_cache_level=default;
 DROP TABLE t1,t2,t3,t4,t5;
 
 --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 #
 --echo # Bug #45019: join buffer contains two blob columns one of which is
 --echo #             used in the key employed to access the joined table
 --echo #
@@ -1643,3 +1685,4 @@ SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH
 set join_cache_level=default;
 
 DROP TABLE t1,t2;
+

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2010-05-05 10:54:22 +0000
+++ b/sql/sql_select.h	2010-05-06 14:11:28 +0000
@@ -1456,7 +1456,8 @@ public:
     @see make_group_fields, alloc_group_fields, JOIN::exec
   */
   bool     sort_and_group; 
-  bool     first_record,full_join,group, no_field_update;
+  bool     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
@@ -1784,11 +1785,12 @@ public:
   void cache_const_exprs();
   /* 
     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) ?
+    return (need_tmp || !sort_by_table || skip_sort_order ||
+            ((group || tmp_table_param.sum_func_count) && !group_list)) ?
               NULL : join_tab+const_tables;
   }
 private:


Attachment: [text/bzr-bundle]
Thread
bzr commit into mysql-next-mr-bugfixing branch (oystein.grovlen:3137)Bug#46328Oystein.Grovlen6 May