spark-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dav...@apache.org
Subject [3/3] spark git commit: [SPARK-15078] [SQL] Add all TPCDS 1.4 benchmark queries for SparkSQL
Date Fri, 20 May 2016 22:19:39 GMT
[SPARK-15078] [SQL] Add all TPCDS 1.4 benchmark queries for SparkSQL

## What changes were proposed in this pull request?

Now that SparkSQL supports all TPC-DS queries, this patch adds all 99 benchmark queries inside SparkSQL.

## How was this patch tested?

Benchmark only

Author: Sameer Agarwal <sameer@databricks.com>

Closes #13188 from sameeragarwal/tpcds-all.


Project: http://git-wip-us.apache.org/repos/asf/spark/repo
Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/a78d6ce3
Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/a78d6ce3
Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/a78d6ce3

Branch: refs/heads/master
Commit: a78d6ce376edf2a8836e01f47b9dff5371058d4c
Parents: dcac8e6
Author: Sameer Agarwal <sameer@databricks.com>
Authored: Fri May 20 15:19:28 2016 -0700
Committer: Davies Liu <davies.liu@gmail.com>
Committed: Fri May 20 15:19:28 2016 -0700

----------------------------------------------------------------------
 dev/.rat-excludes                               |    1 +
 sql/core/src/test/resources/tpcds/q1.sql        |   19 +
 sql/core/src/test/resources/tpcds/q10.sql       |   57 +
 sql/core/src/test/resources/tpcds/q11.sql       |   68 +
 sql/core/src/test/resources/tpcds/q12.sql       |   22 +
 sql/core/src/test/resources/tpcds/q13.sql       |   49 +
 sql/core/src/test/resources/tpcds/q14a.sql      |  120 ++
 sql/core/src/test/resources/tpcds/q14b.sql      |   95 ++
 sql/core/src/test/resources/tpcds/q15.sql       |   15 +
 sql/core/src/test/resources/tpcds/q16.sql       |   23 +
 sql/core/src/test/resources/tpcds/q17.sql       |   33 +
 sql/core/src/test/resources/tpcds/q18.sql       |   28 +
 sql/core/src/test/resources/tpcds/q19.sql       |   19 +
 sql/core/src/test/resources/tpcds/q2.sql        |   81 ++
 sql/core/src/test/resources/tpcds/q20.sql       |   18 +
 sql/core/src/test/resources/tpcds/q21.sql       |   25 +
 sql/core/src/test/resources/tpcds/q22.sql       |   14 +
 sql/core/src/test/resources/tpcds/q23a.sql      |   53 +
 sql/core/src/test/resources/tpcds/q23b.sql      |   68 +
 sql/core/src/test/resources/tpcds/q24a.sql      |   34 +
 sql/core/src/test/resources/tpcds/q24b.sql      |   34 +
 sql/core/src/test/resources/tpcds/q25.sql       |   33 +
 sql/core/src/test/resources/tpcds/q26.sql       |   19 +
 sql/core/src/test/resources/tpcds/q27.sql       |   21 +
 sql/core/src/test/resources/tpcds/q28.sql       |   56 +
 sql/core/src/test/resources/tpcds/q29.sql       |   32 +
 sql/core/src/test/resources/tpcds/q3.sql        |   13 +
 sql/core/src/test/resources/tpcds/q30.sql       |   35 +
 sql/core/src/test/resources/tpcds/q31.sql       |   60 +
 sql/core/src/test/resources/tpcds/q32.sql       |   15 +
 sql/core/src/test/resources/tpcds/q33.sql       |   65 +
 sql/core/src/test/resources/tpcds/q34.sql       |   32 +
 sql/core/src/test/resources/tpcds/q35.sql       |   46 +
 sql/core/src/test/resources/tpcds/q36.sql       |   26 +
 sql/core/src/test/resources/tpcds/q37.sql       |   15 +
 sql/core/src/test/resources/tpcds/q38.sql       |   30 +
 sql/core/src/test/resources/tpcds/q39a.sql      |   47 +
 sql/core/src/test/resources/tpcds/q39b.sql      |   48 +
 sql/core/src/test/resources/tpcds/q4.sql        |  120 ++
 sql/core/src/test/resources/tpcds/q40.sql       |   25 +
 sql/core/src/test/resources/tpcds/q41.sql       |   49 +
 sql/core/src/test/resources/tpcds/q42.sql       |   18 +
 sql/core/src/test/resources/tpcds/q43.sql       |   33 +
 sql/core/src/test/resources/tpcds/q44.sql       |   46 +
 sql/core/src/test/resources/tpcds/q45.sql       |   21 +
 sql/core/src/test/resources/tpcds/q46.sql       |   32 +
 sql/core/src/test/resources/tpcds/q47.sql       |   63 +
 sql/core/src/test/resources/tpcds/q48.sql       |   63 +
 sql/core/src/test/resources/tpcds/q49.sql       |  126 ++
 sql/core/src/test/resources/tpcds/q5.sql        |  131 ++
 sql/core/src/test/resources/tpcds/q50.sql       |   47 +
 sql/core/src/test/resources/tpcds/q51.sql       |   55 +
 sql/core/src/test/resources/tpcds/q52.sql       |   14 +
 sql/core/src/test/resources/tpcds/q53.sql       |   30 +
 sql/core/src/test/resources/tpcds/q54.sql       |   61 +
 sql/core/src/test/resources/tpcds/q55.sql       |   13 +
 sql/core/src/test/resources/tpcds/q56.sql       |   65 +
 sql/core/src/test/resources/tpcds/q57.sql       |   56 +
 sql/core/src/test/resources/tpcds/q58.sql       |   59 +
 sql/core/src/test/resources/tpcds/q59.sql       |   75 ++
 sql/core/src/test/resources/tpcds/q6.sql        |   21 +
 sql/core/src/test/resources/tpcds/q60.sql       |   62 +
 sql/core/src/test/resources/tpcds/q61.sql       |   33 +
 sql/core/src/test/resources/tpcds/q62.sql       |   35 +
 sql/core/src/test/resources/tpcds/q63.sql       |   31 +
 sql/core/src/test/resources/tpcds/q64.sql       |   92 ++
 sql/core/src/test/resources/tpcds/q65.sql       |   33 +
 sql/core/src/test/resources/tpcds/q66.sql       |  240 ++++
 sql/core/src/test/resources/tpcds/q67.sql       |   38 +
 sql/core/src/test/resources/tpcds/q68.sql       |   34 +
 sql/core/src/test/resources/tpcds/q69.sql       |   38 +
 sql/core/src/test/resources/tpcds/q7.sql        |   19 +
 sql/core/src/test/resources/tpcds/q70.sql       |   38 +
 sql/core/src/test/resources/tpcds/q71.sql       |   44 +
 sql/core/src/test/resources/tpcds/q72.sql       |   33 +
 sql/core/src/test/resources/tpcds/q73.sql       |   30 +
 sql/core/src/test/resources/tpcds/q74.sql       |   58 +
 sql/core/src/test/resources/tpcds/q75.sql       |   76 ++
 sql/core/src/test/resources/tpcds/q76.sql       |   47 +
 sql/core/src/test/resources/tpcds/q77.sql       |  100 ++
 sql/core/src/test/resources/tpcds/q78.sql       |   64 +
 sql/core/src/test/resources/tpcds/q79.sql       |   27 +
 sql/core/src/test/resources/tpcds/q8.sql        |   87 ++
 sql/core/src/test/resources/tpcds/q80.sql       |   94 ++
 sql/core/src/test/resources/tpcds/q81.sql       |   38 +
 sql/core/src/test/resources/tpcds/q82.sql       |   15 +
 sql/core/src/test/resources/tpcds/q83.sql       |   56 +
 sql/core/src/test/resources/tpcds/q84.sql       |   19 +
 sql/core/src/test/resources/tpcds/q85.sql       |   82 ++
 sql/core/src/test/resources/tpcds/q86.sql       |   24 +
 sql/core/src/test/resources/tpcds/q87.sql       |   28 +
 sql/core/src/test/resources/tpcds/q88.sql       |  122 ++
 sql/core/src/test/resources/tpcds/q89.sql       |   30 +
 sql/core/src/test/resources/tpcds/q9.sql        |   48 +
 sql/core/src/test/resources/tpcds/q90.sql       |   19 +
 sql/core/src/test/resources/tpcds/q91.sql       |   23 +
 sql/core/src/test/resources/tpcds/q92.sql       |   16 +
 sql/core/src/test/resources/tpcds/q93.sql       |   19 +
 sql/core/src/test/resources/tpcds/q94.sql       |   23 +
 sql/core/src/test/resources/tpcds/q95.sql       |   29 +
 sql/core/src/test/resources/tpcds/q96.sql       |   11 +
 sql/core/src/test/resources/tpcds/q97.sql       |   30 +
 sql/core/src/test/resources/tpcds/q98.sql       |   21 +
 sql/core/src/test/resources/tpcds/q99.sql       |   34 +
 .../benchmark/TPCDSQueryBenchmark.scala         |  126 ++
 .../datasources/parquet/TPCDSBenchmark.scala    | 1226 ------------------
 106 files changed, 4858 insertions(+), 1226 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/dev/.rat-excludes
----------------------------------------------------------------------
diff --git a/dev/.rat-excludes b/dev/.rat-excludes
index 67a2418..fb582de 100644
--- a/dev/.rat-excludes
+++ b/dev/.rat-excludes
@@ -98,3 +98,4 @@ spark-deps-.*
 .*csv
 .*tsv
 org.apache.spark.scheduler.ExternalClusterManager
