MySQL Bugs: #88397: CTE with window function don't work with update/delete as expected.

Bug #88397	CTE with window function don't work with update/delete as expected.
Submitted:	8 Nov 2017 4:14	Modified:	8 Nov 2017 13:36
Reporter:	Meiji Kimura	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.3	OS:	Any
Assigned to:	Guilhem Bichot	CPU Architecture:	Any
Tags:	cte, window

Description:
I tried to run these queries.
CTE with Window functions, Update & Delete. (It works on Microsoft SQL Server 2017).

with UpdView as(
select Val,Row_Number() over(order by Val) as NewVal
  from TestTable)
update UpdView set Val = NewVal;

with DelView as(
select Row_Number() over(order by Val) as rn
  from TestTable)
delete from DelView where rn < 3;

But they all returns errors.

mysql [localhost] {msandbox} (test) > with UpdView as(
    -> select Val,Row_Number() over(order by Val) as NewVal
    ->   from TestTable)
    -> update UpdView set Val = NewVal;
ERROR 1288 (HY000): The target table UpdView of the UPDATE is not updatable

mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > with DelView as(
    -> select Row_Number() over(order by Val) as rn
    ->   from TestTable)
    -> delete from DelView where rn < 3;
ERROR 1146 (42S02): Table 'test.DelView' doesn't exist

How to repeat:
create table TestTable(Val int);

insert into TestTable values
(1),(3),(5),(10),(20),(30),(40);

with UpdView as(
select Val,Row_Number() over(order by Val) as NewVal
  from TestTable)
update UpdView set Val = NewVal;

with DelView as(
select Row_Number() over(order by Val) as rn
  from TestTable)
delete from DelView where rn < 3;

Suggested fix:
I'm very happy to execute these queries !

or need description on manual as limitation based on current implimentation.

https://dev.mysql.com/doc/refman/8.0/en/view-updatability.html

and DelView's error are not properly, need modify it.

In Microsoft SQL Server 2017, they works well as belows.

1> ver(order by Val) as NewVal from TestTable) update UpdView set Val = NewVal;
2> go

(7 rows affected)
1> select * from TestTable;
2> go
Val        
-----------
          1
          2
          3
          4
          5
          6
          7

(7 rows affected)
1> ) over(order by Val) as rn from TestTable) delete from DelView where rn < 3;
2> go
Val        
-----------
          3
          4
          5
          6
          7

(5 rows affected)

Hello Meiji-San,

Thank you for the report and feedback!

Thanks,
Umesh

Hello.
In the 2015 version of the SQL standard, a CTE cannot be defined in UPDATE; MySQL allows it but makes the CTE read-only (we're updating the documentation now to mention this).
This said, one could use a view instead of the CTE; then the view may be updatable, but due to the presence of window functions it is materialized into a temporary table (it is not merged) so is not updatable (we're going to mention it in the doc as well).
Even if we managed to merge the view's definition in the outer UPDATE statement, it would produce an UPDATE containing a window function, which is forbidden by the SQL standard.
A workaround is:
 with numbered as
  (select Val, row_number() over (order by Val) as NewVal from TestTable)
 update TestTable, numbered set TestTable.Val=numbered.NewVal
  where TestTable.Val=numbered.Val;
assuming Val is unique and not null which guarantees a one-to-one matching between TestTable and CTE.
All the above applies to DELETE too.