Skip to content

Instantly share code, notes, and snippets.

@henryr

henryr/com.cloudera.impala.planner.PlannerTest.testJoinOrder

Created July 9, 2015 20:49
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save henryr/f769019ac884adcd98a0 to your computer and use it in GitHub Desktop.
Save henryr/f769019ac884adcd98a0 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
com.cloudera.impala.planner.PlannerTest.testJoinOrder
com.cloudera.impala.planner.PlannerTest.testJoinOrder
Failing for the past 1 build (Since Failed#594 )
Took 0.21 sec.
add description
Error Message
section PLAN of query:
select
n_name,
round(sum(l_extendedprice * (1 - l_discount)), 5) as revenue
from tpch.customer,
tpch.orders o,
tpch.lineitem l,
tpch.supplier s,
tpch.nation,
tpch.region
where l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and c_custkey = o_custkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= '1994-01-01'
and o_orderdate < '1995-01-01'
group by
n_name
order by
revenue desc
limit 100
actual result doesn't match expected result:
12:TOP-N [LIMIT=100]
| order by: round(sum(l_extendedprice * (1 - l_discount)), 5) DESC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--03:SCAN HDFS [tpch.supplier s]
| partitions=1/1 files=1 size=1.33MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: n_regionkey = r_regionkey
|
|--05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
08:HASH JOIN [INNER JOIN]
| hash predicates: tpch.customer.c_nationkey = tpch.nation.n_nationkey
|
|--04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--01:SCAN HDFS [tpch.orders o]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem l]
partitions=1/1 files=1 size=718.94MB
expected:
12:TOP-N [LIMIT=100]
| order by: round(sum(l_extendedprice * (1 - l_discount)), 5) DESC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN]
| hash predicates: n_regionkey = r_regionkey
|
|--05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
|
|--04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
|
|--03:SCAN HDFS [tpch.supplier s]
| partitions=1/1 files=1 size=1.33MB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--01:SCAN HDFS [tpch.orders o]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem l]
partitions=1/1 files=1 size=718.94MB
section DISTRIBUTEDPLAN of query:
select
n_name,
round(sum(l_extendedprice * (1 - l_discount)), 5) as revenue
from tpch.customer,
tpch.orders o,
tpch.lineitem l,
tpch.supplier s,
tpch.nation,
tpch.region
where l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and c_custkey = o_custkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= '1994-01-01'
and o_orderdate < '1995-01-01'
group by
n_name
order by
revenue desc
limit 100
actual result doesn't match expected result:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: round(sum(l_extendedprice * (1 - l_discount)), 5) DESC
| limit: 100
|
12:TOP-N [LIMIT=100]
| order by: round(sum(l_extendedprice * (1 - l_discount)), 5) DESC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: n_name
|
18:EXCHANGE [HASH(n_name)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--17:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.supplier s]
| partitions=1/1 files=1 size=1.33MB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n_regionkey = r_regionkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: tpch.customer.c_nationkey = tpch.nation.n_nationkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.orders o]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem l]
partitions=1/1 files=1 size=718.94MB
expected:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: round(sum(l_extendedprice * (1 - l_discount)), 5) DESC
| limit: 100
|
12:TOP-N [LIMIT=100]
| order by: round(sum(l_extendedprice * (1 - l_discount)), 5) DESC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: n_name
|
18:EXCHANGE [HASH(n_name)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n_regionkey = r_regionkey
|
|--17:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.supplier s]
| partitions=1/1 files=1 size=1.33MB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.orders o]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem l]
partitions=1/1 files=1 size=718.94MB
Stacktrace
java.lang.AssertionError: section PLAN of query:
select
n_name,
round(sum(l_extendedprice * (1 - l_discount)), 5) as revenue
from tpch.customer,
tpch.orders o,
tpch.lineitem l,
tpch.supplier s,
tpch.nation,
tpch.region
where l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and c_custkey = o_custkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= '1994-01-01'
and o_orderdate < '1995-01-01'
group by
n_name
order by
revenue desc
limit 100
actual result doesn't match expected result:
12:TOP-N [LIMIT=100]
| order by: round(sum(l_extendedprice * (1 - l_discount)), 5) DESC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--03:SCAN HDFS [tpch.supplier s]
| partitions=1/1 files=1 size=1.33MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: n_regionkey = r_regionkey
|
|--05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
08:HASH JOIN [INNER JOIN]
| hash predicates: tpch.customer.c_nationkey = tpch.nation.n_nationkey
|
|--04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--01:SCAN HDFS [tpch.orders o]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem l]
partitions=1/1 files=1 size=718.94MB
expected:
12:TOP-N [LIMIT=100]
| order by: round(sum(l_extendedprice * (1 - l_discount)), 5) DESC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN]
| hash predicates: n_regionkey = r_regionkey
|
|--05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
|
|--04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
|
|--03:SCAN HDFS [tpch.supplier s]
| partitions=1/1 files=1 size=1.33MB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--01:SCAN HDFS [tpch.orders o]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem l]
partitions=1/1 files=1 size=718.94MB
section DISTRIBUTEDPLAN of query:
select
n_name,
round(sum(l_extendedprice * (1 - l_discount)), 5) as revenue
from tpch.customer,
tpch.orders o,
tpch.lineitem l,
tpch.supplier s,
tpch.nation,
tpch.region
where l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and c_custkey = o_custkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= '1994-01-01'
and o_orderdate < '1995-01-01'
group by
n_name
order by
revenue desc
limit 100
actual result doesn't match expected result:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: round(sum(l_extendedprice * (1 - l_discount)), 5) DESC
| limit: 100
|
12:TOP-N [LIMIT=100]
| order by: round(sum(l_extendedprice * (1 - l_discount)), 5) DESC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: n_name
|
18:EXCHANGE [HASH(n_name)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--17:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.supplier s]
| partitions=1/1 files=1 size=1.33MB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n_regionkey = r_regionkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: tpch.customer.c_nationkey = tpch.nation.n_nationkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.orders o]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem l]
partitions=1/1 files=1 size=718.94MB
expected:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: round(sum(l_extendedprice * (1 - l_discount)), 5) DESC
| limit: 100
|
12:TOP-N [LIMIT=100]
| order by: round(sum(l_extendedprice * (1 - l_discount)), 5) DESC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: n_name
|
18:EXCHANGE [HASH(n_name)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n_regionkey = r_regionkey
|
|--17:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.supplier s]
| partitions=1/1 files=1 size=1.33MB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.orders o]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem l]
partitions=1/1 files=1 size=718.94MB
at org.junit.Assert.fail(Assert.java:88)
at com.cloudera.impala.planner.PlannerTestBase.runPlannerTestFile(PlannerTestBase.java:643)
at com.cloudera.impala.planner.PlannerTestBase.runPlannerTestFile(PlannerTestBase.java:648)
at com.cloudera.impala.planner.PlannerTest.testJoinOrder(PlannerTest.java:81)
Raw
com.cloudera.impala.planner.PlannerTest.testTpcds
section PLAN of query:
select
i_item_id,
avg(ss_quantity) agg1,
avg(ss_list_price) agg2,
avg(ss_coupon_amt) agg3,
avg(ss_sales_price) agg4
from
store_sales,
customer_demographics,
date_dim,
item,
promotion
where
ss_sold_date_sk = d_date_sk
and ss_item_sk = i_item_sk
and ss_cdemo_sk = cd_demo_sk
and ss_promo_sk = p_promo_sk
and cd_gender = 'F'
and cd_marital_status = 'W'
and cd_education_status = 'Primary'
and (p_channel_email = 'N'
or p_channel_event = 'N')
and d_year = 1998
group by
i_item_id
order by
i_item_id
limit 100
actual result doesn't match expected result:
10:TOP-N [LIMIT=100]
| order by: i_item_id ASC
|
09:AGGREGATE [FINALIZE]
| output: avg(ss_quantity), avg(ss_list_price), avg(ss_coupon_amt), avg(ss_sales_price)
| group by: i_item_id
|
08:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--03:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: ss_promo_sk = p_promo_sk
|
|--04:SCAN HDFS [tpcds.promotion]
| partitions=1/1 files=1 size=36.36KB
| predicates: (p_channel_email = 'N' OR p_channel_event = 'N')
|
06:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--02:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1998
|
05:HASH JOIN [INNER JOIN]
| hash predicates: ss_cdemo_sk = cd_demo_sk
|
|--01:SCAN HDFS [tpcds.customer_demographics]
| partitions=1/1 files=1 size=76.92MB
| predicates: cd_gender = 'F', cd_marital_status = 'W', cd_education_status = 'Primary'
|
00:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
expected:
10:TOP-N [LIMIT=100]
| order by: i_item_id ASC
|
09:AGGREGATE [FINALIZE]
| output: avg(ss_quantity), avg(ss_list_price), avg(ss_coupon_amt), avg(ss_sales_price)
| group by: i_item_id
|
08:HASH JOIN [INNER JOIN]
| hash predicates: ss_promo_sk = p_promo_sk
|
|--04:SCAN HDFS [tpcds.promotion]
| partitions=1/1 files=1 size=36.36KB
| predicates: (p_channel_email = 'N' OR p_channel_event = 'N')
|
07:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
|
|--03:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--02:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1998
|
05:HASH JOIN [INNER JOIN]
| hash predicates: ss_cdemo_sk = cd_demo_sk
|
|--01:SCAN HDFS [tpcds.customer_demographics]
| partitions=1/1 files=1 size=76.92MB
| predicates: cd_gender = 'F', cd_marital_status = 'W', cd_education_status = 'Primary'
|
00:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
section DISTRIBUTEDPLAN of query:
select
i_item_id,
avg(ss_quantity) agg1,
avg(ss_list_price) agg2,
avg(ss_coupon_amt) agg3,
avg(ss_sales_price) agg4
from
store_sales,
customer_demographics,
date_dim,
item,
promotion
where
ss_sold_date_sk = d_date_sk
and ss_item_sk = i_item_sk
and ss_cdemo_sk = cd_demo_sk
and ss_promo_sk = p_promo_sk
and cd_gender = 'F'
and cd_marital_status = 'W'
and cd_education_status = 'Primary'
and (p_channel_email = 'N'
or p_channel_event = 'N')
and d_year = 1998
group by
i_item_id
order by
i_item_id
limit 100
actual result doesn't match expected result:
17:MERGING-EXCHANGE [UNPARTITIONED]
| order by: i_item_id ASC
| limit: 100
|
10:TOP-N [LIMIT=100]
| order by: i_item_id ASC
|
16:AGGREGATE [FINALIZE]
| output: avg:merge(ss_quantity), avg:merge(ss_list_price), avg:merge(ss_coupon_amt), avg:merge(ss_sales_price)
| group by: i_item_id
|
15:EXCHANGE [HASH(i_item_id)]
|
09:AGGREGATE
| output: avg(ss_quantity), avg(ss_list_price), avg(ss_coupon_amt), avg(ss_sales_price)
| group by: i_item_id
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_item_sk = i_item_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--14:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_promo_sk = p_promo_sk
|
|--13:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpcds.promotion]
| partitions=1/1 files=1 size=36.36KB
| predicates: (p_channel_email = 'N' OR p_channel_event = 'N')
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--12:EXCHANGE [BROADCAST]
| |
| 02:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1998
|
05:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_cdemo_sk = cd_demo_sk
|
|--11:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpcds.customer_demographics]
| partitions=1/1 files=1 size=76.92MB
| predicates: cd_gender = 'F', cd_marital_status = 'W', cd_education_status = 'Primary'
|
00:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
expected:
17:MERGING-EXCHANGE [UNPARTITIONED]
| order by: i_item_id ASC
| limit: 100
|
10:TOP-N [LIMIT=100]
| order by: i_item_id ASC
|
16:AGGREGATE [FINALIZE]
| output: avg:merge(ss_quantity), avg:merge(ss_list_price), avg:merge(ss_coupon_amt), avg:merge(ss_sales_price)
| group by: i_item_id
|
15:EXCHANGE [HASH(i_item_id)]
|
09:AGGREGATE
| output: avg(ss_quantity), avg(ss_list_price), avg(ss_coupon_amt), avg(ss_sales_price)
| group by: i_item_id
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_promo_sk = p_promo_sk
|
|--14:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpcds.promotion]
| partitions=1/1 files=1 size=36.36KB
| predicates: (p_channel_email = 'N' OR p_channel_event = 'N')
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_item_sk = i_item_sk
|
|--13:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--12:EXCHANGE [BROADCAST]
| |
| 02:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1998
|
05:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_cdemo_sk = cd_demo_sk
|
|--11:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpcds.customer_demographics]
| partitions=1/1 files=1 size=76.92MB
| predicates: cd_gender = 'F', cd_marital_status = 'W', cd_education_status = 'Primary'
|
00:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
section PLAN of query:
select
s_store_name,
i_item_desc,
sc.revenue,
i_current_price,
i_wholesale_cost,
i_brand
from
store,
item,
(select
ss_store_sk,
avg(revenue) as ave
from
(select
ss_store_sk,
ss_item_sk,
sum(ss_sales_price) as revenue
from
store_sales,
date_dim
where
ss_sold_date_sk = d_date_sk
and d_month_seq between 1212 and 1212 + 11
group by
ss_store_sk,
ss_item_sk
) sa
group by
ss_store_sk
) sb,
(select
ss_store_sk,
ss_item_sk,
sum(ss_sales_price) as revenue
from
store_sales,
date_dim
where
ss_sold_date_sk = d_date_sk
and d_month_seq between 1212 and 1212 + 11
group by
ss_store_sk,
ss_item_sk
) sc
where
sb.ss_store_sk = sc.ss_store_sk
and sc.revenue <= 0.1 * sb.ave
and s_store_sk = sc.ss_store_sk
and i_item_sk = sc.ss_item_sk
order by
s_store_name,
i_item_desc
limit 100
actual result doesn't match expected result:
14:TOP-N [LIMIT=100]
| order by: s_store_name ASC, i_item_desc ASC
|
13:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--01:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
12:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = ss_store_sk
| other predicates: sum(ss_sales_price) <= 0.1 * avg(revenue)
|
|--06:AGGREGATE [FINALIZE]
| | output: avg(sum(ss_sales_price))
| | group by: ss_store_sk
| |
| 05:AGGREGATE [FINALIZE]
| | output: sum(ss_sales_price)
| | group by: ss_store_sk, ss_item_sk
| |
| 04:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--03:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
| |
| 02:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
11:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = s_store_sk
|
|--00:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
|
10:AGGREGATE [FINALIZE]
| output: sum(ss_sales_price)
| group by: ss_store_sk, ss_item_sk
|
09:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--08:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
|
07:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
expected:
14:TOP-N [LIMIT=100]
| order by: s_store_name ASC, i_item_desc ASC
|
13:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = ss_store_sk
| other predicates: sum(ss_sales_price) <= 0.1 * avg(revenue)
|
|--06:AGGREGATE [FINALIZE]
| | output: avg(sum(ss_sales_price))
| | group by: ss_store_sk
| |
| 05:AGGREGATE [FINALIZE]
| | output: sum(ss_sales_price)
| | group by: ss_store_sk, ss_item_sk
| |
| 04:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--03:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
| |
| 02:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
12:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
|
|--01:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
11:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = s_store_sk
|
|--00:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
|
10:AGGREGATE [FINALIZE]
| output: sum(ss_sales_price)
| group by: ss_store_sk, ss_item_sk
|
09:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--08:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
|
07:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
section DISTRIBUTEDPLAN of query:
select
s_store_name,
i_item_desc,
sc.revenue,
i_current_price,
i_wholesale_cost,
i_brand
from
store,
item,
(select
ss_store_sk,
avg(revenue) as ave
from
(select
ss_store_sk,
ss_item_sk,
sum(ss_sales_price) as revenue
from
store_sales,
date_dim
where
ss_sold_date_sk = d_date_sk
and d_month_seq between 1212 and 1212 + 11
group by
ss_store_sk,
ss_item_sk
) sa
group by
ss_store_sk
) sb,
(select
ss_store_sk,
ss_item_sk,
sum(ss_sales_price) as revenue
from
store_sales,
date_dim
where
ss_sold_date_sk = d_date_sk
and d_month_seq between 1212 and 1212 + 11
group by
ss_store_sk,
ss_item_sk
) sc
where
sb.ss_store_sk = sc.ss_store_sk
and sc.revenue <= 0.1 * sb.ave
and s_store_sk = sc.ss_store_sk
and i_item_sk = sc.ss_item_sk
order by
s_store_name,
i_item_desc
limit 100
actual result doesn't match expected result:
26:MERGING-EXCHANGE [UNPARTITIONED]
| order by: s_store_name ASC, i_item_desc ASC
| limit: 100
|
14:TOP-N [LIMIT=100]
| order by: s_store_name ASC, i_item_desc ASC
|
13:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_item_sk = i_item_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--25:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
12:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_store_sk = ss_store_sk
| other predicates: sum(ss_sales_price) <= 0.1 * avg(revenue)
|
|--24:EXCHANGE [BROADCAST]
| |
| 23:AGGREGATE [FINALIZE]
| | output: avg:merge(revenue)
| | group by: ss_store_sk
| |
| 22:EXCHANGE [HASH(ss_store_sk)]
| |
| 06:AGGREGATE
| | output: avg(sum(ss_sales_price))
| | group by: ss_store_sk
| |
| 21:AGGREGATE [FINALIZE]
| | output: sum:merge(ss_sales_price)
| | group by: ss_store_sk, ss_item_sk
| |
| 20:EXCHANGE [HASH(ss_store_sk,ss_item_sk)]
| |
| 05:AGGREGATE
| | output: sum(ss_sales_price)
| | group by: ss_store_sk, ss_item_sk
| |
| 04:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--19:EXCHANGE [BROADCAST]
| | |
| | 03:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
| |
| 02:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
11:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_store_sk = s_store_sk
|
|--18:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
|
17:AGGREGATE [FINALIZE]
| output: sum:merge(ss_sales_price)
| group by: ss_store_sk, ss_item_sk
|
16:EXCHANGE [HASH(ss_store_sk,ss_item_sk)]
|
10:AGGREGATE
| output: sum(ss_sales_price)
| group by: ss_store_sk, ss_item_sk
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--15:EXCHANGE [BROADCAST]
| |
| 08:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
|
07:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
expected:
26:MERGING-EXCHANGE [UNPARTITIONED]
| order by: s_store_name ASC, i_item_desc ASC
| limit: 100
|
14:TOP-N [LIMIT=100]
| order by: s_store_name ASC, i_item_desc ASC
|
13:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_store_sk = ss_store_sk
| other predicates: sum(ss_sales_price) <= 0.1 * avg(revenue)
|
|--25:EXCHANGE [BROADCAST]
| |
| 24:AGGREGATE [FINALIZE]
| | output: avg:merge(revenue)
| | group by: ss_store_sk
| |
| 23:EXCHANGE [HASH(ss_store_sk)]
| |
| 06:AGGREGATE
| | output: avg(sum(ss_sales_price))
| | group by: ss_store_sk
| |
| 22:AGGREGATE [FINALIZE]
| | output: sum:merge(ss_sales_price)
| | group by: ss_store_sk, ss_item_sk
| |
| 21:EXCHANGE [HASH(ss_store_sk,ss_item_sk)]
| |
| 05:AGGREGATE
| | output: sum(ss_sales_price)
| | group by: ss_store_sk, ss_item_sk
| |
| 04:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--20:EXCHANGE [BROADCAST]
| | |
| | 03:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
| |
| 02:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
12:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_item_sk = i_item_sk
|
|--19:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
11:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_store_sk = s_store_sk
|
|--18:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
|
17:AGGREGATE [FINALIZE]
| output: sum:merge(ss_sales_price)
| group by: ss_store_sk, ss_item_sk
|
16:EXCHANGE [HASH(ss_store_sk,ss_item_sk)]
|
10:AGGREGATE
| output: sum(ss_sales_price)
| group by: ss_store_sk, ss_item_sk
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--15:EXCHANGE [BROADCAST]
| |
| 08:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
|
07:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
section PLAN of query:
select
c_last_name,
c_first_name,
c_salutation,
c_preferred_cust_flag,
ss_ticket_number,
cnt
from
(select
ss_ticket_number,
ss_customer_sk,
count(*) cnt
from
store_sales,
date_dim,
store,
household_demographics
where
store_sales.ss_sold_date_sk = date_dim.d_date_sk
and store_sales.ss_store_sk = store.s_store_sk
and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
and (household_demographics.hd_buy_potential = '>10000'
or household_demographics.hd_buy_potential = 'unknown')
and household_demographics.hd_vehicle_count > 0
and case when household_demographics.hd_vehicle_count > 0 then household_demographics.hd_dep_count / household_demographics.hd_vehicle_count else null end > 1
and store.s_county in ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County')
group by
ss_ticket_number,
ss_customer_sk
) dj,
customer
where
ss_customer_sk = c_customer_sk
and cnt between 1 and 5
order by
cnt desc
limit 1000
actual result doesn't match expected result:
10:TOP-N [LIMIT=1000]
| order by: cnt DESC
|
09:HASH JOIN [INNER JOIN]
| hash predicates: c_customer_sk = ss_customer_sk
|
|--07:AGGREGATE [FINALIZE]
| | output: count(*)
| | group by: ss_ticket_number, ss_customer_sk
| | having: count(*) >= 1, count(*) <= 5
| |
| 06:HASH JOIN [INNER JOIN]
| | hash predicates: store_sales.ss_sold_date_sk = date_dim.d_date_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| |
| |--01:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| |
| 05:HASH JOIN [INNER JOIN]
| | hash predicates: store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
| |
| |--03:SCAN HDFS [tpcds.household_demographics]
| | partitions=1/1 files=1 size=148.10KB
| | predicates: (household_demographics.hd_buy_potential = '>10000' OR household_demographics.hd_buy_potential = 'unknown'), household_demographics.hd_vehicle_count > 0, CASE WHEN household_demographics.hd_vehicle_count > 0 THEN household_demographics.hd_dep_count / household_demographics.hd_vehicle_count ELSE NULL END > 1
| |
| 04:HASH JOIN [INNER JOIN]
| | hash predicates: store_sales.ss_store_sk = store.s_store_sk
| |
| |--02:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| | predicates: store.s_county IN ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County')
| |
| 00:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
08:SCAN HDFS [tpcds.customer]
partitions=1/1 files=1 size=12.60MB
expected:
10:TOP-N [LIMIT=1000]
| order by: cnt DESC
|
09:HASH JOIN [INNER JOIN]
| hash predicates: c_customer_sk = ss_customer_sk
|
|--07:AGGREGATE [FINALIZE]
| | output: count(*)
| | group by: ss_ticket_number, ss_customer_sk
| | having: count(*) >= 1, count(*) <= 5
| |
| 06:HASH JOIN [INNER JOIN]
| | hash predicates: store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
| |
| |--03:SCAN HDFS [tpcds.household_demographics]
| | partitions=1/1 files=1 size=148.10KB
| | predicates: (household_demographics.hd_buy_potential = '>10000' OR household_demographics.hd_buy_potential = 'unknown'), household_demographics.hd_vehicle_count > 0, CASE WHEN household_demographics.hd_vehicle_count > 0 THEN household_demographics.hd_dep_count / household_demographics.hd_vehicle_count ELSE NULL END > 1
| |
| 05:HASH JOIN [INNER JOIN]
| | hash predicates: store_sales.ss_store_sk = store.s_store_sk
| |
| |--02:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| | predicates: store.s_county IN ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County')
| |
| 04:HASH JOIN [INNER JOIN]
| | hash predicates: store_sales.ss_sold_date_sk = date_dim.d_date_sk
| |
| |--01:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| |
| 00:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
08:SCAN HDFS [tpcds.customer]
partitions=1/1 files=1 size=12.60MB
section DISTRIBUTEDPLAN of query:
select
c_last_name,
c_first_name,
c_salutation,
c_preferred_cust_flag,
ss_ticket_number,
cnt
from
(select
ss_ticket_number,
ss_customer_sk,
count(*) cnt
from
store_sales,
date_dim,
store,
household_demographics
where
store_sales.ss_sold_date_sk = date_dim.d_date_sk
and store_sales.ss_store_sk = store.s_store_sk
and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
and (household_demographics.hd_buy_potential = '>10000'
or household_demographics.hd_buy_potential = 'unknown')
and household_demographics.hd_vehicle_count > 0
and case when household_demographics.hd_vehicle_count > 0 then household_demographics.hd_dep_count / household_demographics.hd_vehicle_count else null end > 1
and store.s_county in ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County')
group by
ss_ticket_number,
ss_customer_sk
) dj,
customer
where
ss_customer_sk = c_customer_sk
and cnt between 1 and 5
order by
cnt desc
limit 1000
actual result doesn't match expected result:
17:MERGING-EXCHANGE [UNPARTITIONED]
| order by: cnt DESC
| limit: 1000
|
10:TOP-N [LIMIT=1000]
| order by: cnt DESC
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_customer_sk = ss_customer_sk
|
|--16:EXCHANGE [BROADCAST]
| |
| 15:AGGREGATE [FINALIZE]
| | output: count:merge(*)
| | group by: ss_ticket_number, ss_customer_sk
| | having: count(*) >= 1, count(*) <= 5
| |
| 14:EXCHANGE [HASH(ss_ticket_number,ss_customer_sk)]
| |
| 07:AGGREGATE
| | output: count(*)
| | group by: ss_ticket_number, ss_customer_sk
| |
| 06:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: store_sales.ss_sold_date_sk = date_dim.d_date_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| |
| |--13:EXCHANGE [BROADCAST]
| | |
| | 01:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| |
| 05:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
| |
| |--12:EXCHANGE [BROADCAST]
| | |
| | 03:SCAN HDFS [tpcds.household_demographics]
| | partitions=1/1 files=1 size=148.10KB
| | predicates: (household_demographics.hd_buy_potential = '>10000' OR household_demographics.hd_buy_potential = 'unknown'), household_demographics.hd_vehicle_count > 0, CASE WHEN household_demographics.hd_vehicle_count > 0 THEN household_demographics.hd_dep_count / household_demographics.hd_vehicle_count ELSE NULL END > 1
| |
| 04:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: store_sales.ss_store_sk = store.s_store_sk
| |
| |--11:EXCHANGE [BROADCAST]
| | |
| | 02:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| | predicates: store.s_county IN ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County')
| |
| 00:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
08:SCAN HDFS [tpcds.customer]
partitions=1/1 files=1 size=12.60MB
expected:
17:MERGING-EXCHANGE [UNPARTITIONED]
| order by: cnt DESC
| limit: 1000
|
10:TOP-N [LIMIT=1000]
| order by: cnt DESC
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_customer_sk = ss_customer_sk
|
|--16:EXCHANGE [BROADCAST]
| |
| 15:AGGREGATE [FINALIZE]
| | output: count:merge(*)
| | group by: ss_ticket_number, ss_customer_sk
| | having: count(*) >= 1, count(*) <= 5
| |
| 14:EXCHANGE [HASH(ss_ticket_number,ss_customer_sk)]
| |
| 07:AGGREGATE
| | output: count(*)
| | group by: ss_ticket_number, ss_customer_sk
| |
| 06:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
| |
| |--13:EXCHANGE [BROADCAST]
| | |
| | 03:SCAN HDFS [tpcds.household_demographics]
| | partitions=1/1 files=1 size=148.10KB
| | predicates: (household_demographics.hd_buy_potential = '>10000' OR household_demographics.hd_buy_potential = 'unknown'), household_demographics.hd_vehicle_count > 0, CASE WHEN household_demographics.hd_vehicle_count > 0 THEN household_demographics.hd_dep_count / household_demographics.hd_vehicle_count ELSE NULL END > 1
| |
| 05:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: store_sales.ss_store_sk = store.s_store_sk
| |
| |--12:EXCHANGE [BROADCAST]
| | |
| | 02:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| | predicates: store.s_county IN ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County')
| |
| 04:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: store_sales.ss_sold_date_sk = date_dim.d_date_sk
| |
| |--11:EXCHANGE [BROADCAST]
| | |
| | 01:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| |
| 00:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
08:SCAN HDFS [tpcds.customer]
partitions=1/1 files=1 size=12.60MB
section PLAN of query:
select * from (
select a.ca_state state, count(*) cnt
from customer_address a
,customer c
,store_sales s
,date_dim d
,item i
where
a.ca_address_sk = c.c_current_addr_sk
and c.c_customer_sk = s.ss_customer_sk
and s.ss_sold_date_sk = d.d_date_sk
and s.ss_item_sk = i.i_item_sk
and d.d_month_seq =
(select distinct (d_month_seq)
from date_dim
where d_year = 1999
and d_moy = 1
limit 1)
and i.i_current_price > 1.2 *
(select avg(j.i_current_price)
from item j
where j.i_category = i.i_category)
group by a.ca_state
having count(*) >= 10
order by cnt limit 100) as t
actual result doesn't match expected result:
16:TOP-N [LIMIT=100]
| order by: count(*) ASC
|
15:AGGREGATE [FINALIZE]
| output: count(*)
| group by: a.ca_state
| having: count(*) >= 10
|
14:HASH JOIN [LEFT SEMI JOIN]
| hash predicates: i.i_category = j.i_category
| other join predicates: i.i_current_price > 1.2 * avg(j.i_current_price)
|
|--08:AGGREGATE [FINALIZE]
| | output: avg(j.i_current_price)
| | group by: j.i_category
| |
| 07:SCAN HDFS [tpcds.item j]
| partitions=1/1 files=1 size=4.82MB
|
13:HASH JOIN [LEFT SEMI JOIN]
| hash predicates: d.d_month_seq = (d_month_seq)
|
|--06:AGGREGATE [FINALIZE]
| | group by: (d_month_seq)
| | limit: 1
| |
| 05:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1999, d_moy = 1
|
12:HASH JOIN [INNER JOIN]
| hash predicates: s.ss_sold_date_sk = d.d_date_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--03:SCAN HDFS [tpcds.date_dim d]
| partitions=1/1 files=1 size=9.84MB
|
11:HASH JOIN [INNER JOIN]
| hash predicates: c.c_current_addr_sk = a.ca_address_sk
|
|--00:SCAN HDFS [tpcds.customer_address a]
| partitions=1/1 files=1 size=5.25MB
|
10:HASH JOIN [INNER JOIN]
| hash predicates: s.ss_customer_sk = c.c_customer_sk
|
|--01:SCAN HDFS [tpcds.customer c]
| partitions=1/1 files=1 size=12.60MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s.ss_item_sk = i.i_item_sk
|
|--04:SCAN HDFS [tpcds.item i]
| partitions=1/1 files=1 size=4.82MB
|
02:SCAN HDFS [tpcds.store_sales s]
partitions=120/120 files=120 size=21.31MB
expected:
16:TOP-N [LIMIT=100]
| order by: count(*) ASC
|
15:AGGREGATE [FINALIZE]
| output: count(*)
| group by: a.ca_state
| having: count(*) >= 10
|
14:HASH JOIN [LEFT SEMI JOIN]
| hash predicates: i.i_category = j.i_category
| other join predicates: i.i_current_price > 1.2 * avg(j.i_current_price)
|
|--08:AGGREGATE [FINALIZE]
| | output: avg(j.i_current_price)
| | group by: j.i_category
| |
| 07:SCAN HDFS [tpcds.item j]
| partitions=1/1 files=1 size=4.82MB
|
13:HASH JOIN [LEFT SEMI JOIN]
| hash predicates: d.d_month_seq = (d_month_seq)
|
|--06:AGGREGATE [FINALIZE]
| | group by: (d_month_seq)
| | limit: 1
| |
| 05:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1999, d_moy = 1
|
12:HASH JOIN [INNER JOIN]
| hash predicates: s.ss_item_sk = i.i_item_sk
|
|--04:SCAN HDFS [tpcds.item i]
| partitions=1/1 files=1 size=4.82MB
|
11:HASH JOIN [INNER JOIN]
| hash predicates: s.ss_sold_date_sk = d.d_date_sk
|
|--03:SCAN HDFS [tpcds.date_dim d]
| partitions=1/1 files=1 size=9.84MB
|
10:HASH JOIN [INNER JOIN]
| hash predicates: c.c_current_addr_sk = a.ca_address_sk
|
|--00:SCAN HDFS [tpcds.customer_address a]
| partitions=1/1 files=1 size=5.25MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s.ss_customer_sk = c.c_customer_sk
|
|--01:SCAN HDFS [tpcds.customer c]
| partitions=1/1 files=1 size=12.60MB
|
02:SCAN HDFS [tpcds.store_sales s]
partitions=120/120 files=120 size=21.31MB
section DISTRIBUTEDPLAN of query:
select * from (
select a.ca_state state, count(*) cnt
from customer_address a
,customer c
,store_sales s
,date_dim d
,item i
where
a.ca_address_sk = c.c_current_addr_sk
and c.c_customer_sk = s.ss_customer_sk
and s.ss_sold_date_sk = d.d_date_sk
and s.ss_item_sk = i.i_item_sk
and d.d_month_seq =
(select distinct (d_month_seq)
from date_dim
where d_year = 1999
and d_moy = 1
limit 1)
and i.i_current_price > 1.2 *
(select avg(j.i_current_price)
from item j
where j.i_category = i.i_category)
group by a.ca_state
having count(*) >= 10
order by cnt limit 100) as t
actual result doesn't match expected result:
30:MERGING-EXCHANGE [UNPARTITIONED]
| order by: count(*) ASC
| limit: 100
|
16:TOP-N [LIMIT=100]
| order by: count(*) ASC
|
29:AGGREGATE [FINALIZE]
| output: count:merge(*)
| group by: a.ca_state
| having: count(*) >= 10
|
28:EXCHANGE [HASH(a.ca_state)]
|
15:AGGREGATE
| output: count(*)
| group by: a.ca_state
|
14:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
| hash predicates: i.i_category = j.i_category
| other join predicates: i.i_current_price > 1.2 * avg(j.i_current_price)
|
|--27:EXCHANGE [BROADCAST]
| |
| 26:AGGREGATE [FINALIZE]
| | output: avg:merge(j.i_current_price)
| | group by: j.i_category
| |
| 25:EXCHANGE [HASH(j.i_category)]
| |
| 08:AGGREGATE
| | output: avg(j.i_current_price)
| | group by: j.i_category
| |
| 07:SCAN HDFS [tpcds.item j]
| partitions=1/1 files=1 size=4.82MB
|
13:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
| hash predicates: d.d_month_seq = (d_month_seq)
|
|--24:EXCHANGE [BROADCAST]
| |
| 23:EXCHANGE [UNPARTITIONED]
| | limit: 1
| |
| 22:AGGREGATE [FINALIZE]
| | group by: (d_month_seq)
| | limit: 1
| |
| 21:EXCHANGE [HASH((d_month_seq))]
| |
| 06:AGGREGATE
| | group by: (d_month_seq)
| |
| 05:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1999, d_moy = 1
|
12:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s.ss_sold_date_sk = d.d_date_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--20:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpcds.date_dim d]
| partitions=1/1 files=1 size=9.84MB
|
11:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c.c_current_addr_sk = a.ca_address_sk
|
|--19:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpcds.customer_address a]
| partitions=1/1 files=1 size=5.25MB
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s.ss_customer_sk = c.c_customer_sk
|
|--18:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpcds.customer c]
| partitions=1/1 files=1 size=12.60MB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s.ss_item_sk = i.i_item_sk
|
|--17:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpcds.item i]
| partitions=1/1 files=1 size=4.82MB
|
02:SCAN HDFS [tpcds.store_sales s]
partitions=120/120 files=120 size=21.31MB
expected:
30:MERGING-EXCHANGE [UNPARTITIONED]
| order by: count(*) ASC
| limit: 100
|
16:TOP-N [LIMIT=100]
| order by: count(*) ASC
|
29:AGGREGATE [FINALIZE]
| output: count:merge(*)
| group by: a.ca_state
| having: count(*) >= 10
|
28:EXCHANGE [HASH(a.ca_state)]
|
15:AGGREGATE
| output: count(*)
| group by: a.ca_state
|
14:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
| hash predicates: i.i_category = j.i_category
| other join predicates: i.i_current_price > 1.2 * avg(j.i_current_price)
|
|--27:EXCHANGE [BROADCAST]
| |
| 26:AGGREGATE [FINALIZE]
| | output: avg:merge(j.i_current_price)
| | group by: j.i_category
| |
| 25:EXCHANGE [HASH(j.i_category)]
| |
| 08:AGGREGATE
| | output: avg(j.i_current_price)
| | group by: j.i_category
| |
| 07:SCAN HDFS [tpcds.item j]
| partitions=1/1 files=1 size=4.82MB
|
13:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
| hash predicates: d.d_month_seq = (d_month_seq)
|
|--24:EXCHANGE [BROADCAST]
| |
| 23:EXCHANGE [UNPARTITIONED]
| | limit: 1
| |
| 22:AGGREGATE [FINALIZE]
| | group by: (d_month_seq)
| | limit: 1
| |
| 21:EXCHANGE [HASH((d_month_seq))]
| |
| 06:AGGREGATE
| | group by: (d_month_seq)
| |
| 05:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1999, d_moy = 1
|
12:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s.ss_item_sk = i.i_item_sk
|
|--20:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpcds.item i]
| partitions=1/1 files=1 size=4.82MB
|
11:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s.ss_sold_date_sk = d.d_date_sk
|
|--19:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpcds.date_dim d]
| partitions=1/1 files=1 size=9.84MB
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c.c_current_addr_sk = a.ca_address_sk
|
|--18:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpcds.customer_address a]
| partitions=1/1 files=1 size=5.25MB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s.ss_customer_sk = c.c_customer_sk
|
|--17:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpcds.customer c]
| partitions=1/1 files=1 size=12.60MB
|
02:SCAN HDFS [tpcds.store_sales s]
partitions=120/120 files=120 size=21.31MB
section PLAN of query:
with v1 as (
select i_category, i_brand,
s_store_name, s_company_name,
d_year, d_moy,
sum(ss_sales_price) sum_sales,
avg(sum(ss_sales_price)) over
(partition by i_category, i_brand,
s_store_name, s_company_name, d_year)
avg_monthly_sales,
rank() over
(partition by i_category, i_brand,
s_store_name, s_company_name
order by d_year, d_moy) rn
from item, store_sales, date_dim, store
where ss_item_sk = i_item_sk and
ss_sold_date_sk = d_date_sk and
ss_store_sk = s_store_sk and
(
d_year = 2000 or
( d_year = 2000-1 and d_moy =12) or
( d_year = 2000+1 and d_moy =1)
)
group by i_category, i_brand,
s_store_name, s_company_name,
d_year, d_moy),
v2 as(
select v1.i_category, v1.i_brand
,v1.d_year
,v1.avg_monthly_sales
,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
from v1, v1 v1_lag, v1 v1_lead
where v1.i_category = v1_lag.i_category and
v1.i_category = v1_lead.i_category and
v1.i_brand = v1_lag.i_brand and
v1.i_brand = v1_lead.i_brand and
v1.s_store_name = v1_lag.s_store_name and
v1.s_store_name = v1_lead.s_store_name and
v1.s_company_name = v1_lag.s_company_name and
v1.s_company_name = v1_lead.s_company_name and
v1.rn = v1_lag.rn + 1 and
v1.rn = v1_lead.rn - 1)
select * from ( select *
from v2
where d_year = 2000 and
avg_monthly_sales > 0 and
case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
order by sum_sales - avg_monthly_sales, d_year
limit 100
) as v3
actual result doesn't match expected result:
35:TOP-N [LIMIT=100]
| order by: sum_sales - avg_monthly_sales ASC, d_year ASC
|
34:HASH JOIN [INNER JOIN]
| hash predicates: i_category = i_category, i_brand = i_brand, s_store_name = s_store_name, s_company_name = s_company_name, rank() = rank() - 1
|
|--32:ANALYTIC
| | functions: rank()
| | partition by: i_category, i_brand, s_store_name, s_company_name
| | order by: d_year ASC, d_moy ASC
| | window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| |
| 31:SORT
| | order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC, d_moy ASC
| |
| 30:AGGREGATE [FINALIZE]
| | output: sum(ss_sales_price)
| | group by: i_category, i_brand, s_store_name, s_company_name, d_year, d_moy
| |
| 29:HASH JOIN [INNER JOIN]
| | hash predicates: ss_item_sk = i_item_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| |
| |--23:SCAN HDFS [tpcds.item]
| | partitions=1/1 files=1 size=4.82MB
| |
| 28:HASH JOIN [INNER JOIN]
| | hash predicates: ss_store_sk = s_store_sk
| |
| |--26:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| |
| 27:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--25:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: (d_year = 2000 OR (d_year = 2000 - 1 AND d_moy = 12) OR (d_year = 2000 + 1 AND d_moy = 1))
| |
| 24:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
33:HASH JOIN [INNER JOIN]
| hash predicates: i_category = i_category, i_brand = i_brand, s_store_name = s_store_name, s_company_name = s_company_name, rank() + 1 = rank()
|
|--12:SELECT
| | predicates: d_year = 2000, CASE WHEN avg(sum(ss_sales_price)) > 0 THEN abs(sum(ss_sales_price) - avg(sum(ss_sales_price))) / avg(sum(ss_sales_price)) ELSE NULL END > 0.1, avg(sum(ss_sales_price)) > 0
| |
| 11:ANALYTIC
| | functions: avg(sum(ss_sales_price))
| | partition by: i_category, i_brand, s_store_name, s_company_name, d_year
| |
| 10:SORT
| | order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC NULLS FIRST
| |
| 09:ANALYTIC
| | functions: rank()
| | partition by: i_category, i_brand, s_store_name, s_company_name
| | order by: d_year ASC, d_moy ASC
| | window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| |
| 08:SORT
| | order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC, d_moy ASC
| |
| 07:AGGREGATE [FINALIZE]
| | output: sum(ss_sales_price)
| | group by: i_category, i_brand, s_store_name, s_company_name, d_year, d_moy
| |
| 06:HASH JOIN [INNER JOIN]
| | hash predicates: ss_item_sk = i_item_sk
| |
| |--00:SCAN HDFS [tpcds.item]
| | partitions=1/1 files=1 size=4.82MB
| |
| 05:HASH JOIN [INNER JOIN]
| | hash predicates: ss_store_sk = s_store_sk
| |
| |--03:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| |
| 04:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--02:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: (d_year = 2000 OR (d_year = 2000 - 1 AND d_moy = 12) OR (d_year = 2000 + 1 AND d_moy = 1))
| |
| 01:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
22:ANALYTIC
| functions: rank()
| partition by: i_category, i_brand, s_store_name, s_company_name
| order by: d_year ASC, d_moy ASC
| window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|
21:SORT
| order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC, d_moy ASC
|
20:AGGREGATE [FINALIZE]
| output: sum(ss_sales_price)
| group by: i_category, i_brand, s_store_name, s_company_name, d_year, d_moy
|
19:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
|
|--13:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
18:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = s_store_sk
|
|--16:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
|
17:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--15:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: (d_year = 2000 OR (d_year = 2000 - 1 AND d_moy = 12) OR (d_year = 2000 + 1 AND d_moy = 1))
|
14:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
expected:
35:TOP-N [LIMIT=100]
| order by: sum_sales - avg_monthly_sales ASC, d_year ASC
|
34:HASH JOIN [INNER JOIN]
| hash predicates: i_category = i_category, i_brand = i_brand, s_store_name = s_store_name, s_company_name = s_company_name, rank() = rank() - 1
|
|--32:ANALYTIC
| | functions: rank()
| | partition by: i_category, i_brand, s_store_name, s_company_name
| | order by: d_year ASC, d_moy ASC
| | window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| |
| 31:SORT
| | order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC, d_moy ASC
| |
| 30:AGGREGATE [FINALIZE]
| | output: sum(ss_sales_price)
| | group by: i_category, i_brand, s_store_name, s_company_name, d_year, d_moy
| |
| 29:HASH JOIN [INNER JOIN]
| | hash predicates: ss_store_sk = s_store_sk
| |
| |--26:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| |
| 28:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--25:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: (d_year = 2000 OR (d_year = 2000 - 1 AND d_moy = 12) OR (d_year = 2000 + 1 AND d_moy = 1))
| |
| 27:HASH JOIN [INNER JOIN]
| | hash predicates: ss_item_sk = i_item_sk
| |
| |--23:SCAN HDFS [tpcds.item]
| | partitions=1/1 files=1 size=4.82MB
| |
| 24:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
33:HASH JOIN [INNER JOIN]
| hash predicates: i_category = i_category, i_brand = i_brand, s_store_name = s_store_name, s_company_name = s_company_name, rank() + 1 = rank()
|
|--12:SELECT
| | predicates: d_year = 2000, CASE WHEN avg(sum(ss_sales_price)) > 0 THEN abs(sum(ss_sales_price) - avg(sum(ss_sales_price))) / avg(sum(ss_sales_price)) ELSE NULL END > 0.1, avg(sum(ss_sales_price)) > 0
| |
| 11:ANALYTIC
| | functions: avg(sum(ss_sales_price))
| | partition by: i_category, i_brand, s_store_name, s_company_name, d_year
| |
| 10:SORT
| | order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC NULLS FIRST
| |
| 09:ANALYTIC
| | functions: rank()
| | partition by: i_category, i_brand, s_store_name, s_company_name
| | order by: d_year ASC, d_moy ASC
| | window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| |
| 08:SORT
| | order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC, d_moy ASC
| |
| 07:AGGREGATE [FINALIZE]
| | output: sum(ss_sales_price)
| | group by: i_category, i_brand, s_store_name, s_company_name, d_year, d_moy
| |
| 06:HASH JOIN [INNER JOIN]
| | hash predicates: ss_store_sk = s_store_sk
| |
| |--03:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| |
| 05:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--02:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: (d_year = 2000 OR (d_year = 2000 - 1 AND d_moy = 12) OR (d_year = 2000 + 1 AND d_moy = 1))
| |
| 04:HASH JOIN [INNER JOIN]
| | hash predicates: ss_item_sk = i_item_sk
| |
| |--00:SCAN HDFS [tpcds.item]
| | partitions=1/1 files=1 size=4.82MB
| |
| 01:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
22:ANALYTIC
| functions: rank()
| partition by: i_category, i_brand, s_store_name, s_company_name
| order by: d_year ASC, d_moy ASC
| window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|
21:SORT
| order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC, d_moy ASC
|
20:AGGREGATE [FINALIZE]
| output: sum(ss_sales_price)
| group by: i_category, i_brand, s_store_name, s_company_name, d_year, d_moy
|
19:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = s_store_sk
|
|--16:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
|
18:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--15:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: (d_year = 2000 OR (d_year = 2000 - 1 AND d_moy = 12) OR (d_year = 2000 + 1 AND d_moy = 1))
|
17:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
|
|--13:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
14:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
section PLAN of query:
select promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100
from
(select sum(ss_ext_sales_price) promotions
from store_sales
,store
,promotion
,date_dim
,customer
,customer_address
,item
where ss_sold_date_sk = d_date_sk
and ss_store_sk = s_store_sk
and ss_promo_sk = p_promo_sk
and ss_customer_sk= c_customer_sk
and ca_address_sk = c_current_addr_sk
and ss_item_sk = i_item_sk
and ca_gmt_offset = -5
and i_category = 'Books'
and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
and s_gmt_offset = -5
and d_year = 2000
and d_moy = 11) promotional_sales,
(select sum(ss_ext_sales_price) total
from store_sales
,store
,date_dim
,customer
,customer_address
,item
where ss_sold_date_sk = d_date_sk
and ss_store_sk = s_store_sk
and ss_customer_sk= c_customer_sk
and ca_address_sk = c_current_addr_sk
and ss_item_sk = i_item_sk
and ca_gmt_offset = -5
and i_category = 'Books'
and s_gmt_offset = -5
and d_year = 2000
and d_moy = 11) all_sales
order by promotions, total
actual result doesn't match expected result:
27:SORT
| order by: promotions ASC, total ASC
|
26:CROSS JOIN
|
|--25:AGGREGATE [FINALIZE]
| | output: sum(ss_ext_sales_price)
| |
| 24:HASH JOIN [INNER JOIN]
| | hash predicates: c_current_addr_sk = ca_address_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| |
| |--18:SCAN HDFS [tpcds.customer_address]
| | partitions=1/1 files=1 size=5.25MB
| | predicates: ca_gmt_offset = -5
| |
| 23:HASH JOIN [INNER JOIN]
| | hash predicates: ss_customer_sk = c_customer_sk
| |
| |--17:SCAN HDFS [tpcds.customer]
| | partitions=1/1 files=1 size=12.60MB
| |
| 22:HASH JOIN [INNER JOIN]
| | hash predicates: ss_item_sk = i_item_sk
| |
| |--19:SCAN HDFS [tpcds.item]
| | partitions=1/1 files=1 size=4.82MB
| | predicates: i_category = 'Books'
| |
| 21:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--16:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: d_year = 2000, d_moy = 11
| |
| 20:HASH JOIN [INNER JOIN]
| | hash predicates: ss_store_sk = s_store_sk
| |
| |--15:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| | predicates: s_gmt_offset = -5
| |
| 14:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
13:AGGREGATE [FINALIZE]
| output: sum(ss_ext_sales_price)
|
12:HASH JOIN [INNER JOIN]
| hash predicates: c_current_addr_sk = ca_address_sk
|
|--05:SCAN HDFS [tpcds.customer_address]
| partitions=1/1 files=1 size=5.25MB
| predicates: ca_gmt_offset = -5
|
11:HASH JOIN [INNER JOIN]
| hash predicates: ss_customer_sk = c_customer_sk
|
|--04:SCAN HDFS [tpcds.customer]
| partitions=1/1 files=1 size=12.60MB
|
10:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
|
|--06:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
| predicates: i_category = 'Books'
|
09:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--03:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 2000, d_moy = 11
|
08:HASH JOIN [INNER JOIN]
| hash predicates: ss_promo_sk = p_promo_sk
|
|--02:SCAN HDFS [tpcds.promotion]
| partitions=1/1 files=1 size=36.36KB
| predicates: (p_channel_dmail = 'Y' OR p_channel_email = 'Y' OR p_channel_tv = 'Y')
|
07:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = s_store_sk
|
|--01:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
| predicates: s_gmt_offset = -5
|
00:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
expected:
27:SORT
| order by: promotions ASC, total ASC
|
26:CROSS JOIN
|
|--25:AGGREGATE [FINALIZE]
| | output: sum(ss_ext_sales_price)
| |
| 24:HASH JOIN [INNER JOIN]
| | hash predicates: ss_item_sk = i_item_sk
| |
| |--19:SCAN HDFS [tpcds.item]
| | partitions=1/1 files=1 size=4.82MB
| | predicates: i_category = 'Books'
| |
| 23:HASH JOIN [INNER JOIN]
| | hash predicates: c_current_addr_sk = ca_address_sk
| |
| |--18:SCAN HDFS [tpcds.customer_address]
| | partitions=1/1 files=1 size=5.25MB
| | predicates: ca_gmt_offset = -5
| |
| 22:HASH JOIN [INNER JOIN]
| | hash predicates: ss_customer_sk = c_customer_sk
| |
| |--17:SCAN HDFS [tpcds.customer]
| | partitions=1/1 files=1 size=12.60MB
| |
| 21:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--16:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: d_year = 2000, d_moy = 11
| |
| 20:HASH JOIN [INNER JOIN]
| | hash predicates: ss_store_sk = s_store_sk
| |
| |--15:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| | predicates: s_gmt_offset = -5
| |
| 14:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
13:AGGREGATE [FINALIZE]
| output: sum(ss_ext_sales_price)
|
12:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
|
|--06:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
| predicates: i_category = 'Books'
|
11:HASH JOIN [INNER JOIN]
| hash predicates: c_current_addr_sk = ca_address_sk
|
|--05:SCAN HDFS [tpcds.customer_address]
| partitions=1/1 files=1 size=5.25MB
| predicates: ca_gmt_offset = -5
|
10:HASH JOIN [INNER JOIN]
| hash predicates: ss_customer_sk = c_customer_sk
|
|--04:SCAN HDFS [tpcds.customer]
| partitions=1/1 files=1 size=12.60MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--03:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 2000, d_moy = 11
|
08:HASH JOIN [INNER JOIN]
| hash predicates: ss_promo_sk = p_promo_sk
|
|--02:SCAN HDFS [tpcds.promotion]
| partitions=1/1 files=1 size=36.36KB
| predicates: (p_channel_dmail = 'Y' OR p_channel_email = 'Y' OR p_channel_tv = 'Y')
|
07:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = s_store_sk
|
|--01:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
| predicates: s_gmt_offset = -5
|
00:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
Stacktrace
java.lang.AssertionError: section PLAN of query:
select
i_item_id,
avg(ss_quantity) agg1,
avg(ss_list_price) agg2,
avg(ss_coupon_amt) agg3,
avg(ss_sales_price) agg4
from
store_sales,
customer_demographics,
date_dim,
item,
promotion
where
ss_sold_date_sk = d_date_sk
and ss_item_sk = i_item_sk
and ss_cdemo_sk = cd_demo_sk
and ss_promo_sk = p_promo_sk
and cd_gender = 'F'
and cd_marital_status = 'W'
and cd_education_status = 'Primary'
and (p_channel_email = 'N'
or p_channel_event = 'N')
and d_year = 1998
group by
i_item_id
order by
i_item_id
limit 100
actual result doesn't match expected result:
10:TOP-N [LIMIT=100]
| order by: i_item_id ASC
|
09:AGGREGATE [FINALIZE]
| output: avg(ss_quantity), avg(ss_list_price), avg(ss_coupon_amt), avg(ss_sales_price)
| group by: i_item_id
|
08:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--03:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: ss_promo_sk = p_promo_sk
|
|--04:SCAN HDFS [tpcds.promotion]
| partitions=1/1 files=1 size=36.36KB
| predicates: (p_channel_email = 'N' OR p_channel_event = 'N')
|
06:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--02:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1998
|
05:HASH JOIN [INNER JOIN]
| hash predicates: ss_cdemo_sk = cd_demo_sk
|
|--01:SCAN HDFS [tpcds.customer_demographics]
| partitions=1/1 files=1 size=76.92MB
| predicates: cd_gender = 'F', cd_marital_status = 'W', cd_education_status = 'Primary'
|
00:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
expected:
10:TOP-N [LIMIT=100]
| order by: i_item_id ASC
|
09:AGGREGATE [FINALIZE]
| output: avg(ss_quantity), avg(ss_list_price), avg(ss_coupon_amt), avg(ss_sales_price)
| group by: i_item_id
|
08:HASH JOIN [INNER JOIN]
| hash predicates: ss_promo_sk = p_promo_sk
|
|--04:SCAN HDFS [tpcds.promotion]
| partitions=1/1 files=1 size=36.36KB
| predicates: (p_channel_email = 'N' OR p_channel_event = 'N')
|
07:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
|
|--03:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--02:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1998
|
05:HASH JOIN [INNER JOIN]
| hash predicates: ss_cdemo_sk = cd_demo_sk
|
|--01:SCAN HDFS [tpcds.customer_demographics]
| partitions=1/1 files=1 size=76.92MB
| predicates: cd_gender = 'F', cd_marital_status = 'W', cd_education_status = 'Primary'
|
00:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
section DISTRIBUTEDPLAN of query:
select
i_item_id,
avg(ss_quantity) agg1,
avg(ss_list_price) agg2,
avg(ss_coupon_amt) agg3,
avg(ss_sales_price) agg4
from
store_sales,
customer_demographics,
date_dim,
item,
promotion
where
ss_sold_date_sk = d_date_sk
and ss_item_sk = i_item_sk
and ss_cdemo_sk = cd_demo_sk
and ss_promo_sk = p_promo_sk
and cd_gender = 'F'
and cd_marital_status = 'W'
and cd_education_status = 'Primary'
and (p_channel_email = 'N'
or p_channel_event = 'N')
and d_year = 1998
group by
i_item_id
order by
i_item_id
limit 100
actual result doesn't match expected result:
17:MERGING-EXCHANGE [UNPARTITIONED]
| order by: i_item_id ASC
| limit: 100
|
10:TOP-N [LIMIT=100]
| order by: i_item_id ASC
|
16:AGGREGATE [FINALIZE]
| output: avg:merge(ss_quantity), avg:merge(ss_list_price), avg:merge(ss_coupon_amt), avg:merge(ss_sales_price)
| group by: i_item_id
|
15:EXCHANGE [HASH(i_item_id)]
|
09:AGGREGATE
| output: avg(ss_quantity), avg(ss_list_price), avg(ss_coupon_amt), avg(ss_sales_price)
| group by: i_item_id
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_item_sk = i_item_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--14:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_promo_sk = p_promo_sk
|
|--13:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpcds.promotion]
| partitions=1/1 files=1 size=36.36KB
| predicates: (p_channel_email = 'N' OR p_channel_event = 'N')
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--12:EXCHANGE [BROADCAST]
| |
| 02:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1998
|
05:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_cdemo_sk = cd_demo_sk
|
|--11:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpcds.customer_demographics]
| partitions=1/1 files=1 size=76.92MB
| predicates: cd_gender = 'F', cd_marital_status = 'W', cd_education_status = 'Primary'
|
00:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
expected:
17:MERGING-EXCHANGE [UNPARTITIONED]
| order by: i_item_id ASC
| limit: 100
|
10:TOP-N [LIMIT=100]
| order by: i_item_id ASC
|
16:AGGREGATE [FINALIZE]
| output: avg:merge(ss_quantity), avg:merge(ss_list_price), avg:merge(ss_coupon_amt), avg:merge(ss_sales_price)
| group by: i_item_id
|
15:EXCHANGE [HASH(i_item_id)]
|
09:AGGREGATE
| output: avg(ss_quantity), avg(ss_list_price), avg(ss_coupon_amt), avg(ss_sales_price)
| group by: i_item_id
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_promo_sk = p_promo_sk
|
|--14:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpcds.promotion]
| partitions=1/1 files=1 size=36.36KB
| predicates: (p_channel_email = 'N' OR p_channel_event = 'N')
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_item_sk = i_item_sk
|
|--13:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--12:EXCHANGE [BROADCAST]
| |
| 02:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1998
|
05:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_cdemo_sk = cd_demo_sk
|
|--11:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpcds.customer_demographics]
| partitions=1/1 files=1 size=76.92MB
| predicates: cd_gender = 'F', cd_marital_status = 'W', cd_education_status = 'Primary'
|
00:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
section PLAN of query:
select
s_store_name,
i_item_desc,
sc.revenue,
i_current_price,
i_wholesale_cost,
i_brand
from
store,
item,
(select
ss_store_sk,
avg(revenue) as ave
from
(select
ss_store_sk,
ss_item_sk,
sum(ss_sales_price) as revenue
from
store_sales,
date_dim
where
ss_sold_date_sk = d_date_sk
and d_month_seq between 1212 and 1212 + 11
group by
ss_store_sk,
ss_item_sk
) sa
group by
ss_store_sk
) sb,
(select
ss_store_sk,
ss_item_sk,
sum(ss_sales_price) as revenue
from
store_sales,
date_dim
where
ss_sold_date_sk = d_date_sk
and d_month_seq between 1212 and 1212 + 11
group by
ss_store_sk,
ss_item_sk
) sc
where
sb.ss_store_sk = sc.ss_store_sk
and sc.revenue <= 0.1 * sb.ave
and s_store_sk = sc.ss_store_sk
and i_item_sk = sc.ss_item_sk
order by
s_store_name,
i_item_desc
limit 100
actual result doesn't match expected result:
14:TOP-N [LIMIT=100]
| order by: s_store_name ASC, i_item_desc ASC
|
13:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--01:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
12:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = ss_store_sk
| other predicates: sum(ss_sales_price) <= 0.1 * avg(revenue)
|
|--06:AGGREGATE [FINALIZE]
| | output: avg(sum(ss_sales_price))
| | group by: ss_store_sk
| |
| 05:AGGREGATE [FINALIZE]
| | output: sum(ss_sales_price)
| | group by: ss_store_sk, ss_item_sk
| |
| 04:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--03:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
| |
| 02:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
11:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = s_store_sk
|
|--00:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
|
10:AGGREGATE [FINALIZE]
| output: sum(ss_sales_price)
| group by: ss_store_sk, ss_item_sk
|
09:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--08:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
|
07:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
expected:
14:TOP-N [LIMIT=100]
| order by: s_store_name ASC, i_item_desc ASC
|
13:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = ss_store_sk
| other predicates: sum(ss_sales_price) <= 0.1 * avg(revenue)
|
|--06:AGGREGATE [FINALIZE]
| | output: avg(sum(ss_sales_price))
| | group by: ss_store_sk
| |
| 05:AGGREGATE [FINALIZE]
| | output: sum(ss_sales_price)
| | group by: ss_store_sk, ss_item_sk
| |
| 04:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--03:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
| |
| 02:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
12:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
|
|--01:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
11:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = s_store_sk
|
|--00:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
|
10:AGGREGATE [FINALIZE]
| output: sum(ss_sales_price)
| group by: ss_store_sk, ss_item_sk
|
09:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--08:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
|
07:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
section DISTRIBUTEDPLAN of query:
select
s_store_name,
i_item_desc,
sc.revenue,
i_current_price,
i_wholesale_cost,
i_brand
from
store,
item,
(select
ss_store_sk,
avg(revenue) as ave
from
(select
ss_store_sk,
ss_item_sk,
sum(ss_sales_price) as revenue
from
store_sales,
date_dim
where
ss_sold_date_sk = d_date_sk
and d_month_seq between 1212 and 1212 + 11
group by
ss_store_sk,
ss_item_sk
) sa
group by
ss_store_sk
) sb,
(select
ss_store_sk,
ss_item_sk,
sum(ss_sales_price) as revenue
from
store_sales,
date_dim
where
ss_sold_date_sk = d_date_sk
and d_month_seq between 1212 and 1212 + 11
group by
ss_store_sk,
ss_item_sk
) sc
where
sb.ss_store_sk = sc.ss_store_sk
and sc.revenue <= 0.1 * sb.ave
and s_store_sk = sc.ss_store_sk
and i_item_sk = sc.ss_item_sk
order by
s_store_name,
i_item_desc
limit 100
actual result doesn't match expected result:
26:MERGING-EXCHANGE [UNPARTITIONED]
| order by: s_store_name ASC, i_item_desc ASC
| limit: 100
|
14:TOP-N [LIMIT=100]
| order by: s_store_name ASC, i_item_desc ASC
|
13:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_item_sk = i_item_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--25:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
12:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_store_sk = ss_store_sk
| other predicates: sum(ss_sales_price) <= 0.1 * avg(revenue)
|
|--24:EXCHANGE [BROADCAST]
| |
| 23:AGGREGATE [FINALIZE]
| | output: avg:merge(revenue)
| | group by: ss_store_sk
| |
| 22:EXCHANGE [HASH(ss_store_sk)]
| |
| 06:AGGREGATE
| | output: avg(sum(ss_sales_price))
| | group by: ss_store_sk
| |
| 21:AGGREGATE [FINALIZE]
| | output: sum:merge(ss_sales_price)
| | group by: ss_store_sk, ss_item_sk
| |
| 20:EXCHANGE [HASH(ss_store_sk,ss_item_sk)]
| |
| 05:AGGREGATE
| | output: sum(ss_sales_price)
| | group by: ss_store_sk, ss_item_sk
| |
| 04:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--19:EXCHANGE [BROADCAST]
| | |
| | 03:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
| |
| 02:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
11:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_store_sk = s_store_sk
|
|--18:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
|
17:AGGREGATE [FINALIZE]
| output: sum:merge(ss_sales_price)
| group by: ss_store_sk, ss_item_sk
|
16:EXCHANGE [HASH(ss_store_sk,ss_item_sk)]
|
10:AGGREGATE
| output: sum(ss_sales_price)
| group by: ss_store_sk, ss_item_sk
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--15:EXCHANGE [BROADCAST]
| |
| 08:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
|
07:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
expected:
26:MERGING-EXCHANGE [UNPARTITIONED]
| order by: s_store_name ASC, i_item_desc ASC
| limit: 100
|
14:TOP-N [LIMIT=100]
| order by: s_store_name ASC, i_item_desc ASC
|
13:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_store_sk = ss_store_sk
| other predicates: sum(ss_sales_price) <= 0.1 * avg(revenue)
|
|--25:EXCHANGE [BROADCAST]
| |
| 24:AGGREGATE [FINALIZE]
| | output: avg:merge(revenue)
| | group by: ss_store_sk
| |
| 23:EXCHANGE [HASH(ss_store_sk)]
| |
| 06:AGGREGATE
| | output: avg(sum(ss_sales_price))
| | group by: ss_store_sk
| |
| 22:AGGREGATE [FINALIZE]
| | output: sum:merge(ss_sales_price)
| | group by: ss_store_sk, ss_item_sk
| |
| 21:EXCHANGE [HASH(ss_store_sk,ss_item_sk)]
| |
| 05:AGGREGATE
| | output: sum(ss_sales_price)
| | group by: ss_store_sk, ss_item_sk
| |
| 04:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--20:EXCHANGE [BROADCAST]
| | |
| | 03:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
| |
| 02:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
12:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_item_sk = i_item_sk
|
|--19:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
11:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_store_sk = s_store_sk
|
|--18:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
|
17:AGGREGATE [FINALIZE]
| output: sum:merge(ss_sales_price)
| group by: ss_store_sk, ss_item_sk
|
16:EXCHANGE [HASH(ss_store_sk,ss_item_sk)]
|
10:AGGREGATE
| output: sum(ss_sales_price)
| group by: ss_store_sk, ss_item_sk
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--15:EXCHANGE [BROADCAST]
| |
| 08:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_month_seq >= 1212, d_month_seq <= 1212 + 11
|
07:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
section PLAN of query:
select
c_last_name,
c_first_name,
c_salutation,
c_preferred_cust_flag,
ss_ticket_number,
cnt
from
(select
ss_ticket_number,
ss_customer_sk,
count(*) cnt
from
store_sales,
date_dim,
store,
household_demographics
where
store_sales.ss_sold_date_sk = date_dim.d_date_sk
and store_sales.ss_store_sk = store.s_store_sk
and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
and (household_demographics.hd_buy_potential = '>10000'
or household_demographics.hd_buy_potential = 'unknown')
and household_demographics.hd_vehicle_count > 0
and case when household_demographics.hd_vehicle_count > 0 then household_demographics.hd_dep_count / household_demographics.hd_vehicle_count else null end > 1
and store.s_county in ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County')
group by
ss_ticket_number,
ss_customer_sk
) dj,
customer
where
ss_customer_sk = c_customer_sk
and cnt between 1 and 5
order by
cnt desc
limit 1000
actual result doesn't match expected result:
10:TOP-N [LIMIT=1000]
| order by: cnt DESC
|
09:HASH JOIN [INNER JOIN]
| hash predicates: c_customer_sk = ss_customer_sk
|
|--07:AGGREGATE [FINALIZE]
| | output: count(*)
| | group by: ss_ticket_number, ss_customer_sk
| | having: count(*) >= 1, count(*) <= 5
| |
| 06:HASH JOIN [INNER JOIN]
| | hash predicates: store_sales.ss_sold_date_sk = date_dim.d_date_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| |
| |--01:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| |
| 05:HASH JOIN [INNER JOIN]
| | hash predicates: store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
| |
| |--03:SCAN HDFS [tpcds.household_demographics]
| | partitions=1/1 files=1 size=148.10KB
| | predicates: (household_demographics.hd_buy_potential = '>10000' OR household_demographics.hd_buy_potential = 'unknown'), household_demographics.hd_vehicle_count > 0, CASE WHEN household_demographics.hd_vehicle_count > 0 THEN household_demographics.hd_dep_count / household_demographics.hd_vehicle_count ELSE NULL END > 1
| |
| 04:HASH JOIN [INNER JOIN]
| | hash predicates: store_sales.ss_store_sk = store.s_store_sk
| |
| |--02:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| | predicates: store.s_county IN ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County')
| |
| 00:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
08:SCAN HDFS [tpcds.customer]
partitions=1/1 files=1 size=12.60MB
expected:
10:TOP-N [LIMIT=1000]
| order by: cnt DESC
|
09:HASH JOIN [INNER JOIN]
| hash predicates: c_customer_sk = ss_customer_sk
|
|--07:AGGREGATE [FINALIZE]
| | output: count(*)
| | group by: ss_ticket_number, ss_customer_sk
| | having: count(*) >= 1, count(*) <= 5
| |
| 06:HASH JOIN [INNER JOIN]
| | hash predicates: store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
| |
| |--03:SCAN HDFS [tpcds.household_demographics]
| | partitions=1/1 files=1 size=148.10KB
| | predicates: (household_demographics.hd_buy_potential = '>10000' OR household_demographics.hd_buy_potential = 'unknown'), household_demographics.hd_vehicle_count > 0, CASE WHEN household_demographics.hd_vehicle_count > 0 THEN household_demographics.hd_dep_count / household_demographics.hd_vehicle_count ELSE NULL END > 1
| |
| 05:HASH JOIN [INNER JOIN]
| | hash predicates: store_sales.ss_store_sk = store.s_store_sk
| |
| |--02:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| | predicates: store.s_county IN ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County')
| |
| 04:HASH JOIN [INNER JOIN]
| | hash predicates: store_sales.ss_sold_date_sk = date_dim.d_date_sk
| |
| |--01:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| |
| 00:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
08:SCAN HDFS [tpcds.customer]
partitions=1/1 files=1 size=12.60MB
section DISTRIBUTEDPLAN of query:
select
c_last_name,
c_first_name,
c_salutation,
c_preferred_cust_flag,
ss_ticket_number,
cnt
from
(select
ss_ticket_number,
ss_customer_sk,
count(*) cnt
from
store_sales,
date_dim,
store,
household_demographics
where
store_sales.ss_sold_date_sk = date_dim.d_date_sk
and store_sales.ss_store_sk = store.s_store_sk
and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
and (household_demographics.hd_buy_potential = '>10000'
or household_demographics.hd_buy_potential = 'unknown')
and household_demographics.hd_vehicle_count > 0
and case when household_demographics.hd_vehicle_count > 0 then household_demographics.hd_dep_count / household_demographics.hd_vehicle_count else null end > 1
and store.s_county in ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County')
group by
ss_ticket_number,
ss_customer_sk
) dj,
customer
where
ss_customer_sk = c_customer_sk
and cnt between 1 and 5
order by
cnt desc
limit 1000
actual result doesn't match expected result:
17:MERGING-EXCHANGE [UNPARTITIONED]
| order by: cnt DESC
| limit: 1000
|
10:TOP-N [LIMIT=1000]
| order by: cnt DESC
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_customer_sk = ss_customer_sk
|
|--16:EXCHANGE [BROADCAST]
| |
| 15:AGGREGATE [FINALIZE]
| | output: count:merge(*)
| | group by: ss_ticket_number, ss_customer_sk
| | having: count(*) >= 1, count(*) <= 5
| |
| 14:EXCHANGE [HASH(ss_ticket_number,ss_customer_sk)]
| |
| 07:AGGREGATE
| | output: count(*)
| | group by: ss_ticket_number, ss_customer_sk
| |
| 06:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: store_sales.ss_sold_date_sk = date_dim.d_date_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| |
| |--13:EXCHANGE [BROADCAST]
| | |
| | 01:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| |
| 05:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
| |
| |--12:EXCHANGE [BROADCAST]
| | |
| | 03:SCAN HDFS [tpcds.household_demographics]
| | partitions=1/1 files=1 size=148.10KB
| | predicates: (household_demographics.hd_buy_potential = '>10000' OR household_demographics.hd_buy_potential = 'unknown'), household_demographics.hd_vehicle_count > 0, CASE WHEN household_demographics.hd_vehicle_count > 0 THEN household_demographics.hd_dep_count / household_demographics.hd_vehicle_count ELSE NULL END > 1
| |
| 04:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: store_sales.ss_store_sk = store.s_store_sk
| |
| |--11:EXCHANGE [BROADCAST]
| | |
| | 02:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| | predicates: store.s_county IN ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County')
| |
| 00:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
08:SCAN HDFS [tpcds.customer]
partitions=1/1 files=1 size=12.60MB
expected:
17:MERGING-EXCHANGE [UNPARTITIONED]
| order by: cnt DESC
| limit: 1000
|
10:TOP-N [LIMIT=1000]
| order by: cnt DESC
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_customer_sk = ss_customer_sk
|
|--16:EXCHANGE [BROADCAST]
| |
| 15:AGGREGATE [FINALIZE]
| | output: count:merge(*)
| | group by: ss_ticket_number, ss_customer_sk
| | having: count(*) >= 1, count(*) <= 5
| |
| 14:EXCHANGE [HASH(ss_ticket_number,ss_customer_sk)]
| |
| 07:AGGREGATE
| | output: count(*)
| | group by: ss_ticket_number, ss_customer_sk
| |
| 06:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
| |
| |--13:EXCHANGE [BROADCAST]
| | |
| | 03:SCAN HDFS [tpcds.household_demographics]
| | partitions=1/1 files=1 size=148.10KB
| | predicates: (household_demographics.hd_buy_potential = '>10000' OR household_demographics.hd_buy_potential = 'unknown'), household_demographics.hd_vehicle_count > 0, CASE WHEN household_demographics.hd_vehicle_count > 0 THEN household_demographics.hd_dep_count / household_demographics.hd_vehicle_count ELSE NULL END > 1
| |
| 05:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: store_sales.ss_store_sk = store.s_store_sk
| |
| |--12:EXCHANGE [BROADCAST]
| | |
| | 02:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| | predicates: store.s_county IN ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County')
| |
| 04:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: store_sales.ss_sold_date_sk = date_dim.d_date_sk
| |
| |--11:EXCHANGE [BROADCAST]
| | |
| | 01:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| |
| 00:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
08:SCAN HDFS [tpcds.customer]
partitions=1/1 files=1 size=12.60MB
section PLAN of query:
select * from (
select a.ca_state state, count(*) cnt
from customer_address a
,customer c
,store_sales s
,date_dim d
,item i
where
a.ca_address_sk = c.c_current_addr_sk
and c.c_customer_sk = s.ss_customer_sk
and s.ss_sold_date_sk = d.d_date_sk
and s.ss_item_sk = i.i_item_sk
and d.d_month_seq =
(select distinct (d_month_seq)
from date_dim
where d_year = 1999
and d_moy = 1
limit 1)
and i.i_current_price > 1.2 *
(select avg(j.i_current_price)
from item j
where j.i_category = i.i_category)
group by a.ca_state
having count(*) >= 10
order by cnt limit 100) as t
actual result doesn't match expected result:
16:TOP-N [LIMIT=100]
| order by: count(*) ASC
|
15:AGGREGATE [FINALIZE]
| output: count(*)
| group by: a.ca_state
| having: count(*) >= 10
|
14:HASH JOIN [LEFT SEMI JOIN]
| hash predicates: i.i_category = j.i_category
| other join predicates: i.i_current_price > 1.2 * avg(j.i_current_price)
|
|--08:AGGREGATE [FINALIZE]
| | output: avg(j.i_current_price)
| | group by: j.i_category
| |
| 07:SCAN HDFS [tpcds.item j]
| partitions=1/1 files=1 size=4.82MB
|
13:HASH JOIN [LEFT SEMI JOIN]
| hash predicates: d.d_month_seq = (d_month_seq)
|
|--06:AGGREGATE [FINALIZE]
| | group by: (d_month_seq)
| | limit: 1
| |
| 05:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1999, d_moy = 1
|
12:HASH JOIN [INNER JOIN]
| hash predicates: s.ss_sold_date_sk = d.d_date_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--03:SCAN HDFS [tpcds.date_dim d]
| partitions=1/1 files=1 size=9.84MB
|
11:HASH JOIN [INNER JOIN]
| hash predicates: c.c_current_addr_sk = a.ca_address_sk
|
|--00:SCAN HDFS [tpcds.customer_address a]
| partitions=1/1 files=1 size=5.25MB
|
10:HASH JOIN [INNER JOIN]
| hash predicates: s.ss_customer_sk = c.c_customer_sk
|
|--01:SCAN HDFS [tpcds.customer c]
| partitions=1/1 files=1 size=12.60MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s.ss_item_sk = i.i_item_sk
|
|--04:SCAN HDFS [tpcds.item i]
| partitions=1/1 files=1 size=4.82MB
|
02:SCAN HDFS [tpcds.store_sales s]
partitions=120/120 files=120 size=21.31MB
expected:
16:TOP-N [LIMIT=100]
| order by: count(*) ASC
|
15:AGGREGATE [FINALIZE]
| output: count(*)
| group by: a.ca_state
| having: count(*) >= 10
|
14:HASH JOIN [LEFT SEMI JOIN]
| hash predicates: i.i_category = j.i_category
| other join predicates: i.i_current_price > 1.2 * avg(j.i_current_price)
|
|--08:AGGREGATE [FINALIZE]
| | output: avg(j.i_current_price)
| | group by: j.i_category
| |
| 07:SCAN HDFS [tpcds.item j]
| partitions=1/1 files=1 size=4.82MB
|
13:HASH JOIN [LEFT SEMI JOIN]
| hash predicates: d.d_month_seq = (d_month_seq)
|
|--06:AGGREGATE [FINALIZE]
| | group by: (d_month_seq)
| | limit: 1
| |
| 05:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1999, d_moy = 1
|
12:HASH JOIN [INNER JOIN]
| hash predicates: s.ss_item_sk = i.i_item_sk
|
|--04:SCAN HDFS [tpcds.item i]
| partitions=1/1 files=1 size=4.82MB
|
11:HASH JOIN [INNER JOIN]
| hash predicates: s.ss_sold_date_sk = d.d_date_sk
|
|--03:SCAN HDFS [tpcds.date_dim d]
| partitions=1/1 files=1 size=9.84MB
|
10:HASH JOIN [INNER JOIN]
| hash predicates: c.c_current_addr_sk = a.ca_address_sk
|
|--00:SCAN HDFS [tpcds.customer_address a]
| partitions=1/1 files=1 size=5.25MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s.ss_customer_sk = c.c_customer_sk
|
|--01:SCAN HDFS [tpcds.customer c]
| partitions=1/1 files=1 size=12.60MB
|
02:SCAN HDFS [tpcds.store_sales s]
partitions=120/120 files=120 size=21.31MB
section DISTRIBUTEDPLAN of query:
select * from (
select a.ca_state state, count(*) cnt
from customer_address a
,customer c
,store_sales s
,date_dim d
,item i
where
a.ca_address_sk = c.c_current_addr_sk
and c.c_customer_sk = s.ss_customer_sk
and s.ss_sold_date_sk = d.d_date_sk
and s.ss_item_sk = i.i_item_sk
and d.d_month_seq =
(select distinct (d_month_seq)
from date_dim
where d_year = 1999
and d_moy = 1
limit 1)
and i.i_current_price > 1.2 *
(select avg(j.i_current_price)
from item j
where j.i_category = i.i_category)
group by a.ca_state
having count(*) >= 10
order by cnt limit 100) as t
actual result doesn't match expected result:
30:MERGING-EXCHANGE [UNPARTITIONED]
| order by: count(*) ASC
| limit: 100
|
16:TOP-N [LIMIT=100]
| order by: count(*) ASC
|
29:AGGREGATE [FINALIZE]
| output: count:merge(*)
| group by: a.ca_state
| having: count(*) >= 10
|
28:EXCHANGE [HASH(a.ca_state)]
|
15:AGGREGATE
| output: count(*)
| group by: a.ca_state
|
14:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
| hash predicates: i.i_category = j.i_category
| other join predicates: i.i_current_price > 1.2 * avg(j.i_current_price)
|
|--27:EXCHANGE [BROADCAST]
| |
| 26:AGGREGATE [FINALIZE]
| | output: avg:merge(j.i_current_price)
| | group by: j.i_category
| |
| 25:EXCHANGE [HASH(j.i_category)]
| |
| 08:AGGREGATE
| | output: avg(j.i_current_price)
| | group by: j.i_category
| |
| 07:SCAN HDFS [tpcds.item j]
| partitions=1/1 files=1 size=4.82MB
|
13:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
| hash predicates: d.d_month_seq = (d_month_seq)
|
|--24:EXCHANGE [BROADCAST]
| |
| 23:EXCHANGE [UNPARTITIONED]
| | limit: 1
| |
| 22:AGGREGATE [FINALIZE]
| | group by: (d_month_seq)
| | limit: 1
| |
| 21:EXCHANGE [HASH((d_month_seq))]
| |
| 06:AGGREGATE
| | group by: (d_month_seq)
| |
| 05:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1999, d_moy = 1
|
12:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s.ss_sold_date_sk = d.d_date_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--20:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpcds.date_dim d]
| partitions=1/1 files=1 size=9.84MB
|
11:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c.c_current_addr_sk = a.ca_address_sk
|
|--19:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpcds.customer_address a]
| partitions=1/1 files=1 size=5.25MB
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s.ss_customer_sk = c.c_customer_sk
|
|--18:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpcds.customer c]
| partitions=1/1 files=1 size=12.60MB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s.ss_item_sk = i.i_item_sk
|
|--17:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpcds.item i]
| partitions=1/1 files=1 size=4.82MB
|
02:SCAN HDFS [tpcds.store_sales s]
partitions=120/120 files=120 size=21.31MB
expected:
30:MERGING-EXCHANGE [UNPARTITIONED]
| order by: count(*) ASC
| limit: 100
|
16:TOP-N [LIMIT=100]
| order by: count(*) ASC
|
29:AGGREGATE [FINALIZE]
| output: count:merge(*)
| group by: a.ca_state
| having: count(*) >= 10
|
28:EXCHANGE [HASH(a.ca_state)]
|
15:AGGREGATE
| output: count(*)
| group by: a.ca_state
|
14:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
| hash predicates: i.i_category = j.i_category
| other join predicates: i.i_current_price > 1.2 * avg(j.i_current_price)
|
|--27:EXCHANGE [BROADCAST]
| |
| 26:AGGREGATE [FINALIZE]
| | output: avg:merge(j.i_current_price)
| | group by: j.i_category
| |
| 25:EXCHANGE [HASH(j.i_category)]
| |
| 08:AGGREGATE
| | output: avg(j.i_current_price)
| | group by: j.i_category
| |
| 07:SCAN HDFS [tpcds.item j]
| partitions=1/1 files=1 size=4.82MB
|
13:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
| hash predicates: d.d_month_seq = (d_month_seq)
|
|--24:EXCHANGE [BROADCAST]
| |
| 23:EXCHANGE [UNPARTITIONED]
| | limit: 1
| |
| 22:AGGREGATE [FINALIZE]
| | group by: (d_month_seq)
| | limit: 1
| |
| 21:EXCHANGE [HASH((d_month_seq))]
| |
| 06:AGGREGATE
| | group by: (d_month_seq)
| |
| 05:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 1999, d_moy = 1
|
12:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s.ss_item_sk = i.i_item_sk
|
|--20:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpcds.item i]
| partitions=1/1 files=1 size=4.82MB
|
11:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s.ss_sold_date_sk = d.d_date_sk
|
|--19:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpcds.date_dim d]
| partitions=1/1 files=1 size=9.84MB
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c.c_current_addr_sk = a.ca_address_sk
|
|--18:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpcds.customer_address a]
| partitions=1/1 files=1 size=5.25MB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s.ss_customer_sk = c.c_customer_sk
|
|--17:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpcds.customer c]
| partitions=1/1 files=1 size=12.60MB
|
02:SCAN HDFS [tpcds.store_sales s]
partitions=120/120 files=120 size=21.31MB
section PLAN of query:
with v1 as (
select i_category, i_brand,
s_store_name, s_company_name,
d_year, d_moy,
sum(ss_sales_price) sum_sales,
avg(sum(ss_sales_price)) over
(partition by i_category, i_brand,
s_store_name, s_company_name, d_year)
avg_monthly_sales,
rank() over
(partition by i_category, i_brand,
s_store_name, s_company_name
order by d_year, d_moy) rn
from item, store_sales, date_dim, store
where ss_item_sk = i_item_sk and
ss_sold_date_sk = d_date_sk and
ss_store_sk = s_store_sk and
(
d_year = 2000 or
( d_year = 2000-1 and d_moy =12) or
( d_year = 2000+1 and d_moy =1)
)
group by i_category, i_brand,
s_store_name, s_company_name,
d_year, d_moy),
v2 as(
select v1.i_category, v1.i_brand
,v1.d_year
,v1.avg_monthly_sales
,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
from v1, v1 v1_lag, v1 v1_lead
where v1.i_category = v1_lag.i_category and
v1.i_category = v1_lead.i_category and
v1.i_brand = v1_lag.i_brand and
v1.i_brand = v1_lead.i_brand and
v1.s_store_name = v1_lag.s_store_name and
v1.s_store_name = v1_lead.s_store_name and
v1.s_company_name = v1_lag.s_company_name and
v1.s_company_name = v1_lead.s_company_name and
v1.rn = v1_lag.rn + 1 and
v1.rn = v1_lead.rn - 1)
select * from ( select *
from v2
where d_year = 2000 and
avg_monthly_sales > 0 and
case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
order by sum_sales - avg_monthly_sales, d_year
limit 100
) as v3
actual result doesn't match expected result:
35:TOP-N [LIMIT=100]
| order by: sum_sales - avg_monthly_sales ASC, d_year ASC
|
34:HASH JOIN [INNER JOIN]
| hash predicates: i_category = i_category, i_brand = i_brand, s_store_name = s_store_name, s_company_name = s_company_name, rank() = rank() - 1
|
|--32:ANALYTIC
| | functions: rank()
| | partition by: i_category, i_brand, s_store_name, s_company_name
| | order by: d_year ASC, d_moy ASC
| | window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| |
| 31:SORT
| | order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC, d_moy ASC
| |
| 30:AGGREGATE [FINALIZE]
| | output: sum(ss_sales_price)
| | group by: i_category, i_brand, s_store_name, s_company_name, d_year, d_moy
| |
| 29:HASH JOIN [INNER JOIN]
| | hash predicates: ss_item_sk = i_item_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| |
| |--23:SCAN HDFS [tpcds.item]
| | partitions=1/1 files=1 size=4.82MB
| |
| 28:HASH JOIN [INNER JOIN]
| | hash predicates: ss_store_sk = s_store_sk
| |
| |--26:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| |
| 27:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--25:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: (d_year = 2000 OR (d_year = 2000 - 1 AND d_moy = 12) OR (d_year = 2000 + 1 AND d_moy = 1))
| |
| 24:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
33:HASH JOIN [INNER JOIN]
| hash predicates: i_category = i_category, i_brand = i_brand, s_store_name = s_store_name, s_company_name = s_company_name, rank() + 1 = rank()
|
|--12:SELECT
| | predicates: d_year = 2000, CASE WHEN avg(sum(ss_sales_price)) > 0 THEN abs(sum(ss_sales_price) - avg(sum(ss_sales_price))) / avg(sum(ss_sales_price)) ELSE NULL END > 0.1, avg(sum(ss_sales_price)) > 0
| |
| 11:ANALYTIC
| | functions: avg(sum(ss_sales_price))
| | partition by: i_category, i_brand, s_store_name, s_company_name, d_year
| |
| 10:SORT
| | order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC NULLS FIRST
| |
| 09:ANALYTIC
| | functions: rank()
| | partition by: i_category, i_brand, s_store_name, s_company_name
| | order by: d_year ASC, d_moy ASC
| | window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| |
| 08:SORT
| | order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC, d_moy ASC
| |
| 07:AGGREGATE [FINALIZE]
| | output: sum(ss_sales_price)
| | group by: i_category, i_brand, s_store_name, s_company_name, d_year, d_moy
| |
| 06:HASH JOIN [INNER JOIN]
| | hash predicates: ss_item_sk = i_item_sk
| |
| |--00:SCAN HDFS [tpcds.item]
| | partitions=1/1 files=1 size=4.82MB
| |
| 05:HASH JOIN [INNER JOIN]
| | hash predicates: ss_store_sk = s_store_sk
| |
| |--03:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| |
| 04:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--02:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: (d_year = 2000 OR (d_year = 2000 - 1 AND d_moy = 12) OR (d_year = 2000 + 1 AND d_moy = 1))
| |
| 01:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
22:ANALYTIC
| functions: rank()
| partition by: i_category, i_brand, s_store_name, s_company_name
| order by: d_year ASC, d_moy ASC
| window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|
21:SORT
| order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC, d_moy ASC
|
20:AGGREGATE [FINALIZE]
| output: sum(ss_sales_price)
| group by: i_category, i_brand, s_store_name, s_company_name, d_year, d_moy
|
19:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
|
|--13:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
18:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = s_store_sk
|
|--16:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
|
17:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--15:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: (d_year = 2000 OR (d_year = 2000 - 1 AND d_moy = 12) OR (d_year = 2000 + 1 AND d_moy = 1))
|
14:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
expected:
35:TOP-N [LIMIT=100]
| order by: sum_sales - avg_monthly_sales ASC, d_year ASC
|
34:HASH JOIN [INNER JOIN]
| hash predicates: i_category = i_category, i_brand = i_brand, s_store_name = s_store_name, s_company_name = s_company_name, rank() = rank() - 1
|
|--32:ANALYTIC
| | functions: rank()
| | partition by: i_category, i_brand, s_store_name, s_company_name
| | order by: d_year ASC, d_moy ASC
| | window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| |
| 31:SORT
| | order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC, d_moy ASC
| |
| 30:AGGREGATE [FINALIZE]
| | output: sum(ss_sales_price)
| | group by: i_category, i_brand, s_store_name, s_company_name, d_year, d_moy
| |
| 29:HASH JOIN [INNER JOIN]
| | hash predicates: ss_store_sk = s_store_sk
| |
| |--26:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| |
| 28:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--25:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: (d_year = 2000 OR (d_year = 2000 - 1 AND d_moy = 12) OR (d_year = 2000 + 1 AND d_moy = 1))
| |
| 27:HASH JOIN [INNER JOIN]
| | hash predicates: ss_item_sk = i_item_sk
| |
| |--23:SCAN HDFS [tpcds.item]
| | partitions=1/1 files=1 size=4.82MB
| |
| 24:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
33:HASH JOIN [INNER JOIN]
| hash predicates: i_category = i_category, i_brand = i_brand, s_store_name = s_store_name, s_company_name = s_company_name, rank() + 1 = rank()
|
|--12:SELECT
| | predicates: d_year = 2000, CASE WHEN avg(sum(ss_sales_price)) > 0 THEN abs(sum(ss_sales_price) - avg(sum(ss_sales_price))) / avg(sum(ss_sales_price)) ELSE NULL END > 0.1, avg(sum(ss_sales_price)) > 0
| |
| 11:ANALYTIC
| | functions: avg(sum(ss_sales_price))
| | partition by: i_category, i_brand, s_store_name, s_company_name, d_year
| |
| 10:SORT
| | order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC NULLS FIRST
| |
| 09:ANALYTIC
| | functions: rank()
| | partition by: i_category, i_brand, s_store_name, s_company_name
| | order by: d_year ASC, d_moy ASC
| | window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| |
| 08:SORT
| | order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC, d_moy ASC
| |
| 07:AGGREGATE [FINALIZE]
| | output: sum(ss_sales_price)
| | group by: i_category, i_brand, s_store_name, s_company_name, d_year, d_moy
| |
| 06:HASH JOIN [INNER JOIN]
| | hash predicates: ss_store_sk = s_store_sk
| |
| |--03:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| |
| 05:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--02:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: (d_year = 2000 OR (d_year = 2000 - 1 AND d_moy = 12) OR (d_year = 2000 + 1 AND d_moy = 1))
| |
| 04:HASH JOIN [INNER JOIN]
| | hash predicates: ss_item_sk = i_item_sk
| |
| |--00:SCAN HDFS [tpcds.item]
| | partitions=1/1 files=1 size=4.82MB
| |
| 01:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
22:ANALYTIC
| functions: rank()
| partition by: i_category, i_brand, s_store_name, s_company_name
| order by: d_year ASC, d_moy ASC
| window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|
21:SORT
| order by: i_category ASC NULLS FIRST, i_brand ASC NULLS FIRST, s_store_name ASC NULLS FIRST, s_company_name ASC NULLS FIRST, d_year ASC, d_moy ASC
|
20:AGGREGATE [FINALIZE]
| output: sum(ss_sales_price)
| group by: i_category, i_brand, s_store_name, s_company_name, d_year, d_moy
|
19:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = s_store_sk
|
|--16:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
|
18:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--15:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: (d_year = 2000 OR (d_year = 2000 - 1 AND d_moy = 12) OR (d_year = 2000 + 1 AND d_moy = 1))
|
17:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
|
|--13:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
|
14:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
section PLAN of query:
select promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100
from
(select sum(ss_ext_sales_price) promotions
from store_sales
,store
,promotion
,date_dim
,customer
,customer_address
,item
where ss_sold_date_sk = d_date_sk
and ss_store_sk = s_store_sk
and ss_promo_sk = p_promo_sk
and ss_customer_sk= c_customer_sk
and ca_address_sk = c_current_addr_sk
and ss_item_sk = i_item_sk
and ca_gmt_offset = -5
and i_category = 'Books'
and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
and s_gmt_offset = -5
and d_year = 2000
and d_moy = 11) promotional_sales,
(select sum(ss_ext_sales_price) total
from store_sales
,store
,date_dim
,customer
,customer_address
,item
where ss_sold_date_sk = d_date_sk
and ss_store_sk = s_store_sk
and ss_customer_sk= c_customer_sk
and ca_address_sk = c_current_addr_sk
and ss_item_sk = i_item_sk
and ca_gmt_offset = -5
and i_category = 'Books'
and s_gmt_offset = -5
and d_year = 2000
and d_moy = 11) all_sales
order by promotions, total
actual result doesn't match expected result:
27:SORT
| order by: promotions ASC, total ASC
|
26:CROSS JOIN
|
|--25:AGGREGATE [FINALIZE]
| | output: sum(ss_ext_sales_price)
| |
| 24:HASH JOIN [INNER JOIN]
| | hash predicates: c_current_addr_sk = ca_address_sk
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| |
| |--18:SCAN HDFS [tpcds.customer_address]
| | partitions=1/1 files=1 size=5.25MB
| | predicates: ca_gmt_offset = -5
| |
| 23:HASH JOIN [INNER JOIN]
| | hash predicates: ss_customer_sk = c_customer_sk
| |
| |--17:SCAN HDFS [tpcds.customer]
| | partitions=1/1 files=1 size=12.60MB
| |
| 22:HASH JOIN [INNER JOIN]
| | hash predicates: ss_item_sk = i_item_sk
| |
| |--19:SCAN HDFS [tpcds.item]
| | partitions=1/1 files=1 size=4.82MB
| | predicates: i_category = 'Books'
| |
| 21:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--16:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: d_year = 2000, d_moy = 11
| |
| 20:HASH JOIN [INNER JOIN]
| | hash predicates: ss_store_sk = s_store_sk
| |
| |--15:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| | predicates: s_gmt_offset = -5
| |
| 14:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
13:AGGREGATE [FINALIZE]
| output: sum(ss_ext_sales_price)
|
12:HASH JOIN [INNER JOIN]
| hash predicates: c_current_addr_sk = ca_address_sk
|
|--05:SCAN HDFS [tpcds.customer_address]
| partitions=1/1 files=1 size=5.25MB
| predicates: ca_gmt_offset = -5
|
11:HASH JOIN [INNER JOIN]
| hash predicates: ss_customer_sk = c_customer_sk
|
|--04:SCAN HDFS [tpcds.customer]
| partitions=1/1 files=1 size=12.60MB
|
10:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
|
|--06:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
| predicates: i_category = 'Books'
|
09:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--03:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 2000, d_moy = 11
|
08:HASH JOIN [INNER JOIN]
| hash predicates: ss_promo_sk = p_promo_sk
|
|--02:SCAN HDFS [tpcds.promotion]
| partitions=1/1 files=1 size=36.36KB
| predicates: (p_channel_dmail = 'Y' OR p_channel_email = 'Y' OR p_channel_tv = 'Y')
|
07:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = s_store_sk
|
|--01:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
| predicates: s_gmt_offset = -5
|
00:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
expected:
27:SORT
| order by: promotions ASC, total ASC
|
26:CROSS JOIN
|
|--25:AGGREGATE [FINALIZE]
| | output: sum(ss_ext_sales_price)
| |
| 24:HASH JOIN [INNER JOIN]
| | hash predicates: ss_item_sk = i_item_sk
| |
| |--19:SCAN HDFS [tpcds.item]
| | partitions=1/1 files=1 size=4.82MB
| | predicates: i_category = 'Books'
| |
| 23:HASH JOIN [INNER JOIN]
| | hash predicates: c_current_addr_sk = ca_address_sk
| |
| |--18:SCAN HDFS [tpcds.customer_address]
| | partitions=1/1 files=1 size=5.25MB
| | predicates: ca_gmt_offset = -5
| |
| 22:HASH JOIN [INNER JOIN]
| | hash predicates: ss_customer_sk = c_customer_sk
| |
| |--17:SCAN HDFS [tpcds.customer]
| | partitions=1/1 files=1 size=12.60MB
| |
| 21:HASH JOIN [INNER JOIN]
| | hash predicates: ss_sold_date_sk = d_date_sk
| |
| |--16:SCAN HDFS [tpcds.date_dim]
| | partitions=1/1 files=1 size=9.84MB
| | predicates: d_year = 2000, d_moy = 11
| |
| 20:HASH JOIN [INNER JOIN]
| | hash predicates: ss_store_sk = s_store_sk
| |
| |--15:SCAN HDFS [tpcds.store]
| | partitions=1/1 files=1 size=3.08KB
| | predicates: s_gmt_offset = -5
| |
| 14:SCAN HDFS [tpcds.store_sales]
| partitions=120/120 files=120 size=21.31MB
|
13:AGGREGATE [FINALIZE]
| output: sum(ss_ext_sales_price)
|
12:HASH JOIN [INNER JOIN]
| hash predicates: ss_item_sk = i_item_sk
|
|--06:SCAN HDFS [tpcds.item]
| partitions=1/1 files=1 size=4.82MB
| predicates: i_category = 'Books'
|
11:HASH JOIN [INNER JOIN]
| hash predicates: c_current_addr_sk = ca_address_sk
|
|--05:SCAN HDFS [tpcds.customer_address]
| partitions=1/1 files=1 size=5.25MB
| predicates: ca_gmt_offset = -5
|
10:HASH JOIN [INNER JOIN]
| hash predicates: ss_customer_sk = c_customer_sk
|
|--04:SCAN HDFS [tpcds.customer]
| partitions=1/1 files=1 size=12.60MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: ss_sold_date_sk = d_date_sk
|
|--03:SCAN HDFS [tpcds.date_dim]
| partitions=1/1 files=1 size=9.84MB
| predicates: d_year = 2000, d_moy = 11
|
08:HASH JOIN [INNER JOIN]
| hash predicates: ss_promo_sk = p_promo_sk
|
|--02:SCAN HDFS [tpcds.promotion]
| partitions=1/1 files=1 size=36.36KB
| predicates: (p_channel_dmail = 'Y' OR p_channel_email = 'Y' OR p_channel_tv = 'Y')
|
07:HASH JOIN [INNER JOIN]
| hash predicates: ss_store_sk = s_store_sk
|
|--01:SCAN HDFS [tpcds.store]
| partitions=1/1 files=1 size=3.08KB
| predicates: s_gmt_offset = -5
|
00:SCAN HDFS [tpcds.store_sales]
partitions=120/120 files=120 size=21.31MB
at org.junit.Assert.fail(Assert.java:88)
at com.cloudera.impala.planner.PlannerTestBase.runPlannerTestFile(PlannerTestBase.java:643)
at com.cloudera.impala.planner.PlannerTestBase.runPlannerTestFile(PlannerTestBase.java:652)
at com.cloudera.impala.planner.PlannerTest.testTpcds(PlannerTest.java:169)
Raw
com.cloudera.impala.planner.PlannerTest.testTpch
com.cloudera.impala.planner.PlannerTest.testTpch
Failing for the past 1 build (Since Failed#594 )
Took 1.1 sec.
add description
Error Message
section PLAN of query:
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
tpch.customer,
tpch.orders,
tpch.lineitem,
tpch.supplier,
tpch.nation,
tpch.region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= '1994-01-01'
and o_orderdate < '1995-01-01'
group by
n_name
order by
revenue desc
actual result doesn't match expected result:
12:SORT
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--03:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: n_regionkey = r_regionkey
|
|--05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
08:HASH JOIN [INNER JOIN]
| hash predicates: tpch.customer.c_nationkey = tpch.nation.n_nationkey
|
|--04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--01:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
expected:
12:SORT
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN]
| hash predicates: n_regionkey = r_regionkey
|
|--05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
|
|--04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
|
|--03:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--01:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
section DISTRIBUTEDPLAN of query:
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
tpch.customer,
tpch.orders,
tpch.lineitem,
tpch.supplier,
tpch.nation,
tpch.region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= '1994-01-01'
and o_orderdate < '1995-01-01'
group by
n_name
order by
revenue desc
actual result doesn't match expected result:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
|
12:SORT
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: n_name
|
18:EXCHANGE [HASH(n_name)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--17:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n_regionkey = r_regionkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: tpch.customer.c_nationkey = tpch.nation.n_nationkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
expected:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
|
12:SORT
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: n_name
|
18:EXCHANGE [HASH(n_name)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n_regionkey = r_regionkey
|
|--17:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
section PLAN of query:
select
supp_nation,
cust_nation,
l_year,
sum(volume) as revenue
from (
select
n1.n_name as supp_nation,
n2.n_name as cust_nation,
year(l_shipdate) as l_year,
l_extendedprice * (1 - l_discount) as volume
from
tpch.supplier,
tpch.lineitem,
tpch.orders,
tpch.customer,
tpch.nation n1,
tpch.nation n2
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
(n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
)
and l_shipdate between '1995-01-01' and '1996-12-31'
) as shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year
actual result doesn't match expected result:
12:SORT
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n1.n_name, n2.n_name, year(l_shipdate)
|
10:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n1.n_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| other predicates: ((n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY') OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE'))
|
|--04:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey
|
|--00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: c_nationkey = n2.n_nationkey
|
|--05:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--03:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: o_orderkey = l_orderkey
|
|--01:SCAN HDFS [tpch.lineitem]
| partitions=1/1 files=1 size=718.94MB
| predicates: l_shipdate >= '1995-01-01', l_shipdate <= '1996-12-31'
|
02:SCAN HDFS [tpch.orders]
partitions=1/1 files=1 size=162.56MB
expected:
12:SORT
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n1.n_name, n2.n_name, year(l_shipdate)
|
10:HASH JOIN [INNER JOIN]
| hash predicates: c_nationkey = n2.n_nationkey
| other predicates: ((n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY') OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE'))
|
|--05:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n1.n_nationkey
|
|--04:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--03:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey
|
|--00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: o_orderkey = l_orderkey
|
|--01:SCAN HDFS [tpch.lineitem]
| partitions=1/1 files=1 size=718.94MB
| predicates: l_shipdate >= '1995-01-01', l_shipdate <= '1996-12-31'
|
02:SCAN HDFS [tpch.orders]
partitions=1/1 files=1 size=162.56MB
section DISTRIBUTEDPLAN of query:
select
supp_nation,
cust_nation,
l_year,
sum(volume) as revenue
from (
select
n1.n_name as supp_nation,
n2.n_name as cust_nation,
year(l_shipdate) as l_year,
l_extendedprice * (1 - l_discount) as volume
from
tpch.supplier,
tpch.lineitem,
tpch.orders,
tpch.customer,
tpch.nation n1,
tpch.nation n2
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
(n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
)
and l_shipdate between '1995-01-01' and '1996-12-31'
) as shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year
actual result doesn't match expected result:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
|
12:SORT
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(volume)
| group by: supp_nation, cust_nation, l_year
|
18:EXCHANGE [HASH(supp_nation,cust_nation,l_year)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n1.n_name, n2.n_name, year(l_shipdate)
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n1.n_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| other predicates: ((n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY') OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE'))
|
|--17:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = n2.n_nationkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_orderkey = l_orderkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.lineitem]
| partitions=1/1 files=1 size=718.94MB
| predicates: l_shipdate >= '1995-01-01', l_shipdate <= '1996-12-31'
|
02:SCAN HDFS [tpch.orders]
partitions=1/1 files=1 size=162.56MB
expected:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
|
12:SORT
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(volume)
| group by: supp_nation, cust_nation, l_year
|
18:EXCHANGE [HASH(supp_nation,cust_nation,l_year)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n1.n_name, n2.n_name, year(l_shipdate)
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = n2.n_nationkey
| other predicates: ((n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY') OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE'))
|
|--17:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n1.n_nationkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_orderkey = l_orderkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.lineitem]
| partitions=1/1 files=1 size=718.94MB
| predicates: l_shipdate >= '1995-01-01', l_shipdate <= '1996-12-31'
|
02:SCAN HDFS [tpch.orders]
partitions=1/1 files=1 size=162.56MB
section PLAN of query:
select
o_year,
sum(case
when nation = 'BRAZIL'
then volume
else 0
end) / sum(volume) as mkt_share
from (
select
year(o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
tpch.part,
tpch.supplier,
tpch.lineitem,
tpch.orders,
tpch.customer,
tpch.nation n1,
tpch.nation n2,
tpch.region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'AMERICA'
and s_nationkey = n2.n_nationkey
and o_orderdate between '1995-01-01' and '1996-12-31'
and p_type = 'ECONOMY ANODIZED STEEL'
) as all_nations
group by
o_year
order by
o_year
actual result doesn't match expected result:
16:SORT
| order by: o_year ASC
|
15:AGGREGATE [FINALIZE]
| output: sum(CASE WHEN n2.n_name = 'BRAZIL' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum(l_extendedprice * (1 - l_discount))
| group by: year(o_orderdate)
|
14:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n2.n_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--06:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
13:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey
|
|--01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
12:HASH JOIN [INNER JOIN]
| hash predicates: n1.n_regionkey = r_regionkey
|
|--07:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'AMERICA'
|
11:HASH JOIN [INNER JOIN]
| hash predicates: c_nationkey = n1.n_nationkey
|
|--05:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
10:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--04:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--03:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1995-01-01', o_orderdate <= '1996-12-31'
|
08:HASH JOIN [INNER JOIN]
| hash predicates: l_partkey = p_partkey
|
|--00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_type = 'ECONOMY ANODIZED STEEL'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
expected:
16:SORT
| order by: o_year ASC
|
15:AGGREGATE [FINALIZE]
| output: sum(CASE WHEN n2.n_name = 'BRAZIL' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum(l_extendedprice * (1 - l_discount))
| group by: year(o_orderdate)
|
14:HASH JOIN [INNER JOIN]
| hash predicates: n1.n_regionkey = r_regionkey
|
|--07:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'AMERICA'
|
13:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n2.n_nationkey
|
|--06:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
12:HASH JOIN [INNER JOIN]
| hash predicates: c_nationkey = n1.n_nationkey
|
|--05:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
11:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--04:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
10:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--03:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1995-01-01', o_orderdate <= '1996-12-31'
|
09:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey
|
|--01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: l_partkey = p_partkey
|
|--00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_type = 'ECONOMY ANODIZED STEEL'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
section DISTRIBUTEDPLAN of query:
select
o_year,
sum(case
when nation = 'BRAZIL'
then volume
else 0
end) / sum(volume) as mkt_share
from (
select
year(o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
tpch.part,
tpch.supplier,
tpch.lineitem,
tpch.orders,
tpch.customer,
tpch.nation n1,
tpch.nation n2,
tpch.region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'AMERICA'
and s_nationkey = n2.n_nationkey
and o_orderdate between '1995-01-01' and '1996-12-31'
and p_type = 'ECONOMY ANODIZED STEEL'
) as all_nations
group by
o_year
order by
o_year
actual result doesn't match expected result:
26:MERGING-EXCHANGE [UNPARTITIONED]
| order by: o_year ASC
|
16:SORT
| order by: o_year ASC
|
25:AGGREGATE [FINALIZE]
| output: sum:merge(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0 END), sum:merge(volume)
| group by: o_year
|
24:EXCHANGE [HASH(o_year)]
|
15:AGGREGATE
| output: sum(CASE WHEN n2.n_name = 'BRAZIL' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum(l_extendedprice * (1 - l_discount))
| group by: year(o_orderdate)
|
14:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n2.n_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--23:EXCHANGE [BROADCAST]
| |
| 06:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
13:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
|
|--22:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
12:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n1.n_regionkey = r_regionkey
|
|--21:EXCHANGE [BROADCAST]
| |
| 07:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'AMERICA'
|
11:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = n1.n_nationkey
|
|--20:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--19:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--18:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1995-01-01', o_orderdate <= '1996-12-31'
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_partkey = p_partkey
|
|--17:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_type = 'ECONOMY ANODIZED STEEL'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
expected:
26:MERGING-EXCHANGE [UNPARTITIONED]
| order by: o_year ASC
|
16:SORT
| order by: o_year ASC
|
25:AGGREGATE [FINALIZE]
| output: sum:merge(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0 END), sum:merge(volume)
| group by: o_year
|
24:EXCHANGE [HASH(o_year)]
|
15:AGGREGATE
| output: sum(CASE WHEN n2.n_name = 'BRAZIL' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum(l_extendedprice * (1 - l_discount))
| group by: year(o_orderdate)
|
14:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n1.n_regionkey = r_regionkey
|
|--23:EXCHANGE [BROADCAST]
| |
| 07:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'AMERICA'
|
13:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n2.n_nationkey
|
|--22:EXCHANGE [BROADCAST]
| |
| 06:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
12:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = n1.n_nationkey
|
|--21:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
11:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--20:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--19:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1995-01-01', o_orderdate <= '1996-12-31'
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
|
|--18:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_partkey = p_partkey
|
|--17:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_type = 'ECONOMY ANODIZED STEEL'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
section PLAN of query:
select
nation,
o_year,
sum(amount) as sum_profit
from(
select
n_name as nation,
year(o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
tpch.part,
tpch.supplier,
tpch.lineitem,
tpch.partsupp,
tpch.orders,
tpch.nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%green%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc
actual result doesn't match expected result:
12:SORT
| order by: nation ASC, o_year DESC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity)
| group by: n_name, year(o_orderdate)
|
10:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = ps_suppkey, l_partkey = ps_partkey
|
|--03:SCAN HDFS [tpch.partsupp]
| partitions=1/1 files=1 size=112.71MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
|
|--05:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--04:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l_partkey = p_partkey
|
|--00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_name LIKE '%green%'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
expected:
12:SORT
| order by: nation ASC, o_year DESC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity)
| group by: n_name, year(o_orderdate)
|
10:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = ps_suppkey, l_partkey = ps_partkey
|
|--03:SCAN HDFS [tpch.partsupp]
| partitions=1/1 files=1 size=112.71MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
|
|--05:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--04:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey
|
|--01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l_partkey = p_partkey
|
|--00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_name LIKE '%green%'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
section DISTRIBUTEDPLAN of query:
select
nation,
o_year,
sum(amount) as sum_profit
from(
select
n_name as nation,
year(o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
tpch.part,
tpch.supplier,
tpch.lineitem,
tpch.partsupp,
tpch.orders,
tpch.nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%green%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc
actual result doesn't match expected result:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: nation ASC, o_year DESC
|
12:SORT
| order by: nation ASC, o_year DESC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(amount)
| group by: nation, o_year
|
18:EXCHANGE [HASH(nation,o_year)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity)
| group by: n_name, year(o_orderdate)
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = ps_suppkey, l_partkey = ps_partkey
|
|--17:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.partsupp]
| partitions=1/1 files=1 size=112.71MB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--15:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_partkey = p_partkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_name LIKE '%green%'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
expected:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: nation ASC, o_year DESC
|
12:SORT
| order by: nation ASC, o_year DESC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(amount)
| group by: nation, o_year
|
18:EXCHANGE [HASH(nation,o_year)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity)
| group by: n_name, year(o_orderdate)
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = ps_suppkey, l_partkey = ps_partkey
|
|--17:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.partsupp]
| partitions=1/1 files=1 size=112.71MB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_partkey = p_partkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_name LIKE '%green%'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
section PLAN of query:
select
s_name,
count(*) as numwait
from
tpch.supplier,
tpch.lineitem l1,
tpch.orders,
tpch.nation
where
s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists (
select
*
from
tpch.lineitem l2
where
l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
)
and not exists (
select
*
from
tpch.lineitem l3
where
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
and s_nationkey = n_nationkey
and n_name = 'SAUDI ARABIA'
group by
s_name
order by
numwait desc,
s_name
limit 100
actual result doesn't match expected result:
12:TOP-N [LIMIT=100]
| order by: count(*) DESC, s_name ASC
|
11:AGGREGATE [FINALIZE]
| output: count(*)
| group by: s_name
|
10:HASH JOIN [LEFT ANTI JOIN]
| hash predicates: l1.l_orderkey = l3.l_orderkey
| other join predicates: l3.l_suppkey != l1.l_suppkey
|
|--05:SCAN HDFS [tpch.lineitem l3]
| partitions=1/1 files=1 size=718.94MB
| predicates: l3.l_receiptdate > l3.l_commitdate
|
09:HASH JOIN [LEFT SEMI JOIN]
| hash predicates: l1.l_orderkey = l2.l_orderkey
| other join predicates: l2.l_suppkey != l1.l_suppkey
|
|--04:SCAN HDFS [tpch.lineitem l2]
| partitions=1/1 files=1 size=718.94MB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
|
|--03:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
| predicates: n_name = 'SAUDI ARABIA'
|
07:HASH JOIN [INNER JOIN]
| hash predicates: l1.l_suppkey = s_suppkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l1.l_orderkey = o_orderkey
|
|--02:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderstatus = 'F'
|
01:SCAN HDFS [tpch.lineitem l1]
partitions=1/1 files=1 size=718.94MB
predicates: l1.l_receiptdate > l1.l_commitdate
expected:
12:TOP-N [LIMIT=100]
| order by: count(*) DESC, s_name ASC
|
11:AGGREGATE [FINALIZE]
| output: count(*)
| group by: s_name
|
10:HASH JOIN [LEFT ANTI JOIN]
| hash predicates: l1.l_orderkey = l3.l_orderkey
| other join predicates: l3.l_suppkey != l1.l_suppkey
|
|--05:SCAN HDFS [tpch.lineitem l3]
| partitions=1/1 files=1 size=718.94MB
| predicates: l3.l_receiptdate > l3.l_commitdate
|
09:HASH JOIN [LEFT SEMI JOIN]
| hash predicates: l1.l_orderkey = l2.l_orderkey
| other join predicates: l2.l_suppkey != l1.l_suppkey
|
|--04:SCAN HDFS [tpch.lineitem l2]
| partitions=1/1 files=1 size=718.94MB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
|
|--03:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
| predicates: n_name = 'SAUDI ARABIA'
|
07:HASH JOIN [INNER JOIN]
| hash predicates: l1.l_orderkey = o_orderkey
|
|--02:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderstatus = 'F'
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l1.l_suppkey = s_suppkey
|
|--00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
01:SCAN HDFS [tpch.lineitem l1]
partitions=1/1 files=1 size=718.94MB
predicates: l1.l_receiptdate > l1.l_commitdate
section DISTRIBUTEDPLAN of query:
select
s_name,
count(*) as numwait
from
tpch.supplier,
tpch.lineitem l1,
tpch.orders,
tpch.nation
where
s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists (
select
*
from
tpch.lineitem l2
where
l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
)
and not exists (
select
*
from
tpch.lineitem l3
where
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
and s_nationkey = n_nationkey
and n_name = 'SAUDI ARABIA'
group by
s_name
order by
numwait desc,
s_name
limit 100
actual result doesn't match expected result:
21:MERGING-EXCHANGE [UNPARTITIONED]
| order by: count(*) DESC, s_name ASC
| limit: 100
|
12:TOP-N [LIMIT=100]
| order by: count(*) DESC, s_name ASC
|
20:AGGREGATE [FINALIZE]
| output: count:merge(*)
| group by: s_name
|
19:EXCHANGE [HASH(s_name)]
|
11:AGGREGATE
| output: count(*)
| group by: s_name
|
10:HASH JOIN [LEFT ANTI JOIN, PARTITIONED]
| hash predicates: l1.l_orderkey = l3.l_orderkey
| other join predicates: l3.l_suppkey != l1.l_suppkey
|
|--18:EXCHANGE [HASH(l3.l_orderkey)]
| |
| 05:SCAN HDFS [tpch.lineitem l3]
| partitions=1/1 files=1 size=718.94MB
| predicates: l3.l_receiptdate > l3.l_commitdate
|
09:HASH JOIN [LEFT SEMI JOIN, PARTITIONED]
| hash predicates: l1.l_orderkey = l2.l_orderkey
| other join predicates: l2.l_suppkey != l1.l_suppkey
|
|--17:EXCHANGE [HASH(l2.l_orderkey)]
| |
| 04:SCAN HDFS [tpch.lineitem l2]
| partitions=1/1 files=1 size=718.94MB
|
16:EXCHANGE [HASH(l1.l_orderkey)]
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
| predicates: n_name = 'SAUDI ARABIA'
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l1.l_suppkey = s_suppkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--14:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l1.l_orderkey = o_orderkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 02:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderstatus = 'F'
|
01:SCAN HDFS [tpch.lineitem l1]
partitions=1/1 files=1 size=718.94MB
predicates: l1.l_receiptdate > l1.l_commitdate
expected:
21:MERGING-EXCHANGE [UNPARTITIONED]
| order by: count(*) DESC, s_name ASC
| limit: 100
|
12:TOP-N [LIMIT=100]
| order by: count(*) DESC, s_name ASC
|
20:AGGREGATE [FINALIZE]
| output: count:merge(*)
| group by: s_name
|
19:EXCHANGE [HASH(s_name)]
|
11:AGGREGATE
| output: count(*)
| group by: s_name
|
10:HASH JOIN [LEFT ANTI JOIN, PARTITIONED]
| hash predicates: l1.l_orderkey = l3.l_orderkey
| other join predicates: l3.l_suppkey != l1.l_suppkey
|
|--18:EXCHANGE [HASH(l3.l_orderkey)]
| |
| 05:SCAN HDFS [tpch.lineitem l3]
| partitions=1/1 files=1 size=718.94MB
| predicates: l3.l_receiptdate > l3.l_commitdate
|
09:HASH JOIN [LEFT SEMI JOIN, PARTITIONED]
| hash predicates: l1.l_orderkey = l2.l_orderkey
| other join predicates: l2.l_suppkey != l1.l_suppkey
|
|--17:EXCHANGE [HASH(l2.l_orderkey)]
| |
| 04:SCAN HDFS [tpch.lineitem l2]
| partitions=1/1 files=1 size=718.94MB
|
16:EXCHANGE [HASH(l1.l_orderkey)]
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
| predicates: n_name = 'SAUDI ARABIA'
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l1.l_orderkey = o_orderkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 02:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderstatus = 'F'
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l1.l_suppkey = s_suppkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
01:SCAN HDFS [tpch.lineitem l1]
partitions=1/1 files=1 size=718.94MB
predicates: l1.l_receiptdate > l1.l_commitdate
Stacktrace
java.lang.AssertionError: section PLAN of query:
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
tpch.customer,
tpch.orders,
tpch.lineitem,
tpch.supplier,
tpch.nation,
tpch.region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= '1994-01-01'
and o_orderdate < '1995-01-01'
group by
n_name
order by
revenue desc
actual result doesn't match expected result:
12:SORT
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--03:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: n_regionkey = r_regionkey
|
|--05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
08:HASH JOIN [INNER JOIN]
| hash predicates: tpch.customer.c_nationkey = tpch.nation.n_nationkey
|
|--04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--01:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
expected:
12:SORT
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN]
| hash predicates: n_regionkey = r_regionkey
|
|--05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
|
|--04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
|
|--03:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--01:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
section DISTRIBUTEDPLAN of query:
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
tpch.customer,
tpch.orders,
tpch.lineitem,
tpch.supplier,
tpch.nation,
tpch.region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= '1994-01-01'
and o_orderdate < '1995-01-01'
group by
n_name
order by
revenue desc
actual result doesn't match expected result:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
|
12:SORT
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: n_name
|
18:EXCHANGE [HASH(n_name)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--17:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n_regionkey = r_regionkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: tpch.customer.c_nationkey = tpch.nation.n_nationkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
expected:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
|
12:SORT
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: n_name
|
18:EXCHANGE [HASH(n_name)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n_regionkey = r_regionkey
|
|--17:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey, c_nationkey = s_nationkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1994-01-01', o_orderdate < '1995-01-01'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
section PLAN of query:
select
supp_nation,
cust_nation,
l_year,
sum(volume) as revenue
from (
select
n1.n_name as supp_nation,
n2.n_name as cust_nation,
year(l_shipdate) as l_year,
l_extendedprice * (1 - l_discount) as volume
from
tpch.supplier,
tpch.lineitem,
tpch.orders,
tpch.customer,
tpch.nation n1,
tpch.nation n2
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
(n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
)
and l_shipdate between '1995-01-01' and '1996-12-31'
) as shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year
actual result doesn't match expected result:
12:SORT
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n1.n_name, n2.n_name, year(l_shipdate)
|
10:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n1.n_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| other predicates: ((n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY') OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE'))
|
|--04:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey
|
|--00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: c_nationkey = n2.n_nationkey
|
|--05:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--03:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: o_orderkey = l_orderkey
|
|--01:SCAN HDFS [tpch.lineitem]
| partitions=1/1 files=1 size=718.94MB
| predicates: l_shipdate >= '1995-01-01', l_shipdate <= '1996-12-31'
|
02:SCAN HDFS [tpch.orders]
partitions=1/1 files=1 size=162.56MB
expected:
12:SORT
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n1.n_name, n2.n_name, year(l_shipdate)
|
10:HASH JOIN [INNER JOIN]
| hash predicates: c_nationkey = n2.n_nationkey
| other predicates: ((n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY') OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE'))
|
|--05:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n1.n_nationkey
|
|--04:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--03:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey
|
|--00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: o_orderkey = l_orderkey
|
|--01:SCAN HDFS [tpch.lineitem]
| partitions=1/1 files=1 size=718.94MB
| predicates: l_shipdate >= '1995-01-01', l_shipdate <= '1996-12-31'
|
02:SCAN HDFS [tpch.orders]
partitions=1/1 files=1 size=162.56MB
section DISTRIBUTEDPLAN of query:
select
supp_nation,
cust_nation,
l_year,
sum(volume) as revenue
from (
select
n1.n_name as supp_nation,
n2.n_name as cust_nation,
year(l_shipdate) as l_year,
l_extendedprice * (1 - l_discount) as volume
from
tpch.supplier,
tpch.lineitem,
tpch.orders,
tpch.customer,
tpch.nation n1,
tpch.nation n2
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
(n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
)
and l_shipdate between '1995-01-01' and '1996-12-31'
) as shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year
actual result doesn't match expected result:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
|
12:SORT
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(volume)
| group by: supp_nation, cust_nation, l_year
|
18:EXCHANGE [HASH(supp_nation,cust_nation,l_year)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n1.n_name, n2.n_name, year(l_shipdate)
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n1.n_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| other predicates: ((n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY') OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE'))
|
|--17:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = n2.n_nationkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_orderkey = l_orderkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.lineitem]
| partitions=1/1 files=1 size=718.94MB
| predicates: l_shipdate >= '1995-01-01', l_shipdate <= '1996-12-31'
|
02:SCAN HDFS [tpch.orders]
partitions=1/1 files=1 size=162.56MB
expected:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
|
12:SORT
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(volume)
| group by: supp_nation, cust_nation, l_year
|
18:EXCHANGE [HASH(supp_nation,cust_nation,l_year)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n1.n_name, n2.n_name, year(l_shipdate)
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = n2.n_nationkey
| other predicates: ((n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY') OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE'))
|
|--17:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n1.n_nationkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_orderkey = l_orderkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.lineitem]
| partitions=1/1 files=1 size=718.94MB
| predicates: l_shipdate >= '1995-01-01', l_shipdate <= '1996-12-31'
|
02:SCAN HDFS [tpch.orders]
partitions=1/1 files=1 size=162.56MB
section PLAN of query:
select
o_year,
sum(case
when nation = 'BRAZIL'
then volume
else 0
end) / sum(volume) as mkt_share
from (
select
year(o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
tpch.part,
tpch.supplier,
tpch.lineitem,
tpch.orders,
tpch.customer,
tpch.nation n1,
tpch.nation n2,
tpch.region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'AMERICA'
and s_nationkey = n2.n_nationkey
and o_orderdate between '1995-01-01' and '1996-12-31'
and p_type = 'ECONOMY ANODIZED STEEL'
) as all_nations
group by
o_year
order by
o_year
actual result doesn't match expected result:
16:SORT
| order by: o_year ASC
|
15:AGGREGATE [FINALIZE]
| output: sum(CASE WHEN n2.n_name = 'BRAZIL' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum(l_extendedprice * (1 - l_discount))
| group by: year(o_orderdate)
|
14:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n2.n_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--06:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
13:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey
|
|--01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
12:HASH JOIN [INNER JOIN]
| hash predicates: n1.n_regionkey = r_regionkey
|
|--07:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'AMERICA'
|
11:HASH JOIN [INNER JOIN]
| hash predicates: c_nationkey = n1.n_nationkey
|
|--05:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
10:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--04:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--03:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1995-01-01', o_orderdate <= '1996-12-31'
|
08:HASH JOIN [INNER JOIN]
| hash predicates: l_partkey = p_partkey
|
|--00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_type = 'ECONOMY ANODIZED STEEL'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
expected:
16:SORT
| order by: o_year ASC
|
15:AGGREGATE [FINALIZE]
| output: sum(CASE WHEN n2.n_name = 'BRAZIL' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum(l_extendedprice * (1 - l_discount))
| group by: year(o_orderdate)
|
14:HASH JOIN [INNER JOIN]
| hash predicates: n1.n_regionkey = r_regionkey
|
|--07:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'AMERICA'
|
13:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n2.n_nationkey
|
|--06:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
12:HASH JOIN [INNER JOIN]
| hash predicates: c_nationkey = n1.n_nationkey
|
|--05:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
11:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
|
|--04:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
10:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--03:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1995-01-01', o_orderdate <= '1996-12-31'
|
09:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey
|
|--01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: l_partkey = p_partkey
|
|--00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_type = 'ECONOMY ANODIZED STEEL'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
section DISTRIBUTEDPLAN of query:
select
o_year,
sum(case
when nation = 'BRAZIL'
then volume
else 0
end) / sum(volume) as mkt_share
from (
select
year(o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
tpch.part,
tpch.supplier,
tpch.lineitem,
tpch.orders,
tpch.customer,
tpch.nation n1,
tpch.nation n2,
tpch.region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'AMERICA'
and s_nationkey = n2.n_nationkey
and o_orderdate between '1995-01-01' and '1996-12-31'
and p_type = 'ECONOMY ANODIZED STEEL'
) as all_nations
group by
o_year
order by
o_year
actual result doesn't match expected result:
26:MERGING-EXCHANGE [UNPARTITIONED]
| order by: o_year ASC
|
16:SORT
| order by: o_year ASC
|
25:AGGREGATE [FINALIZE]
| output: sum:merge(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0 END), sum:merge(volume)
| group by: o_year
|
24:EXCHANGE [HASH(o_year)]
|
15:AGGREGATE
| output: sum(CASE WHEN n2.n_name = 'BRAZIL' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum(l_extendedprice * (1 - l_discount))
| group by: year(o_orderdate)
|
14:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n2.n_nationkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--23:EXCHANGE [BROADCAST]
| |
| 06:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
13:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
|
|--22:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
12:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n1.n_regionkey = r_regionkey
|
|--21:EXCHANGE [BROADCAST]
| |
| 07:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'AMERICA'
|
11:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = n1.n_nationkey
|
|--20:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--19:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--18:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1995-01-01', o_orderdate <= '1996-12-31'
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_partkey = p_partkey
|
|--17:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_type = 'ECONOMY ANODIZED STEEL'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
expected:
26:MERGING-EXCHANGE [UNPARTITIONED]
| order by: o_year ASC
|
16:SORT
| order by: o_year ASC
|
25:AGGREGATE [FINALIZE]
| output: sum:merge(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0 END), sum:merge(volume)
| group by: o_year
|
24:EXCHANGE [HASH(o_year)]
|
15:AGGREGATE
| output: sum(CASE WHEN n2.n_name = 'BRAZIL' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum(l_extendedprice * (1 - l_discount))
| group by: year(o_orderdate)
|
14:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n1.n_regionkey = r_regionkey
|
|--23:EXCHANGE [BROADCAST]
| |
| 07:SCAN HDFS [tpch.region]
| partitions=1/1 files=1 size=384B
| predicates: r_name = 'AMERICA'
|
13:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n2.n_nationkey
|
|--22:EXCHANGE [BROADCAST]
| |
| 06:SCAN HDFS [tpch.nation n2]
| partitions=1/1 files=1 size=2.15KB
|
12:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = n1.n_nationkey
|
|--21:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation n1]
| partitions=1/1 files=1 size=2.15KB
|
11:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
|
|--20:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.customer]
| partitions=1/1 files=1 size=23.08MB
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--19:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate >= '1995-01-01', o_orderdate <= '1996-12-31'
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
|
|--18:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_partkey = p_partkey
|
|--17:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_type = 'ECONOMY ANODIZED STEEL'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
section PLAN of query:
select
nation,
o_year,
sum(amount) as sum_profit
from(
select
n_name as nation,
year(o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
tpch.part,
tpch.supplier,
tpch.lineitem,
tpch.partsupp,
tpch.orders,
tpch.nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%green%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc
actual result doesn't match expected result:
12:SORT
| order by: nation ASC, o_year DESC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity)
| group by: n_name, year(o_orderdate)
|
10:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = ps_suppkey, l_partkey = ps_partkey
|
|--03:SCAN HDFS [tpch.partsupp]
| partitions=1/1 files=1 size=112.71MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
|
|--05:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--04:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l_partkey = p_partkey
|
|--00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_name LIKE '%green%'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
expected:
12:SORT
| order by: nation ASC, o_year DESC
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity)
| group by: n_name, year(o_orderdate)
|
10:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = ps_suppkey, l_partkey = ps_partkey
|
|--03:SCAN HDFS [tpch.partsupp]
| partitions=1/1 files=1 size=112.71MB
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
|
|--05:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
|
|--04:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
|
07:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey
|
|--01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l_partkey = p_partkey
|
|--00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_name LIKE '%green%'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
section DISTRIBUTEDPLAN of query:
select
nation,
o_year,
sum(amount) as sum_profit
from(
select
n_name as nation,
year(o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
tpch.part,
tpch.supplier,
tpch.lineitem,
tpch.partsupp,
tpch.orders,
tpch.nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%green%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc
actual result doesn't match expected result:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: nation ASC, o_year DESC
|
12:SORT
| order by: nation ASC, o_year DESC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(amount)
| group by: nation, o_year
|
18:EXCHANGE [HASH(nation,o_year)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity)
| group by: n_name, year(o_orderdate)
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = ps_suppkey, l_partkey = ps_partkey
|
|--17:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.partsupp]
| partitions=1/1 files=1 size=112.71MB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--15:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_partkey = p_partkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_name LIKE '%green%'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
expected:
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: nation ASC, o_year DESC
|
12:SORT
| order by: nation ASC, o_year DESC
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(amount)
| group by: nation, o_year
|
18:EXCHANGE [HASH(nation,o_year)]
|
11:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity)
| group by: n_name, year(o_orderdate)
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = ps_suppkey, l_partkey = ps_partkey
|
|--17:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.partsupp]
| partitions=1/1 files=1 size=112.71MB
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
|
|--16:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_partkey = p_partkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.part]
| partitions=1/1 files=1 size=22.83MB
| predicates: p_name LIKE '%green%'
|
02:SCAN HDFS [tpch.lineitem]
partitions=1/1 files=1 size=718.94MB
section PLAN of query:
select
s_name,
count(*) as numwait
from
tpch.supplier,
tpch.lineitem l1,
tpch.orders,
tpch.nation
where
s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists (
select
*
from
tpch.lineitem l2
where
l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
)
and not exists (
select
*
from
tpch.lineitem l3
where
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
and s_nationkey = n_nationkey
and n_name = 'SAUDI ARABIA'
group by
s_name
order by
numwait desc,
s_name
limit 100
actual result doesn't match expected result:
12:TOP-N [LIMIT=100]
| order by: count(*) DESC, s_name ASC
|
11:AGGREGATE [FINALIZE]
| output: count(*)
| group by: s_name
|
10:HASH JOIN [LEFT ANTI JOIN]
| hash predicates: l1.l_orderkey = l3.l_orderkey
| other join predicates: l3.l_suppkey != l1.l_suppkey
|
|--05:SCAN HDFS [tpch.lineitem l3]
| partitions=1/1 files=1 size=718.94MB
| predicates: l3.l_receiptdate > l3.l_commitdate
|
09:HASH JOIN [LEFT SEMI JOIN]
| hash predicates: l1.l_orderkey = l2.l_orderkey
| other join predicates: l2.l_suppkey != l1.l_suppkey
|
|--04:SCAN HDFS [tpch.lineitem l2]
| partitions=1/1 files=1 size=718.94MB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
|
|--03:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
| predicates: n_name = 'SAUDI ARABIA'
|
07:HASH JOIN [INNER JOIN]
| hash predicates: l1.l_suppkey = s_suppkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l1.l_orderkey = o_orderkey
|
|--02:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderstatus = 'F'
|
01:SCAN HDFS [tpch.lineitem l1]
partitions=1/1 files=1 size=718.94MB
predicates: l1.l_receiptdate > l1.l_commitdate
expected:
12:TOP-N [LIMIT=100]
| order by: count(*) DESC, s_name ASC
|
11:AGGREGATE [FINALIZE]
| output: count(*)
| group by: s_name
|
10:HASH JOIN [LEFT ANTI JOIN]
| hash predicates: l1.l_orderkey = l3.l_orderkey
| other join predicates: l3.l_suppkey != l1.l_suppkey
|
|--05:SCAN HDFS [tpch.lineitem l3]
| partitions=1/1 files=1 size=718.94MB
| predicates: l3.l_receiptdate > l3.l_commitdate
|
09:HASH JOIN [LEFT SEMI JOIN]
| hash predicates: l1.l_orderkey = l2.l_orderkey
| other join predicates: l2.l_suppkey != l1.l_suppkey
|
|--04:SCAN HDFS [tpch.lineitem l2]
| partitions=1/1 files=1 size=718.94MB
|
08:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
|
|--03:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
| predicates: n_name = 'SAUDI ARABIA'
|
07:HASH JOIN [INNER JOIN]
| hash predicates: l1.l_orderkey = o_orderkey
|
|--02:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderstatus = 'F'
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l1.l_suppkey = s_suppkey
|
|--00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
01:SCAN HDFS [tpch.lineitem l1]
partitions=1/1 files=1 size=718.94MB
predicates: l1.l_receiptdate > l1.l_commitdate
section DISTRIBUTEDPLAN of query:
select
s_name,
count(*) as numwait
from
tpch.supplier,
tpch.lineitem l1,
tpch.orders,
tpch.nation
where
s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists (
select
*
from
tpch.lineitem l2
where
l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
)
and not exists (
select
*
from
tpch.lineitem l3
where
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
and s_nationkey = n_nationkey
and n_name = 'SAUDI ARABIA'
group by
s_name
order by
numwait desc,
s_name
limit 100
actual result doesn't match expected result:
21:MERGING-EXCHANGE [UNPARTITIONED]
| order by: count(*) DESC, s_name ASC
| limit: 100
|
12:TOP-N [LIMIT=100]
| order by: count(*) DESC, s_name ASC
|
20:AGGREGATE [FINALIZE]
| output: count:merge(*)
| group by: s_name
|
19:EXCHANGE [HASH(s_name)]
|
11:AGGREGATE
| output: count(*)
| group by: s_name
|
10:HASH JOIN [LEFT ANTI JOIN, PARTITIONED]
| hash predicates: l1.l_orderkey = l3.l_orderkey
| other join predicates: l3.l_suppkey != l1.l_suppkey
|
|--18:EXCHANGE [HASH(l3.l_orderkey)]
| |
| 05:SCAN HDFS [tpch.lineitem l3]
| partitions=1/1 files=1 size=718.94MB
| predicates: l3.l_receiptdate > l3.l_commitdate
|
09:HASH JOIN [LEFT SEMI JOIN, PARTITIONED]
| hash predicates: l1.l_orderkey = l2.l_orderkey
| other join predicates: l2.l_suppkey != l1.l_suppkey
|
|--17:EXCHANGE [HASH(l2.l_orderkey)]
| |
| 04:SCAN HDFS [tpch.lineitem l2]
| partitions=1/1 files=1 size=718.94MB
|
16:EXCHANGE [HASH(l1.l_orderkey)]
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
| predicates: n_name = 'SAUDI ARABIA'
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l1.l_suppkey = s_suppkey
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|--14:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l1.l_orderkey = o_orderkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 02:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderstatus = 'F'
|
01:SCAN HDFS [tpch.lineitem l1]
partitions=1/1 files=1 size=718.94MB
predicates: l1.l_receiptdate > l1.l_commitdate
expected:
21:MERGING-EXCHANGE [UNPARTITIONED]
| order by: count(*) DESC, s_name ASC
| limit: 100
|
12:TOP-N [LIMIT=100]
| order by: count(*) DESC, s_name ASC
|
20:AGGREGATE [FINALIZE]
| output: count:merge(*)
| group by: s_name
|
19:EXCHANGE [HASH(s_name)]
|
11:AGGREGATE
| output: count(*)
| group by: s_name
|
10:HASH JOIN [LEFT ANTI JOIN, PARTITIONED]
| hash predicates: l1.l_orderkey = l3.l_orderkey
| other join predicates: l3.l_suppkey != l1.l_suppkey
|
|--18:EXCHANGE [HASH(l3.l_orderkey)]
| |
| 05:SCAN HDFS [tpch.lineitem l3]
| partitions=1/1 files=1 size=718.94MB
| predicates: l3.l_receiptdate > l3.l_commitdate
|
09:HASH JOIN [LEFT SEMI JOIN, PARTITIONED]
| hash predicates: l1.l_orderkey = l2.l_orderkey
| other join predicates: l2.l_suppkey != l1.l_suppkey
|
|--17:EXCHANGE [HASH(l2.l_orderkey)]
| |
| 04:SCAN HDFS [tpch.lineitem l2]
| partitions=1/1 files=1 size=718.94MB
|
16:EXCHANGE [HASH(l1.l_orderkey)]
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
|
|--15:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.nation]
| partitions=1/1 files=1 size=2.15KB
| predicates: n_name = 'SAUDI ARABIA'
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l1.l_orderkey = o_orderkey
|
|--14:EXCHANGE [BROADCAST]
| |
| 02:SCAN HDFS [tpch.orders]
| partitions=1/1 files=1 size=162.56MB
| predicates: o_orderstatus = 'F'
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l1.l_suppkey = s_suppkey
|
|--13:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.supplier]
| partitions=1/1 files=1 size=1.33MB
|
01:SCAN HDFS [tpch.lineitem l1]
partitions=1/1 files=1 size=718.94MB
predicates: l1.l_receiptdate > l1.l_commitdate
at org.junit.Assert.fail(Assert.java:88)
at com.cloudera.impala.planner.PlannerTestBase.runPlannerTestFile(PlannerTestBase.java:643)
at com.cloudera.impala.planner.PlannerTestBase.runPlannerTestFile(PlannerTestBase.java:648)
at com.cloudera.impala.planner.PlannerTest.testTpch(PlannerTest.java:161)
Raw
metadata.test_ddl.TestDdlStatements.test_truncate_table
metadata.test_ddl.TestDdlStatements.test_truncate_table[exec_option: {'batch_size': 0, 'num_nodes': 0, 'sync_ddl': 0, 'disable_codegen': False, 'abort_on_error': 1, 'exec_single_node_rows_threshold': 0} | table_format: text/none] (from pytest)
Failing for the past 1 build (Since Failed#594 )
Took 16 sec.
add description
Error Message
test failure
Stacktrace
metadata/test_ddl.py:160: in test_truncate_table
> multiple_impalad=self.__use_multiple_impalad(vector))
common/impala_test_suite.py:253: in run_test_case
> pytest.config.option.update_results)
common/test_result_verifier.py:346: in verify_raw_results
> VERIFIER_MAP[verifier](expected, actual)
common/test_result_verifier.py:198: in verify_query_result_is_equal
> assert expected_results == actual_results
E assert Comparing QueryTestResults (expected vs actual):
E Detailed information truncated, use "-vv" to show
Standard Error
-- executing against localhost:21000
use default;
SET sync_ddl=1;
-- executing against localhost:21000
show databases;
-- executing against localhost:21000
use default;
SET sync_ddl=1;
-- executing against localhost:21000
show databases;
-- executing against localhost:21000
use default;
SET sync_ddl=1;
-- executing against localhost:21000
show databases;
-- executing against localhost:21000
use default;
SET sync_ddl=1;
-- executing against localhost:21000
show databases;
-- executing against localhost:21000
use default;
SET sync_ddl=1;
-- executing against localhost:21000
show databases;
-- executing against localhost:21000
use default;
SET sync_ddl=1;
-- executing against localhost:21000
show databases;
-- executing against localhost:21000
use default;
SET sync_ddl=1;
-- executing against localhost:21000
show databases;
-- executing against localhost:21000
show tables in `truncate_table_test_db`;
-- executing against localhost:21000
describe formatted `truncate_table_test_db`.`t1`;
-- executing against localhost:21000
drop table `truncate_table_test_db`.`t1`;
-- executing against localhost:21000
describe formatted `truncate_table_test_db`.`t2`;
-- executing against localhost:21000
drop table `truncate_table_test_db`.`t2`;
-- executing against localhost:21000
show functions in `truncate_table_test_db`;
-- executing against localhost:21000
show aggregate functions in `truncate_table_test_db`;
-- executing against localhost:21000
drop database `truncate_table_test_db`;
MainThread: Starting new HTTP connection (1): 0.0.0.0
MainThread: Starting new HTTP connection (1): 0.0.0.0
MainThread: Starting new HTTP connection (1): 0.0.0.0
MainThread: Starting new HTTP connection (1): 0.0.0.0
MainThread: Starting new HTTP connection (1): 0.0.0.0
MainThread: Starting new HTTP connection (1): 0.0.0.0
-- executing against localhost:21000
use default;
SET sync_ddl=1;
-- executing against localhost:21000
create database truncate_table_test_db;
SET batch_size=0;
SET num_nodes=0;
SET sync_ddl=0;
SET disable_codegen=False;
SET abort_on_error=False;
SET exec_single_node_rows_threshold=0;
-- executing against localhost:21000
use truncate_table_test_db;
SET batch_size=0;
SET num_nodes=0;
SET sync_ddl=0;
SET disable_codegen=False;
SET abort_on_error=False;
SET exec_single_node_rows_threshold=0;
-- executing against localhost:21000
create table t1 like functional.alltypes;
-- executing against localhost:21000
insert into t1 partition(year, month) select * from functional.alltypes;
-- executing against localhost:21000
compute incremental stats t1;
-- executing against localhost:21000
show table stats t1;
-- executing against localhost:21000
show column stats t1;
MainThread: Comparing QueryTestResults (expected vs actual):
'bigint_col','BIGINT',10,-1,8,8 == 'bigint_col','BIGINT',10,-1,8,8
'bool_col','BOOLEAN',2,-1,1,1 == 'bool_col','BOOLEAN',2,-1,1,1
'date_string_col','STRING',666,-1,8,8 != 'date_string_col','STRING',736,-1,8,8
'double_col','DOUBLE',10,-1,8,8 == 'double_col','DOUBLE',10,-1,8,8
'float_col','FLOAT',10,-1,4,4 == 'float_col','FLOAT',10,-1,4,4
'id','INT',8161,-1,4,4 != 'id','INT',7505,-1,4,4
'int_col','INT',10,-1,4,4 == 'int_col','INT',10,-1,4,4
'month','INT',12,0,4,4 == 'month','INT',12,0,4,4
'smallint_col','SMALLINT',10,-1,2,2 == 'smallint_col','SMALLINT',10,-1,2,2
'string_col','STRING',10,-1,1,1 == 'string_col','STRING',10,-1,1,1
'timestamp_col','TIMESTAMP',5678,-1,16,16 != 'timestamp_col','TIMESTAMP',7554,-1,16,16
'tinyint_col','TINYINT',10,-1,1,1 == 'tinyint_col','TINYINT',10,-1,1,1
'year','INT',2,0,4,4 == 'year','INT',2,0,4,4
Raw
metadata.test_hbase_metadata.TestHbaseMetadata.test_hbase_compute_stats
metadata.test_hbase_metadata.TestHbaseMetadata.test_hbase_compute_stats
-- executing against localhost:21000
compute stats compute_stats_db_hbase.alltypessmall_hbase;
-- executing against localhost:21000
show table stats compute_stats_db_hbase.alltypessmall_hbase;
-- executing against localhost:21000
show column stats compute_stats_db_hbase.alltypessmall_hbase;
MainThread: Comparing QueryTestResults (expected vs actual):
'bigint_col','BIGINT',10,-1,8,8 == 'bigint_col','BIGINT',10,-1,8,8
'bool_col','BOOLEAN',2,-1,1,1 == 'bool_col','BOOLEAN',2,-1,1,1
'date_string_col','STRING',12,-1,8,8 == 'date_string_col','STRING',12,-1,8,8
'double_col','DOUBLE',10,-1,8,8 == 'double_col','DOUBLE',10,-1,8,8
'float_col','FLOAT',10,-1,4,4 == 'float_col','FLOAT',10,-1,4,4
'id','INT',105,-1,4,4 != 'id','INT',99,-1,4,4
'int_col','INT',10,-1,4,4 == 'int_col','INT',10,-1,4,4
'month','INT',4,-1,4,4 == 'month','INT',4,-1,4,4
'smallint_col','SMALLINT',10,-1,2,2 == 'smallint_col','SMALLINT',10,-1,2,2
'string_col','STRING',10,-1,1,1 == 'string_col','STRING',10,-1,1,1
'timestamp_col','TIMESTAMP',101,-1,16,16 == 'timestamp_col','TIMESTAMP',101,-1,16,16
'tinyint_col','TINYINT',10,-1,1,1 == 'tinyint_col','TINYINT',10,-1,1,1
'year','INT',1,-1,4,4 == 'year','INT',1,-1,4,4
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment