Bug #99851 The LIMIT offset, count function is not performing in a consistent manner.
Submitted: 11 Jun 2020 15:41 Modified: 12 Jun 2020 4:22
Reporter: David Barker Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:8.0.20 OS:Windows (Windows 10 Pro 64bit)
Assigned to: MySQL Verification Team CPU Architecture:x86 (X64)
Tags: limit, order

[11 Jun 2020 15:41] David Barker 
Description:
I have a pretty simple InnoDb table with 14 columns of Ints, varchar and a timestamp. The data in the table is static.

I experience the same behaviour through the MySQL ODBC 8.0 ANSI Driver in a C# ASP .Net4.5 application and MySQL Workbench 8.0.20.

The LIMIT offset, count function is not performing in a consistent manner for me when the offset and count argument are both 1.

QUERIES:

SELECT * FROM tblactcodes WHERE lngGroupId=1 AND lngTier=1 ORDER BY lngOrder LIMIT 0,1;

The above fetches the first row from the result dataset, correct.

SELECT * FROM tblactcodes WHERE lngGroupId=1 AND lngTier=1 ORDER BY lngOrder LIMIT 1,1;

The above fetches the third row from the result dataset, incorrect.

SELECT * FROM tblactcodes WHERE lngGroupId=1 AND lngTier=1 ORDER BY lngOrder LIMIT 2,1;

The above fetches the third row from the result dataset, correct.

SELECT * FROM tblactcodes WHERE lngGroupId=1 AND lngTier=1 ORDER BY lngOrder LIMIT 1,2;

The above fetches the second and third row from the result dataset, correct.

SELECT * FROM tblactcodes WHERE lngGroupId=1 AND lngTier=1 ORDER BY lngOrder LIMIT 8;

The above works as expected fetching the first 8 rows, correct.

How to repeat:
The behaviour is consistent.
[11 Jun 2020 16:02] David Barker 
Result Dataset, Note lngOrderColumn value duplicates

Attachment: 99851-ActivityTableSubsetORDER-LIMIT issue.csv (application/vnd.ms-excel, text), 677 bytes.
[11 Jun 2020 16:24] David Barker 
The issue manifests itself when the ORDER data is has same value, in this instance (see file attachment), lngOrder = 1 for 2nd and third rows.
[12 Jun 2020 4:22] MySQL Verification Team 
Hi David,

> The issue manifests itself when the ORDER data is has same value, in this instance (see file attachment), lngOrder = 1 for 2nd and third rows.

This is not a bug. All your results are "correct". There is no requirement by the SQL standard to respect any order other than what is explicitly stated in the query. 

Kind regards
Bogdan