Home Mysql select ordering different as subquery vs. query in 5.7.x
Reply: 0

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

user3266
1#
user3266 Published in April 20, 2018, 5:59 pm

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?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.315877 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO