hive> SET hive.cbo.enable=false; hive> EXPLAIN > SELECT * > FROM i1122 AS t1 > WHERE EXISTS ( > SELECT 1 > FROM i1122 AS t2 > WHERE t2.id = substr(t1.id,4) > ); OK 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:t2 Fetch Operator limit: -1 Alias -> Map Local Operator Tree: sq_1:t2 TableScan alias: t2 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: id is not null (type: boolean) Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: id (type: string) outputColumnNames: _col0 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Group By Operator keys: _col0 (type: string) mode: hash outputColumnNames: _col0 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE HashTable Sink Operator keys: 0 substr(id, 4) (type: string) 1 _col0 (type: string) Stage: Stage-3 Map Reduce Map Operator Tree: TableScan alias: t1 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: substr(id, 4) is not null (type: boolean) Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Map Join Operator condition map: Left Semi Join 0 to 1 keys: 0 substr(id, 4) (type: string) 1 _col0 (type: string) outputColumnNames: _col0 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe Local Work: Map Reduce Local Work Stage: Stage-0 Fetch Operator limit: -1 Processor Tree: ListSink Time taken: 0.129 seconds, Fetched: 67 row(s) hive>