HIVE-18797 : ExprConstNodeDesc's getExprString should put appropriate qualifier with...
[hive.git] / ql / src / test / results / clientpositive / perf / tez / query40.q.out
1 PREHOOK: query: explain
2 select  
3    w_state
4   ,i_item_id
5   ,sum(case when (cast(d_date as date) < cast ('1998-04-08' as date)) 
6                 then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_before
7   ,sum(case when (cast(d_date as date) >= cast ('1998-04-08' as date)) 
8                 then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_after
9  from
10    catalog_sales left outer join catalog_returns on
11        (cs_order_number = cr_order_number 
12         and cs_item_sk = cr_item_sk)
13   ,warehouse 
14   ,item
15   ,date_dim
16  where
17      i_current_price between 0.99 and 1.49
18  and i_item_sk          = cs_item_sk
19  and cs_warehouse_sk    = w_warehouse_sk 
20  and cs_sold_date_sk    = d_date_sk
21  and d_date between (cast ('1998-04-08' as date) - 30 days)
22                 and (cast ('1998-04-08' as date) + 30 days) 
23  group by
24     w_state,i_item_id
25  order by w_state,i_item_id
26 limit 100
27 PREHOOK: type: QUERY
28 POSTHOOK: query: explain
29 select  
30    w_state
31   ,i_item_id
32   ,sum(case when (cast(d_date as date) < cast ('1998-04-08' as date)) 
33                 then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_before
34   ,sum(case when (cast(d_date as date) >= cast ('1998-04-08' as date)) 
35                 then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_after
36  from
37    catalog_sales left outer join catalog_returns on
38        (cs_order_number = cr_order_number 
39         and cs_item_sk = cr_item_sk)
40   ,warehouse 
41   ,item
42   ,date_dim
43  where
44      i_current_price between 0.99 and 1.49
45  and i_item_sk          = cs_item_sk
46  and cs_warehouse_sk    = w_warehouse_sk 
47  and cs_sold_date_sk    = d_date_sk
48  and d_date between (cast ('1998-04-08' as date) - 30 days)
49                 and (cast ('1998-04-08' as date) + 30 days) 
50  group by
51     w_state,i_item_id
52  order by w_state,i_item_id
53 limit 100
54 POSTHOOK: type: QUERY
55 Plan optimized by CBO.
56
57 Vertex dependency in root stage
58 Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
59 Reducer 3 <- Map 9 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
60 Reducer 4 <- Map 10 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
61 Reducer 5 <- Map 11 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
62 Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
63 Reducer 7 <- Reducer 6 (SIMPLE_EDGE)
64
65 Stage-0
66   Fetch Operator
67     limit:100
68     Stage-1
69       Reducer 7
70       File Output Operator [FS_36]
71         Limit [LIM_35] (rows=100 width=135)
72           Number of rows:100
73           Select Operator [SEL_34] (rows=210822976 width=135)
74             Output:["_col0","_col1","_col2","_col3"]
75           <-Reducer 6 [SIMPLE_EDGE]
76             SHUFFLE [RS_33]
77               Group By Operator [GBY_31] (rows=210822976 width=135)
78                 Output:["_col0","_col1","_col2","_col3"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0, KEY._col1
79               <-Reducer 5 [SIMPLE_EDGE]
80                 SHUFFLE [RS_30]
81                   PartitionCols:_col0, _col1
82                   Group By Operator [GBY_29] (rows=421645953 width=135)
83                     Output:["_col0","_col1","_col2","_col3"],aggregations:["sum(_col2)","sum(_col3)"],keys:_col0, _col1
84                     Select Operator [SEL_27] (rows=421645953 width=135)
85                       Output:["_col0","_col1","_col2","_col3"]
86                       Merge Join Operator [MERGEJOIN_58] (rows=421645953 width=135)
87                         Conds:RS_24._col1=RS_25._col0(Inner),Output:["_col4","_col7","_col9","_col11","_col14"]
88                       <-Map 11 [SIMPLE_EDGE]
89                         SHUFFLE [RS_25]
90                           PartitionCols:_col0
91                           Select Operator [SEL_14] (rows=27 width=1029)
92                             Output:["_col0","_col1"]
93                             Filter Operator [FIL_54] (rows=27 width=1029)
94                               predicate:w_warehouse_sk is not null
95                               TableScan [TS_12] (rows=27 width=1029)
96                                 default@warehouse,warehouse,Tbl:COMPLETE,Col:NONE,Output:["w_warehouse_sk","w_state"]
97                       <-Reducer 4 [SIMPLE_EDGE]
98                         SHUFFLE [RS_24]
99                           PartitionCols:_col1
100                           Merge Join Operator [MERGEJOIN_57] (rows=383314495 width=135)
101                             Conds:RS_21._col2=RS_22._col0(Inner),Output:["_col1","_col4","_col7","_col9","_col11"]
102                           <-Map 10 [SIMPLE_EDGE]
103                             SHUFFLE [RS_22]
104                               PartitionCols:_col0
105                               Select Operator [SEL_11] (rows=51333 width=1436)
106                                 Output:["_col0","_col1"]
107                                 Filter Operator [FIL_53] (rows=51333 width=1436)
108                                   predicate:(i_current_price BETWEEN 0.99 AND 1.49 and i_item_sk is not null)
109                                   TableScan [TS_9] (rows=462000 width=1436)
110                                     default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_item_id","i_current_price"]
111                           <-Reducer 3 [SIMPLE_EDGE]
112                             SHUFFLE [RS_21]
113                               PartitionCols:_col2
114                               Merge Join Operator [MERGEJOIN_56] (rows=348467716 width=135)
115                                 Conds:RS_18._col0=RS_19._col0(Inner),Output:["_col1","_col2","_col4","_col7","_col9"]
116                               <-Map 9 [SIMPLE_EDGE]
117                                 SHUFFLE [RS_19]
118                                   PartitionCols:_col0
119                                   Select Operator [SEL_8] (rows=8116 width=1119)
120                                     Output:["_col0","_col1"]
121                                     Filter Operator [FIL_52] (rows=8116 width=1119)
122                                       predicate:(CAST( d_date AS TIMESTAMP) BETWEEN TIMESTAMP'1998-03-08 23:00:00.0' AND TIMESTAMP'1998-05-08 00:00:00.0' and d_date_sk is not null)
123                                       TableScan [TS_6] (rows=73049 width=1119)
124                                         default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
125                               <-Reducer 2 [SIMPLE_EDGE]
126                                 SHUFFLE [RS_18]
127                                   PartitionCols:_col0
128                                   Merge Join Operator [MERGEJOIN_55] (rows=316788826 width=135)
129                                     Conds:RS_15._col2, _col3=RS_16._col0, _col1(Left Outer),Output:["_col0","_col1","_col2","_col4","_col7"]
130                                   <-Map 1 [SIMPLE_EDGE]
131                                     SHUFFLE [RS_15]
132                                       PartitionCols:_col2, _col3
133                                       Select Operator [SEL_2] (rows=287989836 width=135)
134                                         Output:["_col0","_col1","_col2","_col3","_col4"]
135                                         Filter Operator [FIL_50] (rows=287989836 width=135)
136                                           predicate:(cs_item_sk is not null and cs_sold_date_sk is not null and cs_warehouse_sk is not null)
137                                           TableScan [TS_0] (rows=287989836 width=135)
138                                             default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:NONE,Output:["cs_sold_date_sk","cs_warehouse_sk","cs_item_sk","cs_order_number","cs_sales_price"]
139                                   <-Map 8 [SIMPLE_EDGE]
140                                     SHUFFLE [RS_16]
141                                       PartitionCols:_col0, _col1
142                                       Select Operator [SEL_5] (rows=28798881 width=106)
143                                         Output:["_col0","_col1","_col2"]
144                                         Filter Operator [FIL_51] (rows=28798881 width=106)
145                                           predicate:cr_item_sk is not null
146                                           TableScan [TS_3] (rows=28798881 width=106)
147                                             default@catalog_returns,catalog_returns,Tbl:COMPLETE,Col:NONE,Output:["cr_item_sk","cr_order_number","cr_refunded_cash"]
148