You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by px...@apache.org on 2017/05/31 00:01:19 UTC
[12/15] hive git commit: HIVE-16764: Support numeric as same as
decimal (Pengcheng Xiong, reviewed by Ashutosh Chauhan)
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query13.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query13.q.out b/ql/src/test/results/clientpositive/perf/query13.q.out
index d67abe2..ac9d3cb 100644
--- a/ql/src/test/results/clientpositive/perf/query13.q.out
+++ b/ql/src/test/results/clientpositive/perf/query13.q.out
@@ -1,110 +1,102 @@
-PREHOOK: query: EXPLAIN SELECT Avg(ss_quantity) ,
- Avg(ss_ext_sales_price) ,
- Avg(ss_ext_wholesale_cost) ,
- Sum(ss_ext_wholesale_cost)
-FROM store_sales ,
- store ,
- customer_demographics ,
- household_demographics ,
- customer_address ,
- date_dim
-WHERE store.s_store_sk = store_sales.ss_store_sk
-AND store_sales.ss_sold_date_sk = date_dim.d_date_sk
-AND date_dim.d_year = 2001
-AND ((
- store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk
- AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk
- AND customer_demographics.cd_marital_status = 'M'
- AND customer_demographics.cd_education_status = '4 yr Degree'
- AND store_sales.ss_sales_price BETWEEN 100.00 AND 150.00
- AND household_demographics.hd_dep_count = 3 )
- OR (
- store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk
- AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk
- AND customer_demographics.cd_marital_status = 'D'
- AND customer_demographics.cd_education_status = 'Primary'
- AND store_sales.ss_sales_price BETWEEN 50.00 AND 100.00
- AND household_demographics.hd_dep_count = 1 )
- OR (
- store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk
- AND customer_demographics.cd_demo_sk = ss_cdemo_sk
- AND customer_demographics.cd_marital_status = 'U'
- AND customer_demographics.cd_education_status = 'Advanced Degree'
- AND store_sales.ss_sales_price BETWEEN 150.00 AND 200.00
- AND household_demographics.hd_dep_count = 1 ))
-AND ((
- store_sales.ss_addr_sk = customer_address.ca_address_sk
- AND customer_address.ca_country = 'United States'
- AND customer_address.ca_state IN ('KY',
- 'GA',
- 'NM')
- AND store_sales.ss_net_profit BETWEEN 100 AND 200 )
- OR (
- store_sales.ss_addr_sk = customer_address.ca_address_sk
- AND customer_address.ca_country = 'United States'
- AND customer_address.ca_state IN ('MT',
- 'OR',
- 'IN')
- AND store_sales.ss_net_profit BETWEEN 150 AND 300 )
- OR (
- store_sales.ss_addr_sk = customer_address.ca_address_sk
- AND customer_address.ca_country = 'United States'
- AND customer_address.ca_state IN ('WI', 'MO', 'WV')
- AND store_sales.ss_net_profit BETWEEN 50 AND 250 ))
+PREHOOK: query: explain
+select avg(ss_quantity)
+ ,avg(ss_ext_sales_price)
+ ,avg(ss_ext_wholesale_cost)
+ ,sum(ss_ext_wholesale_cost)
+ from store_sales
+ ,store
+ ,customer_demographics
+ ,household_demographics
+ ,customer_address
+ ,date_dim
+ where s_store_sk = ss_store_sk
+ and ss_sold_date_sk = d_date_sk and d_year = 2001
+ and((ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'M'
+ and cd_education_status = '4 yr Degree'
+ and ss_sales_price between 100.00 and 150.00
+ and hd_dep_count = 3
+ )or
+ (ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'D'
+ and cd_education_status = 'Primary'
+ and ss_sales_price between 50.00 and 100.00
+ and hd_dep_count = 1
+ ) or
+ (ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'U'
+ and cd_education_status = 'Advanced Degree'
+ and ss_sales_price between 150.00 and 200.00
+ and hd_dep_count = 1
+ ))
+ and((ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('KY', 'GA', 'NM')
+ and ss_net_profit between 100 and 200
+ ) or
+ (ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('MT', 'OR', 'IN')
+ and ss_net_profit between 150 and 300
+ ) or
+ (ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('WI', 'MO', 'WV')
+ and ss_net_profit between 50 and 250
+ ))
PREHOOK: type: QUERY
-POSTHOOK: query: EXPLAIN SELECT Avg(ss_quantity) ,
- Avg(ss_ext_sales_price) ,
- Avg(ss_ext_wholesale_cost) ,
- Sum(ss_ext_wholesale_cost)
-FROM store_sales ,
- store ,
- customer_demographics ,
- household_demographics ,
- customer_address ,
- date_dim
-WHERE store.s_store_sk = store_sales.ss_store_sk
-AND store_sales.ss_sold_date_sk = date_dim.d_date_sk
-AND date_dim.d_year = 2001
-AND ((
- store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk
- AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk
- AND customer_demographics.cd_marital_status = 'M'
- AND customer_demographics.cd_education_status = '4 yr Degree'
- AND store_sales.ss_sales_price BETWEEN 100.00 AND 150.00
- AND household_demographics.hd_dep_count = 3 )
- OR (
- store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk
- AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk
- AND customer_demographics.cd_marital_status = 'D'
- AND customer_demographics.cd_education_status = 'Primary'
- AND store_sales.ss_sales_price BETWEEN 50.00 AND 100.00
- AND household_demographics.hd_dep_count = 1 )
- OR (
- store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk
- AND customer_demographics.cd_demo_sk = ss_cdemo_sk
- AND customer_demographics.cd_marital_status = 'U'
- AND customer_demographics.cd_education_status = 'Advanced Degree'
- AND store_sales.ss_sales_price BETWEEN 150.00 AND 200.00
- AND household_demographics.hd_dep_count = 1 ))
-AND ((
- store_sales.ss_addr_sk = customer_address.ca_address_sk
- AND customer_address.ca_country = 'United States'
- AND customer_address.ca_state IN ('KY',
- 'GA',
- 'NM')
- AND store_sales.ss_net_profit BETWEEN 100 AND 200 )
- OR (
- store_sales.ss_addr_sk = customer_address.ca_address_sk
- AND customer_address.ca_country = 'United States'
- AND customer_address.ca_state IN ('MT',
- 'OR',
- 'IN')
- AND store_sales.ss_net_profit BETWEEN 150 AND 300 )
- OR (
- store_sales.ss_addr_sk = customer_address.ca_address_sk
- AND customer_address.ca_country = 'United States'
- AND customer_address.ca_state IN ('WI', 'MO', 'WV')
- AND store_sales.ss_net_profit BETWEEN 50 AND 250 ))
+POSTHOOK: query: explain
+select avg(ss_quantity)
+ ,avg(ss_ext_sales_price)
+ ,avg(ss_ext_wholesale_cost)
+ ,sum(ss_ext_wholesale_cost)
+ from store_sales
+ ,store
+ ,customer_demographics
+ ,household_demographics
+ ,customer_address
+ ,date_dim
+ where s_store_sk = ss_store_sk
+ and ss_sold_date_sk = d_date_sk and d_year = 2001
+ and((ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'M'
+ and cd_education_status = '4 yr Degree'
+ and ss_sales_price between 100.00 and 150.00
+ and hd_dep_count = 3
+ )or
+ (ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'D'
+ and cd_education_status = 'Primary'
+ and ss_sales_price between 50.00 and 100.00
+ and hd_dep_count = 1
+ ) or
+ (ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'U'
+ and cd_education_status = 'Advanced Degree'
+ and ss_sales_price between 150.00 and 200.00
+ and hd_dep_count = 1
+ ))
+ and((ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('KY', 'GA', 'NM')
+ and ss_net_profit between 100 and 200
+ ) or
+ (ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('MT', 'OR', 'IN')
+ and ss_net_profit between 150 and 300
+ ) or
+ (ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('WI', 'MO', 'WV')
+ and ss_net_profit between 50 and 250
+ ))
POSTHOOK: type: QUERY
Plan optimized by CBO.
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query15.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query15.q.out b/ql/src/test/results/clientpositive/perf/query15.q.out
index adec728..a2d95f6 100644
--- a/ql/src/test/results/clientpositive/perf/query15.q.out
+++ b/ql/src/test/results/clientpositive/perf/query15.q.out
@@ -1,6 +1,40 @@
-PREHOOK: query: explain select ca_zip ,sum(cs_sales_price) from catalog_sales ,customer ,customer_address ,date_dim where catalog_sales.cs_bill_customer_sk = customer.c_customer_sk and customer.c_current_addr_sk = customer_address.ca_address_sk and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792') or customer_address.ca_state in ('CA','WA','GA') or catalog_sales.cs_sales_price > 500) and catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and date_dim.d_qoy = 2 and date_dim.d_year = 2000 group by ca_zip order by ca_zip limit 100
+PREHOOK: query: explain
+select ca_zip
+ ,sum(cs_sales_price)
+ from catalog_sales
+ ,customer
+ ,customer_address
+ ,date_dim
+ where cs_bill_customer_sk = c_customer_sk
+ and c_current_addr_sk = ca_address_sk
+ and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475',
+ '85392', '85460', '80348', '81792')
+ or ca_state in ('CA','WA','GA')
+ or cs_sales_price > 500)
+ and cs_sold_date_sk = d_date_sk
+ and d_qoy = 2 and d_year = 2000
+ group by ca_zip
+ order by ca_zip
+ limit 100
PREHOOK: type: QUERY
-POSTHOOK: query: explain select ca_zip ,sum(cs_sales_price) from catalog_sales ,customer ,customer_address ,date_dim where catalog_sales.cs_bill_customer_sk = customer.c_customer_sk and customer.c_current_addr_sk = customer_address.ca_address_sk and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792') or customer_address.ca_state in ('CA','WA','GA') or catalog_sales.cs_sales_price > 500) and catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and date_dim.d_qoy = 2 and date_dim.d_year = 2000 group by ca_zip order by ca_zip limit 100
+POSTHOOK: query: explain
+select ca_zip
+ ,sum(cs_sales_price)
+ from catalog_sales
+ ,customer
+ ,customer_address
+ ,date_dim
+ where cs_bill_customer_sk = c_customer_sk
+ and c_current_addr_sk = ca_address_sk
+ and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475',
+ '85392', '85460', '80348', '81792')
+ or ca_state in ('CA','WA','GA')
+ or cs_sales_price > 500)
+ and cs_sold_date_sk = d_date_sk
+ and d_qoy = 2 and d_year = 2000
+ group by ca_zip
+ order by ca_zip
+ limit 100
POSTHOOK: type: QUERY
Plan optimized by CBO.
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query16.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query16.q.out b/ql/src/test/results/clientpositive/perf/query16.q.out
index cf90c0c..449b9c8 100644
--- a/ql/src/test/results/clientpositive/perf/query16.q.out
+++ b/ql/src/test/results/clientpositive/perf/query16.q.out
@@ -1,5 +1,6 @@
-Warning: Shuffle Join MERGEJOIN[106][tables = [$hdt$_2, $hdt$_3, $hdt$_1, $hdt$_4]] in Stage 'Reducer 17' is a cross product
-PREHOOK: query: explain select
+Warning: Shuffle Join MERGEJOIN[107][tables = [$hdt$_2, $hdt$_3, $hdt$_1, $hdt$_4]] in Stage 'Reducer 17' is a cross product
+PREHOOK: query: explain
+select
count(distinct cs_order_number) as `order count`
,sum(cs_ext_ship_cost) as `total shipping cost`
,sum(cs_net_profit) as `total net profit`
@@ -25,10 +26,11 @@ and exists (select *
and not exists(select *
from catalog_returns cr1
where cs1.cs_order_number = cr1.cr_order_number)
-order by `order count`
+order by count(distinct cs_order_number)
limit 100
PREHOOK: type: QUERY
-POSTHOOK: query: explain select
+POSTHOOK: query: explain
+select
count(distinct cs_order_number) as `order count`
,sum(cs_ext_ship_cost) as `total shipping cost`
,sum(cs_net_profit) as `total net profit`
@@ -54,7 +56,7 @@ and exists (select *
and not exists(select *
from catalog_returns cr1
where cs1.cs_order_number = cr1.cr_order_number)
-order by `order count`
+order by count(distinct cs_order_number)
limit 100
POSTHOOK: type: QUERY
Plan optimized by CBO.
@@ -75,157 +77,159 @@ Reducer 8 <- Reducer 7 (SIMPLE_EDGE)
Stage-0
Fetch Operator
- limit:100
+ limit:-1
Stage-1
Reducer 8
- File Output Operator [FS_73]
+ File Output Operator [FS_74]
Limit [LIM_72] (rows=1 width=344)
Number of rows:100
Select Operator [SEL_71] (rows=1 width=344)
Output:["_col0","_col1","_col2"]
<-Reducer 7 [SIMPLE_EDGE]
SHUFFLE [RS_70]
- Group By Operator [GBY_68] (rows=1 width=344)
- Output:["_col0","_col1","_col2"],aggregations:["count(DISTINCT KEY._col0:0._col0)","sum(VALUE._col1)","sum(VALUE._col2)"]
- <-Reducer 6 [SIMPLE_EDGE]
- SHUFFLE [RS_67]
- Group By Operator [GBY_66] (rows=1395035081047425024 width=1)
- Output:["_col0","_col1","_col2","_col3"],aggregations:["count(DISTINCT _col4)","sum(_col5)","sum(_col6)"],keys:_col4
- Select Operator [SEL_65] (rows=1395035081047425024 width=1)
- Output:["_col4","_col5","_col6"]
- Filter Operator [FIL_64] (rows=1395035081047425024 width=1)
- predicate:_col16 is null
- Select Operator [SEL_63] (rows=2790070162094850048 width=1)
- Output:["_col4","_col5","_col6","_col16"]
- Merge Join Operator [MERGEJOIN_112] (rows=2790070162094850048 width=1)
- Conds:RS_60._col3, _col4=RS_61._col0, _col1(Inner),Output:["_col4","_col5","_col6","_col14"]
- <-Reducer 16 [SIMPLE_EDGE]
- SHUFFLE [RS_61]
- PartitionCols:_col0, _col1
- Group By Operator [GBY_46] (rows=2536427365110644736 width=1)
- Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
- <-Reducer 15 [SIMPLE_EDGE]
- SHUFFLE [RS_45]
- PartitionCols:_col0, _col1
- Group By Operator [GBY_44] (rows=5072854730221289472 width=1)
- Output:["_col0","_col1"],keys:_col2, _col3
- Select Operator [SEL_43] (rows=5072854730221289472 width=1)
- Output:["_col2","_col3"]
- Filter Operator [FIL_42] (rows=5072854730221289472 width=1)
- predicate:(_col2 <> _col0)
- Merge Join Operator [MERGEJOIN_110] (rows=5072854730221289472 width=1)
- Conds:RS_39._col1=RS_40._col1(Inner),Output:["_col0","_col2","_col3"]
- <-Map 14 [SIMPLE_EDGE]
- PARTITION_ONLY_SHUFFLE [RS_39]
- PartitionCols:_col1
- Select Operator [SEL_20] (rows=287989836 width=135)
- Output:["_col0","_col1"]
- TableScan [TS_19] (rows=287989836 width=135)
- default@catalog_sales,cs2,Tbl:COMPLETE,Col:NONE,Output:["cs_warehouse_sk","cs_order_number"]
- <-Reducer 18 [SIMPLE_EDGE]
- SHUFFLE [RS_40]
- PartitionCols:_col1
- Select Operator [SEL_38] (rows=4611686018427387903 width=1)
- Output:["_col0","_col1"]
- Group By Operator [GBY_37] (rows=4611686018427387903 width=1)
- Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
- <-Reducer 17 [SIMPLE_EDGE]
- SHUFFLE [RS_36]
- PartitionCols:_col0, _col1
- Group By Operator [GBY_35] (rows=9223372036854775807 width=1)
- Output:["_col0","_col1"],keys:_col4, _col3
- Merge Join Operator [MERGEJOIN_106] (rows=9223372036854775807 width=1)
- Conds:(Inner),(Inner),(Inner),Output:["_col3","_col4"]
- <-Map 14 [CUSTOM_SIMPLE_EDGE]
- PARTITION_ONLY_SHUFFLE [RS_32]
- Select Operator [SEL_28] (rows=287989836 width=135)
- Output:["_col0","_col1"]
- Please refer to the previous TableScan [TS_19]
- <-Map 19 [CUSTOM_SIMPLE_EDGE]
- PARTITION_ONLY_SHUFFLE [RS_29]
- Select Operator [SEL_22] (rows=73049 width=4)
- TableScan [TS_21] (rows=73049 width=1119)
- default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE
- <-Map 20 [CUSTOM_SIMPLE_EDGE]
- PARTITION_ONLY_SHUFFLE [RS_30]
- Select Operator [SEL_24] (rows=60 width=4)
- TableScan [TS_23] (rows=60 width=2045)
- default@call_center,call_center,Tbl:COMPLETE,Col:COMPLETE
- <-Map 21 [CUSTOM_SIMPLE_EDGE]
- PARTITION_ONLY_SHUFFLE [RS_31]
- Select Operator [SEL_26] (rows=40000000 width=4)
- TableScan [TS_25] (rows=40000000 width=1014)
- default@customer_address,customer_address,Tbl:COMPLETE,Col:COMPLETE
- <-Reducer 5 [SIMPLE_EDGE]
- SHUFFLE [RS_60]
- PartitionCols:_col3, _col4
- Merge Join Operator [MERGEJOIN_111] (rows=421645953 width=135)
- Conds:RS_57._col4=RS_58._col0(Left Outer),Output:["_col3","_col4","_col5","_col6","_col14"]
- <-Reducer 13 [SIMPLE_EDGE]
- SHUFFLE [RS_58]
- PartitionCols:_col0
- Select Operator [SEL_18] (rows=14399440 width=106)
- Output:["_col0","_col1"]
- Group By Operator [GBY_17] (rows=14399440 width=106)
- Output:["_col0"],keys:KEY._col0
- <-Map 12 [SIMPLE_EDGE]
- SHUFFLE [RS_16]
- PartitionCols:_col0
- Group By Operator [GBY_15] (rows=28798881 width=106)
- Output:["_col0"],keys:cr_order_number
- Filter Operator [FIL_103] (rows=28798881 width=106)
- predicate:cr_order_number is not null
- TableScan [TS_12] (rows=28798881 width=106)
- default@catalog_returns,cr1,Tbl:COMPLETE,Col:NONE,Output:["cr_order_number"]
- <-Reducer 4 [SIMPLE_EDGE]
- SHUFFLE [RS_57]
- PartitionCols:_col4
- Merge Join Operator [MERGEJOIN_109] (rows=383314495 width=135)
- Conds:RS_54._col2=RS_55._col0(Inner),Output:["_col3","_col4","_col5","_col6"]
- <-Map 11 [SIMPLE_EDGE]
- SHUFFLE [RS_55]
- PartitionCols:_col0
- Select Operator [SEL_11] (rows=30 width=2045)
- Output:["_col0"]
- Filter Operator [FIL_102] (rows=30 width=2045)
- predicate:((cc_county) IN ('Ziebach County', 'Levy County', 'Huron County', 'Franklin Parish', 'Daviess County') and cc_call_center_sk is not null)
- TableScan [TS_9] (rows=60 width=2045)
- default@call_center,call_center,Tbl:COMPLETE,Col:NONE,Output:["cc_call_center_sk","cc_county"]
- <-Reducer 3 [SIMPLE_EDGE]
- SHUFFLE [RS_54]
- PartitionCols:_col2
- Merge Join Operator [MERGEJOIN_108] (rows=348467716 width=135)
- Conds:RS_51._col1=RS_52._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col6"]
- <-Map 10 [SIMPLE_EDGE]
- SHUFFLE [RS_52]
+ Select Operator [SEL_69] (rows=1 width=344)
+ Output:["_col1","_col2","_col3"]
+ Group By Operator [GBY_68] (rows=1 width=344)
+ Output:["_col0","_col1","_col2"],aggregations:["count(DISTINCT KEY._col0:0._col0)","sum(VALUE._col1)","sum(VALUE._col2)"]
+ <-Reducer 6 [SIMPLE_EDGE]
+ SHUFFLE [RS_67]
+ Group By Operator [GBY_66] (rows=1395035081047425024 width=1)
+ Output:["_col0","_col1","_col2","_col3"],aggregations:["count(DISTINCT _col4)","sum(_col5)","sum(_col6)"],keys:_col4
+ Select Operator [SEL_65] (rows=1395035081047425024 width=1)
+ Output:["_col4","_col5","_col6"]
+ Filter Operator [FIL_64] (rows=1395035081047425024 width=1)
+ predicate:_col16 is null
+ Select Operator [SEL_63] (rows=2790070162094850048 width=1)
+ Output:["_col4","_col5","_col6","_col16"]
+ Merge Join Operator [MERGEJOIN_113] (rows=2790070162094850048 width=1)
+ Conds:RS_60._col3, _col4=RS_61._col0, _col1(Inner),Output:["_col4","_col5","_col6","_col14"]
+ <-Reducer 16 [SIMPLE_EDGE]
+ SHUFFLE [RS_61]
+ PartitionCols:_col0, _col1
+ Group By Operator [GBY_46] (rows=2536427365110644736 width=1)
+ Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+ <-Reducer 15 [SIMPLE_EDGE]
+ SHUFFLE [RS_45]
+ PartitionCols:_col0, _col1
+ Group By Operator [GBY_44] (rows=5072854730221289472 width=1)
+ Output:["_col0","_col1"],keys:_col2, _col3
+ Select Operator [SEL_43] (rows=5072854730221289472 width=1)
+ Output:["_col2","_col3"]
+ Filter Operator [FIL_42] (rows=5072854730221289472 width=1)
+ predicate:(_col2 <> _col0)
+ Merge Join Operator [MERGEJOIN_111] (rows=5072854730221289472 width=1)
+ Conds:RS_39._col1=RS_40._col1(Inner),Output:["_col0","_col2","_col3"]
+ <-Map 14 [SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_39]
+ PartitionCols:_col1
+ Select Operator [SEL_20] (rows=287989836 width=135)
+ Output:["_col0","_col1"]
+ TableScan [TS_19] (rows=287989836 width=135)
+ default@catalog_sales,cs2,Tbl:COMPLETE,Col:NONE,Output:["cs_warehouse_sk","cs_order_number"]
+ <-Reducer 18 [SIMPLE_EDGE]
+ SHUFFLE [RS_40]
+ PartitionCols:_col1
+ Select Operator [SEL_38] (rows=4611686018427387903 width=1)
+ Output:["_col0","_col1"]
+ Group By Operator [GBY_37] (rows=4611686018427387903 width=1)
+ Output:["_col0","_col1"],keys:KEY._col0, KEY._col1
+ <-Reducer 17 [SIMPLE_EDGE]
+ SHUFFLE [RS_36]
+ PartitionCols:_col0, _col1
+ Group By Operator [GBY_35] (rows=9223372036854775807 width=1)
+ Output:["_col0","_col1"],keys:_col4, _col3
+ Merge Join Operator [MERGEJOIN_107] (rows=9223372036854775807 width=1)
+ Conds:(Inner),(Inner),(Inner),Output:["_col3","_col4"]
+ <-Map 14 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_32]
+ Select Operator [SEL_28] (rows=287989836 width=135)
+ Output:["_col0","_col1"]
+ Please refer to the previous TableScan [TS_19]
+ <-Map 19 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_29]
+ Select Operator [SEL_22] (rows=73049 width=4)
+ TableScan [TS_21] (rows=73049 width=1119)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE
+ <-Map 20 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_30]
+ Select Operator [SEL_24] (rows=60 width=4)
+ TableScan [TS_23] (rows=60 width=2045)
+ default@call_center,call_center,Tbl:COMPLETE,Col:COMPLETE
+ <-Map 21 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_31]
+ Select Operator [SEL_26] (rows=40000000 width=4)
+ TableScan [TS_25] (rows=40000000 width=1014)
+ default@customer_address,customer_address,Tbl:COMPLETE,Col:COMPLETE
+ <-Reducer 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_60]
+ PartitionCols:_col3, _col4
+ Merge Join Operator [MERGEJOIN_112] (rows=421645953 width=135)
+ Conds:RS_57._col4=RS_58._col0(Left Outer),Output:["_col3","_col4","_col5","_col6","_col14"]
+ <-Reducer 13 [SIMPLE_EDGE]
+ SHUFFLE [RS_58]
+ PartitionCols:_col0
+ Select Operator [SEL_18] (rows=14399440 width=106)
+ Output:["_col0","_col1"]
+ Group By Operator [GBY_17] (rows=14399440 width=106)
+ Output:["_col0"],keys:KEY._col0
+ <-Map 12 [SIMPLE_EDGE]
+ SHUFFLE [RS_16]
PartitionCols:_col0
- Select Operator [SEL_8] (rows=20000000 width=1014)
- Output:["_col0"]
- Filter Operator [FIL_101] (rows=20000000 width=1014)
- predicate:((ca_state = 'NY') and ca_address_sk is not null)
- TableScan [TS_6] (rows=40000000 width=1014)
- default@customer_address,customer_address,Tbl:COMPLETE,Col:NONE,Output:["ca_address_sk","ca_state"]
- <-Reducer 2 [SIMPLE_EDGE]
- SHUFFLE [RS_51]
- PartitionCols:_col1
- Merge Join Operator [MERGEJOIN_107] (rows=316788826 width=135)
- Conds:RS_48._col0=RS_49._col0(Inner),Output:["_col1","_col2","_col3","_col4","_col5","_col6"]
- <-Map 1 [SIMPLE_EDGE]
- SHUFFLE [RS_48]
- PartitionCols:_col0
- Select Operator [SEL_2] (rows=287989836 width=135)
- Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
- Filter Operator [FIL_99] (rows=287989836 width=135)
- predicate:(cs_ship_date_sk is not null and cs_ship_addr_sk is not null and cs_call_center_sk is not null)
- TableScan [TS_0] (rows=287989836 width=135)
- default@catalog_sales,cs1,Tbl:COMPLETE,Col:NONE,Output:["cs_ship_date_sk","cs_ship_addr_sk","cs_call_center_sk","cs_warehouse_sk","cs_order_number","cs_ext_ship_cost","cs_net_profit"]
- <-Map 9 [SIMPLE_EDGE]
- SHUFFLE [RS_49]
- PartitionCols:_col0
- Select Operator [SEL_5] (rows=8116 width=1119)
- Output:["_col0"]
- Filter Operator [FIL_100] (rows=8116 width=1119)
- predicate:(CAST( d_date AS TIMESTAMP) BETWEEN 2001-04-01 00:00:00.0 AND 2001-05-31 01:00:00.0 and d_date_sk is not null)
- TableScan [TS_3] (rows=73049 width=1119)
- default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
+ Group By Operator [GBY_15] (rows=28798881 width=106)
+ Output:["_col0"],keys:cr_order_number
+ Filter Operator [FIL_104] (rows=28798881 width=106)
+ predicate:cr_order_number is not null
+ TableScan [TS_12] (rows=28798881 width=106)
+ default@catalog_returns,cr1,Tbl:COMPLETE,Col:NONE,Output:["cr_order_number"]
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_57]
+ PartitionCols:_col4
+ Merge Join Operator [MERGEJOIN_110] (rows=383314495 width=135)
+ Conds:RS_54._col2=RS_55._col0(Inner),Output:["_col3","_col4","_col5","_col6"]
+ <-Map 11 [SIMPLE_EDGE]
+ SHUFFLE [RS_55]
+ PartitionCols:_col0
+ Select Operator [SEL_11] (rows=30 width=2045)
+ Output:["_col0"]
+ Filter Operator [FIL_103] (rows=30 width=2045)
+ predicate:((cc_county) IN ('Ziebach County', 'Levy County', 'Huron County', 'Franklin Parish', 'Daviess County') and cc_call_center_sk is not null)
+ TableScan [TS_9] (rows=60 width=2045)
+ default@call_center,call_center,Tbl:COMPLETE,Col:NONE,Output:["cc_call_center_sk","cc_county"]
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_54]
+ PartitionCols:_col2
+ Merge Join Operator [MERGEJOIN_109] (rows=348467716 width=135)
+ Conds:RS_51._col1=RS_52._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col6"]
+ <-Map 10 [SIMPLE_EDGE]
+ SHUFFLE [RS_52]
+ PartitionCols:_col0
+ Select Operator [SEL_8] (rows=20000000 width=1014)
+ Output:["_col0"]
+ Filter Operator [FIL_102] (rows=20000000 width=1014)
+ predicate:((ca_state = 'NY') and ca_address_sk is not null)
+ TableScan [TS_6] (rows=40000000 width=1014)
+ default@customer_address,customer_address,Tbl:COMPLETE,Col:NONE,Output:["ca_address_sk","ca_state"]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_51]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_108] (rows=316788826 width=135)
+ Conds:RS_48._col0=RS_49._col0(Inner),Output:["_col1","_col2","_col3","_col4","_col5","_col6"]
+ <-Map 1 [SIMPLE_EDGE]
+ SHUFFLE [RS_48]
+ PartitionCols:_col0
+ Select Operator [SEL_2] (rows=287989836 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+ Filter Operator [FIL_100] (rows=287989836 width=135)
+ predicate:(cs_ship_date_sk is not null and cs_ship_addr_sk is not null and cs_call_center_sk is not null)
+ TableScan [TS_0] (rows=287989836 width=135)
+ default@catalog_sales,cs1,Tbl:COMPLETE,Col:NONE,Output:["cs_ship_date_sk","cs_ship_addr_sk","cs_call_center_sk","cs_warehouse_sk","cs_order_number","cs_ext_ship_cost","cs_net_profit"]
+ <-Map 9 [SIMPLE_EDGE]
+ SHUFFLE [RS_49]
+ PartitionCols:_col0
+ Select Operator [SEL_5] (rows=8116 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_101] (rows=8116 width=1119)
+ predicate:(CAST( d_date AS TIMESTAMP) BETWEEN 2001-04-01 00:00:00.0 AND 2001-05-31 01:00:00.0 and d_date_sk is not null)
+ TableScan [TS_3] (rows=73049 width=1119)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query17.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query17.q.out b/ql/src/test/results/clientpositive/perf/query17.q.out
index 2f32a9f..5b1f2a8 100644
--- a/ql/src/test/results/clientpositive/perf/query17.q.out
+++ b/ql/src/test/results/clientpositive/perf/query17.q.out
@@ -1,6 +1,90 @@
-PREHOOK: query: explain select i_item_id ,i_item_desc ,s_state ,count(ss_quantity) as store_sales_quantitycount ,avg(ss_quantity) as store_sales_quantityave ,stddev_samp(ss_quantity) as store_sales_quantitystdev ,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov ,count(sr_return_quantity) as_store_returns_quantitycount ,avg(sr_return_quantity) as_store_returns_quantityave ,stddev_samp(sr_return_quantity) as_store_returns_quantitystdev ,stddev_samp(sr_return_quantity)/avg(sr_return_quantity) as store_returns_quantitycov ,count(cs_quantity) as catalog_sales_quantitycount ,avg(cs_quantity) as catalog_sales_quantityave ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitystdev ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov from store_sales ,store_returns ,catalog_sales ,date_dim d1 ,date_dim d2 ,date_dim d3 ,store ,item where d1.d_quarter_name = '2000Q1' and d1.d_date_sk = store_sales.ss_sold_date_sk and item.i_item_sk = store_s
ales.ss_item_sk and store.s_store_sk = store_sales.ss_store_sk and store_sales.ss_customer_sk = store_returns.sr_customer_sk and store_sales.ss_item_sk = store_returns.sr_item_sk and store_sales.ss_ticket_number = store_returns.sr_ticket_number and store_returns.sr_returned_date_sk = d2.d_date_sk and d2.d_quarter_name in ('2000Q1','2000Q2','2000Q3') and store_returns.sr_customer_sk = catalog_sales.cs_bill_customer_sk and store_returns.sr_item_sk = catalog_sales.cs_item_sk and catalog_sales.cs_sold_date_sk = d3.d_date_sk and d3.d_quarter_name in ('2000Q1','2000Q2','2000Q3') group by i_item_id ,i_item_desc ,s_state order by i_item_id ,i_item_desc ,s_state limit 100
+PREHOOK: query: explain
+select i_item_id
+ ,i_item_desc
+ ,s_state
+ ,count(ss_quantity) as store_sales_quantitycount
+ ,avg(ss_quantity) as store_sales_quantityave
+ ,stddev_samp(ss_quantity) as store_sales_quantitystdev
+ ,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov
+ ,count(sr_return_quantity) as_store_returns_quantitycount
+ ,avg(sr_return_quantity) as_store_returns_quantityave
+ ,stddev_samp(sr_return_quantity) as_store_returns_quantitystdev
+ ,stddev_samp(sr_return_quantity)/avg(sr_return_quantity) as store_returns_quantitycov
+ ,count(cs_quantity) as catalog_sales_quantitycount ,avg(cs_quantity) as catalog_sales_quantityave
+ ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitystdev
+ ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov
+ from store_sales
+ ,store_returns
+ ,catalog_sales
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,item
+ where d1.d_quarter_name = '2000Q1'
+ and d1.d_date_sk = ss_sold_date_sk
+ and i_item_sk = ss_item_sk
+ and s_store_sk = ss_store_sk
+ and ss_customer_sk = sr_customer_sk
+ and ss_item_sk = sr_item_sk
+ and ss_ticket_number = sr_ticket_number
+ and sr_returned_date_sk = d2.d_date_sk
+ and d2.d_quarter_name in ('2000Q1','2000Q2','2000Q3')
+ and sr_customer_sk = cs_bill_customer_sk
+ and sr_item_sk = cs_item_sk
+ and cs_sold_date_sk = d3.d_date_sk
+ and d3.d_quarter_name in ('2000Q1','2000Q2','2000Q3')
+ group by i_item_id
+ ,i_item_desc
+ ,s_state
+ order by i_item_id
+ ,i_item_desc
+ ,s_state
+limit 100
PREHOOK: type: QUERY
-POSTHOOK: query: explain select i_item_id ,i_item_desc ,s_state ,count(ss_quantity) as store_sales_quantitycount ,avg(ss_quantity) as store_sales_quantityave ,stddev_samp(ss_quantity) as store_sales_quantitystdev ,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov ,count(sr_return_quantity) as_store_returns_quantitycount ,avg(sr_return_quantity) as_store_returns_quantityave ,stddev_samp(sr_return_quantity) as_store_returns_quantitystdev ,stddev_samp(sr_return_quantity)/avg(sr_return_quantity) as store_returns_quantitycov ,count(cs_quantity) as catalog_sales_quantitycount ,avg(cs_quantity) as catalog_sales_quantityave ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitystdev ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov from store_sales ,store_returns ,catalog_sales ,date_dim d1 ,date_dim d2 ,date_dim d3 ,store ,item where d1.d_quarter_name = '2000Q1' and d1.d_date_sk = store_sales.ss_sold_date_sk and item.i_item_sk = store_
sales.ss_item_sk and store.s_store_sk = store_sales.ss_store_sk and store_sales.ss_customer_sk = store_returns.sr_customer_sk and store_sales.ss_item_sk = store_returns.sr_item_sk and store_sales.ss_ticket_number = store_returns.sr_ticket_number and store_returns.sr_returned_date_sk = d2.d_date_sk and d2.d_quarter_name in ('2000Q1','2000Q2','2000Q3') and store_returns.sr_customer_sk = catalog_sales.cs_bill_customer_sk and store_returns.sr_item_sk = catalog_sales.cs_item_sk and catalog_sales.cs_sold_date_sk = d3.d_date_sk and d3.d_quarter_name in ('2000Q1','2000Q2','2000Q3') group by i_item_id ,i_item_desc ,s_state order by i_item_id ,i_item_desc ,s_state limit 100
+POSTHOOK: query: explain
+select i_item_id
+ ,i_item_desc
+ ,s_state
+ ,count(ss_quantity) as store_sales_quantitycount
+ ,avg(ss_quantity) as store_sales_quantityave
+ ,stddev_samp(ss_quantity) as store_sales_quantitystdev
+ ,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov
+ ,count(sr_return_quantity) as_store_returns_quantitycount
+ ,avg(sr_return_quantity) as_store_returns_quantityave
+ ,stddev_samp(sr_return_quantity) as_store_returns_quantitystdev
+ ,stddev_samp(sr_return_quantity)/avg(sr_return_quantity) as store_returns_quantitycov
+ ,count(cs_quantity) as catalog_sales_quantitycount ,avg(cs_quantity) as catalog_sales_quantityave
+ ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitystdev
+ ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov
+ from store_sales
+ ,store_returns
+ ,catalog_sales
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,item
+ where d1.d_quarter_name = '2000Q1'
+ and d1.d_date_sk = ss_sold_date_sk
+ and i_item_sk = ss_item_sk
+ and s_store_sk = ss_store_sk
+ and ss_customer_sk = sr_customer_sk
+ and ss_item_sk = sr_item_sk
+ and ss_ticket_number = sr_ticket_number
+ and sr_returned_date_sk = d2.d_date_sk
+ and d2.d_quarter_name in ('2000Q1','2000Q2','2000Q3')
+ and sr_customer_sk = cs_bill_customer_sk
+ and sr_item_sk = cs_item_sk
+ and cs_sold_date_sk = d3.d_date_sk
+ and d3.d_quarter_name in ('2000Q1','2000Q2','2000Q3')
+ group by i_item_id
+ ,i_item_desc
+ ,s_state
+ order by i_item_id
+ ,i_item_desc
+ ,s_state
+limit 100
POSTHOOK: type: QUERY
Plan optimized by CBO.
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query18.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query18.q.out b/ql/src/test/results/clientpositive/perf/query18.q.out
index da35ca0..2ac6d4b 100644
--- a/ql/src/test/results/clientpositive/perf/query18.q.out
+++ b/ql/src/test/results/clientpositive/perf/query18.q.out
@@ -1,6 +1,68 @@
-PREHOOK: query: explain select i_item_id, ca_country, ca_state, ca_county, avg( cast(cs_quantity as decimal(12,2))) agg1, avg( cast(cs_list_price as decimal(12,2))) agg2, avg( cast(cs_coupon_amt as decimal(12,2))) agg3, avg( cast(cs_sales_price as decimal(12,2))) agg4, avg( cast(cs_net_profit as decimal(12,2))) agg5, avg( cast(c_birth_year as decimal(12,2))) agg6, avg( cast(cd1.cd_dep_count as decimal(12,2))) agg7 from catalog_sales, date_dim, customer_demographics cd1, item, customer, customer_address, customer_demographics cd2 where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and catalog_sales.cs_item_sk = item.i_item_sk and catalog_sales.cs_bill_cdemo_sk = cd1.cd_demo_sk and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk and cd1.cd_gender = 'M' and cd1.cd_education_status = 'College' and customer.c_current_cdemo_sk = cd2.cd_demo_sk and customer.c_current_addr_sk = customer_address.ca_address_sk and c_birth_month in (9,5,12,4,1,10) and d_year = 2001 and ca_state
in ('ND','WI','AL' ,'NC','OK','MS','TN') group by i_item_id, ca_country, ca_state, ca_county with rollup order by ca_country, ca_state, ca_county, i_item_id limit 100
+PREHOOK: query: explain
+select i_item_id,
+ ca_country,
+ ca_state,
+ ca_county,
+ avg( cast(cs_quantity as numeric(12,2))) agg1,
+ avg( cast(cs_list_price as numeric(12,2))) agg2,
+ avg( cast(cs_coupon_amt as numeric(12,2))) agg3,
+ avg( cast(cs_sales_price as numeric(12,2))) agg4,
+ avg( cast(cs_net_profit as numeric(12,2))) agg5,
+ avg( cast(c_birth_year as numeric(12,2))) agg6,
+ avg( cast(cd1.cd_dep_count as numeric(12,2))) agg7
+ from catalog_sales, customer_demographics cd1,
+ customer_demographics cd2, customer, customer_address, date_dim, item
+ where cs_sold_date_sk = d_date_sk and
+ cs_item_sk = i_item_sk and
+ cs_bill_cdemo_sk = cd1.cd_demo_sk and
+ cs_bill_customer_sk = c_customer_sk and
+ cd1.cd_gender = 'M' and
+ cd1.cd_education_status = 'College' and
+ c_current_cdemo_sk = cd2.cd_demo_sk and
+ c_current_addr_sk = ca_address_sk and
+ c_birth_month in (9,5,12,4,1,10) and
+ d_year = 2001 and
+ ca_state in ('ND','WI','AL'
+ ,'NC','OK','MS','TN')
+ group by rollup (i_item_id, ca_country, ca_state, ca_county)
+ order by ca_country,
+ ca_state,
+ ca_county,
+ i_item_id
+ limit 100
PREHOOK: type: QUERY
-POSTHOOK: query: explain select i_item_id, ca_country, ca_state, ca_county, avg( cast(cs_quantity as decimal(12,2))) agg1, avg( cast(cs_list_price as decimal(12,2))) agg2, avg( cast(cs_coupon_amt as decimal(12,2))) agg3, avg( cast(cs_sales_price as decimal(12,2))) agg4, avg( cast(cs_net_profit as decimal(12,2))) agg5, avg( cast(c_birth_year as decimal(12,2))) agg6, avg( cast(cd1.cd_dep_count as decimal(12,2))) agg7 from catalog_sales, date_dim, customer_demographics cd1, item, customer, customer_address, customer_demographics cd2 where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and catalog_sales.cs_item_sk = item.i_item_sk and catalog_sales.cs_bill_cdemo_sk = cd1.cd_demo_sk and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk and cd1.cd_gender = 'M' and cd1.cd_education_status = 'College' and customer.c_current_cdemo_sk = cd2.cd_demo_sk and customer.c_current_addr_sk = customer_address.ca_address_sk and c_birth_month in (9,5,12,4,1,10) and d_year = 2001 and ca_stat
e in ('ND','WI','AL' ,'NC','OK','MS','TN') group by i_item_id, ca_country, ca_state, ca_county with rollup order by ca_country, ca_state, ca_county, i_item_id limit 100
+POSTHOOK: query: explain
+select i_item_id,
+ ca_country,
+ ca_state,
+ ca_county,
+ avg( cast(cs_quantity as numeric(12,2))) agg1,
+ avg( cast(cs_list_price as numeric(12,2))) agg2,
+ avg( cast(cs_coupon_amt as numeric(12,2))) agg3,
+ avg( cast(cs_sales_price as numeric(12,2))) agg4,
+ avg( cast(cs_net_profit as numeric(12,2))) agg5,
+ avg( cast(c_birth_year as numeric(12,2))) agg6,
+ avg( cast(cd1.cd_dep_count as numeric(12,2))) agg7
+ from catalog_sales, customer_demographics cd1,
+ customer_demographics cd2, customer, customer_address, date_dim, item
+ where cs_sold_date_sk = d_date_sk and
+ cs_item_sk = i_item_sk and
+ cs_bill_cdemo_sk = cd1.cd_demo_sk and
+ cs_bill_customer_sk = c_customer_sk and
+ cd1.cd_gender = 'M' and
+ cd1.cd_education_status = 'College' and
+ c_current_cdemo_sk = cd2.cd_demo_sk and
+ c_current_addr_sk = ca_address_sk and
+ c_birth_month in (9,5,12,4,1,10) and
+ d_year = 2001 and
+ ca_state in ('ND','WI','AL'
+ ,'NC','OK','MS','TN')
+ group by rollup (i_item_id, ca_country, ca_state, ca_county)
+ order by ca_country,
+ ca_state,
+ ca_county,
+ i_item_id
+ limit 100
POSTHOOK: type: QUERY
Plan optimized by CBO.
@@ -89,7 +151,7 @@ Stage-0
Select Operator [SEL_11] (rows=287989836 width=135)
Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"]
Filter Operator [FIL_74] (rows=287989836 width=135)
- predicate:(cs_sold_date_sk is not null and cs_bill_cdemo_sk is not null and cs_item_sk is not null and cs_bill_customer_sk is not null)
+ predicate:(cs_bill_cdemo_sk is not null and cs_bill_customer_sk is not null and cs_sold_date_sk is not null and cs_item_sk is not null)
TableScan [TS_9] (rows=287989836 width=135)
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"]
<-Reducer 3 [SIMPLE_EDGE]
@@ -117,7 +179,7 @@ Stage-0
Select Operator [SEL_2] (rows=40000000 width=860)
Output:["_col0","_col1","_col2","_col4"]
Filter Operator [FIL_71] (rows=40000000 width=860)
- predicate:((c_birth_month) IN (9, 5, 12, 4, 1, 10) and c_customer_sk is not null and c_current_addr_sk is not null and c_current_cdemo_sk is not null)
+ predicate:((c_birth_month) IN (9, 5, 12, 4, 1, 10) and c_customer_sk is not null and c_current_cdemo_sk is not null and c_current_addr_sk is not null)
TableScan [TS_0] (rows=80000000 width=860)
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"]
<-Map 7 [SIMPLE_EDGE]
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query19.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query19.q.out b/ql/src/test/results/clientpositive/perf/query19.q.out
index cebfc3a..fccc0c3 100644
--- a/ql/src/test/results/clientpositive/perf/query19.q.out
+++ b/ql/src/test/results/clientpositive/perf/query19.q.out
@@ -1,6 +1,50 @@
-PREHOOK: query: explain select i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact, sum(ss_ext_sales_price) ext_price from date_dim, store_sales, item,customer,customer_address,store where date_dim.d_date_sk = store_sales.ss_sold_date_sk and store_sales.ss_item_sk = item.i_item_sk and i_manager_id=7 and d_moy=11 and d_year=1999 and store_sales.ss_customer_sk = customer.c_customer_sk and customer.c_current_addr_sk = customer_address.ca_address_sk and substr(ca_zip,1,5) <> substr(s_zip,1,5) and store_sales.ss_store_sk = store.s_store_sk group by i_brand ,i_brand_id ,i_manufact_id ,i_manufact order by ext_price desc ,i_brand ,i_brand_id ,i_manufact_id ,i_manufact limit 100
+PREHOOK: query: explain
+select i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact,
+ sum(ss_ext_sales_price) ext_price
+ from date_dim, store_sales, item,customer,customer_address,store
+ where d_date_sk = ss_sold_date_sk
+ and ss_item_sk = i_item_sk
+ and i_manager_id=7
+ and d_moy=11
+ and d_year=1999
+ and ss_customer_sk = c_customer_sk
+ and c_current_addr_sk = ca_address_sk
+ and substr(ca_zip,1,5) <> substr(s_zip,1,5)
+ and ss_store_sk = s_store_sk
+ group by i_brand
+ ,i_brand_id
+ ,i_manufact_id
+ ,i_manufact
+ order by ext_price desc
+ ,i_brand
+ ,i_brand_id
+ ,i_manufact_id
+ ,i_manufact
+limit 100
PREHOOK: type: QUERY
-POSTHOOK: query: explain select i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact, sum(ss_ext_sales_price) ext_price from date_dim, store_sales, item,customer,customer_address,store where date_dim.d_date_sk = store_sales.ss_sold_date_sk and store_sales.ss_item_sk = item.i_item_sk and i_manager_id=7 and d_moy=11 and d_year=1999 and store_sales.ss_customer_sk = customer.c_customer_sk and customer.c_current_addr_sk = customer_address.ca_address_sk and substr(ca_zip,1,5) <> substr(s_zip,1,5) and store_sales.ss_store_sk = store.s_store_sk group by i_brand ,i_brand_id ,i_manufact_id ,i_manufact order by ext_price desc ,i_brand ,i_brand_id ,i_manufact_id ,i_manufact limit 100
+POSTHOOK: query: explain
+select i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact,
+ sum(ss_ext_sales_price) ext_price
+ from date_dim, store_sales, item,customer,customer_address,store
+ where d_date_sk = ss_sold_date_sk
+ and ss_item_sk = i_item_sk
+ and i_manager_id=7
+ and d_moy=11
+ and d_year=1999
+ and ss_customer_sk = c_customer_sk
+ and c_current_addr_sk = ca_address_sk
+ and substr(ca_zip,1,5) <> substr(s_zip,1,5)
+ and ss_store_sk = s_store_sk
+ group by i_brand
+ ,i_brand_id
+ ,i_manufact_id
+ ,i_manufact
+ order by ext_price desc
+ ,i_brand
+ ,i_brand_id
+ ,i_manufact_id
+ ,i_manufact
+limit 100
POSTHOOK: type: QUERY
Plan optimized by CBO.
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query2.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query2.q.out b/ql/src/test/results/clientpositive/perf/query2.q.out
new file mode 100644
index 0000000..50d7f7b
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/query2.q.out
@@ -0,0 +1,259 @@
+PREHOOK: query: explain
+with wscs as
+ (select sold_date_sk
+ ,sales_price
+ from (select ws_sold_date_sk sold_date_sk
+ ,ws_ext_sales_price sales_price
+ from web_sales) x
+ union all
+ (select cs_sold_date_sk sold_date_sk
+ ,cs_ext_sales_price sales_price
+ from catalog_sales)),
+ wswscs as
+ (select d_week_seq,
+ sum(case when (d_day_name='Sunday') then sales_price else null end) sun_sales,
+ sum(case when (d_day_name='Monday') then sales_price else null end) mon_sales,
+ sum(case when (d_day_name='Tuesday') then sales_price else null end) tue_sales,
+ sum(case when (d_day_name='Wednesday') then sales_price else null end) wed_sales,
+ sum(case when (d_day_name='Thursday') then sales_price else null end) thu_sales,
+ sum(case when (d_day_name='Friday') then sales_price else null end) fri_sales,
+ sum(case when (d_day_name='Saturday') then sales_price else null end) sat_sales
+ from wscs
+ ,date_dim
+ where d_date_sk = sold_date_sk
+ group by d_week_seq)
+ select d_week_seq1
+ ,round(sun_sales1/sun_sales2,2)
+ ,round(mon_sales1/mon_sales2,2)
+ ,round(tue_sales1/tue_sales2,2)
+ ,round(wed_sales1/wed_sales2,2)
+ ,round(thu_sales1/thu_sales2,2)
+ ,round(fri_sales1/fri_sales2,2)
+ ,round(sat_sales1/sat_sales2,2)
+ from
+ (select wswscs.d_week_seq d_week_seq1
+ ,sun_sales sun_sales1
+ ,mon_sales mon_sales1
+ ,tue_sales tue_sales1
+ ,wed_sales wed_sales1
+ ,thu_sales thu_sales1
+ ,fri_sales fri_sales1
+ ,sat_sales sat_sales1
+ from wswscs,date_dim
+ where date_dim.d_week_seq = wswscs.d_week_seq and
+ d_year = 2001) y,
+ (select wswscs.d_week_seq d_week_seq2
+ ,sun_sales sun_sales2
+ ,mon_sales mon_sales2
+ ,tue_sales tue_sales2
+ ,wed_sales wed_sales2
+ ,thu_sales thu_sales2
+ ,fri_sales fri_sales2
+ ,sat_sales sat_sales2
+ from wswscs
+ ,date_dim
+ where date_dim.d_week_seq = wswscs.d_week_seq and
+ d_year = 2001+1) z
+ where d_week_seq1=d_week_seq2-53
+ order by d_week_seq1
+PREHOOK: type: QUERY
+POSTHOOK: query: explain
+with wscs as
+ (select sold_date_sk
+ ,sales_price
+ from (select ws_sold_date_sk sold_date_sk
+ ,ws_ext_sales_price sales_price
+ from web_sales) x
+ union all
+ (select cs_sold_date_sk sold_date_sk
+ ,cs_ext_sales_price sales_price
+ from catalog_sales)),
+ wswscs as
+ (select d_week_seq,
+ sum(case when (d_day_name='Sunday') then sales_price else null end) sun_sales,
+ sum(case when (d_day_name='Monday') then sales_price else null end) mon_sales,
+ sum(case when (d_day_name='Tuesday') then sales_price else null end) tue_sales,
+ sum(case when (d_day_name='Wednesday') then sales_price else null end) wed_sales,
+ sum(case when (d_day_name='Thursday') then sales_price else null end) thu_sales,
+ sum(case when (d_day_name='Friday') then sales_price else null end) fri_sales,
+ sum(case when (d_day_name='Saturday') then sales_price else null end) sat_sales
+ from wscs
+ ,date_dim
+ where d_date_sk = sold_date_sk
+ group by d_week_seq)
+ select d_week_seq1
+ ,round(sun_sales1/sun_sales2,2)
+ ,round(mon_sales1/mon_sales2,2)
+ ,round(tue_sales1/tue_sales2,2)
+ ,round(wed_sales1/wed_sales2,2)
+ ,round(thu_sales1/thu_sales2,2)
+ ,round(fri_sales1/fri_sales2,2)
+ ,round(sat_sales1/sat_sales2,2)
+ from
+ (select wswscs.d_week_seq d_week_seq1
+ ,sun_sales sun_sales1
+ ,mon_sales mon_sales1
+ ,tue_sales tue_sales1
+ ,wed_sales wed_sales1
+ ,thu_sales thu_sales1
+ ,fri_sales fri_sales1
+ ,sat_sales sat_sales1
+ from wswscs,date_dim
+ where date_dim.d_week_seq = wswscs.d_week_seq and
+ d_year = 2001) y,
+ (select wswscs.d_week_seq d_week_seq2
+ ,sun_sales sun_sales2
+ ,mon_sales mon_sales2
+ ,tue_sales tue_sales2
+ ,wed_sales wed_sales2
+ ,thu_sales thu_sales2
+ ,fri_sales fri_sales2
+ ,sat_sales sat_sales2
+ from wswscs
+ ,date_dim
+ where date_dim.d_week_seq = wswscs.d_week_seq and
+ d_year = 2001+1) z
+ where d_week_seq1=d_week_seq2-53
+ order by d_week_seq1
+POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 1 <- Union 2 (CONTAINS)
+Map 14 <- Union 15 (CONTAINS)
+Map 16 <- Union 15 (CONTAINS)
+Map 8 <- Union 2 (CONTAINS)
+Reducer 10 <- Map 9 (SIMPLE_EDGE), Union 15 (SIMPLE_EDGE)
+Reducer 11 <- Reducer 10 (SIMPLE_EDGE)
+Reducer 12 <- Map 13 (SIMPLE_EDGE), Reducer 11 (SIMPLE_EDGE)
+Reducer 3 <- Map 9 (SIMPLE_EDGE), Union 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Map 13 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
+Reducer 6 <- Reducer 12 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Reducer 6 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 7
+ File Output Operator [FS_60]
+ Select Operator [SEL_59] (rows=287491028 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ <-Reducer 6 [SIMPLE_EDGE]
+ SHUFFLE [RS_58]
+ Select Operator [SEL_57] (rows=287491028 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ Merge Join Operator [MERGEJOIN_95] (rows=287491028 width=135)
+ Conds:RS_54._col0=RS_55.(_col0 - 53)(Inner),Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col9","_col10","_col11","_col12","_col13","_col14","_col15"]
+ <-Reducer 12 [SIMPLE_EDGE]
+ SHUFFLE [RS_55]
+ PartitionCols:(_col0 - 53)
+ Merge Join Operator [MERGEJOIN_94] (rows=261355475 width=135)
+ Conds:RS_50._col0=RS_51._col0(Inner),Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ <-Map 13 [SIMPLE_EDGE]
+ SHUFFLE [RS_51]
+ PartitionCols:_col0
+ Select Operator [SEL_49] (rows=36524 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_90] (rows=36524 width=1119)
+ predicate:((d_year = 2002) and d_week_seq is not null)
+ TableScan [TS_20] (rows=73049 width=1119)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_week_seq","d_year"]
+ <-Reducer 11 [SIMPLE_EDGE]
+ SHUFFLE [RS_50]
+ PartitionCols:_col0
+ Group By Operator [GBY_45] (rows=237595882 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)","sum(VALUE._col3)","sum(VALUE._col4)","sum(VALUE._col5)","sum(VALUE._col6)"],keys:KEY._col0
+ <-Reducer 10 [SIMPLE_EDGE]
+ SHUFFLE [RS_44]
+ PartitionCols:_col0
+ Group By Operator [GBY_43] (rows=475191764 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(_col1)","sum(_col2)","sum(_col3)","sum(_col4)","sum(_col5)","sum(_col6)","sum(_col7)"],keys:_col0
+ Select Operator [SEL_41] (rows=475191764 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ Merge Join Operator [MERGEJOIN_93] (rows=475191764 width=135)
+ Conds:Union 15._col0=RS_39._col0(Inner),Output:["_col1","_col3","_col4"]
+ <-Map 9 [SIMPLE_EDGE]
+ SHUFFLE [RS_39]
+ PartitionCols:_col0
+ Select Operator [SEL_37] (rows=73049 width=1119)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_89] (rows=73049 width=1119)
+ predicate:(d_date_sk is not null and d_week_seq is not null)
+ TableScan [TS_8] (rows=73049 width=1119)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_week_seq","d_day_name"]
+ <-Union 15 [SIMPLE_EDGE]
+ <-Map 14 [CONTAINS]
+ Reduce Output Operator [RS_38]
+ PartitionCols:_col0
+ Select Operator [SEL_29] (rows=144002668 width=135)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_87] (rows=144002668 width=135)
+ predicate:ws_sold_date_sk is not null
+ TableScan [TS_27] (rows=144002668 width=135)
+ Output:["ws_sold_date_sk","ws_ext_sales_price"]
+ <-Map 16 [CONTAINS]
+ Reduce Output Operator [RS_38]
+ PartitionCols:_col0
+ Select Operator [SEL_32] (rows=287989836 width=135)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_88] (rows=287989836 width=135)
+ predicate:cs_sold_date_sk is not null
+ TableScan [TS_30] (rows=287989836 width=135)
+ Output:["cs_sold_date_sk","cs_ext_sales_price"]
+ <-Reducer 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_54]
+ PartitionCols:_col0
+ Merge Join Operator [MERGEJOIN_92] (rows=261355475 width=135)
+ Conds:RS_23._col0=RS_24._col0(Inner),Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ <-Map 13 [SIMPLE_EDGE]
+ SHUFFLE [RS_24]
+ PartitionCols:_col0
+ Select Operator [SEL_22] (rows=36524 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_86] (rows=36524 width=1119)
+ predicate:((d_year = 2001) and d_week_seq is not null)
+ Please refer to the previous TableScan [TS_20]
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_23]
+ PartitionCols:_col0
+ Group By Operator [GBY_18] (rows=237595882 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)","sum(VALUE._col3)","sum(VALUE._col4)","sum(VALUE._col5)","sum(VALUE._col6)"],keys:KEY._col0
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_17]
+ PartitionCols:_col0
+ Group By Operator [GBY_16] (rows=475191764 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(_col1)","sum(_col2)","sum(_col3)","sum(_col4)","sum(_col5)","sum(_col6)","sum(_col7)"],keys:_col0
+ Select Operator [SEL_14] (rows=475191764 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ Merge Join Operator [MERGEJOIN_91] (rows=475191764 width=135)
+ Conds:Union 2._col0=RS_12._col0(Inner),Output:["_col1","_col3","_col4"]
+ <-Map 9 [SIMPLE_EDGE]
+ SHUFFLE [RS_12]
+ PartitionCols:_col0
+ Select Operator [SEL_10] (rows=73049 width=1119)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_85] (rows=73049 width=1119)
+ predicate:(d_date_sk is not null and d_week_seq is not null)
+ Please refer to the previous TableScan [TS_8]
+ <-Union 2 [SIMPLE_EDGE]
+ <-Map 1 [CONTAINS]
+ Reduce Output Operator [RS_11]
+ PartitionCols:_col0
+ Select Operator [SEL_2] (rows=144002668 width=135)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_83] (rows=144002668 width=135)
+ predicate:ws_sold_date_sk is not null
+ TableScan [TS_0] (rows=144002668 width=135)
+ Output:["ws_sold_date_sk","ws_ext_sales_price"]
+ <-Map 8 [CONTAINS]
+ Reduce Output Operator [RS_11]
+ PartitionCols:_col0
+ Select Operator [SEL_5] (rows=287989836 width=135)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_84] (rows=287989836 width=135)
+ predicate:cs_sold_date_sk is not null
+ TableScan [TS_3] (rows=287989836 width=135)
+ Output:["cs_sold_date_sk","cs_ext_sales_price"]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query20.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query20.q.out b/ql/src/test/results/clientpositive/perf/query20.q.out
index cff652f..c07141b 100644
--- a/ql/src/test/results/clientpositive/perf/query20.q.out
+++ b/ql/src/test/results/clientpositive/perf/query20.q.out
@@ -1,6 +1,58 @@
-PREHOOK: query: explain select i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(cs_ext_sales_price) as itemrevenue ,sum(cs_ext_sales_price)*100/sum(sum(cs_ext_sales_price)) over (partition by i_class) as revenueratio from catalog_sales ,item ,date_dim where catalog_sales.cs_item_sk = item.i_item_sk and i_category in ('Jewelry', 'Sports', 'Books') and catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and d_date between '2001-01-12' and '2001-02-11' group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio limit 100
+PREHOOK: query: explain
+select i_item_desc
+ ,i_category
+ ,i_class
+ ,i_current_price
+ ,sum(cs_ext_sales_price) as itemrevenue
+ ,sum(cs_ext_sales_price)*100/sum(sum(cs_ext_sales_price)) over
+ (partition by i_class) as revenueratio
+ from catalog_sales
+ ,item
+ ,date_dim
+ where cs_item_sk = i_item_sk
+ and i_category in ('Jewelry', 'Sports', 'Books')
+ and cs_sold_date_sk = d_date_sk
+ and d_date between cast('2001-01-12' as date)
+ and (cast('2001-01-12' as date) + 30 days)
+ group by i_item_id
+ ,i_item_desc
+ ,i_category
+ ,i_class
+ ,i_current_price
+ order by i_category
+ ,i_class
+ ,i_item_id
+ ,i_item_desc
+ ,revenueratio
+limit 100
PREHOOK: type: QUERY
-POSTHOOK: query: explain select i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(cs_ext_sales_price) as itemrevenue ,sum(cs_ext_sales_price)*100/sum(sum(cs_ext_sales_price)) over (partition by i_class) as revenueratio from catalog_sales ,item ,date_dim where catalog_sales.cs_item_sk = item.i_item_sk and i_category in ('Jewelry', 'Sports', 'Books') and catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and d_date between '2001-01-12' and '2001-02-11' group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio limit 100
+POSTHOOK: query: explain
+select i_item_desc
+ ,i_category
+ ,i_class
+ ,i_current_price
+ ,sum(cs_ext_sales_price) as itemrevenue
+ ,sum(cs_ext_sales_price)*100/sum(sum(cs_ext_sales_price)) over
+ (partition by i_class) as revenueratio
+ from catalog_sales
+ ,item
+ ,date_dim
+ where cs_item_sk = i_item_sk
+ and i_category in ('Jewelry', 'Sports', 'Books')
+ and cs_sold_date_sk = d_date_sk
+ and d_date between cast('2001-01-12' as date)
+ and (cast('2001-01-12' as date) + 30 days)
+ group by i_item_id
+ ,i_item_desc
+ ,i_category
+ ,i_class
+ ,i_current_price
+ order by i_category
+ ,i_class
+ ,i_item_id
+ ,i_item_desc
+ ,revenueratio
+limit 100
POSTHOOK: type: QUERY
Plan optimized by CBO.
@@ -13,14 +65,14 @@ Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
Stage-0
Fetch Operator
- limit:100
+ limit:-1
Stage-1
Reducer 6
- File Output Operator [FS_28]
+ File Output Operator [FS_29]
Limit [LIM_27] (rows=100 width=135)
Number of rows:100
Select Operator [SEL_26] (rows=174233858 width=135)
- Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
<-Reducer 5 [SIMPLE_EDGE]
SHUFFLE [RS_25]
Select Operator [SEL_23] (rows=174233858 width=135)
@@ -41,28 +93,28 @@ Stage-0
PartitionCols:_col0, _col1, _col2, _col3, _col4
Group By Operator [GBY_16] (rows=348467716 width=135)
Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)"],keys:_col10, _col9, _col6, _col7, _col8
- Merge Join Operator [MERGEJOIN_38] (rows=348467716 width=135)
+ Merge Join Operator [MERGEJOIN_39] (rows=348467716 width=135)
Conds:RS_12._col1=RS_13._col0(Inner),Output:["_col2","_col6","_col7","_col8","_col9","_col10"]
<-Map 8 [SIMPLE_EDGE]
SHUFFLE [RS_13]
PartitionCols:_col0
Select Operator [SEL_8] (rows=231000 width=1436)
Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
- Filter Operator [FIL_36] (rows=231000 width=1436)
+ Filter Operator [FIL_37] (rows=231000 width=1436)
predicate:((i_category) IN ('Jewelry', 'Sports', 'Books') and i_item_sk is not null)
TableScan [TS_6] (rows=462000 width=1436)
default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_item_id","i_item_desc","i_current_price","i_class","i_category"]
<-Reducer 2 [SIMPLE_EDGE]
SHUFFLE [RS_12]
PartitionCols:_col1
- Merge Join Operator [MERGEJOIN_37] (rows=316788826 width=135)
+ Merge Join Operator [MERGEJOIN_38] (rows=316788826 width=135)
Conds:RS_9._col0=RS_10._col0(Inner),Output:["_col1","_col2"]
<-Map 1 [SIMPLE_EDGE]
SHUFFLE [RS_9]
PartitionCols:_col0
Select Operator [SEL_2] (rows=287989836 width=135)
Output:["_col0","_col1","_col2"]
- Filter Operator [FIL_34] (rows=287989836 width=135)
+ Filter Operator [FIL_35] (rows=287989836 width=135)
predicate:(cs_item_sk is not null and cs_sold_date_sk is not null)
TableScan [TS_0] (rows=287989836 width=135)
default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:NONE,Output:["cs_sold_date_sk","cs_item_sk","cs_ext_sales_price"]
@@ -71,8 +123,8 @@ Stage-0
PartitionCols:_col0
Select Operator [SEL_5] (rows=8116 width=1119)
Output:["_col0"]
- Filter Operator [FIL_35] (rows=8116 width=1119)
- predicate:(d_date BETWEEN '2001-01-12' AND '2001-02-11' and d_date_sk is not null)
+ Filter Operator [FIL_36] (rows=8116 width=1119)
+ predicate:(CAST( d_date AS TIMESTAMP) BETWEEN 2001-01-12 00:00:00.0 AND 2001-02-11 00:00:00.0 and d_date_sk is not null)
TableScan [TS_3] (rows=73049 width=1119)
default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query23.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query23.q.out b/ql/src/test/results/clientpositive/perf/query23.q.out
index 5794301..b8cdad3 100644
--- a/ql/src/test/results/clientpositive/perf/query23.q.out
+++ b/ql/src/test/results/clientpositive/perf/query23.q.out
@@ -1,6 +1,7 @@
-Warning: Shuffle Join MERGEJOIN[369][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 30' is a cross product
Warning: Shuffle Join MERGEJOIN[367][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 25' is a cross product
-PREHOOK: query: explain with frequent_ss_items as
+Warning: Shuffle Join MERGEJOIN[369][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 30' is a cross product
+PREHOOK: query: explain
+with frequent_ss_items as
(select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt
from store_sales
,date_dim
@@ -50,7 +51,8 @@ from
and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer))) y
limit 100
PREHOOK: type: QUERY
-POSTHOOK: query: explain with frequent_ss_items as
+POSTHOOK: query: explain
+with frequent_ss_items as
(select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt
from store_sales
,date_dim
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query24.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query24.q.out b/ql/src/test/results/clientpositive/perf/query24.q.out
index 105871f..2aa0c19 100644
--- a/ql/src/test/results/clientpositive/perf/query24.q.out
+++ b/ql/src/test/results/clientpositive/perf/query24.q.out
@@ -1,99 +1,101 @@
Warning: Shuffle Join MERGEJOIN[154][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 8' is a cross product
-PREHOOK: query: explain with ssales as
- (select c_last_name
- ,c_first_name
- ,s_store_name
- ,ca_state
- ,s_state
- ,i_color
- ,i_current_price
- ,i_manager_id
- ,i_units
- ,i_size
- ,sum(ss_sales_price) netpaid
- from store_sales
- ,store_returns
- ,store
- ,item
- ,customer
- ,customer_address
- where ss_ticket_number = sr_ticket_number
- and ss_item_sk = sr_item_sk
- and ss_customer_sk = c_customer_sk
- and ss_item_sk = i_item_sk
- and ss_store_sk = s_store_sk
- and c_birth_country = upper(ca_country)
- and s_zip = ca_zip
- and s_market_id=7
- group by c_last_name
- ,c_first_name
- ,s_store_name
- ,ca_state
- ,s_state
- ,i_color
- ,i_current_price
- ,i_manager_id
- ,i_units
- ,i_size)
- select c_last_name
- ,c_first_name
- ,s_store_name
- ,sum(netpaid) paid
- from ssales
- where i_color = 'orchid'
- group by c_last_name
- ,c_first_name
- ,s_store_name
- having sum(netpaid) > (select 0.05*avg(netpaid)
- from ssales)
+PREHOOK: query: explain
+with ssales as
+(select c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size
+ ,sum(ss_sales_price) netpaid
+from store_sales
+ ,store_returns
+ ,store
+ ,item
+ ,customer
+ ,customer_address
+where ss_ticket_number = sr_ticket_number
+ and ss_item_sk = sr_item_sk
+ and ss_customer_sk = c_customer_sk
+ and ss_item_sk = i_item_sk
+ and ss_store_sk = s_store_sk
+ and c_birth_country = upper(ca_country)
+ and s_zip = ca_zip
+and s_market_id=7
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size)
+select c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,sum(netpaid) paid
+from ssales
+where i_color = 'orchid'
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+having sum(netpaid) > (select 0.05*avg(netpaid)
+ from ssales)
PREHOOK: type: QUERY
-POSTHOOK: query: explain with ssales as
- (select c_last_name
- ,c_first_name
- ,s_store_name
- ,ca_state
- ,s_state
- ,i_color
- ,i_current_price
- ,i_manager_id
- ,i_units
- ,i_size
- ,sum(ss_sales_price) netpaid
- from store_sales
- ,store_returns
- ,store
- ,item
- ,customer
- ,customer_address
- where ss_ticket_number = sr_ticket_number
- and ss_item_sk = sr_item_sk
- and ss_customer_sk = c_customer_sk
- and ss_item_sk = i_item_sk
- and ss_store_sk = s_store_sk
- and c_birth_country = upper(ca_country)
- and s_zip = ca_zip
- and s_market_id=7
- group by c_last_name
- ,c_first_name
- ,s_store_name
- ,ca_state
- ,s_state
- ,i_color
- ,i_current_price
- ,i_manager_id
- ,i_units
- ,i_size)
- select c_last_name
- ,c_first_name
- ,s_store_name
- ,sum(netpaid) paid
- from ssales
- where i_color = 'orchid'
- group by c_last_name
- ,c_first_name
- ,s_store_name
- having sum(netpaid) > (select 0.05*avg(netpaid)
- from ssales)
+POSTHOOK: query: explain
+with ssales as
+(select c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size
+ ,sum(ss_sales_price) netpaid
+from store_sales
+ ,store_returns
+ ,store
+ ,item
+ ,customer
+ ,customer_address
+where ss_ticket_number = sr_ticket_number
+ and ss_item_sk = sr_item_sk
+ and ss_customer_sk = c_customer_sk
+ and ss_item_sk = i_item_sk
+ and ss_store_sk = s_store_sk
+ and c_birth_country = upper(ca_country)
+ and s_zip = ca_zip
+and s_market_id=7
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size)
+select c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,sum(netpaid) paid
+from ssales
+where i_color = 'orchid'
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+having sum(netpaid) > (select 0.05*avg(netpaid)
+ from ssales)
POSTHOOK: type: QUERY
Plan optimized by CBO.