我执行的sql如下:
select * from db_name.table_name_1 where exists(select 1 from db_name.table_name_2 a where a.row_id = db_name.table_name_1.row_id )
执行后报错信息为:
- Error while compiling statement: FAILED: SemanticException Line 2:31 Invalid SubQuery expression 'row_id' in definition of SubQuery sq_1 [ exists (select 1 from db_name.table_name_2 a where a.row_id= db_name.table_name_1.row_id) ] used as sq_1 at Line 1:47: For Exists/Not Exists operator SubQuery must be Correlated.
官网关于exists/not exists的示例:
SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)
比对后并没有发现任何不同的地方.
之后将sql略作变动,发现可以正常执行了,新sql如下:
select * from db_name.table_name_1 b where exists(select 1 from db_name.table_name_2 a where a.row_id = b.row_id )
怀疑是由于exists条件中使用了多个目标限定符“.”导致无法正确识别列名,于是再次对原sql进行修改,将exists条件中的db_name.删除,发现也可以正常执行,修改后的sql如下:
select * from db_name.table_name_1 where exists(select 1 from db_name.table_name_2 a where a.row_id = table_name_1.row_id )
具体的原因我也不太清楚,欢迎留言讨论。