Saving all output to "!!{outputDirectory}!!/join_filters_overlap.q.raw". Enter "record" with no arguments to stop it.
>>>  !run !!{qFileDirectory}!!/join_filters_overlap.q
>>>  -- HIVE-3411 Filter predicates on outer join overlapped on single alias is not handled properly
>>>  
>>>  create table a as SELECT 100 as key, a.value as value FROM src LATERAL VIEW explode(array(40, 50, 60)) a as value limit 3;
'key','value'
No rows selected 
>>>  
>>>  -- overlap on a
>>>  explain extended select * from a left outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (a.key=c.key AND a.value=60 AND c.value=60);
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_LEFTOUTERJOIN (TOK_LEFTOUTERJOIN (TOK_TABREF (TOK_TABNAME a)) (TOK_TABREF (TOK_TABNAME a) b) (AND (AND (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL b) key)) (= (. (TOK_TABLE_OR_COL a) value) 50)) (= (. (TOK_TABLE_OR_COL b) value) 50))) (TOK_TABREF (TOK_TABNAME a) c) (AND (AND (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL c) key)) (= (. (TOK_TABLE_OR_COL a) value) 60)) (= (. (TOK_TABLE_OR_COL c) value) 60)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        a '
'          TableScan'
'            alias: a'
'            GatherStats: false'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: int'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: int'
'              tag: 0'
'              value expressions:'
'                    expr: key'
'                    type: int'
'                    expr: value'
'                    type: int'
'        b '
'          TableScan'
'            alias: b'
'            GatherStats: false'
'            Filter Operator'
'              isSamplingPred: false'
'              predicate:'
'                  expr: (value = 50)'
'                  type: boolean'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: key'
'                      type: int'
'                sort order: +'
'                Map-reduce partition columns:'
'                      expr: key'
'                      type: int'
'                tag: 1'
'                value expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'        c '
'          TableScan'
'            alias: c'
'            GatherStats: false'
'            Filter Operator'
'              isSamplingPred: false'
'              predicate:'
'                  expr: (value = 60)'
'                  type: boolean'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: key'
'                      type: int'
'                sort order: +'
'                Map-reduce partition columns:'
'                      expr: key'
'                      type: int'
'                tag: 2'
'                value expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'      Needs Tagging: true'
'      Path -> Alias:'
'        !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a [b, c, a]'
'      Path -> Partition:'
'        !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a '
'          Partition'
'            base file name: a'
'            input format: org.apache.hadoop.mapred.TextInputFormat'
'            output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'            properties:'
'              bucket_count -1'
'              columns key,value'
'              columns.types int:int'
'              file.inputformat org.apache.hadoop.mapred.TextInputFormat'
'              file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'              location !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a'
'              name join_filters_overlap.a'
'              numFiles 1'
'              numPartitions 0'
'              numRows 3'
'              rawDataSize 18'
'              serialization.ddl struct a { i32 key, i32 value}'
'              serialization.format 1'
'              serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'              totalSize 21'
'              transient_lastDdlTime !!UNIXTIME!!'
'            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'          '
'              input format: org.apache.hadoop.mapred.TextInputFormat'
'              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'              properties:'
'                bucket_count -1'
'                columns key,value'
'                columns.types int:int'
'                file.inputformat org.apache.hadoop.mapred.TextInputFormat'
'                file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'                location !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a'
'                name join_filters_overlap.a'
'                numFiles 1'
'                numPartitions 0'
'                numRows 3'
'                rawDataSize 18'
'                serialization.ddl struct a { i32 key, i32 value}'
'                serialization.format 1'
'                serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'                totalSize 21'
'                transient_lastDdlTime !!UNIXTIME!!'
'              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'              name: join_filters_overlap.a'
'            name: join_filters_overlap.a'
'      Reduce Operator Tree:'
'        Join Operator'
'          condition map:'
'               Left Outer Join0 to 1'
'               Left Outer Join0 to 2'
'          condition expressions:'
'            0 {VALUE._col0} {VALUE._col1}'
'            1 {VALUE._col0} {VALUE._col1}'
'            2 {VALUE._col0} {VALUE._col1}'
'          filter mappings:'
'            0 [1, 1, 2, 1]'
'          filter predicates:'
'            0 {(VALUE._col1 = 50)} {(VALUE._col1 = 60)}'
'            1 '
'            2 '
'          handleSkewJoin: false'
'          outputColumnNames: _col0, _col1, _col4, _col5, _col8, _col9'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: int'
'                  expr: _col4'
'                  type: int'
'                  expr: _col5'
'                  type: int'
'                  expr: _col8'
'                  type: int'
'                  expr: _col9'
'                  type: int'
'            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              directory: file:!!{hive.exec.scratchdir}!!'
'              NumFilesPerFileSink: 1'
'              Stats Publishing Key Prefix: file:!!{hive.exec.scratchdir}!!'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'                  properties:'
'                    columns _col0,_col1,_col2,_col3,_col4,_col5'
'                    columns.types int:int:int:int:int:int'
'                    escape.delim \'
'                    serialization.format 1'
'              TotalFiles: 1'
'              GatherStats: false'
'              MultiFileSpray: false'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
180 rows selected 
>>>  select * from a left outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (a.key=c.key AND a.value=60 AND c.value=60);
'key','value','key','value','key','value'
'100','40','','','',''
'100','50','100','50','',''
'100','60','','','100','60'
3 rows selected 
>>>  select /*+ MAPJOIN(b,c)*/ * from a left outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (a.key=c.key AND a.value=60 AND c.value=60);
'key','value','key','value','key','value'
'100','40','','','',''
'100','50','100','50','',''
'100','60','','','100','60'
3 rows selected 
>>>  
>>>  -- overlap on b
>>>  explain extended select * from a right outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (b.key=c.key AND b.value=60 AND c.value=60);
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_LEFTOUTERJOIN (TOK_RIGHTOUTERJOIN (TOK_TABREF (TOK_TABNAME a)) (TOK_TABREF (TOK_TABNAME a) b) (AND (AND (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL b) key)) (= (. (TOK_TABLE_OR_COL a) value) 50)) (= (. (TOK_TABLE_OR_COL b) value) 50))) (TOK_TABREF (TOK_TABNAME a) c) (AND (AND (= (. (TOK_TABLE_OR_COL b) key) (. (TOK_TABLE_OR_COL c) key)) (= (. (TOK_TABLE_OR_COL b) value) 60)) (= (. (TOK_TABLE_OR_COL c) value) 60)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        a '
'          TableScan'
'            alias: a'
'            GatherStats: false'
'            Filter Operator'
'              isSamplingPred: false'
'              predicate:'
'                  expr: (value = 50)'
'                  type: boolean'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: key'
'                      type: int'
'                sort order: +'
'                Map-reduce partition columns:'
'                      expr: key'
'                      type: int'
'                tag: 0'
'                value expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'        b '
'          TableScan'
'            alias: b'
'            GatherStats: false'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: int'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: int'
'              tag: 1'
'              value expressions:'
'                    expr: key'
'                    type: int'
'                    expr: value'
'                    type: int'
'        c '
'          TableScan'
'            alias: c'
'            GatherStats: false'
'            Filter Operator'
'              isSamplingPred: false'
'              predicate:'
'                  expr: (value = 60)'
'                  type: boolean'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: key'
'                      type: int'
'                sort order: +'
'                Map-reduce partition columns:'
'                      expr: key'
'                      type: int'
'                tag: 2'
'                value expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'      Needs Tagging: true'
'      Path -> Alias:'
'        !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a [b, c, a]'
'      Path -> Partition:'
'        !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a '
'          Partition'
'            base file name: a'
'            input format: org.apache.hadoop.mapred.TextInputFormat'
'            output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'            properties:'
'              bucket_count -1'
'              columns key,value'
'              columns.types int:int'
'              file.inputformat org.apache.hadoop.mapred.TextInputFormat'
'              file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'              location !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a'
'              name join_filters_overlap.a'
'              numFiles 1'
'              numPartitions 0'
'              numRows 3'
'              rawDataSize 18'
'              serialization.ddl struct a { i32 key, i32 value}'
'              serialization.format 1'
'              serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'              totalSize 21'
'              transient_lastDdlTime !!UNIXTIME!!'
'            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'          '
'              input format: org.apache.hadoop.mapred.TextInputFormat'
'              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'              properties:'
'                bucket_count -1'
'                columns key,value'
'                columns.types int:int'
'                file.inputformat org.apache.hadoop.mapred.TextInputFormat'
'                file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'                location !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a'
'                name join_filters_overlap.a'
'                numFiles 1'
'                numPartitions 0'
'                numRows 3'
'                rawDataSize 18'
'                serialization.ddl struct a { i32 key, i32 value}'
'                serialization.format 1'
'                serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'                totalSize 21'
'                transient_lastDdlTime !!UNIXTIME!!'
'              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'              name: join_filters_overlap.a'
'            name: join_filters_overlap.a'
'      Reduce Operator Tree:'
'        Join Operator'
'          condition map:'
'               Right Outer Join0 to 1'
'               Left Outer Join1 to 2'
'          condition expressions:'
'            0 {VALUE._col0} {VALUE._col1}'
'            1 {VALUE._col0} {VALUE._col1}'
'            2 {VALUE._col0} {VALUE._col1}'
'          filter mappings:'
'            1 [0, 1, 2, 1]'
'          filter predicates:'
'            0 '
'            1 {(VALUE._col1 = 50)} {(VALUE._col1 = 60)}'
'            2 '
'          handleSkewJoin: false'
'          outputColumnNames: _col0, _col1, _col4, _col5, _col8, _col9'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: int'
'                  expr: _col4'
'                  type: int'
'                  expr: _col5'
'                  type: int'
'                  expr: _col8'
'                  type: int'
'                  expr: _col9'
'                  type: int'
'            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              directory: file:!!{hive.exec.scratchdir}!!'
'              NumFilesPerFileSink: 1'
'              Stats Publishing Key Prefix: file:!!{hive.exec.scratchdir}!!'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'                  properties:'
'                    columns _col0,_col1,_col2,_col3,_col4,_col5'
'                    columns.types int:int:int:int:int:int'
'                    escape.delim \'
'                    serialization.format 1'
'              TotalFiles: 1'
'              GatherStats: false'
'              MultiFileSpray: false'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
180 rows selected 
>>>  select * from a right outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (b.key=c.key AND b.value=60 AND c.value=60);
'key','value','key','value','key','value'
'','','100','40','',''
'100','50','100','50','',''
'','','100','60','100','60'
3 rows selected 
>>>  select /*+ MAPJOIN(a,c)*/ * from a right outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (b.key=c.key AND b.value=60 AND c.value=60);
'key','value','key','value','key','value'
'','','100','40','',''
'100','50','100','50','',''
'','','100','60','100','60'
3 rows selected 
>>>  
>>>  -- overlap on b with two filters for each
>>>  explain extended select * from a right outer join a b on (a.key=b.key AND a.value=50 AND b.value=50 AND b.value>10) left outer join a c on (b.key=c.key AND b.value=60 AND b.value>20 AND c.value=60);
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_LEFTOUTERJOIN (TOK_RIGHTOUTERJOIN (TOK_TABREF (TOK_TABNAME a)) (TOK_TABREF (TOK_TABNAME a) b) (AND (AND (AND (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL b) key)) (= (. (TOK_TABLE_OR_COL a) value) 50)) (= (. (TOK_TABLE_OR_COL b) value) 50)) (> (. (TOK_TABLE_OR_COL b) value) 10))) (TOK_TABREF (TOK_TABNAME a) c) (AND (AND (AND (= (. (TOK_TABLE_OR_COL b) key) (. (TOK_TABLE_OR_COL c) key)) (= (. (TOK_TABLE_OR_COL b) value) 60)) (> (. (TOK_TABLE_OR_COL b) value) 20)) (= (. (TOK_TABLE_OR_COL c) value) 60)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        a '
'          TableScan'
'            alias: a'
'            GatherStats: false'
'            Filter Operator'
'              isSamplingPred: false'
'              predicate:'
'                  expr: (value = 50)'
'                  type: boolean'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: key'
'                      type: int'
'                sort order: +'
'                Map-reduce partition columns:'
'                      expr: key'
'                      type: int'
'                tag: 0'
'                value expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'        b '
'          TableScan'
'            alias: b'
'            GatherStats: false'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: int'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: int'
'              tag: 1'
'              value expressions:'
'                    expr: key'
'                    type: int'
'                    expr: value'
'                    type: int'
'        c '
'          TableScan'
'            alias: c'
'            GatherStats: false'
'            Filter Operator'
'              isSamplingPred: false'
'              predicate:'
'                  expr: (value = 60)'
'                  type: boolean'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: key'
'                      type: int'
'                sort order: +'
'                Map-reduce partition columns:'
'                      expr: key'
'                      type: int'
'                tag: 2'
'                value expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'      Needs Tagging: true'
'      Path -> Alias:'
'        !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a [b, c, a]'
'      Path -> Partition:'
'        !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a '
'          Partition'
'            base file name: a'
'            input format: org.apache.hadoop.mapred.TextInputFormat'
'            output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'            properties:'
'              bucket_count -1'
'              columns key,value'
'              columns.types int:int'
'              file.inputformat org.apache.hadoop.mapred.TextInputFormat'
'              file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'              location !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a'
'              name join_filters_overlap.a'
'              numFiles 1'
'              numPartitions 0'
'              numRows 3'
'              rawDataSize 18'
'              serialization.ddl struct a { i32 key, i32 value}'
'              serialization.format 1'
'              serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'              totalSize 21'
'              transient_lastDdlTime !!UNIXTIME!!'
'            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'          '
'              input format: org.apache.hadoop.mapred.TextInputFormat'
'              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'              properties:'
'                bucket_count -1'
'                columns key,value'
'                columns.types int:int'
'                file.inputformat org.apache.hadoop.mapred.TextInputFormat'
'                file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'                location !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a'
'                name join_filters_overlap.a'
'                numFiles 1'
'                numPartitions 0'
'                numRows 3'
'                rawDataSize 18'
'                serialization.ddl struct a { i32 key, i32 value}'
'                serialization.format 1'
'                serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'                totalSize 21'
'                transient_lastDdlTime !!UNIXTIME!!'
'              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'              name: join_filters_overlap.a'
'            name: join_filters_overlap.a'
'      Reduce Operator Tree:'
'        Join Operator'
'          condition map:'
'               Right Outer Join0 to 1'
'               Left Outer Join1 to 2'
'          condition expressions:'
'            0 {VALUE._col0} {VALUE._col1}'
'            1 {VALUE._col0} {VALUE._col1}'
'            2 {VALUE._col0} {VALUE._col1}'
'          filter mappings:'
'            1 [0, 2, 2, 2]'
'          filter predicates:'
'            0 '
'            1 {(VALUE._col1 = 50)} {(VALUE._col1 > 10)} {(VALUE._col1 = 60)} {(VALUE._col1 > 20)}'
'            2 '
'          handleSkewJoin: false'
'          outputColumnNames: _col0, _col1, _col4, _col5, _col8, _col9'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: int'
'                  expr: _col4'
'                  type: int'
'                  expr: _col5'
'                  type: int'
'                  expr: _col8'
'                  type: int'
'                  expr: _col9'
'                  type: int'
'            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              directory: file:!!{hive.exec.scratchdir}!!'
'              NumFilesPerFileSink: 1'
'              Stats Publishing Key Prefix: file:!!{hive.exec.scratchdir}!!'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'                  properties:'
'                    columns _col0,_col1,_col2,_col3,_col4,_col5'
'                    columns.types int:int:int:int:int:int'
'                    escape.delim \'
'                    serialization.format 1'
'              TotalFiles: 1'
'              GatherStats: false'
'              MultiFileSpray: false'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
180 rows selected 
>>>  select * from a right outer join a b on (a.key=b.key AND a.value=50 AND b.value=50 AND b.value>10) left outer join a c on (b.key=c.key AND b.value=60 AND b.value>20 AND c.value=60);
'key','value','key','value','key','value'
'','','100','40','',''
'100','50','100','50','',''
'','','100','60','100','60'
3 rows selected 
>>>  select /*+ MAPJOIN(a,c)*/ * from a right outer join a b on (a.key=b.key AND a.value=50 AND b.value=50 AND b.value>10) left outer join a c on (b.key=c.key AND b.value=60 AND b.value>20 AND c.value=60);
'key','value','key','value','key','value'
'','','100','40','',''
'100','50','100','50','',''
'','','100','60','100','60'
3 rows selected 
>>>  
>>>  -- overlap on a, b
>>>  explain extended select * from a full outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (b.key=c.key AND b.value=60 AND c.value=60) left outer join a d on (a.key=d.key AND a.value=40 AND d.value=40);
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_LEFTOUTERJOIN (TOK_LEFTOUTERJOIN (TOK_FULLOUTERJOIN (TOK_TABREF (TOK_TABNAME a)) (TOK_TABREF (TOK_TABNAME a) b) (AND (AND (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL b) key)) (= (. (TOK_TABLE_OR_COL a) value) 50)) (= (. (TOK_TABLE_OR_COL b) value) 50))) (TOK_TABREF (TOK_TABNAME a) c) (AND (AND (= (. (TOK_TABLE_OR_COL b) key) (. (TOK_TABLE_OR_COL c) key)) (= (. (TOK_TABLE_OR_COL b) value) 60)) (= (. (TOK_TABLE_OR_COL c) value) 60))) (TOK_TABREF (TOK_TABNAME a) d) (AND (AND (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL d) key)) (= (. (TOK_TABLE_OR_COL a) value) 40)) (= (. (TOK_TABLE_OR_COL d) value) 40)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        a '
'          TableScan'
'            alias: a'
'            GatherStats: false'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: int'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: int'
'              tag: 0'
'              value expressions:'
'                    expr: key'
'                    type: int'
'                    expr: value'
'                    type: int'
'        b '
'          TableScan'
'            alias: b'
'            GatherStats: false'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: int'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: int'
'              tag: 1'
'              value expressions:'
'                    expr: key'
'                    type: int'
'                    expr: value'
'                    type: int'
'        c '
'          TableScan'
'            alias: c'
'            GatherStats: false'
'            Filter Operator'
'              isSamplingPred: false'
'              predicate:'
'                  expr: (value = 60)'
'                  type: boolean'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: key'
'                      type: int'
'                sort order: +'
'                Map-reduce partition columns:'
'                      expr: key'
'                      type: int'
'                tag: 3'
'                value expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'        d '
'          TableScan'
'            alias: d'
'            GatherStats: false'
'            Filter Operator'
'              isSamplingPred: false'
'              predicate:'
'                  expr: (value = 40)'
'                  type: boolean'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: key'
'                      type: int'
'                sort order: +'
'                Map-reduce partition columns:'
'                      expr: key'
'                      type: int'
'                tag: 2'
'                value expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'      Needs Tagging: true'
'      Path -> Alias:'
'        !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a [d, b, c, a]'
'      Path -> Partition:'
'        !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a '
'          Partition'
'            base file name: a'
'            input format: org.apache.hadoop.mapred.TextInputFormat'
'            output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'            properties:'
'              bucket_count -1'
'              columns key,value'
'              columns.types int:int'
'              file.inputformat org.apache.hadoop.mapred.TextInputFormat'
'              file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'              location !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a'
'              name join_filters_overlap.a'
'              numFiles 1'
'              numPartitions 0'
'              numRows 3'
'              rawDataSize 18'
'              serialization.ddl struct a { i32 key, i32 value}'
'              serialization.format 1'
'              serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'              totalSize 21'
'              transient_lastDdlTime !!UNIXTIME!!'
'            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'          '
'              input format: org.apache.hadoop.mapred.TextInputFormat'
'              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'              properties:'
'                bucket_count -1'
'                columns key,value'
'                columns.types int:int'
'                file.inputformat org.apache.hadoop.mapred.TextInputFormat'
'                file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'                location !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a'
'                name join_filters_overlap.a'
'                numFiles 1'
'                numPartitions 0'
'                numRows 3'
'                rawDataSize 18'
'                serialization.ddl struct a { i32 key, i32 value}'
'                serialization.format 1'
'                serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'                totalSize 21'
'                transient_lastDdlTime !!UNIXTIME!!'
'              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'              name: join_filters_overlap.a'
'            name: join_filters_overlap.a'
'      Reduce Operator Tree:'
'        Join Operator'
'          condition map:'
'               Outer Join 0 to 1'
'               Left Outer Join0 to 2'
'               Left Outer Join1 to 3'
'          condition expressions:'
'            0 {VALUE._col0} {VALUE._col1}'
'            1 {VALUE._col0} {VALUE._col1}'
'            2 {VALUE._col0} {VALUE._col1}'
'            3 {VALUE._col0} {VALUE._col1}'
'          filter mappings:'
'            0 [1, 1, 2, 1]'
'            1 [0, 1, 3, 1]'
'          filter predicates:'
'            0 {(VALUE._col1 = 50)} {(VALUE._col1 = 40)}'
'            1 {(VALUE._col1 = 50)} {(VALUE._col1 = 60)}'
'            2 '
'            3 '
'          handleSkewJoin: false'
'          outputColumnNames: _col0, _col1, _col4, _col5, _col8, _col9, _col12, _col13'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: int'
'                  expr: _col4'
'                  type: int'
'                  expr: _col5'
'                  type: int'
'                  expr: _col12'
'                  type: int'
'                  expr: _col13'
'                  type: int'
'                  expr: _col8'
'                  type: int'
'                  expr: _col9'
'                  type: int'
'            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              directory: file:!!{hive.exec.scratchdir}!!'
'              NumFilesPerFileSink: 1'
'              Stats Publishing Key Prefix: file:!!{hive.exec.scratchdir}!!'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'                  properties:'
'                    columns _col0,_col1,_col2,_col3,_col4,_col5,_col6,_col7'
'                    columns.types int:int:int:int:int:int:int:int'
'                    escape.delim \'
'                    serialization.format 1'
'              TotalFiles: 1'
'              GatherStats: false'
'              MultiFileSpray: false'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
206 rows selected 
>>>  select * from a full outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (b.key=c.key AND b.value=60 AND c.value=60) left outer join a d on (a.key=d.key AND a.value=40 AND d.value=40);
'key','value','key','value','key','value','key','value'
'100','40','','','','','100','40'
'','','100','40','','','',''
'100','40','','','','','100','40'
'100','40','','','','','100','40'
'','','100','60','100','60','',''
'100','50','','','','','',''
'','','100','40','','','',''
'100','50','100','50','','','',''
'100','50','','','','','',''
'','','100','60','100','60','',''
'100','60','','','','','',''
'','','100','40','','','',''
'100','60','','','','','',''
'100','60','','','','','',''
'','','100','60','100','60','',''
15 rows selected 
>>>  
>>>  -- triple overlap on a
>>>  explain extended select * from a left outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (a.key=c.key AND a.value=60 AND c.value=60) left outer join a d on (a.key=d.key AND a.value=40 AND d.value=40);
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_LEFTOUTERJOIN (TOK_LEFTOUTERJOIN (TOK_LEFTOUTERJOIN (TOK_TABREF (TOK_TABNAME a)) (TOK_TABREF (TOK_TABNAME a) b) (AND (AND (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL b) key)) (= (. (TOK_TABLE_OR_COL a) value) 50)) (= (. (TOK_TABLE_OR_COL b) value) 50))) (TOK_TABREF (TOK_TABNAME a) c) (AND (AND (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL c) key)) (= (. (TOK_TABLE_OR_COL a) value) 60)) (= (. (TOK_TABLE_OR_COL c) value) 60))) (TOK_TABREF (TOK_TABNAME a) d) (AND (AND (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL d) key)) (= (. (TOK_TABLE_OR_COL a) value) 40)) (= (. (TOK_TABLE_OR_COL d) value) 40)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        a '
'          TableScan'
'            alias: a'
'            GatherStats: false'
'            Reduce Output Operator'
'              key expressions:'
'                    expr: key'
'                    type: int'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: key'
'                    type: int'
'              tag: 0'
'              value expressions:'
'                    expr: key'
'                    type: int'
'                    expr: value'
'                    type: int'
'        b '
'          TableScan'
'            alias: b'
'            GatherStats: false'
'            Filter Operator'
'              isSamplingPred: false'
'              predicate:'
'                  expr: (value = 50)'
'                  type: boolean'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: key'
'                      type: int'
'                sort order: +'
'                Map-reduce partition columns:'
'                      expr: key'
'                      type: int'
'                tag: 1'
'                value expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'        c '
'          TableScan'
'            alias: c'
'            GatherStats: false'
'            Filter Operator'
'              isSamplingPred: false'
'              predicate:'
'                  expr: (value = 60)'
'                  type: boolean'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: key'
'                      type: int'
'                sort order: +'
'                Map-reduce partition columns:'
'                      expr: key'
'                      type: int'
'                tag: 2'
'                value expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'        d '
'          TableScan'
'            alias: d'
'            GatherStats: false'
'            Filter Operator'
'              isSamplingPred: false'
'              predicate:'
'                  expr: (value = 40)'
'                  type: boolean'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: key'
'                      type: int'
'                sort order: +'
'                Map-reduce partition columns:'
'                      expr: key'
'                      type: int'
'                tag: 3'
'                value expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'      Needs Tagging: true'
'      Path -> Alias:'
'        !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a [d, b, c, a]'
'      Path -> Partition:'
'        !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a '
'          Partition'
'            base file name: a'
'            input format: org.apache.hadoop.mapred.TextInputFormat'
'            output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'            properties:'
'              bucket_count -1'
'              columns key,value'
'              columns.types int:int'
'              file.inputformat org.apache.hadoop.mapred.TextInputFormat'
'              file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'              location !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a'
'              name join_filters_overlap.a'
'              numFiles 1'
'              numPartitions 0'
'              numRows 3'
'              rawDataSize 18'
'              serialization.ddl struct a { i32 key, i32 value}'
'              serialization.format 1'
'              serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'              totalSize 21'
'              transient_lastDdlTime !!UNIXTIME!!'
'            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'          '
'              input format: org.apache.hadoop.mapred.TextInputFormat'
'              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'              properties:'
'                bucket_count -1'
'                columns key,value'
'                columns.types int:int'
'                file.inputformat org.apache.hadoop.mapred.TextInputFormat'
'                file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'                location !!{hive.metastore.warehouse.dir}!!/join_filters_overlap.db/a'
'                name join_filters_overlap.a'
'                numFiles 1'
'                numPartitions 0'
'                numRows 3'
'                rawDataSize 18'
'                serialization.ddl struct a { i32 key, i32 value}'
'                serialization.format 1'
'                serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'                totalSize 21'
'                transient_lastDdlTime !!UNIXTIME!!'
'              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
'              name: join_filters_overlap.a'
'            name: join_filters_overlap.a'
'      Reduce Operator Tree:'
'        Join Operator'
'          condition map:'
'               Left Outer Join0 to 1'
'               Left Outer Join0 to 2'
'               Left Outer Join0 to 3'
'          condition expressions:'
'            0 {VALUE._col0} {VALUE._col1}'
'            1 {VALUE._col0} {VALUE._col1}'
'            2 {VALUE._col0} {VALUE._col1}'
'            3 {VALUE._col0} {VALUE._col1}'
'          filter mappings:'
'            0 [1, 1, 2, 1, 3, 1]'
'          filter predicates:'
'            0 {(VALUE._col1 = 50)} {(VALUE._col1 = 60)} {(VALUE._col1 = 40)}'
'            1 '
'            2 '
'            3 '
'          handleSkewJoin: false'
'          outputColumnNames: _col0, _col1, _col4, _col5, _col8, _col9, _col12, _col13'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: int'
'                  expr: _col4'
'                  type: int'
'                  expr: _col5'
'                  type: int'
'                  expr: _col8'
'                  type: int'
'                  expr: _col9'
'                  type: int'
'                  expr: _col12'
'                  type: int'
'                  expr: _col13'
'                  type: int'
'            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              directory: file:!!{hive.exec.scratchdir}!!'
'              NumFilesPerFileSink: 1'
'              Stats Publishing Key Prefix: file:!!{hive.exec.scratchdir}!!'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'                  properties:'
'                    columns _col0,_col1,_col2,_col3,_col4,_col5,_col6,_col7'
'                    columns.types int:int:int:int:int:int:int:int'
'                    escape.delim \'
'                    serialization.format 1'
'              TotalFiles: 1'
'              GatherStats: false'
'              MultiFileSpray: false'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
210 rows selected 
>>>  select * from a left outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (a.key=c.key AND a.value=60 AND c.value=60) left outer join a d on (a.key=d.key AND a.value=40 AND d.value=40);
'key','value','key','value','key','value','key','value'
'100','40','','','','','100','40'
'100','50','100','50','','','',''
'100','60','','','100','60','',''
3 rows selected 
>>>  select /*+ MAPJOIN(b,c, d)*/ * from a left outer join a b on (a.key=b.key AND a.value=50 AND b.value=50) left outer join a c on (a.key=c.key AND a.value=60 AND c.value=60) left outer join a d on (a.key=d.key AND a.value=40 AND d.value=40);
'key','value','key','value','key','value','key','value'
'100','40','','','','','100','40'
'100','50','100','50','','','',''
'100','60','','','100','60','',''
3 rows selected 
>>>  !record