An UPDATE runs unexpectedly on MySQL 5.7 whereas it runs as expected on prior versions

I use a column called sequence on tables where I have to re-sort blocks of rows into an arbitrarily order.

CREATE TABLE `tkmemberstage` (
  `ID` bigint(22) unsigned NOT NULL AUTO_INCREMENT,
  `tkmembershipID` bigint(22) unsigned NOT NULL DEFAULT '0',
  `sequence` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `tkmembershipID` (`tkmembershipID`)
)

insert into tkmemberstage set tkmembershipID = 1, sequence = 10;
insert into tkmemberstage set tkmembershipID = 1, sequence = 20;
insert into tkmemberstage set tkmembershipID = 1, sequence = 30;
insert into tkmemberstage set tkmembershipID = 1, sequence = 40;

A few years back I researched the following as a means of resequencing a block of rows in a single UPDATE

This works by creating an interim virtual table and then updating the entire block of rows in one transaction instead of having to write each row out programmatically.

The UPDATE statement goes like this:

update 
tkmemberstage 
join 
(select tkmemberstage.ID,
(@newSequence := @newSequence+10) as newSequence 
from 
tkmemberstage 
cross join 
(select @newSequence := 0) 
constructedTable 
where 
tkmemberstage.tkmembershipID = 1  
order by tkmemberstage.sequence) 
constructedTableReordered 
on tkmemberstage.ID=constructedTableReordered.ID 
set tkmemberstage.sequence=constructedTableReordered.newSequence;

I have copied it verbatim from a development server in order not to add any translation problems to the mix.

I currently have this running on a mix of RDS servers in the AWS cloud and on anything other than a 5.7 server it works as expected.

In troubleshooting this I tried to deconstruct the statement and try it in partial chunks.

Here is the dataset I'm working on:

mysql> select ID, sequence from tkmemberstage;
+----+----------+
| ID | sequence |
+----+----------+
|  1 |       10 |
|  2 |       20 |
|  3 |       30 |
|  4 |       40 |
+----+----------+
4 rows in set (0.00 sec)

I then perform an update to seq

The problem is likely caused by one of these - or both:

-
Lack of matching index for the query - on (tkmembershipID, sequence). Without this index (and any index), the optimizer will likely choose a table scan.

-
Optimization improvements on version 5.7. I've seen similar issues in other flavours (call me Maria) where an optimization improvement caused a query to use a different plan. Specifically, (after either version 5.3 or 5.5) MariaDB's optimizer knew that ORDER BY in subqueries is redundant, so it could be "optimized away".

If a similar thing happens here, and the plan used gets rid of the ORDER BY, that explains the results.

(And that may be considered a bug - as it changes your expected outcome when using variables. You can file a bug report to MySQL, with your situation and they may be able to fix it - or suggest a different solution/workaround).

In the mean time, I suggest you add the index mentioned above and check the plans again.

By the way, it seems like your query can be simplified to:

set @newSequence = 0 ; 

update tkmemberstage 
set sequence = (@newSequence := @newSequence + 10) 
where tkmembershipID = 1 
order by sequence ;

StackExchange Database Administrators Q#220841, answer score: 4