MySQL – Duplicate Results When Sorting by 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()
AND n.expiration_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

ajax starts here since we have 10 per page

2 read priority 1

2 read priority > 1

---

The table structure is as follows.

notifications

+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| id                | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| type_id           | int(10) unsigned | NO   |     | NULL    |                |
| sticky            | int(10) unsigned | NO   |     | NULL    |                |
| priority          | int(10) unsigned | NO   |     | NULL    |                |
| esb_consultant_id | varchar(40)      | NO   |     |         |                |
| message_id        | varchar(100)     | NO   |     |         |                |
| esb_params        | varchar(255)     | YES  |     |         |                |
| creation_date     | datetime         | YES  |     | NULL    |                |
| expiration_date   | datetime         | YES  |     | NULL    |                |
+-------------------+------------------+------+-----+---------+----------------+

---

notification_user_role

+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| user_role_id    | varchar(150)     | NO   |     |         |       |
| notification_id | int(10) unsigned | NO   | MUL | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+

---

notification_status

+-------------------+------------------+------+-----+---------+-------+
| Field             | Type             | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+-------+
| esb_consultant_id | varchar(20)      | NO   |     |         |       |
| notification_id   | int(10) unsigned | NO   | MUL | NULL    |       |
+-------------------+------------------+------+-----+---------+-------+

---

The query I'm using to retrieve the results:

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 = :consultant_id 
  OR 
  n.esb_consultant_id = :role_all
)
AND nur.user_role_id = :consultant_role
AND n.creation_date <= NOW()
AND n.expiration_date >= NOW()
ORDER BY n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC
LIMIT $offset, $limit

$offset is the page times 10 – so if the page is 0(first page) the offset is 0, if the page is 1(first ajax call) the offset is 10 and so on

$limit is the limit, it's always 10.

:consultant_id is the user id – unique

:role_all is a simple string all. It is used for when certain notifications are for all roles(such as a birthday notification). All users have this notification, regardless of role since all of them have a birthday.

---

The problem:

Whenever I'm doing an ajax call I get certain notifications that are duplicated. I'll just post a screenshot of it since it's easier than drawing it.

Do note that the ajax itself is just part of the way I'm retrieving the results but is not responsible for the duplicates themselvs, I'm absolutely sure. It's not a display issue either, I've double and triple checked that.

Before Ajax

After ajax

What I've noticed is that if I were to remove this part

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

from the query. It works fine. No duplicates.

The dump of the query above, with limit removed and order by removed:

Dump

Sorry for the image but it's easier.

---

I'm using PHP to query the database.

  public function all($consultant_id, $consultant_role, $offset = 0) {
    $limit = 10;
    $offset = $offset * 10;

    $query = <<<SQL
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 = :consultant_id 
  OR 
  n.esb_consultant_id = :role_all
)
AND nur.user_role_id = :consultant_role
AND n.creation_date <= NOW()
AND n.expiration_date >= NOW()
ORDER BY n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC
LIMIT $offset, $limit
SQL;
    $return = $this->connection
      ->query($query
        , [
          ':consultant_role' => $consultant_role,
          ':consultant_id'   => $consultant_id,
          ':role_all'        => NotificationStatus::PL_N_ALL,
        ]
      )->fetchAll(\PDO::FETCH_ASSOC);

    foreach($return as $item) { // this is added simply for display purposes
      echo $item['id'] . '<br>';
    }

    return $return;
  }

The above is a copy+paste of the code used to retrieve the results. The function simply returns the results for displaying, no other magic going around.

The foreach is added to simply display the results in the browser.

Here is the image of the output. Notification 10 is duplicated.

Here is the exact same code only with
ORDER BY n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC removed. Limit and offset still apply here.

---

I'm not that good at mysql or sql in general and I'm not sure where the problem itself lies.

Any pointing in the right direction is greatly appreciated. Even workarounds or "hacks" I'm fine with.

---

mysql> SELECT * FROM notification_status WHERE notification_id = 10;
Empty set (0.00 sec)

mysql> SELECT * FROM notification_user_role WHERE notification_id = 10;
+--------------------------------+-----------------+
| user_role_id                   | notification_id |
+--------------------------------+-----------------+
| pl_sso_regional_vice_president |              10 |
+--------------------------------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM notifications WHERE id = 10;
+----+---------+--------+----------+-------------------+-------------------------------------+------------+---------------------+---------------------+
| id | type_id | sticky | priority | esb_consultant_id | message_id                          | esb_params | creation_date       | expiration_date     |
+----+---------+--------+----------+-------------------+-------------------------------------+------------+---------------------+---------------------+
| 10 |       2 |      0 |        4 | role              | pl_n_325f1676e8a263c86432edc7b9f09c | NULL       | 2018-02-01 00:00:00 | 2018-02-28 00:00:00 |
+----+---------+--------+----------+-------------------+-------------------------------------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

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()
  AND n.expiration_date >= NOW()
ORDER BY 
    n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC
LIMIT 
    $offset, $limit 

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

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

SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM TAB_EVENT e 
LEFT JOIN TAB_MESSAGE m ON e.id=m.event_id 
WHERE ( m.`read` IS NULL OR m.`read` = 0) 
GROUP BY e.id 
ORDER BY m.datetime DESC, e.id ASC 
LIMIT 10; 

SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM
(
    SELECT * FROM
    (SELECT id,event_id,datetime FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    ORDER BY datetime DESC) mm
    LIMIT 10
) m
LEFT JOIN TAB_EVENT e 
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC;

SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM
TAB_EVENT e LEFT JOIN
(
    SELECT * FROM
    (SELECT id,event_id,datetime FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    ORDER BY datetime DESC) mm
    LIMIT 10
) m
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC;

ALTER TABLE TAB_MESSAGE ADD INDEX read_datetime_ndx (read,datetime);