Strange Sorting/Updating Problem

MySQL & MariaDB Other MySQL® Questions
1

Hello!

I’m using a mix of UPDATE and SELECT to pre-sort rows for a faster SELECT later on. This is my query:

SET @num = 0;UPDATE rel_artist_album LEFT JOIN ( SELECT raa.album_id, CASE WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.album_fullength = 1 AND a.is_movie = 0 AND a.is_live = 0 AND a.is_compilation = 0 AND a.is_misc = 0) THEN 1 WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.album_fullength = 0 AND a.is_movie = 0 AND a.is_live = 0 AND a.is_compilation = 0 AND a.is_misc = 0) THEN 2 WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.is_movie = 1) THEN 3 WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.is_live = 1) THEN 4 WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.is_compilation = 1) THEN 5 WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.is_misc = 1) THEN 6 WHEN (a.is_bootleg = 1) THEN 7 WHEN (a.album_fullength = 1 AND a.is_sampler = 1 AND a.is_bootleg = 0) THEN 8 END AS sort_col FROM rel_artist_album AS raa LEFT JOIN albums AS a ON raa.album_id = a.album_id WHERE raa.artist_id = 541 ORDER BY sort_col ASC, IF (a.is_sampler = 1, a.album_name, a.album_year) ASC, a.album_name ASC) AS oai ON oai.album_id = rel_artist_album.album_id SET rel_artist_album.album_order = @num := @num +1 WHERE rel_artist_album.album_id = oai.album_id AND rel_artist_album.artist_id = 541;

In most cases everything will be sorted right. But sometimes, if there are only very few rows to sort, the sorting goes wrong. If I take only the inner SELECT the value generated by the CASE is okay, but the UPDATE sorts the rows the other way.

Lets say, I have three rows and the inner SELECT sorts them like this (ID - sort_col value):

  1. 7825 - 1
  2. 7826 - 1
  3. 7824 - 2

But after the UPDATE the rows are ordered like this:

  1. 7824
  2. 7825
  3. 7826

Is there anyone who faced a similar problem?