hive-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From hashut...@apache.org
Subject [16/21] hive git commit: HIVE-15192 : Use Calcite to de-correlate and plan subqueries (Vineet Garg via Ashutosh Chauhan)
Date Fri, 16 Dec 2016 18:28:29 GMT
http://git-wip-us.apache.org/repos/asf/hive/blob/382dc208/ql/src/test/results/clientpositive/llap/explainuser_1.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/llap/explainuser_1.q.out b/ql/src/test/results/clientpositive/llap/explainuser_1.q.out
index 70ec02f..fa54bb7 100644
--- a/ql/src/test/results/clientpositive/llap/explainuser_1.q.out
+++ b/ql/src/test/results/clientpositive/llap/explainuser_1.q.out
@@ -1803,46 +1803,71 @@ POSTHOOK: type: QUERY
 Plan optimized by CBO.
 
 Vertex dependency in root stage
-Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
-Reducer 4 <- Map 3 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE)
+Reducer 4 <- Map 3 (SIMPLE_EDGE), Reducer 7 (SIMPLE_EDGE)
+Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
+Reducer 7 <- Map 6 (SIMPLE_EDGE)
 
 Stage-0
   Fetch Operator
     limit:-1
     Stage-1
       Reducer 2 llap
-      File Output Operator [FS_14]
-        Select Operator [SEL_13] (rows=1 width=178)
+      File Output Operator [FS_29]
+        Select Operator [SEL_28] (rows=1 width=178)
           Output:["_col0","_col1"]
-          Filter Operator [FIL_12] (rows=1 width=269)
+          Filter Operator [FIL_27] (rows=1 width=182)
             predicate:_col3 is null
-            Merge Join Operator [MERGEJOIN_17] (rows=500 width=269)
-              Conds:RS_9._col1=RS_10._col1(Left Outer),Output:["_col0","_col1","_col3"]
+            Merge Join Operator [MERGEJOIN_37] (rows=500 width=182)
+              Conds:RS_24._col1=RS_25._col0(Left Outer),Output:["_col0","_col1","_col3"]
             <-Map 1 [SIMPLE_EDGE] llap
-              SHUFFLE [RS_9]
+              SHUFFLE [RS_24]
                 PartitionCols:_col1
                 Select Operator [SEL_1] (rows=500 width=178)
                   Output:["_col0","_col1"]
                   TableScan [TS_0] (rows=500 width=178)
                     default@src_cbo,b,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
-            <-Reducer 4 [SIMPLE_EDGE] llap
-              SHUFFLE [RS_10]
-                PartitionCols:_col1
-                Select Operator [SEL_8] (rows=83 width=178)
-                  Output:["_col1"]
-                  Group By Operator [GBY_7] (rows=83 width=178)
-                    Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
-                  <-Map 3 [SIMPLE_EDGE] llap
-                    SHUFFLE [RS_6]
-                      PartitionCols:_col0, _col1
-                      Group By Operator [GBY_5] (rows=83 width=178)
-                        Output:["_col0","_col1"],keys:value, key
-                        Select Operator [SEL_4] (rows=166 width=178)
-                          Output:["value","key"]
-                          Filter Operator [FIL_16] (rows=166 width=178)
-                            predicate:(value > 'val_2')
-                            TableScan [TS_2] (rows=500 width=178)
-                              default@src_cbo,a,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
+            <-Reducer 5 [SIMPLE_EDGE] llap
+              SHUFFLE [RS_25]
+                PartitionCols:_col0
+                Select Operator [SEL_23] (rows=56 width=95)
+                  Output:["_col0","_col1"]
+                  Group By Operator [GBY_22] (rows=56 width=91)
+                    Output:["_col0"],keys:_col1
+                    Select Operator [SEL_18] (rows=83 width=178)
+                      Output:["_col1"]
+                      Group By Operator [GBY_17] (rows=83 width=178)
+                        Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+                      <-Reducer 4 [SIMPLE_EDGE] llap
+                        SHUFFLE [RS_16]
+                          PartitionCols:_col0
+                          Group By Operator [GBY_15] (rows=83 width=178)
+                            Output:["_col0","_col1"],keys:_col2, _col0
+                            Select Operator [SEL_14] (rows=166 width=178)
+                              Output:["_col2","_col0"]
+                              Merge Join Operator [MERGEJOIN_36] (rows=166 width=178)
+                                Conds:RS_11._col1=RS_12._col0(Inner),Output:["_col0","_col2"]
+                              <-Map 3 [SIMPLE_EDGE] llap
+                                SHUFFLE [RS_11]
+                                  PartitionCols:_col1
+                                  Select Operator [SEL_4] (rows=166 width=178)
+                                    Output:["_col0","_col1"]
+                                    Filter Operator [FIL_34] (rows=166 width=178)
+                                      predicate:(value > 'val_2')
+                                      TableScan [TS_2] (rows=500 width=178)
+                                        default@src_cbo,a,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
+                              <-Reducer 7 [SIMPLE_EDGE] llap
+                                SHUFFLE [RS_12]
+                                  PartitionCols:_col0
+                                  Group By Operator [GBY_9] (rows=214 width=91)
+                                    Output:["_col0"],keys:KEY._col0
+                                  <-Map 6 [SIMPLE_EDGE] llap
+                                    SHUFFLE [RS_8]
+                                      PartitionCols:_col0
+                                      Group By Operator [GBY_7] (rows=214 width=91)
+                                        Output:["_col0"],keys:value
+                                        TableScan [TS_5] (rows=500 width=91)
+                                          default@src_cbo,b,Tbl:COMPLETE,Col:COMPLETE,Output:["value"]
 
 PREHOOK: query: explain select * 
 from src_cbo b 
@@ -1866,31 +1891,25 @@ Plan optimized by CBO.
 
 Vertex dependency in root stage
 Reducer 2 <- Map 1 (SIMPLE_EDGE)
-Reducer 3 <- Map 4 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 2 (SIMPLE_EDGE), Reducer 6 (SIMPLE_EDGE)
+Reducer 5 <- Map 4 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE)
+Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
+Reducer 8 <- Map 7 (SIMPLE_EDGE)
 
 Stage-0
   Fetch Operator
     limit:-1
     Stage-1
       Reducer 3 llap
-      File Output Operator [FS_14]
-        Select Operator [SEL_13] (rows=1 width=178)
+      File Output Operator [FS_33]
+        Select Operator [SEL_32] (rows=1 width=178)
           Output:["_col0","_col1"]
-          Filter Operator [FIL_12] (rows=1 width=265)
-            predicate:_col3 is null
-            Merge Join Operator [MERGEJOIN_17] (rows=250 width=265)
-              Conds:RS_9._col0, _col1=RS_10._col1, _col0(Left Outer),Output:["_col0","_col1","_col3"]
-            <-Map 4 [SIMPLE_EDGE] llap
-              SHUFFLE [RS_10]
-                PartitionCols:_col1, _col0
-                Select Operator [SEL_8] (rows=166 width=178)
-                  Output:["_col0","_col1"]
-                  Filter Operator [FIL_16] (rows=166 width=178)
-                    predicate:(value > 'val_12')
-                    TableScan [TS_6] (rows=500 width=178)
-                      default@src_cbo,a,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
+          Filter Operator [FIL_31] (rows=1 width=182)
+            predicate:_col4 is null
+            Merge Join Operator [MERGEJOIN_41] (rows=250 width=182)
+              Conds:RS_28._col0, _col1=RS_29._col0, _col1(Left Outer),Output:["_col0","_col1","_col4"]
             <-Reducer 2 [SIMPLE_EDGE] llap
-              SHUFFLE [RS_9]
+              SHUFFLE [RS_28]
                 PartitionCols:_col0, _col1
                 Group By Operator [GBY_4] (rows=250 width=178)
                   Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
@@ -1903,6 +1922,43 @@ Stage-0
                         Output:["key","value"]
                         TableScan [TS_0] (rows=500 width=178)
                           default@src_cbo,b,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
+            <-Reducer 6 [SIMPLE_EDGE] llap
+              SHUFFLE [RS_29]
+                PartitionCols:_col0, _col1
+                Select Operator [SEL_27] (rows=1 width=182)
+                  Output:["_col0","_col1","_col2"]
+                  Group By Operator [GBY_26] (rows=1 width=178)
+                    Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+                  <-Reducer 5 [SIMPLE_EDGE] llap
+                    SHUFFLE [RS_25]
+                      PartitionCols:_col0, _col1
+                      Group By Operator [GBY_24] (rows=1 width=178)
+                        Output:["_col0","_col1"],keys:_col2, _col3
+                        Merge Join Operator [MERGEJOIN_40] (rows=1 width=178)
+                          Conds:RS_20._col0, _col1=RS_21._col0, _col1(Inner),Output:["_col2","_col3"]
+                        <-Map 4 [SIMPLE_EDGE] llap
+                          SHUFFLE [RS_20]
+                            PartitionCols:_col0, _col1
+                            Select Operator [SEL_8] (rows=166 width=178)
+                              Output:["_col0","_col1"]
+                              Filter Operator [FIL_38] (rows=166 width=178)
+                                predicate:(value > 'val_12')
+                                TableScan [TS_6] (rows=500 width=178)
+                                  default@src_cbo,a,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
+                        <-Reducer 8 [SIMPLE_EDGE] llap
+                          SHUFFLE [RS_21]
+                            PartitionCols:_col0, _col1
+                            Group By Operator [GBY_18] (rows=250 width=178)
+                              Output:["_col0","_col1"],keys:_col0, _col1
+                              Group By Operator [GBY_13] (rows=250 width=178)
+                                Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+                              <-Map 7 [SIMPLE_EDGE] llap
+                                SHUFFLE [RS_12]
+                                  PartitionCols:_col0, _col1
+                                  Group By Operator [GBY_11] (rows=250 width=178)
+                                    Output:["_col0","_col1"],keys:key, value
+                                    TableScan [TS_9] (rows=500 width=178)
+                                      default@src_cbo,b,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
 
 PREHOOK: query: create view cv1 as 
 select * 
@@ -1933,36 +1989,59 @@ POSTHOOK: type: QUERY
 Plan optimized by CBO.
 
 Vertex dependency in root stage
-Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 3 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE)
+Reducer 4 <- Map 3 (SIMPLE_EDGE), Reducer 7 (SIMPLE_EDGE)
+Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
+Reducer 7 <- Map 6 (SIMPLE_EDGE)
 
 Stage-0
   Fetch Operator
     limit:-1
     Stage-1
       Reducer 2 llap
-      File Output Operator [FS_12]
-        Merge Join Operator [MERGEJOIN_17] (rows=2 width=178)
-          Conds:RS_8._col0, _col1=RS_9._col0, _col1(Left Semi),Output:["_col0","_col1"]
+      File Output Operator [FS_23]
+        Merge Join Operator [MERGEJOIN_33] (rows=1 width=178)
+          Conds:RS_19._col0, _col1=RS_20._col0, _col1(Inner),Output:["_col0","_col1"]
         <-Map 1 [SIMPLE_EDGE] llap
-          SHUFFLE [RS_8]
+          SHUFFLE [RS_19]
             PartitionCols:_col0, _col1
-            Select Operator [SEL_2] (rows=166 width=178)
+            Select Operator [SEL_1] (rows=500 width=178)
               Output:["_col0","_col1"]
