| Bug #84256 | ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY | ||
|---|---|---|---|
| Submitted: | 19 Dec 2016 8:27 | Modified: | 23 Dec 2016 8:16 |
| Reporter: | Vladimír Jilemnický | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.7/8.0 | OS: | Windows (w10 CZECH NATIONALISATION) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | join, order by, UPDATE, VIEW | ||
[19 Dec 2016 21:10]
MySQL Verification Team
Thank you for the bug report. Verified as described.
[21 Dec 2016 8:55]
Roy Lyseng
Hi Vladimir, this is not a bug in 5.7. Actually, it was a bug in 5.6 that was fixed by a refactoring worklog in 5.7. MySQL does not allow ORDER BY together with a multi-table update operation. And when you UPDATE a view that is defined as a join operation, it effectively becomes a multi-table UPDATE. In 5.6, the ORDER BY was simply ignored, but in 5.7, the check for ORDER BY and UPDATE is performed after expansion of views into tables, so a proper error message is given. You can see this by expanding the view into the UPDATE statement, getting this query: UPDATE Test04 INNER JOIN Test02 ON Test04.ID=Test02.ID SET Error = Error | 1 WHERE NewRow > 0 and E0 > 0 ORDER BY ID, CasStamp; The query will fail in both 5.6 and 5.7. A reasonable workaround to your problem is to remove the ORDER BY clause from the view definition.
[25 Dec 2016 16:41]
Roy Lyseng
Hi Vladimir, I understand your concern about workarounds. However, the problem here is that we do support ORDER BY combined with UPDATE (and DELETE), but only for single-table operations. We do not support ORDER BY for multi-table UPDATE, it will give an error for "unsupported operation". Thus, it is also reasonable that UPDATE of a multi-table view issues the same error, anything else would be inconsistent with the non-view case. About UPDATE and ORDER BY: It does indeed make sense because it may force a certain order of access before performing the UPDATE, but the biggest use case is in combination with LIMIT to make a deterministic subset of rows for update. A little more on views and ORDER BY: The SQL standard says that an ORDER BY clause is in effect only within the view (or derived table) definition. Thus, you cannot in general rely on a query using a view with an ORDER BY clause will provide rows in that ordering. However, MySQL supports an extension where ORDER BY is sometimes propagated to the outer query: - For single-table UPDATE and DELETE statements, ORDER BY from view is propagated into the UPDATE/DELETE statement. - However, ordering of multi-table UPDATE and DELETE statements is not supported and cause an error. - For SELECT statements where a view with an ORDER BY clause is the only referenced table, and which is not grouped or have an ORDER BY clause itself, the ORDER BY clause of the view is used for the SELECT. - For SELECT statements that are grouped or ordered or refer more than one table (actually more than the view), any ORDER BY clauses in referenced views are ignored. And they are ignored because ORDER BY on the source table in this case does not make sense. We clarified these rules when we released 5.7. Old MySQL had a lot of "extensions" that might at first sight be seen as user-friendly, but when you looked at them more closely, they might be inconsistent, or provide non-deterministic results. We have a long-lasting effort at removing inconsistent results as much as possible, and clarify confusing semantics.
[19 Dec 2017 16:36]
teo teo
> MySQL does not allow ORDER BY together with a multi-table update operation What??? And why on earth shouldn't it be allowed?
Description: Update of a view made of joined tables with "order by" clause in definition of the view does not work in versions 5.7.x. Up to 5.6.35 there is no problem with it. Tested on 5.5.19, 5.5.54, 5.6.35, 5.7.15, 5.7.17 versions (except of 5.5.19 all 64-bits compilation) How to repeat: delimiter ; ; Create database if not exists Test; use Test; Select 'Droping tables ...' as Command; drop view if exists vTest021; drop table if exists Test02 ; drop table if exists Test04 ; Select 'Creating Test02 ...' as Command; create table Test02 (ID INTEGER UNSIGNED, Error TINYINT UNSIGNED, CasStamp bigint UNSIGNED, E0 TINYINT UNSIGNED, E1 TINYINT UNSIGNED, E2 TINYINT UNSIGNED) ENGINE = MYISAM ; create UNIQUE index iIDCasStamp on Test02 (ID, CasStamp) ; create index iID on Test02 (ID) ; create index iError on Test02 (Error) ; create table Test04 (ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, Podnik int unsigned, COM char(12), NewRow tinyint, PRIMARY KEY (ID) ) ENGINE = MYISAM ; create index iPodnikCOM on Test04 (Podnik, COM) ; create index iNewRow on Test04 (NewRow) ; create view vTest021 as select Test04.ID, Podnik, COM, CasStamp, sign(Error) as E, concat(repeat('0', 8-length(bin(Error))), bin(Error)) as Err, NewRow, Error, E0, E1, E2 from Test04 inner join Test02 on Test04.ID=Test02.ID where NewRow > 0 order by ID, CasStamp; insert into Test04 (Podnik, COM, NewRow) values (71, '111', 0), (71, '112', 0), (72, '222', 1), (72, '223', 1), (72, '224', 1); insert into Test02 (ID, Error, CasStamp, E0, E1, E2) values (1, 0, 2016010101, 0,0,0), (1, 0, 2016010102, 0,0,0), (1, 0, 2016010103, 0,0,0), (1, 0, 2016010104, 0, 0, 0); insert into Test02 (ID, Error, CasStamp, E0, E1, E2) values (2, 4, 2016010104, 0,0,0), (2, 0, 2016010103, 0,0,16), (2, 0, 2016010102, 0,0,0), (2, 0, 2016010101, 0, 0, 0); insert into Test02 (ID, Error, CasStamp, E0, E1, E2) values (3, 0, 2016010104, 0,0,1), (3, 0, 2016010103, 16,0,64), (3, 0, 2016010102, 0,0,0), (3, 0, 2016010101, 0, 0, 0); insert into Test02 (ID, Error, CasStamp, E0, E1, E2) values (4, 0, 2016010101, 0,0,0), (4, 0, 2016010103, 0,32,1), (4, 0, 2016010102, 0,0,0), (4, 0, 2016010104, 0, 4, 0); insert into Test02 (ID, Error, CasStamp, E0, E1, E2) values (5, 0, 2016010101, 0,0,0), (5, 0, 2016010102, 0,0,0), (5, 0, 2016010104, 0,0,0), (5, 0, 2016010103, 0, 0, 0); #This update works fine up to 5.6.35 but generates "ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY" in 5.7.x version update vTest021 set Error = Error | 1 where E0>0; update vTest021 set Error = Error | 2 where E1>0; update vTest021 set Error = Error | 4 where E2>0;