List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:November 11 2011 8:11am
Subject:bzr push into mysql-trunk branch (jorgen.loland:3594 to 3595) Bug#12578908
View as plain text  
 3595 Jorgen Loland	2011-11-11
      Bug#12578908: SELECT SQL_BUFFER_RESULT OUTPUTS TOO MANY ROWS 
                    WHEN GROUP IS CONSTANT
      
      For all but simple grouped queries, temporary tables are used to
      resolve grouping. In these cases, the list of grouping fields is
      stored in the temporary table and grouping is resolved
      there (e.g. by adding a unique constraint on the involved
      fields). Because of this, grouping is not done when inserting
      rows into the temporary table.
      
      In the case where a group clause may be optimized away, grouping
      does not have to be resolved using a temporary table. However, if
      a temporary table is explicitly requested (e.g. because the
      SQL_BUFFER_RESULT hint is used, or the statement is
      INSERT...SELECT), a temporary table is used anyway. In this case,
      the temporary table is created with an empty group list (because
      the group clause was optimized away) and it will therefore not
      create groups. At the same time, make_simple_join() assumes that
      since a temporary table is used, grouping will be taken care of
      and sets JOIN::group to false. 
      
      The fix is in make_simple_join(), and is to not set JOIN::group
      to false in the unlikely event that a temporary table is used for
      a query where the group clause was optimized away. With this
      change, end_{send|write}_group() is used when reading from the
      temporary table. This is the same function as is used when a
      temporary table is not explicitly requested.
     @ mysql-test/r/group_by.result
        Add test for BUG#12578908
     @ mysql-test/t/group_by.test
        Add test for BUG#12578908
     @ sql/sql_select.cc
        Don't set JOIN::group=false in make_simple_join() if the group
        clause was optimized away.

    modified:
      mysql-test/r/group_by.result
      mysql-test/t/group_by.test
      sql/sql_select.cc
 3594 Sunny Bains	2011-11-11
      Fix bug#13363044 FAILING ASSERTION: ERR == DB_SUCCESS IN FILE ROW0VERS.C LINE 144
      
      Checking for DB_SUCCES alone is not sufficient. Not being able to find a
      previous version of the record is a perfectly valid state. This can happen
      because the transaction that created the record has committed and is not
      in purge's view. The caller of row_vers_impl_x_locked_low() has to check
      again whether the transaction is active or not, which it does.
      
      Approved by Jimmy over IM.

    modified:
      storage/innobase/row/row0vers.c
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2011-10-27 08:52:27 +0000
+++ b/mysql-test/r/group_by.result	2011-11-11 08:11:38 +0000
@@ -2127,3 +2127,29 @@ o
 p
 DROP TABLE t1;
 # End of Bug#12837714
+#
+# Bug#12578908: SELECT SQL_BUFFER_RESULT OUTPUTS TOO MANY 
+#               ROWS WHEN GROUP IS OPTIMIZED AWAY
+#
+CREATE TABLE t1 (col1 int, col2 int) ;
+INSERT INTO t1 VALUES (10,1),(11,7);
+CREATE TABLE t2 (col1 int, col2 int) ;
+INSERT INTO t2 VALUES (10,8);
+
+EXPLAIN SELECT SQL_BUFFER_RESULT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	Using temporary
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT SQL_BUFFER_RESULT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2;
+col2
+8
+
+EXPLAIN SELECT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2;
+col2
+8
+
+DROP TABLE t1,t2;

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2011-10-18 10:23:09 +0000
+++ b/mysql-test/t/group_by.test	2011-11-11 08:11:38 +0000
@@ -1482,3 +1482,26 @@ SELECT vc FROM t1 GROUP BY vc;
 DROP TABLE t1; 
 
 --echo # End of Bug#12837714
+
+--echo #
+--echo # Bug#12578908: SELECT SQL_BUFFER_RESULT OUTPUTS TOO MANY 
+--echo #               ROWS WHEN GROUP IS OPTIMIZED AWAY
+--echo #
+
+CREATE TABLE t1 (col1 int, col2 int) ;
+INSERT INTO t1 VALUES (10,1),(11,7);
+
+CREATE TABLE t2 (col1 int, col2 int) ;
+INSERT INTO t2 VALUES (10,8);
+
+let $q_body=t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2;
+
+--echo
+--eval EXPLAIN SELECT SQL_BUFFER_RESULT $q_body
+--eval SELECT SQL_BUFFER_RESULT $q_body
+--echo
+--eval EXPLAIN SELECT $q_body
+--eval SELECT $q_body
+
+--echo
+DROP TABLE t1,t2;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-11-04 15:20:13 +0000
+++ b/sql/sql_select.cc	2011-11-11 08:11:38 +0000
@@ -10385,7 +10385,29 @@ JOIN::make_simple_join(JOIN *parent, TAB
   tmp_table_param.copy_field= tmp_table_param.copy_field_end=0;
   first_record= sort_and_group=0;
   send_records= (ha_rows) 0;
-  group= 0;
+
+  if (!group_optimized_away)
+  {
+    group= false;
+  }
+  else
+  {
+    /*
+      If grouping has been optimized away, a temporary table is
+      normally not needed unless we're explicitly requested to create
+      one (e.g. due to a SQL_BUFFER_RESULT hint or INSERT ... SELECT).
+
+      In this case (grouping was optimized away), tmp_table was
+      created without a grouping expression and JOIN::exec() will not
+      perform the necessary grouping (by the use of end_send_group()
+      or end_write_group()) if JOIN::group is set to false.
+    */
+    // the temporary table was explicitly requested
+    DBUG_ASSERT(test(select_options & OPTION_BUFFER_RESULT));
+    // the temporary table does not have a grouping expression
+    DBUG_ASSERT(!tmp_table->group); 
+  }
+
   row_limit= unit->select_limit_cnt;
   do_send_rows= row_limit ? 1 : 0;
 

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (jorgen.loland:3594 to 3595) Bug#12578908Jorgen Loland11 Nov