-              Filter Operator [FIL_15] (rows=166 width=178)
-                predicate:((value > 'val_9') and key is not null)
-                TableScan [TS_0] (rows=500 width=178)
-                  default@src_cbo,b,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"],properties:{"insideView":"TRUE"}
-        <-Map 3 [SIMPLE_EDGE] llap
-          SHUFFLE [RS_9]
+              TableScan [TS_0] (rows=500 width=178)
+                default@src_cbo,b,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"],properties:{"insideView":"TRUE"}
+        <-Reducer 5 [SIMPLE_EDGE] llap
+          SHUFFLE [RS_20]
             PartitionCols:_col0, _col1
-            Group By Operator [GBY_7] (rows=83 width=178)
-              Output:["_col0","_col1"],keys:_col0, _col1
-              Select Operator [SEL_5] (rows=166 width=178)
-                Output:["_col0","_col1"]
-                Filter Operator [FIL_16] (rows=166 width=178)
-                  predicate:((value > 'val_9') and key is not null)
-                  TableScan [TS_3] (rows=500 width=178)
-                    default@src_cbo,a,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
+            Group By Operator [GBY_17] (rows=1 width=178)
+              Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+            <-Reducer 4 [SIMPLE_EDGE] llap
+              SHUFFLE [RS_16]
+                PartitionCols:_col0, _col1
+                Group By Operator [GBY_15] (rows=1 width=178)
+                  Output:["_col0","_col1"],keys:_col2, _col3
+                  Merge Join Operator [MERGEJOIN_32] (rows=1 width=178)
+                    Conds:RS_11._col0, _col1=RS_12._col0, _col1(Inner),Output:["_col2","_col3"]
+                  <-Map 3 [SIMPLE_EDGE] llap
+                    SHUFFLE [RS_11]
+                      PartitionCols:_col0, _col1
+                      Select Operator [SEL_4] (rows=166 width=178)
+                        Output:["_col0","_col1"]
+                        Filter Operator [FIL_30] (rows=166 width=178)
+                          predicate:(value > 'val_9')
+                          TableScan [TS_2] (rows=500 width=178)
+                            default@src_cbo,a,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
+                  <-Reducer 7 [SIMPLE_EDGE] llap
+                    SHUFFLE [RS_12]
+                      PartitionCols:_col0, _col1
+                      Group By Operator [GBY_9] (rows=250 width=178)
+                        Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+                      <-Map 6 [SIMPLE_EDGE] llap
+                        SHUFFLE [RS_8]
+                          PartitionCols:_col0, _col1
+                          Group By Operator [GBY_7] (rows=250 width=178)
+                            Output:["_col0","_col1"],keys:key, value
+                            TableScan [TS_5] (rows=500 width=178)
+                              default@src_cbo,b,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"],properties:{"insideView":"TRUE"}
 
 PREHOOK: query: explain select * 
 from (select * 
@@ -1985,36 +2064,59 @@ POSTHOOK: type: QUERY
 Plan optimized by CBO.
 
 Vertex dependency in root stage
-Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 3 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE)
+Reducer 4 <- Map 3 (SIMPLE_EDGE), Reducer 7 (SIMPLE_EDGE)
+Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
+Reducer 7 <- Map 6 (SIMPLE_EDGE)
 
 Stage-0
   Fetch Operator
     limit:-1
     Stage-1
       Reducer 2 llap
-      File Output Operator [FS_12]
-        Merge Join Operator [MERGEJOIN_17] (rows=2 width=178)
-          Conds:RS_8._col0, _col1=RS_9._col0, _col1(Left Semi),Output:["_col0","_col1"]
+      File Output Operator [FS_23]
+        Merge Join Operator [MERGEJOIN_33] (rows=1 width=178)
+          Conds:RS_19._col0, _col1=RS_20._col0, _col1(Inner),Output:["_col0","_col1"]
         <-Map 1 [SIMPLE_EDGE] llap
-          SHUFFLE [RS_8]
+          SHUFFLE [RS_19]
             PartitionCols:_col0, _col1
-            Select Operator [SEL_2] (rows=166 width=178)
+            Select Operator [SEL_1] (rows=500 width=178)
               Output:["_col0","_col1"]
-              Filter Operator [FIL_15] (rows=166 width=178)
-                predicate:((value > 'val_9') and key is not null)
-                TableScan [TS_0] (rows=500 width=178)
-                  default@src_cbo,b,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
-        <-Map 3 [SIMPLE_EDGE] llap
-          SHUFFLE [RS_9]
+              TableScan [TS_0] (rows=500 width=178)
+                default@src_cbo,b,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
+        <-Reducer 5 [SIMPLE_EDGE] llap
+          SHUFFLE [RS_20]
             PartitionCols:_col0, _col1
-            Group By Operator [GBY_7] (rows=83 width=178)
-              Output:["_col0","_col1"],keys:_col0, _col1
-              Select Operator [SEL_5] (rows=166 width=178)
-                Output:["_col0","_col1"]
-                Filter Operator [FIL_16] (rows=166 width=178)
-                  predicate:((value > 'val_9') and key is not null)
-                  TableScan [TS_3] (rows=500 width=178)
-                    default@src_cbo,a,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
+            Group By Operator [GBY_17] (rows=1 width=178)
+              Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+            <-Reducer 4 [SIMPLE_EDGE] llap
+              SHUFFLE [RS_16]
+                PartitionCols:_col0, _col1
+                Group By Operator [GBY_15] (rows=1 width=178)
+                  Output:["_col0","_col1"],keys:_col2, _col3
+                  Merge Join Operator [MERGEJOIN_32] (rows=1 width=178)
+                    Conds:RS_11._col0, _col1=RS_12._col0, _col1(Inner),Output:["_col2","_col3"]
+                  <-Map 3 [SIMPLE_EDGE] llap
+                    SHUFFLE [RS_11]
+                      PartitionCols:_col0, _col1
+                      Select Operator [SEL_4] (rows=166 width=178)
+                        Output:["_col0","_col1"]
+                        Filter Operator [FIL_30] (rows=166 width=178)
+                          predicate:(value > 'val_9')
+                          TableScan [TS_2] (rows=500 width=178)
+                            default@src_cbo,a,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
+                  <-Reducer 7 [SIMPLE_EDGE] llap
+                    SHUFFLE [RS_12]
+                      PartitionCols:_col0, _col1
+                      Group By Operator [GBY_9] (rows=250 width=178)
+                        Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+                      <-Map 6 [SIMPLE_EDGE] llap
+                        SHUFFLE [RS_8]
+                          PartitionCols:_col0, _col1
+                          Group By Operator [GBY_7] (rows=250 width=178)
+                            Output:["_col0","_col1"],keys:key, value
+                            TableScan [TS_5] (rows=500 width=178)
+                              default@src_cbo,b,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
 
 PREHOOK: query: explain select * 
 from src_cbo 
@@ -2027,36 +2129,38 @@ POSTHOOK: type: QUERY
 Plan optimized by CBO.
 
 Vertex dependency in root stage
-Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 3 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
+Reducer 4 <- Map 3 (SIMPLE_EDGE)
 
 Stage-0
   Fetch Operator
     limit:-1
     Stage-1
       Reducer 2 llap
-      File Output Operator [FS_12]
-        Merge Join Operator [MERGEJOIN_17] (rows=166 width=178)
-          Conds:RS_8._col0=RS_9._col0(Left Semi),Output:["_col0","_col1"]
+      File Output Operator [FS_13]
+        Merge Join Operator [MERGEJOIN_18] (rows=168 width=178)
+          Conds:RS_9._col0=RS_10._col0(Inner),Output:["_col0","_col1"]
         <-Map 1 [SIMPLE_EDGE] llap
-          SHUFFLE [RS_8]
+          SHUFFLE [RS_9]
             PartitionCols:_col0
-            Select Operator [SEL_2] (rows=166 width=178)
+            Select Operator [SEL_1] (rows=500 width=178)
               Output:["_col0","_col1"]
-              Filter Operator [FIL_15] (rows=166 width=178)
-                predicate:(key > '9')
-                TableScan [TS_0] (rows=500 width=178)
-                  default@src_cbo,src_cbo,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
-        <-Map 3 [SIMPLE_EDGE] llap
-          SHUFFLE [RS_9]
+              TableScan [TS_0] (rows=500 width=178)
+                default@src_cbo,src_cbo,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
+        <-Reducer 4 [SIMPLE_EDGE] llap
+          SHUFFLE [RS_10]
             PartitionCols:_col0
             Group By Operator [GBY_7] (rows=69 width=87)
-              Output:["_col0"],keys:_col0
-              Select Operator [SEL_5] (rows=166 width=87)
-                Output:["_col0"]
-                Filter Operator [FIL_16] (rows=166 width=87)
-                  predicate:(key > '9')
-                  TableScan [TS_3] (rows=500 width=87)
-                    default@src_cbo,s1,Tbl:COMPLETE,Col:COMPLETE,Output:["key"]
+              Output:["_col0"],keys:KEY._col0
+            <-Map 3 [SIMPLE_EDGE] llap
+              SHUFFLE [RS_6]
+                PartitionCols:_col0
+                Group By Operator [GBY_5] (rows=69 width=87)
+                  Output:["_col0"],keys:key
+                  Filter Operator [FIL_17] (rows=166 width=87)
+                    predicate:(key > '9')
+                    TableScan [TS_2] (rows=500 width=87)
+                      default@src_cbo,s1,Tbl:COMPLETE,Col:COMPLETE,Output:["key"]
 
 PREHOOK: query: explain select p.p_partkey, li.l_suppkey 
 from (select distinct l_partkey as p_partkey from lineitem) p join lineitem li on p.p_partkey = li.l_partkey 
@@ -2071,59 +2175,109 @@ POSTHOOK: type: QUERY
 Plan optimized by CBO.
 
 Vertex dependency in root stage
-Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 4 (SIMPLE_EDGE)
-Reducer 3 <- Reducer 2 (SIMPLE_EDGE), Reducer 6 (SIMPLE_EDGE)
-Reducer 6 <- Map 5 (SIMPLE_EDGE)
+Reducer 10 <- Map 9 (SIMPLE_EDGE)
+Reducer 11 <- Map 13 (SIMPLE_EDGE), Reducer 10 (SIMPLE_EDGE)
+Reducer 12 <- Reducer 11 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE)
+Reducer 3 <- Map 5 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 3 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE)
+Reducer 7 <- Map 6 (SIMPLE_EDGE), Reducer 12 (SIMPLE_EDGE)
+Reducer 8 <- Reducer 7 (SIMPLE_EDGE)
 
 Stage-0
   Fetch Operator
     limit:-1
     Stage-1
-      Reducer 3 llap
-      File Output Operator [FS_22]
-        Select Operator [SEL_21] (rows=4 width=8)
+      Reducer 4 llap
+      File Output Operator [FS_46]
+        Select Operator [SEL_45] (rows=5 width=8)
           Output:["_col0","_col1"]
