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 2020/08/24 16:03:32 UTC
[impala] branch master updated (cd52932 -> 3f1b147)
This is an automated email from the ASF dual-hosted git repository.
tarmstrong pushed a change to branch master
in repository https://gitbox.apache.org/repos/asf/impala.git.
from cd52932 IMPALA-4364: Query option to refresh updated HMS partitions
new d65cb05 IMPALA-7714: try to avoid be test crash in statestore
new 3f1b147 IMPALA-10034: Add remaining TPC-DS queries to workload.
The 2 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
Summary of changes:
be/src/statestore/statestore.cc | 10 +-
be/src/statestore/statestore.h | 4 +-
.../tpcds/queries/tpcds-decimal_v2-q10a.test | 3 +-
...pcds-q23-1.test => tpcds-decimal_v2-q23-1.test} | 3 +-
...pcds-q23-2.test => tpcds-decimal_v2-q23-2.test} | 1 +
...pcds-q24-1.test => tpcds-decimal_v2-q24-1.test} | 2 +-
...pcds-q24-2.test => tpcds-decimal_v2-q24-2.test} | 1 +
.../tpcds/queries/tpcds-decimal_v2-q28.test | 60 ++++++
.../queries/tpcds-decimal_v2-q35a.test} | 71 +++----
.../queries/tpcds-decimal_v2-q38-rewrite.test | 33 ---
.../{tpcds-q38.test => tpcds-decimal_v2-q38.test} | 0
.../{tpcds-q44.test => tpcds-decimal_v2-q44.test} | 3 +-
.../tpcds/queries/tpcds-decimal_v2-q49.test | 139 +++++++++++++
.../tpcds/queries/tpcds-decimal_v2-q66.test | 228 +++++++++++++++++++++
.../tpcds/queries/tpcds-decimal_v2-q8.test | 153 +++++++-------
.../{tpcds-q87.test => tpcds-decimal_v2-q87.test} | 3 +-
.../tpcds/queries/tpcds-decimal_v2-q90.test | 34 +++
.../tpcds/queries/tpcds-decimal_v2-q93.test | 125 +++++++++++
tests/query_test/test_tpcds_queries.py | 38 +++-
tests/util/parse_util.py | 2 +-
20 files changed, 763 insertions(+), 150 deletions(-)
copy testdata/workloads/tpcds/queries/{tpcds-q23-1.test => tpcds-decimal_v2-q23-1.test} (99%)
copy testdata/workloads/tpcds/queries/{tpcds-q23-2.test => tpcds-decimal_v2-q23-2.test} (99%)
copy testdata/workloads/tpcds/queries/{tpcds-q24-1.test => tpcds-decimal_v2-q24-1.test} (96%)
copy testdata/workloads/tpcds/queries/{tpcds-q24-2.test => tpcds-decimal_v2-q24-2.test} (99%)
create mode 100644 testdata/workloads/tpcds/queries/tpcds-decimal_v2-q28.test
copy testdata/workloads/{tpcds-unmodified/queries/tpcds-q35a.test => tpcds/queries/tpcds-decimal_v2-q35a.test} (83%)
delete mode 100644 testdata/workloads/tpcds/queries/tpcds-decimal_v2-q38-rewrite.test
copy testdata/workloads/tpcds/queries/{tpcds-q38.test => tpcds-decimal_v2-q38.test} (100%)
copy testdata/workloads/tpcds/queries/{tpcds-q44.test => tpcds-decimal_v2-q44.test} (99%)
create mode 100644 testdata/workloads/tpcds/queries/tpcds-decimal_v2-q49.test
create mode 100644 testdata/workloads/tpcds/queries/tpcds-decimal_v2-q66.test
copy testdata/workloads/tpcds/queries/{tpcds-q87.test => tpcds-decimal_v2-q87.test} (94%)
create mode 100644 testdata/workloads/tpcds/queries/tpcds-decimal_v2-q90.test
create mode 100644 testdata/workloads/tpcds/queries/tpcds-decimal_v2-q93.test
[impala] 01/02: IMPALA-7714: try to avoid be test crash in
statestore
Posted by ta...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
tarmstrong pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/impala.git
commit d65cb05bb8398a65d6cfb460eed4712c4b47b753
Author: Tim Armstrong <ta...@cloudera.com>
AuthorDate: Fri Aug 14 11:33:12 2020 -0700
IMPALA-7714: try to avoid be test crash in statestore
We didn't get to a clear root cause for this, so I'm going
to try two things.
First, under the theory that the problem is somehow the
destruction of the strings, convert them to char char*
which does not require destruction on process teardown.
Second, add some logging if the map lookup fails so
we can better understand what may have happened.
Change-Id: Id4363a93addb8a808d292906cac44ebd25c16889
Reviewed-on: http://gerrit.cloudera.org:8080/16341
Reviewed-by: Impala Public Jenkins <im...@cloudera.com>
Tested-by: Impala Public Jenkins <im...@cloudera.com>
---
be/src/statestore/statestore.cc | 10 +++++++---
be/src/statestore/statestore.h | 4 ++--
2 files changed, 9 insertions(+), 5 deletions(-)
diff --git a/be/src/statestore/statestore.cc b/be/src/statestore/statestore.cc
index 7df60b2..9450294 100644
--- a/be/src/statestore/statestore.cc
+++ b/be/src/statestore/statestore.cc
@@ -135,8 +135,8 @@ const Statestore::TopicEntry::Version Statestore::Subscriber::TOPIC_INITIAL_VERS
// Updates or heartbeats that miss their deadline by this much are logged.
const uint32_t DEADLINE_MISS_THRESHOLD_MS = 2000;
-const string Statestore::IMPALA_MEMBERSHIP_TOPIC("impala-membership");
-const string Statestore::IMPALA_REQUEST_QUEUE_TOPIC("impala-request-queue");
+const char* Statestore::IMPALA_MEMBERSHIP_TOPIC = "impala-membership";
+const char* Statestore::IMPALA_REQUEST_QUEUE_TOPIC = "impala-request-queue";
typedef ClientConnection<StatestoreSubscriberClientWrapper> StatestoreSubscriberConn;
@@ -392,7 +392,11 @@ void Statestore::Subscriber::SetLastTopicVersionProcessed(const TopicId& topic_i
// modified.
Topics* subscribed_topics = GetTopicsMapForId(topic_id);
Topics::iterator topic_it = subscribed_topics->find(topic_id);
- DCHECK(topic_it != subscribed_topics->end());
+ // IMPALA-7714: log warning to aid debugging in release builds without DCHECK.
+ if (UNLIKELY(topic_it == subscribed_topics->end())) {
+ LOG(ERROR) << "Couldn't find subscribed topic " << topic_id;
+ }
+ DCHECK(topic_it != subscribed_topics->end()) << topic_id;
topic_it->second.last_version.Store(version);
}
diff --git a/be/src/statestore/statestore.h b/be/src/statestore/statestore.h
index 8b95ce7..7b6844d 100644
--- a/be/src/statestore/statestore.h
+++ b/be/src/statestore/statestore.h
@@ -173,9 +173,9 @@ class Statestore : public CacheLineAligned {
/// interface for specifying prioritized topics, but for now we only have a small
/// fixed set of topics.
/// Topic tracking the set of live Impala daemon instances.
- static const std::string IMPALA_MEMBERSHIP_TOPIC;
+ static const char* IMPALA_MEMBERSHIP_TOPIC;
/// Topic tracking the state of admission control on all coordinators.
- static const std::string IMPALA_REQUEST_QUEUE_TOPIC;
+ static const char* IMPALA_REQUEST_QUEUE_TOPIC;
int32_t port() { return thrift_server_->port(); }
[impala] 02/02: IMPALA-10034: Add remaining TPC-DS queries to
workload.
Posted by ta...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
tarmstrong pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/impala.git
commit 3f1b1476af2082f716812edac9ed72eac0c903b5
Author: Shant Hovsepian <sh...@cloudera.com>
AuthorDate: Mon Aug 3 21:58:44 2020 -0400
IMPALA-10034: Add remaining TPC-DS queries to workload.
Include remaining TPC-DS queries to the testdata workload definition.
Q8 and Q38 were using non standard variants, those have been
replaced by the official query versions. Q35 is using an official
variant. Had to escape a table alias in Q90 as we treat 'AT' as a
reserved keyword.
Change-Id: Id5436689390f149694f14e6da1df624de4f5f7ad
Reviewed-on: http://gerrit.cloudera.org:8080/16280
Tested-by: Impala Public Jenkins <im...@cloudera.com>
Reviewed-by: Tim Armstrong <ta...@cloudera.com>
---
.../tpcds/queries/tpcds-decimal_v2-q10a.test | 3 +-
.../tpcds/queries/tpcds-decimal_v2-q23-1.test | 57 ++++++
.../tpcds/queries/tpcds-decimal_v2-q23-2.test | 66 ++++++
.../tpcds/queries/tpcds-decimal_v2-q24-1.test | 58 ++++++
.../tpcds/queries/tpcds-decimal_v2-q24-2.test | 60 ++++++
.../tpcds/queries/tpcds-decimal_v2-q28.test | 60 ++++++
.../tpcds/queries/tpcds-decimal_v2-q35a.test | 165 +++++++++++++++
.../queries/tpcds-decimal_v2-q38-rewrite.test | 33 ---
.../tpcds/queries/tpcds-decimal_v2-q38.test | 35 ++++
.../tpcds/queries/tpcds-decimal_v2-q44.test | 63 ++++++
.../tpcds/queries/tpcds-decimal_v2-q49.test | 139 +++++++++++++
.../tpcds/queries/tpcds-decimal_v2-q66.test | 228 +++++++++++++++++++++
.../tpcds/queries/tpcds-decimal_v2-q8.test | 153 +++++++-------
.../tpcds/queries/tpcds-decimal_v2-q87.test | 38 ++++
.../tpcds/queries/tpcds-decimal_v2-q90.test | 34 +++
.../tpcds/queries/tpcds-decimal_v2-q93.test | 125 +++++++++++
tests/query_test/test_tpcds_queries.py | 38 +++-
tests/util/parse_util.py | 2 +-
18 files changed, 1252 insertions(+), 105 deletions(-)
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q10a.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q10a.test
index 1148f0d..0061c3d 100644
--- a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q10a.test
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q10a.test
@@ -1,5 +1,6 @@
====
---- QUERY: TPCDS-Q10A
+# Official variant for lack of multiple subqueries in OR predicate.
select
cd_gender,
cd_marital_status,
@@ -67,4 +68,4 @@ limit 100;
'M','W','Unknown',1,4500,1,'Good',1,5,1,0,1,1,1
---- TYPES
STRING, STRING, STRING, BIGINT, INT, BIGINT, STRING, BIGINT, INT, BIGINT, INT, BIGINT, INT, BIGINT
-====
\ No newline at end of file
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q23-1.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q23-1.test
new file mode 100644
index 0000000..0e88f97
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q23-1.test
@@ -0,0 +1,57 @@
+====
+---- QUERY: TPCDS-Q23-1
+with frequent_ss_items as
+ (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt
+ from store_sales
+ ,date_dim
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_item_sk = i_item_sk
+ and d_year in (2000,2000+1,2000+2,2000+3)
+ group by substr(i_item_desc,1,30),i_item_sk,d_date
+ having count(*) >4),
+ max_store_sales as
+ (select max(csales) tpcds_cmax
+ from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales
+ from store_sales
+ ,customer
+ ,date_dim
+ where ss_customer_sk = c_customer_sk
+ and ss_sold_date_sk = d_date_sk
+ and d_year in (2000,2000+1,2000+2,2000+3)
+ group by c_customer_sk) x),
+ best_ss_customer as
+ (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales
+ from store_sales
+ ,customer
+ where ss_customer_sk = c_customer_sk
+ group by c_customer_sk
+ having sum(ss_quantity*ss_sales_price) > (50/100.0) * (select
+ *
+from
+ max_store_sales))
+ select sum(sales)
+ from (select cs_quantity*cs_list_price sales
+ from catalog_sales
+ ,date_dim
+ where d_year = 2000
+ and d_moy = 2
+ and cs_sold_date_sk = d_date_sk
+ and cs_item_sk in (select item_sk from frequent_ss_items)
+ and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer)
+ union all
+ select ws_quantity*ws_list_price sales
+ from web_sales
+ ,date_dim
+ where d_year = 2000
+ and d_moy = 2
+ and ws_sold_date_sk = d_date_sk
+ and ws_item_sk in (select item_sk from frequent_ss_items)
+ and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)) y
+ limit 100
+---- RESULTS
+17030.91
+---- TYPES
+DECIMAL
+====
+
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q23-2.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q23-2.test
new file mode 100644
index 0000000..7948d81
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q23-2.test
@@ -0,0 +1,66 @@
+====
+---- QUERY: TPCDS-Q23-2
+with frequent_ss_items as
+ (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt
+ from store_sales
+ ,date_dim
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_item_sk = i_item_sk
+ and d_year in (2000,2000 + 1,2000 + 2,2000 + 3)
+ group by substr(i_item_desc,1,30),i_item_sk,d_date
+ having count(*) >4),
+ max_store_sales as
+ (select max(csales) tpcds_cmax
+ from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales
+ from store_sales
+ ,customer
+ ,date_dim
+ where ss_customer_sk = c_customer_sk
+ and ss_sold_date_sk = d_date_sk
+ and d_year in (2000,2000+1,2000+2,2000+3)
+ group by c_customer_sk) x),
+ best_ss_customer as
+ (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales
+ from store_sales
+ ,customer
+ where ss_customer_sk = c_customer_sk
+ group by c_customer_sk
+ having sum(ss_quantity*ss_sales_price) > (50/100.0) * (select
+ *
+ from max_store_sales))
+ select c_last_name,c_first_name,sales
+ from (select c_last_name,c_first_name,sum(cs_quantity*cs_list_price) sales
+ from catalog_sales
+ ,customer
+ ,date_dim
+ where d_year = 2000
+ and d_moy = 2
+ and cs_sold_date_sk = d_date_sk
+ and cs_item_sk in (select item_sk from frequent_ss_items)
+ and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer)
+ and cs_bill_customer_sk = c_customer_sk
+ group by c_last_name,c_first_name
+ union all
+ select c_last_name,c_first_name,sum(ws_quantity*ws_list_price) sales
+ from web_sales
+ ,customer
+ ,date_dim
+ where d_year = 2000
+ and d_moy = 2
+ and ws_sold_date_sk = d_date_sk
+ and ws_item_sk in (select item_sk from frequent_ss_items)
+ and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)
+ and ws_bill_customer_sk = c_customer_sk
+ group by c_last_name,c_first_name) y
+ order by c_last_name,c_first_name,sales
+ limit 100;
+
+---- RESULTS
+'Brown','Monika',6031.52
+'Collins','Gordon',727.57
+'Green','Jesse',9672.96
+'NULL','Robert',598.86
+---- TYPES
+STRING, STRING, DECIMAL
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q24-1.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q24-1.test
new file mode 100644
index 0000000..68f914d
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q24-1.test
@@ -0,0 +1,58 @@
+====
+---- QUERY: TPCDS-Q24-1
+with ssales as
+(select c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size
+ ,sum(ss_net_paid) netpaid
+from store_sales
+ ,store_returns
+ ,store
+ ,item
+ ,customer
+ ,customer_address
+where ss_ticket_number = sr_ticket_number
+ and ss_item_sk = sr_item_sk
+ and ss_customer_sk = c_customer_sk
+ and ss_item_sk = i_item_sk
+ and ss_store_sk = s_store_sk
+ and c_current_addr_sk = ca_address_sk
+ and c_birth_country <> upper(ca_country)
+ and s_zip = ca_zip
+and s_market_id = 8
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size)
+select c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,sum(netpaid) paid
+from ssales
+where i_color = 'peach'
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+having sum(netpaid) > (select 0.05*avg(netpaid)
+ from ssales)
+order by c_last_name, c_first_name, s_store_name
+---- RESULTS
+'Martins','Cara','bar',241.96
+'Smallwood','Rhonda','bar',3089.28
+'Terry','Sandra','bar',509.42
+---- TYPES
+STRING, STRING, STRING, DECIMAL
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q24-2.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q24-2.test
new file mode 100644
index 0000000..3c268bc
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q24-2.test
@@ -0,0 +1,60 @@
+====
+---- QUERY: TPCDS-Q24-2
+with ssales as
+(select c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size
+ ,sum(ss_net_paid) netpaid
+from store_sales
+ ,store_returns
+ ,store
+ ,item
+ ,customer
+ ,customer_address
+where ss_ticket_number = sr_ticket_number
+ and ss_item_sk = sr_item_sk
+ and ss_customer_sk = c_customer_sk
+ and ss_item_sk = i_item_sk
+ and ss_store_sk = s_store_sk
+ and c_current_addr_sk = ca_address_sk
+ and c_birth_country <> upper(ca_country)
+ and s_zip = ca_zip
+ and s_market_id = 8
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size)
+select c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,sum(netpaid) paid
+from ssales
+where i_color = 'saddle'
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+having sum(netpaid) > (select 0.05*avg(netpaid)
+ from ssales)
+order by c_last_name, c_first_name, s_store_name;
+---- RESULTS
+'Hamlin','Heather','able',149.65
+'Martin','Harold','bar',5834.88
+'Nall','Mike','able',999.70
+'Southern','Jeannie','bar',446.31
+---- TYPES
+STRING, STRING, STRING, DECIMAL
+====
+
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q28.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q28.test
new file mode 100644
index 0000000..3f9ced0
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q28.test
@@ -0,0 +1,60 @@
+====
+---- QUERY: TPCDS-Q28
+SELECT *
+FROM
+ (SELECT avg(ss_list_price) B1_LP,
+ count(ss_list_price) B1_CNT,
+ count(DISTINCT ss_list_price) B1_CNTD
+ FROM store_sales
+ WHERE ss_quantity BETWEEN 0 AND 5
+ AND (ss_list_price BETWEEN 8 AND 8+10
+ OR ss_coupon_amt BETWEEN 459 AND 459+1000
+ OR ss_wholesale_cost BETWEEN 57 AND 57+20)) B1,
+ (SELECT avg(ss_list_price) B2_LP,
+ count(ss_list_price) B2_CNT,
+ count(DISTINCT ss_list_price) B2_CNTD
+ FROM store_sales
+ WHERE ss_quantity BETWEEN 6 AND 10
+ AND (ss_list_price BETWEEN 90 AND 90+10
+ OR ss_coupon_amt BETWEEN 2323 AND 2323+1000
+ OR ss_wholesale_cost BETWEEN 31 AND 31+20)) B2,
+ (SELECT avg(ss_list_price) B3_LP,
+ count(ss_list_price) B3_CNT,
+ count(DISTINCT ss_list_price) B3_CNTD
+ FROM store_sales
+ WHERE ss_quantity BETWEEN 11 AND 15
+ AND (ss_list_price BETWEEN 142 AND 142+10
+ OR ss_coupon_amt BETWEEN 12214 AND 12214+1000
+ OR ss_wholesale_cost BETWEEN 79 AND 79+20)) B3,
+ (SELECT avg(ss_list_price) B4_LP,
+ count(ss_list_price) B4_CNT,
+ count(DISTINCT ss_list_price) B4_CNTD
+ FROM store_sales
+ WHERE ss_quantity BETWEEN 16 AND 20
+ AND (ss_list_price BETWEEN 135 AND 135+10
+ OR ss_coupon_amt BETWEEN 6071 AND 6071+1000
+ OR ss_wholesale_cost BETWEEN 38 AND 38+20)) B4,
+ (SELECT avg(ss_list_price) B5_LP,
+ count(ss_list_price) B5_CNT,
+ count(DISTINCT ss_list_price) B5_CNTD
+ FROM store_sales
+ WHERE ss_quantity BETWEEN 21 AND 25
+ AND (ss_list_price BETWEEN 122 AND 122+10
+ OR ss_coupon_amt BETWEEN 836 AND 836+1000
+ OR ss_wholesale_cost BETWEEN 17 AND 17+20)) B5,
+ (SELECT avg(ss_list_price) B6_LP,
+ count(ss_list_price) B6_CNT,
+ count(DISTINCT ss_list_price) B6_CNTD
+ FROM store_sales
+ WHERE ss_quantity BETWEEN 26 AND 30
+ AND (ss_list_price BETWEEN 154 AND 154+10
+ OR ss_coupon_amt BETWEEN 7326 AND 7326+1000
+ OR ss_wholesale_cost BETWEEN 7 AND 7+20)) B6
+LIMIT 100;
+
+---- RESULTS
+77.717478,36142,9210,69.502663,35250,6592,134.011514,27875,9741,82.552774,31422,7678,61.826050,36100,8664,39.279556,29776,5210
+---- TYPES
+DECIMAL, BIGINT, BIGINT, DECIMAL, BIGINT, BIGINT, DECIMAL, BIGINT, BIGINT, DECIMAL, BIGINT, BIGINT, DECIMAL, BIGINT, BIGINT, DECIMAL, BIGINT, BIGINT
+====
+
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q35a.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q35a.test
new file mode 100644
index 0000000..0cb2d6c
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q35a.test
@@ -0,0 +1,165 @@
+====
+---- QUERY: TPCDS-Q35A
+# Official variant for lack of multiple subqueries in OR predicate.
+SELECT ca_state,
+ cd_gender,
+ cd_marital_status,
+ cd_dep_count,
+ count(*) cnt1,
+ min(cd_dep_count),
+ max(cd_dep_count),
+ avg(cd_dep_count),
+ cd_dep_employed_count,
+ count(*) cnt2,
+ min(cd_dep_employed_count),
+ max(cd_dep_employed_count),
+ avg(cd_dep_employed_count),
+ cd_dep_college_count,
+ count(*) cnt3,
+ min(cd_dep_college_count),
+ max(cd_dep_college_count),
+ avg(cd_dep_college_count)
+FROM customer c,
+ customer_address ca,
+ customer_demographics
+ WHERE
+ c.c_current_addr_sk = ca.ca_address_sk and
+ cd_demo_sk = c.c_current_cdemo_sk and
+ EXISTS (SELECT *
+ from store_sales,date_dim
+ WHERE c.c_customer_sk = ss_customer_sk and
+ ss_sold_date_sk = d_date_sk and
+ d_year = 2002 and
+ d_qoy < 4) and
+ EXISTS (SELECT * from
+ (SELECT ws_bill_customer_sk customsk
+ from web_sales,date_dim
+ WHERE
+ ws_sold_date_sk = d_date_sk and
+ d_year = 2002 and
+ d_qoy < 4
+ UNION ALL
+ SELECT cs_ship_customer_sk customsk
+ FROM catalog_sales,date_dim
+ WHERE
+ cs_sold_date_sk = d_date_sk and
+ d_year = 2002 and
+ d_qoy < 4)x
+ WHERE x.customsk = c.c_customer_sk)
+ GROUP BY ca_state,
+ cd_gender,
+ cd_marital_status,
+ cd_dep_count,
+ cd_dep_employed_count,
+ cd_dep_college_count
+ ORDER BY ca_state,
+ cd_gender,
+ cd_marital_status,
+ cd_dep_count,
+ cd_dep_employed_count,
+ cd_dep_college_count
+LIMIT 100
+---- RESULTS
+'AK','F','D',1,1,1,1,1,2,1,2,2,2,2,1,2,2,2
+'AK','F','D',5,1,5,5,5,3,1,3,3,3,3,1,3,3,3
+'AK','F','M',0,1,0,0,0,6,1,6,6,6,0,1,0,0,0
+'AK','F','M',4,1,4,4,4,2,1,2,2,2,0,1,0,0,0
+'AK','F','M',5,1,5,5,5,2,1,2,2,2,2,1,2,2,2
+'AK','F','S',2,1,2,2,2,3,1,3,3,3,0,1,0,0,0
+'AK','F','S',2,1,2,2,2,3,1,3,3,3,3,1,3,3,3
+'AK','F','S',2,1,2,2,2,5,1,5,5,5,2,1,2,2,2
+'AK','F','S',3,1,3,3,3,0,1,0,0,0,2,1,2,2,2
+'AK','F','S',4,1,4,4,4,1,1,1,1,1,6,1,6,6,6
+'AK','F','S',5,1,5,5,5,6,1,6,6,6,4,1,4,4,4
+'AK','F','S',6,1,6,6,6,2,1,2,2,2,4,1,4,4,4
+'AK','F','S',6,1,6,6,6,6,1,6,6,6,3,1,3,3,3
+'AK','F','U',1,1,1,1,1,5,1,5,5,5,1,1,1,1,1
+'AK','F','U',4,1,4,4,4,0,1,0,0,0,4,1,4,4,4
+'AK','F','U',5,1,5,5,5,2,1,2,2,2,5,1,5,5,5
+'AK','F','W',0,1,0,0,0,4,1,4,4,4,0,1,0,0,0
+'AK','F','W',1,1,1,1,1,1,1,1,1,1,4,1,4,4,4
+'AK','F','W',2,1,2,2,2,2,1,2,2,2,5,1,5,5,5
+'AK','F','W',4,1,4,4,4,2,1,2,2,2,2,1,2,2,2
+'AK','M','D',0,1,0,0,0,0,1,0,0,0,2,1,2,2,2
+'AK','M','D',0,1,0,0,0,5,1,5,5,5,4,1,4,4,4
+'AK','M','D',5,1,5,5,5,2,1,2,2,2,4,1,4,4,4
+'AK','M','D',6,1,6,6,6,3,1,3,3,3,6,1,6,6,6
+'AK','M','M',0,1,0,0,0,6,1,6,6,6,5,1,5,5,5
+'AK','M','M',3,1,3,3,3,3,1,3,3,3,1,1,1,1,1
+'AK','M','M',5,1,5,5,5,3,1,3,3,3,0,1,0,0,0
+'AK','M','S',0,1,0,0,0,2,1,2,2,2,4,1,4,4,4
+'AK','M','S',4,1,4,4,4,0,1,0,0,0,1,1,1,1,1
+'AK','M','S',4,1,4,4,4,0,1,0,0,0,4,1,4,4,4
+'AK','M','S',5,1,5,5,5,5,1,5,5,5,2,1,2,2,2
+'AK','M','U',0,1,0,0,0,3,1,3,3,3,5,1,5,5,5
+'AK','M','U',4,1,4,4,4,6,1,6,6,6,1,1,1,1,1
+'AK','M','W',0,1,0,0,0,6,1,6,6,6,2,1,2,2,2
+'AK','M','W',2,1,2,2,2,2,1,2,2,2,5,1,5,5,5
+'AK','M','W',2,1,2,2,2,4,1,4,4,4,6,1,6,6,6
+'AK','M','W',3,1,3,3,3,5,1,5,5,5,4,1,4,4,4
+'AK','M','W',4,1,4,4,4,6,1,6,6,6,3,1,3,3,3
+'AL','F','D',0,1,0,0,0,6,1,6,6,6,3,1,3,3,3
+'AL','F','D',2,1,2,2,2,0,1,0,0,0,6,1,6,6,6
+'AL','F','D',2,1,2,2,2,4,1,4,4,4,5,1,5,5,5
+'AL','F','D',2,1,2,2,2,6,1,6,6,6,3,1,3,3,3
+'AL','F','D',3,1,3,3,3,2,1,2,2,2,0,1,0,0,0
+'AL','F','D',4,1,4,4,4,6,1,6,6,6,6,1,6,6,6
+'AL','F','D',5,1,5,5,5,0,1,0,0,0,6,1,6,6,6
+'AL','F','D',5,1,5,5,5,2,1,2,2,2,5,1,5,5,5
+'AL','F','D',5,1,5,5,5,3,1,3,3,3,3,1,3,3,3
+'AL','F','D',5,1,5,5,5,4,1,4,4,4,1,1,1,1,1
+'AL','F','D',5,1,5,5,5,4,1,4,4,4,2,1,2,2,2
+'AL','F','M',0,1,0,0,0,3,1,3,3,3,3,1,3,3,3
+'AL','F','M',1,1,1,1,1,3,1,3,3,3,1,1,1,1,1
+'AL','F','M',1,1,1,1,1,3,1,3,3,3,3,1,3,3,3
+'AL','F','M',1,1,1,1,1,4,1,4,4,4,4,1,4,4,4
+'AL','F','M',1,1,1,1,1,6,1,6,6,6,1,1,1,1,1
+'AL','F','M',1,2,1,1,1,6,2,6,6,6,5,2,5,5,5
+'AL','F','M',2,1,2,2,2,0,1,0,0,0,1,1,1,1,1
+'AL','F','M',2,1,2,2,2,5,1,5,5,5,3,1,3,3,3
+'AL','F','M',3,1,3,3,3,3,1,3,3,3,0,1,0,0,0
+'AL','F','M',4,1,4,4,4,3,1,3,3,3,2,1,2,2,2
+'AL','F','M',4,1,4,4,4,6,1,6,6,6,3,1,3,3,3
+'AL','F','M',5,1,5,5,5,3,1,3,3,3,1,1,1,1,1
+'AL','F','M',5,1,5,5,5,5,1,5,5,5,6,1,6,6,6
+'AL','F','M',6,1,6,6,6,1,1,1,1,1,6,1,6,6,6
+'AL','F','M',6,1,6,6,6,3,1,3,3,3,4,1,4,4,4
+'AL','F','S',1,1,1,1,1,0,1,0,0,0,4,1,4,4,4
+'AL','F','S',2,1,2,2,2,1,1,1,1,1,3,1,3,3,3
+'AL','F','S',2,1,2,2,2,5,1,5,5,5,4,1,4,4,4
+'AL','F','S',2,1,2,2,2,5,1,5,5,5,5,1,5,5,5
+'AL','F','S',3,1,3,3,3,0,1,0,0,0,3,1,3,3,3
+'AL','F','S',3,1,3,3,3,2,1,2,2,2,3,1,3,3,3
+'AL','F','S',3,1,3,3,3,4,1,4,4,4,3,1,3,3,3
+'AL','F','S',3,1,3,3,3,4,1,4,4,4,4,1,4,4,4
+'AL','F','S',5,1,5,5,5,1,1,1,1,1,6,1,6,6,6
+'AL','F','U',0,2,0,0,0,1,2,1,1,1,1,2,1,1,1
+'AL','F','U',0,1,0,0,0,6,1,6,6,6,5,1,5,5,5
+'AL','F','U',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
+'AL','F','U',1,1,1,1,1,2,1,2,2,2,5,1,5,5,5
+'AL','F','U',1,1,1,1,1,3,1,3,3,3,2,1,2,2,2
+'AL','F','U',2,1,2,2,2,2,1,2,2,2,0,1,0,0,0
+'AL','F','U',5,1,5,5,5,6,1,6,6,6,1,1,1,1,1
+'AL','F','U',6,1,6,6,6,5,1,5,5,5,2,1,2,2,2
+'AL','F','W',1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
+'AL','F','W',1,1,1,1,1,3,1,3,3,3,2,1,2,2,2
+'AL','F','W',1,1,1,1,1,4,1,4,4,4,3,1,3,3,3
+'AL','F','W',2,1,2,2,2,2,1,2,2,2,5,1,5,5,5
+'AL','F','W',3,1,3,3,3,1,1,1,1,1,5,1,5,5,5
+'AL','F','W',3,1,3,3,3,2,1,2,2,2,3,1,3,3,3
+'AL','F','W',4,1,4,4,4,3,1,3,3,3,4,1,4,4,4
+'AL','F','W',5,1,5,5,5,1,1,1,1,1,3,1,3,3,3
+'AL','F','W',5,1,5,5,5,1,1,1,1,1,6,1,6,6,6
+'AL','F','W',5,1,5,5,5,5,1,5,5,5,4,1,4,4,4
+'AL','F','W',5,1,5,5,5,6,1,6,6,6,0,1,0,0,0
+'AL','M','D',1,1,1,1,1,1,1,1,1,1,2,1,2,2,2
+'AL','M','D',3,1,3,3,3,3,1,3,3,3,3,1,3,3,3
+'AL','M','D',4,1,4,4,4,3,1,3,3,3,6,1,6,6,6
+'AL','M','D',5,1,5,5,5,0,1,0,0,0,2,1,2,2,2
+'AL','M','M',2,1,2,2,2,2,1,2,2,2,1,1,1,1,1
+'AL','M','M',2,1,2,2,2,2,1,2,2,2,2,1,2,2,2
+'AL','M','M',2,1,2,2,2,4,1,4,4,4,5,1,5,5,5
+'AL','M','M',2,1,2,2,2,6,1,6,6,6,0,1,0,0,0
+---- TYPES
+STRING, STRING, STRING, INT, BIGINT, INT, INT, DOUBLE, INT, BIGINT, INT, INT, DOUBLE, INT, BIGINT, INT, INT, DOUBLE
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q38-rewrite.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q38-rewrite.test
deleted file mode 100644
index 6c0878a..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q38-rewrite.test
+++ /dev/null
@@ -1,33 +0,0 @@
-====
----- QUERY: TPCDS-Q38-REWRITE
--- This is an unofficial variant that is rewritten to use EXISTS subqueries instead of
--- INTERSECT
-select count(*) from (
- select distinct c_last_name, c_first_name, d_date
- from store_sales, date_dim dd1, customer c1
- where store_sales.ss_sold_date_sk = dd1.d_date_sk
- and store_sales.ss_customer_sk = c1.c_customer_sk
- and d_month_seq between 1189 and 1189 + 11
- and exists (
- select distinct c_last_name, c_first_name, d_date
- from catalog_sales, date_dim dd2, customer c2
- where catalog_sales.cs_sold_date_sk = dd2.d_date_sk
- and catalog_sales.cs_bill_customer_sk = c2.c_customer_sk
- and d_month_seq between 1189 and 1189 + 11
- and c1.c_last_name <=> c2.c_last_name and c1.c_first_name <=> c2.c_first_name and dd1.d_date <=> dd2.d_date
- )
- and exists (
- select distinct c_last_name, c_first_name, d_date
- from web_sales, date_dim dd3, customer c3
- where web_sales.ws_sold_date_sk = dd3.d_date_sk
- and web_sales.ws_bill_customer_sk = c3.c_customer_sk
- and d_month_seq between 1189 and 1189 + 11
- and c1.c_last_name <=> c3.c_last_name and c1.c_first_name <=> c3.c_first_name and dd1.d_date <=> dd3.d_date
- )
-) hot_cust
-limit 100
----- RESULTS
-108
----- TYPES
-BIGINT
-====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q38.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q38.test
new file mode 100644
index 0000000..3de28d1
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q38.test
@@ -0,0 +1,35 @@
+====
+---- QUERY: TPCDS-Q38
+SELECT count(*)
+FROM
+ (SELECT DISTINCT c_last_name,
+ c_first_name,
+ d_date
+ FROM store_sales,
+ date_dim,
+ customer
+ WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ AND store_sales.ss_customer_sk = customer.c_customer_sk
+ AND d_month_seq BETWEEN 1200 AND 1200 + 11 INTERSECT
+ SELECT DISTINCT c_last_name,
+ c_first_name,
+ d_date
+ FROM catalog_sales,
+ date_dim,
+ customer WHERE catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
+ AND catalog_sales.cs_bill_customer_sk = customer.c_customer_sk
+ AND d_month_seq BETWEEN 1200 AND 1200 + 11 INTERSECT
+ SELECT DISTINCT c_last_name,
+ c_first_name,
+ d_date
+ FROM web_sales,
+ date_dim,
+ customer WHERE web_sales.ws_sold_date_sk = date_dim.d_date_sk
+ AND web_sales.ws_bill_customer_sk = customer.c_customer_sk
+ AND d_month_seq BETWEEN 1200 AND 1200 + 11 ) hot_cust
+LIMIT 100
+---- RESULTS
+107
+---- TYPES
+BIGINT
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q44.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q44.test
new file mode 100644
index 0000000..4fd2f07
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q44.test
@@ -0,0 +1,63 @@
+====
+---- QUERY: TPCDS-Q44
+SELECT asceding.rnk,
+ i1.i_product_name best_performing,
+ i2.i_product_name worst_performing
+FROM
+ (SELECT *
+ FROM
+ (SELECT item_sk,
+ rank() OVER (
+ ORDER BY rank_col ASC) rnk
+ FROM
+ (SELECT ss_item_sk item_sk,
+ avg(ss_net_profit) rank_col
+ FROM store_sales ss1
+ WHERE ss_store_sk = 4
+ GROUP BY ss_item_sk
+ HAVING avg(ss_net_profit) > 0.9*
+ (SELECT avg(ss_net_profit) rank_col
+ FROM store_sales
+ WHERE ss_store_sk = 4
+ AND ss_addr_sk IS NULL
+ GROUP BY ss_store_sk))V1)V11
+ WHERE rnk < 11) asceding,
+ (SELECT *
+ FROM
+ (SELECT item_sk,
+ rank() OVER (
+ ORDER BY rank_col DESC) rnk
+ FROM
+ (SELECT ss_item_sk item_sk,
+ avg(ss_net_profit) rank_col
+ FROM store_sales ss1
+ WHERE ss_store_sk = 4
+ GROUP BY ss_item_sk
+ HAVING avg(ss_net_profit) > 0.9*
+ (SELECT avg(ss_net_profit) rank_col
+ FROM store_sales
+ WHERE ss_store_sk = 4
+ AND ss_addr_sk IS NULL
+ GROUP BY ss_store_sk))V2)V21
+ WHERE rnk < 11) descending,
+ item i1,
+ item i2
+WHERE asceding.rnk = descending.rnk
+ AND i1.i_item_sk=asceding.item_sk
+ AND i2.i_item_sk=descending.item_sk
+ORDER BY asceding.rnk
+LIMIT 100
+---- RESULTS
+1,'oughtantiprin st','callyeingbarcallyought'
+2,'barcallyprioughtought','bareseationcallyought'
+3,'ableeingantiable','barn stcallycally'
+4,'n stesebarn st','eingoughtn stn st'
+5,'antioughtationbarought','callycallybarantiought'
+6,'callyeseationantiought','NULL'
+7,'priableeseableought','eseableablepriought'
+8,'ableoughtableeseought','ationoughtantianti'
+9,'esebarableeseought','callyn stantieseought'
+10,'eingoughtn station','barcallyableought'
+---- TYPES
+BIGINT, STRING, STRING
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q49.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q49.test
new file mode 100644
index 0000000..162a48c
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q49.test
@@ -0,0 +1,139 @@
+====
+---- QUERY: TPCDS-Q49
+SELECT channel,
+ item,
+ return_ratio,
+ return_rank,
+ currency_rank
+FROM
+ (SELECT 'web' AS channel,
+ web.item,
+ web.return_ratio,
+ web.return_rank,
+ web.currency_rank
+ FROM
+ (SELECT item,
+ return_ratio,
+ currency_ratio,
+ rank() OVER (
+ ORDER BY return_ratio) AS return_rank,
+ rank() OVER (
+ ORDER BY currency_ratio) AS currency_rank
+ FROM
+ (SELECT ws.ws_item_sk AS item,
+ (cast(sum(coalesce(wr.wr_return_quantity,0)) AS decimal(15,4))/ cast(sum(coalesce(ws.ws_quantity,0)) AS decimal(15,4))) AS return_ratio,
+ (cast(sum(coalesce(wr.wr_return_amt,0)) AS decimal(15,4))/ cast(sum(coalesce(ws.ws_net_paid,0)) AS decimal(15,4))) AS currency_ratio
+ FROM web_sales ws
+ LEFT OUTER JOIN web_returns wr ON (ws.ws_order_number = wr.wr_order_number
+ AND ws.ws_item_sk = wr.wr_item_sk) ,date_dim
+ WHERE wr.wr_return_amt > 10000
+ AND ws.ws_net_profit > 1
+ AND ws.ws_net_paid > 0
+ AND ws.ws_quantity > 0
+ AND ws_sold_date_sk = d_date_sk
+ AND d_year = 2001
+ AND d_moy = 12
+ GROUP BY ws.ws_item_sk) in_web) web
+ WHERE (web.return_rank <= 10
+ OR web.currency_rank <= 10)
+ UNION SELECT 'catalog' AS channel,
+ catalog.item,
+ catalog.return_ratio,
+ catalog.return_rank,
+ catalog.currency_rank
+ FROM
+ (SELECT item,
+ return_ratio,
+ currency_ratio,
+ rank() OVER (
+ ORDER BY return_ratio) AS return_rank,
+ rank() OVER (
+ ORDER BY currency_ratio) AS currency_rank
+ FROM
+ (SELECT cs.cs_item_sk AS item,
+ (cast(sum(coalesce(cr.cr_return_quantity,0)) AS decimal(15,4))/ cast(sum(coalesce(cs.cs_quantity,0)) AS decimal(15,4))) AS return_ratio,
+ (cast(sum(coalesce(cr.cr_return_amount,0)) AS decimal(15,4))/ cast(sum(coalesce(cs.cs_net_paid,0)) AS decimal(15,4))) AS currency_ratio
+ FROM catalog_sales cs
+ LEFT OUTER JOIN catalog_returns cr ON (cs.cs_order_number = cr.cr_order_number
+ AND cs.cs_item_sk = cr.cr_item_sk) ,date_dim
+ WHERE cr.cr_return_amount > 10000
+ AND cs.cs_net_profit > 1
+ AND cs.cs_net_paid > 0
+ AND cs.cs_quantity > 0
+ AND cs_sold_date_sk = d_date_sk
+ AND d_year = 2001
+ AND d_moy = 12
+ GROUP BY cs.cs_item_sk) in_cat) CATALOG
+ WHERE (catalog.return_rank <= 10
+ OR catalog.currency_rank <=10)
+ UNION SELECT 'store' AS channel,
+ store.item,
+ store.return_ratio,
+ store.return_rank,
+ store.currency_rank
+ FROM
+ (SELECT item,
+ return_ratio,
+ currency_ratio,
+ rank() OVER (
+ ORDER BY return_ratio) AS return_rank,
+ rank() OVER (
+ ORDER BY currency_ratio) AS currency_rank
+ FROM
+ (SELECT sts.ss_item_sk AS item,
+ (cast(sum(coalesce(sr.sr_return_quantity,0)) AS decimal(15,4))/cast(sum(coalesce(sts.ss_quantity,0)) AS decimal(15,4))) AS return_ratio,
+ (cast(sum(coalesce(sr.sr_return_amt,0)) AS decimal(15,4))/cast(sum(coalesce(sts.ss_net_paid,0)) AS decimal(15,4))) AS currency_ratio
+ FROM store_sales sts
+ LEFT OUTER JOIN store_returns sr ON (sts.ss_ticket_number = sr.sr_ticket_number
+ AND sts.ss_item_sk = sr.sr_item_sk) ,date_dim
+ WHERE sr.sr_return_amt > 10000
+ AND sts.ss_net_profit > 1
+ AND sts.ss_net_paid > 0
+ AND sts.ss_quantity > 0
+ AND ss_sold_date_sk = d_date_sk
+ AND d_year = 2001
+ AND d_moy = 12
+ GROUP BY sts.ss_item_sk) in_store) store
+ WHERE (store.return_rank <= 10
+ OR store.currency_rank <= 10) ) sq1
+ORDER BY 1,
+ 4,
+ 5,
+ 2
+LIMIT 100
+---- RESULTS
+'catalog',17543,0.57142857142857142857,1,1
+'catalog',14513,0.63541666666666666667,2,2
+'catalog',12577,0.65591397849462365591,3,3
+'catalog',3411,0.71641791044776119403,4,4
+'catalog',361,0.74647887323943661972,5,5
+'catalog',8189,0.74698795180722891566,6,6
+'catalog',8929,0.76250000000000000000,7,7
+'catalog',14869,0.77173913043478260870,8,8
+'catalog',9295,0.77894736842105263158,9,9
+'catalog',16215,0.79069767441860465116,10,10
+'store',9471,0.77500000000000000000,1,1
+'store',9797,0.80000000000000000000,2,2
+'store',12641,0.81609195402298850575,3,3
+'store',15839,0.81632653061224489796,4,4
+'store',1171,0.82417582417582417582,5,5
+'store',11589,0.82653061224489795918,6,6
+'store',6661,0.92207792207792207792,7,7
+'store',13013,0.94202898550724637681,8,8
+'store',14925,0.96470588235294117647,9,9
+'store',4063,1.00000000000000000000,10,10
+'store',9029,1.00000000000000000000,10,10
+'web',7539,0.59000000000000000000,1,1
+'web',3337,0.62650602409638554217,2,2
+'web',15597,0.66197183098591549296,3,3
+'web',2915,0.69863013698630136986,4,4
+'web',11933,0.71717171717171717172,5,5
+'web',3305,0.73750000000000000000,6,16
+'web',483,0.80000000000000000000,7,6
+'web',85,0.85714285714285714286,8,7
+'web',97,0.90361445783132530120,9,8
+'web',117,0.92500000000000000000,10,9
+'web',5299,0.92708333333333333333,11,10
+---- TYPES
+STRING, BIGINT, DECIMAL, BIGINT, BIGINT
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q66.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q66.test
new file mode 100644
index 0000000..fff6b94
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q66.test
@@ -0,0 +1,228 @@
+====
+---- QUERY: TPCDS-Q66
+select
+ w_warehouse_name
+ ,w_warehouse_sq_ft
+ ,w_city
+ ,w_county
+ ,w_state
+ ,w_country
+ ,ship_carriers
+ ,year_
+ ,sum(jan_sales) as jan_sales
+ ,sum(feb_sales) as feb_sales
+ ,sum(mar_sales) as mar_sales
+ ,sum(apr_sales) as apr_sales
+ ,sum(may_sales) as may_sales
+ ,sum(jun_sales) as jun_sales
+ ,sum(jul_sales) as jul_sales
+ ,sum(aug_sales) as aug_sales
+ ,sum(sep_sales) as sep_sales
+ ,sum(oct_sales) as oct_sales
+ ,sum(nov_sales) as nov_sales
+ ,sum(dec_sales) as dec_sales
+ ,sum(jan_sales/w_warehouse_sq_ft) as jan_sales_per_sq_foot
+ ,sum(feb_sales/w_warehouse_sq_ft) as feb_sales_per_sq_foot
+ ,sum(mar_sales/w_warehouse_sq_ft) as mar_sales_per_sq_foot
+ ,sum(apr_sales/w_warehouse_sq_ft) as apr_sales_per_sq_foot
+ ,sum(may_sales/w_warehouse_sq_ft) as may_sales_per_sq_foot
+ ,sum(jun_sales/w_warehouse_sq_ft) as jun_sales_per_sq_foot
+ ,sum(jul_sales/w_warehouse_sq_ft) as jul_sales_per_sq_foot
+ ,sum(aug_sales/w_warehouse_sq_ft) as aug_sales_per_sq_foot
+ ,sum(sep_sales/w_warehouse_sq_ft) as sep_sales_per_sq_foot
+ ,sum(oct_sales/w_warehouse_sq_ft) as oct_sales_per_sq_foot
+ ,sum(nov_sales/w_warehouse_sq_ft) as nov_sales_per_sq_foot
+ ,sum(dec_sales/w_warehouse_sq_ft) as dec_sales_per_sq_foot
+ ,sum(jan_net) as jan_net
+ ,sum(feb_net) as feb_net
+ ,sum(mar_net) as mar_net
+ ,sum(apr_net) as apr_net
+ ,sum(may_net) as may_net
+ ,sum(jun_net) as jun_net
+ ,sum(jul_net) as jul_net
+ ,sum(aug_net) as aug_net
+ ,sum(sep_net) as sep_net
+ ,sum(oct_net) as oct_net
+ ,sum(nov_net) as nov_net
+ ,sum(dec_net) as dec_net
+ from (
+ select
+ w_warehouse_name
+ ,w_warehouse_sq_ft
+ ,w_city
+ ,w_county
+ ,w_state
+ ,w_country
+ ,'DHL,BARIAN' as ship_carriers
+ ,d_year as year_
+ ,sum(case when d_moy = 1
+ then ws_ext_sales_price* ws_quantity else 0 end) as jan_sales
+ ,sum(case when d_moy = 2
+ then ws_ext_sales_price* ws_quantity else 0 end) as feb_sales
+ ,sum(case when d_moy = 3
+ then ws_ext_sales_price* ws_quantity else 0 end) as mar_sales
+ ,sum(case when d_moy = 4
+ then ws_ext_sales_price* ws_quantity else 0 end) as apr_sales
+ ,sum(case when d_moy = 5
+ then ws_ext_sales_price* ws_quantity else 0 end) as may_sales
+ ,sum(case when d_moy = 6
+ then ws_ext_sales_price* ws_quantity else 0 end) as jun_sales
+ ,sum(case when d_moy = 7
+ then ws_ext_sales_price* ws_quantity else 0 end) as jul_sales
+ ,sum(case when d_moy = 8
+ then ws_ext_sales_price* ws_quantity else 0 end) as aug_sales
+ ,sum(case when d_moy = 9
+ then ws_ext_sales_price* ws_quantity else 0 end) as sep_sales
+ ,sum(case when d_moy = 10
+ then ws_ext_sales_price* ws_quantity else 0 end) as oct_sales
+ ,sum(case when d_moy = 11
+ then ws_ext_sales_price* ws_quantity else 0 end) as nov_sales
+ ,sum(case when d_moy = 12
+ then ws_ext_sales_price* ws_quantity else 0 end) as dec_sales
+ ,sum(case when d_moy = 1
+ then ws_net_paid * ws_quantity else 0 end) as jan_net
+ ,sum(case when d_moy = 2
+ then ws_net_paid * ws_quantity else 0 end) as feb_net
+ ,sum(case when d_moy = 3
+ then ws_net_paid * ws_quantity else 0 end) as mar_net
+ ,sum(case when d_moy = 4
+ then ws_net_paid * ws_quantity else 0 end) as apr_net
+ ,sum(case when d_moy = 5
+ then ws_net_paid * ws_quantity else 0 end) as may_net
+ ,sum(case when d_moy = 6
+ then ws_net_paid * ws_quantity else 0 end) as jun_net
+ ,sum(case when d_moy = 7
+ then ws_net_paid * ws_quantity else 0 end) as jul_net
+ ,sum(case when d_moy = 8
+ then ws_net_paid * ws_quantity else 0 end) as aug_net
+ ,sum(case when d_moy = 9
+ then ws_net_paid * ws_quantity else 0 end) as sep_net
+ ,sum(case when d_moy = 10
+ then ws_net_paid * ws_quantity else 0 end) as oct_net
+ ,sum(case when d_moy = 11
+ then ws_net_paid * ws_quantity else 0 end) as nov_net
+ ,sum(case when d_moy = 12
+ then ws_net_paid * ws_quantity else 0 end) as dec_net
+ from
+ web_sales
+ ,warehouse
+ ,date_dim
+ ,time_dim
+ ,ship_mode
+ where
+ ws_warehouse_sk = w_warehouse_sk
+ and ws_sold_date_sk = d_date_sk
+ and ws_sold_time_sk = t_time_sk
+ and ws_ship_mode_sk = sm_ship_mode_sk
+ and d_year = 2001
+ and t_time between 30838 and 30838+28800
+ and sm_carrier in ('DHL','BARIAN')
+ group by
+ w_warehouse_name
+ ,w_warehouse_sq_ft
+ ,w_city
+ ,w_county
+ ,w_state
+ ,w_country
+ ,d_year
+ union all
+ select
+ w_warehouse_name
+ ,w_warehouse_sq_ft
+ ,w_city
+ ,w_county
+ ,w_state
+ ,w_country
+ ,'DHL,BARIAN' as ship_carriers
+ ,d_year as year_
+ ,sum(case when d_moy = 1
+ then cs_sales_price* cs_quantity else 0 end) as jan_sales
+ ,sum(case when d_moy = 2
+ then cs_sales_price* cs_quantity else 0 end) as feb_sales
+ ,sum(case when d_moy = 3
+ then cs_sales_price* cs_quantity else 0 end) as mar_sales
+ ,sum(case when d_moy = 4
+ then cs_sales_price* cs_quantity else 0 end) as apr_sales
+ ,sum(case when d_moy = 5
+ then cs_sales_price* cs_quantity else 0 end) as may_sales
+ ,sum(case when d_moy = 6
+ then cs_sales_price* cs_quantity else 0 end) as jun_sales
+ ,sum(case when d_moy = 7
+ then cs_sales_price* cs_quantity else 0 end) as jul_sales
+ ,sum(case when d_moy = 8
+ then cs_sales_price* cs_quantity else 0 end) as aug_sales
+ ,sum(case when d_moy = 9
+ then cs_sales_price* cs_quantity else 0 end) as sep_sales
+ ,sum(case when d_moy = 10
+ then cs_sales_price* cs_quantity else 0 end) as oct_sales
+ ,sum(case when d_moy = 11
+ then cs_sales_price* cs_quantity else 0 end) as nov_sales
+ ,sum(case when d_moy = 12
+ then cs_sales_price* cs_quantity else 0 end) as dec_sales
+ ,sum(case when d_moy = 1
+ then cs_net_paid_inc_tax * cs_quantity else 0 end) as jan_net
+ ,sum(case when d_moy = 2
+ then cs_net_paid_inc_tax * cs_quantity else 0 end) as feb_net
+ ,sum(case when d_moy = 3
+ then cs_net_paid_inc_tax * cs_quantity else 0 end) as mar_net
+ ,sum(case when d_moy = 4
+ then cs_net_paid_inc_tax * cs_quantity else 0 end) as apr_net
+ ,sum(case when d_moy = 5
+ then cs_net_paid_inc_tax * cs_quantity else 0 end) as may_net
+ ,sum(case when d_moy = 6
+ then cs_net_paid_inc_tax * cs_quantity else 0 end) as jun_net
+ ,sum(case when d_moy = 7
+ then cs_net_paid_inc_tax * cs_quantity else 0 end) as jul_net
+ ,sum(case when d_moy = 8
+ then cs_net_paid_inc_tax * cs_quantity else 0 end) as aug_net
+ ,sum(case when d_moy = 9
+ then cs_net_paid_inc_tax * cs_quantity else 0 end) as sep_net
+ ,sum(case when d_moy = 10
+ then cs_net_paid_inc_tax * cs_quantity else 0 end) as oct_net
+ ,sum(case when d_moy = 11
+ then cs_net_paid_inc_tax * cs_quantity else 0 end) as nov_net
+ ,sum(case when d_moy = 12
+ then cs_net_paid_inc_tax * cs_quantity else 0 end) as dec_net
+ from
+ catalog_sales
+ ,warehouse
+ ,date_dim
+ ,time_dim
+ ,ship_mode
+ where
+ cs_warehouse_sk = w_warehouse_sk
+ and cs_sold_date_sk = d_date_sk
+ and cs_sold_time_sk = t_time_sk
+ and cs_ship_mode_sk = sm_ship_mode_sk
+ and d_year = 2001
+ and t_time between 30838 AND 30838+28800
+ and sm_carrier in ('DHL','BARIAN')
+ group by
+ w_warehouse_name
+ ,w_warehouse_sq_ft
+ ,w_city
+ ,w_county
+ ,w_state
+ ,w_country
+ ,d_year
+ ) x
+ group by
+ w_warehouse_name
+ ,w_warehouse_sq_ft
+ ,w_city
+ ,w_county
+ ,w_state
+ ,w_country
+ ,ship_carriers
+ ,year_
+ order by w_warehouse_name
+LIMIT 100
+---- RESULTS
+'Bad cards must make.',621234,'Fairview','Williamson County','TN','United States','DHL,BARIAN',2001,9506753.46,8008140.33,6116769.63,11973045.15,7756254.92,5352978.49,13733996.10,16418794.37,17212743.32,17042707.41,34304935.61,35324164.21,15.303015,12.890699,9.846160,19.273004,12.485239,8.616687,22.107605,26.429324,27.707343,27.433635,55.220635,56.861286,30534943.77,24481685.94,22178710.81,25695798.18,29954903.78,18084140.05,30805576.13,47156887.22,51158588.86,55759942.80,86253544.16,834 [...]
+'Conventional childr',977787,'Fairview','Williamson County','TN','United States','DHL,BARIAN',2001,8860645.55,14415813.74,6761497.23,11820654.76,8246260.69,6636877.49,11434492.25,25673812.14,23074206.96,21834581.94,26894900.53,33575091.74,9.061938,14.743307,6.915102,12.089192,8.433596,6.787651,11.694257,26.257060,23.598398,22.330612,27.505889,34.337838,23836085.83,32073313.37,25037904.18,22659895.86,21757401.03,24451608.10,21933001.85,55996703.43,57371880.44,62087214.51,82849910.15,88970319.31
+'Doors canno',294242,'Fairview','Williamson County','TN','United States','DHL,BARIAN',2001,6355232.31,10198920.36,10246200.97,12209716.50,8566998.28,8806316.81,9789405.60,16466584.88,26443785.61,27016047.80,33660589.67,27462468.62,21.598658,34.661674,34.822360,41.495491,29.115484,29.928823,33.269913,55.962728,89.870873,91.815743,114.397637,93.332932,22645143.09,24487254.60,24925759.42,30503655.27,26558160.29,20976233.52,29895796.09,56002198.38,53488158.53,76287235.46,82483747.59,88088266.69
+'Important issues liv',138504,'Fairview','Williamson County','TN','United States','DHL,BARIAN',2001,11748784.55,14351305.77,9896470.93,7990874.78,8879247.90,7362383.09,10011144.75,17741201.32,21346976.05,18074978.16,29675125.64,32545325.29,84.826319,103.616544,71.452600,57.694180,64.108242,53.156465,72.280546,128.091617,154.125340,130.501488,214.254647,234.977512,27204167.15,25980378.13,19943398.93,25710421.13,19484481.03,26346611.48,25075158.43,54094778.13,41066732.11,54547058.28,724659 [...]
+'NULL',NULL,'Fairview','Williamson County','TN','United States','DHL,BARIAN',2001,9597806.95,11121820.57,8670867.91,8994786.04,10887248.09,14187671.36,9732598.41,19798897.07,21007842.34,21495513.67,34795669.17,33122997.94,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,21913594.59,32518476.51,24885662.72,25698343.86,33735910.61,35527031.58,25465193.48,53623238.66,51409986.76,54159173.90,92227043.25,83435390.84
+---- TYPES
+STRING, INT, STRING, STRING, STRING, STRING, STRING, INT, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q8.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q8.test
index 91e41d2..573be36 100644
--- a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q8.test
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q8.test
@@ -1,74 +1,89 @@
====
---- QUERY: TPCDS-Q8
-select
- s_store_name,
- sum(ss_net_profit)
-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
+SELECT s_store_name,
+ sum(ss_net_profit)
+FROM store_sales,
+ date_dim,
+ store,
+ (SELECT ca_zip
+ FROM
+ (SELECT SUBSTRING(ca_zip, 1, 5) ca_zip
+ FROM customer_address
+ WHERE SUBSTRING(ca_zip, 1, 5) IN ('24128', '76232', '65084', '87816', '83926', '77556', '20548',
+ '26231', '43848', '15126', '91137', '61265', '98294', '25782', '17920',
+ '18426', '98235', '40081', '84093', '28577', '55565', '17183', '54601',
+ '67897', '22752', '86284', '18376', '38607', '45200', '21756', '29741',
+ '96765', '23932', '89360', '29839', '25989', '28898', '91068', '72550',
+ '10390', '18845', '47770', '82636', '41367', '76638', '86198', '81312',
+ '37126', '39192', '88424', '72175', '81426', '53672', '10445', '42666',
+ '66864', '66708', '41248', '48583', '82276', '18842', '78890', '49448',
+ '14089', '38122', '34425', '79077', '19849', '43285', '39861', '66162',
+ '77610', '13695', '99543', '83444', '83041', '12305', '57665', '68341',
+ '25003', '57834', '62878', '49130', '81096', '18840', '27700', '23470',
+ '50412', '21195', '16021', '76107', '71954', '68309', '18119', '98359',
+ '64544', '10336', '86379', '27068', '39736', '98569', '28915', '24206',
+ '56529', '57647', '54917', '42961', '91110', '63981', '14922', '36420',
+ '23006', '67467', '32754', '30903', '20260', '31671', '51798', '72325',
+ '85816', '68621', '13955', '36446', '41766', '68806', '16725', '15146',
+ '22744', '35850', '88086', '51649', '18270', '52867', '39972', '96976',
+ '63792', '11376', '94898', '13595', '10516', '90225', '58943', '39371',
+ '94945', '28587', '96576', '57855', '28488', '26105', '83933', '25858',
+ '34322', '44438', '73171', '30122', '34102', '22685', '71256', '78451',
+ '54364', '13354', '45375', '40558', '56458', '28286', '45266', '47305',
+ '69399', '83921', '26233', '11101', '15371', '69913', '35942', '15882',
+ '25631', '24610', '44165', '99076', '33786', '70738', '26653', '14328',
+ '72305', '62496', '22152', '10144', '64147', '48425', '14663', '21076',
+ '18799', '30450', '63089', '81019', '68893', '24996', '51200', '51211',
+ '45692', '92712', '70466', '79994', '22437', '25280', '38935', '71791',
+ '73134', '56571', '14060', '19505', '72425', '56575', '74351', '68786',
+ '51650', '20004', '18383', '76614', '11634', '18906', '15765', '41368',
+ '73241', '76698', '78567', '97189', '28545', '76231', '75691', '22246',
+ '51061', '90578', '56691', '68014', '51103', '94167', '57047', '14867',
+ '73520', '15734', '63435', '25733', '35474', '24676', '94627', '53535',
+ '17879', '15559', '53268', '59166', '11928', '59402', '33282', '45721',
+ '43933', '68101', '33515', '36634', '71286', '19736', '58058', '55253',
+ '67473', '41918', '19515', '36495', '19430', '22351', '77191', '91393',
+ '49156', '50298', '87501', '18652', '53179', '18767', '63193', '23968',
+ '65164', '68880', '21286', '72823', '58470', '67301', '13394', '31016',
+ '70372', '67030', '40604', '24317', '45748', '39127', '26065', '77721',
+ '31029', '31880', '60576', '24671', '45549', '13376', '50016', '33123',
+ '19769', '22927', '97789', '46081', '72151', '15723', '46136', '51949',
+ '68100', '96888', '64528', '14171', '79777', '28709', '11489', '25103',
+ '32213', '78668', '22245', '15798', '27156', '37930', '62971', '21337',
+ '51622', '67853', '10567', '38415', '15455', '58263', '42029', '60279',
+ '37125', '56240', '88190', '50308', '26859', '64457', '89091', '82136',
+ '62377', '36233', '63837', '58078', '17043', '30010', '60099', '28810',
+ '98025', '29178', '87343', '73273', '30469', '64034', '39516', '86057',
+ '21309', '90257', '67875', '40162', '11356', '73650', '61810', '72013',
+ '30431', '22461', '19512', '13375', '55307', '30625', '83849', '68908',
+ '26689', '96451', '38193', '46820', '88885', '84935', '69035', '83144',
+ '47537', '56616', '94983', '48033', '69952', '25486', '61547', '27385',
+ '61860', '58048', '56910', '16807', '17871', '35258', '31387', '35458',
+ '35576') INTERSECT
+ SELECT ca_zip
+ FROM
+ (SELECT SUBSTRING(ca_zip, 1, 5) ca_zip,
+ count(*) cnt
+ FROM customer_address,
+ customer
+ WHERE ca_address_sk = c_current_addr_sk
+ AND c_preferred_cust_flag='Y'
+ GROUP BY ca_zip
+ HAVING count(*) > 10)A1)A2) V1
+WHERE ss_store_sk = s_store_sk
+ AND ss_sold_date_sk = d_date_sk
+ AND d_qoy = 2
+ AND d_year = 1998
+ AND (SUBSTRING(s_zip, 1, 2) = SUBSTRING(V1.ca_zip, 1, 2))
+GROUP BY s_store_name
+ORDER BY s_store_name
+LIMIT 100
---- RESULTS
+'able',-9767751.88
+'ation',-10546549.93
+'bar',-10133672.06
+'ese',-10335181.30
+'ought',-10737659.56
---- TYPES
STRING, DECIMAL
-====
\ No newline at end of file
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q87.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q87.test
new file mode 100644
index 0000000..cadc25a
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q87.test
@@ -0,0 +1,38 @@
+====
+---- QUERY: TPCDS-Q87
+SELECT count(*)
+FROM ((SELECT DISTINCT c_last_name,
+ c_first_name,
+ d_date
+ FROM store_sales,
+ date_dim,
+ customer
+ WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ AND store_sales.ss_customer_sk = customer.c_customer_sk
+ AND d_month_seq BETWEEN 1200 AND 1200+11)
+ EXCEPT
+ (SELECT DISTINCT c_last_name,
+ c_first_name,
+ d_date
+ FROM catalog_sales,
+ date_dim,
+ customer
+ WHERE catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
+ AND catalog_sales.cs_bill_customer_sk = customer.c_customer_sk
+ AND d_month_seq BETWEEN 1200 AND 1200+11)
+ EXCEPT
+ (SELECT DISTINCT c_last_name,
+ c_first_name,
+ d_date
+ FROM web_sales,
+ date_dim,
+ customer
+ WHERE web_sales.ws_sold_date_sk = date_dim.d_date_sk
+ AND web_sales.ws_bill_customer_sk = customer.c_customer_sk
+ AND d_month_seq BETWEEN 1200 AND 1200+11)) cool_cust
+
+---- RESULTS
+47298
+---- TYPES
+BIGINT
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q90.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q90.test
new file mode 100644
index 0000000..4453374
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q90.test
@@ -0,0 +1,34 @@
+====
+---- QUERY: TPCDS-Q90
+# Had to escape the AT alias as its a keyword in Impala.
+SELECT cast(amc AS decimal(15,4))/cast(pmc AS decimal(15,4)) am_pm_ratio
+FROM
+ (SELECT count(*) amc
+ FROM web_sales,
+ household_demographics,
+ time_dim,
+ web_page
+ WHERE ws_sold_time_sk = time_dim.t_time_sk
+ AND ws_ship_hdemo_sk = household_demographics.hd_demo_sk
+ AND ws_web_page_sk = web_page.wp_web_page_sk
+ AND time_dim.t_hour BETWEEN 8 AND 8+1
+ AND household_demographics.hd_dep_count = 6
+ AND web_page.wp_char_count BETWEEN 5000 AND 5200) `AT`,
+ (SELECT count(*) pmc
+ FROM web_sales,
+ household_demographics,
+ time_dim,
+ web_page
+ WHERE ws_sold_time_sk = time_dim.t_time_sk
+ AND ws_ship_hdemo_sk = household_demographics.hd_demo_sk
+ AND ws_web_page_sk = web_page.wp_web_page_sk
+ AND time_dim.t_hour BETWEEN 19 AND 19+1
+ AND household_demographics.hd_dep_count = 6
+ AND web_page.wp_char_count BETWEEN 5000 AND 5200) pt
+ORDER BY am_pm_ratio
+LIMIT 100
+---- RESULTS
+0.61244019138755980861
+---- TYPES
+DECIMAL
+====
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q93.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q93.test
new file mode 100644
index 0000000..1bf1682
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q93.test
@@ -0,0 +1,125 @@
+====
+---- QUERY: TPCDS-Q93
+SELECT ss_customer_sk,
+ sum(act_sales) sumsales
+FROM
+ (SELECT ss_item_sk,
+ ss_ticket_number,
+ ss_customer_sk,
+ CASE
+ WHEN sr_return_quantity IS NOT NULL THEN (ss_quantity-sr_return_quantity)*ss_sales_price
+ ELSE (ss_quantity*ss_sales_price)
+ END act_sales
+ FROM store_sales
+ LEFT OUTER JOIN store_returns ON (sr_item_sk = ss_item_sk
+ AND sr_ticket_number = ss_ticket_number) ,reason
+ WHERE sr_reason_sk = r_reason_sk
+ AND r_reason_desc = 'reason 28') t
+GROUP BY ss_customer_sk
+ORDER BY sumsales,
+ ss_customer_sk
+LIMIT 100
+---- RESULTS
+148,0.00
+357,0.00
+501,0.00
+620,0.00
+1159,0.00
+1393,0.00
+2149,0.00
+2191,0.00
+2346,0.00
+2496,0.00
+3071,0.00
+3886,0.00
+3914,0.00
+4010,0.00
+4086,0.00
+4173,0.00
+4314,0.00
+4519,0.00
+5166,0.00
+5175,0.00
+5432,0.00
+5799,0.00
+5972,0.00
+5980,0.00
+6584,0.00
+6969,0.00
+7141,0.00
+7238,0.00
+7656,0.00
+7774,0.00
+8017,0.00
+9383,0.00
+9500,0.00
+9815,0.00
+10311,0.00
+10471,0.00
+10682,0.00
+10723,0.00
+11223,0.00
+11283,0.00
+11491,0.00
+11502,0.00
+11745,0.00
+11889,0.00
+12015,0.00
+12644,0.00
+13168,0.00
+13209,0.00
+13840,0.00
+14091,0.00
+14423,0.00
+14429,0.00
+14554,0.00
+14867,0.00
+15211,0.00
+15589,0.00
+15882,0.00
+15986,0.00
+16325,0.00
+16988,0.00
+17179,0.00
+17335,0.00
+18294,0.00
+18523,0.00
+18618,0.00
+18951,0.00
+19406,0.00
+19543,0.00
+19755,0.00
+19762,0.00
+20178,0.00
+20374,0.00
+20898,0.00
+20968,0.00
+20980,0.00
+21300,0.00
+21419,0.00
+21579,0.00
+23531,0.00
+23804,0.00
+24281,0.00
+24284,0.00
+24330,0.00
+24548,0.00
+25052,0.00
+25146,0.00
+25517,0.00
+25608,0.00
+26351,0.00
+26651,0.00
+26777,0.00
+26853,0.00
+26932,0.00
+27605,0.00
+27904,0.00
+28048,0.00
+28613,0.00
+28794,0.00
+28798,0.00
+28803,0.00
+---- TYPES
+INT, DECIMAL
+====
diff --git a/tests/query_test/test_tpcds_queries.py b/tests/query_test/test_tpcds_queries.py
index 75b8ce7..4cb6c3c 100644
--- a/tests/query_test/test_tpcds_queries.py
+++ b/tests/query_test/test_tpcds_queries.py
@@ -426,6 +426,18 @@ class TestTpcdsDecimalV2Query(ImpalaTestSuite):
def test_tpcds_q22a(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q22a', vector)
+ def test_tpcds_q23_1(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q23-1', vector)
+
+ def test_tpcds_q23_2(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q23-2', vector)
+
+ def test_tpcds_q24_1(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q24-1', vector)
+
+ def test_tpcds_q24_2(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q24-2', vector)
+
def test_tpcds_q25(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q25', vector)
@@ -435,6 +447,9 @@ class TestTpcdsDecimalV2Query(ImpalaTestSuite):
def test_tpcds_q27(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q27', vector)
+ def test_tpcds_q28(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q28', vector)
+
def test_tpcds_q29(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q29', vector)
@@ -453,6 +468,9 @@ class TestTpcdsDecimalV2Query(ImpalaTestSuite):
def test_tpcds_q34(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q34', vector)
+ def test_tpcds_q35(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q35a', vector)
+
def test_tpcds_q36(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q36', vector)
@@ -460,7 +478,7 @@ class TestTpcdsDecimalV2Query(ImpalaTestSuite):
self.run_test_case(self.get_workload() + '-decimal_v2-q37', vector)
def test_tpcds_q38(self, vector):
- self.run_test_case(self.get_workload() + '-decimal_v2-q38-rewrite', vector)
+ self.run_test_case(self.get_workload() + '-decimal_v2-q38', vector)
def test_tpcds_q39_1(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q39-1', vector)
@@ -480,6 +498,9 @@ class TestTpcdsDecimalV2Query(ImpalaTestSuite):
def test_tpcds_q43(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q43', vector)
+ def test_tpcds_q44(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q44', vector)
+
def test_tpcds_q45(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q45', vector)
@@ -492,6 +513,9 @@ class TestTpcdsDecimalV2Query(ImpalaTestSuite):
def test_tpcds_q48(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q48', vector)
+ def test_tpcds_q49(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q49', vector)
+
def test_tpcds_q50(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q50', vector)
@@ -543,6 +567,9 @@ class TestTpcdsDecimalV2Query(ImpalaTestSuite):
def test_tpcds_q65(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q65', vector)
+ def test_tpcds_q66(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q66', vector)
+
def test_tpcds_q67(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q67', vector)
@@ -618,18 +645,27 @@ class TestTpcdsDecimalV2Query(ImpalaTestSuite):
def test_tpcds_q86a(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q86a', vector)
+ def test_tpcds_q87(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q87', vector)
+
def test_tpcds_q88(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q88', vector)
def test_tpcds_q89(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q89', vector)
+ def test_tpcds_q90(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q90', vector)
+
def test_tpcds_q91(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q91', vector)
def test_tpcds_q92(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q92', vector)
+ def test_tpcds_q93(self, vector):
+ self.run_test_case(self.get_workload() + '-decimal_v2-q93', vector)
+
def test_tpcds_q94(self, vector):
self.run_test_case(self.get_workload() + '-decimal_v2-q94', vector)
diff --git a/tests/util/parse_util.py b/tests/util/parse_util.py
index bb19db7..97c1c5c 100644
--- a/tests/util/parse_util.py
+++ b/tests/util/parse_util.py
@@ -22,7 +22,7 @@ from datetime import datetime
# changed, and the stress test loses the ability to run the full set of queries. Set
# these constants and assert that when a workload is used, all the queries we expect to
# use are there.
-EXPECTED_TPCDS_QUERIES_COUNT = 99
+EXPECTED_TPCDS_QUERIES_COUNT = 111
EXPECTED_TPCH_NESTED_QUERIES_COUNT = 22
EXPECTED_TPCH_QUERIES_COUNT = 22
# Add the number of stress test specific queries, i.e. in files like '*-stress-*.test'