Bug #119498 The different sorting results are caused by the range query.
Submitted: 3 Dec 9:07 Modified: 3 Dec 9:10
Reporter: gg a Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:8.4.7-TLS OS:Any
Assigned to: CPU Architecture:Any

[3 Dec 9:07] gg a 
Description:
The results and sorting of the following two SQL queries are different:

select id 
from EXECUTION 
where company_id = 1 
  and EXECUTE_DATE = '20251112' 
order by EXECUTE_DATE desc, ID desc 
limit 0, 25;

=========

select id 
from EXECUTION 
where company_id = 1 
  and EXECUTE_DATE >= '20251112' 
  and EXECUTE_DATE <= '20251112' 
order by EXECUTE_DATE desc, ID desc 
limit 0, 25;

I have verified this issue in all versions of 8.4.x, and the problem exists in every one of them.
However, this issue does not occur in version 8.0.30.

How to repeat:
(Tue Dec 2 19:12:52 2025,6)(dev-dbadmin@oci.db)[TRADING]>select id from EXECUTION where company_id=1 and EXECUTE_DATE='20251112' order by EXECUTE_DATE desc, ID desc limit 0, 25; 
+---------------------+ | id | +---------------------+ | 2611938173270819893 | | 2611938173203710005 | | 2611938154161568309 | | 2611938154086069813 | | 2611938137342406709 | | 2611938137275296821 | | 2611938118317041205 | | 2611938118249931317 | | 2611938099518168117 | | 2611938099451058229 | | 2611938080702517813 | | 2611938080635407925 | | 2611938061836535861 | | 2611938061769425973 | | 2611938042911833653 | | 2611938042844723765 | | 2611938024054240309 | | 2611938023987130421 | | 2611938005582522933 | | 2611938005515413045 | | 2611937986775261237 | | 2611937986708151349 | | 2611937969494726197 | | 2611937969436004917 | | 2611937950544858165 | +---------------------+ 
25 rows in set (0.02 sec) 
(Tue Dec 2 19:13:11 2025,7)(dev-dbadmin@oci.db)[TRADING]>select id from EXECUTION where company_id=1 and EXECUTE_DATE>='20251112' and EXECUTE_DATE<='20251112' order by EXECUTE_DATE desc, ID desc limit 0, 25; 
+---------------------+ | id | +---------------------+ | 2611583402405525557 | | 2611583402481024053 | | 2611583421212787253 | | 2611583421288285749 | | 2611583438317161525 | | 2611583438384271413 | | 2611583457149589045 | | 2611583457225087541 | | 2611583475386425397 | | 2611583475461923893 | | 2611583494202075701 | | 2611583494269185589 | | 2611583513059669045 | | 2611583513126778933 | | 2611583531967594037 | | 2611583532043092533 | | 2611583550800021557 | | 2611583550867131445 | | 2611583569624060469 | | 2611583569691170357 | | 2611583588565539893 | | 2611583588632649781 | | 2611583605258872373 | | 2611583605325982261 | | 2611583624250683445 | +---------------------+ 25 rows in set (0.00 sec)
[3 Dec 9:10] gg a 
(Tue Dec  2 19:12:52 2025,6)(dev-dbadmin@oci.db)[TRADING]>select id from EXECUTION where company_id=1 and EXECUTE_DATE='20251112' order by EXECUTE_DATE desc, ID desc limit 0, 25;
+---------------------+
| id                  |
+---------------------+
| 2611938173270819893 |
| 2611938173203710005 |
| 2611938154161568309 |
| 2611938154086069813 |
| 2611938137342406709 |
| 2611938137275296821 |
| 2611938118317041205 |
| 2611938118249931317 |
| 2611938099518168117 |
| 2611938099451058229 |
| 2611938080702517813 |
| 2611938080635407925 |
| 2611938061836535861 |
| 2611938061769425973 |
| 2611938042911833653 |
| 2611938042844723765 |
| 2611938024054240309 |
| 2611938023987130421 |
| 2611938005582522933 |
| 2611938005515413045 |
| 2611937986775261237 |
| 2611937986708151349 |
| 2611937969494726197 |
| 2611937969436004917 |
| 2611937950544858165 |
+---------------------+
25 rows in set (0.02 sec)

(Tue Dec  2 19:13:11 2025,7)(dbadmin@oci.db)[TRADING]>select id from EXECUTION where company_id=1 and EXECUTE_DATE>='20251112' and  EXECUTE_DATE<='20251112' order by EXECUTE_DATE desc, ID desc limit 0, 25;
+---------------------+
| id                  |
+---------------------+
| 2611583402405525557 |
| 2611583402481024053 |
| 2611583421212787253 |
| 2611583421288285749 |
| 2611583438317161525 |
| 2611583438384271413 |
| 2611583457149589045 |
| 2611583457225087541 |
| 2611583475386425397 |
| 2611583475461923893 |
| 2611583494202075701 |
| 2611583494269185589 |
| 2611583513059669045 |
| 2611583513126778933 |
| 2611583531967594037 |
| 2611583532043092533 |
| 2611583550800021557 |
| 2611583550867131445 |
| 2611583569624060469 |
| 2611583569691170357 |
| 2611583588565539893 |
| 2611583588632649781 |
| 2611583605258872373 |
| 2611583605325982261 |
| 2611583624250683445 |
+---------------------+
25 rows in set (0.00 sec)