Bug #39798 Limit 20,10 exceeds max result rows will return extra rows

• Submitted: 2 Oct 2008 0:06
• Modified: 2 Oct 2008 17:13
• Reporter: James Burrows
• Status: Not a Bug
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 5.1.6
• OS: Any
• CPU Architecture: Any
• Tags: Connector/J, limit, prepared statement, wrong results

[2 Oct 2008 0:06] James Burrows

Description:
Doing a select returns 25 rows.
When doing a limit on the select such as limit 20,5 it should only return 5 rows however it returns 15.

How to repeat:
Using a prepared statement do the following select
select * from TABLENAME where vehicle_id=7 and type=2 order by serviced_date DESC limit 20,5;

Here is the table and data.

[2 Oct 2008 0:08] James Burrows

Using a prepared statement do the following select
select * from TABLENAME where vehicle_id=7 and type=2 order by serviced_date DESC limit
20,5;

[2 Oct 2008 4:44] Tonci Grgin

Hi James and thanks for your report.

I will need you to:
  - Attach full self-sufficient test case demonstrating this behaviour
  - Write down MySQL server version and JDK version used
  - Check if table is crashed. If so, repair and retest. Inform me of result.

[2 Oct 2008 15:40] James Burrows

Something was wrong with the table. It works properly now. Thanks

[2 Oct 2008 17:13] Tonci Grgin

James, no problem as long as everything works.

Closing the report now. Thanks for your interest in MySQL.