Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
5.5.52-galera
-
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!