Hi, I have tested the correlated and uncorrelated subqueries in Hive CDH 5.13 and you are right.     CORRELATED: First the data and test in Impala: [10.197.0.0:21000] > create table work.tbla ( id int, s string ); [10.197.0.0:21000] > create table work.tblb ( id int, s string ); [10.197.0.0:21000] > insert into work.tbla select 100, '2008-01-01' union select 200, '2009-12-01'; Modified 2 row(s) in 3.69s [10.197.0.0:21000] > insert into work.tblb select 100, '2008-01-01' union select 200, '2009-12-01'; Modified 2 row(s) in 3.63s [10.197.0.0:21000] > select * from work.tbla a where exists ( select null from work.tblb b where a.id = b.id and b.s > '2009-10-01' ); +-----+------------+ | id | s | +-----+------------+ | 200 | 2009-12-01 | +-----+------------+ Now in Hive:   0: jdbc:hive2://xxx.com> explain select * from work.tbla a where exists ( select null from work.tblb b where a.id = b.id and b.s > '2009-10-01' ); .. INFO : OK +----------------------------------------------------+--+ | Explain | +----------------------------------------------------+--+ | STAGE DEPENDENCIES: | | Stage-4 is a root stage | | Stage-3 depends on stages: Stage-4 | | Stage-0 depends on stages: Stage-3 | | | | STAGE PLANS: | | Stage: Stage-4 | | Map Reduce Local Work | | Alias -> Map Local Tables: | | sq_1:b | | Fetch Operator | | limit: -1 | | Alias -> Map Local Operator Tree: | | sq_1:b | | TableScan | | alias: b | | filterExpr: ((s > '2009-10-01') and id is not null) (type: boolean) | | Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE | | Filter Operator | | predicate: ((s > '2009-10-01') and id is not null) (type: boolean) | | Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE | | Select Operator | | expressions: id (type: int) | | outputColumnNames: _col1 | | Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE | | Group By Operator | | keys: _col1 (type: int) | | mode: hash | | outputColumnNames: _col0 | | Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE | | HashTable Sink Operator | | keys: | | 0 id (type: int) | | 1 _col0 (type: int) | | | | Stage: Stage-3 | | Map Reduce | | Map Operator Tree: | | TableScan | | alias: a | | filterExpr: id is not null (type: boolean) | | Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE | | Filter Operator | | predicate: id is not null (type: boolean) | | Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE | | Map Join Operator | | condition map: | | Left Semi Join 0 to 1 | | keys: | | 0 id (type: int) | | 1 _col0 (type: int) | | outputColumnNames: _col0, _col1 | | Statistics: Num rows: 1 Data size: 33 Basic stats: COMPLETE Column stats: NONE | | File Output Operator | | compressed: false | | Statistics: Num rows: 1 Data size: 33 Basic stats: COMPLETE Column stats: NONE | | table: | | input format: org.apache.hadoop.mapred.TextInputFormat | | output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat | | serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe | | Local Work: | | Map Reduce Local Work | | | | Stage: Stage-0 | | Fetch Operator | | limit: -1 | | Processor Tree: | | ListSink | | | +----------------------------------------------------+--+ 69 rows selected (0.311 seconds) UNCORRELATED - Hive fails:   0: jdbc:hive2://xxxx.com> explain select * from work.tbla a where exists ( select null from work.tblb b where b.s > '2009-10-01' ); Error: Error while compiling statement: FAILED: SemanticException Line 1:65 Invalid SubQuery expression ''2009-10-01'' in definition of SubQuery sq_1 [ exists ( select null from work.tblb b where b.s > '2009-10-01' ) ] used as sq_1 at Line 1:39: For Exists/Not Exists operator SubQuery must be Correlated. (state=42000,code=40000) Impala ok [10.197.0.0:21000] > select * from work.tbla a where exists ( select null from work.tblb b where b.s > '2009-10-01' ); +-----+------------+ | id | s | +-----+------------+ | 100 | 2008-01-01 | | 200 | 2009-12-01 | +-----+------------+ Fetched 2 row(s) in 0.19s ... View more

Yes. Just in the hql file. Not anything in XML file ... View more