mySQL query return changing based on 'LIMIT' useage | Database

Question

My website has a messaging system that uses PHP and a mySQL database to store and pull data.

I have a _CONVERSATION table and a _MESSAGE table. The former stores information about the inbox data associated with a conversation. The latter is weak to the former and stores individual message data including message send dates. When the user loads the messaging page, I want the inbox loaded in order of the most recent conversations had by the user. To accomplish this, I created a query that selects the distinct convoID from the _MESSAGE table that is ordered by the messageID (works just as well when ordered by the message date, same issue occurs).

When the user loads the messaging page, I use the discussed query to pull the last 10 conversations the user took part in. I use a limit for this, and a PDO while-fetch to load the data into an array.

This occurs as expected with no issues.

PDO SQL WITH LIMIT:

'SELECT distinct convoID from _MESSAGE where receiverID = :userID or senderID = :userID order by messageID desc limit '.(int)$ext[0].';'
/// userID = 40 (same every time for testing)
/// $ext[0] = 10
/// RESULT: 171, 8, 194, 193, 187, 178, 173, 157, 12, 151 (EXPECTED)

When the user scrolls down in their inbox, the websites loads more conversations beyond the first ten. I run the same query without the limit. I run the while-fetch until the last user that was loaded is found, then I load the next five items into the array that is returned. Once five new items are loaded, I break out of the PHP while loop.

This is where I noticed some strange behavior. I'm not getting expected results and some convoIDs are being repeated. I set up a echo $row['convoID'].', '; to get the full query.

PDO SQL WITHOUT LIMIT:

'SELECT distinct convoID from _MESSAGE where receiverID = :userID or senderID = :userID order by messageID desc;'
/// userID = 40 (same every time for testing)
/// Set up to echo five items after last convoID is found
/// RESULT: 194, 193, 187, 178, 173, 171, 157, 151, 136, 13, 3, 6, 8, 10 (UNEXPECTED)

SQL FROM mySQL WORKBENCH:

select distinct convoID from _MESSAGE where receiverID = 40 or senderID = 40 order by messageID desc; 
# RETURNS: 171,8,194,193,187,178,173,157,12,151,etc (EXPECTED)

This is the return I want and expect from this query.

This isn't the first time PDO has switched up on what I expected. I'm wondering what's causing this odd behavior and how can I get the expected results from the PDO query without using limits.

Am I writing something wrong in my query? If so, how come there is no issues when running the same exact query in mySQL workbench?

Here is the full php function for reference:

function get_convos($conn, $userID, $ext = ['10','']) {
  /// EXT: ['How many to pull','pull all convos after']
  $_rtn = [];
  $l = 0;
  $sql = null;
  if ($ext[1] == '') {
    $sql = $conn->prepare('SELECT distinct convoID from _MESSAGE where receiverID = :userID or senderID = :userID order by messageID desc limit '.(int)$ext[0].';');
    $sql->execute(['userID' => $userID]);
  } else {
    $sql = $conn->prepare('SELECT distinct convoID from _MESSAGE where receiverID = :userID or senderID = :userID order by messageID desc;');
    $sql->execute(['userID' => $userID]);
  }
  $ready = false;
  while ($row = $sql->fetch(PDO::FETCH_ASSOC)) {
    echo $row['convoID'].', ';
    $sql2 = $conn->prepare('SELECT * from _CONVERSATION where convoID = :convoID;');
    $sql2->execute(['convoID' => $row['convoID']]);
    if ($row2 = $sql2->fetch(PDO::FETCH_ASSOC)) {
      if ($ext[1] == '') {
        $_rtn[$l] = $row2;
        $l++;
      } else {
        if ($ready) {
          if ($l < (int)$ext[0]) {
            $_rtn[$l] = $row2;
            $l++;
          } else {
            break;
          }
        }
        if ($row2['convoID'] == $ext[1]) {
          $ready = true;
        }
      }
    }
  }
  return $_rtn;
}

Thanks for any help you guys can offer.

Answer 1

The problem is that ORDER BY messageID is processed after it selects a distinct message from each convoID group, and which row it selects is unpredictable. MySQL processes the query differently depending on whether there's a LIMIT clause, so you get different results.

You need to use a deterministic method to select the message within each conversation to order by, such as ordering by the most recent message. See SQL select only rows with max value on a column