-          Merge Join Operator [MERGEJOIN_32] (rows=4 width=8)
-            Conds:RS_18._col1=RS_19._col0(Inner),Output:["_col2","_col4"]
-          <-Reducer 2 [SIMPLE_EDGE] llap
-            SHUFFLE [RS_18]
-              PartitionCols:_col1
-              Merge Join Operator [MERGEJOIN_31] (rows=13 width=8)
-                Conds:RS_15._col0, 1=RS_16._col0, _col1(Left Semi),Output:["_col1","_col2"]
-              <-Map 1 [SIMPLE_EDGE] llap
-                SHUFFLE [RS_15]
-                  PartitionCols:_col0, 1
-                  Select Operator [SEL_2] (rows=17 width=16)
-                    Output:["_col0","_col1","_col2"]
-                    Filter Operator [FIL_28] (rows=17 width=16)
-                      predicate:((l_linenumber = 1) and l_partkey is not null and l_orderkey is not null)
-                      TableScan [TS_0] (rows=100 width=16)
-                        default@lineitem,li,Tbl:COMPLETE,Col:COMPLETE,Output:["l_orderkey","l_partkey","l_suppkey","l_linenumber"]
-              <-Map 4 [SIMPLE_EDGE] llap
-                SHUFFLE [RS_16]
-                  PartitionCols:_col0, _col1
-                  Group By Operator [GBY_14] (rows=4 width=8)
-                    Output:["_col0","_col1"],keys:_col0, _col1
-                    Select Operator [SEL_5] (rows=14 width=8)
-                      Output:["_col0","_col1"]
-                      Filter Operator [FIL_29] (rows=14 width=96)
-                        predicate:((l_shipmode = 'AIR') and (l_linenumber = 1) and l_orderkey is not null)
-                        TableScan [TS_3] (rows=100 width=96)
-                          default@lineitem,lineitem,Tbl:COMPLETE,Col:COMPLETE,Output:["l_orderkey","l_linenumber","l_shipmode"]
-          <-Reducer 6 [SIMPLE_EDGE] llap
-            SHUFFLE [RS_19]
-              PartitionCols:_col0
-              Group By Operator [GBY_11] (rows=50 width=4)
-                Output:["_col0"],keys:KEY._col0
+          Merge Join Operator [MERGEJOIN_67] (rows=5 width=8)
+            Conds:RS_42._col1, _col4=RS_43._col0, _col1(Inner),Output:["_col0","_col3"]
+          <-Reducer 3 [SIMPLE_EDGE] llap
+            SHUFFLE [RS_42]
+              PartitionCols:_col1, _col4
+              Merge Join Operator [MERGEJOIN_64] (rows=5 width=16)
+                Conds:RS_39._col0=RS_40._col1(Inner),Output:["_col0","_col1","_col3","_col4"]
               <-Map 5 [SIMPLE_EDGE] llap
-                SHUFFLE [RS_10]
+                SHUFFLE [RS_40]
+                  PartitionCols:_col1
+                  Select Operator [SEL_9] (rows=17 width=16)
+                    Output:["_col0","_col1","_col2","_col3"]
+                    Filter Operator [FIL_60] (rows=17 width=16)
+                      predicate:((l_linenumber = 1) and l_partkey is not null)
+                      TableScan [TS_7] (rows=100 width=16)
+                        default@lineitem,li,Tbl:COMPLETE,Col:COMPLETE,Output:["l_orderkey","l_partkey","l_suppkey","l_linenumber"]
+              <-Reducer 2 [SIMPLE_EDGE] llap
+                SHUFFLE [RS_39]
                   PartitionCols:_col0
-                  Group By Operator [GBY_9] (rows=50 width=4)
-                    Output:["_col0"],keys:l_partkey
-                    Filter Operator [FIL_30] (rows=100 width=4)
-                      predicate:l_partkey is not null
-                      TableScan [TS_6] (rows=100 width=4)
-                        default@lineitem,lineitem,Tbl:COMPLETE,Col:COMPLETE,Output:["l_partkey"]
+                  Group By Operator [GBY_5] (rows=50 width=4)
+                    Output:["_col0"],keys:KEY._col0
+                  <-Map 1 [SIMPLE_EDGE] llap
+                    SHUFFLE [RS_4]
+                      PartitionCols:_col0
+                      Group By Operator [GBY_3] (rows=50 width=4)
+                        Output:["_col0"],keys:l_partkey
+                        Filter Operator [FIL_59] (rows=100 width=4)
+                          predicate:l_partkey is not null
+                          TableScan [TS_0] (rows=100 width=4)
+                            default@lineitem,lineitem,Tbl:COMPLETE,Col:COMPLETE,Output:["l_partkey"]
+          <-Reducer 8 [SIMPLE_EDGE] llap
+            SHUFFLE [RS_43]
+              PartitionCols:_col0, _col1
+              Group By Operator [GBY_37] (rows=4 width=8)
+                Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+              <-Reducer 7 [SIMPLE_EDGE] llap
+                SHUFFLE [RS_36]
+                  PartitionCols:_col0, _col1
+                  Group By Operator [GBY_35] (rows=4 width=8)
+                    Output:["_col0","_col1"],keys:_col0, _col3
+                    Merge Join Operator [MERGEJOIN_66] (rows=14 width=8)
+                      Conds:RS_31._col1=RS_32._col0(Inner),Output:["_col0","_col3"]
+                    <-Map 6 [SIMPLE_EDGE] llap
+                      SHUFFLE [RS_31]
+                        PartitionCols:_col1
+                        Select Operator [SEL_12] (rows=14 width=95)
+                          Output:["_col0","_col1"]
+                          Filter Operator [FIL_61] (rows=14 width=96)
+                            predicate:(l_shipmode = 'AIR')
+                            TableScan [TS_10] (rows=100 width=96)
+                              default@lineitem,lineitem,Tbl:COMPLETE,Col:COMPLETE,Output:["l_orderkey","l_linenumber","l_shipmode"]
+                    <-Reducer 12 [SIMPLE_EDGE] llap
+                      SHUFFLE [RS_32]
+                        PartitionCols:_col0
+                        Group By Operator [GBY_29] (rows=3 width=4)
+                          Output:["_col0"],keys:KEY._col0
+                        <-Reducer 11 [SIMPLE_EDGE] llap
+                          SHUFFLE [RS_28]
+                            PartitionCols:_col0
+                            Group By Operator [GBY_27] (rows=3 width=4)
+                              Output:["_col0"],keys:_col2
+                              Merge Join Operator [MERGEJOIN_65] (rows=34 width=4)
+                                Conds:RS_23._col0=RS_24._col0(Inner),Output:["_col2"]
+                              <-Map 13 [SIMPLE_EDGE] llap
+                                SHUFFLE [RS_24]
+                                  PartitionCols:_col0
+                                  Select Operator [SEL_22] (rows=100 width=8)
+                                    Output:["_col0","_col1"]
+                                    Filter Operator [FIL_63] (rows=100 width=8)
+                                      predicate:l_partkey is not null
+                                      TableScan [TS_20] (rows=100 width=8)
+                                        default@lineitem,li,Tbl:COMPLETE,Col:COMPLETE,Output:["l_partkey","l_linenumber"]
+                              <-Reducer 10 [SIMPLE_EDGE] llap
+                                SHUFFLE [RS_23]
+                                  PartitionCols:_col0
+                                  Group By Operator [GBY_18] (rows=50 width=4)
+                                    Output:["_col0"],keys:KEY._col0
+                                  <-Map 9 [SIMPLE_EDGE] llap
+                                    SHUFFLE [RS_17]
+                                      PartitionCols:_col0
+                                      Group By Operator [GBY_16] (rows=50 width=4)
+                                        Output:["_col0"],keys:l_partkey
+                                        Filter Operator [FIL_62] (rows=100 width=4)
+                                          predicate:l_partkey is not null
+                                          TableScan [TS_13] (rows=100 width=4)
+                                            default@lineitem,lineitem,Tbl:COMPLETE,Col:COMPLETE,Output:["l_partkey"]
 
 PREHOOK: query: explain select key, value, count(*) 
 from src_cbo b
@@ -2140,72 +2294,74 @@ POSTHOOK: type: QUERY
 Plan optimized by CBO.
 
 Vertex dependency in root stage
-Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 5 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 6 (SIMPLE_EDGE)
 Reducer 3 <- Reducer 2 (SIMPLE_EDGE)
-Reducer 4 <- Reducer 3 (SIMPLE_EDGE), Reducer 7 (SIMPLE_EDGE)
-Reducer 7 <- Map 6 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 3 (SIMPLE_EDGE), Reducer 9 (SIMPLE_EDGE)
+Reducer 6 <- Map 5 (SIMPLE_EDGE)
+Reducer 8 <- Map 7 (SIMPLE_EDGE)
+Reducer 9 <- Reducer 8 (SIMPLE_EDGE)
 
 Stage-0
   Fetch Operator
     limit:-1
     Stage-1
       Reducer 4 llap
-      File Output Operator [FS_31]
-        Merge Join Operator [MERGEJOIN_44] (rows=34 width=186)
-          Conds:RS_27._col2=RS_28._col0(Left Semi),Output:["_col0","_col1","_col2"]
+      File Output Operator [FS_33]
+        Merge Join Operator [MERGEJOIN_46] (rows=34 width=186)
+          Conds:RS_29._col2=RS_30._col0(Inner),Output:["_col0","_col1","_col2"]
         <-Reducer 3 [SIMPLE_EDGE] llap
-          SHUFFLE [RS_27]
+          SHUFFLE [RS_29]
             PartitionCols:_col2
-            Filter Operator [FIL_37] (rows=83 width=186)
-              predicate:_col2 is not null
-              Group By Operator [GBY_14] (rows=83 width=186)
-                Output:["_col0","_col1","_col2"],aggregations:["count(VALUE._col0)"],keys:KEY._col0, KEY._col1
-              <-Reducer 2 [SIMPLE_EDGE] llap
-                SHUFFLE [RS_13]
-                  PartitionCols:_col0, _col1
-                  Group By Operator [GBY_12] (rows=83 width=186)
-                    Output:["_col0","_col1","_col2"],aggregations:["count()"],keys:_col0, _col1
-                    Merge Join Operator [MERGEJOIN_43] (rows=166 width=178)
-                      Conds:RS_8._col0=RS_9._col0(Left Semi),Output:["_col0","_col1"]
-                    <-Map 1 [SIMPLE_EDGE] llap
-                      SHUFFLE [RS_8]
-                        PartitionCols:_col0
-                        Select Operator [SEL_2] (rows=166 width=178)
-                          Output:["_col0","_col1"]
-                          Filter Operator [FIL_38] (rows=166 width=178)
-                            predicate:(key > '8')
-                            TableScan [TS_0] (rows=500 width=178)
-                              default@src_cbo,b,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
-                    <-Map 5 [SIMPLE_EDGE] llap
-                      SHUFFLE [RS_9]
-                        PartitionCols:_col0
-                        Group By Operator [GBY_7] (rows=69 width=87)
-                          Output:["_col0"],keys:_col0
-                          Select Operator [SEL_5] (rows=166 width=87)
-                            Output:["_col0"]
-                            Filter Operator [FIL_39] (rows=166 width=87)
+            Group By Operator [GBY_15] (rows=84 width=186)
+              Output:["_col0","_col1","_col2"],aggregations:["count(VALUE._col0)"],keys:KEY._col0, KEY._col1
+            <-Reducer 2 [SIMPLE_EDGE] llap
+              SHUFFLE [RS_14]
+                PartitionCols:_col0, _col1
+                Group By Operator [GBY_13] (rows=84 width=186)
+                  Output:["_col0","_col1","_col2"],aggregations:["count()"],keys:_col0, _col1
+                  Merge Join Operator [MERGEJOIN_45] (rows=168 width=178)
+                    Conds:RS_9._col0=RS_10._col0(Inner),Output:["_col0","_col1"]
+                  <-Map 1 [SIMPLE_EDGE] llap
+                    SHUFFLE [RS_9]
+                      PartitionCols:_col0
+                      Select Operator [SEL_1] (rows=500 width=178)
+                        Output:["_col0","_col1"]
+                        TableScan [TS_0] (rows=500 width=178)
+                          default@src_cbo,b,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
+                  <-Reducer 6 [SIMPLE_EDGE] llap
+                    SHUFFLE [RS_10]
+                      PartitionCols:_col0
+                      Group By Operator [GBY_7] (rows=69 width=87)
+                        Output:["_col0"],keys:KEY._col0
+                      <-Map 5 [SIMPLE_EDGE] llap
+                        SHUFFLE [RS_6]
+                          PartitionCols:_col0
+                          Group By Operator [GBY_5] (rows=69 width=87)
+                            Output:["_col0"],keys:key
+                            Filter Operator [FIL_41] (rows=166 width=87)
                               predicate:(key > '8')
