mysql 8.0 执行 sql 语句 中 where 条件中 in 的子查询是错误的，但是完整的 sql 语句执行是成功

[liaowb3]

大佬们，我遇到一个很奇怪的 sql 问题，首先我先展示两个建表语句

CREATE TABLE `monitor_message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `monitor_id` int(11) NOT NULL DEFAULT '0',
  `monitor_type` varchar(255) NOT NULL DEFAULT '',
  `run_time` datetime NOT NULL,
  `type` varchar(255) NOT NULL 
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5238 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

CREATE TABLE `monitor_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` varchar(128) NOT NULL DEFAULT '',
  `monitor_type` varchar(255) NOT NULL DEFAULT '',
  `name` varchar(512) NOT NULL DEFAULT '',
  `state` varchar(64) NOT NULL DEFAULT '启用',
  `json_config` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=257 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

然后下面那个语句能执行成功

		select
			id,
			monitor_id,
			monitor_type
		from
			monitor_message
		where
			run_time >= '2023-11-07'
			AND run_time < '2023-11-08'
			AND monitor_id IN (
				SELECT
					monitor_id
				FROM
					monitor_config
				WHERE
					project_id = '123'
			)
		order by run_time desc

但是其中的子查询是有问题的，单独执行是失败的，为啥会执行成功呢

[thevita]

https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

[liaowb3]

@thevita 我看到你发的链接是关于表达式求值中的类型转换，但是我不太能关联到我这边这个问题，所以能稍微具体说一下是什么个问题吗

[thevita]

@liaowb3 ...看错了，ignore me

[adoal]

子查询单独执行时又不知道 monitor_id 是哪里来的

[wps353]

https://dba.stackexchange.com/questions/218871/subquery-gives-no-error-for-a-non-existing-column-with-the-same-name-as-in-the-o

[fujizx]

monitor_config 表里没有 monitor_id 啊

[foursevenlove]

楼上正解

[dsioahui2]

另外这个语句改成 join 性能会有成倍的提高，比如
```sql
select
id,
monitor_id,
monitor_type
from
monitor_message t1 join monitor_config t2
on t1.monitor_id = t2.id (不知道你是要关联哪个字段，姑且按照 id 了)
where
t1.run_time >= '2023-11-07'
AND t1.run_time < '2023-11-08'
AND t2.project_id = '123'
order by run_time desc
```

[wu00]

题目和内容都说的很清楚啊...
我也试了下还真是,好像子查询异常被吃了一样最终生成
SELECT * FROM table1 WHERE mid IN ( SELECT NULL )

[whorusq]

IN() 操作符允许使用 NULL 值

[Rache1]

DataGrip 里面执行的时候提示了这里是外部的列


explain analyze 的结果如下

-> Sort: monitor_message.run_time DESC (actual time=0.048..0.048 rows=0 loops=1)
-> Stream results (cost=0.70 rows=1) (actual time=0.035..0.035 rows=0 loops=1)
-> Hash semijoin (no condition) (cost=0.70 rows=1) (actual time=0.033..0.033 rows=0 loops=1)
-> Filter: ((monitor_message.run_time >= TIMESTAMP'2023-11-07 00:00:00') and (monitor_message.run_time < TIMESTAMP'2023-11-08 00:00:00')) (cost=0.35 rows=1) (never executed)
-> Table scan on monitor_message (cost=0.35 rows=1) (never executed)
-> Hash
-> Limit: 1 row(s) (cost=0.35 rows=1) (actual time=0.027..0.027 rows=0 loops=1)
-> Filter: (monitor_config.project_id = '123') (cost=0.35 rows=1) (actual time=0.026..0.026 rows=0 loops=1)
-> Table scan on monitor_config (cost=0.35 rows=1) (actual time=0.020..0.024 rows=1 loops=1)

[Rache1]

@Rache1 #11 缩进居然没了