CODEWITHSUNDEEP
mysqljoinsql-update
kasdega
kasdega

Reputation: 18786

Mysql select ordering different as subquery vs. query in 5.7.x

Let me preface this with by saying that what I'm describing here was working just fine in Mysql 5.6.x and lower. Now we're trying to update to mysql 5.7 and finding an issue with this UPDATE statement.

We have a table that looks like this:

imagefile
----------
id      int
itemId  int
...
sequenceNumber int
updatedDate    date

In our application it is necessary for users to reorder imageFiles within the Item. Our SQL to do that looks like this:

UPDATE imageFile f1 
  JOIN ( 
        SELECT f2.id, (@row:=@row+1) rowNum 
          FROM imageFile f2, (SELECT @row:=0) dm 
         WHERE f2.itemId = 8035323
         ORDER BY f2.sequenceNumber, f2.updatedDate 
        ) rs ON f1.id = rs.id 
SET f1.sequenceNumber = rs.rowNum;

If I run just the select part as a stand-alone query it produces this:

SELECT f2.id, f2.sequenceNumber, sqNum (@row:=@row+1) rowNum, updatedDate 
  FROM imageFile f2, (SELECT @row:=0) dm 
 WHERE f2.itemId = 8035323
 ORDER BY f2.sequenceNumber, f2.updatedDate;

 id   sqNum   rowNum   updatedDate
| 9 |   1   |   1   |2018-04-16 18:39:12
| 8 |   2   |   2   |2018-04-16 18:38:42
| 7 |   3   |   3   |2018-04-16 18:37:03
| 6 |   4   |   4   |2018-04-16 18:37:28
| 5 |   5   |   5   |2018-04-16 18:36:37
| 4 |   6   |   6   |2018-04-16 18:38:16
| 3 |   7   |   7   |2017-09-12 16:59:20

Which is correct and exactly what I expect... After I run the UPDATE with the SELECT as the subquery/join I get this:

 id   sqNum  rowNum   updatedDate
| 3 |   1   |   1   |2017-09-12 16:59:20
| 4 |   2   |   2   |2018-04-16 18:38:16
| 5 |   3   |   3   |2018-04-16 18:36:37
| 6 |   4   |   4   |2018-04-16 18:37:28
| 7 |   5   |   5   |2018-04-16 18:37:03
| 8 |   6   |   6   |2018-04-16 18:38:42
| 9 |   7   |   7   |2018-04-16 18:39:12

Which is completely wrong and doesn't seem to follow any order by that I told it to. Obviously something changed in the new version of mysql. When I do the EXPLAIN for that query in each version it's different as well...

5.6.34

id  select_type table   type    possible keys   key key length  ref rows    extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    35  NULL
1   PRIMARY i1  eq_ref  PRIMARY PRIMARY 4   rs.id   1   NULL
2   DERIVED <derived3>  system  NULL    NULL    NULL    NULL    1   Using filesort
2   DERIVED i2  ref uc_ItemNumber, in_workspaceId   uc_ItemNumber   4   const   35  Using where
3   DERIVED NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used

5.7.18 

id  select_type table   type    possible_keys   key key_len ref rows    extra
1   PRIMARY <derived2>  ALL                 35  
1   UPDATE  i1  eq_ref  PRIMARY PRIMARY 4   rs.id   1   
2   DERIVED i2  ref uc_ItemNumber, in_workspaceId   uc_ItemNumber   4   const   35  Using temporary; Using filesort
2   DERIVED <derived3>  ALL                 1   Using join buffer (Block Nested Loop)
3   DERIVED                             No tables used

My question is how do I get this to go back to the previous/desired behavior?

Upvotes: 0

Views: 41

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522386

I seem to recall that session variables should not be used/relied upon for MySQL DML statements. But the good news is that we can rewrite your query without session variables:

UPDATE imageFile f1 
INNER JOIN
( 
    SELECT
        f2.id, 
        (SELECT COUNT(*) 
         FROM imageFile t
     WHERE t.itemId = 8035323 AND
           (t.sequenceNumber < f2.sequenceNumber OR
            t.sequenceNumber = f2.sequenceNumber AND
            (t.updatedDate < f2.updatedDate OR
            (t.updatedDate = f2.updatedDate AND t.id < f2.id)))) rowNum
    FROM imageFile f2
    WHERE f2.itemId = 8035323
) rs
    ON f1.id = rs.id 
SET f1.sequenceNumber = rs.rowNum;

Upvotes: 1

Related Questions