bk commit into 4.1 tree (timour:1.2535) BUG#21787

[timour]

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 14:47:27+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 14:47:24+03:00, timour@stripped +14 -0
    Test case for BUG#21787

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

  sql/sql_select.cc@stripped, 2006-08-31 14:47:24+03:00, timour@stripped +6 -1
    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 14:47:29 +03:00
+++ 1.465/sql/sql_select.cc	2006-08-31 14:47:29 +03:00
@@ -5612,8 +5612,13 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
       keyinfo->key_length+=  key_part_info->length;
     }
   }
-  else
+  else if (!thd->lex->current_select->with_sum_func)
   {
+    /*
+      If there are no aggregate functions, then we can materialize only up
+      to 'rows_limit' records. Otherwise we have to store all rows since
+      aggregate functions must be computed over all result rows.
+    */
     set_if_smaller(table->max_rows, rows_limit);
     param->end_write_records= rows_limit;
   }

--- 1.8/mysql-test/r/limit.result	2006-08-31 14:47:29 +03:00
+++ 1.9/mysql-test/r/limit.result	2006-08-31 14:47:29 +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 14:47:29 +03:00
+++ 1.10/mysql-test/t/limit.test	2006-08-31 14:47:29 +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