diff options
| author | unknown <timour/timka@lamia.home> | 2006-09-01 15:07:04 +0300 |
|---|---|---|
| committer | unknown <timour/timka@lamia.home> | 2006-09-01 15:07:04 +0300 |
| commit | b017caefbd5f95fcdc81f1f545fdc944a454d168 (patch) | |
| tree | 3e3f57b1259be8b80af868ee4f4c15dbf692b032 /mysql-test/r/limit.result | |
| parent | bfdbb780c26aae2705cf0d7e3048b5c0e40f59e1 (diff) | |
| download | mariadb-git-b017caefbd5f95fcdc81f1f545fdc944a454d168.tar.gz | |
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.
Diffstat (limited to 'mysql-test/r/limit.result')
| -rw-r--r-- | mysql-test/r/limit.result | 14 |
1 files changed, 14 insertions, 0 deletions
diff --git a/mysql-test/r/limit.result b/mysql-test/r/limit.result index 6a3d2bffab0..92803ec3449 100644 --- a/mysql-test/r/limit.result +++ b/mysql-test/r/limit.result @@ -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 |