Bug #4786 Aggregates doesn't return values when LIMIT+OFFSET

• Submitted: 28 Jul 2004 14:12
• Modified: 28 Jul 2004 15:39
• Reporter: Ruslan Zakirov
• Status: Not a Bug
• Category: Server
• Severity: S3 (Non-critical)
• Version: 4.0.20
• OS: Linux (Linux)

[28 Jul 2004 14:12] Ruslan Zakirov

Description:
Agregates doesn't work as expected when OFFSET defined:
> select MAX(id) from TEST order by test_id LIMIT 3 OFFSET 1;
Empty set (0.00 sec)

How to repeat:
> select MAX(id) from TEST order by test_id LIMIT 3 OFFSET 1;
Empty set (0.00 sec)

> select id from TEST order by test_id LIMIT 3 OFFSET 1;
+------+
| id   |
+------+
|    6 |
|    3 |
|    4 |
+------+

> select id from TEST order by test_id LIMIT 3;
+------+
| id   |
+------+
|    5 |
|    6 |
|    3 |
+------+
 
> select MAX(id) from TEST order by test_id LIMIT 3;
+---------+
| MAX(id) |
+---------+
|       6 |
+---------+

[28 Jul 2004 15:39] Hartmut Holzgraefe

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Row offsets start at 0, not 1. SELECT MAX(...) without a 
GROUP BY will only return one row with offset zero.
With "OFFSET 1" you are asking for a nonexistant second
result row so you get an empty result set.

See also: http://dev.mysql.com/doc/mysql/en/SELECT.html

  The LIMIT clause can be used to constrain the number of rows 
  returned by the SELECT statement. LIMIT takes one or two 
  numeric arguments, which must be integer constants. With two 
  arguments, the first argument specifies the offset of the first row 
  to return, and the second specifies the maximum number of rows 
  to return. The offset of the initial row is 0 (not 1):