| Bug #112289 | No error seen when unknown column is used in subquery for Update Statement | ||
|---|---|---|---|
| Submitted: | 7 Sep 2023 11:32 | Modified: | 8 Sep 2023 7:08 |
| Reporter: | Aaditya Dubey | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 8.0.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Sep 2023 7:08]
MySQL Verification Team
Hello Aditya, Thank you for the report and feedback. IMHO this is duplicate of Bug #100599, please see Sinisa's explanation in his initial response. # This behavior can be seen in other RDBMS as well PostgreSQL 15.0 = select * from tmp_t1; user_id gold 111 0 112 0 select * from tmp_t2; id fname 111 zhangsan update tmp_t1 set gold=10000 where user_id =(select user_id from tmp_t2 where id=111); select * from tmp_t1; user_id gold 111 10000 112 10000 # Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) select * from tmp_t1; user_id gold 111 0 112 0 select * from tmp_t2; id fname 111 zhangsan update tmp_t1 set gold=10000 where user_id =(select user_id from tmp_t2 where id=111); select * from tmp_t1; user_id gold 111 10000 112 10000 regards, Umesh
[6 Dec 2024 11:36]
Sveta Smirnova
Which bug is this one is duplicate of?
[6 Dec 2024 12:03]
MySQL Verification Team
Hello Sveta, This one is duplicate of Bug #100599. Thank you. Sincerely, Umesh
Description: Update query is running fine when unknown column is used in where clause in subquery. Please check How to repeat section: MySQL Version : 8.0.34 if we changed the sql to like below then it will throw error: update tmp_t1 set gold=10000 where user_id =(select user_id1 from tmp_t2 where id=111); mysql [localhost:8036] {msandbox} (test) > update tmp_t1 set gold=10000 -> where user_id =(select user_id1 from tmp_t2 where id=111); ERROR 1054 (42S22): Unknown column 'user_id1' in 'field list' but when use user_id which is unknown column to tmp_t2, it works fine. update tmp_t1 set gold=10000 where user_id =(select user_id from tmp_t2 where id=111); mysql [localhost:8036] {msandbox} (test) > update tmp_t1 set gold=10000 -> where user_id =(select user_id from tmp_t2 where id=111); Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 How to repeat: mysql [localhost:8036] {msandbox} ((none)) > use test Database changed mysql [localhost:8036] {msandbox} (test) > create table tmp_t1(user_id int,gold int); Query OK, 0 rows affected (0.03 sec) mysql [localhost:8036] {msandbox} (test) > create table tmp_t2(id int,fname varchar(32)); Query OK, 0 rows affected (0.02 sec) mysql [localhost:8036] {msandbox} (test) > insert into tmp_t1(user_id,gold) values (111,0); Query OK, 1 row affected (0.01 sec) mysql [localhost:8036] {msandbox} (test) > insert into tmp_t1(user_id,gold) values (112,0); Query OK, 1 row affected (0.01 sec) mysql [localhost:8036] {msandbox} (test) > insert into tmp_t2(id,fname)values (111,'zhangsan'); Query OK, 1 row affected (0.01 sec) mysql [localhost:8036] {msandbox} (test) > select * from tmp_t1; +---------+------+ | user_id | gold | +---------+------+ | 111 | 0 | | 112 | 0 | +---------+------+ 2 rows in set (0.00 sec) mysql [localhost:8036] {msandbox} (test) > select * from tmp_t2; +------+----------+ | id | fname | +------+----------+ | 111 | zhangsan | +------+----------+ 1 row in set (0.00 sec) mysql [localhost:8036] {msandbox} (test) > update tmp_t1 set gold=10000 -> where user_id =(select user_id from tmp_t2 where id=111); Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql [localhost:8036] {msandbox} (test) > select * from tmp_t1; +---------+-------+ | user_id | gold | +---------+-------+ | 111 | 10000 | | 112 | 10000 | +---------+-------+ 2 rows in set (0.00 sec) Suggested fix: Expecting error since it can be dangerous.