-                              TableScan [TS_3] (rows=500 width=87)
+                              TableScan [TS_2] (rows=500 width=87)
                                 default@src_cbo,src_cbo,Tbl:COMPLETE,Col:COMPLETE,Output:["key"]
-        <-Reducer 7 [SIMPLE_EDGE] llap
-          SHUFFLE [RS_28]
+        <-Reducer 9 [SIMPLE_EDGE] llap
+          SHUFFLE [RS_30]
             PartitionCols:_col0
-            Group By Operator [GBY_26] (rows=34 width=8)
-              Output:["_col0"],keys:_col0
-              Select Operator [SEL_24] (rows=69 width=8)
-                Output:["_col0"]
-                Filter Operator [FIL_40] (rows=69 width=8)
-                  predicate:_col1 is not null
-                  Select Operator [SEL_42] (rows=69 width=8)
+            Group By Operator [GBY_27] (rows=34 width=8)
+              Output:["_col0"],keys:KEY._col0
+            <-Reducer 8 [SIMPLE_EDGE] llap
+              SHUFFLE [RS_26]
+                PartitionCols:_col0
+                Group By Operator [GBY_25] (rows=34 width=8)
+                  Output:["_col0"],keys:_col1
+                  Select Operator [SEL_44] (rows=69 width=8)
                     Output:["_col1"]
                     Group By Operator [GBY_22] (rows=69 width=95)
                       Output:["_col0","_col1"],aggregations:["count(VALUE._col0)"],keys:KEY._col0
-                    <-Map 6 [SIMPLE_EDGE] llap
+                    <-Map 7 [SIMPLE_EDGE] llap
                       SHUFFLE [RS_21]
                         PartitionCols:_col0
                         Group By Operator [GBY_20] (rows=69 width=95)
                           Output:["_col0","_col1"],aggregations:["count()"],keys:key
-                          Filter Operator [FIL_41] (rows=166 width=87)
+                          Filter Operator [FIL_43] (rows=166 width=87)
                             predicate:(key > '9')
                             TableScan [TS_17] (rows=500 width=87)
                               default@src_cbo,s1,Tbl:COMPLETE,Col:COMPLETE,Output:["key"]
@@ -2226,8 +2382,9 @@ Plan optimized by CBO.
 
 Vertex dependency in root stage
 Reducer 2 <- Map 1 (SIMPLE_EDGE)
-Reducer 3 <- Reducer 2 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 2 (SIMPLE_EDGE), Reducer 6 (SIMPLE_EDGE)
 Reducer 5 <- Map 4 (SIMPLE_EDGE)
+Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
 
 Stage-0
   Fetch Operator
@@ -2236,41 +2393,42 @@ Stage-0
       Reducer 3 llap
       File Output Operator [FS_21]
         Merge Join Operator [MERGEJOIN_26] (rows=6 width=227)
-          Conds:RS_17._col1=RS_18._col0(Left Semi),Output:["_col0","_col1","_col2"]
+          Conds:RS_17._col1=RS_18._col0(Inner),Output:["_col0","_col1","_col2"]
         <-Reducer 2 [SIMPLE_EDGE] llap
           SHUFFLE [RS_17]
             PartitionCols:_col1
-            Select Operator [SEL_6] (rows=13 width=227)
+            Select Operator [SEL_5] (rows=13 width=227)
               Output:["_col0","_col1","_col2"]
-              Group By Operator [GBY_5] (rows=13 width=227)
+              Group By Operator [GBY_4] (rows=13 width=227)
                 Output:["_col0","_col1","_col2"],aggregations:["avg(VALUE._col0)"],keys:KEY._col0, KEY._col1
               <-Map 1 [SIMPLE_EDGE] llap
-                SHUFFLE [RS_4]
+                SHUFFLE [RS_3]
                   PartitionCols:_col0, _col1
-                  Group By Operator [GBY_3] (rows=13 width=295)
+                  Group By Operator [GBY_2] (rows=13 width=295)
                     Output:["_col0","_col1","_col2"],aggregations:["avg(p_size)"],keys:p_name, p_mfgr
-                    Filter Operator [FIL_24] (rows=26 width=223)
-                      predicate:p_name is not null
-                      TableScan [TS_0] (rows=26 width=223)
-                        default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_name","p_mfgr","p_size"]
-        <-Reducer 5 [SIMPLE_EDGE] llap
+                    TableScan [TS_0] (rows=26 width=223)
+                      default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_name","p_mfgr","p_size"]
+        <-Reducer 6 [SIMPLE_EDGE] llap
           SHUFFLE [RS_18]
             PartitionCols:_col0
-            Group By Operator [GBY_16] (rows=13 width=184)
-              Output:["_col0"],keys:_col0
-              Select Operator [SEL_11] (rows=26 width=184)
-                Output:["_col0"]
-                Filter Operator [FIL_25] (rows=26 width=491)
-                  predicate:first_value_window_0 is not null
-                  PTF Operator [PTF_10] (rows=26 width=491)
-                    Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col5 ASC NULLS FIRST","partition by:":"_col2"}]
-                    Select Operator [SEL_9] (rows=26 width=491)
-                      Output:["_col1","_col2","_col5"]
-                    <-Map 4 [SIMPLE_EDGE] llap
-                      SHUFFLE [RS_8]
-                        PartitionCols:p_mfgr
-                        TableScan [TS_7] (rows=26 width=223)
-                          default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_mfgr","p_name","p_size"]
+            Group By Operator [GBY_15] (rows=13 width=184)
+              Output:["_col0"],keys:KEY._col0
+            <-Reducer 5 [SIMPLE_EDGE] llap
+              SHUFFLE [RS_14]
+                PartitionCols:_col0
+                Group By Operator [GBY_13] (rows=13 width=184)
+                  Output:["_col0"],keys:_col0
+                  Select Operator [SEL_10] (rows=26 width=491)
+                    Output:["_col0"]
+                    PTF Operator [PTF_9] (rows=26 width=491)
+                      Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col5 ASC NULLS FIRST","partition by:":"_col2"}]
+                      Select Operator [SEL_8] (rows=26 width=491)
+                        Output:["_col1","_col2","_col5"]
+                      <-Map 4 [SIMPLE_EDGE] llap
+                        SHUFFLE [RS_7]
+                          PartitionCols:p_mfgr
+                          TableScan [TS_6] (rows=26 width=223)
+                            default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_mfgr","p_name","p_size"]
 
 PREHOOK: query: explain select * 
 from src_cbo 
@@ -2290,62 +2448,66 @@ Plan optimized by CBO.
 
 Vertex dependency in root stage
 Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 6 (SIMPLE_EDGE)
-Reducer 3 <- Map 7 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 2 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE)
 Reducer 4 <- Reducer 3 (SIMPLE_EDGE)
 Reducer 6 <- Map 5 (SIMPLE_EDGE)
+Reducer 8 <- Map 7 (SIMPLE_EDGE)
 
 Stage-0
   Fetch Operator
     limit:-1
     Stage-1
       Reducer 4 llap
-      File Output Operator [FS_25]
-        Select Operator [SEL_24] (rows=1 width=178)
+      File Output Operator [FS_27]
+        Select Operator [SEL_26] (rows=250 width=178)
           Output:["_col0","_col1"]
         <-Reducer 3 [SIMPLE_EDGE] llap
-          SHUFFLE [RS_23]
-            Select Operator [SEL_22] (rows=1 width=178)
+          SHUFFLE [RS_25]
+            Select Operator [SEL_24] (rows=250 width=178)
               Output:["_col0","_col1"]
-              Filter Operator [FIL_21] (rows=1 width=265)
-                predicate:_col3 is null
-                Merge Join Operator [MERGEJOIN_29] (rows=500 width=265)
-                  Conds:RS_18._col0=RS_19._col0(Left Outer),Output:["_col0","_col1","_col3"]
-                <-Map 7 [SIMPLE_EDGE] llap
-                  SHUFFLE [RS_19]
-                    PartitionCols:_col0
-                    Select Operator [SEL_14] (rows=166 width=87)
-                      Output:["_col0"]
-                      Filter Operator [FIL_27] (rows=166 width=87)
-                        predicate:(key > '2')
-                        TableScan [TS_12] (rows=500 width=87)
-                          default@src_cbo,s1,Tbl:COMPLETE,Col:COMPLETE,Output:["key"]
+              Filter Operator [FIL_23] (rows=250 width=198)
+                predicate:(not CASE WHEN ((_col2 = 0)) THEN (false) WHEN (_col5 is not null) THEN (true) WHEN (_col0 is null) THEN (null) WHEN ((_col3 < _col2)) THEN (true) ELSE (false) END)
+                Merge Join Operator [MERGEJOIN_32] (rows=500 width=198)
+                  Conds:RS_20._col0=RS_21._col0(Left Outer),Output:["_col0","_col1","_col2","_col3","_col5"]
                 <-Reducer 2 [SIMPLE_EDGE] llap
-                  SHUFFLE [RS_18]
+                  SHUFFLE [RS_20]
                     PartitionCols:_col0
-                    Merge Join Operator [MERGEJOIN_28] (rows=500 width=178)
-                      Conds:(Inner),Output:["_col0","_col1"]
+                    Merge Join Operator [MERGEJOIN_31] (rows=500 width=194)
+                      Conds:(Inner),Output:["_col0","_col1","_col2","_col3"]
                     <-Map 1 [SIMPLE_EDGE] llap
-                      SHUFFLE [RS_15]
+                      SHUFFLE [RS_17]
                         Select Operator [SEL_1] (rows=500 width=178)
                           Output:["_col0","_col1"]
                           TableScan [TS_0] (rows=500 width=178)
                             default@src_cbo,src_cbo,Tbl:COMPLETE,Col:COMPLETE,Output:["key","value"]
                     <-Reducer 6 [SIMPLE_EDGE] llap
