You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by ta...@apache.org on 2018/03/20 20:44:02 UTC
[04/21] impala git commit: IMPALA-6551: Change Kudu TPCDS and TPCH
columns to DECIMAL
IMPALA-6551: Change Kudu TPCDS and TPCH columns to DECIMAL
Before Kudu supported DECIMAL columns the TPCDS and TPCH
columns were djusted to use DOUBLE in place of DECIMAL. This
patch undoes that change now that Kudu supports DECIMAL.
Testing:
- Updated concurrent_select.py
- Updated test_tpch_queries.py
- Excersized by the Kudu planner tests
Change-Id: I2f7e4464dc6705cadd610a82c459390a9c0dfe4f
Reviewed-on: http://gerrit.cloudera.org:8080/9691
Reviewed-by: Thomas Tauber-Marshall <tm...@cloudera.com>
Tested-by: Impala Public Jenkins
Project: http://git-wip-us.apache.org/repos/asf/impala/repo
Commit: http://git-wip-us.apache.org/repos/asf/impala/commit/f6ad4e6b
Tree: http://git-wip-us.apache.org/repos/asf/impala/tree/f6ad4e6b
Diff: http://git-wip-us.apache.org/repos/asf/impala/diff/f6ad4e6b
Branch: refs/heads/2.x
Commit: f6ad4e6b0e094e091ae1c8105828735a2e5202bf
Parents: 7336839
Author: Grant Henke <gh...@cloudera.com>
Authored: Mon Mar 12 11:55:29 2018 -0500
Committer: Impala Public Jenkins <im...@gerrit.cloudera.org>
Committed: Sat Mar 17 08:30:54 2018 +0000
----------------------------------------------------------------------
testdata/datasets/tpcds/tpcds_kudu_template.sql | 174 +-
testdata/datasets/tpch/tpch_kudu_template.sql | 24 +-
testdata/datasets/tpch/tpch_schema_template.sql | 18 +-
.../workloads/tpcds/queries/tpcds-kudu-q19.test | 39 -
.../workloads/tpcds/queries/tpcds-kudu-q27.test | 36 -
.../workloads/tpcds/queries/tpcds-kudu-q3.test | 32 -
.../workloads/tpcds/queries/tpcds-kudu-q34.test | 47 -
.../workloads/tpcds/queries/tpcds-kudu-q42.test | 29 -
.../workloads/tpcds/queries/tpcds-kudu-q43.test | 37 -
.../workloads/tpcds/queries/tpcds-kudu-q46.test | 81 -
.../workloads/tpcds/queries/tpcds-kudu-q47.test | 53 -
.../workloads/tpcds/queries/tpcds-kudu-q52.test | 28 -
.../workloads/tpcds/queries/tpcds-kudu-q53.test | 38 -
.../workloads/tpcds/queries/tpcds-kudu-q55.test | 25 -
.../workloads/tpcds/queries/tpcds-kudu-q59.test | 84 -
.../workloads/tpcds/queries/tpcds-kudu-q6.test | 28 -
.../workloads/tpcds/queries/tpcds-kudu-q61.test | 43 -
.../workloads/tpcds/queries/tpcds-kudu-q63.test | 38 -
.../workloads/tpcds/queries/tpcds-kudu-q65.test | 63 -
.../workloads/tpcds/queries/tpcds-kudu-q68.test | 62 -
.../workloads/tpcds/queries/tpcds-kudu-q7.test | 32 -
.../workloads/tpcds/queries/tpcds-kudu-q73.test | 51 -
.../workloads/tpcds/queries/tpcds-kudu-q79.test | 61 -
.../workloads/tpcds/queries/tpcds-kudu-q8.test | 71 -
.../workloads/tpcds/queries/tpcds-kudu-q88.test | 93 -
.../workloads/tpcds/queries/tpcds-kudu-q89.test | 33 -
.../workloads/tpcds/queries/tpcds-kudu-q96.test | 17 -
.../workloads/tpcds/queries/tpcds-kudu-q98.test | 34 -
.../workloads/tpch/queries/tpch-kudu-q1.test | 32 -
.../workloads/tpch/queries/tpch-kudu-q10.test | 60 -
.../workloads/tpch/queries/tpch-kudu-q11.test | 1088 -
.../workloads/tpch/queries/tpch-kudu-q12.test | 37 -
.../workloads/tpch/queries/tpch-kudu-q13.test | 69 -
.../workloads/tpch/queries/tpch-kudu-q14.test | 21 -
.../workloads/tpch/queries/tpch-kudu-q15.test | 38 -
.../workloads/tpch/queries/tpch-kudu-q16.test | 18351 -----------------
.../workloads/tpch/queries/tpch-kudu-q17.test | 25 -
.../workloads/tpch/queries/tpch-kudu-q18.test | 98 -
.../workloads/tpch/queries/tpch-kudu-q19.test | 43 -
.../workloads/tpch/queries/tpch-kudu-q2.test | 151 -
.../workloads/tpch/queries/tpch-kudu-q20.test | 229 -
.../workloads/tpch/queries/tpch-kudu-q21.test | 147 -
.../workloads/tpch/queries/tpch-kudu-q22.test | 48 -
.../workloads/tpch/queries/tpch-kudu-q3.test | 40 -
.../workloads/tpch/queries/tpch-kudu-q4.test | 33 -
.../workloads/tpch/queries/tpch-kudu-q5.test | 36 -
.../workloads/tpch/queries/tpch-kudu-q6.test | 17 -
.../workloads/tpch/queries/tpch-kudu-q7.test | 49 -
.../workloads/tpch/queries/tpch-kudu-q8.test | 46 -
.../workloads/tpch/queries/tpch-kudu-q9.test | 213 -
tests/query_test/test_tpch_queries.py | 9 +-
tests/stress/concurrent_select.py | 14 +-
52 files changed, 108 insertions(+), 22157 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/datasets/tpcds/tpcds_kudu_template.sql
----------------------------------------------------------------------
diff --git a/testdata/datasets/tpcds/tpcds_kudu_template.sql b/testdata/datasets/tpcds/tpcds_kudu_template.sql
index 5c4932f..c0a8760 100644
--- a/testdata/datasets/tpcds/tpcds_kudu_template.sql
+++ b/testdata/datasets/tpcds/tpcds_kudu_template.sql
@@ -35,18 +35,18 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.store_sales (
ss_store_sk BIGINT,
ss_promo_sk BIGINT,
ss_quantity BIGINT,
- ss_wholesale_cost DOUBLE,
- ss_list_price DOUBLE,
- ss_sales_price DOUBLE,
- ss_ext_discount_amt DOUBLE,
- ss_ext_sales_price DOUBLE,
- ss_ext_wholesale_cost DOUBLE,
- ss_ext_list_price DOUBLE,
- ss_ext_tax DOUBLE,
- ss_coupon_amt DOUBLE,
- ss_net_paid DOUBLE,
- ss_net_paid_inc_tax DOUBLE,
- ss_net_profit DOUBLE,
+ ss_wholesale_cost DECIMAL(7,2),
+ ss_list_price DECIMAL(7,2),
+ ss_sales_price DECIMAL(7,2),
+ ss_ext_discount_amt DECIMAL(7,2),
+ ss_ext_sales_price DECIMAL(7,2),
+ ss_ext_wholesale_cost DECIMAL(7,2),
+ ss_ext_list_price DECIMAL(7,2),
+ ss_ext_tax DECIMAL(7,2),
+ ss_coupon_amt DECIMAL(7,2),
+ ss_net_paid DECIMAL(7,2),
+ ss_net_paid_inc_tax DECIMAL(7,2),
+ ss_net_profit DECIMAL(7,2),
PRIMARY KEY (ss_ticket_number, ss_item_sk)
)
PARTITION BY HASH (ss_ticket_number,ss_item_sk) PARTITIONS {buckets}
@@ -100,21 +100,21 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.web_sales (
ws_warehouse_sk BIGINT,
ws_promo_sk BIGINT,
ws_quantity BIGINT,
- ws_wholesale_cost DOUBLE,
- ws_list_price DOUBLE,
- ws_sales_price DOUBLE,
- ws_ext_discount_amt DOUBLE,
- ws_ext_sales_price DOUBLE,
- ws_ext_wholesale_cost DOUBLE,
- ws_ext_list_price DOUBLE,
- ws_ext_tax DOUBLE,
- ws_coupon_amt DOUBLE,
- ws_ext_ship_cost DOUBLE,
- ws_net_paid DOUBLE,
- ws_net_paid_inc_tax DOUBLE,
- ws_net_paid_inc_ship DOUBLE,
- ws_net_paid_inc_ship_tax DOUBLE,
- ws_net_profit DOUBLE,
+ ws_wholesale_cost DECIMAL(7,2),
+ ws_list_price DECIMAL(7,2),
+ ws_sales_price DECIMAL(7,2),
+ ws_ext_discount_amt DECIMAL(7,2),
+ ws_ext_sales_price DECIMAL(7,2),
+ ws_ext_wholesale_cost DECIMAL(7,2),
+ ws_ext_list_price DECIMAL(7,2),
+ ws_ext_tax DECIMAL(7,2),
+ ws_coupon_amt DECIMAL(7,2),
+ ws_ext_ship_cost DECIMAL(7,2),
+ ws_net_paid DECIMAL(7,2),
+ ws_net_paid_inc_tax DECIMAL(7,2),
+ ws_net_paid_inc_ship DECIMAL(7,2),
+ ws_net_paid_inc_ship_tax DECIMAL(7,2),
+ ws_net_profit DECIMAL(7,2),
PRIMARY KEY (ws_order_number, ws_item_sk)
)
PARTITION BY HASH (ws_order_number,ws_item_sk) PARTITIONS {buckets}
@@ -180,21 +180,21 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.catalog_sales (
cs_warehouse_sk BIGINT,
cs_promo_sk BIGINT,
cs_quantity BIGINT,
- cs_wholesale_cost DOUBLE,
- cs_list_price DOUBLE,
- cs_sales_price DOUBLE,
- cs_ext_discount_amt DOUBLE,
- cs_ext_sales_price DOUBLE,
- cs_ext_wholesale_cost DOUBLE,
- cs_ext_list_price DOUBLE,
- cs_ext_tax DOUBLE,
- cs_coupon_amt DOUBLE,
- cs_ext_ship_cost DOUBLE,
- cs_net_paid DOUBLE,
- cs_net_paid_inc_tax DOUBLE,
- cs_net_paid_inc_ship DOUBLE,
- cs_net_paid_inc_ship_tax DOUBLE,
- cs_net_profit DOUBLE,
+ cs_wholesale_cost DECIMAL(7,2),
+ cs_list_price DECIMAL(7,2),
+ cs_sales_price DECIMAL(7,2),
+ cs_ext_discount_amt DECIMAL(7,2),
+ cs_ext_sales_price DECIMAL(7,2),
+ cs_ext_wholesale_cost DECIMAL(7,2),
+ cs_ext_list_price DECIMAL(7,2),
+ cs_ext_tax DECIMAL(7,2),
+ cs_coupon_amt DECIMAL(7,2),
+ cs_ext_ship_cost DECIMAL(7,2),
+ cs_net_paid DECIMAL(7,2),
+ cs_net_paid_inc_tax DECIMAL(7,2),
+ cs_net_paid_inc_ship DECIMAL(7,2),
+ cs_net_paid_inc_ship_tax DECIMAL(7,2),
+ cs_net_profit DECIMAL(7,2),
PRIMARY KEY (cs_order_number, cs_item_sk)
)
PARTITION BY HASH (cs_order_number,cs_item_sk) PARTITIONS {buckets}
@@ -252,15 +252,15 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.store_returns (
sr_store_sk BIGINT,
sr_reason_sk BIGINT,
sr_return_quantity BIGINT,
- sr_return_amt DOUBLE,
- sr_return_tax DOUBLE,
- sr_return_amt_inc_tax DOUBLE,
- sr_fee DOUBLE,
- sr_return_ship_cost DOUBLE,
- sr_refunded_cash DOUBLE,
- sr_reversed_charge DOUBLE,
- sr_store_credit DOUBLE,
- sr_net_loss DOUBLE,
+ sr_return_amt DECIMAL(7,2),
+ sr_return_tax DECIMAL(7,2),
+ sr_return_amt_inc_tax DECIMAL(7,2),
+ sr_fee DECIMAL(7,2),
+ sr_return_ship_cost DECIMAL(7,2),
+ sr_refunded_cash DECIMAL(7,2),
+ sr_reversed_charge DECIMAL(7,2),
+ sr_store_credit DECIMAL(7,2),
+ sr_net_loss DECIMAL(7,2),
PRIMARY KEY (sr_ticket_number, sr_item_sk)
)
PARTITION BY HASH (sr_ticket_number,sr_item_sk) PARTITIONS {buckets}
@@ -308,15 +308,15 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.web_returns (
wr_web_page_sk BIGINT,
wr_reason_sk BIGINT,
wr_return_quantity BIGINT,
- wr_return_amt DOUBLE,
- wr_return_tax DOUBLE,
- wr_return_amt_inc_tax DOUBLE,
- wr_fee DOUBLE,
- wr_return_ship_cost DOUBLE,
- wr_refunded_cash DOUBLE,
- wr_reversed_charge DOUBLE,
- wr_account_credit DOUBLE,
- wr_net_loss DOUBLE,
+ wr_return_amt DECIMAL(7,2),
+ wr_return_tax DECIMAL(7,2),
+ wr_return_amt_inc_tax DECIMAL(7,2),
+ wr_fee DECIMAL(7,2),
+ wr_return_ship_cost DECIMAL(7,2),
+ wr_refunded_cash DECIMAL(7,2),
+ wr_reversed_charge DECIMAL(7,2),
+ wr_account_credit DECIMAL(7,2),
+ wr_net_loss DECIMAL(7,2),
PRIMARY KEY (wr_order_number, wr_item_sk)
)
PARTITION BY HASH (wr_order_number,wr_item_sk) PARTITIONS {buckets}
@@ -371,15 +371,15 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.catalog_returns (
cr_warehouse_sk BIGINT,
cr_reason_sk BIGINT,
cr_return_quantity BIGINT,
- cr_return_amount DOUBLE,
- cr_return_tax DOUBLE,
- cr_return_amt_inc_tax DOUBLE,
- cr_fee DOUBLE,
- cr_return_ship_cost DOUBLE,
- cr_refunded_cash DOUBLE,
- cr_reversed_charge DOUBLE,
- cr_store_credit DOUBLE,
- cr_net_loss DOUBLE,
+ cr_return_amount DECIMAL(7,2),
+ cr_return_tax DECIMAL(7,2),
+ cr_return_amt_inc_tax DECIMAL(7,2),
+ cr_fee DECIMAL(7,2),
+ cr_return_ship_cost DECIMAL(7,2),
+ cr_refunded_cash DECIMAL(7,2),
+ cr_reversed_charge DECIMAL(7,2),
+ cr_store_credit DECIMAL(7,2),
+ cr_net_loss DECIMAL(7,2),
PRIMARY KEY (cr_order_number, cr_item_sk)
)
PARTITION BY HASH (cr_order_number,cr_item_sk) PARTITIONS {buckets}
@@ -451,7 +451,7 @@ CREATE TABLE {target_db_name}.customer (
c_birth_country STRING,
c_login STRING,
c_email_address STRING,
- c_last_review_date BIGINT
+ c_last_review_date STRING
)
PARTITION BY HASH (c_customer_sk) PARTITIONS {buckets}
STORED AS KUDU
@@ -472,7 +472,7 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.customer_address (
ca_state STRING,
ca_zip STRING,
ca_country STRING,
- ca_gmt_offset DOUBLE,
+ ca_gmt_offset DECIMAL(5,2),
ca_location_type STRING
)
PARTITION BY HASH (ca_address_sk) PARTITIONS {buckets}
@@ -560,8 +560,8 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.item (
i_rec_start_date STRING,
i_rec_end_date STRING,
i_item_desc STRING,
- i_current_price DOUBLE,
- i_wholesale_cost DOUBLE,
+ i_current_price DECIMAL(7,2),
+ i_wholesale_cost DECIMAL(7,2),
i_brand_id BIGINT,
i_brand STRING,
i_class_id BIGINT,
@@ -591,7 +591,7 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.promotion (
p_start_date_sk BIGINT,
p_end_date_sk BIGINT,
p_promo_id STRING,
- p_cost DOUBLE,
+ p_cost DECIMAL(15,2),
p_response_target BIGINT,
p_promo_name STRING,
p_channel_dmail STRING,
@@ -662,8 +662,8 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.store (
s_state STRING,
s_zip STRING,
s_country STRING,
- s_gmt_offset DOUBLE,
- s_tax_precentage DOUBLE
+ s_gmt_offset DECIMAL(5,2),
+ s_tax_precentage DECIMAL(5,2)
)
PARTITION BY HASH (s_store_sk) PARTITIONS {buckets}
STORED AS KUDU
@@ -721,8 +721,8 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.call_center (
cc_state STRING,
cc_zip STRING,
cc_country STRING,
- cc_gmt_offset DOUBLE,
- cc_tax_percentage DOUBLE
+ cc_gmt_offset DECIMAL(5,2),
+ cc_tax_percentage DECIMAL(5,2)
)
PARTITION BY HASH (cc_call_center_sk) PARTITIONS {buckets}
STORED AS KUDU
@@ -760,18 +760,6 @@ TBLPROPERTIES ('kudu.master_addresses'='{kudu_master}:7051');
INSERT INTO {target_db_name}.income_band SELECT * FROM {source_db_name}.income_band;
----- REASON
-CREATE TABLE IF NOT EXISTS {target_db_name}.reason (
- r_reason_sk BIGINT PRIMARY KEY,
- r_reason_id STRING,
- r_reason_desc STRING
-)
-PARTITION BY HASH (r_reason_sk) PARTITIONS {buckets}
-STORED AS KUDU
-TBLPROPERTIES ('kudu.master_addresses'='{kudu_master}:7051');
-
-INSERT INTO {target_db_name}.reason SELECT * FROM {source_db_name}.reason;
-
---- SHIP_MODE
CREATE TABLE IF NOT EXISTS {target_db_name}.ship_mode (
sm_ship_mode_sk BIGINT PRIMARY KEY,
@@ -802,7 +790,7 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.warehouse (
w_state STRING,
w_zip STRING,
w_country STRING,
- w_gmt_offset DOUBLE
+ w_gmt_offset DECIMAL(5,2)
)
PARTITION BY HASH (w_warehouse_sk) PARTITIONS {buckets}
STORED AS KUDU
@@ -859,8 +847,8 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.web_site (
web_state STRING,
web_zip STRING,
web_country STRING,
- web_gmt_offset DOUBLE,
- web_tax_percentage DOUBLE
+ web_gmt_offset DECIMAL(5,2),
+ web_tax_percentage DECIMAL(5,2)
)
PARTITION BY HASH (web_site_sk) PARTITIONS {buckets}
STORED AS KUDU
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/datasets/tpch/tpch_kudu_template.sql
----------------------------------------------------------------------
diff --git a/testdata/datasets/tpch/tpch_kudu_template.sql b/testdata/datasets/tpch/tpch_kudu_template.sql
index 032a19a..26de6b7 100644
--- a/testdata/datasets/tpch/tpch_kudu_template.sql
+++ b/testdata/datasets/tpch/tpch_kudu_template.sql
@@ -25,13 +25,13 @@
--- LINEITEM
CREATE TABLE IF NOT EXISTS {target_db_name}.lineitem (
L_ORDERKEY BIGINT,
- L_LINENUMBER BIGINT,
+ L_LINENUMBER INT,
L_PARTKEY BIGINT,
L_SUPPKEY BIGINT,
- L_QUANTITY DOUBLE,
- L_EXTENDEDPRICE DOUBLE,
- L_DISCOUNT DOUBLE,
- L_TAX DOUBLE,
+ L_QUANTITY DECIMAL(12,2),
+ L_EXTENDEDPRICE DECIMAL(12,2),
+ L_DISCOUNT DECIMAL(12,2),
+ L_TAX DECIMAL(12,2),
L_RETURNFLAG STRING,
L_LINESTATUS STRING,
L_SHIPDATE STRING,
@@ -73,9 +73,9 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.part (
P_MFGR STRING,
P_BRAND STRING,
P_TYPE STRING,
- P_SIZE BIGINT,
+ P_SIZE INT,
P_CONTAINER STRING,
- P_RETAILPRICE DOUBLE,
+ P_RETAILPRICE DECIMAL(12,2),
P_COMMENT STRING
)
partition by hash (p_partkey) partitions {buckets}
@@ -89,7 +89,7 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.partsupp (
PS_PARTKEY BIGINT,
PS_SUPPKEY BIGINT,
PS_AVAILQTY BIGINT,
- PS_SUPPLYCOST DOUBLE,
+ PS_SUPPLYCOST DECIMAL(12,2),
PS_COMMENT STRING,
PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY)
)
@@ -106,7 +106,7 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.supplier (
S_ADDRESS STRING,
S_NATIONKEY BIGINT,
S_PHONE STRING,
- S_ACCTBAL DOUBLE,
+ S_ACCTBAL DECIMAL(12,2),
S_COMMENT STRING
)
partition by hash (s_suppkey) partitions {buckets}
@@ -143,11 +143,11 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.orders (
O_ORDERKEY BIGINT PRIMARY KEY,
O_CUSTKEY BIGINT,
O_ORDERSTATUS STRING,
- O_TOTALPRICE DOUBLE,
+ O_TOTALPRICE DECIMAL(12,2),
O_ORDERDATE STRING,
O_ORDERPRIORITY STRING,
O_CLERK STRING,
- O_SHIPPRIORITY BIGINT,
+ O_SHIPPRIORITY INT,
O_COMMENT STRING
)
partition by hash (o_orderkey) partitions {buckets}
@@ -163,7 +163,7 @@ CREATE TABLE IF NOT EXISTS {target_db_name}.customer (
C_ADDRESS STRING,
C_NATIONKEY BIGINT,
C_PHONE STRING,
- C_ACCTBAL DOUBLE,
+ C_ACCTBAL DECIMAL(12,2),
C_MKTSEGMENT STRING,
C_COMMENT STRING
)
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/datasets/tpch/tpch_schema_template.sql
----------------------------------------------------------------------
diff --git a/testdata/datasets/tpch/tpch_schema_template.sql b/testdata/datasets/tpch/tpch_schema_template.sql
index dfba06d..8997800 100644
--- a/testdata/datasets/tpch/tpch_schema_template.sql
+++ b/testdata/datasets/tpch/tpch_schema_template.sql
@@ -46,10 +46,10 @@ create table if not exists {db_name}{db_suffix}.{table_name} (
L_PARTKEY BIGINT,
L_SUPPKEY BIGINT,
L_LINENUMBER INT,
- L_QUANTITY DOUBLE,
- L_EXTENDEDPRICE DOUBLE,
- L_DISCOUNT DOUBLE,
- L_TAX DOUBLE,
+ L_QUANTITY DECIMAL(12,2),
+ L_EXTENDEDPRICE DECIMAL(12,2),
+ L_DISCOUNT DECIMAL(12,2),
+ L_TAX DECIMAL(12,2),
L_RETURNFLAG STRING,
L_LINESTATUS STRING,
L_SHIPDATE STRING,
@@ -92,7 +92,7 @@ create table if not exists {db_name}{db_suffix}.{table_name} (
P_TYPE STRING,
P_SIZE INT,
P_CONTAINER STRING,
- P_RETAILPRICE DOUBLE,
+ P_RETAILPRICE DECIMAL(12,2),
P_COMMENT STRING
)
partition by hash (p_partkey) partitions 9 stored as kudu;
@@ -119,7 +119,7 @@ create table if not exists {db_name}{db_suffix}.{table_name} (
PS_PARTKEY BIGINT,
PS_SUPPKEY BIGINT,
PS_AVAILQTY BIGINT,
- PS_SUPPLYCOST DOUBLE,
+ PS_SUPPLYCOST DECIMAL(12,2),
PS_COMMENT STRING,
PRIMARY KEY(PS_PARTKEY, PS_SUPPKEY)
)
@@ -151,7 +151,7 @@ create table if not exists {db_name}{db_suffix}.{table_name} (
S_ADDRESS STRING,
S_NATIONKEY SMALLINT,
S_PHONE STRING,
- S_ACCTBAL DOUBLE,
+ S_ACCTBAL DECIMAL(12,2),
S_COMMENT STRING
)
partition by hash (s_suppkey) partitions 9 stored as kudu;
@@ -230,7 +230,7 @@ create table if not exists {db_name}{db_suffix}.{table_name} (
O_ORDERKEY BIGINT PRIMARY KEY,
O_CUSTKEY BIGINT,
O_ORDERSTATUS STRING,
- O_TOTALPRICE DOUBLE,
+ O_TOTALPRICE DECIMAL(12,2),
O_ORDERDATE STRING,
O_ORDERPRIORITY STRING,
O_CLERK STRING,
@@ -266,7 +266,7 @@ create table if not exists {db_name}{db_suffix}.{table_name} (
C_ADDRESS STRING,
C_NATIONKEY SMALLINT,
C_PHONE STRING,
- C_ACCTBAL DOUBLE,
+ C_ACCTBAL DECIMAL(12,2),
C_MKTSEGMENT STRING,
C_COMMENT STRING
)
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q19.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q19.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q19.test
deleted file mode 100644
index 46f358e..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q19.test
+++ /dev/null
@@ -1,39 +0,0 @@
-====
----- QUERY: TPCDS-Q19
-select
- i_brand_id brand_id,
- i_brand brand,
- i_manufact_id,
- i_manufact,
- round(sum(ss_ext_sales_price), 2) ext_price
-from
- date_dim,
- store_sales,
- item,
- customer,
- customer_address,
- store
-where
- d_date_sk = ss_sold_date_sk
- and ss_item_sk = i_item_sk
- and i_manager_id = 7
- and d_moy = 11
- and d_year = 1999
- and ss_customer_sk = c_customer_sk
- and c_current_addr_sk = ca_address_sk
- and substr(ca_zip, 1, 5) <> substr(s_zip, 1, 5)
- and ss_store_sk = s_store_sk
- and ss_sold_date_sk between 2451484 and 2451513
-group by
- i_brand,
- i_brand_id,
- i_manufact_id,
- i_manufact
-order by
- ext_price desc,
- i_brand,
- i_brand_id,
- i_manufact_id,
- i_manufact
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q27.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q27.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q27.test
deleted file mode 100644
index f2723c8..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q27.test
+++ /dev/null
@@ -1,36 +0,0 @@
-====
----- QUERY: TPCDS-Q27
-select
- i_item_id,
- s_state,
- -- grouping(s_state) g_state,
- round(avg(ss_quantity), 2) agg1,
- round(avg(ss_list_price), 2) agg2,
- round(avg(ss_coupon_amt), 2) agg3,
- round(avg(ss_sales_price), 2) agg4
-from
- store_sales,
- customer_demographics,
- date_dim,
- store,
- item
-where
- ss_sold_date_sk = d_date_sk
- and ss_item_sk = i_item_sk
- and ss_store_sk = s_store_sk
- and ss_cdemo_sk = cd_demo_sk
- and cd_gender = 'F'
- and cd_marital_status = 'W'
- and cd_education_status = 'Primary'
- and d_year = 1998
- and s_state in ('WI', 'CA', 'TX', 'FL', 'WA', 'TN')
- and ss_sold_date_sk between 2450815 and 2451179 -- partition key filter
-group by
- -- rollup (i_item_id, s_state)
- i_item_id,
- s_state
-order by
- i_item_id,
- s_state
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q3.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q3.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q3.test
deleted file mode 100644
index ba1dd67..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q3.test
+++ /dev/null
@@ -1,32 +0,0 @@
-====
----- QUERY: TPCDS-Q3
-select
- dt.d_year,
- item.i_brand_id brand_id,
- item.i_brand brand,
- round(sum(ss_ext_sales_price), 2) sum_agg
-from
- date_dim dt,
- store_sales,
- item
-where
- dt.d_date_sk = store_sales.ss_sold_date_sk
- and store_sales.ss_item_sk = item.i_item_sk
- and item.i_manufact_id = 436
- and dt.d_moy = 12
- -- partition key filters
- and (ss_sold_date_sk between 2451149 and 2451179
- or ss_sold_date_sk between 2451514 and 2451544
- or ss_sold_date_sk between 2451880 and 2451910
- or ss_sold_date_sk between 2452245 and 2452275
- or ss_sold_date_sk between 2452610 and 2452640)
-group by
- dt.d_year,
- item.i_brand,
- item.i_brand_id
-order by
- dt.d_year,
- sum_agg desc,
- brand_id
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q34.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q34.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q34.test
deleted file mode 100644
index 2e6e033..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q34.test
+++ /dev/null
@@ -1,47 +0,0 @@
-====
----- QUERY: TPCDS-Q34
-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 (date_dim.d_dom between 1 and 3
- or date_dim.d_dom between 25 and 28)
- 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.2
- and date_dim.d_year in (1998, 1998 + 1, 1998 + 2)
- and store.s_county in ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County', 'Fairfield County', 'Raleigh County', 'Ziebach County', 'Williamson County')
- and ss_sold_date_sk between 2450816 and 2451910 -- partition key filter
- group by
- ss_ticket_number,
- ss_customer_sk
- ) dn,
- customer
-where
- ss_customer_sk = c_customer_sk
- and cnt between 15 and 20
-order by
- c_last_name,
- c_first_name,
- c_salutation,
- c_preferred_cust_flag desc
-limit 100000;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q42.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q42.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q42.test
deleted file mode 100644
index 6d173e3..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q42.test
+++ /dev/null
@@ -1,29 +0,0 @@
-====
----- QUERY: TPCDS-Q42
-select
- dt.d_year,
- item.i_category_id,
- item.i_category,
- round(sum(ss_ext_sales_price), 2)
-from
- date_dim dt,
- store_sales,
- item
-where
- dt.d_date_sk = store_sales.ss_sold_date_sk
- and store_sales.ss_item_sk = item.i_item_sk
- and item.i_manager_id = 1
- and dt.d_moy = 12
- and dt.d_year = 1998
- and ss_sold_date_sk between 2451149 and 2451179 -- partition key filter
-group by
- dt.d_year,
- item.i_category_id,
- item.i_category
-order by
- round(sum(ss_ext_sales_price), 2) desc,
- dt.d_year,
- item.i_category_id,
- item.i_category
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q43.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q43.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q43.test
deleted file mode 100644
index 5c7ccda..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q43.test
+++ /dev/null
@@ -1,37 +0,0 @@
-====
----- QUERY: TPCDS-Q43
-select
- s_store_name,
- s_store_id,
- round(sum(case when (d_day_name = 'Sunday') then ss_sales_price else null end), 2) sun_sales,
- round(sum(case when (d_day_name = 'Monday') then ss_sales_price else null end), 2) mon_sales,
- round(sum(case when (d_day_name = 'Tuesday') then ss_sales_price else null end), 2) tue_sales,
- round(sum(case when (d_day_name = 'Wednesday') then ss_sales_price else null end), 2) wed_sales,
- round(sum(case when (d_day_name = 'Thursday') then ss_sales_price else null end), 2) thu_sales,
- round(sum(case when (d_day_name = 'Friday') then ss_sales_price else null end), 2) fri_sales,
- round(sum(case when (d_day_name = 'Saturday') then ss_sales_price else null end), 2) sat_sales
-from
- date_dim,
- store_sales,
- store
-where
- d_date_sk = ss_sold_date_sk
- and s_store_sk = ss_store_sk
- and s_gmt_offset = -5
- and d_year = 1998
- and ss_sold_date_sk between 2450816 and 2451179 -- partition key filter
-group by
- s_store_name,
- s_store_id
-order by
- s_store_name,
- s_store_id,
- sun_sales,
- mon_sales,
- tue_sales,
- wed_sales,
- thu_sales,
- fri_sales,
- sat_sales
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q46.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q46.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q46.test
deleted file mode 100644
index dffecc3..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q46.test
+++ /dev/null
@@ -1,81 +0,0 @@
-====
----- QUERY: TPCDS-Q46
-select
- c_last_name,
- c_first_name,
- ca_city,
- bought_city,
- ss_ticket_number,
- amt,
- profit
-from
- (select
- ss_ticket_number,
- ss_customer_sk,
- ca_city bought_city,
- round(sum(ss_coupon_amt), 2) amt,
- round(sum(ss_net_profit), 2) profit
- from
- store_sales,
- date_dim,
- store,
- household_demographics,
- customer_address
- 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 store_sales.ss_addr_sk = customer_address.ca_address_sk
- and (household_demographics.hd_dep_count = 5
- or household_demographics.hd_vehicle_count = 3)
- and date_dim.d_dow in (6, 0)
- and date_dim.d_year in (1999, 1999 + 1, 1999 + 2)
- and store.s_city in ('Midway', 'Concord', 'Spring Hill', 'Brownsville', 'Greenville')
- -- partition key filter
- and ss_sold_date_sk in (2451181, 2451182, 2451188, 2451189, 2451195, 2451196, 2451202, 2451203, 2451209, 2451210, 2451216, 2451217,
- 2451223, 2451224, 2451230, 2451231, 2451237, 2451238, 2451244, 2451245, 2451251, 2451252, 2451258, 2451259,
- 2451265, 2451266, 2451272, 2451273, 2451279, 2451280, 2451286, 2451287, 2451293, 2451294, 2451300, 2451301,
- 2451307, 2451308, 2451314, 2451315, 2451321, 2451322, 2451328, 2451329, 2451335, 2451336, 2451342, 2451343,
- 2451349, 2451350, 2451356, 2451357, 2451363, 2451364, 2451370, 2451371, 2451377, 2451378, 2451384, 2451385,
- 2451391, 2451392, 2451398, 2451399, 2451405, 2451406, 2451412, 2451413, 2451419, 2451420, 2451426, 2451427,
- 2451433, 2451434, 2451440, 2451441, 2451447, 2451448, 2451454, 2451455, 2451461, 2451462, 2451468, 2451469,
- 2451475, 2451476, 2451482, 2451483, 2451489, 2451490, 2451496, 2451497, 2451503, 2451504, 2451510, 2451511,
- 2451517, 2451518, 2451524, 2451525, 2451531, 2451532, 2451538, 2451539, 2451545, 2451546, 2451552, 2451553,
- 2451559, 2451560, 2451566, 2451567, 2451573, 2451574, 2451580, 2451581, 2451587, 2451588, 2451594, 2451595,
- 2451601, 2451602, 2451608, 2451609, 2451615, 2451616, 2451622, 2451623, 2451629, 2451630, 2451636, 2451637,
- 2451643, 2451644, 2451650, 2451651, 2451657, 2451658, 2451664, 2451665, 2451671, 2451672, 2451678, 2451679,
- 2451685, 2451686, 2451692, 2451693, 2451699, 2451700, 2451706, 2451707, 2451713, 2451714, 2451720, 2451721,
- 2451727, 2451728, 2451734, 2451735, 2451741, 2451742, 2451748, 2451749, 2451755, 2451756, 2451762, 2451763,
- 2451769, 2451770, 2451776, 2451777, 2451783, 2451784, 2451790, 2451791, 2451797, 2451798, 2451804, 2451805,
- 2451811, 2451812, 2451818, 2451819, 2451825, 2451826, 2451832, 2451833, 2451839, 2451840, 2451846, 2451847,
- 2451853, 2451854, 2451860, 2451861, 2451867, 2451868, 2451874, 2451875, 2451881, 2451882, 2451888, 2451889,
- 2451895, 2451896, 2451902, 2451903, 2451909, 2451910, 2451916, 2451917, 2451923, 2451924, 2451930, 2451931,
- 2451937, 2451938, 2451944, 2451945, 2451951, 2451952, 2451958, 2451959, 2451965, 2451966, 2451972, 2451973,
- 2451979, 2451980, 2451986, 2451987, 2451993, 2451994, 2452000, 2452001, 2452007, 2452008, 2452014, 2452015,
- 2452021, 2452022, 2452028, 2452029, 2452035, 2452036, 2452042, 2452043, 2452049, 2452050, 2452056, 2452057,
- 2452063, 2452064, 2452070, 2452071, 2452077, 2452078, 2452084, 2452085, 2452091, 2452092, 2452098, 2452099,
- 2452105, 2452106, 2452112, 2452113, 2452119, 2452120, 2452126, 2452127, 2452133, 2452134, 2452140, 2452141,
- 2452147, 2452148, 2452154, 2452155, 2452161, 2452162, 2452168, 2452169, 2452175, 2452176, 2452182, 2452183,
- 2452189, 2452190, 2452196, 2452197, 2452203, 2452204, 2452210, 2452211, 2452217, 2452218, 2452224, 2452225,
- 2452231, 2452232, 2452238, 2452239, 2452245, 2452246, 2452252, 2452253, 2452259, 2452260, 2452266, 2452267,
- 2452273, 2452274)
- group by
- ss_ticket_number,
- ss_customer_sk,
- ss_addr_sk,
- ca_city
- ) dn,
- customer,
- customer_address current_addr
-where
- ss_customer_sk = c_customer_sk
- and customer.c_current_addr_sk = current_addr.ca_address_sk
- and current_addr.ca_city <> bought_city
-order by
- c_last_name,
- c_first_name,
- ca_city,
- bought_city,
- ss_ticket_number
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q47.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q47.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q47.test
deleted file mode 100644
index 51305e9..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q47.test
+++ /dev/null
@@ -1,53 +0,0 @@
-====
----- QUERY: TPCDS-Q47
-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
- ,round(v1.avg_monthly_sales, 2) avg_monthly_sales
- ,round(v1.sum_sales, 2) sum_sales, round(v1_lag.sum_sales, 2) psum
- ,round(v1_lead.sum_sales, 2) 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;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q52.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q52.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q52.test
deleted file mode 100644
index c3a252a..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q52.test
+++ /dev/null
@@ -1,28 +0,0 @@
-====
----- QUERY: TPCDS-Q52
-select
- dt.d_year,
- item.i_brand_id brand_id,
- item.i_brand brand,
- round(sum(ss_ext_sales_price), 2) ext_price
-from
- date_dim dt,
- store_sales,
- item
-where
- dt.d_date_sk = store_sales.ss_sold_date_sk
- and store_sales.ss_item_sk = item.i_item_sk
- and item.i_manager_id = 1
- and dt.d_moy = 12
- and dt.d_year = 1998
- and ss_sold_date_sk between 2451149 and 2451179 -- added for partition pruning
-group by
- dt.d_year,
- item.i_brand,
- item.i_brand_id
-order by
- dt.d_year,
- ext_price desc,
- brand_id
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q53.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q53.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q53.test
deleted file mode 100644
index a3ac8e7..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q53.test
+++ /dev/null
@@ -1,38 +0,0 @@
-====
----- QUERY: TPCDS-Q53
-select
- *
-from
- (select
- i_manufact_id,
- round(sum(ss_sales_price), 2) sum_sales
- -- avg(sum(ss_sales_price)) over (partition by i_manufact_id) avg_quarterly_sales
- 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_month_seq in (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11)
- and ((i_category in ('Books', 'Children', 'Electronics')
- and i_class in ('personal', 'portable', 'reference', 'self-help')
- and i_brand in ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9'))
- or (i_category in ('Women', 'Music', 'Men')
- and i_class in ('accessories', 'classical', 'fragrances', 'pants')
- and i_brand in ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1')))
- and ss_sold_date_sk between 2451911 and 2452275 -- partition key filter
- group by
- i_manufact_id,
- d_qoy
- ) tmp1
--- where
--- case when avg_quarterly_sales > 0 then abs (sum_sales - avg_quarterly_sales) / avg_quarterly_sales else null end > 0.1
-order by
- -- avg_quarterly_sales,
- sum_sales,
- i_manufact_id
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q55.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q55.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q55.test
deleted file mode 100644
index 15223c5..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q55.test
+++ /dev/null
@@ -1,25 +0,0 @@
-====
----- QUERY: TPCDS-Q55
-select
- i_brand_id brand_id,
- i_brand brand,
- round(sum(ss_ext_sales_price), 2) ext_price
-from
- date_dim,
- store_sales,
- item
-where
- d_date_sk = ss_sold_date_sk
- and ss_item_sk = i_item_sk
- and i_manager_id = 36
- and d_moy = 12
- and d_year = 2001
- and ss_sold_date_sk between 2452245 and 2452275 -- partition key filter
-group by
- i_brand,
- i_brand_id
-order by
- ext_price desc,
- i_brand_id
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q59.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q59.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q59.test
deleted file mode 100644
index e4e3afb..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q59.test
+++ /dev/null
@@ -1,84 +0,0 @@
-====
----- QUERY: TPCDS-Q59
-with
- wss as
- (select
- d_week_seq,
- ss_store_sk,
- round(sum(case when (d_day_name = 'Sunday') then ss_sales_price else null end), 2) sun_sales,
- round(sum(case when (d_day_name = 'Monday') then ss_sales_price else null end), 2) mon_sales,
- round(sum(case when (d_day_name = 'Tuesday') then ss_sales_price else null end), 2) tue_sales,
- round(sum(case when (d_day_name = 'Wednesday') then ss_sales_price else null end), 2) wed_sales,
- round(sum(case when (d_day_name = 'Thursday') then ss_sales_price else null end), 2) thu_sales,
- round(sum(case when (d_day_name = 'Friday') then ss_sales_price else null end), 2) fri_sales,
- round(sum(case when (d_day_name = 'Saturday') then ss_sales_price else null end), 2) sat_sales
- from
- store_sales,
- date_dim
- where
- d_date_sk = ss_sold_date_sk
- group by
- d_week_seq,
- ss_store_sk
- )
-select
- s_store_name1,
- s_store_id1,
- d_week_seq1,
- sun_sales1 / sun_sales2,
- mon_sales1 / mon_sales2,
- tue_sales1 / tue_sales2,
- wed_sales1 / wed_sales2,
- thu_sales1 / thu_sales2,
- fri_sales1 / fri_sales2,
- sat_sales1 / sat_sales2
-from
- (select
- s_store_name s_store_name1,
- wss.d_week_seq d_week_seq1,
- s_store_id s_store_id1,
- sun_sales sun_sales1,
- mon_sales mon_sales1,
- tue_sales tue_sales1,
- wed_sales wed_sales1,
- thu_sales thu_sales1,
- fri_sales fri_sales1,
- sat_sales sat_sales1
- from
- wss,
- store,
- date_dim d
- where
- d.d_week_seq = wss.d_week_seq
- and ss_store_sk = s_store_sk
- and d_month_seq between 1185 and 1185 + 11
- ) y,
- (select
- s_store_name s_store_name2,
- wss.d_week_seq d_week_seq2,
- s_store_id s_store_id2,
- sun_sales sun_sales2,
- mon_sales mon_sales2,
- tue_sales tue_sales2,
- wed_sales wed_sales2,
- thu_sales thu_sales2,
- fri_sales fri_sales2,
- sat_sales sat_sales2
- from
- wss,
- store,
- date_dim d
- where
- d.d_week_seq = wss.d_week_seq
- and ss_store_sk = s_store_sk
- and d_month_seq between 1185 + 12 and 1185 + 23
- ) x
-where
- s_store_id1 = s_store_id2
- and d_week_seq1 = d_week_seq2 - 52
-order by
- s_store_name1,
- s_store_id1,
- d_week_seq1
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q6.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q6.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q6.test
deleted file mode 100644
index 82e3f66..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q6.test
+++ /dev/null
@@ -1,28 +0,0 @@
-====
----- QUERY: TPCDS-Q6
-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 round(avg(j.i_current_price), 2)
- 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;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q61.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q61.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q61.test
deleted file mode 100644
index c31e167..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q61.test
+++ /dev/null
@@ -1,43 +0,0 @@
-====
----- QUERY: TPCDS-Q61
-select promotions,total,cast(promotions as DOUBLE)/cast(total as DOUBLE)*100
-from
- (select round(sum(ss_ext_sales_price), 2) 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 round(sum(ss_ext_sales_price), 2) 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;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q63.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q63.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q63.test
deleted file mode 100644
index 387db25..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q63.test
+++ /dev/null
@@ -1,38 +0,0 @@
-====
----- QUERY: TPCDS-Q63
-select
- *
-from
- (select
- i_manager_id,
- round(sum(ss_sales_price), 2) sum_sales,
- round(avg(sum(ss_sales_price)) over (partition by i_manager_id), 2) avg_monthly_sales
- 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_month_seq in (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11)
- and ((i_category in ('Books', 'Children', 'Electronics')
- and i_class in ('personal', 'portable', 'refernece', 'self-help')
- and i_brand in ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9'))
- or (i_category in ('Women', 'Music', 'Men')
- and i_class in ('accessories', 'classical', 'fragrances', 'pants')
- and i_brand in ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1')))
- and ss_sold_date_sk between 2451911 and 2452275 -- partition key filter
- group by
- i_manager_id,
- d_moy
- ) tmp1
-where
-case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
-order by
- i_manager_id,
- avg_monthly_sales,
- sum_sales
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q65.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q65.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q65.test
deleted file mode 100644
index e1d3af0..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q65.test
+++ /dev/null
@@ -1,63 +0,0 @@
-====
----- QUERY: TPCDS-Q65
-select
- s_store_name,
- i_item_desc,
- sc.revenue,
- i_current_price,
- i_wholesale_cost,
- i_brand
-from
- store,
- item,
- (select
- ss_store_sk,
- round(avg(revenue), 2) as ave
- from
- (select
- ss_store_sk,
- ss_item_sk,
- round(sum(ss_sales_price), 2) as revenue
- from
- store_sales,
- date_dim
- where
- ss_sold_date_sk = d_date_sk
- and d_month_seq between 1212 and 1212 + 11
- and ss_sold_date_sk between 2451911 and 2452275 -- partition key filter
- group by
- ss_store_sk,
- ss_item_sk
- ) sa
- group by
- ss_store_sk
- ) sb,
- (select
- ss_store_sk,
- ss_item_sk,
- round(sum(ss_sales_price), 2) as revenue
- from
- store_sales,
- date_dim
- where
- ss_sold_date_sk = d_date_sk
- and d_month_seq between 1212 and 1212 + 11
- and ss_sold_date_sk between 2451911 and 2452275 -- partition key filter
- 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,
- sc.revenue, -- for consistent ordering of results
- i_current_price, -- for consistent ordering of results
- i_wholesale_cost, -- for consistent ordering of results
- i_brand -- for consistent ordering of results
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q68.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q68.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q68.test
deleted file mode 100644
index 2e0f994..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q68.test
+++ /dev/null
@@ -1,62 +0,0 @@
-====
----- QUERY: TPCDS-Q68
-select
- c_last_name,
- c_first_name,
- ca_city,
- bought_city,
- ss_ticket_number,
- extended_price,
- extended_tax,
- list_price
-from
- (select
- ss_ticket_number,
- ss_customer_sk,
- ca_city bought_city,
- round(sum(ss_ext_sales_price), 2) extended_price,
- round(sum(ss_ext_list_price), 2) list_price,
- round(sum(ss_ext_tax), 2) extended_tax
- from
- store_sales,
- date_dim,
- store,
- household_demographics,
- customer_address
- 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 store_sales.ss_addr_sk = customer_address.ca_address_sk
- -- and date_dim.d_dom between 1 and 2
- and (household_demographics.hd_dep_count = 5
- or household_demographics.hd_vehicle_count = 3)
- -- and date_dim.d_year in (1999, 1999 + 1, 1999 + 2)
- and store.s_city in ('Midway', 'Fairview')
- -- partition key filter
- -- and ss_sold_date_sk in (2451180, 2451181, 2451211, 2451212, 2451239, 2451240, 2451270, 2451271, 2451300, 2451301, 2451331,
- -- 2451332, 2451361, 2451362, 2451392, 2451393, 2451423, 2451424, 2451453, 2451454, 2451484, 2451485,
- -- 2451514, 2451515, 2451545, 2451546, 2451576, 2451577, 2451605, 2451606, 2451636, 2451637, 2451666,
- -- 2451667, 2451697, 2451698, 2451727, 2451728, 2451758, 2451759, 2451789, 2451790, 2451819, 2451820,
- -- 2451850, 2451851, 2451880, 2451881, 2451911, 2451912, 2451942, 2451943, 2451970, 2451971, 2452001,
- -- 2452002, 2452031, 2452032, 2452062, 2452063, 2452092, 2452093, 2452123, 2452124, 2452154, 2452155,
- -- 2452184, 2452185, 2452215, 2452216, 2452245, 2452246)
- and ss_sold_date_sk between 2451180 and 2451269 -- partition key filter (3 months)
- and d_date between '1999-01-01' and '1999-03-31'
- group by
- ss_ticket_number,
- ss_customer_sk,
- ss_addr_sk,
- ca_city
- ) dn,
- customer,
- customer_address current_addr
-where
- ss_customer_sk = c_customer_sk
- and customer.c_current_addr_sk = current_addr.ca_address_sk
- and current_addr.ca_city <> bought_city
-order by
- c_last_name,
- ss_ticket_number
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q7.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q7.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q7.test
deleted file mode 100644
index 0130d4c..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q7.test
+++ /dev/null
@@ -1,32 +0,0 @@
-====
----- QUERY: TPCDS-Q7
-select
- i_item_id,
- round(avg(ss_quantity), 2) agg1,
- round(avg(ss_list_price), 2) agg2,
- round(avg(ss_coupon_amt), 2) agg3,
- round(avg(ss_sales_price), 1) 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
- and ss_sold_date_sk between 2450815 and 2451179 -- partition key filter
-group by
- i_item_id
-order by
- i_item_id
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q73.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q73.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q73.test
deleted file mode 100644
index 66e2a8f..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q73.test
+++ /dev/null
@@ -1,51 +0,0 @@
-====
----- QUERY: TPCDS-Q73
-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 date_dim.d_dom between 1 and 2
- 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 date_dim.d_year in (1998, 1998 + 1, 1998 + 2)
- and store.s_county in ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County')
- -- partition key filter
- -- and ss_sold_date_sk in (2450816, 2450846, 2450847, 2450874, 2450875, 2450905, 2450906, 2450935, 2450936, 2450966, 2450967,
- -- 2450996, 2450997, 2451027, 2451028, 2451058, 2451059, 2451088, 2451089, 2451119, 2451120, 2451149,
- -- 2451150, 2451180, 2451181, 2451211, 2451212, 2451239, 2451240, 2451270, 2451271, 2451300, 2451301,
- -- 2451331, 2451332, 2451361, 2451362, 2451392, 2451393, 2451423, 2451424, 2451453, 2451454, 2451484,
- -- 2451485, 2451514, 2451515, 2451545, 2451546, 2451576, 2451577, 2451605, 2451606, 2451636, 2451637,
- -- 2451666, 2451667, 2451697, 2451698, 2451727, 2451728, 2451758, 2451759, 2451789, 2451790, 2451819,
- -- 2451820, 2451850, 2451851, 2451880, 2451881)
- and ss_sold_date_sk between 2451180 and 2451269 -- partition key filter (3 months)
- 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;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q79.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q79.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q79.test
deleted file mode 100644
index fcaca50..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q79.test
+++ /dev/null
@@ -1,61 +0,0 @@
-====
----- QUERY: TPCDS-Q79
-select
- c_last_name,
- c_first_name,
- substr(s_city, 1, 30),
- ss_ticket_number,
- amt,
- profit
-from
- (select
- ss_ticket_number,
- ss_customer_sk,
- store.s_city,
- round(sum(ss_coupon_amt), 2) amt,
- round(sum(ss_net_profit), 2) profit
- 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_dep_count = 8
- or household_demographics.hd_vehicle_count > 0)
- -- and date_dim.d_dow = 1
- -- and date_dim.d_year in (1998, 1998 + 1, 1998 + 2)
- and store.s_number_employees between 200 and 295
- -- partition key filter
- -- and ss_sold_date_sk in (2450819, 2450826, 2450833, 2450840, 2450847, 2450854, 2450861, 2450868, 2450875, 2450882, 2450889,
- -- 2450896, 2450903, 2450910, 2450917, 2450924, 2450931, 2450938, 2450945, 2450952, 2450959, 2450966, 2450973, 2450980, 2450987,
- -- 2450994, 2451001, 2451008, 2451015, 2451022, 2451029, 2451036, 2451043, 2451050, 2451057, 2451064, 2451071, 2451078, 2451085,
- -- 2451092, 2451099, 2451106, 2451113, 2451120, 2451127, 2451134, 2451141, 2451148, 2451155, 2451162, 2451169, 2451176, 2451183,
- -- 2451190, 2451197, 2451204, 2451211, 2451218, 2451225, 2451232, 2451239, 2451246, 2451253, 2451260, 2451267, 2451274, 2451281,
- -- 2451288, 2451295, 2451302, 2451309, 2451316, 2451323, 2451330, 2451337, 2451344, 2451351, 2451358, 2451365, 2451372, 2451379,
- -- 2451386, 2451393, 2451400, 2451407, 2451414, 2451421, 2451428, 2451435, 2451442, 2451449, 2451456, 2451463, 2451470, 2451477,
- -- 2451484, 2451491, 2451498, 2451505, 2451512, 2451519, 2451526, 2451533, 2451540, 2451547, 2451554, 2451561, 2451568, 2451575,
- -- 2451582, 2451589, 2451596, 2451603, 2451610, 2451617, 2451624, 2451631, 2451638, 2451645, 2451652, 2451659, 2451666, 2451673,
- -- 2451680, 2451687, 2451694, 2451701, 2451708, 2451715, 2451722, 2451729, 2451736, 2451743, 2451750, 2451757, 2451764, 2451771,
- -- 2451778, 2451785, 2451792, 2451799, 2451806, 2451813, 2451820, 2451827, 2451834, 2451841, 2451848, 2451855, 2451862, 2451869,
- -- 2451876, 2451883, 2451890, 2451897, 2451904)
- and d_date between '1999-01-01' and '1999-03-31'
- and ss_sold_date_sk between 2451180 and 2451269 -- partition key filter
- group by
- ss_ticket_number,
- ss_customer_sk,
- ss_addr_sk,
- store.s_city
- ) ms,
- customer
-where
- ss_customer_sk = c_customer_sk
-order by
- c_last_name,
- c_first_name,
- substr(s_city, 1, 30),
- profit
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q8.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q8.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q8.test
deleted file mode 100644
index be99b2f..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q8.test
+++ /dev/null
@@ -1,71 +0,0 @@
-====
----- QUERY: TPCDS-Q8
-select
- s_store_name,
- round(sum(ss_net_profit), 2)
-from
- store_sales
- join store on (store_sales.ss_store_sk = store.s_store_sk)
- join
- (select
- a.ca_zip
- from
- (select
- substr(ca_zip, 1, 5) ca_zip,
- count( *) cnt
- from
- customer_address
- join customer on (customer_address.ca_address_sk = customer.c_current_addr_sk)
- where
- c_preferred_cust_flag = 'Y'
- group by
- ca_zip
- having
- count(*) > 10
- ) a
- left semi join
- (select
- substr(ca_zip, 1, 5) ca_zip
- from
- customer_address
- where
- substr(ca_zip, 1, 5) in ('89436', '30868', '65085', '22977', '83927', '77557', '58429', '40697', '80614', '10502', '32779',
- '91137', '61265', '98294', '17921', '18427', '21203', '59362', '87291', '84093', '21505', '17184', '10866', '67898', '25797',
- '28055', '18377', '80332', '74535', '21757', '29742', '90885', '29898', '17819', '40811', '25990', '47513', '89531', '91068',
- '10391', '18846', '99223', '82637', '41368', '83658', '86199', '81625', '26696', '89338', '88425', '32200', '81427', '19053',
- '77471', '36610', '99823', '43276', '41249', '48584', '83550', '82276', '18842', '78890', '14090', '38123', '40936', '34425',
- '19850', '43286', '80072', '79188', '54191', '11395', '50497', '84861', '90733', '21068', '57666', '37119', '25004', '57835',
- '70067', '62878', '95806', '19303', '18840', '19124', '29785', '16737', '16022', '49613', '89977', '68310', '60069', '98360',
- '48649', '39050', '41793', '25002', '27413', '39736', '47208', '16515', '94808', '57648', '15009', '80015', '42961', '63982',
- '21744', '71853', '81087', '67468', '34175', '64008', '20261', '11201', '51799', '48043', '45645', '61163', '48375', '36447',
- '57042', '21218', '41100', '89951', '22745', '35851', '83326', '61125', '78298', '80752', '49858', '52940', '96976', '63792',
- '11376', '53582', '18717', '90226', '50530', '94203', '99447', '27670', '96577', '57856', '56372', '16165', '23427', '54561',
- '28806', '44439', '22926', '30123', '61451', '92397', '56979', '92309', '70873', '13355', '21801', '46346', '37562', '56458',
- '28286', '47306', '99555', '69399', '26234', '47546', '49661', '88601', '35943', '39936', '25632', '24611', '44166', '56648',
- '30379', '59785', '11110', '14329', '93815', '52226', '71381', '13842', '25612', '63294', '14664', '21077', '82626', '18799',
- '60915', '81020', '56447', '76619', '11433', '13414', '42548', '92713', '70467', '30884', '47484', '16072', '38936', '13036',
- '88376', '45539', '35901', '19506', '65690', '73957', '71850', '49231', '14276', '20005', '18384', '76615', '11635', '38177',
- '55607', '41369', '95447', '58581', '58149', '91946', '33790', '76232', '75692', '95464', '22246', '51061', '56692', '53121',
- '77209', '15482', '10688', '14868', '45907', '73520', '72666', '25734', '17959', '24677', '66446', '94627', '53535', '15560',
- '41967', '69297', '11929', '59403', '33283', '52232', '57350', '43933', '40921', '36635', '10827', '71286', '19736', '80619',
- '25251', '95042', '15526', '36496', '55854', '49124', '81980', '35375', '49157', '63512', '28944', '14946', '36503', '54010',
- '18767', '23969', '43905', '66979', '33113', '21286', '58471', '59080', '13395', '79144', '70373', '67031', '38360', '26705',
- '50906', '52406', '26066', '73146', '15884', '31897', '30045', '61068', '45550', '92454', '13376', '14354', '19770', '22928',
- '97790', '50723', '46081', '30202', '14410', '20223', '88500', '67298', '13261', '14172', '81410', '93578', '83583', '46047',
- '94167', '82564', '21156', '15799', '86709', '37931', '74703', '83103', '23054', '70470', '72008', '49247', '91911', '69998',
- '20961', '70070', '63197', '54853', '88191', '91830', '49521', '19454', '81450', '89091', '62378', '25683', '61869', '51744',
- '36580', '85778', '36871', '48121', '28810', '83712', '45486', '67393', '26935', '42393', '20132', '55349', '86057', '21309',
- '80218', '10094', '11357', '48819', '39734', '40758', '30432', '21204', '29467', '30214', '61024', '55307', '74621', '11622',
- '68908', '33032', '52868', '99194', '99900', '84936', '69036', '99149', '45013', '32895', '59004', '32322', '14933', '32936',
- '33562', '72550', '27385', '58049', '58200', '16808', '21360', '32961', '18586', '79307', '15492')
- ) b
- on (a.ca_zip = b.ca_zip)
- ) v1 on (substr(store.s_zip, 1, 2) = substr(v1.ca_zip, 1, 2))
-where
- ss_sold_date_sk between 2452276 and 2452366
-group by
- s_store_name
-order by
- s_store_name
-limit 100;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q88.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q88.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q88.test
deleted file mode 100644
index bb2beb8..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q88.test
+++ /dev/null
@@ -1,93 +0,0 @@
-====
----- QUERY: TPCDS-Q88
-select *
-from
- (select count(*) h8_30_to_9
- from store_sales, household_demographics , time_dim, store
- where ss_sold_time_sk = time_dim.t_time_sk
- and ss_hdemo_sk = household_demographics.hd_demo_sk
- and ss_store_sk = s_store_sk
- and time_dim.t_hour = 8
- and time_dim.t_minute >= 30
- and ((household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
- (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
- (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2))
- and store.s_store_name = 'ese') s1,
- (select count(*) h9_to_9_30
- from store_sales, household_demographics , time_dim, store
- where ss_sold_time_sk = time_dim.t_time_sk
- and ss_hdemo_sk = household_demographics.hd_demo_sk
- and ss_store_sk = s_store_sk
- and time_dim.t_hour = 9
- and time_dim.t_minute < 30
- and ((household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
- (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
- (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2))
- and store.s_store_name = 'ese') s2,
- (select count(*) h9_30_to_10
- from store_sales, household_demographics , time_dim, store
- where ss_sold_time_sk = time_dim.t_time_sk
- and ss_hdemo_sk = household_demographics.hd_demo_sk
- and ss_store_sk = s_store_sk
- and time_dim.t_hour = 9
- and time_dim.t_minute >= 30
- and ((household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
- (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
- (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2))
- and store.s_store_name = 'ese') s3,
- (select count(*) h10_to_10_30
- from store_sales, household_demographics , time_dim, store
- where ss_sold_time_sk = time_dim.t_time_sk
- and ss_hdemo_sk = household_demographics.hd_demo_sk
- and ss_store_sk = s_store_sk
- and time_dim.t_hour = 10
- and time_dim.t_minute < 30
- and ((household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
- (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
- (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2))
- and store.s_store_name = 'ese') s4,
- (select count(*) h10_30_to_11
- from store_sales, household_demographics , time_dim, store
- where ss_sold_time_sk = time_dim.t_time_sk
- and ss_hdemo_sk = household_demographics.hd_demo_sk
- and ss_store_sk = s_store_sk
- and time_dim.t_hour = 10
- and time_dim.t_minute >= 30
- and ((household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
- (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
- (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2))
- and store.s_store_name = 'ese') s5,
- (select count(*) h11_to_11_30
- from store_sales, household_demographics , time_dim, store
- where ss_sold_time_sk = time_dim.t_time_sk
- and ss_hdemo_sk = household_demographics.hd_demo_sk
- and ss_store_sk = s_store_sk
- and time_dim.t_hour = 11
- and time_dim.t_minute < 30
- and ((household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
- (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
- (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2))
- and store.s_store_name = 'ese') s6,
- (select count(*) h11_30_to_12
- from store_sales, household_demographics , time_dim, store
- where ss_sold_time_sk = time_dim.t_time_sk
- and ss_hdemo_sk = household_demographics.hd_demo_sk
- and ss_store_sk = s_store_sk
- and time_dim.t_hour = 11
- and time_dim.t_minute >= 30
- and ((household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
- (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
- (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2))
- and store.s_store_name = 'ese') s7,
- (select count(*) h12_to_12_30
- from store_sales, household_demographics , time_dim, store
- where ss_sold_time_sk = time_dim.t_time_sk
- and ss_hdemo_sk = household_demographics.hd_demo_sk
- and ss_store_sk = s_store_sk
- and time_dim.t_hour = 12
- and time_dim.t_minute < 30
- and ((household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
- (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
- (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2))
- and store.s_store_name = 'ese') s8;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q89.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q89.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q89.test
deleted file mode 100644
index 6677b07..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q89.test
+++ /dev/null
@@ -1,33 +0,0 @@
-====
----- QUERY: TPCDS-Q89
-/* Modifications: Added Partition Key filter because Impala does not do dynamic partition
- pruning.*/
-select * from (select i_category, i_class, i_brand, s_store_name, s_company_name
- d_moy, round(sum_sales, 2) sum_sales,
- round(avg_monthly_sales, 2) avg_monthly_sales
-from (
-select i_category, i_class, i_brand,
- s_store_name, s_company_name,
- 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)
- avg_monthly_sales
-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 in (2000) and
- ((i_category in ('Children','Music','Home') and
- i_class in ('toddlers','pop','lighting')
- )
- or (i_category in ('Jewelry','Books','Sports') and
- i_class in ('costume','travel','football')
- ))
- and ss_sold_date_sk between 2451545 and 2451910 -- partition key filter
-group by i_category, i_class, i_brand,
- s_store_name, s_company_name, d_moy) tmp1
-where 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, s_store_name
-limit 100) tmp2;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q96.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q96.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q96.test
deleted file mode 100644
index 1687bc4..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q96.test
+++ /dev/null
@@ -1,17 +0,0 @@
-====
----- QUERY: TPCDS-Q96
-SELECT
- COUNT(*) AS total
-FROM store_sales ss
-JOIN time_dim td
- ON (ss.ss_sold_time_sk = td.t_time_sk)
-JOIN household_demographics hd
- ON (ss.ss_hdemo_sk = hd.hd_demo_sk)
-JOIN store s
- ON (ss.ss_store_sk = s.s_store_sk)
-WHERE
- td.t_hour = 8
- AND td.t_minute >= 30
- AND hd.hd_dep_count = 5
- AND s.s_store_name = 'ese';
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpcds/queries/tpcds-kudu-q98.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q98.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q98.test
deleted file mode 100644
index fcd2081..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-kudu-q98.test
+++ /dev/null
@@ -1,34 +0,0 @@
-====
----- QUERY: TPCDS-Q98
-select
- i_item_desc,
- i_category,
- i_class,
- i_current_price,
- round(sum(ss_ext_sales_price), 2) as itemrevenue,
- round(sum(ss_ext_sales_price)*100/ sum(sum(ss_ext_sales_price))
- over (partition by i_class), 2) as revenueratio
-from
- store_sales,
- item,
- date_dim
-where
- ss_item_sk = i_item_sk
- and i_category in ('Jewelry', 'Sports', 'Books')
- and ss_sold_date_sk = d_date_sk
- and ss_sold_date_sk between 2451911 and 2451941
- and d_date between '2001-01-01' and '2001-01-31' -- original uses interval and the
-group by
- i_item_id,
- i_item_desc,
- i_category,
- i_class,
- i_current_price
-order by
- i_category,
- i_class,
- i_item_id,
- i_item_desc,
- revenueratio
-limit 1000;
-====
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpch/queries/tpch-kudu-q1.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpch/queries/tpch-kudu-q1.test b/testdata/workloads/tpch/queries/tpch-kudu-q1.test
deleted file mode 100644
index ffbeccc..0000000
--- a/testdata/workloads/tpch/queries/tpch-kudu-q1.test
+++ /dev/null
@@ -1,32 +0,0 @@
-====
----- QUERY: TPCH-Q1
-# Q1 - Pricing Summary Report Query
-select
- l_returnflag,
- l_linestatus,
- round(sum(l_quantity), 1) as sum_qty,
- round(sum(l_extendedprice), 1) as sum_base_price,
- round(sum(l_extendedprice * (1 - l_discount)), 1) as sum_disc_price,
- round(sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)), 1) as sum_charge,
- round(avg(l_quantity), 1) as avg_qty,
- round(avg(l_extendedprice), 1) as avg_price,
- round(avg(l_discount), 1) as avg_disc,
- count(*) as count_order
-from
- lineitem
-where
- l_shipdate <= '1998-09-02'
-group by
- l_returnflag,
- l_linestatus
-order by
- l_returnflag,
- l_linestatus
----- RESULTS
-'A','F',37734107.0,56586554400.7,53758257134.9,55909065222.8,25.5,38273.1,0.0,1478493
-'N','F',991417.0,1487504710.4,1413082168.1,1469649223.2,25.5,38284.5,0.1,38854
-'N','O',74476040.0,111701729697.7,106118230307.6,110367043872.5,25.5,38249.1,0.0,2920374
-'R','F',37719753.0,56568041380.9,53741292684.6,55889619119.8,25.5,38250.9,0.1,1478870
----- TYPES
-string, string, double, double, double, double, double, double, double, bigint
-====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/f6ad4e6b/testdata/workloads/tpch/queries/tpch-kudu-q10.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpch/queries/tpch-kudu-q10.test b/testdata/workloads/tpch/queries/tpch-kudu-q10.test
deleted file mode 100644
index be52a31..0000000
--- a/testdata/workloads/tpch/queries/tpch-kudu-q10.test
+++ /dev/null
@@ -1,60 +0,0 @@
-====
----- QUERY: TPCH-Q10
-# Q10 - Returned Item Reporting Query
-# Converted select from multiple tables to joins
-select
- c_custkey,
- c_name,
- round(sum(l_extendedprice * (1 - l_discount)), 1) as revenue,
- cast (c_acctbal as bigint),
- n_name,
- c_address,
- c_phone,
- c_comment
-from
- customer,
- orders,
- lineitem,
- nation
-where
- c_custkey = o_custkey
- and l_orderkey = o_orderkey
- and o_orderdate >= '1993-10-01'
- and o_orderdate < '1994-01-01'
- and l_returnflag = 'R'
- and c_nationkey = n_nationkey
-group by
- c_custkey,
- c_name,
- cast(c_acctbal as bigint),
- c_phone,
- n_name,
- c_address,
- c_comment
-order by
- revenue desc
-limit 20
----- RESULTS
-57040,'Customer#000057040',734235.2,632,'JAPAN','Eioyzjf4pp','22-895-641-3466','sits. slyly regular requests sleep alongside of the regular inst'
-143347,'Customer#000143347',721002.7,2557,'EGYPT','1aReFYv,Kw4','14-742-935-3718','ggle carefully enticing requests. final deposits use bold, bold pinto beans. ironic, idle re'
-60838,'Customer#000060838',679127.3,2454,'BRAZIL','64EaJ5vMAHWJlBOxJklpNc2RJiWE','12-913-494-9813',' need to boost against the slyly regular account'
-101998,'Customer#000101998',637029.6,3790,'UNITED KINGDOM','01c9CILnNtfOQYmZj','33-593-865-6378','ress foxes wake slyly after the bold excuses. ironic platelets are furiously carefully bold theodolites'
-125341,'Customer#000125341',633508.1,4983,'GERMANY','S29ODD6bceU8QSuuEJznkNaK','17-582-695-5962','arefully even depths. blithely even excuses sleep furiously. foxes use except the dependencies. ca'
-25501,'Customer#000025501',620269.8,7725,'ETHIOPIA',' W556MXuoiaYCCZamJI,Rn0B4ACUGdkQ8DZ','15-874-808-6793','he pending instructions wake carefully at the pinto beans. regular, final instructions along the slyly fina'
-115831,'Customer#000115831',596423.9,5098,'FRANCE','rFeBbEEyk dl ne7zV5fDrmiq1oK09wV7pxqCgIc','16-715-386-3788','l somas sleep. furiously final deposits wake blithely regular pinto b'
-84223,'Customer#000084223',594998.0,528,'UNITED KINGDOM','nAVZCs6BaWap rrM27N 2qBnzc5WBauxbA','33-442-824-8191',' slyly final deposits haggle regular, pending dependencies. pending escapades wake '
-54289,'Customer#000054289',585603.4,5583,'IRAN','vXCxoCsU0Bad5JQI ,oobkZ','20-834-292-4707','ely special foxes are quickly finally ironic p'
-39922,'Customer#000039922',584878.1,7321,'GERMANY','Zgy4s50l2GKN4pLDPBU8m342gIw6R','17-147-757-8036','y final requests. furiously final foxes cajole blithely special platelets. f'
-6226,'Customer#000006226',576783.8,2230,'UNITED KINGDOM','8gPu8,NPGkfyQQ0hcIYUGPIBWc,ybP5g,','33-657-701-3391','ending platelets along the express deposits cajole carefully final '
-922,'Customer#000000922',576767.5,3869,'GERMANY','Az9RFaut7NkPnc5zSD2PwHgVwr4jRzq','17-945-916-9648','luffily fluffy deposits. packages c'
-147946,'Customer#000147946',576455.1,2030,'ALGERIA','iANyZHjqhyy7Ajah0pTrYyhJ','10-886-956-3143','ithely ironic deposits haggle blithely ironic requests. quickly regu'
-115640,'Customer#000115640',569341.2,6436,'ARGENTINA','Vtgfia9qI 7EpHgecU1X','11-411-543-4901','ost slyly along the patterns; pinto be'
-73606,'Customer#000073606',568656.9,1785,'JAPAN','xuR0Tro5yChDfOCrjkd2ol','22-437-653-6966','he furiously regular ideas. slowly'
-110246,'Customer#000110246',566843.0,7763,'VIETNAM','7KzflgX MDOq7sOkI','31-943-426-9837','egular deposits serve blithely above the fl'
-142549,'Customer#000142549',563537.2,5085,'INDONESIA','ChqEoK43OysjdHbtKCp6dKqjNyvvi9','19-955-562-2398','sleep pending courts. ironic deposits against the carefully unusual platelets cajole carefully express accounts.'
-146149,'Customer#000146149',557255.0,1791,'ROMANIA','s87fvzFQpU','29-744-164-6487',' of the slyly silent accounts. quickly final accounts across the '
-52528,'Customer#000052528',556397.4,551,'ARGENTINA','NFztyTOR10UOJ','11-208-192-3205',' deposits hinder. blithely pending asymptotes breach slyly regular re'
-23431,'Customer#000023431',554269.5,3381,'ROMANIA','HgiV0phqhaIa9aydNoIlb','29-915-458-2654','nusual, even instructions: furiously stealthy n'
----- TYPES
-BIGINT, STRING, DOUBLE, BIGINT, STRING, STRING, STRING, STRING
-====
\ No newline at end of file