Description:
As we have one table in which we are getting different results set as unexpected. Mainly the issue is when we are using order by with limit then the result set will suffer as unexpected. For example:
select src.id, src.name, src.sku_type, src.is_deleted, src.status, src.is_service_configured as is_activatable, src.created_at from service_request_config src WHERE src.is_deleted = 0 AND src.status = 1;
This above query giving me 31 records as expected which is proper in DB. Now going to add order by on one column created_at then result set will suffer as the expected result.
select src.id, src.name, src.sku_type, src.is_deleted, src.status, src.is_service_configured as is_activatable, src.created_at from service_request_config src WHERE src.is_deleted = 0 AND src.status = 1 order by src.created_at desc;
Giving me result perfect as ordering by created_at. Now going to add limit 10 with offset 0 with the same query. As our expectation it will result first 10 rows from above query but it will giving us any random result from total record.
select src.id, src.name, src.sku_type, src.is_deleted, src.status, src.is_service_configured as is_activatable, src.created_at from service_request_config src WHERE src.is_deleted = 0 AND src.status = 1 order by src.created_at desc limit 10 offset 0;
Giving me a different result set from the above query but the expected result was first 10 rows. Now I want next 10 rows.
select src.id, src.name, src.sku_type, src.is_deleted, src.status, src.is_service_configured as is_activatable, src.created_at from service_request_config src WHERE src.is_deleted = 0 AND src.status = 1 order by src.created_at desc limit 10 offset 10;
Gave me the results of the next 10 rows but some ids are the same as the first 10 rows. so that is totally wrong result.
Explain same below with real data set.
How to repeat:
1. select src.id, src.name, src.sku_type, src.is_deleted, src.status, src.is_service_configured as is_activatable, src.created_at from service_request_config src WHERE src.is_deleted = 0 AND src.status = 1 order by created_at desc;
Result:
mysql> select src.id, src.name, src.sku_type, src.is_deleted, src.status, src.is_service_configured as is_activatable, src.created_at from service_request_config src WHERE src.is_deleted = 0 AND src.status = 1 ORDER BY src.created_at desc;
+-----+--------------------------------------+----------+------------+--------+----------------+---------------------+
| id | name | sku_type | is_deleted | status | is_activatable | created_at |
+-----+--------------------------------------+----------+------------+--------+----------------+---------------------+
| 236 | AddOn21Jan | Add-on | 0 | 1 | 1 | 2023-01-21 06:02:28 |
| 233 | Add on 3D | Add-on | 0 | 1 | 1 | 2023-01-21 02:01:40 |
| 231 | Test Add On 1 | Add-on | 0 | 1 | 1 | 2023-01-20 10:57:37 |
| 230 | 2000 | Add-on | 0 | 1 | 1 | 2023-01-20 10:41:29 |
| 222 | Add on test | Add-on | 0 | 1 | 1 | 2023-01-19 09:57:23 |
| 207 | Print Delivery | Add-on | 0 | 1 | 1 | 2023-01-18 08:47:37 |
| 109 | Same Category Package | Bundle | 0 | 1 | 1 | 2022-12-23 09:01:13 |
| 104 | Furniture Layout 3D | Add-on | 0 | 1 | 1 | 2022-12-21 10:10:20 |
| 18 | 2D Layout | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 28 | Night View | Add-on | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 25 | Additional 3D design Option | Add-on | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 23 | Additional 2D design Option | Add-on | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 22 | Visualization Package | Bundle | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 21 | Advanced Concept Design Package | Bundle | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 20 | Concept Design Package | Bundle | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 19 | 3D Elevation | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 2 | Ask The Expert | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 17 | Detailed Estimation | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 16 | Financial Service | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 15 | Anti Termite Treatment | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 14 | Rainwater Harvesting | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 13 | Vaastu Service | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 11 | Water Quality Check | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 10 | Site Demo for Column and Foundation | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 9 | Sand Quality Check | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 8 | Coarse Aggregates Quality Check | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 7 | Concrete Mix Proportioning | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 6 | Roof Slab Concreting Assistance | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 5 | Concrete Compressive Strength Test | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 4 | Site Demo For Masonry And Plastering | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 3 | Virtual Reality Experience | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
+-----+--------------------------------------+----------+------------+--------+----------------+---------------------+
31 rows in set (0.00 sec)
2. select src.id, src.name, src.sku_type, src.is_deleted, src.status, src.is_service_configured as is_activatable, src.created_at from service_request_config src WHERE src.is_deleted = 0 AND src.status = 1 ORDER BY src.created_at desc limit 10 offset 0;
Result:
mysql> select src.id, src.name, src.sku_type, src.is_deleted, src.status, src.is_service_configured as is_activatable, src.created_at from service_request_config src WHERE src.is_deleted = 0 AND src.status = 1 ORDER BY src.created_at desc limit 10 offset 0;
+-----+---------------------------------+----------+------------+--------+----------------+---------------------+
| id | name | sku_type | is_deleted | status | is_activatable | created_at |
+-----+---------------------------------+----------+------------+--------+----------------+---------------------+
| 236 | AddOn21Jan | Add-on | 0 | 1 | 1 | 2023-01-21 06:02:28 |
| 233 | Add on 3D | Add-on | 0 | 1 | 1 | 2023-01-21 02:01:40 |
| 231 | Test Add On 1 | Add-on | 0 | 1 | 1 | 2023-01-20 10:57:37 |
| 230 | 2000 | Add-on | 0 | 1 | 1 | 2023-01-20 10:41:29 |
| 222 | Add on test | Add-on | 0 | 1 | 1 | 2023-01-19 09:57:23 |
| 207 | Print Delivery | Add-on | 0 | 1 | 1 | 2023-01-18 08:47:37 |
| 109 | Same Category Package | Bundle | 0 | 1 | 1 | 2022-12-23 09:01:13 |
| 104 | Furniture Layout 3D | Add-on | 0 | 1 | 1 | 2022-12-21 10:10:20 |
| 3 | Virtual Reality Experience | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 6 | Roof Slab Concreting Assistance | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
+-----+---------------------------------+----------+------------+--------+----------------+---------------------+
10 rows in set (0.00 sec)
comment: As expecting same order from query 10 with just first 10 rows.
3. select src.id, src.name, src.sku_type, src.is_deleted, src.status, src.is_service_configured as is_activatable, src.created_at from service_request_config src WHERE src.is_deleted = 0 AND src.status = 1 ORDER BY src.created_at desc limit 10 offset 10;
Result:
mysql> select src.id, src.name, src.sku_type, src.is_deleted, src.status, src.is_service_configured as is_activatable, src.created_at from service_request_config src WHERE src.is_deleted = 0 AND src.status = 1 ORDER BY src.created_at desc limit 10 offset 10;
+----+--------------------------------------+----------+------------+--------+----------------+---------------------+
| id | name | sku_type | is_deleted | status | is_activatable | created_at |
+----+--------------------------------------+----------+------------+--------+----------------+---------------------+
| 20 | Concept Design Package | Bundle | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 17 | Detailed Estimation | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 16 | Financial Service | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 15 | Anti Termite Treatment | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 14 | Rainwater Harvesting | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 10 | Site Demo for Column and Foundation | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 9 | Sand Quality Check | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 7 | Concrete Mix Proportioning | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 6 | Roof Slab Concreting Assistance | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 4 | Site Demo For Masonry And Plastering | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
+----+--------------------------------------+----------+------------+--------+----------------+---------------------+
10 rows in set (0.00 sec)
comment: As expected no single record will be repeated but here id 6 is in both result sets no. 2 and 3.
4. select src.id, src.name, src.sku_type, src.is_deleted, src.status, src.is_service_configured as is_activatable, src.created_at from service_request_config src WHERE src.is_deleted = 0 AND src.status = 1 ORDER BY src.created_at desc limit 10 offset 20;
Result:
mysql> select src.id, src.name, src.sku_type, src.is_deleted, src.status, src.is_service_configured as is_activatable, src.created_at from service_request_config src WHERE src.is_deleted = 0 AND src.status = 1 ORDER BY src.created_at desc limit 10 offset 20;
+----+--------------------------------------+----------+------------+--------+----------------+---------------------+
| id | name | sku_type | is_deleted | status | is_activatable | created_at |
+----+--------------------------------------+----------+------------+--------+----------------+---------------------+
| 14 | Rainwater Harvesting | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 13 | Vaastu Service | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 11 | Water Quality Check | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 10 | Site Demo for Column and Foundation | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 9 | Sand Quality Check | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 8 | Coarse Aggregates Quality Check | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 7 | Concrete Mix Proportioning | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 6 | Roof Slab Concreting Assistance | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 5 | Concrete Compressive Strength Test | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
| 4 | Site Demo For Masonry And Plastering | Basic | 0 | 1 | 1 | 2022-10-26 06:25:06 |
+----+--------------------------------------+----------+------------+--------+----------------+---------------------+
10 rows in set (0.00 sec)
comment: As expected no single record will be repeated but here id 6 is in all three result sets no. 2, 3 and 4. id 14, 10, 9, 7 and 4 are in both result sets no. 3 and 4.
