HIVE-18797 : ExprConstNodeDesc's getExprString should put appropriate qualifier with...
[hive.git] / ql / src / test / results / clientpositive / perf / tez / query18.q.out
1 PREHOOK: query: explain
2 select  i_item_id,
3         ca_country,
4         ca_state, 
5         ca_county,
6         avg( cast(cs_quantity as numeric(12,2))) agg1,
7         avg( cast(cs_list_price as numeric(12,2))) agg2,
8         avg( cast(cs_coupon_amt as numeric(12,2))) agg3,
9         avg( cast(cs_sales_price as numeric(12,2))) agg4,
10         avg( cast(cs_net_profit as numeric(12,2))) agg5,
11         avg( cast(c_birth_year as numeric(12,2))) agg6,
12         avg( cast(cd1.cd_dep_count as numeric(12,2))) agg7
13  from catalog_sales, customer_demographics cd1, 
14       customer_demographics cd2, customer, customer_address, date_dim, item
15  where cs_sold_date_sk = d_date_sk and
16        cs_item_sk = i_item_sk and
17        cs_bill_cdemo_sk = cd1.cd_demo_sk and
18        cs_bill_customer_sk = c_customer_sk and
19        cd1.cd_gender = 'M' and 
20        cd1.cd_education_status = 'College' and
21        c_current_cdemo_sk = cd2.cd_demo_sk and
22        c_current_addr_sk = ca_address_sk and
23        c_birth_month in (9,5,12,4,1,10) and
24        d_year = 2001 and
25        ca_state in ('ND','WI','AL'
26                    ,'NC','OK','MS','TN')
27  group by rollup (i_item_id, ca_country, ca_state, ca_county)
28  order by ca_country,
29         ca_state, 
30         ca_county,
31         i_item_id
32  limit 100
33 PREHOOK: type: QUERY
34 POSTHOOK: query: explain
35 select  i_item_id,
36         ca_country,
37         ca_state, 
38         ca_county,
39         avg( cast(cs_quantity as numeric(12,2))) agg1,
40         avg( cast(cs_list_price as numeric(12,2))) agg2,
41         avg( cast(cs_coupon_amt as numeric(12,2))) agg3,
42         avg( cast(cs_sales_price as numeric(12,2))) agg4,
43         avg( cast(cs_net_profit as numeric(12,2))) agg5,
44         avg( cast(c_birth_year as numeric(12,2))) agg6,
45         avg( cast(cd1.cd_dep_count as numeric(12,2))) agg7
46  from catalog_sales, customer_demographics cd1, 
47       customer_demographics cd2, customer, customer_address, date_dim, item
48  where cs_sold_date_sk = d_date_sk and
49        cs_item_sk = i_item_sk and
50        cs_bill_cdemo_sk = cd1.cd_demo_sk and
51        cs_bill_customer_sk = c_customer_sk and
52        cd1.cd_gender = 'M' and 
53        cd1.cd_education_status = 'College' and
54        c_current_cdemo_sk = cd2.cd_demo_sk and
55        c_current_addr_sk = ca_address_sk and
56        c_birth_month in (9,5,12,4,1,10) and
57        d_year = 2001 and
58        ca_state in ('ND','WI','AL'
59                    ,'NC','OK','MS','TN')
60  group by rollup (i_item_id, ca_country, ca_state, ca_county)
61  order by ca_country,
62         ca_state, 
63         ca_county,
64         i_item_id
65  limit 100
66 POSTHOOK: type: QUERY
67 Plan optimized by CBO.
68
69 Vertex dependency in root stage
70 Reducer 10 <- Map 13 (SIMPLE_EDGE), Map 9 (SIMPLE_EDGE)
71 Reducer 11 <- Map 14 (SIMPLE_EDGE), Reducer 10 (SIMPLE_EDGE)
72 Reducer 12 <- Map 15 (SIMPLE_EDGE), Reducer 11 (SIMPLE_EDGE)
73 Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE)
74 Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
75 Reducer 4 <- Reducer 12 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
76 Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
77 Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
78
79 Stage-0
80   Fetch Operator
81     limit:100
82     Stage-1
83       Reducer 6
84       File Output Operator [FS_49]
85         Limit [LIM_48] (rows=100 width=135)
86           Number of rows:100
87           Select Operator [SEL_47] (rows=1054114882 width=135)
88             Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10"]
89           <-Reducer 5 [SIMPLE_EDGE]
90             SHUFFLE [RS_46]
91               Select Operator [SEL_45] (rows=1054114882 width=135)
92                 Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10"]
93                 Group By Operator [GBY_44] (rows=1054114882 width=135)
94                   Output:["_col0","_col1","_col2","_col3","_col5","_col6","_col7","_col8","_col9","_col10","_col11"],aggregations:["avg(VALUE._col0)","avg(VALUE._col1)","avg(VALUE._col2)","avg(VALUE._col3)","avg(VALUE._col4)","avg(VALUE._col5)","avg(VALUE._col6)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4
95                 <-Reducer 4 [SIMPLE_EDGE]
96                   SHUFFLE [RS_43]
97                     PartitionCols:_col0, _col1, _col2, _col3, _col4
98                     Group By Operator [GBY_42] (rows=2108229765 width=135)
99                       Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11"],aggregations:["avg(_col4)","avg(_col5)","avg(_col6)","avg(_col7)","avg(_col8)","avg(_col9)","avg(_col10)"],keys:_col0, _col1, _col2, _col3, 0L
100                       Select Operator [SEL_40] (rows=421645953 width=135)
101                         Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10"]
102                         Merge Join Operator [MERGEJOIN_83] (rows=421645953 width=135)
103                           Conds:RS_37._col0=RS_38._col3(Inner),Output:["_col4","_col6","_col7","_col8","_col11","_col16","_col17","_col18","_col19","_col20","_col26"]
104                         <-Reducer 12 [SIMPLE_EDGE]
105                           SHUFFLE [RS_38]
106                             PartitionCols:_col3
107                             Select Operator [SEL_30] (rows=383314495 width=135)
108                               Output:["_col1","_col3","_col6","_col7","_col8","_col9","_col10","_col16"]
109                               Merge Join Operator [MERGEJOIN_82] (rows=383314495 width=135)
110                                 Conds:RS_27._col3=RS_28._col0(Inner),Output:["_col1","_col4","_col5","_col6","_col7","_col8","_col14","_col16"]
111                               <-Map 15 [SIMPLE_EDGE]
112                                 SHUFFLE [RS_28]
113                                   PartitionCols:_col0
114                                   Select Operator [SEL_20] (rows=462000 width=1436)
115                                     Output:["_col0","_col1"]
116                                     Filter Operator [FIL_77] (rows=462000 width=1436)
117                                       predicate:i_item_sk is not null
118                                       TableScan [TS_18] (rows=462000 width=1436)
119                                         default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_item_id"]
120                               <-Reducer 11 [SIMPLE_EDGE]
121                                 SHUFFLE [RS_27]
122                                   PartitionCols:_col3
123                                   Merge Join Operator [MERGEJOIN_81] (rows=348467716 width=135)
124                                     Conds:RS_24._col2=RS_25._col0(Inner),Output:["_col1","_col3","_col4","_col5","_col6","_col7","_col8","_col14"]
125                                   <-Map 14 [SIMPLE_EDGE]
126                                     SHUFFLE [RS_25]
127                                       PartitionCols:_col0
128                                       Select Operator [SEL_17] (rows=465450 width=385)
129                                         Output:["_col0","_col3"]
130                                         Filter Operator [FIL_76] (rows=465450 width=385)
131                                           predicate:((cd_education_status = 'College') and (cd_gender = 'M') and cd_demo_sk is not null)
132                                           TableScan [TS_15] (rows=1861800 width=385)
133                                             default@customer_demographics,cd1,Tbl:COMPLETE,Col:NONE,Output:["cd_demo_sk","cd_gender","cd_education_status","cd_dep_count"]
134                                   <-Reducer 10 [SIMPLE_EDGE]
135                                     SHUFFLE [RS_24]
136                                       PartitionCols:_col2
137                                       Merge Join Operator [MERGEJOIN_80] (rows=316788826 width=135)
138                                         Conds:RS_21._col0=RS_22._col0(Inner),Output:["_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"]
139                                       <-Map 13 [SIMPLE_EDGE]
140                                         SHUFFLE [RS_22]
141                                           PartitionCols:_col0
142                                           Select Operator [SEL_14] (rows=36524 width=1119)
143                                             Output:["_col0"]
144                                             Filter Operator [FIL_75] (rows=36524 width=1119)
145                                               predicate:((d_year = 2001) and d_date_sk is not null)
146                                               TableScan [TS_12] (rows=73049 width=1119)
147                                                 default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year"]
148                                       <-Map 9 [SIMPLE_EDGE]
149                                         SHUFFLE [RS_21]
150                                           PartitionCols:_col0
151                                           Select Operator [SEL_11] (rows=287989836 width=135)
152                                             Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"]
153                                             Filter Operator [FIL_74] (rows=287989836 width=135)
154                                               predicate:(cs_bill_cdemo_sk is not null and cs_bill_customer_sk is not null and cs_item_sk is not null and cs_sold_date_sk is not null)
155                                               TableScan [TS_9] (rows=287989836 width=135)
156                                                 default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:NONE,Output:["cs_sold_date_sk","cs_bill_customer_sk","cs_bill_cdemo_sk","cs_item_sk","cs_quantity","cs_list_price","cs_sales_price","cs_coupon_amt","cs_net_profit"]
157                         <-Reducer 3 [SIMPLE_EDGE]
158                           SHUFFLE [RS_37]
159                             PartitionCols:_col0
160                             Merge Join Operator [MERGEJOIN_79] (rows=48400001 width=860)
161                               Conds:RS_34._col1=RS_35._col0(Inner),Output:["_col0","_col4","_col6","_col7","_col8"]
162                             <-Map 8 [SIMPLE_EDGE]
163                               SHUFFLE [RS_35]
164                                 PartitionCols:_col0
165                                 Select Operator [SEL_8] (rows=1861800 width=385)
166                                   Output:["_col0"]
167                                   Filter Operator [FIL_73] (rows=1861800 width=385)
168                                     predicate:cd_demo_sk is not null
169                                     TableScan [TS_6] (rows=1861800 width=385)
170                                       default@customer_demographics,cd2,Tbl:COMPLETE,Col:NONE,Output:["cd_demo_sk"]
171                             <-Reducer 2 [SIMPLE_EDGE]
172                               SHUFFLE [RS_34]
173                                 PartitionCols:_col1
174                                 Merge Join Operator [MERGEJOIN_78] (rows=44000000 width=860)
175                                   Conds:RS_31._col2=RS_32._col0(Inner),Output:["_col0","_col1","_col4","_col6","_col7","_col8"]
176                                 <-Map 1 [SIMPLE_EDGE]
177                                   SHUFFLE [RS_31]
178                                     PartitionCols:_col2
179                                     Select Operator [SEL_2] (rows=40000000 width=860)
180                                       Output:["_col0","_col1","_col2","_col4"]
181                                       Filter Operator [FIL_71] (rows=40000000 width=860)
182                                         predicate:((c_birth_month) IN (9, 5, 12, 4, 1, 10) and c_current_addr_sk is not null and c_current_cdemo_sk is not null and c_customer_sk is not null)
183                                         TableScan [TS_0] (rows=80000000 width=860)
184                                           default@customer,customer,Tbl:COMPLETE,Col:NONE,Output:["c_customer_sk","c_current_cdemo_sk","c_current_addr_sk","c_birth_month","c_birth_year"]
185                                 <-Map 7 [SIMPLE_EDGE]
186                                   SHUFFLE [RS_32]
187                                     PartitionCols:_col0
188                                     Select Operator [SEL_5] (rows=20000000 width=1014)
189                                       Output:["_col0","_col1","_col2","_col3"]
190                                       Filter Operator [FIL_72] (rows=20000000 width=1014)
191                                         predicate:((ca_state) IN ('ND', 'WI', 'AL', 'NC', 'OK', 'MS', 'TN') and ca_address_sk is not null)
192                                         TableScan [TS_3] (rows=40000000 width=1014)
193                                           default@customer_address,customer_address,Tbl:COMPLETE,Col:NONE,Output:["ca_address_sk","ca_county","ca_state","ca_country"]
194