+.*\.sql

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q1.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q1.sql b/sql/core/src/test/resources/tpcds/q1.sql
new file mode 100755
index 0000000..4d20faa
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q1.sql
@@ -0,0 +1,19 @@
+WITH customer_total_return AS
+( SELECT
+    sr_customer_sk AS ctr_customer_sk,
+    sr_store_sk AS ctr_store_sk,
+    sum(sr_return_amt) AS ctr_total_return
+  FROM store_returns, date_dim
+  WHERE sr_returned_date_sk = d_date_sk AND d_year = 2000
+  GROUP BY sr_customer_sk, sr_store_sk)
+SELECT c_customer_id
+FROM customer_total_return ctr1, store, customer
+WHERE ctr1.ctr_total_return >
+  (SELECT avg(ctr_total_return) * 1.2
+  FROM customer_total_return ctr2
+  WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk)
+  AND s_store_sk = ctr1.ctr_store_sk
+  AND s_state = 'TN'
+  AND ctr1.ctr_customer_sk = c_customer_sk
+ORDER BY c_customer_id
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q10.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q10.sql b/sql/core/src/test/resources/tpcds/q10.sql
new file mode 100755
index 0000000..5500e1a
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q10.sql
@@ -0,0 +1,57 @@
+SELECT
+  cd_gender,
+  cd_marital_status,
+  cd_education_status,
+  count(*) cnt1,
+  cd_purchase_estimate,
+  count(*) cnt2,
+  cd_credit_rating,
+  count(*) cnt3,
+  cd_dep_count,
+  count(*) cnt4,
+  cd_dep_employed_count,
+  count(*) cnt5,
+  cd_dep_college_count,
+  count(*) cnt6
+FROM
+  customer c, customer_address ca, customer_demographics
+WHERE
+  c.c_current_addr_sk = ca.ca_address_sk AND
+    ca_county IN ('Rush County', 'Toole County', 'Jefferson County',
+                  'Dona Ana County', 'La Porte County') 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_moy BETWEEN 1 AND 1 + 3) AND
+    (exists(SELECT *
+            FROM web_sales, date_dim
+            WHERE c.c_customer_sk = ws_bill_customer_sk AND
+              ws_sold_date_sk = d_date_sk AND
+              d_year = 2002 AND
+              d_moy BETWEEN 1 AND 1 + 3) OR
+      exists(SELECT *
+             FROM catalog_sales, date_dim
+             WHERE c.c_customer_sk = cs_ship_customer_sk AND
+               cs_sold_date_sk = d_date_sk AND
+               d_year = 2002 AND
+               d_moy BETWEEN 1 AND 1 + 3))
+GROUP BY cd_gender,
+  cd_marital_status,
+  cd_education_status,
+  cd_purchase_estimate,
+  cd_credit_rating,
+  cd_dep_count,
+  cd_dep_employed_count,
+  cd_dep_college_count
+ORDER BY cd_gender,
+  cd_marital_status,
+  cd_education_status,
+  cd_purchase_estimate,
+  cd_credit_rating,
+  cd_dep_count,
+  cd_dep_employed_count,
+  cd_dep_college_count
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q11.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q11.sql b/sql/core/src/test/resources/tpcds/q11.sql
new file mode 100755
index 0000000..3618fb1
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q11.sql
@@ -0,0 +1,68 @@
+WITH year_total AS (
+  SELECT
+    c_customer_id customer_id,
+    c_first_name customer_first_name,
+    c_last_name customer_last_name,
+    c_preferred_cust_flag customer_preferred_cust_flag,
+    c_birth_country customer_birth_country,
+    c_login customer_login,
+    c_email_address customer_email_address,
+    d_year dyear,
+    sum(ss_ext_list_price - ss_ext_discount_amt) year_total,
+    's' sale_type
+  FROM customer, store_sales, date_dim
+  WHERE c_customer_sk = ss_customer_sk
+    AND ss_sold_date_sk = d_date_sk
+  GROUP BY c_customer_id
+    , c_first_name
+    , c_last_name
+    , d_year
+    , c_preferred_cust_flag
+    , c_birth_country
+    , c_login
+    , c_email_address
+    , d_year
+  UNION ALL
+  SELECT
+    c_customer_id customer_id,
+    c_first_name customer_first_name,
+    c_last_name customer_last_name,
+    c_preferred_cust_flag customer_preferred_cust_flag,
+    c_birth_country customer_birth_country,
+    c_login customer_login,
+    c_email_address customer_email_address,
+    d_year dyear,
+    sum(ws_ext_list_price - ws_ext_discount_amt) year_total,
+    'w' sale_type
+  FROM customer, web_sales, date_dim
+  WHERE c_customer_sk = ws_bill_customer_sk
+    AND ws_sold_date_sk = d_date_sk
+  GROUP BY
+    c_customer_id, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_country,
+    c_login, c_email_address, d_year)
+SELECT t_s_secyear.customer_preferred_cust_flag
+FROM year_total t_s_firstyear
+  , year_total t_s_secyear
+  , year_total t_w_firstyear
+  , year_total t_w_secyear
+WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id
+  AND t_s_firstyear.customer_id = t_w_secyear.customer_id
+  AND t_s_firstyear.customer_id = t_w_firstyear.customer_id
+  AND t_s_firstyear.sale_type = 's'
+  AND t_w_firstyear.sale_type = 'w'
+  AND t_s_secyear.sale_type = 's'
+  AND t_w_secyear.sale_type = 'w'
+  AND t_s_firstyear.dyear = 2001
+  AND t_s_secyear.dyear = 2001 + 1
+  AND t_w_firstyear.dyear = 2001
+  AND t_w_secyear.dyear = 2001 + 1
+  AND t_s_firstyear.year_total > 0
+  AND t_w_firstyear.year_total > 0
+  AND CASE WHEN t_w_firstyear.year_total > 0
+  THEN t_w_secyear.year_total / t_w_firstyear.year_total
+      ELSE NULL END
+  > CASE WHEN t_s_firstyear.year_total > 0
+  THEN t_s_secyear.year_total / t_s_firstyear.year_total
+    ELSE NULL END
+ORDER BY t_s_secyear.customer_preferred_cust_flag
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q12.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q12.sql b/sql/core/src/test/resources/tpcds/q12.sql
new file mode 100755
index 0000000..0382737
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q12.sql
@@ -0,0 +1,22 @@
+SELECT
+  i_item_desc,
+  i_category,
+  i_class,
+  i_current_price,
+  sum(ws_ext_sales_price) AS itemrevenue,
+  sum(ws_ext_sales_price) * 100 / sum(sum(ws_ext_sales_price))
+  OVER
+  (PARTITION BY i_class) AS revenueratio
+FROM
+  web_sales, item, date_dim
+WHERE
+  ws_item_sk = i_item_sk
+    AND i_category IN ('Sports', 'Books', 'Home')
+    AND ws_sold_date_sk = d_date_sk
+    AND d_date BETWEEN cast('1999-02-22' AS DATE)
+  AND (cast('1999-02-22' AS DATE) + INTERVAL 30 days)
+GROUP BY
+  i_item_id, i_item_desc, i_category, i_class, i_current_price
+ORDER BY
+  i_category, i_class, i_item_id, i_item_desc, revenueratio
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q13.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q13.sql b/sql/core/src/test/resources/tpcds/q13.sql
new file mode 100755
index 0000000..32dc9e2
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q13.sql
@@ -0,0 +1,49 @@
+SELECT
+  avg(ss_quantity),
+  avg(ss_ext_sales_price),
+  avg(ss_ext_wholesale_cost),
+  sum(ss_ext_wholesale_cost)
+FROM store_sales
+  , store
+  , customer_demographics
+  , household_demographics
+  , customer_address
+  , date_dim
+WHERE s_store_sk = ss_store_sk
+  AND ss_sold_date_sk = d_date_sk AND d_year = 2001
+  AND ((ss_hdemo_sk = hd_demo_sk
+  AND cd_demo_sk = ss_cdemo_sk
+  AND cd_marital_status = 'M'
+  AND cd_education_status = 'Advanced Degree'
+  AND ss_sales_price BETWEEN 100.00 AND 150.00
+  AND hd_dep_count = 3
+) OR
+  (ss_hdemo_sk = hd_demo_sk
+    AND cd_demo_sk = ss_cdemo_sk
+    AND cd_marital_status = 'S'
+    AND cd_education_status = 'College'
+    AND ss_sales_price BETWEEN 50.00 AND 100.00
+    AND hd_dep_count = 1
+  ) OR
+  (ss_hdemo_sk = hd_demo_sk
+    AND cd_demo_sk = ss_cdemo_sk
+    AND cd_marital_status = 'W'
+    AND cd_education_status = '2 yr Degree'
+    AND ss_sales_price BETWEEN 150.00 AND 200.00
+    AND hd_dep_count = 1
+  ))
+  AND ((ss_addr_sk = ca_address_sk
+  AND ca_country = 'United States'
+  AND ca_state IN ('TX', 'OH', 'TX')
+  AND ss_net_profit BETWEEN 100 AND 200
+) OR
+  (ss_addr_sk = ca_address_sk
+    AND ca_country = 'United States'
+    AND ca_state IN ('OR', 'NM', 'KY')
+    AND ss_net_profit BETWEEN 150 AND 300
+  ) OR
+  (ss_addr_sk = ca_address_sk
+    AND ca_country = 'United States'
+    AND ca_state IN ('VA', 'TX', 'MS')
+    AND ss_net_profit BETWEEN 50 AND 250
+  ))

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q14a.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q14a.sql b/sql/core/src/test/resources/tpcds/q14a.sql
new file mode 100755
index 0000000..954ddd4
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q14a.sql
@@ -0,0 +1,120 @@
+WITH cross_items AS
+(SELECT i_item_sk ss_item_sk
+  FROM item,
+    (SELECT
+      iss.i_brand_id brand_id,
+      iss.i_class_id class_id,
+      iss.i_category_id category_id
+    FROM store_sales, item iss, date_dim d1
+    WHERE ss_item_sk = iss.i_item_sk
+      AND ss_sold_date_sk = d1.d_date_sk
+      AND d1.d_year BETWEEN 1999 AND 1999 + 2
+    INTERSECT
+    SELECT
+      ics.i_brand_id,
+      ics.i_class_id,
+      ics.i_category_id
+    FROM catalog_sales, item ics, date_dim d2
+    WHERE cs_item_sk = ics.i_item_sk
+      AND cs_sold_date_sk = d2.d_date_sk
+      AND d2.d_year BETWEEN 1999 AND 1999 + 2
+    INTERSECT
+    SELECT
+      iws.i_brand_id,
+      iws.i_class_id,
+      iws.i_category_id
+    FROM web_sales, item iws, date_dim d3
+    WHERE ws_item_sk = iws.i_item_sk
+      AND ws_sold_date_sk = d3.d_date_sk
+      AND d3.d_year BETWEEN 1999 AND 1999 + 2) x
+  WHERE i_brand_id = brand_id
+    AND i_class_id = class_id
+    AND i_category_id = category_id
+),
+    avg_sales AS
+  (SELECT avg(quantity * list_price) average_sales
+  FROM (
+         SELECT
+           ss_quantity quantity,
+           ss_list_price list_price
+         FROM store_sales, date_dim
+         WHERE ss_sold_date_sk = d_date_sk
+           AND d_year BETWEEN 1999 AND 2001
+         UNION ALL
+         SELECT
+           cs_quantity quantity,
+           cs_list_price list_price
+         FROM catalog_sales, date_dim
+         WHERE cs_sold_date_sk = d_date_sk
+           AND d_year BETWEEN 1999 AND 1999 + 2
+         UNION ALL
+         SELECT
+           ws_quantity quantity,
+           ws_list_price list_price
+         FROM web_sales, date_dim
+         WHERE ws_sold_date_sk = d_date_sk
+           AND d_year BETWEEN 1999 AND 1999 + 2) x)
+SELECT
+  channel,
+  i_brand_id,
+  i_class_id,
+  i_category_id,
+  sum(sales),
+  sum(number_sales)
+FROM (
+       SELECT
+         'store' channel,
+         i_brand_id,
+         i_class_id,
+         i_category_id,
+         sum(ss_quantity * ss_list_price) sales,
+         count(*) number_sales
+       FROM store_sales, item, date_dim
+       WHERE ss_item_sk IN (SELECT ss_item_sk
+       FROM cross_items)
+         AND ss_item_sk = i_item_sk
+         AND ss_sold_date_sk = d_date_sk
+         AND d_year = 1999 + 2
+         AND d_moy = 11
+       GROUP BY i_brand_id, i_class_id, i_category_id
+       HAVING sum(ss_quantity * ss_list_price) > (SELECT average_sales
+       FROM avg_sales)
+       UNION ALL
+       SELECT
+         'catalog' channel,
+         i_brand_id,
+         i_class_id,
+         i_category_id,
+         sum(cs_quantity * cs_list_price) sales,
+         count(*) number_sales
+       FROM catalog_sales, item, date_dim
+       WHERE cs_item_sk IN (SELECT ss_item_sk
+       FROM cross_items)
+         AND cs_item_sk = i_item_sk
+         AND cs_sold_date_sk = d_date_sk
+         AND d_year = 1999 + 2
+         AND d_moy = 11
+       GROUP BY i_brand_id, i_class_id, i_category_id
+       HAVING sum(cs_quantity * cs_list_price) > (SELECT average_sales FROM avg_sales)
+       UNION ALL
+       SELECT
+         'web' channel,
+         i_brand_id,
+         i_class_id,
+         i_category_id,
+         sum(ws_quantity * ws_list_price) sales,
+         count(*) number_sales
+       FROM web_sales, item, date_dim
+       WHERE ws_item_sk IN (SELECT ss_item_sk
+       FROM cross_items)
+         AND ws_item_sk = i_item_sk
+         AND ws_sold_date_sk = d_date_sk
+         AND d_year = 1999 + 2
+         AND d_moy = 11
+       GROUP BY i_brand_id, i_class_id, i_category_id
+       HAVING sum(ws_quantity * ws_list_price) > (SELECT average_sales
+       FROM avg_sales)
+     ) y
+GROUP BY ROLLUP (channel, i_brand_id, i_class_id, i_category_id)
+ORDER BY channel, i_brand_id, i_class_id, i_category_id
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q14b.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q14b.sql b/sql/core/src/test/resources/tpcds/q14b.sql
new file mode 100755
index 0000000..929a848
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q14b.sql
@@ -0,0 +1,95 @@
+WITH cross_items AS
+(SELECT i_item_sk ss_item_sk
+  FROM item,
+    (SELECT
+      iss.i_brand_id brand_id,
+      iss.i_class_id class_id,
+      iss.i_category_id category_id
+    FROM store_sales, item iss, date_dim d1
+    WHERE ss_item_sk = iss.i_item_sk
+      AND ss_sold_date_sk = d1.d_date_sk
+      AND d1.d_year BETWEEN 1999 AND 1999 + 2
+    INTERSECT
+    SELECT
+      ics.i_brand_id,
+      ics.i_class_id,
+      ics.i_category_id
+    FROM catalog_sales, item ics, date_dim d2
+    WHERE cs_item_sk = ics.i_item_sk
+      AND cs_sold_date_sk = d2.d_date_sk
+      AND d2.d_year BETWEEN 1999 AND 1999 + 2
+    INTERSECT
+    SELECT
+      iws.i_brand_id,
+      iws.i_class_id,
+      iws.i_category_id
+    FROM web_sales, item iws, date_dim d3
+    WHERE ws_item_sk = iws.i_item_sk
+      AND ws_sold_date_sk = d3.d_date_sk
+      AND d3.d_year BETWEEN 1999 AND 1999 + 2) x
+  WHERE i_brand_id = brand_id
+    AND i_class_id = class_id
+    AND i_category_id = category_id
+),
+    avg_sales AS
+  (SELECT avg(quantity * list_price) average_sales
+  FROM (SELECT
+          ss_quantity quantity,
+          ss_list_price list_price
+        FROM store_sales, date_dim
+        WHERE ss_sold_date_sk = d_date_sk AND d_year BETWEEN 1999 AND 1999 + 2
+        UNION ALL
+        SELECT
+          cs_quantity quantity,
+          cs_list_price list_price
+        FROM catalog_sales, date_dim
+        WHERE cs_sold_date_sk = d_date_sk AND d_year BETWEEN 1999 AND 1999 + 2
+        UNION ALL
+        SELECT
+          ws_quantity quantity,
+          ws_list_price list_price
+        FROM web_sales, date_dim
+        WHERE ws_sold_date_sk = d_date_sk AND d_year BETWEEN 1999 AND 1999 + 2) x)
+SELECT *
+FROM
+  (SELECT
+    'store' channel,
+    i_brand_id,
+    i_class_id,
+    i_category_id,
+    sum(ss_quantity * ss_list_price) sales,
+    count(*) number_sales
+  FROM store_sales, item, date_dim
+  WHERE ss_item_sk IN (SELECT ss_item_sk
+  FROM cross_items)
+    AND ss_item_sk = i_item_sk
+    AND ss_sold_date_sk = d_date_sk
+    AND d_week_seq = (SELECT d_week_seq
+  FROM date_dim
+  WHERE d_year = 1999 + 1 AND d_moy = 12 AND d_dom = 11)
+  GROUP BY i_brand_id, i_class_id, i_category_id
+  HAVING sum(ss_quantity * ss_list_price) > (SELECT average_sales
+  FROM avg_sales)) this_year,
+  (SELECT
+    'store' channel,
+    i_brand_id,
+    i_class_id,
+    i_category_id,
+    sum(ss_quantity * ss_list_price) sales,
+    count(*) number_sales
+  FROM store_sales, item, date_dim
+  WHERE ss_item_sk IN (SELECT ss_item_sk
+  FROM cross_items)
+    AND ss_item_sk = i_item_sk
+    AND ss_sold_date_sk = d_date_sk
+    AND d_week_seq = (SELECT d_week_seq
+  FROM date_dim
+  WHERE d_year = 1999 AND d_moy = 12 AND d_dom = 11)
+  GROUP BY i_brand_id, i_class_id, i_category_id
+  HAVING sum(ss_quantity * ss_list_price) > (SELECT average_sales
+  FROM avg_sales)) last_year
+WHERE this_year.i_brand_id = last_year.i_brand_id
+  AND this_year.i_class_id = last_year.i_class_id
+  AND this_year.i_category_id = last_year.i_category_id
+ORDER BY this_year.channel, this_year.i_brand_id, this_year.i_class_id, this_year.i_category_id
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q15.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q15.sql b/sql/core/src/test/resources/tpcds/q15.sql
new file mode 100755
index 0000000..b8182e2
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q15.sql
@@ -0,0 +1,15 @@
+SELECT
+  ca_zip,
+  sum(cs_sales_price)
+FROM catalog_sales, customer, customer_address, date_dim
+WHERE cs_bill_customer_sk = c_customer_sk
+  AND c_current_addr_sk = ca_address_sk
+  AND (substr(ca_zip, 1, 5) IN ('85669', '86197', '88274', '83405', '86475',
+                                '85392', '85460', '80348', '81792')
+  OR ca_state IN ('CA', 'WA', 'GA')
+  OR cs_sales_price > 500)
+  AND cs_sold_date_sk = d_date_sk
+  AND d_qoy = 2 AND d_year = 2001
+GROUP BY ca_zip
+ORDER BY ca_zip
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q16.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q16.sql b/sql/core/src/test/resources/tpcds/q16.sql
new file mode 100755
index 0000000..732ad0d
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q16.sql
@@ -0,0 +1,23 @@
+SELECT
+  count(DISTINCT cs_order_number) AS `order count `,
+  sum(cs_ext_ship_cost) AS `total shipping cost `,
+  sum(cs_net_profit) AS `total net profit `
+FROM
+  catalog_sales cs1, date_dim, customer_address, call_center
+WHERE
+  d_date BETWEEN '2002-02-01' AND (CAST('2002-02-01' AS DATE) + INTERVAL 60 days)
+    AND cs1.cs_ship_date_sk = d_date_sk
+    AND cs1.cs_ship_addr_sk = ca_address_sk
+    AND ca_state = 'GA'
+    AND cs1.cs_call_center_sk = cc_call_center_sk
+    AND cc_county IN
+    ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
+    AND EXISTS(SELECT *
+               FROM catalog_sales cs2
+               WHERE cs1.cs_order_number = cs2.cs_order_number
+                 AND cs1.cs_warehouse_sk <> cs2.cs_warehouse_sk)
+    AND NOT EXISTS(SELECT *
+                   FROM catalog_returns cr1
+                   WHERE cs1.cs_order_number = cr1.cr_order_number)
+ORDER BY count(DISTINCT cs_order_number)
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q17.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q17.sql b/sql/core/src/test/resources/tpcds/q17.sql
new file mode 100755
index 0000000..4d647f7
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q17.sql
@@ -0,0 +1,33 @@
+SELECT
+  i_item_id,
+  i_item_desc,
+  s_state,
+  count(ss_quantity) AS store_sales_quantitycount,
+  avg(ss_quantity) AS store_sales_quantityave,
+  stddev_samp(ss_quantity) AS store_sales_quantitystdev,
+  stddev_samp(ss_quantity) / avg(ss_quantity) AS store_sales_quantitycov,
+  count(sr_return_quantity) as_store_returns_quantitycount,
+  avg(sr_return_quantity) as_store_returns_quantityave,
+  stddev_samp(sr_return_quantity) as_store_returns_quantitystdev,
+  stddev_samp(sr_return_quantity) / avg(sr_return_quantity) AS store_returns_quantitycov,
+  count(cs_quantity) AS catalog_sales_quantitycount,
+  avg(cs_quantity) AS catalog_sales_quantityave,
+  stddev_samp(cs_quantity) / avg(cs_quantity) AS catalog_sales_quantitystdev,
+  stddev_samp(cs_quantity) / avg(cs_quantity) AS catalog_sales_quantitycov
+FROM store_sales, store_returns, catalog_sales, date_dim d1, date_dim d2, date_dim d3, store, item
+WHERE d1.d_quarter_name = '2001Q1'
+  AND d1.d_date_sk = ss_sold_date_sk
+  AND i_item_sk = ss_item_sk
+  AND s_store_sk = ss_store_sk
+  AND ss_customer_sk = sr_customer_sk
+  AND ss_item_sk = sr_item_sk
+  AND ss_ticket_number = sr_ticket_number
+  AND sr_returned_date_sk = d2.d_date_sk
+  AND d2.d_quarter_name IN ('2001Q1', '2001Q2', '2001Q3')
+  AND sr_customer_sk = cs_bill_customer_sk
+  AND sr_item_sk = cs_item_sk
+  AND cs_sold_date_sk = d3.d_date_sk
+  AND d3.d_quarter_name IN ('2001Q1', '2001Q2', '2001Q3')
+GROUP BY i_item_id, i_item_desc, s_state
+ORDER BY i_item_id, i_item_desc, s_state
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q18.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q18.sql b/sql/core/src/test/resources/tpcds/q18.sql
new file mode 100755
index 0000000..4055c80
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q18.sql
@@ -0,0 +1,28 @@
+SELECT
+  i_item_id,
+  ca_country,
+  ca_state,
+  ca_county,
+  avg(cast(cs_quantity AS DECIMAL(12, 2))) agg1,
+  avg(cast(cs_list_price AS DECIMAL(12, 2))) agg2,
+  avg(cast(cs_coupon_amt AS DECIMAL(12, 2))) agg3,
+  avg(cast(cs_sales_price AS DECIMAL(12, 2))) agg4,
+  avg(cast(cs_net_profit AS DECIMAL(12, 2))) agg5,
+  avg(cast(c_birth_year AS DECIMAL(12, 2))) agg6,
+  avg(cast(cd1.cd_dep_count AS DECIMAL(12, 2))) agg7
+FROM catalog_sales, customer_demographics cd1,
+  customer_demographics cd2, customer, customer_address, date_dim, item
+WHERE cs_sold_date_sk = d_date_sk AND
+  cs_item_sk = i_item_sk AND
+  cs_bill_cdemo_sk = cd1.cd_demo_sk AND
+  cs_bill_customer_sk = c_customer_sk AND
+  cd1.cd_gender = 'F' AND
+  cd1.cd_education_status = 'Unknown' AND
+  c_current_cdemo_sk = cd2.cd_demo_sk AND
+  c_current_addr_sk = ca_address_sk AND
+  c_birth_month IN (1, 6, 8, 9, 12, 2) AND
+  d_year = 1998 AND
+  ca_state IN ('MS', 'IN', 'ND', 'OK', 'NM', 'VA', 'MS')
+GROUP BY ROLLUP (i_item_id, ca_country, ca_state, ca_county)
+ORDER BY ca_country, ca_state, ca_county, i_item_id
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q19.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q19.sql b/sql/core/src/test/resources/tpcds/q19.sql
new file mode 100755
index 0000000..e38ab7f
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q19.sql
@@ -0,0 +1,19 @@
+SELECT
+  i_brand_id brand_id,
+  i_brand brand,
+  i_manufact_id,
+  i_manufact,
+  sum(ss_ext_sales_price) ext_price
+FROM date_dim, store_sales, item, customer, customer_address, store
+WHERE d_date_sk = ss_sold_date_sk
+  AND ss_item_sk = i_item_sk
+  AND i_manager_id = 8
+  AND d_moy = 11
+  AND d_year = 1998
+  AND ss_customer_sk = c_customer_sk
+  AND c_current_addr_sk = ca_address_sk
+  AND substr(ca_zip, 1, 5) <> substr(s_zip, 1, 5)
+  AND ss_store_sk = s_store_sk
+GROUP BY i_brand, i_brand_id, i_manufact_id, i_manufact
+ORDER BY ext_price DESC, brand, brand_id, i_manufact_id, i_manufact
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q2.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q2.sql b/sql/core/src/test/resources/tpcds/q2.sql
new file mode 100755
index 0000000..52c0e90
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q2.sql
@@ -0,0 +1,81 @@
+WITH wscs AS
+( SELECT
+    sold_date_sk,
+    sales_price
+  FROM (SELECT
+    ws_sold_date_sk sold_date_sk,
+    ws_ext_sales_price sales_price
+  FROM web_sales) x
+  UNION ALL
+  (SELECT
+    cs_sold_date_sk sold_date_sk,
+    cs_ext_sales_price sales_price
+  FROM catalog_sales)),
+    wswscs AS
+  ( SELECT
+    d_week_seq,
+    sum(CASE WHEN (d_day_name = 'Sunday')
+      THEN sales_price
+        ELSE NULL END)
+    sun_sales,
+    sum(CASE WHEN (d_day_name = 'Monday')
+      THEN sales_price
+        ELSE NULL END)
+    mon_sales,
+    sum(CASE WHEN (d_day_name = 'Tuesday')
+      THEN sales_price
+        ELSE NULL END)
+    tue_sales,
+    sum(CASE WHEN (d_day_name = 'Wednesday')
+      THEN sales_price
+        ELSE NULL END)
+    wed_sales,
+    sum(CASE WHEN (d_day_name = 'Thursday')
+      THEN sales_price
+        ELSE NULL END)
+    thu_sales,
+    sum(CASE WHEN (d_day_name = 'Friday')
+      THEN sales_price
+        ELSE NULL END)
+    fri_sales,
+    sum(CASE WHEN (d_day_name = 'Saturday')
+      THEN sales_price
+        ELSE NULL END)
+    sat_sales
+  FROM wscs, date_dim
+  WHERE d_date_sk = sold_date_sk
+  GROUP BY d_week_seq)
+SELECT
+  d_week_seq1,
+  round(sun_sales1 / sun_sales2, 2),
+  round(mon_sales1 / mon_sales2, 2),
+  round(tue_sales1 / tue_sales2, 2),
+  round(wed_sales1 / wed_sales2, 2),
+  round(thu_sales1 / thu_sales2, 2),
+  round(fri_sales1 / fri_sales2, 2),
+  round(sat_sales1 / sat_sales2, 2)
+FROM
+  (SELECT
+    wswscs.d_week_seq d_week_seq1,
+    sun_sales sun_sales1,
+    mon_sales mon_sales1,
+    tue_sales tue_sales1,
+    wed_sales wed_sales1,
+    thu_sales thu_sales1,
+    fri_sales fri_sales1,
+    sat_sales sat_sales1
+  FROM wswscs, date_dim
+  WHERE date_dim.d_week_seq = wswscs.d_week_seq AND d_year = 2001) y,
+  (SELECT
+    wswscs.d_week_seq d_week_seq2,
+    sun_sales sun_sales2,
+    mon_sales mon_sales2,
+    tue_sales tue_sales2,
+    wed_sales wed_sales2,
+    thu_sales thu_sales2,
+    fri_sales fri_sales2,
+    sat_sales sat_sales2
+  FROM wswscs, date_dim
+  WHERE date_dim.d_week_seq = wswscs.d_week_seq AND d_year = 2001 + 1) z
+WHERE d_week_seq1 = d_week_seq2 - 53
+ORDER BY d_week_seq1

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q20.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q20.sql b/sql/core/src/test/resources/tpcds/q20.sql
new file mode 100755
index 0000000..7ac6c7a
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q20.sql
@@ -0,0 +1,18 @@
+SELECT
+  i_item_desc,
+  i_category,
+  i_class,
+  i_current_price,
+  sum(cs_ext_sales_price) AS itemrevenue,
+  sum(cs_ext_sales_price) * 100 / sum(sum(cs_ext_sales_price))
+  OVER
+  (PARTITION BY i_class) AS revenueratio
+FROM catalog_sales, item, date_dim
+WHERE cs_item_sk = i_item_sk
+  AND i_category IN ('Sports', 'Books', 'Home')
+  AND cs_sold_date_sk = d_date_sk
+  AND d_date BETWEEN cast('1999-02-22' AS DATE)
+AND (cast('1999-02-22' AS DATE) + INTERVAL 30 days)
+GROUP BY i_item_id, i_item_desc, i_category, i_class, i_current_price
+ORDER BY i_category, i_class, i_item_id, i_item_desc, revenueratio
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q21.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q21.sql b/sql/core/src/test/resources/tpcds/q21.sql
new file mode 100755
index 0000000..5508811
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q21.sql
@@ -0,0 +1,25 @@
+SELECT *
+FROM (
+       SELECT
+         w_warehouse_name,
+         i_item_id,
+         sum(CASE WHEN (cast(d_date AS DATE) < cast('2000-03-11' AS DATE))
+           THEN inv_quantity_on_hand
+             ELSE 0 END) AS inv_before,
+         sum(CASE WHEN (cast(d_date AS DATE) >= cast('2000-03-11' AS DATE))
+           THEN inv_quantity_on_hand
+             ELSE 0 END) AS inv_after
+       FROM inventory, warehouse, item, date_dim
+       WHERE i_current_price BETWEEN 0.99 AND 1.49
+         AND i_item_sk = inv_item_sk
+         AND inv_warehouse_sk = w_warehouse_sk
+         AND inv_date_sk = d_date_sk
+         AND d_date BETWEEN (cast('2000-03-11' AS DATE) - INTERVAL 30 days)
+       AND (cast('2000-03-11' AS DATE) + INTERVAL 30 days)
+       GROUP BY w_warehouse_name, i_item_id) x
+WHERE (CASE WHEN inv_before > 0
+  THEN inv_after / inv_before
+       ELSE NULL
+       END) BETWEEN 2.0 / 3.0 AND 3.0 / 2.0
+ORDER BY w_warehouse_name, i_item_id
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q22.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q22.sql b/sql/core/src/test/resources/tpcds/q22.sql
new file mode 100755
index 0000000..add3b41
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q22.sql
@@ -0,0 +1,14 @@
+SELECT
+  i_product_name,
+  i_brand,
+  i_class,
+  i_category,
+  avg(inv_quantity_on_hand) qoh
+FROM inventory, date_dim, item, warehouse
+WHERE inv_date_sk = d_date_sk
+  AND inv_item_sk = i_item_sk
+  AND inv_warehouse_sk = w_warehouse_sk
+  AND d_month_seq BETWEEN 1200 AND 1200 + 11
+GROUP BY ROLLUP (i_product_name, i_brand, i_class, i_category)
+ORDER BY qoh, i_product_name, i_brand, i_class, i_category
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q23a.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q23a.sql b/sql/core/src/test/resources/tpcds/q23a.sql
new file mode 100755
index 0000000..37791f6
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q23a.sql
@@ -0,0 +1,53 @@
+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

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q23b.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q23b.sql b/sql/core/src/test/resources/tpcds/q23b.sql
new file mode 100755
index 0000000..0115019
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q23b.sql
@@ -0,0 +1,68 @@
+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

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q24a.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q24a.sql b/sql/core/src/test/resources/tpcds/q24a.sql
new file mode 100755
index 0000000..bcc1894
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q24a.sql
@@ -0,0 +1,34 @@
+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_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 = 'pale'
+GROUP BY c_last_name, c_first_name, s_store_name
+HAVING sum(netpaid) > (SELECT 0.05 * avg(netpaid)
+FROM ssales)

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q24b.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q24b.sql b/sql/core/src/test/resources/tpcds/q24b.sql
new file mode 100755
index 0000000..830eb67
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q24b.sql
@@ -0,0 +1,34 @@
+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_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 = 'chiffon'
+GROUP BY c_last_name, c_first_name, s_store_name
+HAVING sum(netpaid) > (SELECT 0.05 * avg(netpaid)
+FROM ssales)

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q25.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q25.sql b/sql/core/src/test/resources/tpcds/q25.sql
new file mode 100755
index 0000000..a4d78a3
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q25.sql
@@ -0,0 +1,33 @@
+SELECT
+  i_item_id,
+  i_item_desc,
+  s_store_id,
+  s_store_name,
+  sum(ss_net_profit) AS store_sales_profit,
+  sum(sr_net_loss) AS store_returns_loss,
+  sum(cs_net_profit) AS catalog_sales_profit
+FROM
+  store_sales, store_returns, catalog_sales, date_dim d1, date_dim d2, date_dim d3,
+  store, item
+WHERE
+  d1.d_moy = 4
+    AND d1.d_year = 2001
+    AND d1.d_date_sk = ss_sold_date_sk
+    AND i_item_sk = ss_item_sk
+    AND s_store_sk = ss_store_sk
+    AND ss_customer_sk = sr_customer_sk
+    AND ss_item_sk = sr_item_sk
+    AND ss_ticket_number = sr_ticket_number
+    AND sr_returned_date_sk = d2.d_date_sk
+    AND d2.d_moy BETWEEN 4 AND 10
+    AND d2.d_year = 2001
+    AND sr_customer_sk = cs_bill_customer_sk
+    AND sr_item_sk = cs_item_sk
+    AND cs_sold_date_sk = d3.d_date_sk
+    AND d3.d_moy BETWEEN 4 AND 10
+    AND d3.d_year = 2001
+GROUP BY
+  i_item_id, i_item_desc, s_store_id, s_store_name
+ORDER BY
+  i_item_id, i_item_desc, s_store_id, s_store_name
+LIMIT 100
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q26.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q26.sql b/sql/core/src/test/resources/tpcds/q26.sql
new file mode 100755
index 0000000..6d395a1
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q26.sql
@@ -0,0 +1,19 @@
+SELECT
+  i_item_id,
+  avg(cs_quantity) agg1,
+  avg(cs_list_price) agg2,
+  avg(cs_coupon_amt) agg3,
+  avg(cs_sales_price) agg4
+FROM catalog_sales, customer_demographics, date_dim, item, promotion
+WHERE cs_sold_date_sk = d_date_sk AND
+  cs_item_sk = i_item_sk AND
+  cs_bill_cdemo_sk = cd_demo_sk AND
+  cs_promo_sk = p_promo_sk AND
+  cd_gender = 'M' AND
+  cd_marital_status = 'S' AND
+  cd_education_status = 'College' AND
+  (p_channel_email = 'N' OR p_channel_event = 'N') AND
+  d_year = 2000
+GROUP BY i_item_id
+ORDER BY i_item_id
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q27.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q27.sql b/sql/core/src/test/resources/tpcds/q27.sql
new file mode 100755
index 0000000..b0e2fd9
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q27.sql
@@ -0,0 +1,21 @@
+SELECT
+  i_item_id,
+  s_state,
+  grouping(s_state) g_state,
+  avg(ss_quantity) agg1,
+  avg(ss_list_price) agg2,
+  avg(ss_coupon_amt) agg3,
+  avg(ss_sales_price) agg4
+FROM store_sales, customer_demographics, date_dim, 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 = 'M' AND
+  cd_marital_status = 'S' AND
+  cd_education_status = 'College' AND
+  d_year = 2002 AND
+  s_state IN ('TN', 'TN', 'TN', 'TN', 'TN', 'TN')
+GROUP BY ROLLUP (i_item_id, s_state)
+ORDER BY i_item_id, s_state
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q28.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q28.sql b/sql/core/src/test/resources/tpcds/q28.sql
new file mode 100755
index 0000000..f34c2bb
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q28.sql
@@ -0,0 +1,56 @@
+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

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q29.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q29.sql b/sql/core/src/test/resources/tpcds/q29.sql
new file mode 100755
index 0000000..3f1fd55
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q29.sql
@@ -0,0 +1,32 @@
+SELECT
+  i_item_id,
+  i_item_desc,
+  s_store_id,
+  s_store_name,
+  sum(ss_quantity) AS store_sales_quantity,
+  sum(sr_return_quantity) AS store_returns_quantity,
+  sum(cs_quantity) AS catalog_sales_quantity
+FROM
+  store_sales, store_returns, catalog_sales, date_dim d1, date_dim d2,
+  date_dim d3, store, item
+WHERE
+  d1.d_moy = 9
+    AND d1.d_year = 1999
+    AND d1.d_date_sk = ss_sold_date_sk
+    AND i_item_sk = ss_item_sk
+    AND s_store_sk = ss_store_sk
+    AND ss_customer_sk = sr_customer_sk
+    AND ss_item_sk = sr_item_sk
+    AND ss_ticket_number = sr_ticket_number
+    AND sr_returned_date_sk = d2.d_date_sk
+    AND d2.d_moy BETWEEN 9 AND 9 + 3
+    AND d2.d_year = 1999
+    AND sr_customer_sk = cs_bill_customer_sk
+    AND sr_item_sk = cs_item_sk
+    AND cs_sold_date_sk = d3.d_date_sk
+    AND d3.d_year IN (1999, 1999 + 1, 1999 + 2)
+GROUP BY
+  i_item_id, i_item_desc, s_store_id, s_store_name
+ORDER BY
+  i_item_id, i_item_desc, s_store_id, s_store_name
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q3.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q3.sql b/sql/core/src/test/resources/tpcds/q3.sql
new file mode 100755
index 0000000..181509d
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q3.sql
@@ -0,0 +1,13 @@
+SELECT
+  dt.d_year,
+  item.i_brand_id brand_id,
+  item.i_brand brand,
+  SUM(ss_ext_sales_price) 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 = 128
+  AND dt.d_moy = 11
+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/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q30.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q30.sql b/sql/core/src/test/resources/tpcds/q30.sql
new file mode 100755
index 0000000..986bef5
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q30.sql
@@ -0,0 +1,35 @@
+WITH customer_total_return AS
+(SELECT
+    wr_returning_customer_sk AS ctr_customer_sk,
+    ca_state AS ctr_state,
+    sum(wr_return_amt) AS ctr_total_return
+  FROM web_returns, date_dim, customer_address
+  WHERE wr_returned_date_sk = d_date_sk
+    AND d_year = 2002
+    AND wr_returning_addr_sk = ca_address_sk
+  GROUP BY wr_returning_customer_sk, ca_state)
+SELECT
+  c_customer_id,
+  c_salutation,
+  c_first_name,
+  c_last_name,
+  c_preferred_cust_flag,
+  c_birth_day,
+  c_birth_month,
+  c_birth_year,
+  c_birth_country,
+  c_login,
+  c_email_address,
+  c_last_review_date,
+  ctr_total_return
+FROM customer_total_return ctr1, customer_address, customer
+WHERE ctr1.ctr_total_return > (SELECT avg(ctr_total_return) * 1.2
+FROM customer_total_return ctr2
+WHERE ctr1.ctr_state = ctr2.ctr_state)
+  AND ca_address_sk = c_current_addr_sk
+  AND ca_state = 'GA'
+  AND ctr1.ctr_customer_sk = c_customer_sk
+ORDER BY c_customer_id, c_salutation, c_first_name, c_last_name, c_preferred_cust_flag
+  , c_birth_day, c_birth_month, c_birth_year, c_birth_country, c_login, c_email_address
+  , c_last_review_date, ctr_total_return
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q31.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q31.sql b/sql/core/src/test/resources/tpcds/q31.sql
new file mode 100755
index 0000000..3e543d5
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q31.sql
@@ -0,0 +1,60 @@
+WITH ss AS
+(SELECT
+    ca_county,
+    d_qoy,
+    d_year,
+    sum(ss_ext_sales_price) AS store_sales
+  FROM store_sales, date_dim, customer_address
+  WHERE ss_sold_date_sk = d_date_sk
+    AND ss_addr_sk = ca_address_sk
+  GROUP BY ca_county, d_qoy, d_year),
+    ws AS
+  (SELECT
+    ca_county,
+    d_qoy,
+    d_year,
+    sum(ws_ext_sales_price) AS web_sales
+  FROM web_sales, date_dim, customer_address
+  WHERE ws_sold_date_sk = d_date_sk
+    AND ws_bill_addr_sk = ca_address_sk
+  GROUP BY ca_county, d_qoy, d_year)
+SELECT
+  ss1.ca_county,
+  ss1.d_year,
+  ws2.web_sales / ws1.web_sales web_q1_q2_increase,
+  ss2.store_sales / ss1.store_sales store_q1_q2_increase,
+  ws3.web_sales / ws2.web_sales web_q2_q3_increase,
+  ss3.store_sales / ss2.store_sales store_q2_q3_increase
+FROM
+  ss ss1, ss ss2, ss ss3, ws ws1, ws ws2, ws ws3
+WHERE
+  ss1.d_qoy = 1
+    AND ss1.d_year = 2000
+    AND ss1.ca_county = ss2.ca_county
+    AND ss2.d_qoy = 2
+    AND ss2.d_year = 2000
+    AND ss2.ca_county = ss3.ca_county
+    AND ss3.d_qoy = 3
+    AND ss3.d_year = 2000
+    AND ss1.ca_county = ws1.ca_county
+    AND ws1.d_qoy = 1
+    AND ws1.d_year = 2000
+    AND ws1.ca_county = ws2.ca_county
+    AND ws2.d_qoy = 2
+    AND ws2.d_year = 2000
+    AND ws1.ca_county = ws3.ca_county
+    AND ws3.d_qoy = 3
+    AND ws3.d_year = 2000
+    AND CASE WHEN ws1.web_sales > 0
+    THEN ws2.web_sales / ws1.web_sales
+        ELSE NULL END
+    > CASE WHEN ss1.store_sales > 0
+    THEN ss2.store_sales / ss1.store_sales
+      ELSE NULL END
+    AND CASE WHEN ws2.web_sales > 0
+    THEN ws3.web_sales / ws2.web_sales
+        ELSE NULL END
+    > CASE WHEN ss2.store_sales > 0
+    THEN ss3.store_sales / ss2.store_sales
+      ELSE NULL END
+ORDER BY ss1.ca_county

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q32.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q32.sql b/sql/core/src/test/resources/tpcds/q32.sql
new file mode 100755
index 0000000..1a90796
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q32.sql
@@ -0,0 +1,15 @@
+SELECT 1 AS `excess discount amount `
+FROM
+  catalog_sales, item, date_dim
+WHERE
+  i_manufact_id = 977
+    AND i_item_sk = cs_item_sk
+    AND d_date BETWEEN '2000-01-27' AND (cast('2000-01-27' AS DATE) + interval 90 days)
+    AND d_date_sk = cs_sold_date_sk
+    AND cs_ext_discount_amt > (
+    SELECT 1.3 * avg(cs_ext_discount_amt)
+    FROM catalog_sales, date_dim
+    WHERE cs_item_sk = i_item_sk
+      AND d_date BETWEEN '2000-01-27]' AND (cast('2000-01-27' AS DATE) + interval 90 days)
+      AND d_date_sk = cs_sold_date_sk)
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q33.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q33.sql b/sql/core/src/test/resources/tpcds/q33.sql
new file mode 100755
index 0000000..d24856a
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q33.sql
@@ -0,0 +1,65 @@
+WITH ss AS (
+  SELECT
+    i_manufact_id,
+    sum(ss_ext_sales_price) total_sales
+  FROM
+    store_sales, date_dim, customer_address, item
+  WHERE
+    i_manufact_id IN (SELECT i_manufact_id
+    FROM item
+    WHERE i_category IN ('Electronics'))
+      AND ss_item_sk = i_item_sk
+      AND ss_sold_date_sk = d_date_sk
+      AND d_year = 1998
+      AND d_moy = 5
+      AND ss_addr_sk = ca_address_sk
+      AND ca_gmt_offset = -5
+  GROUP BY i_manufact_id), cs AS
+(SELECT
+    i_manufact_id,
+    sum(cs_ext_sales_price) total_sales
+  FROM catalog_sales, date_dim, customer_address, item
+  WHERE
+    i_manufact_id IN (
+      SELECT i_manufact_id
+      FROM item
+      WHERE
+        i_category IN ('Electronics'))
+      AND cs_item_sk = i_item_sk
+      AND cs_sold_date_sk = d_date_sk
+      AND d_year = 1998
+      AND d_moy = 5
+      AND cs_bill_addr_sk = ca_address_sk
+      AND ca_gmt_offset = -5
+  GROUP BY i_manufact_id),
+    ws AS (
+    SELECT
+      i_manufact_id,
+      sum(ws_ext_sales_price) total_sales
+    FROM
+      web_sales, date_dim, customer_address, item
+    WHERE
+      i_manufact_id IN (SELECT i_manufact_id
+      FROM item
+      WHERE i_category IN ('Electronics'))
+        AND ws_item_sk = i_item_sk
+        AND ws_sold_date_sk = d_date_sk
+        AND d_year = 1998
+        AND d_moy = 5
+        AND ws_bill_addr_sk = ca_address_sk
+        AND ca_gmt_offset = -5
+    GROUP BY i_manufact_id)
+SELECT
+  i_manufact_id,
+  sum(total_sales) total_sales
+FROM (SELECT *
+      FROM ss
+      UNION ALL
+      SELECT *
+      FROM cs
+      UNION ALL
+      SELECT *
+      FROM ws) tmp1
+GROUP BY i_manufact_id
+ORDER BY total_sales
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q34.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q34.sql b/sql/core/src/test/resources/tpcds/q34.sql
new file mode 100755
index 0000000..33396bf
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q34.sql
@@ -0,0 +1,32 @@
+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 (1999, 1999 + 1, 1999 + 2)
+    AND store.s_county IN
+    ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County',
+     'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
+  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

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q35.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q35.sql b/sql/core/src/test/resources/tpcds/q35.sql
new file mode 100755
index 0000000..cfe4342
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q35.sql
@@ -0,0 +1,46 @@
+SELECT
+  ca_state,
+  cd_gender,
+  cd_marital_status,
+  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 web_sales, date_dim
+            WHERE c.c_customer_sk = ws_bill_customer_sk AND
+              ws_sold_date_sk = d_date_sk AND
+              d_year = 2002 AND
+              d_qoy < 4) OR
+      exists(SELECT *
+             FROM catalog_sales, date_dim
+             WHERE c.c_customer_sk = cs_ship_customer_sk AND
+               cs_sold_date_sk = d_date_sk AND
+               d_year = 2002 AND
+               d_qoy < 4))
+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

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q36.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q36.sql b/sql/core/src/test/resources/tpcds/q36.sql
new file mode 100755
index 0000000..a8f93df
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q36.sql
@@ -0,0 +1,26 @@
+SELECT
+  sum(ss_net_profit) / sum(ss_ext_sales_price) AS gross_margin,
+  i_category,
+  i_class,
+  grouping(i_category) + grouping(i_class) AS lochierarchy,
+  rank()
+  OVER (
+    PARTITION BY grouping(i_category) + grouping(i_class),
+      CASE WHEN grouping(i_class) = 0
+        THEN i_category END
+    ORDER BY sum(ss_net_profit) / sum(ss_ext_sales_price) ASC) AS rank_within_parent
+FROM
+  store_sales, date_dim d1, item, store
+WHERE
+  d1.d_year = 2001
+    AND d1.d_date_sk = ss_sold_date_sk
+    AND i_item_sk = ss_item_sk
+    AND s_store_sk = ss_store_sk
+    AND s_state IN ('TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN')
+GROUP BY ROLLUP (i_category, i_class)
+ORDER BY
+  lochierarchy DESC
+  , CASE WHEN lochierarchy = 0
+  THEN i_category END
+  , rank_within_parent
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q37.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q37.sql b/sql/core/src/test/resources/tpcds/q37.sql
new file mode 100755
index 0000000..11b3821
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q37.sql
@@ -0,0 +1,15 @@
+SELECT
+  i_item_id,
+  i_item_desc,
+  i_current_price
+FROM item, inventory, date_dim, catalog_sales
+WHERE i_current_price BETWEEN 68 AND 68 + 30
+  AND inv_item_sk = i_item_sk
+  AND d_date_sk = inv_date_sk
+  AND d_date BETWEEN cast('2000-02-01' AS DATE) AND (cast('2000-02-01' AS DATE) + INTERVAL 60 days)
+  AND i_manufact_id IN (677, 940, 694, 808)
+  AND inv_quantity_on_hand BETWEEN 100 AND 500
+  AND cs_item_sk = i_item_sk
+GROUP BY i_item_id, i_item_desc, i_current_price
+ORDER BY i_item_id
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q38.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q38.sql b/sql/core/src/test/resources/tpcds/q38.sql
new file mode 100755
index 0000000..1c8d53e
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q38.sql
@@ -0,0 +1,30 @@
+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

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q39a.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q39a.sql b/sql/core/src/test/resources/tpcds/q39a.sql
new file mode 100755
index 0000000..9fc4c17
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q39a.sql
@@ -0,0 +1,47 @@
+WITH inv AS
+(SELECT
+    w_warehouse_name,
+    w_warehouse_sk,
+    i_item_sk,
+    d_moy,
+    stdev,
+    mean,
+    CASE mean
+    WHEN 0
+      THEN NULL
+    ELSE stdev / mean END cov
+  FROM (SELECT
+    w_warehouse_name,
+    w_warehouse_sk,
+    i_item_sk,
+    d_moy,
+    stddev_samp(inv_quantity_on_hand) stdev,
+    avg(inv_quantity_on_hand) mean
+  FROM inventory, item, warehouse, date_dim
+  WHERE inv_item_sk = i_item_sk
+    AND inv_warehouse_sk = w_warehouse_sk
+    AND inv_date_sk = d_date_sk
+    AND d_year = 2001
+  GROUP BY w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy) foo
+  WHERE CASE mean
+        WHEN 0
+          THEN 0
+        ELSE stdev / mean END > 1)
+SELECT
+  inv1.w_warehouse_sk,
+  inv1.i_item_sk,
+  inv1.d_moy,
+  inv1.mean,
+  inv1.cov,
+  inv2.w_warehouse_sk,
+  inv2.i_item_sk,
+  inv2.d_moy,
+  inv2.mean,
+  inv2.cov
+FROM inv inv1, inv inv2
+WHERE inv1.i_item_sk = inv2.i_item_sk
+  AND inv1.w_warehouse_sk = inv2.w_warehouse_sk
+  AND inv1.d_moy = 1
+  AND inv2.d_moy = 1 + 1
+ORDER BY inv1.w_warehouse_sk, inv1.i_item_sk, inv1.d_moy, inv1.mean, inv1.cov
+  , inv2.d_moy, inv2.mean, inv2.cov

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q39b.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q39b.sql b/sql/core/src/test/resources/tpcds/q39b.sql
new file mode 100755
index 0000000..6f84930
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q39b.sql
@@ -0,0 +1,48 @@
+WITH inv AS
+(SELECT
+    w_warehouse_name,
+    w_warehouse_sk,
+    i_item_sk,
+    d_moy,
+    stdev,
+    mean,
+    CASE mean
+    WHEN 0
+      THEN NULL
+    ELSE stdev / mean END cov
+  FROM (SELECT
+    w_warehouse_name,
+    w_warehouse_sk,
+    i_item_sk,
+    d_moy,
+    stddev_samp(inv_quantity_on_hand) stdev,
+    avg(inv_quantity_on_hand) mean
+  FROM inventory, item, warehouse, date_dim
+  WHERE inv_item_sk = i_item_sk
+    AND inv_warehouse_sk = w_warehouse_sk
+    AND inv_date_sk = d_date_sk
+    AND d_year = 2001
+  GROUP BY w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy) foo
+  WHERE CASE mean
+        WHEN 0
+          THEN 0
+        ELSE stdev / mean END > 1)
+SELECT
+  inv1.w_warehouse_sk,
+  inv1.i_item_sk,
+  inv1.d_moy,
+  inv1.mean,
+  inv1.cov,
+  inv2.w_warehouse_sk,
+  inv2.i_item_sk,
+  inv2.d_moy,
+  inv2.mean,
+  inv2.cov
+FROM inv inv1, inv inv2
+WHERE inv1.i_item_sk = inv2.i_item_sk
+  AND inv1.w_warehouse_sk = inv2.w_warehouse_sk
+  AND inv1.d_moy = 1
+  AND inv2.d_moy = 1 + 1
+  AND inv1.cov > 1.5
+ORDER BY inv1.w_warehouse_sk, inv1.i_item_sk, inv1.d_moy, inv1.mean, inv1.cov
+  , inv2.d_moy, inv2.mean, inv2.cov

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q4.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q4.sql b/sql/core/src/test/resources/tpcds/q4.sql
new file mode 100755
index 0000000..b9f27fb
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q4.sql
@@ -0,0 +1,120 @@
+WITH year_total AS (
+  SELECT
+    c_customer_id customer_id,
+    c_first_name customer_first_name,
+    c_last_name customer_last_name,
+    c_preferred_cust_flag customer_preferred_cust_flag,
+    c_birth_country customer_birth_country,
+    c_login customer_login,
+    c_email_address customer_email_address,
+    d_year dyear,
+    sum(((ss_ext_list_price - ss_ext_wholesale_cost - ss_ext_discount_amt) +
+      ss_ext_sales_price) / 2) year_total,
+    's' sale_type
+  FROM customer, store_sales, date_dim
+  WHERE c_customer_sk = ss_customer_sk AND ss_sold_date_sk = d_date_sk
+  GROUP BY c_customer_id,
+    c_first_name,
+    c_last_name,
+    c_preferred_cust_flag,
+    c_birth_country,
+    c_login,
+    c_email_address,
+    d_year
+  UNION ALL
+  SELECT
+    c_customer_id customer_id,
+    c_first_name customer_first_name,
+    c_last_name customer_last_name,
+    c_preferred_cust_flag customer_preferred_cust_flag,
+    c_birth_country customer_birth_country,
+    c_login customer_login,
+    c_email_address customer_email_address,
+    d_year dyear,
+    sum((((cs_ext_list_price - cs_ext_wholesale_cost - cs_ext_discount_amt) +
+      cs_ext_sales_price) / 2)) year_total,
+    'c' sale_type
+  FROM customer, catalog_sales, date_dim
+  WHERE c_customer_sk = cs_bill_customer_sk AND cs_sold_date_sk = d_date_sk
+  GROUP BY c_customer_id,
+    c_first_name,
+    c_last_name,
+    c_preferred_cust_flag,
+    c_birth_country,
+    c_login,
+    c_email_address,
+    d_year
+  UNION ALL
+  SELECT
+    c_customer_id customer_id,
+    c_first_name customer_first_name,
+    c_last_name customer_last_name,
+    c_preferred_cust_flag customer_preferred_cust_flag,
+    c_birth_country customer_birth_country,
+    c_login customer_login,
+    c_email_address customer_email_address,
+    d_year dyear,
+    sum((((ws_ext_list_price - ws_ext_wholesale_cost - ws_ext_discount_amt) + ws_ext_sales_price) /
+      2)) year_total,
+    'w' sale_type
+  FROM customer, web_sales, date_dim
+  WHERE c_customer_sk = ws_bill_customer_sk AND ws_sold_date_sk = d_date_sk
+  GROUP BY c_customer_id,
+    c_first_name,
+    c_last_name,
+    c_preferred_cust_flag,
+    c_birth_country,
+    c_login,
+    c_email_address,
+    d_year)
+SELECT
+  t_s_secyear.customer_id,
+  t_s_secyear.customer_first_name,
+  t_s_secyear.customer_last_name,
+  t_s_secyear.customer_preferred_cust_flag,
+  t_s_secyear.customer_birth_country,
+  t_s_secyear.customer_login,
+  t_s_secyear.customer_email_address
+FROM year_total t_s_firstyear, year_total t_s_secyear, year_total t_c_firstyear,
+  year_total t_c_secyear, year_total t_w_firstyear, year_total t_w_secyear
+WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id
+  AND t_s_firstyear.customer_id = t_c_secyear.customer_id
+  AND t_s_firstyear.customer_id = t_c_firstyear.customer_id
+  AND t_s_firstyear.customer_id = t_w_firstyear.customer_id
+  AND t_s_firstyear.customer_id = t_w_secyear.customer_id
+  AND t_s_firstyear.sale_type = 's'
+  AND t_c_firstyear.sale_type = 'c'
+  AND t_w_firstyear.sale_type = 'w'
+  AND t_s_secyear.sale_type = 's'
+  AND t_c_secyear.sale_type = 'c'
+  AND t_w_secyear.sale_type = 'w'
+  AND t_s_firstyear.dyear = 2001
+  AND t_s_secyear.dyear = 2001 + 1
+  AND t_c_firstyear.dyear = 2001
+  AND t_c_secyear.dyear = 2001 + 1
+  AND t_w_firstyear.dyear = 2001
+  AND t_w_secyear.dyear = 2001 + 1
+  AND t_s_firstyear.year_total > 0
+  AND t_c_firstyear.year_total > 0
+  AND t_w_firstyear.year_total > 0
+  AND CASE WHEN t_c_firstyear.year_total > 0
+  THEN t_c_secyear.year_total / t_c_firstyear.year_total
+      ELSE NULL END
+  > CASE WHEN t_s_firstyear.year_total > 0
+  THEN t_s_secyear.year_total / t_s_firstyear.year_total
+    ELSE NULL END
+  AND CASE WHEN t_c_firstyear.year_total > 0
+  THEN t_c_secyear.year_total / t_c_firstyear.year_total
+      ELSE NULL END
+  > CASE WHEN t_w_firstyear.year_total > 0
+  THEN t_w_secyear.year_total / t_w_firstyear.year_total
+    ELSE NULL END
+ORDER BY
+  t_s_secyear.customer_id,
+  t_s_secyear.customer_first_name,
+  t_s_secyear.customer_last_name,
+  t_s_secyear.customer_preferred_cust_flag,
+  t_s_secyear.customer_birth_country,
+  t_s_secyear.customer_login,
+  t_s_secyear.customer_email_address
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q40.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q40.sql b/sql/core/src/test/resources/tpcds/q40.sql
new file mode 100755
index 0000000..66d8b73
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q40.sql
@@ -0,0 +1,25 @@
+SELECT
+  w_state,
+  i_item_id,
+  sum(CASE WHEN (cast(d_date AS DATE) < cast('2000-03-11' AS DATE))
+    THEN cs_sales_price - coalesce(cr_refunded_cash, 0)
+      ELSE 0 END) AS sales_before,
+  sum(CASE WHEN (cast(d_date AS DATE) >= cast('2000-03-11' AS DATE))
+    THEN cs_sales_price - coalesce(cr_refunded_cash, 0)
+      ELSE 0 END) AS sales_after
+FROM
+  catalog_sales
+  LEFT OUTER JOIN catalog_returns ON
+                                    (cs_order_number = cr_order_number
+                                      AND cs_item_sk = cr_item_sk)
+  , warehouse, item, date_dim
+WHERE
+  i_current_price BETWEEN 0.99 AND 1.49
+    AND i_item_sk = cs_item_sk
+    AND cs_warehouse_sk = w_warehouse_sk
+    AND cs_sold_date_sk = d_date_sk
+    AND d_date BETWEEN (cast('2000-03-11' AS DATE) - INTERVAL 30 days)
+  AND (cast('2000-03-11' AS DATE) + INTERVAL 30 days)
+GROUP BY w_state, i_item_id
+ORDER BY w_state, i_item_id
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q41.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q41.sql b/sql/core/src/test/resources/tpcds/q41.sql
new file mode 100755
index 0000000..25e317e
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q41.sql
@@ -0,0 +1,49 @@
+SELECT DISTINCT (i_product_name)
+FROM item i1
+WHERE i_manufact_id BETWEEN 738 AND 738 + 40
+  AND (SELECT count(*) AS item_cnt
+FROM item
+WHERE (i_manufact = i1.i_manufact AND
+  ((i_category = 'Women' AND
+    (i_color = 'powder' OR i_color = 'khaki') AND
+    (i_units = 'Ounce' OR i_units = 'Oz') AND
+    (i_size = 'medium' OR i_size = 'extra large')
+  ) OR
+    (i_category = 'Women' AND
+      (i_color = 'brown' OR i_color = 'honeydew') AND
+      (i_units = 'Bunch' OR i_units = 'Ton') AND
+      (i_size = 'N/A' OR i_size = 'small')
+    ) OR
+    (i_category = 'Men' AND
+      (i_color = 'floral' OR i_color = 'deep') AND
+      (i_units = 'N/A' OR i_units = 'Dozen') AND
+      (i_size = 'petite' OR i_size = 'large')
+    ) OR
+    (i_category = 'Men' AND
+      (i_color = 'light' OR i_color = 'cornflower') AND
+      (i_units = 'Box' OR i_units = 'Pound') AND
+      (i_size = 'medium' OR i_size = 'extra large')
+    ))) OR
+  (i_manufact = i1.i_manufact AND
+    ((i_category = 'Women' AND
+      (i_color = 'midnight' OR i_color = 'snow') AND
+      (i_units = 'Pallet' OR i_units = 'Gross') AND
+      (i_size = 'medium' OR i_size = 'extra large')
+    ) OR
+      (i_category = 'Women' AND
+        (i_color = 'cyan' OR i_color = 'papaya') AND
+        (i_units = 'Cup' OR i_units = 'Dram') AND
+        (i_size = 'N/A' OR i_size = 'small')
+      ) OR
+      (i_category = 'Men' AND
+        (i_color = 'orange' OR i_color = 'frosted') AND
+        (i_units = 'Each' OR i_units = 'Tbl') AND
+        (i_size = 'petite' OR i_size = 'large')
+      ) OR
+      (i_category = 'Men' AND
+        (i_color = 'forest' OR i_color = 'ghost') AND
+        (i_units = 'Lb' OR i_units = 'Bundle') AND
+        (i_size = 'medium' OR i_size = 'extra large')
+      )))) > 0
+ORDER BY i_product_name
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q42.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q42.sql b/sql/core/src/test/resources/tpcds/q42.sql
new file mode 100755
index 0000000..4d2e717
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q42.sql
@@ -0,0 +1,18 @@
+SELECT
+  dt.d_year,
+  item.i_category_id,
+  item.i_category,
+  sum(ss_ext_sales_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 = 11
+  AND dt.d_year = 2000
+GROUP BY dt.d_year
+  , item.i_category_id
+  , item.i_category
+ORDER BY sum(ss_ext_sales_price) DESC, dt.d_year
+  , item.i_category_id
+  , item.i_category
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q43.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q43.sql b/sql/core/src/test/resources/tpcds/q43.sql
new file mode 100755
index 0000000..4541177
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q43.sql
@@ -0,0 +1,33 @@
+SELECT
+  s_store_name,
+  s_store_id,
+  sum(CASE WHEN (d_day_name = 'Sunday')
+    THEN ss_sales_price
+      ELSE NULL END) sun_sales,
+  sum(CASE WHEN (d_day_name = 'Monday')
+    THEN ss_sales_price
+      ELSE NULL END) mon_sales,
+  sum(CASE WHEN (d_day_name = 'Tuesday')
+    THEN ss_sales_price
+      ELSE NULL END) tue_sales,
+  sum(CASE WHEN (d_day_name = 'Wednesday')
+    THEN ss_sales_price
+      ELSE NULL END) wed_sales,
+  sum(CASE WHEN (d_day_name = 'Thursday')
+    THEN ss_sales_price
+      ELSE NULL END) thu_sales,
+  sum(CASE WHEN (d_day_name = 'Friday')
+    THEN ss_sales_price
+      ELSE NULL END) fri_sales,
+  sum(CASE WHEN (d_day_name = 'Saturday')
+    THEN ss_sales_price
+      ELSE NULL END) 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 = 2000
+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/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q44.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q44.sql b/sql/core/src/test/resources/tpcds/q44.sql
new file mode 100755
index 0000000..379e604
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q44.sql
@@ -0,0 +1,46 @@
+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

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q45.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q45.sql b/sql/core/src/test/resources/tpcds/q45.sql
new file mode 100755
index 0000000..907438f
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q45.sql
@@ -0,0 +1,21 @@
+SELECT
+  ca_zip,
+  ca_city,
+  sum(ws_sales_price)
+FROM web_sales, customer, customer_address, date_dim, item
+WHERE ws_bill_customer_sk = c_customer_sk
+  AND c_current_addr_sk = ca_address_sk
+  AND ws_item_sk = i_item_sk
+  AND (substr(ca_zip, 1, 5) IN
+  ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792')
+  OR
+  i_item_id IN (SELECT i_item_id
+  FROM item
+  WHERE i_item_sk IN (2, 3, 5, 7, 11, 13, 17, 19, 23, 29)
+  )
+)
+  AND ws_sold_date_sk = d_date_sk
+  AND d_qoy = 2 AND d_year = 2001
+GROUP BY ca_zip, ca_city
+ORDER BY ca_zip, ca_city
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q46.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q46.sql b/sql/core/src/test/resources/tpcds/q46.sql
new file mode 100755
index 0000000..0911677
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q46.sql
@@ -0,0 +1,32 @@
+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,
+    sum(ss_coupon_amt) amt,
+    sum(ss_net_profit) 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 = 4 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 ('Fairview', 'Midway', 'Fairview', 'Fairview', 'Fairview')
+  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/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q47.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q47.sql b/sql/core/src/test/resources/tpcds/q47.sql
new file mode 100755
index 0000000..cfc37a4
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q47.sql
@@ -0,0 +1,63 @@
+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 = 1999 OR
+        (d_year = 1999 - 1 AND d_moy = 12) OR
+        (d_year = 1999 + 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.s_store_name,
+      v1.s_company_name,
+      v1.d_year,
+      v1.d_moy,
+      v1.avg_monthly_sales,
+      v1.sum_sales,
+      v1_lag.sum_sales psum,
+      v1_lead.sum_sales nsum
+    FROM v1, v1 v1_lag, v1 v1_lead
+    WHERE v1.i_category = v1_lag.i_category AND
+      v1.i_category = v1_lead.i_category AND
+      v1.i_brand = v1_lag.i_brand AND
+      v1.i_brand = v1_lead.i_brand AND
+      v1.s_store_name = v1_lag.s_store_name AND
+      v1.s_store_name = v1_lead.s_store_name AND
+      v1.s_company_name = v1_lag.s_company_name AND
+      v1.s_company_name = v1_lead.s_company_name AND
+      v1.rn = v1_lag.rn + 1 AND
+      v1.rn = v1_lead.rn - 1)
+SELECT *
+FROM v2
+WHERE d_year = 1999 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, 3
+LIMIT 100

http://git-wip-us.apache.org/repos/asf/spark/blob/a78d6ce3/sql/core/src/test/resources/tpcds/q48.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/tpcds/q48.sql b/sql/core/src/test/resources/tpcds/q48.sql
new file mode 100755
index 0000000..fdb9f38
--- /dev/null
+++ b/sql/core/src/test/resources/tpcds/q48.sql
@@ -0,0 +1,63 @@
+SELECT sum(ss_quantity)
+FROM store_sales, store, customer_demographics, customer_address, date_dim
+WHERE s_store_sk = ss_store_sk
+  AND ss_sold_date_sk = d_date_sk AND d_year = 2001
+  AND
+  (
+    (
+      cd_demo_sk = ss_cdemo_sk
+        AND
+        cd_marital_status = 'M'
+        AND
+        cd_education_status = '4 yr Degree'
+        AND
+        ss_sales_price BETWEEN 100.00 AND 150.00
+    )
+      OR
+      (
+        cd_demo_sk = ss_cdemo_sk
+          AND
+          cd_marital_status = 'D'
+          AND
+          cd_education_status = '2 yr Degree'
+          AND
+          ss_sales_price BETWEEN 50.00 AND 100.00
+      )
+      OR
+      (
+        cd_demo_sk = ss_cdemo_sk
+          AND
+          cd_marital_status = 'S'
+          AND
+          cd_education_status = 'College'
+          AND
+          ss_sales_price BETWEEN 150.00 AND 200.00
+      )
+  )
+  AND
+  (
+    (
+      ss_addr_sk = ca_address_sk
+        AND
+        ca_country = 'United States'
+        AND
+        ca_state IN ('CO', 'OH', 'TX')
+        AND ss_net_profit BETWEEN 0 AND 2000
+    )
+      OR
+      (ss_addr_sk = ca_address_sk
+        AND
+        ca_country = 'United States'
+        AND
+        ca_state IN ('OR', 'MN', 'KY')
+        AND ss_net_profit BETWEEN 150 AND 3000
+      )
+      OR
+      (ss_addr_sk = ca_address_sk
+        AND
+        ca_country = 'United States'
+        AND
+        ca_state IN ('VA', 'CA', 'MS')
+        AND ss_net_profit BETWEEN 50 AND 25000
+      )
+  )


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org


Mime
View raw message