Description:
When a subquery contains a field that does not exist in the inner table, no error is reported during the SQL statement execution.
How to repeat:
1.Create tables.
CREATE TABLE `content_thread_basic` (
`id` bigint unsigned NOT NULL,
`circle_id` bigint NOT NULL,
`business_type_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
INSERT INTO `content_thread_basic` VALUES(1, 10000006, '6fadb388-a51a-11ec-af1e-911b6d9f1ec');
CREATE TABLE `content_thread_circle_relation` (
`thread_id` bigint NOT NULL,
`circle_id` bigint NOT NULL,
`update_time` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
INSERT INTO `content_thread_circle_relation` VALUES(1, 10000006, 1712476782810);
2.Execute the select or update operation. The `thread_id` field of the subquery does not exist in the inner table.
mysql> select circle_id, update_time from content_thread_circle_relation where circle_id = 10000006 and thread_id in (select thread_id from content_thread_basic a where a.circle_id = 10000006 and a.business_type_id = '6fadb388-a51a-11ec-af1e-911b6d9f1ec');
+-----------+---------------+
| circle_id | update_time |
+-----------+---------------+
| 10000006 | 1712476782810 |
+-----------+---------------+
1 row in set (0.00 sec)
mysql> update content_thread_circle_relation set circle_id = 10000094, update_time = replace(unix_timestamp(current_timestamp(3)), '.', '') where circle_id = 10000006 and thread_id in (select thread_id from content_thread_basic a where a.circle_id = 10000006 and a.business_type_id = '6fadb388-a51a-11ec-af1e-911b6d9f1ec');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from content_thread_circle_relation;
+-----------+-----------+---------------+
| thread_id | circle_id | update_time |
+-----------+-----------+---------------+
| 1 | 10000094 | 1712476932912 |
+-----------+-----------+---------------+
1 row in set (0.01 sec)
The select or update operation executes successfully, but my expectation is to get an error, like this:
mysql> select thread_id from content_thread_basic a where a.circle_id = 10000006 and a.business_type_id = '6fadb388-a51a-11ec-af1e-911b6d9f1ec';
ERROR 1054 (42S22): Unknown column 'thread_id' in 'field list'
