BigQuery子查询报错但外层查询正常执行的原因咨询

场景还原 #

单独执行以下查询时，会因table1不存在enrollment_year列（实际列名为enrol_year），且值类型不匹配（正确值应为字符串'Year 2003'），抛出错误：Unrecognized name: enrollment_year at [1:100]

select student_id 
from table1 
where enrollment_year = 2003 
group by 1

但将该错误子查询作为IN条件嵌入外层查询时，整个语句却能正常运行并返回结果：

select grade, count(*)
from `table2`
where student_id in (select student_id 
                     from `table1` 
                     where enrollment_year = 2003 
                     group by 1)
group by 1;

原因解析 #

这是BigQuery（从报错格式判断你使用的是BigQuery）的列名作用域解析规则导致的：

• 当子查询中的列名无法在自身查询的表中找到时，BigQuery会自动向外层查询的作用域查找该列名。如果外层表table2恰好存在enrollment_year列，子查询里的enrollment_year会被解析为外层表的列，而非table1的列。
• 此时子查询的逻辑已经偏离了你的原本意图：原本想筛选table1中2003年入学的学生ID，实际变成了针对table2的每一行，用该行的enrollment_year值去匹配table1的学生ID。如果外层table2的enrollment_year和2003没有匹配项，子查询会返回空集合，外层查询会统计所有student_id不在空集合里的记录；如果有匹配项，则按错误的逻辑返回结果。

验证方法 #

给子查询的列名加上表别名限定，强制指定从table1读取列，此时查询会立刻抛出原本的列不存在错误，验证作用域解析的问题：

select grade, count(*)
from `table2`
where student_id in (select student_id 
                     from `table1` 
                     where table1.enrollment_year = 2003 
                     group by 1)
group by 1;

内容的提问来源于stack exchange，提问作者KG1