HIVE-18797 : ExprConstNodeDesc's getExprString should put appropriate qualifier with...
[hive.git] / ql / src / test / results / clientpositive / perf / tez / query8.q.out
1 PREHOOK: query: explain
2 select  s_store_name
3       ,sum(ss_net_profit)
4  from store_sales
5      ,date_dim
6      ,store,
7      (select ca_zip
8      from (
9      (SELECT substr(ca_zip,1,5) ca_zip
10       FROM customer_address
11       WHERE substr(ca_zip,1,5) IN (
12                           '89436','30868','65085','22977','83927','77557',
13                           '58429','40697','80614','10502','32779',
14                           '91137','61265','98294','17921','18427',
15                           '21203','59362','87291','84093','21505',
16                           '17184','10866','67898','25797','28055',
17                           '18377','80332','74535','21757','29742',
18                           '90885','29898','17819','40811','25990',
19                           '47513','89531','91068','10391','18846',
20                           '99223','82637','41368','83658','86199',
21                           '81625','26696','89338','88425','32200',
22                           '81427','19053','77471','36610','99823',
23                           '43276','41249','48584','83550','82276',
24                           '18842','78890','14090','38123','40936',
25                           '34425','19850','43286','80072','79188',
26                           '54191','11395','50497','84861','90733',
27                           '21068','57666','37119','25004','57835',
28                           '70067','62878','95806','19303','18840',
29                           '19124','29785','16737','16022','49613',
30                           '89977','68310','60069','98360','48649',
31                           '39050','41793','25002','27413','39736',
32                           '47208','16515','94808','57648','15009',
33                           '80015','42961','63982','21744','71853',
34                           '81087','67468','34175','64008','20261',
35                           '11201','51799','48043','45645','61163',
36                           '48375','36447','57042','21218','41100',
37                           '89951','22745','35851','83326','61125',
38                           '78298','80752','49858','52940','96976',
39                           '63792','11376','53582','18717','90226',
40                           '50530','94203','99447','27670','96577',
41                           '57856','56372','16165','23427','54561',
42                           '28806','44439','22926','30123','61451',
43                           '92397','56979','92309','70873','13355',
44                           '21801','46346','37562','56458','28286',
45                           '47306','99555','69399','26234','47546',
46                           '49661','88601','35943','39936','25632',
47                           '24611','44166','56648','30379','59785',
48                           '11110','14329','93815','52226','71381',
49                           '13842','25612','63294','14664','21077',
50                           '82626','18799','60915','81020','56447',
51                           '76619','11433','13414','42548','92713',
52                           '70467','30884','47484','16072','38936',
53                           '13036','88376','45539','35901','19506',
54                           '65690','73957','71850','49231','14276',
55                           '20005','18384','76615','11635','38177',
56                           '55607','41369','95447','58581','58149',
57                           '91946','33790','76232','75692','95464',
58                           '22246','51061','56692','53121','77209',
59                           '15482','10688','14868','45907','73520',
60                           '72666','25734','17959','24677','66446',
61                           '94627','53535','15560','41967','69297',
62                           '11929','59403','33283','52232','57350',
63                           '43933','40921','36635','10827','71286',
64                           '19736','80619','25251','95042','15526',
65                           '36496','55854','49124','81980','35375',
66                           '49157','63512','28944','14946','36503',
67                           '54010','18767','23969','43905','66979',
68                           '33113','21286','58471','59080','13395',
69                           '79144','70373','67031','38360','26705',
70                           '50906','52406','26066','73146','15884',
71                           '31897','30045','61068','45550','92454',
72                           '13376','14354','19770','22928','97790',
73                           '50723','46081','30202','14410','20223',
74                           '88500','67298','13261','14172','81410',
75                           '93578','83583','46047','94167','82564',
76                           '21156','15799','86709','37931','74703',
77                           '83103','23054','70470','72008','49247',
78                           '91911','69998','20961','70070','63197',
79                           '54853','88191','91830','49521','19454',
80                           '81450','89091','62378','25683','61869',
81                           '51744','36580','85778','36871','48121',
82                           '28810','83712','45486','67393','26935',
83                           '42393','20132','55349','86057','21309',
84                           '80218','10094','11357','48819','39734',
85                           '40758','30432','21204','29467','30214',
86                           '61024','55307','74621','11622','68908',
87                           '33032','52868','99194','99900','84936',
88                           '69036','99149','45013','32895','59004',
89                           '32322','14933','32936','33562','72550',
90                           '27385','58049','58200','16808','21360',
91                           '32961','18586','79307','15492'))
92      intersect
93      (select ca_zip
94       from (SELECT substr(ca_zip,1,5) ca_zip,count(*) cnt
95             FROM customer_address, customer
96             WHERE ca_address_sk = c_current_addr_sk and
97                   c_preferred_cust_flag='Y'
98             group by ca_zip
99             having count(*) > 10)A1))A2) V1
100  where ss_store_sk = s_store_sk
101   and ss_sold_date_sk = d_date_sk
102   and d_qoy = 1 and d_year = 2002
103   and (substr(s_zip,1,2) = substr(V1.ca_zip,1,2))
104  group by s_store_name
105  order by s_store_name
106  limit 100
107 PREHOOK: type: QUERY
108 POSTHOOK: query: explain
109 select  s_store_name
110       ,sum(ss_net_profit)
111  from store_sales
112      ,date_dim
113      ,store,
114      (select ca_zip
115      from (
116      (SELECT substr(ca_zip,1,5) ca_zip
117       FROM customer_address
118       WHERE substr(ca_zip,1,5) IN (
119                           '89436','30868','65085','22977','83927','77557',
120                           '58429','40697','80614','10502','32779',
121                           '91137','61265','98294','17921','18427',
122                           '21203','59362','87291','84093','21505',
123                           '17184','10866','67898','25797','28055',
124                           '18377','80332','74535','21757','29742',
125                           '90885','29898','17819','40811','25990',
126                           '47513','89531','91068','10391','18846',
127                           '99223','82637','41368','83658','86199',
128                           '81625','26696','89338','88425','32200',
129                           '81427','19053','77471','36610','99823',
130                           '43276','41249','48584','83550','82276',
131                           '18842','78890','14090','38123','40936',
132                           '34425','19850','43286','80072','79188',
133                           '54191','11395','50497','84861','90733',
134                           '21068','57666','37119','25004','57835',
135                           '70067','62878','95806','19303','18840',
136                           '19124','29785','16737','16022','49613',
137                           '89977','68310','60069','98360','48649',
138                           '39050','41793','25002','27413','39736',
139                           '47208','16515','94808','57648','15009',
140                           '80015','42961','63982','21744','71853',
141                           '81087','67468','34175','64008','20261',
142                           '11201','51799','48043','45645','61163',
143                           '48375','36447','57042','21218','41100',
144                           '89951','22745','35851','83326','61125',
145                           '78298','80752','49858','52940','96976',
146                           '63792','11376','53582','18717','90226',
147                           '50530','94203','99447','27670','96577',
148                           '57856','56372','16165','23427','54561',
149                           '28806','44439','22926','30123','61451',
150                           '92397','56979','92309','70873','13355',
151                           '21801','46346','37562','56458','28286',
152                           '47306','99555','69399','26234','47546',
153                           '49661','88601','35943','39936','25632',
154                           '24611','44166','56648','30379','59785',
155                           '11110','14329','93815','52226','71381',
156                           '13842','25612','63294','14664','21077',
157                           '82626','18799','60915','81020','56447',
158                           '76619','11433','13414','42548','92713',
159                           '70467','30884','47484','16072','38936',
160                           '13036','88376','45539','35901','19506',
161                           '65690','73957','71850','49231','14276',
162                           '20005','18384','76615','11635','38177',
163                           '55607','41369','95447','58581','58149',
164                           '91946','33790','76232','75692','95464',
165                           '22246','51061','56692','53121','77209',
166                           '15482','10688','14868','45907','73520',
167                           '72666','25734','17959','24677','66446',
168                           '94627','53535','15560','41967','69297',
169                           '11929','59403','33283','52232','57350',
170                           '43933','40921','36635','10827','71286',
171                           '19736','80619','25251','95042','15526',
172                           '36496','55854','49124','81980','35375',
173                           '49157','63512','28944','14946','36503',
174                           '54010','18767','23969','43905','66979',
175                           '33113','21286','58471','59080','13395',
176                           '79144','70373','67031','38360','26705',
177                           '50906','52406','26066','73146','15884',
178                           '31897','30045','61068','45550','92454',
179                           '13376','14354','19770','22928','97790',
180                           '50723','46081','30202','14410','20223',
181                           '88500','67298','13261','14172','81410',
182                           '93578','83583','46047','94167','82564',
183                           '21156','15799','86709','37931','74703',
184                           '83103','23054','70470','72008','49247',
185                           '91911','69998','20961','70070','63197',
186                           '54853','88191','91830','49521','19454',
187                           '81450','89091','62378','25683','61869',
188                           '51744','36580','85778','36871','48121',
189                           '28810','83712','45486','67393','26935',
190                           '42393','20132','55349','86057','21309',
191                           '80218','10094','11357','48819','39734',
192                           '40758','30432','21204','29467','30214',
193                           '61024','55307','74621','11622','68908',
194                           '33032','52868','99194','99900','84936',
195                           '69036','99149','45013','32895','59004',
196                           '32322','14933','32936','33562','72550',
197                           '27385','58049','58200','16808','21360',
198                           '32961','18586','79307','15492'))
199      intersect
200      (select ca_zip
201       from (SELECT substr(ca_zip,1,5) ca_zip,count(*) cnt
202             FROM customer_address, customer
203             WHERE ca_address_sk = c_current_addr_sk and
204                   c_preferred_cust_flag='Y'
205             group by ca_zip
206             having count(*) > 10)A1))A2) V1
207  where ss_store_sk = s_store_sk
208   and ss_sold_date_sk = d_date_sk
209   and d_qoy = 1 and d_year = 2002
210   and (substr(s_zip,1,2) = substr(V1.ca_zip,1,2))
211  group by s_store_name
212  order by s_store_name
213  limit 100
214 POSTHOOK: type: QUERY
215 Plan optimized by CBO.
216
217 Vertex dependency in root stage
218 Reducer 10 <- Union 9 (SIMPLE_EDGE)
219 Reducer 11 <- Map 17 (SIMPLE_EDGE), Reducer 10 (SIMPLE_EDGE)
220 Reducer 13 <- Map 12 (SIMPLE_EDGE), Map 16 (SIMPLE_EDGE)
221 Reducer 14 <- Reducer 13 (SIMPLE_EDGE)
222 Reducer 15 <- Reducer 14 (SIMPLE_EDGE), Union 9 (CONTAINS)
223 Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 6 (SIMPLE_EDGE)
224 Reducer 3 <- Reducer 11 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
225 Reducer 4 <- Reducer 3 (SIMPLE_EDGE)
226 Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
227 Reducer 8 <- Map 7 (SIMPLE_EDGE), Union 9 (CONTAINS)
228
229 Stage-0
230   Fetch Operator
231     limit:100
232     Stage-1
233       Reducer 5
234       File Output Operator [FS_63]
235         Limit [LIM_62] (rows=100 width=88)
236           Number of rows:100
237           Select Operator [SEL_61] (rows=348477374 width=88)
238             Output:["_col0","_col1"]
239           <-Reducer 4 [SIMPLE_EDGE]
240             SHUFFLE [RS_60]
241               Group By Operator [GBY_58] (rows=348477374 width=88)
242                 Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0
243               <-Reducer 3 [SIMPLE_EDGE]
244                 SHUFFLE [RS_57]
245                   PartitionCols:_col0
246                   Group By Operator [GBY_56] (rows=696954748 width=88)
247                     Output:["_col0","_col1"],aggregations:["sum(_col2)"],keys:_col8
248                     Merge Join Operator [MERGEJOIN_89] (rows=696954748 width=88)
249                       Conds:RS_52._col1=RS_53._col1(Inner),Output:["_col2","_col8"]
250                     <-Reducer 11 [SIMPLE_EDGE]
251                       SHUFFLE [RS_53]
252                         PartitionCols:_col1
253                         Merge Join Operator [MERGEJOIN_88] (rows=1874 width=1911)
254                           Conds:RS_45.substr(_col0, 1, 2)=RS_46.substr(_col2, 1, 2)(Inner),Output:["_col1","_col2"]
255                         <-Map 17 [SIMPLE_EDGE]
256                           SHUFFLE [RS_46]
257                             PartitionCols:substr(_col2, 1, 2)
258                             Select Operator [SEL_44] (rows=1704 width=1910)
259                               Output:["_col0","_col1","_col2"]
260                               Filter Operator [FIL_85] (rows=1704 width=1910)
261                                 predicate:(s_store_sk is not null and substr(s_zip, 1, 2) is not null)
262                                 TableScan [TS_42] (rows=1704 width=1910)
263                                   default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_store_name","s_zip"]
264                         <-Reducer 10 [SIMPLE_EDGE]
265                           SHUFFLE [RS_45]
266                             PartitionCols:substr(_col0, 1, 2)
267                             Select Operator [SEL_41] (rows=1 width=1014)
268                               Output:["_col0"]
269                               Filter Operator [FIL_40] (rows=1 width=1014)
270                                 predicate:(_col1 = 2L)
271                                 Group By Operator [GBY_39] (rows=6833333 width=1014)
272                                   Output:["_col0","_col1"],aggregations:["count(VALUE._col0)"],keys:KEY._col0
273                                 <-Union 9 [SIMPLE_EDGE]
274                                   <-Reducer 15 [CONTAINS]
275                                     Reduce Output Operator [RS_38]
276                                       PartitionCols:_col0
277                                       Group By Operator [GBY_37] (rows=13666666 width=1014)
278                                         Output:["_col0","_col1"],aggregations:["count(_col1)"],keys:_col0
279                                         Group By Operator [GBY_32] (rows=3666666 width=1014)
280                                           Output:["_col0","_col1"],aggregations:["count(VALUE._col0)"],keys:KEY._col0
281                                         <-Reducer 14 [SIMPLE_EDGE]
282                                           SHUFFLE [RS_31]
283                                             PartitionCols:_col0
284                                             Group By Operator [GBY_30] (rows=7333333 width=1014)
285                                               Output:["_col0","_col1"],aggregations:["count()"],keys:_col0
286                                               Select Operator [SEL_28] (rows=7333333 width=1014)
287                                                 Output:["_col0"]
288                                                 Filter Operator [FIL_27] (rows=7333333 width=1014)
289                                                   predicate:(_col1 > 10L)
290                                                   Group By Operator [GBY_26] (rows=22000000 width=1014)
291                                                     Output:["_col0","_col1"],aggregations:["count(VALUE._col0)"],keys:KEY._col0
292                                                   <-Reducer 13 [SIMPLE_EDGE]
293                                                     SHUFFLE [RS_25]
294                                                       PartitionCols:_col0
295                                                       Group By Operator [GBY_24] (rows=44000000 width=1014)
296                                                         Output:["_col0","_col1"],aggregations:["count()"],keys:_col1
297                                                         Merge Join Operator [MERGEJOIN_87] (rows=44000000 width=1014)
298                                                           Conds:RS_20._col0=RS_21._col0(Inner),Output:["_col1"]
299                                                         <-Map 12 [SIMPLE_EDGE]
300                                                           SHUFFLE [RS_20]
301                                                             PartitionCols:_col0
302                                                             Select Operator [SEL_16] (rows=40000000 width=1014)
303                                                               Output:["_col0","_col1"]
304                                                               Filter Operator [FIL_83] (rows=40000000 width=1014)
305                                                                 predicate:(ca_address_sk is not null and substr(substr(ca_zip, 1, 5), 1, 2) is not null)
306                                                                 TableScan [TS_14] (rows=40000000 width=1014)
307                                                                   default@customer_address,customer_address,Tbl:COMPLETE,Col:NONE,Output:["ca_address_sk","ca_zip"]
308                                                         <-Map 16 [SIMPLE_EDGE]
309                                                           SHUFFLE [RS_21]
310                                                             PartitionCols:_col0
311                                                             Select Operator [SEL_19] (rows=40000000 width=860)
312                                                               Output:["_col0"]
313                                                               Filter Operator [FIL_84] (rows=40000000 width=860)
314                                                                 predicate:((c_preferred_cust_flag = 'Y') and c_current_addr_sk is not null)
315                                                                 TableScan [TS_17] (rows=80000000 width=860)
316                                                                   default@customer,customer,Tbl:COMPLETE,Col:NONE,Output:["c_current_addr_sk","c_preferred_cust_flag"]
317                                   <-Reducer 8 [CONTAINS]
318                                     Reduce Output Operator [RS_38]
319                                       PartitionCols:_col0
320                                       Group By Operator [GBY_37] (rows=13666666 width=1014)
321                                         Output:["_col0","_col1"],aggregations:["count(_col1)"],keys:_col0
322                                         Group By Operator [GBY_12] (rows=10000000 width=1014)
323                                           Output:["_col0","_col1"],aggregations:["count(VALUE._col0)"],keys:KEY._col0
324                                         <-Map 7 [SIMPLE_EDGE]
325                                           SHUFFLE [RS_11]
326                                             PartitionCols:_col0
327                                             Group By Operator [GBY_10] (rows=20000000 width=1014)
328                                               Output:["_col0","_col1"],aggregations:["count()"],keys:_col0
329                                               Select Operator [SEL_8] (rows=20000000 width=1014)
330                                                 Output:["_col0"]
331                                                 Filter Operator [FIL_81] (rows=20000000 width=1014)
332                                                   predicate:((substr(ca_zip, 1, 5)) IN ('89436', '30868', '65085', '22977', '83927', '77557', '58429', '40697', '80614', '10502', '32779', '91137', '61265', '98294', '17921', '18427', '21203', '59362', '87291', '84093', '21505', '17184', '10866', '67898', '25797', '28055', '18377', '80332', '74535', '21757', '29742', '90885', '29898', '17819', '40811', '25990', '47513', '89531', '91068', '10391', '18846', '99223', '82637', '41368', '83658', '86199', '81625', '26696', '89338', '88425', '32200', '81427', '19053', '77471', '36610', '99823', '43276', '41249', '48584', '83550', '82276', '18842', '78890', '14090', '38123', '40936', '34425', '19850', '43286', '80072', '79188', '54191', '11395', '50497', '84861', '90733', '21068', '57666', '37119', '25004', '57835', '70067', '62878', '95806', '19303', '18840', '19124', '29785', '16737', '16022', '49613', '89977', '68310', '60069', '98360', '48649', '39050', '41793', '25002', '27413', '39736', '47208', '16515', '94808', '57648', '15009', '80015', '42961', '63982', '21744', '71853', '81087', '67468', '34175', '64008', '20261', '11201', '51799', '48043', '45645', '61163', '48375', '36447', '57042', '21218', '41100', '89951', '22745', '35851', '83326', '61125', '78298', '80752', '49858', '52940', '96976', '63792', '11376', '53582', '18717', '90226', '50530', '94203', '99447', '27670', '96577', '57856', '56372', '16165', '23427', '54561', '28806', '44439', '22926', '30123', '61451', '92397', '56979', '92309', '70873', '13355', '21801', '46346', '37562', '56458', '28286', '47306', '99555', '69399', '26234', '47546', '49661', '88601', '35943', '39936', '25632', '24611', '44166', '56648', '30379', '59785', '11110', '14329', '93815', '52226', '71381', '13842', '25612', '63294', '14664', '21077', '82626', '18799', '60915', '81020', '56447', '76619', '11433', '13414', '42548', '92713', '70467', '30884', '47484', '16072', '38936', '13036', '88376', '45539', '35901', '19506', '65690', '73957', '71850', '49231', '14276', '20005', '18384', '76615', '11635', '38177', '55607', '41369', '95447', '58581', '58149', '91946', '33790', '76232', '75692', '95464', '22246', '51061', '56692', '53121', '77209', '15482', '10688', '14868', '45907', '73520', '72666', '25734', '17959', '24677', '66446', '94627', '53535', '15560', '41967', '69297', '11929', '59403', '33283', '52232', '57350', '43933', '40921', '36635', '10827', '71286', '19736', '80619', '25251', '95042', '15526', '36496', '55854', '49124', '81980', '35375', '49157', '63512', '28944', '14946', '36503', '54010', '18767', '23969', '43905', '66979', '33113', '21286', '58471', '59080', '13395', '79144', '70373', '67031', '38360', '26705', '50906', '52406', '26066', '73146', '15884', '31897', '30045', '61068', '45550', '92454', '13376', '14354', '19770', '22928', '97790', '50723', '46081', '30202', '14410', '20223', '88500', '67298', '13261', '14172', '81410', '93578', '83583', '46047', '94167', '82564', '21156', '15799', '86709', '37931', '74703', '83103', '23054', '70470', '72008', '49247', '91911', '69998', '20961', '70070', '63197', '54853', '88191', '91830', '49521', '19454', '81450', '89091', '62378', '25683', '61869', '51744', '36580', '85778', '36871', '48121', '28810', '83712', '45486', '67393', '26935', '42393', '20132', '55349', '86057', '21309', '80218', '10094', '11357', '48819', '39734', '40758', '30432', '21204', '29467', '30214', '61024', '55307', '74621', '11622', '68908', '33032', '52868', '99194', '99900', '84936', '69036', '99149', '45013', '32895', '59004', '32322', '14933', '32936', '33562', '72550', '27385', '58049', '58200', '16808', '21360', '32961', '18586', '79307', '15492') and substr(substr(ca_zip, 1, 5), 1, 2) is not null)
333                                                   TableScan [TS_6] (rows=40000000 width=1014)
334                                                     default@customer_address,customer_address,Tbl:COMPLETE,Col:NONE,Output:["ca_zip"]
335                     <-Reducer 2 [SIMPLE_EDGE]
336                       SHUFFLE [RS_52]
337                         PartitionCols:_col1
338                         Merge Join Operator [MERGEJOIN_86] (rows=633595212 width=88)
339                           Conds:RS_49._col0=RS_50._col0(Inner),Output:["_col1","_col2"]
340                         <-Map 1 [SIMPLE_EDGE]
341                           SHUFFLE [RS_49]
342                             PartitionCols:_col0
343                             Select Operator [SEL_2] (rows=575995635 width=88)
344                               Output:["_col0","_col1","_col2"]
345                               Filter Operator [FIL_78] (rows=575995635 width=88)
346                                 predicate:(ss_sold_date_sk is not null and ss_store_sk is not null)
347                                 TableScan [TS_0] (rows=575995635 width=88)
348                                   default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_store_sk","ss_net_profit"]
349                         <-Map 6 [SIMPLE_EDGE]
350                           SHUFFLE [RS_50]
351                             PartitionCols:_col0
352                             Select Operator [SEL_5] (rows=18262 width=1119)
353                               Output:["_col0"]
354                               Filter Operator [FIL_79] (rows=18262 width=1119)
355                                 predicate:((d_qoy = 1) and (d_year = 2002) and d_date_sk is not null)
356                                 TableScan [TS_3] (rows=73049 width=1119)
357                                   default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year","d_qoy"]
358