MySQL Bugs: #109728: SELECT statement: Result is not proper with GROUP BY with LIMIT OFFSET

Bug #109728	SELECT statement: Result is not proper with GROUP BY with LIMIT OFFSET
Submitted:	21 Jan 2023 19:27	Modified:	21 Feb 2023 15:50
Reporter:	Shubham Parmar	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	8.0.30	OS:	Any
Assigned to:		CPU Architecture:	Any

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.

Hi,
I cannot reproduce this using similar query and my own dataset. Please provide full reproducible test case with 

- create table to create all tables required for the query
- inserts to populate this tables with required data
- queries that return wrong data

Thanks

Hi,

I am also experiencing troubles with the LIMIT OFFSET that loses data.
Running : mysql  Ver 8.0.33-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

Based on above dataset, the issue happens when applying a limit offset to a sorted list.
While adding a limit, some results are changed, leading to wrong order, missing lines and duplicates.

See full scenario in attached file : full_scenario_with_base_query.txt

1/ Full list without LIMIT (= base query)

mysql> ... ;
+-----+--------------------------------------+----------+------------+--------+----------------+-----------------------+
| 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/ Exact same list filtered 0,10 = base query + limit 10 offset 0

mysql> ... 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)

Expected :
2 last lines should be raw ids 18 and 28 but they have been replaced by 3, 6
Raws 18 and 28 are missing in other pages
Raw 3 is present in offset 0 and 30
Raw 6 is presnet in offset 0 and 20

Regards,
Aurélien

Scenario with base query and results

Attachment: full_scenario_with_base_query.txt (text/plain), 25.36 KiB.

It happens also with Server version: 8.0.34-0ubuntu0.20.04.1 (Ubuntu)

If we look at this result set:

| 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 |
+-

we'll see that the created_at column is the same for all rows. This means that the actual result is not deterministic and varying the LIMIT and OFFSET values can give arbitrary results. Please try to add a secondary ordering column that makes the ordering deterministic. The "id" column can probably be used for this.

Correct. Adding the id to the ORDER clause solved the issue. Thank you.