Got can't find column error when IN/NOT IN is used

[guo-shaoge]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists stu;
drop table if exists exam;

create table stu(id int, name varchar(100));
insert into stu values(1, null);

create table exam(stu_id int, course varchar(100), grade int);
insert into exam values(1, 'math', 100);

select * from stu where stu.name not in (select 'guo' from exam where exam.stu_id = stu.id);

2. What did you expect to see? (Required)

select * from stu where stu.name not in (select 'guo' from exam where exam.stu_id = stu.id);
Empty set (0.001 sec)

3. What did you see instead (Required)

MySQL [test]> explain select * from stu where stu.name not in (select 'guo' from exam where exam.stu_id = stu.id);
ERROR 1105 (HY000): Can't find column Column#8 in schema Column: [test.stu.id,test.stu.name,test.exam.stu_id] Unique key: []

4. What is your TiDB version? (Required)

MySQL [test]> select tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()
   |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.2.0-alpha-671-gb150be820
Edition: Community
Git Commit Hash: b150be8200f3d44dbea20b09cd6521190bd3bcb1
Git Branch: master
UTC Build Time: 2022-08-10 13:08:34
GoVersion: go1.18
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

[ChenPeng2013]

release-4.0、release-5.0、release-5.1、release-5.2、release-5.3 and release-5.4

MySQL [test]> select * from stu where stu.name not in (select 'guo' from exam where exam.stu_id = stu.id);
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

[guo-shaoge]

release-4.0、release-5.0、release-5.1、release-5.2、release-5.3 and release-5.4

MySQL [test]> select * from stu where stu.name not in (select 'guo' from exam where exam.stu_id = stu.id);
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

The old version doesn't give error, but its result is still wrong. (We expect empty result, like MySQL8.0).

[winoros]

You can use NOT EXISTS as a workaround.