PREHOOK: query: create table table_asc(key int, value string) CLUSTERED BY (key) SORTED BY (key asc) INTO 1 BUCKETS STORED AS RCFILE PREHOOK: type: CREATETABLE POSTHOOK: query: create table table_asc(key int, value string) CLUSTERED BY (key) SORTED BY (key asc) INTO 1 BUCKETS STORED AS RCFILE POSTHOOK: type: CREATETABLE POSTHOOK: Output: default@table_asc PREHOOK: query: create table table_desc(key int, value string) CLUSTERED BY (key) SORTED BY (key desc) INTO 1 BUCKETS STORED AS RCFILE PREHOOK: type: CREATETABLE POSTHOOK: query: create table table_desc(key int, value string) CLUSTERED BY (key) SORTED BY (key desc) INTO 1 BUCKETS STORED AS RCFILE POSTHOOK: type: CREATETABLE POSTHOOK: Output: default@table_desc PREHOOK: query: insert overwrite table table_asc select key, value from src PREHOOK: type: QUERY PREHOOK: Input: default@src PREHOOK: Output: default@table_asc POSTHOOK: query: insert overwrite table table_asc select key, value from src POSTHOOK: type: QUERY POSTHOOK: Input: default@src POSTHOOK: Output: default@table_asc POSTHOOK: Lineage: table_asc.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ] POSTHOOK: Lineage: table_asc.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ] PREHOOK: query: insert overwrite table table_desc select key, value from src PREHOOK: type: QUERY PREHOOK: Input: default@src PREHOOK: Output: default@table_desc POSTHOOK: query: insert overwrite table table_desc select key, value from src POSTHOOK: type: QUERY POSTHOOK: Input: default@src POSTHOOK: Output: default@table_desc POSTHOOK: Lineage: table_asc.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ] POSTHOOK: Lineage: table_asc.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ] POSTHOOK: Lineage: table_desc.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ] POSTHOOK: Lineage: table_desc.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ] PREHOOK: query: -- If the user asked for sort merge join to be enforced (by setting -- hive.enforce.sortmergebucketmapjoin to true), an error should be thrown, since -- one of the tables is in ascending order and the other is in descending order, -- and sort merge bucket mapjoin cannot be performed. In the default mode, the -- query would succeed, although a regular map-join would be performed instead of -- what the user asked. explain select /*+mapjoin(a)*/ * from table_asc a join table_desc b on a.key = b.key PREHOOK: type: QUERY POSTHOOK: query: -- If the user asked for sort merge join to be enforced (by setting -- hive.enforce.sortmergebucketmapjoin to true), an error should be thrown, since -- one of the tables is in ascending order and the other is in descending order, -- and sort merge bucket mapjoin cannot be performed. In the default mode, the -- query would succeed, although a regular map-join would be performed instead of -- what the user asked. explain select /*+mapjoin(a)*/ * from table_asc a join table_desc b on a.key = b.key POSTHOOK: type: QUERY POSTHOOK: Lineage: table_asc.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ] POSTHOOK: Lineage: table_asc.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ] POSTHOOK: Lineage: table_desc.key EXPRESSION [(src)src.FieldSchema(name:key, type:string, comment:default), ] POSTHOOK: Lineage: table_desc.value SIMPLE [(src)src.FieldSchema(name:value, type:string, comment:default), ] ABSTRACT SYNTAX TREE: (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_TABREF (TOK_TABNAME table_asc) a) (TOK_TABREF (TOK_TABNAME table_desc) b) (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL b) key)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_HINTLIST (TOK_HINT TOK_MAPJOIN (TOK_HINTARGLIST a))) (TOK_SELEXPR TOK_ALLCOLREF)))) STAGE DEPENDENCIES: Stage-3 is a root stage Stage-1 depends on stages: Stage-3 Stage-0 is a root stage STAGE PLANS: Stage: Stage-3 Map Reduce Local Work Alias -> Map Local Tables: a Fetch Operator limit: -1 Alias -> Map Local Operator Tree: a TableScan alias: a HashTable Sink Operator condition expressions: 0 {key} {value} 1 {key} {value} handleSkewJoin: false keys: 0 [Column[key]] 1 [Column[key]] Position of Big Table: 1 Stage: Stage-1 Map Reduce Alias -> Map Operator Tree: b TableScan alias: b Map Join Operator condition map: Inner Join 0 to 1 condition expressions: 0 {key} {value} 1 {key} {value} handleSkewJoin: false keys: 0 [Column[key]] 1 [Column[key]] outputColumnNames: _col0, _col1, _col4, _col5 Position of Big Table: 1 Select Operator expressions: expr: _col0 type: int expr: _col1 type: string expr: _col4 type: int expr: _col5 type: string outputColumnNames: _col0, _col1, _col4, _col5 Select Operator expressions: expr: _col0 type: int expr: _col1 type: string expr: _col4 type: int expr: _col5 type: string outputColumnNames: _col0, _col1, _col2, _col3 File Output Operator compressed: false GlobalTableId: 0 table: input format: org.apache.hadoop.mapred.TextInputFormat output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat Local Work: Map Reduce Local Work Stage: Stage-0 Fetch Operator limit: -1 FAILED: SemanticException [Error 10135]: Sort merge bucketed join could not be performed. If you really want to perform the operation, either set hive.optimize.bucketmapjoin.sortedmerge=false, or set hive.enforce.sortmergebucketmapjoin=false.