Sorting issue with limit and offset MYSQL

Question

I am facing sorting issue in mysql

See the output of below query:

select astrologers.id,astrologers.name,chat_online,online,experience from `astrologers` 
where `astrologers`.`status` = '1' 
order by experience asc limit 10;

id	name	chat_online	online	experience
15	Astro Anoop	0	0	3
20	Test Astro2	0	0	3
3	Test anoop	0	0	5
4	Anoop Kumar trivedi	0	0	5
7	Test	0	0	5
58	Neeraj yadav	1	0	5
45	Satish Kumar Gupta	1	1	10
56	AP Sharma	1	0	15
40	VG Astrologer App	1	0	55

In above result id 58 (Neeraj yadav) is at 6th position but when I run the same query with limit 3, same id 58 (Neeraj yadav) is at 3rd position:

select astrologers.id,astrologers.name,chat_online,online,experience 
from `astrologers` 
where `astrologers`.`status` = '1' 
order by experience asc limit 3;

id	name	chat_online	experience
20	Test Astro2	0	3
15	Astro Anoop	0	3
58	Neeraj yadav	1	5

The 3rd row in above result should be id 3 (Test anoop) but it gives id 58 (Neeraj yadav)

Is this bug in mysql?

Why should it be 3 and not 58? They both have the exact same experience so any one of the three is correct. Is there another column that you are wanting to sort by in addition to experience? Perhaps you want ORDER BY experience, id? — JNevill
– JNevill, Commented Nov 2, 2022 at 18:01

GMB · Accepted Answer · 2022-11-02 18:05:33Z

3

Is this a bug in MySQL?

No. The problem is that your sort is not deterministic, and gives ties in the third position:

|  3 | Test anoop          |           0 |      0 |          5 |
|  4 | Anoop Kumar trivedi |           0 |      0 |          5 |
|  7 | Test                |           0 |      0 |          5 |
| 58 | Neeraj yadav        |           1 |      0 |          5 |

All 4 users have the same experience, hence leaving the database to figure out how they should be sorted.

When asked to return to top 3 rows, the database picks the first two, and then one of the 4 ties. The result that you get might not be consistent over consequent executions of the same query, as you are starting to see.

Bottom line: know you data; if you want a deterministic result, then use a deterministic sort. We could, for example, use id to break the ties, hence making the result predictable:

order by experience, id limit 3

answered Nov 2, 2022 at 18:05

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Satish Gupta Over a year ago

MySQL first fetches the result and then applies the limit na ?, in this case, it result should be the same irrespective of the limit and offset

Satish Gupta Over a year ago

work with this but I don't understand the logic. Mysql always gives priority to the first created row if the sort is tied. @gmb

GMB Over a year ago

@SatishGupta: Mysql always gives priority to the first created row if the sort is tied.: that's a common misunderstanding. Your experience might trick you into thinking that the database follows a certain rule, but the database actually does not guarantee anything in that case. This is not a documented behavior, and it may or may not work as you expect without qualifying as a bug.

Collectives™ on Stack Overflow

Sorting issue with limit and offset MYSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related