update stmt get panic and wrong result

[xiongjiwei]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t(a int);
create table t1(b int);

2. What did you expect to see? (Required)

mysql> update (select 1 as a) as t set a=1;
ERROR 1288 (HY000): The target table t of the UPDATE is not updatable
mysql> update (select 1 as a) as t, t1 set a=1;
ERROR 1288 (HY000): The target table t of the UPDATE is not updatable

3. What did you see instead (Required)

mysql> update (select 1 as a) as t set a=1;
ERROR 1105 (HY000): runtime error: invalid memory address or nil pointer dereference
mysql> update (select 1 as a) as t, t1 set a=3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

4. What is your TiDB version? (Required)

[guo-shaoge]

invalid memory error can be reproduced in 5.0, but not in master. Maybe someone already fixed it?

drop table if exists t;
drop table if exists t1;
create table t(a int);
create table t1(b int);
update (select 1 as a) as t set a=1;
update (select 1 as a) as t, t1 set a=1;

No errors:

mysql> update (select 1 as a) as t set a=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update (select 1 as a) as t, t1 set a=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Why TiDB doesn't give error: PlanBuilder compares alias name(t) with table name(a) which recored by column in assignment to check if a assignment is updating a updatable table or not. But actually the names should both use t or a.

[lzmhhh123]

#24614 @xiongjiwei You claimed it is a bug in MySQL. What's your opinion?

[xiongjiwei]

@lzmhhh123 please see https://bugs.mysql.com/bug.php?id=103694. MySQL will give an error, but it should not

[xiongjiwei]

it is different from this issue, this issue is tidb's bug.

when explicit test.t.a, the target table is test.t.a and it is updatable.
in this issue we run update (select 1 as a) as t, t1 set a=3; the target table is a subquery which is not updatable

[chrysan]

/assign AilinKid

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.