Why doesn't MySQL sort properly when + grouping?

Question

MySQL

Good day.
There is a request, for example:

SELECT id, thread_id, device_id, body, date FROM message WHERE device_id = 12 GROUP BY thread_id ORDER BY date LIMIT 0, 50

thread_id - identifier of the conversation, with one or another person. device_id - device ID. At the same time, only this device_id = 12 is present in this plate at the moment.
This query should list messages for the specified device_id , group them by conversation and sort them by date.
If we remove the WHERE device_id = 12 condition , then the grouping and sorting work correctly.
But if the condition is present, then the output sorting by date is not correct. Why might the WHERE device_id = N
condition harm sorting?

Answer 1

I am posting an edited version of the answer.
The query speed, of course, dropped from 0.042 to 0.115 (everything without indexes so far), but still - this is the way out.

SELECT * FROM (SELECT * FROM message WHERE device_id=12 ORDER BY date DESC) AS msg WHERE device_id=12 GROUP BY thread_id ORDER BY date DESC LIMIT 0 , 50

Answer 2

Did the recipe from here help?
it's not about WHERE device_id = N but about simultaneous GROUP and ORDER BY, this is a frequent case

Answer 3

I may be confusing something - but why do you need GROUP BY here? Where is the aggregate expression? What does "group by conversation" mean? Maybe you need sorting by date and conversation at the same time?
Now it's clear, try this:

select thread_id, body, date from
    (select message.thread_id, max(message.date) as date from message where device_id = 12 group by thread_id) as last_message
  join
    message
  on message.thread_id = last_message.thread_id and message.date = last_message.date
limit 0, 50