From b017caefbd5f95fcdc81f1f545fdc944a454d168 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 1 Sep 2006 15:07:04 +0300 Subject: 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: Test case for BUG#21787 mysql-test/t/limit.test: Test case for BUG#21787 sql/sql_select.cc: 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. --- mysql-test/t/limit.test | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'mysql-test/t/limit.test') diff --git a/mysql-test/t/limit.test b/mysql-test/t/limit.test index ef9f63067a4..f70cf835588 100644 --- a/mysql-test/t/limit.test +++ b/mysql-test/t/limit.test @@ -60,4 +60,14 @@ select 1 as a from t1 union all select 1 from dual limit 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 -- cgit v1.2.1