MySQL Bugs: #110471: Fix docs for error 1093

Bug #110471	Fix docs for error 1093
Submitted:	22 Mar 2023 20:40	Modified:	23 Mar 2023 4:08
Reporter:	Mark Callaghan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The docs for error 1093 state:
1) this is about UPDATE statements
2) incorrectly used table

For 1) the error can also occur for DELETE statements. 
For 1) the limitation is correctly explained here:
https://dev.mysql.com/doc/refman/8.0/en/subquery-restrictions.html
... but incorrectly explained here:
https://dev.mysql.com/doc/refman/8.0/en/subquery-errors.html
... and the error message only mentions UPDATE, not DELETE

For 2) this isn't incorrect, it is a missing feature in MySQL. It works fine in Postgres (which really cares about being standards compliant) and other DBMS.

How to repeat:

mysql> explain DELETE FROM t0 WHERE deviceid=4 AND timestamp IN (SELECT MIN(timestamp) FROM t0 WHERE deviceid=4);
ERROR 1093 (HY000): You can't specify target table 't0' for update in FROM clause

Suggested fix:
Would be great if MySQL supported this. Failing that, please update the docs athttps://dev.mysql.com/doc/refman/8.0/en/subquery-errors.html and the error message text.

It is odd that this gets error 1093

DELETE FROM t1 WHERE deviceid=4 AND timestamp IN 
    (SELECT MIN(timestamp) FROM t1 WHERE deviceid=4)

But this works

DELETE FROM t1 WHERE deviceid=4 AND timestamp IN 
    (select * from (SELECT MIN(timestamp) FROM t1 WHERE deviceid=4) as xx);

Hello Mark,

Thank you for the report and feedback!

Thanks,
Umesh

If the error message is incorrect, that's a software issue.

Changing category to Errors so this can be fixed.