-                      SHUFFLE [RS_16]
-                        Select Operator [SEL_11] (rows=1 width=8)
-                          Filter Operator [FIL_10] (rows=1 width=8)
-                            predicate:(_col0 = 0)
-                            Group By Operator [GBY_8] (rows=1 width=8)
-                              Output:["_col0"],aggregations:["count(VALUE._col0)"]
-                            <-Map 5 [SIMPLE_EDGE] llap
-                              SHUFFLE [RS_7]
-                                Group By Operator [GBY_6] (rows=1 width=8)
-                                  Output:["_col0"],aggregations:["count()"]
-                                  Filter Operator [FIL_4] (rows=1 width=4)
-                                    predicate:false
-                                    Select Operator [SEL_3] (rows=500 width=4)
-                                      TableScan [TS_2] (rows=500 width=10)
-                                        default@src_cbo,s1,Tbl:COMPLETE,Col:COMPLETE
+                      SHUFFLE [RS_18]
+                        Group By Operator [GBY_7] (rows=1 width=16)
+                          Output:["_col0","_col1"],aggregations:["count(VALUE._col0)","count(VALUE._col1)"]
+                        <-Map 5 [SIMPLE_EDGE] llap
+                          SHUFFLE [RS_6]
+                            Group By Operator [GBY_5] (rows=1 width=16)
+                              Output:["_col0","_col1"],aggregations:["count()","count(key)"]
+                              Filter Operator [FIL_29] (rows=166 width=87)
+                                predicate:(key > '2')
+                                TableScan [TS_2] (rows=500 width=87)
+                                  default@src_cbo,s1,Tbl:COMPLETE,Col:COMPLETE,Output:["key"]
+                <-Reducer 8 [SIMPLE_EDGE] llap
+                  SHUFFLE [RS_21]
+                    PartitionCols:_col0
+                    Group By Operator [GBY_15] (rows=69 width=91)
+                      Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+                    <-Map 7 [SIMPLE_EDGE] llap
+                      SHUFFLE [RS_14]
+                        PartitionCols:_col0, _col1
+                        Group By Operator [GBY_13] (rows=69 width=91)
+                          Output:["_col0","_col1"],keys:_col0, true
+                          Select Operator [SEL_11] (rows=166 width=87)
+                            Output:["_col0"]
+                            Filter Operator [FIL_30] (rows=166 width=87)
+                              predicate:(key > '2')
+                              TableScan [TS_9] (rows=500 width=87)
+                                default@src_cbo,s1,Tbl:COMPLETE,Col:COMPLETE,Output:["key"]
 
 PREHOOK: query: explain select p_mfgr, b.p_name, p_size 
 from part b 
@@ -2366,58 +2528,128 @@ POSTHOOK: type: QUERY
 Plan optimized by CBO.
 
 Vertex dependency in root stage
-Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE)
-Reducer 3 <- Map 6 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
-Reducer 5 <- Map 4 (SIMPLE_EDGE)
+Reducer 10 <- Map 9 (SIMPLE_EDGE), Reducer 14 (SIMPLE_EDGE)
+Reducer 11 <- Reducer 10 (SIMPLE_EDGE)
+Reducer 12 <- Reducer 11 (SIMPLE_EDGE), Reducer 16 (SIMPLE_EDGE)
+Reducer 14 <- Map 13 (SIMPLE_EDGE)
+Reducer 16 <- Map 15 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 6 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 12 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 5 <- Map 4 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE)
+Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
+Reducer 8 <- Map 7 (SIMPLE_EDGE)
 
 Stage-0
   Fetch Operator
     limit:-1
     Stage-1
       Reducer 3 llap
-      File Output Operator [FS_22]
-        Select Operator [SEL_21] (rows=1 width=223)
+      File Output Operator [FS_54]
+        Select Operator [SEL_53] (rows=13 width=223)
           Output:["_col0","_col1","_col2"]
-          Filter Operator [FIL_20] (rows=1 width=344)
-            predicate:_col4 is null
-            Merge Join Operator [MERGEJOIN_27] (rows=26 width=344)
-              Conds:RS_17._col0, _col1=RS_18._col0, _col1(Left Outer),Output:["_col0","_col1","_col2","_col4"]
-            <-Map 6 [SIMPLE_EDGE] llap
-              SHUFFLE [RS_18]
-                PartitionCols:_col0, _col1
-                Select Operator [SEL_13] (rows=8 width=219)
-                  Output:["_col0","_col1"]
-                  Filter Operator [FIL_25] (rows=8 width=223)
-                    predicate:(p_size < 10)
-                    TableScan [TS_11] (rows=26 width=223)
-                      default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_name","p_mfgr","p_size"]
+          Filter Operator [FIL_52] (rows=13 width=243)
+            predicate:CASE WHEN ((_col4 = 0)) THEN (true) WHEN (_col4 is null) THEN (true) WHEN (_col8 is not null) THEN (false) WHEN (_col0 is null) THEN (null) WHEN ((_col5 < _col4)) THEN (false) ELSE (true) END
+            Merge Join Operator [MERGEJOIN_76] (rows=26 width=243)
+              Conds:RS_49._col0, _col1=RS_50._col3, _col1(Left Outer),Output:["_col0","_col1","_col2","_col4","_col5","_col8"]
+            <-Reducer 12 [SIMPLE_EDGE] llap
+              SHUFFLE [RS_50]
+                PartitionCols:_col3, _col1
+                Merge Join Operator [MERGEJOIN_75] (rows=2 width=223)
+                  Conds:RS_42._col0=RS_43._col0(Inner),Output:["_col1","_col2","_col3"]
+                <-Reducer 11 [SIMPLE_EDGE] llap
+                  SHUFFLE [RS_42]
+                    PartitionCols:_col0
+                    Select Operator [SEL_35] (rows=4 width=223)
+                      Output:["_col0","_col1","_col2"]
+                      Group By Operator [GBY_34] (rows=4 width=219)
+                        Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+                      <-Reducer 10 [SIMPLE_EDGE] llap
+                        SHUFFLE [RS_33]
+                          PartitionCols:_col0, _col1
+                          Group By Operator [GBY_32] (rows=4 width=219)
+                            Output:["_col0","_col1"],keys:_col1, _col2
+                            Merge Join Operator [MERGEJOIN_74] (rows=8 width=219)
+                              Conds:RS_28._col0=RS_29._col0(Inner),Output:["_col1","_col2"]
+                            <-Map 9 [SIMPLE_EDGE] llap
+                              SHUFFLE [RS_28]
+                                PartitionCols:_col0
+                                Select Operator [SEL_21] (rows=8 width=219)
+                                  Output:["_col0","_col1"]
+                                  Filter Operator [FIL_69] (rows=8 width=223)
+                                    predicate:(p_size < 10)
+                                    TableScan [TS_19] (rows=26 width=223)
+                                      default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_name","p_mfgr","p_size"]
+                            <-Reducer 14 [SIMPLE_EDGE] llap
+                              SHUFFLE [RS_29]
+                                PartitionCols:_col0
+                                Group By Operator [GBY_26] (rows=5 width=98)
+                                  Output:["_col0"],keys:KEY._col0
+                                <-Map 13 [SIMPLE_EDGE] llap
+                                  SHUFFLE [RS_25]
+                                    PartitionCols:_col0
+                                    Group By Operator [GBY_24] (rows=5 width=98)
+                                      Output:["_col0"],keys:p_mfgr
+                                      TableScan [TS_22] (rows=26 width=98)
+                                        default@part,b,Tbl:COMPLETE,Col:COMPLETE,Output:["p_mfgr"]
+                <-Reducer 16 [SIMPLE_EDGE] llap
+                  SHUFFLE [RS_43]
+                    PartitionCols:_col0
+                    Group By Operator [GBY_40] (rows=13 width=121)
+                      Output:["_col0"],keys:KEY._col0
+                    <-Map 15 [SIMPLE_EDGE] llap
+                      SHUFFLE [RS_39]
+                        PartitionCols:_col0
+                        Group By Operator [GBY_38] (rows=13 width=121)
+                          Output:["_col0"],keys:p_name
+                          TableScan [TS_36] (rows=26 width=121)
+                            default@part,b,Tbl:COMPLETE,Col:COMPLETE,Output:["p_name"]
             <-Reducer 2 [SIMPLE_EDGE] llap
-              SHUFFLE [RS_17]
+              SHUFFLE [RS_49]
                 PartitionCols:_col0, _col1
-                Merge Join Operator [MERGEJOIN_26] (rows=26 width=223)
-                  Conds:(Inner),Output:["_col0","_col1","_col2"]
+                Merge Join Operator [MERGEJOIN_73] (rows=26 width=239)
+                  Conds:RS_46._col1=RS_47._col0(Left Outer),Output:["_col0","_col1","_col2","_col4","_col5"]
                 <-Map 1 [SIMPLE_EDGE] llap
-                  SHUFFLE [RS_14]
+                  SHUFFLE [RS_46]
+                    PartitionCols:_col1
                     Select Operator [SEL_1] (rows=26 width=223)
                       Output:["_col0","_col1","_col2"]
                       TableScan [TS_0] (rows=26 width=223)
                         default@part,b,Tbl:COMPLETE,Col:COMPLETE,Output:["p_name","p_mfgr","p_size"]
-                <-Reducer 5 [SIMPLE_EDGE] llap
-                  SHUFFLE [RS_15]
-                    Select Operator [SEL_10] (rows=1 width=8)
-                      Filter Operator [FIL_9] (rows=1 width=8)
-                        predicate:(_col0 = 0)
-                        Group By Operator [GBY_7] (rows=1 width=8)
-                          Output:["_col0"],aggregations:["count(VALUE._col0)"]
-                        <-Map 4 [SIMPLE_EDGE] llap
-                          SHUFFLE [RS_6]
-                            Group By Operator [GBY_5] (rows=1 width=8)
-                              Output:["_col0"],aggregations:["count()"]
-                              Select Operator [SEL_4] (rows=1 width=223)
-                                Filter Operator [FIL_24] (rows=1 width=223)
-                                  predicate:((p_size < 10) and (p_name is null or p_mfgr is null))
-                                  TableScan [TS_2] (rows=26 width=223)
-                                    default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_name","p_mfgr","p_size"]
+                <-Reducer 6 [SIMPLE_EDGE] llap
+                  SHUFFLE [RS_47]
+                    PartitionCols:_col0
+                    Group By Operator [GBY_17] (rows=2 width=114)
+                      Output:["_col0","_col1","_col2"],aggregations:["count(VALUE._col0)","count(VALUE._col1)"],keys:KEY._col0
+                    <-Reducer 5 [SIMPLE_EDGE] llap
+                      SHUFFLE [RS_16]
+                        PartitionCols:_col0
+                        Group By Operator [GBY_15] (rows=2 width=114)
+                          Output:["_col0","_col1","_col2"],aggregations:["count()","count(_col1)"],keys:_col2
+                          Select Operator [SEL_14] (rows=8 width=219)
+                            Output:["_col2","_col1"]
+                            Merge Join Operator [MERGEJOIN_72] (rows=8 width=219)
+                              Conds:RS_11._col0=RS_12._col0(Inner),Output:["_col1","_col2"]
+                            <-Map 4 [SIMPLE_EDGE] llap
+                              SHUFFLE [RS_11]
+                                PartitionCols:_col0
+                                Select Operator [SEL_4] (rows=8 width=219)
+                                  Output:["_col0","_col1"]
+                                  Filter Operator [FIL_67] (rows=8 width=223)
+                                    predicate:(p_size < 10)
+                                    TableScan [TS_2] (rows=26 width=223)
+                                      default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_name","p_mfgr","p_size"]
+                            <-Reducer 8 [SIMPLE_EDGE] llap
+                              SHUFFLE [RS_12]
+                                PartitionCols:_col0
+                                Group By Operator [GBY_9] (rows=5 width=98)
+                                  Output:["_col0"],keys:KEY._col0
+                                <-Map 7 [SIMPLE_EDGE] llap
+                                  SHUFFLE [RS_8]
+                                    PartitionCols:_col0
+                                    Group By Operator [GBY_7] (rows=5 width=98)
+                                      Output:["_col0"],keys:p_mfgr
+                                      TableScan [TS_5] (rows=26 width=98)
+                                        default@part,b,Tbl:COMPLETE,Col:COMPLETE,Output:["p_mfgr"]
 
 PREHOOK: query: explain select p_name, p_size 
 from 
