Order by is ignored if inside a derived table of a update (only in MYSQL 5.7)

Question

I have a update to fix positions values inside a table. The update worked fine in MYSQL 5.5, but after upgrade to MYSQL 5.7 it doesnt work anymore. Now, the "order by" inside the derived table is ignored. So, the rows are updateds based in primary key, not in order by.

May someone help me? Why the order by is ignored in MYSQL 5.7 and how to fixed it?

Thanks!!!

OLD POSITION VALUES

ImageId     Position
389         0
390         6
391         4
392         1

NEW POSITION VALUES, AFTER FIXED (MYSQL 5.5)

ImageId     Position
389         1
390         4
391         3
392         2

NEW POSITION VALUES, AFTER FIXED (MYSQL 5.7)

ImageId     Position
389         1
390         2
391         3
392         4

UPDATE

UPDATE  Image as t1
        INNER JOIN  (
                    SELECT  t.*, @rownum := @rownum + 1 AS newPosition
                    FROM    Image t, (SELECT @rownum := 0) r
                    ORDER   BY t.Position
                    ) as td
            ON t1.ImageId = td.ImageId SET     t1.Position= td.newPosition

Answer 1

A derived table in SQL is a set, and a set by definition has no row order. Your attempt to use an imperative calculation (@rownum := @rownum + 1) within a set was wrong to begin with, and while it may have worked for a while, as you now realized, there is no guarantee for that. Most database engines will (correctly) raise an error if you try to use a similar query.

The need that you are describing was addressed with window ranking functions which are supported as of MySQL 8. If you can upgrade to that, your solution will be easy and elegant with ROW_NUMBER () OVER(PARTITION BY ImageID ORDER BY Position).

If you can't use version 8, your only guaranteed solution is to use a correlated subquery, something along the lines of:

Disclaimer - Since you didn't provide DDL and sample data, the solution below isn't tested

UPDATE  Image as t1
SET     Position = (
                    SELECT COUNT(*) 
                    FROM Image AS t2 
                    WHERE t1.ImageID = t2.ImageID 
                          AND 
                          t1.Position <= t2.position
                    );

Note that for large tables, this solution may not perform well, but it is guaranteed to work regardless of version as it follows SQL set rules.

This is valid ANSI SQL syntax. MySQL specifically may require some adjustments.

Answer 2

Server knows that ORDER BY in subquery makes no sense, and removes it. It needs too deep analysing for to understand that the ordering makes sense due to variables usage.

You can verify this by adding LIMIT N to your subquery, where N is less than records count...

The simplest solution is in temporarily define Position as PRIMARY KEY, re-enumerate records, then restore PK. But this needs the values in Position to be unique and not NULL.