Bug #116366 Pagination order by a datetime column with same value display duplicate rows

• Submitted: 16 Oct 2024 8:00
• Modified: 16 Oct 2024 9:01
• Reporter: Aiden Zang
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.39
• OS: Any
• CPU Architecture: Any

[16 Oct 2024 8:00] Aiden Zang

Description:
Table:
CREATE TABLE `table_a` (
  `number` bigint not null,
  `id` varchar(32) NOT NULL DEFAULT '',
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`number`,`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4  COLLATE=utf8mb4_general_ci;

Query:
SELECT *
FROM table_a u
WHERE number = 93216519897
ORDER BY create_time desc limit x,x ;

If create_time is same,in mysql 5.7 it will sort by the PRIMARY KEY (`number`,`id`) and each page rows is not repeat. But in MySQL 8.0.39 it will not sort by PRIMARY KEY, and some row in page 15,15 will duplicate with 0,15

SELECT *
FROM table_a u
WHERE number = 93216519897
ORDER BY create_time desc limit 0,15 ;
| number      | id                     | create_time         |
|-------------|------------------------|---------------------|
| 93216519897 | 0HnzZbspSB2Idv3_0QahGQ | 2024-10-09 08:25:14 |
| 93216519897 | 0TlNIMUvSpumNdP__N9Iww | 2024-10-09 08:25:14 |
| 93216519897 | 0yttS9-PQziXn-CLMZ_I3w | 2024-10-09 08:25:14 |
| 93216519897 | 1guMhHd2Tpi7fiEf-OIU5w | 2024-10-09 08:25:14 |
| 93216519897 | 2iRRV3q6TPyTZhYjFEC8Pw | 2024-10-09 08:25:14 |
| 93216519897 | 2vMcnPFURdSHbVwFh-v5EQ | 2024-10-09 08:25:14 |
| 93216519897 | 2wY5A9sVTo29GV5tp5siZg | 2024-10-09 08:25:14 |
| 93216519897 | 2_F3MU83SC29wl_9Y116sg | 2024-10-09 08:25:14 |
| 93216519897 | 3SxCG68DQ6Kfd15JxijTrg | 2024-10-09 08:25:14 |
| 93216519897 | 3_BMM2XBRe-cLxHTfG185Q | 2024-10-09 08:25:14 |
| 93216519897 | 4aYzulUqToWbClO8UJXVHg | 2024-10-09 08:25:14 |
| 93216519897 | 4B_KZSh3SZ6nw5Oxr8Fp-w | 2024-10-09 08:25:14 |
| 93216519897 | 4jq25likQGyCnubyIPX0lA | 2024-10-09 08:25:14 |
| 93216519897 | 5LBDRgJbR0G-osGbngfeUQ | 2024-10-09 08:25:14 |
| 93216519897 | 6PARSwVFTFeRYv7j5LaCuQ | 2024-10-09 08:25:14 |

SELECT *
FROM table_a u
WHERE number = 93216519897
ORDER BY create_time desc limit 15,15 ;
| number      | id                     | create_time         |
|-------------|------------------------|---------------------|
| 93216519897 | 8oVRauozQCWMOaERvwajdA | 2024-10-09 08:25:14 |
| 93216519897 | 0HnzZbspSB2Idv3_0QahGQ | 2024-10-09 08:25:14 |
| 93216519897 | 6PARSwVFTFeRYv7j5LaCuQ | 2024-10-09 08:25:14 |
| 93216519897 | 5LBDRgJbR0G-osGbngfeUQ | 2024-10-09 08:25:14 |
| 93216519897 | 4jq25likQGyCnubyIPX0lA | 2024-10-09 08:25:14 |
| 93216519897 | 4B_KZSh3SZ6nw5Oxr8Fp-w | 2024-10-09 08:25:14 |
| 93216519897 | 4aYzulUqToWbClO8UJXVHg | 2024-10-09 08:25:14 |
| 93216519897 | 3_BMM2XBRe-cLxHTfG185Q | 2024-10-09 08:25:14 |
| 93216519897 | 3SxCG68DQ6Kfd15JxijTrg | 2024-10-09 08:25:14 |
| 93216519897 | 2_F3MU83SC29wl_9Y116sg | 2024-10-09 08:25:14 |
| 93216519897 | 2wY5A9sVTo29GV5tp5siZg | 2024-10-09 08:25:14 |
| 93216519897 | 2vMcnPFURdSHbVwFh-v5EQ | 2024-10-09 08:25:14 |
| 93216519897 | 2iRRV3q6TPyTZhYjFEC8Pw | 2024-10-09 08:25:14 |
| 93216519897 | 1guMhHd2Tpi7fiEf-OIU5w | 2024-10-09 08:25:14 |
| 93216519897 | 0yttS9-PQziXn-CLMZ_I3w | 2024-10-09 08:25:14 |

below id appear twice in different pages:
0yttS9-PQziXn-CLMZ_I3w
1guMhHd2Tpi7fiEf-OIU5w
2iRRV3q6TPyTZhYjFEC8Pw
2vMcnPFURdSHbVwFh-v5EQ
2wY5A9sVTo29GV5tp5siZg
2_F3MU83SC29wl_9Y116sg
3SxCG68DQ6Kfd15JxijTrg
3_BMM2XBRe-cLxHTfG185Q
4jq25likQGyCnubyIPX0lA
4B_KZSh3SZ6nw5Oxr8Fp-w
4aYzulUqToWbClO8UJXVHg
5LBDRgJbR0G-osGbngfeUQ
6PARSwVFTFeRYv7j5LaCuQ

How to repeat:
CREATE TABLE `table_b` (
  `number` bigint not null,
  `id` varchar(32) NOT NULL DEFAULT '',
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`number`,`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4  COLLATE=utf8mb4_general_ci;

INSERT INTO `table_a` (`number`, `id`, `create_time`) VALUES
(93216519897, '0HnzZbspSB2Idv3_0QahGQ', '2024-10-09 08:25:14'),
(93216519897, '0TlNIMUvSpumNdP__N9Iww', '2024-10-09 08:25:14'),
(93216519897, '0yttS9-PQziXn-CLMZ_I3w', '2024-10-09 08:25:14'),
(93216519897, '1guMhHd2Tpi7fiEf-OIU5w', '2024-10-09 08:25:14'),
(93216519897, '2iRRV3q6TPyTZhYjFEC8Pw', '2024-10-09 08:25:14'),
(93216519897, '2vMcnPFURdSHbVwFh-v5EQ', '2024-10-09 08:25:14'),
(93216519897, '2wY5A9sVTo29GV5tp5siZg', '2024-10-09 08:25:14'),
(93216519897, '2_F3MU83SC29wl_9Y116sg', '2024-10-09 08:25:14'),
(93216519897, '3SxCG68DQ6Kfd15JxijTrg', '2024-10-09 08:25:14'),
(93216519897, '3_BMM2XBRe-cLxHTfG185Q', '2024-10-09 08:25:14'),
(93216519897, '4aYzulUqToWbClO8UJXVHg', '2024-10-09 08:25:14'),
(93216519897, '4B_KZSh3SZ6nw5Oxr8Fp-w', '2024-10-09 08:25:14'),
(93216519897, '4jq25likQGyCnubyIPX0lA', '2024-10-09 08:25:14'),
(93216519897, '5LBDRgJbR0G-osGbngfeUQ', '2024-10-09 08:25:14'),
(93216519897, '6PARSwVFTFeRYv7j5LaCuQ', '2024-10-09 08:25:14'),
(93216519897, '7UXTlM79Q3ea-ljlJzn9Kw', '2024-10-09 08:25:14'),
(93216519897, '8oVRauozQCWMOaERvwajdA', '2024-10-09 08:25:14'),
(93216519897, 'acdz6bfcQ86zY7y5zZ7uzw', '2024-10-09 08:25:14'),
(93216519897, 'Axb_7XeNSLG2wK3A9ZpNQA', '2024-10-09 08:25:14'),
(93216519897, 'ay7bWj12RRa4_B2ElbEAJw', '2024-10-09 08:25:14'),
(93216519897, 'bprL5X1GQlmAXCFOdIF4nw', '2024-10-09 08:25:14'),
(93216519897, 'BQwFCMeuR_CmbARAuKI0RA', '2024-10-09 08:25:14'),
(93216519897, 'BT70PYApTPulTpE61XSw5w', '2024-10-09 08:25:14'),
(93216519897, 'C16TZ9KFRH2BomP9taQjkQ', '2024-10-09 08:25:14'),
(93216519897, 'cdWF7MQbSo-v-cBKfoLyWw', '2024-10-09 08:25:14'),
(93216519897, 'CVDleGuIR--_T7wAq42sNA', '2024-10-09 08:25:14'),
(93216519897, 'dQ3L2gwgQaeuqM3cZsqTjQ', '2024-10-09 08:25:14'),
(93216519897, 'Ec3FQUbRQhaSA3rmq3nXoQ', '2024-10-09 08:25:14'),
(93216519897, 'efB353VLSKuF8xPlHwrKkg', '2024-10-09 08:25:14'),
(93216519897, 'eIFHz3AxR1ysBtogWqKSXQ', '2024-10-09 08:25:14'),
(93216519897, 'fR2DehSjSz6q6uM-SxSjpg', '2024-10-09 08:25:14'),
(93216519897, 'Fu0HPrbXTmeX9-kwwWy97Q', '2024-10-09 08:25:14'),
(93216519897, 'gAkiOF9PSFy3CqX0klOgcA', '2024-10-09 08:25:14'),
(93216519897, 'GBmAFwzpR26tXnTL2OoaCg', '2024-10-09 08:25:14'),
(93216519897, 'GBvU661-RjKkldPz0W-DXQ', '2024-10-09 08:25:14'),
(93216519897, 'GjbLjDD-TmeVa5_DvDey5A', '2024-10-09 08:25:14'),
(93216519897, 'gT6uhl75SgG1CHq7GqcLwg', '2024-10-09 08:25:14'),
(93216519897, 'gTNq9A4GT4Cc_m2_zn4Agw', '2024-10-09 08:25:14'),
(93216519897, 'hsqxS8kPR3C9zyKk7r65Cw', '2024-10-09 08:25:14'),
(93216519897, 'HysBnWkmR3SQJ58zCEZTOw', '2024-10-09 08:25:14'),
(93216519897, 'hzw0NphOTVKfQF8-KLunXg', '2024-10-09 08:25:14'),
(93216519897, 'i3taxMx9S7aZTy8XjsX1oA', '2024-10-09 08:25:14'),
(93216519897, 'IhflEwhlTa6fTG5xB7yh4g', '2024-10-09 08:25:14'),
(93216519897, 'iHZAwcKVSoCWkKSBlCBY2w', '2024-10-09 08:25:14'),
(93216519897, 'ii8rL7X6TAGGwMBlyQKvZQ', '2024-10-09 08:25:14'),
(93216519897, 'jX0bB6TlS6WRtSG8Aj-Rkw', '2024-10-09 08:25:14'),
(93216519897, 'KPTGqoC-S4GIeDjkvI8Ubw', '2024-10-09 08:25:14'),
(93216519897, 'lG0D1EJ0Rpaq-OaEag4THQ', '2024-10-09 08:25:14'),
(93216519897, 'Lool9qKUS4e2IpsRbmXoZw', '2024-10-09 08:25:14'),
(93216519897, 'LybyI_6HQWO6lzbmNyjatg', '2024-10-09 08:25:14'),
(93216519897, 'mXEVGQtiTLalVBHpIIkioQ', '2024-10-09 08:25:14'),
(93216519897, 'N0lsEQYASvy1W_c1Ec4OOA', '2024-10-09 08:25:14'),
(93216519897, 'NaZk3zYDRG6YygUbOYuckQ', '2024-10-09 08:25:14'),
(93216519897, 'Nbb880l-RRuIHUVX8KLZnA', '2024-10-09 08:25:14'),
(93216519897, 'NIbO9GBYSUa0VywarBU0Ug', '2024-10-09 08:25:14'),
(93216519897, 'NRB9_xwLRzmMHr8XiMW44w', '2024-10-09 08:25:14'),
(93216519897, 'nYefkCvWRzmxbRW87wqgAg', '2024-10-09 08:25:14'),
(93216519897, 'O5nS8nOMSaOO7dnswg_teQ', '2024-10-09 08:25:14'),
(93216519897, 'oD-tDgy8SOiFDzZd0jtr3w', '2024-10-09 08:25:14'),
(93216519897, 'OIJivnTdQfSPPxtNus_1PA', '2024-10-09 08:25:14'),
(93216519897, 'ORan1NdnR6efB-_1bOWgTg', '2024-10-09 08:25:14'),
(93216519897, 'oWrsM44lRsuvfYYCBB4lwQ', '2024-10-09 08:25:14'),
(93216519897, 'oyDgPBWrQHy7YTy7tmhmpQ', '2024-10-09 08:25:14'),
(93216519897, 'pC2r8P6ZS-GSYNaxslz78g', '2024-10-09 08:25:14'),
(93216519897, 'PgRAqCkhSYuBYopeiFPnEg', '2024-10-09 08:25:14'),
(93216519897, 'qbNiwNL3Q3u61oIsogJ02A', '2024-10-09 08:25:14'),
(93216519897, 'qCaJQw5yQbiQuzdJnpywcQ', '2024-10-09 08:25:14'),
(93216519897, 'qFu4T8drQRK6n2QlCyiPJA', '2024-10-09 08:25:14'),
(93216519897, 'R26G_nvBRO6sFt7CwZJ1aQ', '2024-10-09 08:25:14'),
(93216519897, 'rrOyi3FvQl2OeHD7FqLqPA', '2024-10-09 08:25:14'),
(93216519897, 'S1Jt9iF4RcCotVtVDLgZoA', '2024-10-09 08:25:14'),
(93216519897, 'SL0GN6hZQ4ekvOLedFariw', '2024-10-09 08:25:14'),
(93216519897, 'sXAjmeMuRiu2jwLl8QshFw', '2024-10-09 08:25:14'),
(93216519897, 's_FzrnxnRze1GvZKh8s6HQ', '2024-10-09 08:25:14'),
(93216519897, 'tCnZOZv4QNa9EiQPmmjypg', '2024-10-09 08:25:14'),
(93216519897, 'tMW5BNlNTT-6nEr8e9KVlQ', '2024-10-09 08:25:14'),
(93216519897, 'tnxiheouR7yqlnyps8hlkQ', '2024-10-09 08:25:14'),
(93216519897, 'TY3Fkv8YRXyydS7VHH2LyQ', '2024-10-09 08:25:14'),
(93216519897, 'TzAUdd9yQzGcfIRPjlEWHw', '2024-10-09 08:25:14'),
(93216519897, 'UnSkwzVTTJy0vve2-TKr8Q', '2024-10-09 08:25:14'),
(93216519897, 'Vh2v0eI6QCWyFt_iXcXlwg', '2024-10-09 08:25:14'),
(93216519897, 'vPZ9x9iASgquOg6SQMb5jA', '2024-10-09 08:25:14'),
(93216519897, 'vxdkxdSZR8uWUxv4d_4cNQ', '2024-10-09 08:25:14'),
(93216519897, 'wRRP5u5NTjWGzoGheB84ag', '2024-10-09 08:25:14'),
(93216519897, 'x1vbkXcpSGW6mp3wLcVEGw', '2024-10-09 08:25:14'),
(93216519897, 'X20_K2wYR86rp2cGi3S8Xg', '2024-10-09 08:25:14'),
(93216519897, 'xeh5hTwER-m_KLevSYa9Ww', '2024-10-09 08:25:14'),
(93216519897, 'XFIHtET4Sranm68m4GLmOg', '2024-10-09 08:25:14'),
(93216519897, 'XM71GeT5QB6nzVs3qw0h6w', '2024-10-09 08:25:14'),
(93216519897, 'xqTwbJfHShe4nzSBhIeqGw', '2024-10-09 08:25:14'),
(93216519897, 'xvKcLnOMSC6MAUb2Bh7kqg', '2024-10-09 08:25:14'),
(93216519897, 'y5TdK4hHS1K3mOG882LQLw', '2024-10-09 08:25:14'),
(93216519897, 'yMMyysvIQX-uJCTiKkRIbA', '2024-10-09 08:25:14'),
(93216519897, 'ytPOw2uCRMO8V9q7qd3y1Q', '2024-10-09 08:25:14'),
(93216519897, 'Y_MvObmuQWy7c6PcA13KjA', '2024-10-09 08:25:14'),
(93216519897, 'z8RILShyQpG5ZldUwoMnTw', '2024-10-09 08:25:14'),
(93216519897, 'ZRHVan5yR-OnKGutGZWiBA', '2024-10-09 08:25:14'),
(93216519897, '_hfAMYHYTsaqRQ6t01H7JQ', '2024-10-09 08:25:14'),
(93216519897, '_SkvsenpTbejO__57oBnnw', '2024-10-09 08:25:14');

select query1:
SELECT *
FROM table_a u
WHERE number = 93216519897
ORDER BY create_time desc limit 0,15 ;

select query2:
SELECT *
FROM table_a u
WHERE number = 93216519897
ORDER BY create_time desc limit 15,15 ;
 
select query1 and query2 data is duplicated

 

Suggested fix:
if the value of order by column is same, it shoud order by the PRIMARY KEY like mysql 5.7

[16 Oct 2024 9:01] MySQL Verification Team

HI Mr. Zang,

Thank you for your bug report.

However, this is not a bug.

Simply, MySQL 8.0 has much better optimiser then the one that version 5.7 had.

Your search criteria are all done on the column that has all identical values.

Your sorting criteria are also all done on the column that has all identical values.

Hence, there is no reason to use PRIMARY index.

If you want to have a proper pagination, you have to include a column `id` in the ordering of the results. That is all according to the SQL Standard, in the Foundation volume, chapter 9.14.

Not a bug.