MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:timour Date:August 31 2006 2:04pm
Subject:bk commit into 4.1 tree (timour:1.2535) BUG#21787
View as plain text  
Below is the list of changes that have just been committed into a local
4.1 repository of timka. When timka does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-08-31 17:04:00+03:00, timour@stripped +3 -0
  Fix for BUG#21787: COUNT(*) + ORDER BY + LIMIT returns wrong result
  
  The problem was due to a prior fix for BUG 9676, which limited
  the rows stored in a temporary table to the LIMIT clause. This
  optimization is not applicable to non-group queries with aggregate
  functions. The fix disables the optimization in this case.

  mysql-test/r/limit.result@stripped, 2006-08-31 17:03:58+03:00, timour@stripped +14 -0
    Test case for BUG#21787

  mysql-test/t/limit.test@stripped, 2006-08-31 17:03:58+03:00, timour@stripped +10 -0
    Test case for BUG#21787

  sql/sql_select.cc@stripped, 2006-08-31 17:03:59+03:00, timour@stripped +14 -5
    If there is an aggregate function in a non-group query,
    materialize all rows in the temporary table no matter if
    there is a LIMIT clause. This is necessary, since the
    aggregate functions must be computed over all result rows,
    not just the first LIMIT rows.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	timour
# Host:	lamia.home
# Root:	/home/timka/mysql/src/4.1-bug-21787

--- 1.464/sql/sql_select.cc	2006-08-31 17:04:03 +03:00
+++ 1.465/sql/sql_select.cc	2006-08-31 17:04:03 +03:00
@@ -5612,11 +5612,6 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
       keyinfo->key_length+=  key_part_info->length;
     }
   }
-  else
-  {
-    set_if_smaller(table->max_rows, rows_limit);
-    param->end_write_records= rows_limit;
-  }
 
   if (distinct && field_count != param->hidden_field_count)
   {
@@ -5679,6 +5674,20 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
 	0 : FIELDFLAG_BINARY;
     }
   }
+
+  /*
+    Push the LIMIT clause to the temporary table creation, so that we
+    materialize only up to 'rows_limit' records instead of all result records.
+    This optimization is not applicable when there is GROUP BY or there is
+    no GROUP BY, but there are aggregate functions, because both must be
+    computed for all result rows.
+  */
+  if (!group && !thd->lex->current_select->with_sum_func)
+  {
+    set_if_smaller(table->max_rows, rows_limit);
+    param->end_write_records= rows_limit;
+  }
+
   if (thd->is_fatal_error)				// If end of memory
     goto err;					 /* purecov: inspected */
   table->db_record_offset=1;

--- 1.8/mysql-test/r/limit.result	2006-08-31 17:04:03 +03:00
+++ 1.9/mysql-test/r/limit.result	2006-08-31 17:04:03 +03:00
@@ -76,3 +76,17 @@ a
 a
 1
 drop table t1;
+create table t1 (a int);
+insert into t1 values (1),(2),(3),(4),(5),(6),(7);
+explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where; Using temporary
+select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+c
+7
+explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where; Using temporary
+select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+c
+28

--- 1.9/mysql-test/t/limit.test	2006-08-31 17:04:03 +03:00
+++ 1.10/mysql-test/t/limit.test	2006-08-31 17:04:03 +03:00
@@ -60,4 +60,14 @@ select 1 as a from t1 union all select 1
 (select 1 as a from t1) union all (select 1 from dual) limit 1;
 drop table t1;
 
+#
+# Bug #21787: COUNT(*) + ORDER BY + LIMIT returns wrong result
+#
+create table t1 (a int);
+insert into t1 values (1),(2),(3),(4),(5),(6),(7);
+explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+
 # End of 4.1 tests
Thread
bk commit into 4.1 tree (timour:1.2535) BUG#21787timour31 Aug