HIVE-18797 : ExprConstNodeDesc's getExprString should put appropriate qualifier with...
[hive.git] / ql / src / test / results / clientpositive / perf / tez / query36.q.out
1 PREHOOK: query: explain
2 select  
3     sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
4    ,i_category
5    ,i_class
6    ,grouping(i_category)+grouping(i_class) as lochierarchy
7    ,rank() over (
8         partition by grouping(i_category)+grouping(i_class),
9         case when grouping(i_class) = 0 then i_category end 
10         order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as rank_within_parent
11  from
12     store_sales
13    ,date_dim       d1
14    ,item
15    ,store
16  where
17     d1.d_year = 1999 
18  and d1.d_date_sk = ss_sold_date_sk
19  and i_item_sk  = ss_item_sk 
20  and s_store_sk  = ss_store_sk
21  and s_state in ('SD','FL','MI','LA',
22                  'MO','SC','AL','GA')
23  group by rollup(i_category,i_class)
24  order by
25    lochierarchy desc
26   ,case when lochierarchy = 0 then i_category end
27   ,rank_within_parent
28   limit 100
29 PREHOOK: type: QUERY
30 POSTHOOK: query: explain
31 select  
32     sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
33    ,i_category
34    ,i_class
35    ,grouping(i_category)+grouping(i_class) as lochierarchy
36    ,rank() over (
37         partition by grouping(i_category)+grouping(i_class),
38         case when grouping(i_class) = 0 then i_category end 
39         order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as rank_within_parent
40  from
41     store_sales
42    ,date_dim       d1
43    ,item
44    ,store
45  where
46     d1.d_year = 1999 
47  and d1.d_date_sk = ss_sold_date_sk
48  and i_item_sk  = ss_item_sk 
49  and s_store_sk  = ss_store_sk
50  and s_state in ('SD','FL','MI','LA',
51                  'MO','SC','AL','GA')
52  group by rollup(i_category,i_class)
53  order by
54    lochierarchy desc
55   ,case when lochierarchy = 0 then i_category end
56   ,rank_within_parent
57   limit 100
58 POSTHOOK: type: QUERY
59 Plan optimized by CBO.
60
61 Vertex dependency in root stage
62 Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
63 Reducer 3 <- Map 9 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
64 Reducer 4 <- Map 10 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
65 Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
66 Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
67 Reducer 7 <- Reducer 6 (SIMPLE_EDGE)
68
69 Stage-0
70   Fetch Operator
71     limit:-1
72     Stage-1
73       Reducer 7
74       File Output Operator [FS_36]
75         Limit [LIM_34] (rows=100 width=88)
76           Number of rows:100
77           Select Operator [SEL_33] (rows=1149975358 width=88)
78             Output:["_col0","_col1","_col2","_col3","_col4"]
79           <-Reducer 6 [SIMPLE_EDGE]
80             SHUFFLE [RS_32]
81               Select Operator [SEL_30] (rows=1149975358 width=88)
82                 Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
83                 PTF Operator [PTF_29] (rows=1149975358 width=88)
84                   Function definitions:[{},{"name:":"windowingtablefunction","order by:":"(_col2 / _col3) ASC NULLS FIRST","partition by:":"(grouping(_col4, 1) + grouping(_col4, 0)), CASE WHEN ((grouping(_col4, 0) = 0)) THEN (_col0) ELSE (UDFToString(null)) END"}]
85                   Select Operator [SEL_28] (rows=1149975358 width=88)
86                     Output:["_col0","_col1","_col2","_col3","_col4"]
87                   <-Reducer 5 [SIMPLE_EDGE]
88                     SHUFFLE [RS_27]
89                       PartitionCols:(grouping(_col4, 1) + grouping(_col4, 0)), CASE WHEN ((grouping(_col4, 0) = 0)) THEN (_col0) ELSE (UDFToString(null)) END
90                       Select Operator [SEL_26] (rows=1149975358 width=88)
91                         Output:["_col0","_col1","_col2","_col3","_col4"]
92                         Group By Operator [GBY_25] (rows=1149975358 width=88)
93                           Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0, KEY._col1, KEY._col2
94                         <-Reducer 4 [SIMPLE_EDGE]
95                           SHUFFLE [RS_24]
96                             PartitionCols:_col0, _col1, _col2
97                             Group By Operator [GBY_23] (rows=2299950717 width=88)
98                               Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col2)","sum(_col3)"],keys:_col0, _col1, 0L
99                               Select Operator [SEL_21] (rows=766650239 width=88)
100                                 Output:["_col0","_col1","_col2","_col3"]
101                                 Merge Join Operator [MERGEJOIN_52] (rows=766650239 width=88)
102                                   Conds:RS_18._col1=RS_19._col0(Inner),Output:["_col3","_col4","_col10","_col11"]
103                                 <-Map 10 [SIMPLE_EDGE]
104                                   SHUFFLE [RS_19]
105                                     PartitionCols:_col0
106                                     Select Operator [SEL_11] (rows=462000 width=1436)
107                                       Output:["_col0","_col1","_col2"]
108                                       Filter Operator [FIL_49] (rows=462000 width=1436)
109                                         predicate:i_item_sk is not null
110                                         TableScan [TS_9] (rows=462000 width=1436)
111                                           default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_class","i_category"]
112                                 <-Reducer 3 [SIMPLE_EDGE]
113                                   SHUFFLE [RS_18]
114                                     PartitionCols:_col1
115                                     Merge Join Operator [MERGEJOIN_51] (rows=696954748 width=88)
116                                       Conds:RS_15._col2=RS_16._col0(Inner),Output:["_col1","_col3","_col4"]
117                                     <-Map 9 [SIMPLE_EDGE]
118                                       SHUFFLE [RS_16]
119                                         PartitionCols:_col0
120                                         Select Operator [SEL_8] (rows=852 width=1910)
121                                           Output:["_col0"]
122                                           Filter Operator [FIL_48] (rows=852 width=1910)
123                                             predicate:((s_state) IN ('SD', 'FL', 'MI', 'LA', 'MO', 'SC', 'AL', 'GA') and s_store_sk is not null)
124                                             TableScan [TS_6] (rows=1704 width=1910)
125                                               default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_state"]
126                                     <-Reducer 2 [SIMPLE_EDGE]
127                                       SHUFFLE [RS_15]
128                                         PartitionCols:_col2
129                                         Merge Join Operator [MERGEJOIN_50] (rows=633595212 width=88)
130                                           Conds:RS_12._col0=RS_13._col0(Inner),Output:["_col1","_col2","_col3","_col4"]
131                                         <-Map 1 [SIMPLE_EDGE]
132                                           SHUFFLE [RS_12]
133                                             PartitionCols:_col0
134                                             Select Operator [SEL_2] (rows=575995635 width=88)
135                                               Output:["_col0","_col1","_col2","_col3","_col4"]
136                                               Filter Operator [FIL_46] (rows=575995635 width=88)
137                                                 predicate:(ss_item_sk is not null and ss_sold_date_sk is not null and ss_store_sk is not null)
138                                                 TableScan [TS_0] (rows=575995635 width=88)
139                                                   default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_item_sk","ss_store_sk","ss_ext_sales_price","ss_net_profit"]
140                                         <-Map 8 [SIMPLE_EDGE]
141                                           SHUFFLE [RS_13]
142                                             PartitionCols:_col0
143                                             Select Operator [SEL_5] (rows=36524 width=1119)
144                                               Output:["_col0"]
145                                               Filter Operator [FIL_47] (rows=36524 width=1119)
146                                                 predicate:((d_year = 1999) and d_date_sk is not null)
147                                                 TableScan [TS_3] (rows=73049 width=1119)
148                                                   default@date_dim,d1,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year"]
149