@@ -2439,71 +2671,73 @@ Plan optimized by CBO.
 
 Vertex dependency in root stage
 Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 6 (SIMPLE_EDGE)
-Reducer 3 <- Reducer 2 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 2 (SIMPLE_EDGE), Reducer 9 (SIMPLE_EDGE)
 Reducer 4 <- Reducer 3 (SIMPLE_EDGE)
 Reducer 6 <- Map 5 (SIMPLE_EDGE)
 Reducer 8 <- Map 7 (SIMPLE_EDGE)
+Reducer 9 <- Reducer 8 (SIMPLE_EDGE)
 
 Stage-0
   Fetch Operator
     limit:-1
     Stage-1
       Reducer 4 llap
-      File Output Operator [FS_35]
-        Select Operator [SEL_34] (rows=1 width=125)
+      File Output Operator [FS_36]
+        Select Operator [SEL_35] (rows=13 width=125)
           Output:["_col0","_col1"]
         <-Reducer 3 [SIMPLE_EDGE] llap
-          SHUFFLE [RS_33]
-            Select Operator [SEL_32] (rows=1 width=125)
+          SHUFFLE [RS_34]
+            Select Operator [SEL_33] (rows=13 width=125)
               Output:["_col0","_col1"]
-              Filter Operator [FIL_31] (rows=1 width=133)
-                predicate:_col3 is null
-                Merge Join Operator [MERGEJOIN_41] (rows=26 width=133)
-                  Conds:RS_28.UDFToDouble(_col1)=RS_29._col0(Left Outer),Output:["_col0","_col1","_col3"]
+              Filter Operator [FIL_32] (rows=13 width=145)
+                predicate:(not CASE WHEN ((_col2 = 0)) THEN (false) WHEN (_col5 is not null) THEN (true) WHEN (_col1 is null) THEN (null) WHEN ((_col3 < _col2)) THEN (true) ELSE (false) END)
+                Merge Join Operator [MERGEJOIN_42] (rows=26 width=145)
+                  Conds:RS_29.UDFToDouble(_col1)=RS_30._col0(Left Outer),Output:["_col0","_col1","_col2","_col3","_col5"]
                 <-Reducer 2 [SIMPLE_EDGE] llap
-                  SHUFFLE [RS_28]
+                  SHUFFLE [RS_29]
                     PartitionCols:UDFToDouble(_col1)
-                    Merge Join Operator [MERGEJOIN_40] (rows=26 width=125)
-                      Conds:(Inner),Output:["_col0","_col1"]
+                    Merge Join Operator [MERGEJOIN_41] (rows=26 width=141)
+                      Conds:(Inner),Output:["_col0","_col1","_col2","_col3"]
                     <-Map 1 [SIMPLE_EDGE] llap
-                      SHUFFLE [RS_25]
+                      SHUFFLE [RS_26]
                         Select Operator [SEL_1] (rows=26 width=125)
                           Output:["_col0","_col1"]
                           TableScan [TS_0] (rows=26 width=125)
                             default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_name","p_size"]
                     <-Reducer 6 [SIMPLE_EDGE] llap
-                      SHUFFLE [RS_26]
-                        Select Operator [SEL_17] (rows=1 width=8)
-                          Filter Operator [FIL_16] (rows=1 width=8)
-                            predicate:(_col0 = 0)
-                            Group By Operator [GBY_14] (rows=1 width=8)
-                              Output:["_col0"],aggregations:["count()"]
-                              Select Operator [SEL_10] (rows=1 width=8)
-                                Filter Operator [FIL_9] (rows=1 width=8)
-                                  predicate:_col0 is null
-                                  Group By Operator [GBY_7] (rows=1 width=8)
-                                    Output:["_col0"],aggregations:["avg(VALUE._col0)"]
-                                  <-Map 5 [SIMPLE_EDGE] llap
-                                    SHUFFLE [RS_6]
-                                      Group By Operator [GBY_5] (rows=1 width=76)
-                                        Output:["_col0"],aggregations:["avg(p_size)"]
-                                        Filter Operator [FIL_37] (rows=8 width=4)
-                                          predicate:(p_size < 10)
-                                          TableScan [TS_2] (rows=26 width=4)
-                                            default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_size"]
-                <-Reducer 8 [SIMPLE_EDGE] llap
-                  SHUFFLE [RS_29]
+                      SHUFFLE [RS_27]
+                        Group By Operator [GBY_12] (rows=1 width=16)
+                          Output:["_col0","_col1"],aggregations:["count()","count(_col0)"]
+                          Group By Operator [GBY_7] (rows=1 width=8)
+                            Output:["_col0"],aggregations:["avg(VALUE._col0)"]
+                          <-Map 5 [SIMPLE_EDGE] llap
+                            SHUFFLE [RS_6]
+                              Group By Operator [GBY_5] (rows=1 width=76)
+                                Output:["_col0"],aggregations:["avg(p_size)"]
+                                Filter Operator [FIL_38] (rows=8 width=4)
+                                  predicate:(p_size < 10)
+                                  TableScan [TS_2] (rows=26 width=4)
+                                    default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_size"]
+                <-Reducer 9 [SIMPLE_EDGE] llap
+                  SHUFFLE [RS_30]
                     PartitionCols:_col0
-                    Group By Operator [GBY_23] (rows=1 width=8)
-                      Output:["_col0"],aggregations:["avg(VALUE._col0)"]
-                    <-Map 7 [SIMPLE_EDGE] llap
-                      SHUFFLE [RS_22]
-                        Group By Operator [GBY_21] (rows=1 width=76)
-                          Output:["_col0"],aggregations:["avg(p_size)"]
-                          Filter Operator [FIL_39] (rows=8 width=4)
-                            predicate:(p_size < 10)
-                            TableScan [TS_18] (rows=26 width=4)
-                              default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_size"]
+                    Group By Operator [GBY_24] (rows=1 width=12)
+                      Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+                    <-Reducer 8 [SIMPLE_EDGE] llap
+                      SHUFFLE [RS_23]
+                        PartitionCols:_col0, _col1
+                        Group By Operator [GBY_22] (rows=1 width=12)
+                          Output:["_col0","_col1"],keys:_col0, true
+                          Group By Operator [GBY_19] (rows=1 width=8)
+                            Output:["_col0"],aggregations:["avg(VALUE._col0)"]
+                          <-Map 7 [SIMPLE_EDGE] llap
+                            SHUFFLE [RS_18]
+                              Group By Operator [GBY_17] (rows=1 width=76)
+                                Output:["_col0"],aggregations:["avg(p_size)"]
+                                Filter Operator [FIL_40] (rows=8 width=4)
+                                  predicate:(p_size < 10)
+                                  TableScan [TS_14] (rows=26 width=4)
+                                    default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_size"]
 
 PREHOOK: query: explain select b.p_mfgr, min(p_retailprice) 
 from part b 
@@ -2528,53 +2762,119 @@ POSTHOOK: type: QUERY
 Plan optimized by CBO.
 
 Vertex dependency in root stage
-Reducer 10 <- Map 9 (SIMPLE_EDGE)
+Reducer 11 <- Map 10 (SIMPLE_EDGE)
+Reducer 12 <- Reducer 11 (SIMPLE_EDGE)
+Reducer 14 <- Map 13 (SIMPLE_EDGE)
+Reducer 15 <- Reducer 14 (SIMPLE_EDGE), Reducer 20 (SIMPLE_EDGE)
+Reducer 16 <- Reducer 15 (SIMPLE_EDGE)
+Reducer 17 <- Reducer 16 (SIMPLE_EDGE), Reducer 22 (SIMPLE_EDGE)
+Reducer 19 <- Map 18 (SIMPLE_EDGE)
 Reducer 2 <- Map 1 (SIMPLE_EDGE)
-Reducer 3 <- Reducer 2 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE)
-Reducer 4 <- Reducer 10 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
+Reducer 20 <- Reducer 19 (SIMPLE_EDGE)
+Reducer 22 <- Map 21 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 2 (SIMPLE_EDGE), Reducer 9 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 17 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
 Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
 Reducer 7 <- Map 6 (SIMPLE_EDGE)
-Reducer 8 <- Reducer 7 (SIMPLE_EDGE)
+Reducer 8 <- Reducer 12 (SIMPLE_EDGE), Reducer 7 (SIMPLE_EDGE)
+Reducer 9 <- Reducer 8 (SIMPLE_EDGE)
 
 Stage-0
   Fetch Operator
     limit:-1
     Stage-1
       Reducer 5 llap
-      File Output Operator [FS_37]
-        Select Operator [SEL_36] (rows=1 width=106)
+      File Output Operator [FS_83]
+        Select Operator [SEL_82] (rows=2 width=106)
           Output:["_col0","_col1"]
         <-Reducer 4 [SIMPLE_EDGE] llap
-          SHUFFLE [RS_35]
-            Select Operator [SEL_34] (rows=1 width=106)
+          SHUFFLE [RS_81]
+            Select Operator [SEL_80] (rows=2 width=106)
               Output:["_col0","_col1"]
