[MDEV-14291] UPDATE works even if SELECT subquery has wrong syntax (that shouldn't compile at all) Created: 2017-11-05  Updated: 2017-11-07  Resolved: 2017-11-07

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 5.5.52-galera
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Devaliano Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: innodb
Environment:

Centos 7 Linux server


 Description   

Here is the table: it has two columns (termsNoticeId and dateOf)

 desc termsNotice;
 
+---------------+-------------+------+-----+---------+-------+
 
| Field         | Type        | Null | Key | Default | Extra |
 
+---------------+-------------+------+-----+---------+-------+
 
| termsNoticeId | smallint(6) | NO   | PRI | 0       |       |
 
| dateOf        | datetime    | YES  |     | NULL    |       |
 
+---------------+-------------+------+-----+---------+-------+

The table is created and populated like this:

drop table if exists termsNotice;
 
create table termsNotice (
 
    termsNoticeId smallint primary key,
 
    dateOf datetime
 
);
 
insert into termsNotice (termsNoticeId,dateOf) values (1,now());

So table currently has one row.

There is also 'user' table that has columns managerId, id and dateOfAssent:

create table user (
 
    managerId bigint not null,
 
    id bigint auto_increment primary key, 
    dateOfAssent datetime 
    );

You can insert into 'user' as:

insert into user (managerId, id, dateOfAssent) values (0,19,now());

Then UPDATE this table like this:

update user set managerId=id, dateOfAssent=(select dateOf from termsNotice where id=1) where id='19';
 
Query OK, 1 row affected (0.08 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0

This query is INVALID. There is no 'id' column in termsNotice in SELECT subquery. Yet the query succeeded, and instead of an error message, the table was updated, and dateOfAssent was set to NULL!

 Comments   
Comment by Elena Stepanova [ 2017-11-07 ]

It's a normal correlated subquery which refers to the outer query. I expect you wouldn't claim it to be incorrect if it was

update `user` set managerId=id, dateOfAssent=(select dateOf from termsNotice where `user`.id=1) where id='19';

However, since your termsNotice table does not contain id column, the column name unambiguously resolves to the outer query and the qualifier can be skipped.

The structure is perfectly fine, it works in all versions of MariaDB, MySQL, and in PostgreSQL too.

Generated at Thu Feb 08 08:12:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.