Duplicate results in mysql query when try to sort by with limit and offset

The fiddle and the query is here so it's easier to find, read on for the question itself.

SQL Fiddle if you guys wanna mess around with it

I'm unable to reproduce the issue using the fiddle.

Here is the query you can use in the fiddle

SELECT n.*, ns.notification_id AS is_read FROM notifications n
LEFT OUTER JOIN notification_status ns
ON n.id = ns.notification_id
LEFT JOIN notification_user_role nur
ON n.id = nur.notification_id
WHERE
(
  n.esb_consultant_id = 19291
  OR
  n.esb_consultant_id = 'role'
)
AND nur.user_role_id = 'pl_sso_regional_vice_president'
AND n.creation_date = NOW()
ORDER BY n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC
LIMIT 0, 10

I've also placed it lower in this post, but here it's easier to catch the eyes.

I'll try to keep this as brief as possible.

I'm working on a notification system. I have 3 tables described below.

I'm trying to get notifications with a LIMIT of 10, paginated, 10 per page(so an OFFSET of 10). I'm using ajax to load the next 10.

They are to be ordered by priority(from 1 to 6, 1 being displayed first, while 6th being displayed last).

All unread notifications must be displayed first(priority still applies), while read notifications must be displayed last(priority still applies).

Notifications are per role. A user can have several roles(thus the need for another table).

The notification_status table described below is used to keep track of which notifications are read.

Which ever notification is NOT in the notification_status table is NOT read. This is very important. I didn't make this decision. I just have to live with it.

To put it into the big picture lets have an example:

Assume we have 14 notifications:

5 of them will be priority 1, unread.

4 of them will be priority > 1, unread.

3 of them will be priority 1, read.

2 of them will be priority > 1, read.

The expected display order is the following.

5 unread priority 1

4 unread priority > 1

1 read priority 1

aj

Do you have notifications associated with 2 or more roles? That would explain the duplicate results.

From the schema we can infer that a notification could be associated with many statues and also with many roles. Therefore if a notification is associated with many - say 3 - roles, then every one of its statuses will appear many (3) times. The notifications that are associated with 1 role do not show any duplication.

We also notice that the SELECT list does not include any columns from the role table. That helps for a solution: convert the JOIN to table role to an EXISTS subquery. The query becomes:

SELECT 
    n.*, ns.notification_id AS is_read 
FROM 
    notifications n
    LEFT OUTER JOIN notification_status ns
    ON n.id = ns.notification_id
WHERE EXISTS
      ( SELECT 1
        FROM notification_user_role nur
        WHERE n.id = nur.notification_id 
          AND nur.user_role_id = :consultant_role
      )
  AND n.esb_consultant_id IN (:consultant_id, :role_all)
  AND n.creation_date = NOW()
ORDER BY 
    n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC
LIMIT 
    $offset, $limit

The other reason might be that you have many statuses for a notification. In that case, the solution depends on what you want to do about it. (i.e. what is means for a notification to have 2 or more statuses, should it appear once or twice in the results, etc).

(but that's not the problem in this case)

The third reason that you may get "duplicates" is when you don't have a definite (fully deterministic) ORDER BY clause. That is, when the ORDER BY cannot resolve all ties. In this case, when there are ties (say in positions 8 to 28), the LIMIT 10 OFFSET 0 means "get me 10 rows, order by that". But the "that" is only good enough to specify the first 7 rows. The last 8th, 9th and 10th are tied (with 18 more!) so MySQL decides and picks 3 of them arbitrarily. (Still no duplicates)

But in the next query, when you ask for rows 11th to 20 with OFFSET 10 LIMIT 10, MySQL resolves the ties in a different way and may give you the 3 rows that you had seen before again (now appearing as 11th, 14th and 15th!).

Why does it do that? Because it can and you haven't told her a definitive and full way to do the ORDER BY.

By the way, I wouldn't call these "duplicates" as they appear in different result sets / calls. The slightly misleading terminology was probably the reason the cause couldn't easily be identified.

The solution is easy however. Just add one more column (that is unique) in the ORDER BY clause:

ORDER BY 
    n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC,
    n.id    -- to resolve ties

(or, as the OP clarified in the discussion in comments and the chat room, a different order by was needed, still with the n.id in the end):

ORDER BY
    (is_read IS NULL) DESC, n.priority ASC, n.creation_date DESC,
    n.id DESC

StackExchange Database Administrators Q#198169, answer score: 15