I have a cursor pagination implemented with MySQL (version 8.0), which works fine as long as there are no null values involved.
Here's my example data (id is a random UUID, date is date, time is time):
id | date | time
--------------------------
68 | 2017-10-28 | 22:00:00
d3 | 2017-11-03 | null
dd | 2017-11-03 | 21:45:00
62 | 2017-11-04 | 14:00:00
a1 | 2017-11-04 | 19:40:00
The cursor I use always consists of all three columns.
I use this query to get the next results (after the cursor):
SELECT * FROM table
WHERE (date > cursor.date)
OR (date = cursor.date AND time > cursor.time)
OR (date = cursor.date AND time = cursor.time AND id > cursor.id)
ORDER BY date ASC, time ASC, id ASC
And this query for prev results (before the cursor):
SELECT * FROM table
WHERE (date < cursor.date)
OR (date = cursor.date AND time < cursor.time)
OR (date = cursor.date AND time = cursor.time AND id < cursor.id)
ORDER BY date DESC, time DESC, id DESC
When using the prev query with cursor [id = dd, date = 2017-11-03, time = 21:45:00] it won't return the row with id = d3, because time is null, and this won't get selected by time < cursor.time.
Though I tried using time < cursor.time OR time IS NULL instead of time < cursor.time to include rows with null values. Which seems to fix this particular problem, but then creates a new problem: When using the prev query with cursor [id = d3, date = 2017-11-03, time = null], because now the result contains the row of the provided cursor.
I hope there's an easy solution for this. There seems to be no examples or tutorials on the web that deal with null values in cursor pagination.
Note: For the solution it doesn't matter if null will be sorted before or after non-null values, as long as it's consistent. (MySQL's default ordering is null < non-null)
Santiago Trujillo
I am not going to touch the topic of using cursors for pagination. There are alternatives, such as limit/offset.
But my recommendation for your queries is to use coalesce(), assigning a fake time for the comparison. MySQL makes this somewhat simple, because it supports time values in excess of 24 hours. And those would not be valid values for a date/time combination.
So:
SELECT *
FROM table
WHERE (date > cursor.date) OR
(date = cursor.date AND COALESCE(time, '24:00:00') > COALESCE(cursor.time, '24:00:00')) OR
(date = cursor.date AND COALESCE(time, '24:00:00') = COALESCE(cursor.time, '24:00:00') AND id > cursor.id)
ORDER BY date ASC, time ASC, id ASC
A more concise WHERE clause would be:
WHERE (date, COALESCE(time, '24:00:00'), id) > (cursor.date, COALESCE(cursor.time, '24:00:00'), cursor.id)
Add another column to the table. Make it a DATETIME. Combine date and time into it when not NULL; combine date with some particular time when NULL. Then your cursor has two columns to work with and no nulls.
If you have a reasonably recent version of MySQL, you can use a "generated stored" column, thereby avoiding any code changes.
And be sure to have INDEX(datetime, id).
If you are using MySQL 8.0 then you can consider to use row_number() window funciton create an unique sequential id (rn) for each row. Then just pass the rn for current row to get the previous rows.
Schema and insert statements:
create table cursortable( id varchar(10), date date, time time);
insert into cursortable values('68' , '2017-10-28' , '22:00:00');
insert into cursortable values('d3' , '2017-11-03' , null);
insert into cursortable values('dd' , '2017-11-03' , '21:45:00');
insert into cursortable values('62' , '2017-11-04' , '14:00:00');
insert into cursortable values('a1' , '2017-11-04' , '19:40:00');
Query to get the result for fist time:
select *,row_number()over(order by date,time,id)rn from cursortable
Output:
| id | date | time | rn |
|---|---|---|---|
| 68 | 2017-10-28 | 22:00:00 | 1 |
| d3 | 2017-11-03 | null | 2 |
| dd | 2017-11-03 | 21:45:00 | 3 |
| 62 | 2017-11-04 | 14:00:00 | 4 |
| a1 | 2017-11-04 | 19:40:00 | 5 |
Query to get the previous rows for cursor [id = dd, date = 2017-11-03, time = 21:45:00, rn=3] with only cursor [rn=3]:
with cte as
(
select *,row_number()over(order by date,time,id)rn from cursortable
)
select * from cte where rn<3
Output:
| id | date | time | rn |
|---|---|---|---|
| 68 | 2017-10-28 | 22:00:00 | 1 |
| d3 | 2017-11-03 | null | 2 |
db<>fiddle here
If you don't want to introduce an computed column into your code then please try the below solution considering all three columns cursor [id = dd, date = 2017-11-03, time = 21:45:00]
Query:
with cte as
(
select *,row_number()over(order by date,time,id)rn from cursortable
)
,cte2 as
(
select * from cte where id='dd' and date= '2017-11-03' and time= '21:45:00'
)
select cte.id,cte.date,cte.time from cte inner join cte2 on cte.rn<cte2.rn
Output:
| id | date | time |
|---|---|---|
| 68 | 2017-10-28 | 22:00:00 |
| d3 | 2017-11-03 | null |
db<>fiddle here
Your code would be like below:
with cte as
(
select *,row_number()over(order by date,time,id)rn from cursortable
)
,cte2 as
(
select * from cte where id=cursor.id and date= cursor.date and time= cursor.time
)
select cte.id,cte.date,cte.time from cte inner join cte2 on cte.rn<cte2.rn