[MDEV-27431] Datetime order by and limit offset not working Created: 2022-01-06  Updated: 2022-01-07  Resolved: 2022-01-07

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.5.12, 10.7.1
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Geoffrey Saleur Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

from docker image : docker run --rm -e MYSQL_ROOT_PASSWORD=azerty -e TZ=Europe\Paris -it mariadb:10.7.1

Attachments: PNG File 2022-01-06_23-49.png     PNG File 2022-01-06_23-57.png    

 Description   

Example :

CREATE TABLE test (a VARCHAR(255), b datetime);
 
insert into test values('1', '2021-11-03 11:21:21');
 
insert into test values('2', '2021-11-03 11:22:21');
 
insert into test values('3', '2021-11-04');
 
insert into test values('5', '2021-11-05');
 
insert into test values('6', '2040-01-01 10:10');
 
 
 
MariaDB [toto]> select * from test;
 
+------+---------------------+
 
| a    | b                   |
 
+------+---------------------+
 
| 1    | 2021-11-03 11:21:21 |
 
| 2    | 2021-11-03 11:22:21 |
 
| 3    | 2021-11-04 00:00:00 |
 
| 5    | 2021-11-05 00:00:00 |
 
| 6    | 2040-01-01 10:10:00 |
 
+------+---------------------+
 
5 rows in set (0.000 sec)
 
 
 
MariaDB [toto]> select * from test order by b;
 
+------+---------------------+
 
| a    | b                   |
 
+------+---------------------+
 
| 1    | 2021-11-03 11:21:21 |
 
| 2    | 2021-11-03 11:22:21 |
 
| 3    | 2021-11-04 00:00:00 |
 
| 5    | 2021-11-05 00:00:00 |
 
| 6    | 2040-01-01 10:10:00 |
 
+------+---------------------+
 
5 rows in set (0.000 sec)
 
 
 
MariaDB [toto]> select * from test order by b limit 3 offset 0;
 
+------+---------------------+
 
| a    | b                   |
 
+------+---------------------+
 
| 1    | 2021-11-03 11:21:21 |
 
| 2    | 2021-11-03 11:22:21 |
 
| 3    | 2021-11-04 00:00:00 |
 
+------+---------------------+
 
3 rows in set (0.000 sec)
 
 
 
MariaDB [toto]> select * from test order by b limit 3 offset 1;
 
+------+---------------------+
 
| a    | b                   |
 
+------+---------------------+
 
| 2    | 2021-11-03 11:22:21 | <= ERROR
 
| 3    | 2021-11-04 00:00:00 | <= ERROR
 
| 5    | 2021-11-05 00:00:00 |
 
+------+---------------------+
 
3 rows in set (0.000 sec)
 
 
 
MariaDB [toto]> select * from test order by b DESC;
 
+------+---------------------+
 
| a    | b                   |
 
+------+---------------------+
 
| 6    | 2040-01-01 10:10:00 |
 
| 5    | 2021-11-05 00:00:00 |
 
| 3    | 2021-11-04 00:00:00 |
 
| 2    | 2021-11-03 11:22:21 |
 
| 1    | 2021-11-03 11:21:21 |
 
+------+---------------------+
 
5 rows in set (0.000 sec)
 
 
 
MariaDB [toto]> select * from test order by b DESC limit 3 offset 0;
 
+------+---------------------+
 
| a    | b                   |
 
+------+---------------------+
 
| 6    | 2040-01-01 10:10:00 |
 
| 5    | 2021-11-05 00:00:00 |
 
| 3    | 2021-11-04 00:00:00 |
 
+------+---------------------+
 
3 rows in set (0.000 sec)
 
 
 
MariaDB [toto]> select * from test order by b DESC limit 3 offset 1;
 
+------+---------------------+
 
| a    | b                   |
 
+------+---------------------+
 
| 5    | 2021-11-05 00:00:00 |  <= ERROR
 
| 3    | 2021-11-04 00:00:00 |  <= ERROR
 
| 2    | 2021-11-03 11:22:21 |
 
+------+---------------------+
 
3 rows in set (0.001 sec)



 Comments   
Comment by Sergei Golubchik [ 2022-01-06 ]

What is the error there? What result do you expect those queries to return?

Comment by Geoffrey Saleur [ 2022-01-06 ]

For this request : select * from test order by b limit 3 offset 1
I am waiting for the results of box 2 :

But the command return this :

MariaDB [toto]> select * from test order by b limit 3 offset 1;
 
+------+---------------------+
 
| a    | b                   |
 
+------+---------------------+
 
| 2    | 2021-11-03 11:22:21 | <= ERROR
 
| 3    | 2021-11-04 00:00:00 | <= ERROR
 
| 5    | 2021-11-05 00:00:00 |
 
+------+---------------------+
 
3 rows in set (0.000 sec)

If we reverse the order we have the same problem:
For this request : select * from test order by b DESC limit 3 offset 1;
I am waiting for the results of box 2 :

But the command return this :

MariaDB [toto]> select * from test order by b DESC limit 3 offset 1;
 
+------+---------------------+
 
| a    | b                   |
 
+------+---------------------+
 
| 5    | 2021-11-05 00:00:00 |  <= ERROR
 
| 3    | 2021-11-04 00:00:00 |  <= ERROR
 
| 2    | 2021-11-03 11:22:21 |
 
+------+---------------------+
 
3 rows in set (0.001 sec)

Comment by Sergei Golubchik [ 2022-01-07 ]

Thanks. Now I can see where a confusion is. OFFSET is specified in the number of rows. OFFSET 1 means "skip one row, start from the second one". For your desired results you should use LIMIT 3 OFFSET 3

Generated at Thu Feb 08 09:52:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.