-              Filter Operator [FIL_33] (rows=1 width=204)
-                predicate:_col3 is null
-                Merge Join Operator [MERGEJOIN_42] (rows=5 width=204)
-                  Conds:RS_30._col0, _col1=RS_31._col0, _col1(Left Outer),Output:["_col0","_col1","_col3"]
-                <-Reducer 10 [SIMPLE_EDGE] llap
-                  SHUFFLE [RS_31]
-                    PartitionCols:_col0, _col1
-                    Select Operator [SEL_26] (rows=1 width=106)
-                      Output:["_col0","_col1"]
-                      Filter Operator [FIL_39] (rows=1 width=114)
-                        predicate:((_col2 - _col1) > 600.0)
-                        Group By Operator [GBY_24] (rows=5 width=114)
-                          Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)"],keys:KEY._col0
-                        <-Map 9 [SIMPLE_EDGE] llap
-                          SHUFFLE [RS_23]
-                            PartitionCols:_col0
-                            Group By Operator [GBY_22] (rows=5 width=114)
-                              Output:["_col0","_col1","_col2"],aggregations:["min(p_retailprice)","max(p_retailprice)"],keys:p_mfgr
-                              TableScan [TS_20] (rows=26 width=106)
-                                default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_mfgr","p_retailprice"]
+              Filter Operator [FIL_79] (rows=2 width=126)
+                predicate:CASE WHEN ((_col3 = 0)) THEN (true) WHEN (_col3 is null) THEN (true) WHEN (_col7 is not null) THEN (false) WHEN (_col0 is null) THEN (null) WHEN ((_col4 < _col3)) THEN (false) ELSE (true) END
+                Merge Join Operator [MERGEJOIN_108] (rows=5 width=126)
+                  Conds:RS_76._col0, _col1=RS_77._col3, _col1(Left Outer),Output:["_col0","_col1","_col3","_col4","_col7"]
+                <-Reducer 17 [SIMPLE_EDGE] llap
+                  SHUFFLE [RS_77]
+                    PartitionCols:_col3, _col1
+                    Merge Join Operator [MERGEJOIN_107] (rows=1 width=110)
+                      Conds:RS_69._col0=RS_70._col0(Inner),Output:["_col1","_col2","_col3"]
+                    <-Reducer 16 [SIMPLE_EDGE] llap
+                      SHUFFLE [RS_69]
+                        PartitionCols:_col0
+                        Select Operator [SEL_57] (rows=1 width=110)
+                          Output:["_col0","_col1","_col2"]
+                          Group By Operator [GBY_56] (rows=1 width=106)
+                            Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+                          <-Reducer 15 [SIMPLE_EDGE] llap
+                            SHUFFLE [RS_55]
+                              PartitionCols:_col0, _col1
+                              Group By Operator [GBY_54] (rows=1 width=106)
+                                Output:["_col0","_col1"],keys:_col0, _col3
+                                Merge Join Operator [MERGEJOIN_106] (rows=1 width=106)
+                                  Conds:RS_50._col1=RS_51._col0(Inner),Output:["_col0","_col3"]
+                                <-Reducer 14 [SIMPLE_EDGE] llap
+                                  SHUFFLE [RS_50]
+                                    PartitionCols:_col1
+                                    Select Operator [SEL_38] (rows=1 width=114)
+                                      Output:["_col0","_col1"]
+                                      Filter Operator [FIL_98] (rows=1 width=114)
+                                        predicate:((_col2 - _col1) > 600.0)
+                                        Group By Operator [GBY_36] (rows=5 width=114)
+                                          Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)"],keys:KEY._col0
+                                        <-Map 13 [SIMPLE_EDGE] llap
+                                          SHUFFLE [RS_35]
+                                            PartitionCols:_col0
+                                            Group By Operator [GBY_34] (rows=5 width=114)
+                                              Output:["_col0","_col1","_col2"],aggregations:["min(p_retailprice)","max(p_retailprice)"],keys:p_mfgr
+                                              TableScan [TS_32] (rows=26 width=106)
+                                                default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_mfgr","p_retailprice"]
+                                <-Reducer 20 [SIMPLE_EDGE] llap
+                                  SHUFFLE [RS_51]
+                                    PartitionCols:_col0
+                                    Group By Operator [GBY_48] (rows=2 width=8)
+                                      Output:["_col0"],keys:KEY._col0
+                                    <-Reducer 19 [SIMPLE_EDGE] llap
+                                      SHUFFLE [RS_47]
+                                        PartitionCols:_col0
+                                        Group By Operator [GBY_46] (rows=2 width=8)
+                                          Output:["_col0"],keys:_col1
+                                          Select Operator [SEL_103] (rows=5 width=8)
+                                            Output:["_col1"]
+                                            Group By Operator [GBY_43] (rows=5 width=106)
+                                              Output:["_col0","_col1"],aggregations:["min(VALUE._col0)"],keys:KEY._col0
+                                            <-Map 18 [SIMPLE_EDGE] llap
+                                              SHUFFLE [RS_42]
+                                                PartitionCols:_col0
+                                                Group By Operator [GBY_41] (rows=5 width=106)
+                                                  Output:["_col0","_col1"],aggregations:["min(p_retailprice)"],keys:p_mfgr
+                                                  Select Operator [SEL_40] (rows=26 width=106)
+                                                    Output:["p_mfgr","p_retailprice"]
+                                                    TableScan [TS_39] (rows=26 width=106)
+                                                      default@part,b,Tbl:COMPLETE,Col:COMPLETE,Output:["p_mfgr","p_retailprice"]
+                    <-Reducer 22 [SIMPLE_EDGE] llap
+                      SHUFFLE [RS_70]
+                        PartitionCols:_col0
+                        Group By Operator [GBY_67] (rows=5 width=98)
+                          Output:["_col0"],keys:_col0
+                          Group By Operator [GBY_62] (rows=5 width=98)
+                            Output:["_col0"],keys:KEY._col0
+                          <-Map 21 [SIMPLE_EDGE] llap
+                            SHUFFLE [RS_61]
+                              PartitionCols:_col0
+                              Group By Operator [GBY_60] (rows=5 width=98)
+                                Output:["_col0"],keys:p_mfgr
+                                TableScan [TS_58] (rows=26 width=98)
+                                  default@part,b,Tbl:COMPLETE,Col:COMPLETE,Output:["p_mfgr"]
                 <-Reducer 3 [SIMPLE_EDGE] llap
-                  SHUFFLE [RS_30]
+                  SHUFFLE [RS_76]
                     PartitionCols:_col0, _col1
-                    Merge Join Operator [MERGEJOIN_41] (rows=5 width=106)
-                      Conds:(Inner),Output:["_col0","_col1"]
+                    Merge Join Operator [MERGEJOIN_105] (rows=5 width=122)
+                      Conds:RS_73._col1=RS_74._col0(Left Outer),Output:["_col0","_col1","_col3","_col4"]
                     <-Reducer 2 [SIMPLE_EDGE] llap
-                      SHUFFLE [RS_27]
+                      SHUFFLE [RS_73]
+                        PartitionCols:_col1
                         Group By Operator [GBY_4] (rows=5 width=106)
                           Output:["_col0","_col1"],aggregations:["min(VALUE._col0)"],keys:KEY._col0
                         <-Map 1 [SIMPLE_EDGE] llap
@@ -2586,31 +2886,61 @@ Stage-0
                                 Output:["p_mfgr","p_retailprice"]
                                 TableScan [TS_0] (rows=26 width=106)
                                   default@part,b,Tbl:COMPLETE,Col:COMPLETE,Output:["p_mfgr","p_retailprice"]
-                    <-Reducer 8 [SIMPLE_EDGE] llap
-                      SHUFFLE [RS_28]
-                        Select Operator [SEL_19] (rows=1 width=8)
-                          Filter Operator [FIL_18] (rows=1 width=8)
-                            predicate:(_col0 = 0)
-                            Group By Operator [GBY_16] (rows=1 width=8)
-                              Output:["_col0"],aggregations:["count(VALUE._col0)"]
-                            <-Reducer 7 [SIMPLE_EDGE] llap
-                              SHUFFLE [RS_15]
-                                Group By Operator [GBY_14] (rows=1 width=8)
-                                  Output:["_col0"],aggregations:["count()"]
-                                  Select Operator [SEL_12] (rows=1 width=114)
-                                    Filter Operator [FIL_11] (rows=1 width=114)
-                                      predicate:(((_col2 - _col1) > 600.0) and (_col0 is null or _col1 is null))
-                                      Group By Operator [GBY_10] (rows=5 width=114)
-                                        Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)"],keys:KEY._col0
-                                      <-Map 6 [SIMPLE_EDGE] llap
-                                        SHUFFLE [RS_9]
-                                          PartitionCols:_col0
-                                          Group By Operator [GBY_8] (rows=5 width=114)
-                                            Output:["_col0","_col1","_col2"],aggregations:["min(p_retailprice)","max(p_retailprice)"],keys:p_mfgr
-                                            Select Operator [SEL_7] (rows=26 width=106)
-                                              Output:["p_mfgr","p_retailprice"]
-                                              TableScan [TS_6] (rows=26 width=106)
-                                                default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_mfgr","p_retailprice"]
+                    <-Reducer 9 [SIMPLE_EDGE] llap
+                      SHUFFLE [RS_74]
+                        PartitionCols:_col0
+                        Group By Operator [GBY_30] (rows=1 width=24)
+                          Output:["_col0","_col1","_col2"],aggregations:["count(VALUE._col0)","count(VALUE._col1)"],keys:KEY._col0
+                        <-Reducer 8 [SIMPLE_EDGE] llap
+                          SHUFFLE [RS_29]
+                            PartitionCols:_col0
+                            Group By Operator [GBY_28] (rows=1 width=24)
+                              Output:["_col0","_col1","_col2"],aggregations:["count()","count(_col0)"],keys:_col3
+                              Select Operator [SEL_27] (rows=1 width=106)
+                                Output:["_col3","_col0"]
+                                Merge Join Operator [MERGEJOIN_104] (rows=1 width=106)
+                                  Conds:RS_24._col1=RS_25._col0(Inner),Output:["_col0","_col3"]
+                                <-Reducer 12 [SIMPLE_EDGE] llap
+                                  SHUFFLE [RS_25]
+                                    PartitionCols:_col0
+                                    Group By Operator [GBY_22] (rows=2 width=8)
+                                      Output:["_col0"],keys:KEY._col0
+                                    <-Reducer 11 [SIMPLE_EDGE] llap
+                                      SHUFFLE [RS_21]
+                                        PartitionCols:_col0
+                                        Group By Operator [GBY_20] (rows=2 width=8)
+                                          Output:["_col0"],keys:_col1
+                                          Select Operator [SEL_102] (rows=5 width=8)
+                                            Output:["_col1"]
+                                            Group By Operator [GBY_17] (rows=5 width=106)
+                                              Output:["_col0","_col1"],aggregations:["min(VALUE._col0)"],keys:KEY._col0
+                                            <-Map 10 [SIMPLE_EDGE] llap
+                                              SHUFFLE [RS_16]
+                                                PartitionCols:_col0
+                                                Group By Operator [GBY_15] (rows=5 width=106)
+                                                  Output:["_col0","_col1"],aggregations:["min(p_retailprice)"],keys:p_mfgr
+                                                  Select Operator [SEL_14] (rows=26 width=106)
+                                                    Output:["p_mfgr","p_retailprice"]
+                                                    TableScan [TS_13] (rows=26 width=106)
+                                                      default@part,b,Tbl:COMPLETE,Col:COMPLETE,Output:["p_mfgr","p_retailprice"]
+                                <-Reducer 7 [SIMPLE_EDGE] llap
+                                  SHUFFLE [RS_24]
+                                    PartitionCols:_col1
+                                    Select Operator [SEL_12] (rows=1 width=114)
+                                      Output:["_col0","_col1"]
+                                      Filter Operator [FIL_96] (rows=1 width=114)
+                                        predicate:((_col2 - _col1) > 600.0)
+                                        Group By Operator [GBY_10] (rows=5 width=114)
+                                          Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)"],keys:KEY._col0
+                                        <-Map 6 [SIMPLE_EDGE] llap
+                                          SHUFFLE [RS_9]
+                                            PartitionCols:_col0
+                                            Group By Operator [GBY_8] (rows=5 width=114)
+                                              Output:["_col0","_col1","_col2"],aggregations:["min(p_retailprice)","max(p_retailprice)"],keys:p_mfgr
+                                              Select Operator [SEL_7] (rows=26 width=106)
+                                                Output:["p_mfgr","p_retailprice"]
+                                                TableScan [TS_6] (rows=26 width=106)
+                                                  default@part,part,Tbl:COMPLETE,Col:COMPLETE,Output:["p_mfgr","p_retailprice"]
 
 PREHOOK: query: explain select count(c_int) over(), sum(c_float) over(), max(c_int) over(), min(c_int) over(), row_number() over(), rank() over(), dense_rank() over(), percent_rank() over(), lead(c_int, 2, c_int) over(), lag(c_float, 2, c_float) over() from cbo_t1
 PREHOOK: type: QUERY

