CODEWITHSUNDEEP
mysqljoinleft-join
Tony Dinh
Tony Dinh

Reputation: 6726

Different MySQL versions returns different result when have LEFT JOIN and ORDER in subquery

I have this query

SELECT r.id, r.name, r.code, rv.votes
FROM tbl_rhythms AS r
LEFT JOIN
  (SELECT rhythm_id, votes
   FROM tbl_songs_rhythms
   WHERE song_id = 1
   ORDER BY votes DESC) AS rv
ON r.id = rv.rhythm_id

...which returns this result when executed in MySQL 5.6.20

3,Blues,blues,         2
4,Bollero,bollero,     1
1,Ballad,ballad,       NULL
2,Slow,slow,           NULL
5,Slow rocj,slowrock,  NULL
6,Disco,disco,         NULL

but when I execute the same query in MySQL 5.5.40-0ubuntu0.14.04.1

1,Ballad,ballad,       NULL
2,Slow,slow,           NULL
3,Blues,blues,         2
4,Bollero,bollero,     1
5,Slow rock,slowrock,  NULL
6,Disco,disco,         NULL

As you can see, the order is messed up.

Is it a bug of MySQL 5.5.40?

Upvotes: 0

Views: 344

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Your question has different results, but then you add "the order is messed up."

This is obviously true because the outer query has no order by. And, it is the order by in the outermost select that determines the ordering. You need to put the order by there:

SELECT r.id, r.name, r.code, rv.votes
FROM tbl_rhythms r LEFT JOIN
     tbl_songs_rhythms rv
     ON r.id = rv.rhythm_id AND rv.song_id = 1
ORDER BY rv.votes DESC;

This also should make the query more efficient, because it is not materializing a subquery and it can use an in dex on tbl_songs_rhythms(rhythm_id) for the join.

As for why balad,balad would get NULL in one case but 1 in the other -- I think that would be a data issue and has nothing to do with the structure of this query.

Upvotes: 1

Related Questions