Bug #103694 updatable tables check is wrong in update statement

• Submitted: 14 May 2021 6:04
• Modified: 14 May 2021 6:08
• Reporter: x j
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.22-0ubuntu0.20.04.3 (Ubuntu)
• OS: Ubuntu
• CPU Architecture: x86

[14 May 2021 6:04] x j

Description:
an updatable table can not update in query

How to repeat:
create database db1;
create database db2;

-- both in db1 and db2
create table t(a int);
insert into t values (1);

use db1;
update db2.t, (select 1 as a) as t set db2.t.a=1;
update (select 1 as a) as t, db2.t set db2.t.a=1;

the first SQL runs successfully, and the second SQL will report an error:
ERROR 1288 (HY000): The target table t of the UPDATE is not updatable

[14 May 2021 6:08] MySQL Verification Team

Hello x j,

Thank you for the report and test case.
Imho this is duplicate of Bug #103665, please see Bug #103665
Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

regards,
Umesh