http://git-wip-us.apache.org/repos/asf/hive/blob/382dc208/ql/src/test/results/clientpositive/llap/lineage3.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/llap/lineage3.q.out b/ql/src/test/results/clientpositive/llap/lineage3.q.out
index 1a532da..72a9344 100644
--- a/ql/src/test/results/clientpositive/llap/lineage3.q.out
+++ b/ql/src/test/results/clientpositive/llap/lineage3.q.out
@@ -166,7 +166,7 @@ where key in (select key+18 from src1) order by key
 PREHOOK: type: QUERY
 PREHOOK: Input: default@src1
 #### A masked pattern was here ####
-{"version":"1.0","engine":"tez","database":"default","hash":"8b9d63653e36ecf4dd425d3cc3de9199","queryText":"select key, value from src1\nwhere key in (select key+18 from src1) order by key","edges":[{"sources":[2],"targets":[0],"edgeType":"PROJECTION"},{"sources":[3],"targets":[1],"edgeType":"PROJECTION"},{"sources":[2],"targets":[0,1],"expression":"UDFToDouble(src1.key) is not null","edgeType":"PREDICATE"},{"sources":[2],"targets":[0,1],"expression":"(UDFToDouble(src1.key) = (UDFToDouble(src1.key) + 18.0))","edgeType":"PREDICATE"},{"sources":[2],"targets":[0,1],"expression":"(UDFToDouble(src1.key) + 18.0) is not null","edgeType":"PREDICATE"}],"vertices":[{"id":0,"vertexType":"COLUMN","vertexId":"key"},{"id":1,"vertexType":"COLUMN","vertexId":"value"},{"id":2,"vertexType":"COLUMN","vertexId":"default.src1.key"},{"id":3,"vertexType":"COLUMN","vertexId":"default.src1.value"}]}
+{"version":"1.0","engine":"tez","database":"default","hash":"8b9d63653e36ecf4dd425d3cc3de9199","queryText":"select key, value from src1\nwhere key in (select key+18 from src1) order by key","edges":[{"sources":[2],"targets":[0],"edgeType":"PROJECTION"},{"sources":[3],"targets":[1],"edgeType":"PROJECTION"},{"sources":[2],"targets":[0,1],"expression":"(UDFToDouble(src1.key) = (UDFToDouble(src1.key) + 18.0))","edgeType":"PREDICATE"}],"vertices":[{"id":0,"vertexType":"COLUMN","vertexId":"key"},{"id":1,"vertexType":"COLUMN","vertexId":"value"},{"id":2,"vertexType":"COLUMN","vertexId":"default.src1.key"},{"id":3,"vertexType":"COLUMN","vertexId":"default.src1.value"}]}
 146	val_146
 273	val_273
 PREHOOK: query: select * from src1 a
@@ -178,15 +178,15 @@ PREHOOK: type: QUERY
 PREHOOK: Input: default@alltypesorc
 PREHOOK: Input: default@src1
 #### A masked pattern was here ####
-{"version":"1.0","engine":"tez","database":"default","hash":"8bf193b0658183be94e2428a79d91d10","queryText":"select * from src1 a\nwhere exists\n  (select cint from alltypesorc b\n   where a.key = b.ctinyint + 300)\nand key > 300","edges":[{"sources":[2],"targets":[0],"edgeType":"PROJECTION"},{"sources":[3],"targets":[1],"edgeType":"PROJECTION"},{"sources":[2],"targets":[0,1],"expression":"(UDFToDouble(a.key) > 300.0)","edgeType":"PREDICATE"},{"sources":[2,4],"targets":[0,1],"expression":"(UDFToDouble(a.key) = UDFToDouble((UDFToInteger(b.ctinyint) + 300)))","edgeType":"PREDICATE"},{"sources":[4],"targets":[0,1],"expression":"UDFToDouble((UDFToInteger(b.ctinyint) + 300)) is not null","edgeType":"PREDICATE"}],"vertices":[{"id":0,"vertexType":"COLUMN","vertexId":"a.key"},{"id":1,"vertexType":"COLUMN","vertexId":"a.value"},{"id":2,"vertexType":"COLUMN","vertexId":"default.src1.key"},{"id":3,"vertexType":"COLUMN","vertexId":"default.src1.value"},{"id":4,"vertexType":"COLUMN","vertexId":"d
 efault.alltypesorc.ctinyint"}]}
+{"version":"1.0","engine":"tez","database":"default","hash":"8bf193b0658183be94e2428a79d91d10","queryText":"select * from src1 a\nwhere exists\n  (select cint from alltypesorc b\n   where a.key = b.ctinyint + 300)\nand key > 300","edges":[{"sources":[2],"targets":[0],"edgeType":"PROJECTION"},{"sources":[3],"targets":[1],"edgeType":"PROJECTION"},{"sources":[2],"targets":[0,1],"expression":"(UDFToDouble(a.key) > 300.0)","edgeType":"PREDICATE"},{"sources":[2],"targets":[0,1],"expression":"(a.key = a.key)","edgeType":"PREDICATE"},{"sources":[4,2],"targets":[0,1],"expression":"(UDFToDouble((UDFToInteger(b.ctinyint) + 300)) = UDFToDouble(a.key))","edgeType":"PREDICATE"}],"vertices":[{"id":0,"vertexType":"COLUMN","vertexId":"a.key"},{"id":1,"vertexType":"COLUMN","vertexId":"a.value"},{"id":2,"vertexType":"COLUMN","vertexId":"default.src1.key"},{"id":3,"vertexType":"COLUMN","vertexId":"default.src1.value"},{"id":4,"vertexType":"COLUMN","vertexId":"default.alltypesorc.ctinyint"}]}
 311	val_311
-Warning: Shuffle Join MERGEJOIN[27][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 2' is a cross product
+Warning: Shuffle Join MERGEJOIN[29][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 2' is a cross product
 PREHOOK: query: select key, value from src1
 where key not in (select key+18 from src1) order by key
 PREHOOK: type: QUERY
 PREHOOK: Input: default@src1
 #### A masked pattern was here ####
-{"version":"1.0","engine":"tez","database":"default","hash":"9b488fe1d7cf018aad3825173808cd36","queryText":"select key, value from src1\nwhere key not in (select key+18 from src1) order by key","edges":[{"sources":[2],"targets":[0],"edgeType":"PROJECTION"},{"sources":[3],"targets":[1],"edgeType":"PROJECTION"},{"sources":[2],"targets":[0,1],"expression":"(UDFToDouble(src1.key) + 18.0) is null","edgeType":"PREDICATE"},{"sources":[4],"targets":[0,1],"expression":"(count(*) = 0)","edgeType":"PREDICATE"},{"sources":[2],"targets":[0,1],"expression":"(UDFToDouble(src1.key) = (UDFToDouble(src1.key) + 18.0))","edgeType":"PREDICATE"}],"vertices":[{"id":0,"vertexType":"COLUMN","vertexId":"key"},{"id":1,"vertexType":"COLUMN","vertexId":"value"},{"id":2,"vertexType":"COLUMN","vertexId":"default.src1.key"},{"id":3,"vertexType":"COLUMN","vertexId":"default.src1.value"},{"id":4,"vertexType":"TABLE","vertexId":"default.src1"}]}
+{"version":"1.0","engine":"tez","database":"default","hash":"9b488fe1d7cf018aad3825173808cd36","queryText":"select key, value from src1\nwhere key not in (select key+18 from src1) order by key","edges":[{"sources":[2],"targets":[0],"edgeType":"PROJECTION"},{"sources":[3],"targets":[1],"edgeType":"PROJECTION"},{"sources":[2],"targets":[0,1],"expression":"(UDFToDouble(src1.key) = (UDFToDouble(src1.key) + 18.0))","edgeType":"PREDICATE"},{"sources":[4,2],"targets":[0,1],"expression":"(not CASE WHEN ((count(*) = 0)) THEN (false) WHEN (i is not null) THEN (true) WHEN (src1.key is null) THEN (null) WHEN ((count((UDFToDouble(src1.key) + 18.0)) < count(*))) THEN (true) ELSE (false) END)","edgeType":"PREDICATE"}],"vertices":[{"id":0,"vertexType":"COLUMN","vertexId":"key"},{"id":1,"vertexType":"COLUMN","vertexId":"value"},{"id":2,"vertexType":"COLUMN","vertexId":"default.src1.key"},{"id":3,"vertexType":"COLUMN","vertexId":"default.src1.value"},{"id":4,"vertexType":"TABLE","vertexId":"default.s
 rc1"}]}
 PREHOOK: query: select * from src1 a
 where not exists
   (select cint from alltypesorc b
@@ -196,7 +196,7 @@ PREHOOK: type: QUERY
 PREHOOK: Input: default@alltypesorc
 PREHOOK: Input: default@src1
 #### A masked pattern was here ####
-{"version":"1.0","engine":"tez","database":"default","hash":"53191056e05af9080a30de853e8cea9c","queryText":"select * from src1 a\nwhere not exists\n  (select cint from alltypesorc b\n   where a.key = b.ctinyint + 300)\nand key > 300","edges":[{"sources":[2],"targets":[0],"edgeType":"PROJECTION"},{"sources":[3],"targets":[1],"edgeType":"PROJECTION"},{"sources":[2],"targets":[0,1],"expression":"(UDFToDouble(a.key) > 300.0)","edgeType":"PREDICATE"},{"sources":[2,4],"targets":[0,1],"expression":"(UDFToDouble(a.key) = UDFToDouble((UDFToInteger(b.ctinyint) + 300)))","edgeType":"PREDICATE"},{"sources":[4],"targets":[0,1],"expression":"(UDFToInteger(b.ctinyint) + 300) is null","edgeType":"PREDICATE"}],"vertices":[{"id":0,"vertexType":"COLUMN","vertexId":"a.key"},{"id":1,"vertexType":"COLUMN","vertexId":"a.value"},{"id":2,"vertexType":"COLUMN","vertexId":"default.src1.key"},{"id":3,"vertexType":"COLUMN","vertexId":"default.src1.value"},{"id":4,"vertexType":"COLUMN","vertexId":"default.alltyp
 esorc.ctinyint"}]}
+{"version":"1.0","engine":"tez","database":"default","hash":"53191056e05af9080a30de853e8cea9c","queryText":"select * from src1 a\nwhere not exists\n  (select cint from alltypesorc b\n   where a.key = b.ctinyint + 300)\nand key > 300","edges":[{"sources":[2],"targets":[0],"edgeType":"PROJECTION"},{"sources":[3],"targets":[1],"edgeType":"PROJECTION"},{"sources":[2],"targets":[0,1],"expression":"(UDFToDouble(a.key) > 300.0)","edgeType":"PREDICATE"},{"sources":[2],"targets":[0,1],"expression":"(a.key = a.key)","edgeType":"PREDICATE"},{"sources":[4,2],"targets":[0,1],"expression":"(UDFToDouble((UDFToInteger(b.ctinyint) + 300)) = UDFToDouble(a.key))","edgeType":"PREDICATE"},{"sources":[],"targets":[0,1],"expression":"true is null","edgeType":"PREDICATE"}],"vertices":[{"id":0,"vertexType":"COLUMN","vertexId":"a.key"},{"id":1,"vertexType":"COLUMN","vertexId":"a.value"},{"id":2,"vertexType":"COLUMN","vertexId":"default.src1.key"},{"id":3,"vertexType":"COLUMN","vertexId":"default.src1.value"}
 ,{"id":4,"vertexType":"COLUMN","vertexId":"default.alltypesorc.ctinyint"}]}
 369	
 401	val_401
 406	val_406


Mime
View raw message