You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@carbondata.apache.org by ja...@apache.org on 2018/06/11 13:25:53 UTC
[3/4] carbondata git commit: [CARBONDATA-2573] integrate carbonstore
mv branch
http://git-wip-us.apache.org/repos/asf/carbondata/blob/0ef7e55c/datamap/mv/core/src/test/scala/org/apache/carbondata/mv/rewrite/matching/TestTPCDS_1_4_Batch.scala
----------------------------------------------------------------------
diff --git a/datamap/mv/core/src/test/scala/org/apache/carbondata/mv/rewrite/matching/TestTPCDS_1_4_Batch.scala b/datamap/mv/core/src/test/scala/org/apache/carbondata/mv/rewrite/matching/TestTPCDS_1_4_Batch.scala
index 074bf00..e564052 100644
--- a/datamap/mv/core/src/test/scala/org/apache/carbondata/mv/rewrite/matching/TestTPCDS_1_4_Batch.scala
+++ b/datamap/mv/core/src/test/scala/org/apache/carbondata/mv/rewrite/matching/TestTPCDS_1_4_Batch.scala
@@ -1,20 +1,3 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one or more
- * contributor license agreements. See the NOTICE file distributed with
- * this work for additional information regarding copyright ownership.
- * The ASF licenses this file to You under the Apache License, Version 2.0
- * (the "License"); you may not use this file except in compliance with
- * the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing, software
- * distributed under the License is distributed on an "AS IS" BASIS,
- * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
- * See the License for the specific language governing permissions and
- * limitations under the License.
- */
-
package org.apache.carbondata.mv.rewrite.matching
object TestTPCDS_1_4_Batch {
@@ -32,73 +15,67 @@ object TestTPCDS_1_4_Batch {
|FROM item
|WHERE i_item_sk IN (2, 3, 5, 7, 11, 13, 17, 19)
""".stripMargin.trim,
- """
- |SELECT
- |FROM
- |WHERE
- """.stripMargin.trim),
+ """
+ """.stripMargin.trim),
// test case 2: test SELECT-SELECT-EXACT_MATCH with SELECT containing join (derive from q64)
- ("case_2",
- """
- |SELECT cs1.product_name, cs1.store_name, cs1.store_zip, cs1.b_street_number,
- | cs1.b_streen_name, cs1.b_city, cs1.b_zip, cs1.c_street_number, cs1.c_street_name,
- | cs1.c_city, cs1.c_zip, cs1.syear, cs1.cnt, cs1.s1, cs1.s2, cs1.s3, cs2.s1,
- | cs2.s2, cs2.s3, cs2.syear, cs2.cnt
- |FROM cross_sales cs1,cross_sales cs2
- |WHERE cs1.item_sk=cs2.item_sk AND
- | cs1.syear = 1999 AND
- | cs2.syear = 1999 + 1 AND
- | cs2.cnt <= cs1.cnt AND
- | cs1.store_name = cs2.store_name AND
- | cs1.store_zip = cs2.store_zip
- """.stripMargin.trim,
- """
- |SELECT cs1.product_name, cs1.store_name, cs1.store_zip, cs1.b_street_number,
- | cs1.b_streen_name, cs1.b_city, cs1.b_zip, cs1.c_street_number, cs1.c_street_name,
- | cs1.c_city, cs1.c_zip, cs1.syear, cs1.cnt, cs1.s1, cs1.s2, cs1.s3, cs2.s1,
- | cs2.s2, cs2.s3
- |FROM cross_sales cs1,cross_sales cs2
- |WHERE cs1.item_sk=cs2.item_sk AND
- | cs1.syear = 1999 AND
- | cs2.syear = 1999 + 1 AND
- | cs2.cnt <= cs1.cnt AND
- | cs1.store_name = cs2.store_name AND
- | cs1.store_zip = cs2.store_zip
- |ORDER BY cs1.product_name, cs1.store_name, cs2.cnt
- """.stripMargin.trim,
- """
- |SELECT
- |FROM
- |WHERE
- """.stripMargin.trim),
+ // cross_sales not in Tpcds_1_4_Tables.scala
+// ("case_2",
+// """
+// |SELECT cs1.product_name, cs1.store_name, cs1.store_zip, cs1.b_street_number,
+// | cs1.b_streen_name, cs1.b_city, cs1.b_zip, cs1.c_street_number, cs1.c_street_name,
+// | cs1.c_city, cs1.c_zip, cs1.syear, cs1.cnt, cs1.s1, cs1.s2, cs1.s3, cs2.s1,
+// | cs2.s2, cs2.s3, cs2.syear, cs2.cnt
+// |FROM cross_sales cs1,cross_sales cs2
+// |WHERE cs1.item_sk=cs2.item_sk AND
+// | cs1.syear = 1999 AND
+// | cs2.syear = 1999 + 1 AND
+// | cs2.cnt <= cs1.cnt AND
+// | cs1.store_name = cs2.store_name AND
+// | cs1.store_zip = cs2.store_zip
+// """.stripMargin.trim,
+// """
+// |SELECT cs1.product_name, cs1.store_name, cs1.store_zip, cs1.b_street_number,
+// | cs1.b_streen_name, cs1.b_city, cs1.b_zip, cs1.c_street_number, cs1.c_street_name,
+// | cs1.c_city, cs1.c_zip, cs1.syear, cs1.cnt, cs1.s1, cs1.s2, cs1.s3, cs2.s1,
+// | cs2.s2, cs2.s3
+// |FROM cross_sales cs1,cross_sales cs2
+// |WHERE cs1.item_sk=cs2.item_sk AND
+// | cs1.syear = 1999 AND
+// | cs2.syear = 1999 + 1 AND
+// | cs2.cnt <= cs1.cnt AND
+// | cs1.store_name = cs2.store_name AND
+// | cs1.store_zip = cs2.store_zip
+// |ORDER BY cs1.product_name, cs1.store_name, cs2.cnt
+// """.stripMargin.trim,
+// """
+// |SELECT
+// |FROM
+// |WHERE
+// """.stripMargin.trim),
// test case 3: test simple SELECT with GROUPBY (from q99)
("case_3",
"""
|SELECT count(ss_sold_date_sk) as not_null_total,
| max(ss_sold_date_sk) as max_ss_sold_date_sk,
| max(ss_sold_time_sk) as max_ss_sold_time_sk,
- | ss_item_sk,
- | ss_store_sk
+ | ss_item_sk, ss_store_sk
|FROM store_sales
|GROUP BY ss_item_sk, ss_store_sk
""".stripMargin.trim,
"""
|SELECT count(ss_sold_date_sk) as not_null_total,
| max(ss_sold_date_sk) as max_ss_sold_date_sk,
- | ss_item_sk,
- | ss_store_sk
+ | ss_item_sk, ss_store_sk
|FROM store_sales
|GROUP BY ss_item_sk, ss_store_sk
""".stripMargin.trim,
"""
- |SELECT gen_subsumer_0.`not_null_total`,
- | gen_subsumer_0.`max_ss_sold_date_sk`,
- | gen_subsumer_0.`ss_item_sk`,
- | gen_subsumer_0.`ss_store_sk`
+ |SELECT gen_subsumer_0.`not_null_total` AS `not_null_total`, gen_subsumer_0.`max_ss_sold_date_sk` AS `max_ss_sold_date_sk`, gen_subsumer_0.`ss_item_sk`, gen_subsumer_0.`ss_store_sk`
|FROM
- | (SELECT count(`ss_sold_date_sk`) AS `not_null_total`, max(`ss_sold_date_sk`) AS `max_ss_sold_date_sk`, max(`ss_sold_time_sk`) AS `max_ss_sold_time_sk`, `ss_item_sk`, `ss_store_sk`
- | FROM store_sales
- | GROUP BY `ss_item_sk`, `ss_store_sk`) gen_subsumer_0
+ | (SELECT count(store_sales.`ss_sold_date_sk`) AS `not_null_total`, max(store_sales.`ss_sold_date_sk`) AS `max_ss_sold_date_sk`, max(store_sales.`ss_sold_time_sk`) AS `max_ss_sold_time_sk`, store_sales.`ss_item_sk`, store_sales.`ss_store_sk`
+ | FROM
+ | store_sales
+ | GROUP BY store_sales.`ss_item_sk`, store_sales.`ss_store_sk`) gen_subsumer_0
""".stripMargin.trim),
// test case 4 test SELECT containing join with GROUPBY (from q65)
("case_4",
@@ -115,11 +92,11 @@ object TestTPCDS_1_4_Batch {
|GROUP BY ss_store_sk, ss_item_sk
""".stripMargin.trim,
"""
- |SELECT `ss_store_sk`, `ss_item_sk`, sum(`ss_sales_price`) AS `revenue`
+ |SELECT store_sales.`ss_store_sk`, store_sales.`ss_item_sk`, sum(store_sales.`ss_sales_price`) AS `revenue`
|FROM
| store_sales
- | INNER JOIN date_dim ON (`d_month_seq` >= 1176) AND (`d_month_seq` <= 1187) AND (`ss_sold_date_sk` = `d_date_sk`)
- |GROUP BY `ss_store_sk`, `ss_item_sk`
+ | INNER JOIN date_dim ON (date_dim.`d_month_seq` >= 1176) AND (date_dim.`d_month_seq` <= 1187) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
+ |GROUP BY store_sales.`ss_store_sk`, store_sales.`ss_item_sk`
""".stripMargin.trim),
// the following 6 cases involve an MV of store_sales, item, date_dim
// q3
@@ -152,12 +129,12 @@ object TestTPCDS_1_4_Batch {
"""
|SELECT gen_subsumer_0.`d_year`, gen_subsumer_0.`i_brand_id` AS `brand_id`, gen_subsumer_0.`i_brand` AS `brand`, sum(gen_subsumer_0.`sum_agg`) AS `sum_agg`
|FROM
- | (SELECT `d_date`, `d_moy`, `d_year`, `i_brand`, `i_brand_id`, `i_item_id`, `i_item_desc`, `i_manufact_id`, substring(`i_item_desc`, 1, 30) AS `itemdesc`, `i_category`, `i_class`, `i_current_price`, `i_item_sk`, `ss_store_sk`, sum(`ss_ext_sales_price`) AS `sum_agg`, sum((CAST(CAST(`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(`ss_list_price` AS DECIMAL(12,2)))) AS `sales`, count(1) AS `number_sales`
+ | (SELECT dt.`d_date`, dt.`d_moy`, dt.`d_year`, item.`i_brand`, item.`i_brand_id`, item.`i_item_id`, item.`i_item_desc`, item.`i_manufact_id`, substring(item.`i_item_desc`, 1, 30) AS `itemdesc`, item.`i_category`, item.`i_class`, item.`i_current_price`, item.`i_item_sk`, store_sales.`ss_store_sk`, sum(store_sales.`ss_ext_sales_price`) AS `sum_agg`, sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_list_price` AS DECIMAL(12,2)))) AS `sales`, count(1) AS `number_sales`
| FROM
| date_dim dt
- | INNER JOIN store_sales ON (`d_date_sk` = `ss_sold_date_sk`)
- | INNER JOIN item ON (`ss_item_sk` = `i_item_sk`)
- | GROUP BY `d_date`, `d_moy`, `d_year`, `i_brand`, `i_brand_id`, `i_item_id`, `i_item_desc`, `i_manufact_id`, substring(`i_item_desc`, 1, 30), `i_category`, `i_category_id`, `i_class`, `i_class_id`, `i_current_price`, `i_manager_id`, `i_item_sk`, `ss_store_sk`) gen_subsumer_0
+ | INNER JOIN store_sales ON (dt.`d_date_sk` = store_sales.`ss_sold_date_sk`)
+ | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`)
+ | GROUP BY dt.`d_date`, dt.`d_moy`, dt.`d_year`, item.`i_brand`, item.`i_brand_id`, item.`i_item_id`, item.`i_item_desc`, item.`i_manufact_id`, substring(item.`i_item_desc`, 1, 30), item.`i_category`, item.`i_category_id`, item.`i_class`, item.`i_class_id`, item.`i_current_price`, item.`i_manager_id`, item.`i_item_sk`, store_sales.`ss_store_sk`) gen_subsumer_0
|WHERE
| (gen_subsumer_0.`d_moy` = 11) AND (gen_subsumer_0.`i_manufact_id` = 128)
|GROUP BY gen_subsumer_0.`d_year`, gen_subsumer_0.`i_brand`, gen_subsumer_0.`i_brand_id`
@@ -234,7 +211,7 @@ object TestTPCDS_1_4_Batch {
| FROM
| date_dim dt
| INNER JOIN store_sales ON (dt.`d_date_sk` = store_sales.`ss_sold_date_sk`)
- | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`)
+ | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`)
| GROUP BY dt.`d_date`, dt.`d_moy`, dt.`d_year`, item.`i_brand`, item.`i_brand_id`, item.`i_item_id`, item.`i_item_desc`, item.`i_manufact_id`, substring(item.`i_item_desc`, 1, 30), item.`i_category`, item.`i_category_id`, item.`i_class`, item.`i_class_id`, item.`i_current_price`, item.`i_manager_id`, item.`i_item_sk`, store_sales.`ss_store_sk`) gen_subsumer_0
| WHERE
| (gen_subsumer_0.`d_year` IN (2000, 2001, 2002, 2003))
@@ -242,8 +219,8 @@ object TestTPCDS_1_4_Batch {
| LEFT SEMI JOIN (SELECT customer.`c_customer_sk`, sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#271 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#274 as decimal(12,2)))), DecimalType(18,2)))`
| FROM
| store_sales
- | INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`)
- | GROUP BY customer.`c_customer_sk`) gen_subquery_1 ON (CAST(gen_subquery_1.`sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#271 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#274 as decimal(12,2)))), DecimalType(18,2)))` AS DECIMAL(38,8)) > (0.500000BD * CAST((SELECT max(gen_scalar_subquery_0_0.`csales`) AS `tpcds_cmax` FROM (SELECT sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` FROM store_sales INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) GROUP BY customer.`c_customer_sk`) gen_scalar_subquery_0_0 ) AS DECIMAL(32,6)))) AND (catalog_sales.`cs_bill_customer_sk` = gen_subquery_1.`c_customer_sk`)
+ | INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`)
+ | GROUP BY customer.`c_customer_sk`) gen_subquery_1 ON (CAST(gen_subquery_1.`sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#271 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#274 as decimal(12,2)))), DecimalType(18,2)))` AS DECIMAL(38,8)) > (0.500000BD * CAST((SELECT max(gen_expression_0_0.`csales`) AS `tpcds_cmax` FROM (SELECT sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` FROM store_sales INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) GROUP BY customer.`c_customer_sk`) gen_expression_0_0 ) AS DECIMAL(32,6)))) AND (catalog_sales.`cs_bill_customer_sk` = gen_subquery_1.`c_customer_sk`)
| INNER JOIN date_dim ON (date_dim.`d_year` = 2000) AND (date_dim.`d_moy` = 2) AND (catalog_sales.`cs_sold_date_sk` = date_dim.`d_date_sk`)
| UNION ALL
| SELECT (CAST(CAST(web_sales.`ws_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(web_sales.`ws_list_price` AS DECIMAL(12,2))) AS `sales`
@@ -255,7 +232,7 @@ object TestTPCDS_1_4_Batch {
| FROM
| date_dim dt
| INNER JOIN store_sales ON (dt.`d_date_sk` = store_sales.`ss_sold_date_sk`)
- | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`)
+ | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`)
| GROUP BY dt.`d_date`, dt.`d_moy`, dt.`d_year`, item.`i_brand`, item.`i_brand_id`, item.`i_item_id`, item.`i_item_desc`, item.`i_manufact_id`, substring(item.`i_item_desc`, 1, 30), item.`i_category`, item.`i_category_id`, item.`i_class`, item.`i_class_id`, item.`i_current_price`, item.`i_manager_id`, item.`i_item_sk`, store_sales.`ss_store_sk`) gen_subsumer_1
| WHERE
| (gen_subsumer_1.`d_year` IN (2000, 2001, 2002, 2003))
@@ -263,8 +240,8 @@ object TestTPCDS_1_4_Batch {
| LEFT SEMI JOIN (SELECT customer.`c_customer_sk`, sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#271 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#274 as decimal(12,2)))), DecimalType(18,2)))`
| FROM
| store_sales
- | INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`)
- | GROUP BY customer.`c_customer_sk`) gen_subquery_3 ON (CAST(gen_subquery_3.`sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#271 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#274 as decimal(12,2)))), DecimalType(18,2)))` AS DECIMAL(38,8)) > (0.500000BD * CAST((SELECT max(gen_scalar_subquery_1_0.`csales`) AS `tpcds_cmax` FROM (SELECT sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` FROM store_sales INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) GROUP BY customer.`c_customer_sk`) gen_scalar_subquery_1_0 ) AS DECIMAL(32,6)))) AND (web_sales.`ws_bill_customer_sk` = gen_subquery_3.`c_customer_sk`)
+ | INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`)
+ | GROUP BY customer.`c_customer_sk`) gen_subquery_3 ON (CAST(gen_subquery_3.`sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#271 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#274 as decimal(12,2)))), DecimalType(18,2)))` AS DECIMAL(38,8)) > (0.500000BD * CAST((SELECT max(gen_expression_1_0.`csales`) AS `tpcds_cmax` FROM (SELECT sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` FROM store_sales INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) GROUP BY customer.`c_customer_sk`) gen_expression_1_0 ) AS DECIMAL(32,6)))) AND (web_sales.`ws_bill_customer_sk` = gen_subquery_3.`c_customer_sk`)
| INNER JOIN date_dim ON (date_dim.`d_year` = 2000) AND (date_dim.`d_moy` = 2) AND (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`)) gen_subquery_4
|LIMIT 100
""".stripMargin.trim),
@@ -365,10 +342,7 @@ object TestTPCDS_1_4_Batch {
| limit 100
""".stripMargin.trim,
"""
- |
- |
- |
- """.stripMargin.trim),
+ """.stripMargin.trim),
// q55
("case_8",
"""
@@ -401,12 +375,12 @@ object TestTPCDS_1_4_Batch {
"""
|SELECT gen_subsumer_0.`i_brand_id` AS `brand_id`, gen_subsumer_0.`i_brand` AS `brand`, sum(gen_subsumer_0.`sum_agg`) AS `ext_price`
|FROM
- | (SELECT `d_date`, `d_moy`, `d_year`, `i_brand`, `i_brand_id`, `i_item_id`, `i_item_desc`, substring(`i_item_desc`, 1, 30) AS `itemdesc`, `i_category`, `i_class`, `i_manager_id`, `i_current_price`, `i_item_sk`, `ss_store_sk`, sum(`ss_ext_sales_price`) AS `sum_agg`, sum((CAST(CAST(`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(`ss_list_price` AS DECIMAL(12,2)))) AS `sales`, count(1) AS `number_sales`
+ | (SELECT dt.`d_date`, dt.`d_moy`, dt.`d_year`, item.`i_brand`, item.`i_brand_id`, item.`i_item_id`, item.`i_item_desc`, substring(item.`i_item_desc`, 1, 30) AS `itemdesc`, item.`i_category`, item.`i_class`, item.`i_manager_id`, item.`i_current_price`, item.`i_item_sk`, store_sales.`ss_store_sk`, sum(store_sales.`ss_ext_sales_price`) AS `sum_agg`, sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_list_price` AS DECIMAL(12,2)))) AS `sales`, count(1) AS `number_sales`
| FROM
| date_dim dt
- | INNER JOIN store_sales ON (`d_date_sk` = `ss_sold_date_sk`)
- | INNER JOIN item ON (`ss_item_sk` = `i_item_sk`)
- | GROUP BY `d_date`, `d_moy`, `d_year`, `i_brand`, `i_brand_id`, `i_item_id`, `i_item_desc`, substring(`i_item_desc`, 1, 30), `i_category`, `i_category_id`, `i_class`, `i_class_id`, `i_current_price`, `i_manager_id`, `i_item_sk`, `ss_store_sk`) gen_subsumer_0
+ | INNER JOIN store_sales ON (dt.`d_date_sk` = store_sales.`ss_sold_date_sk`)
+ | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`)
+ | GROUP BY dt.`d_date`, dt.`d_moy`, dt.`d_year`, item.`i_brand`, item.`i_brand_id`, item.`i_item_id`, item.`i_item_desc`, substring(item.`i_item_desc`, 1, 30), item.`i_category`, item.`i_category_id`, item.`i_class`, item.`i_class_id`, item.`i_current_price`, item.`i_manager_id`, item.`i_item_sk`, store_sales.`ss_store_sk`) gen_subsumer_0
|WHERE
| (gen_subsumer_0.`d_moy` = 11) AND (gen_subsumer_0.`d_year` = 1999) AND (gen_subsumer_0.`i_manager_id` = 28)
|GROUP BY gen_subsumer_0.`i_brand`, gen_subsumer_0.`i_brand_id`
@@ -448,14 +422,28 @@ object TestTPCDS_1_4_Batch {
| i_category, i_class, i_item_id, i_item_desc, revenueratio
""".stripMargin.trim,
"""
- |
- |
- |
+ |SELECT gen_subquery_1.`i_item_desc`, gen_subquery_1.`i_category`, gen_subquery_1.`i_class`, gen_subquery_1.`i_current_price`, gen_subquery_1.`itemrevenue`, ((gen_subquery_1.`_w0` * 100.00BD) / CAST(gen_subquery_1.`_we0` AS DECIMAL(28,2))) AS `revenueratio`
+ |FROM
+ | (SELECT gen_subquery_0.`i_item_desc`, gen_subquery_0.`i_category`, gen_subquery_0.`i_class`, gen_subquery_0.`i_current_price`, gen_subquery_0.`itemrevenue`, gen_subquery_0.`_w0`, gen_subquery_0.`_w1`, gen_subquery_0.`i_item_id`, sum(gen_subquery_0.`_w1`) OVER (PARTITION BY gen_subquery_0.`i_class` ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS `_we0`
+ | FROM
+ | (SELECT gen_subsumer_0.`i_item_desc`, gen_subsumer_0.`i_category`, gen_subsumer_0.`i_class`, gen_subsumer_0.`i_current_price`, sum(gen_subsumer_0.`sum_agg`) AS `itemrevenue`, sum(gen_subsumer_0.`sum_agg`) AS `_w0`, sum(gen_subsumer_0.`sum_agg`) AS `_w1`, gen_subsumer_0.`i_item_id`
+ | FROM
+ | (SELECT dt.`d_date`, dt.`d_moy`, dt.`d_year`, item.`i_brand`, item.`i_brand_id`, item.`i_item_id`, item.`i_item_desc`, substring(item.`i_item_desc`, 1, 30) AS `itemdesc`, item.`i_category`, item.`i_class`, item.`i_manager_id`, item.`i_current_price`, item.`i_item_sk`, store_sales.`ss_store_sk`, sum(store_sales.`ss_ext_sales_price`) AS `sum_agg`, sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_list_price` AS DECIMAL(12,2)))) AS `sales`, count(1) AS `number_sales`
+ | FROM
+ | date_dim dt
+ | INNER JOIN store_sales ON (dt.`d_date_sk` = store_sales.`ss_sold_date_sk`)
+ | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`)
+ | GROUP BY dt.`d_date`, dt.`d_moy`, dt.`d_year`, item.`i_brand`, item.`i_brand_id`, item.`i_item_id`, item.`i_item_desc`, substring(item.`i_item_desc`, 1, 30), item.`i_category`, item.`i_category_id`, item.`i_class`, item.`i_class_id`, item.`i_current_price`, item.`i_manager_id`, item.`i_item_sk`, store_sales.`ss_store_sk`) gen_subsumer_0
+ | WHERE
+ | (gen_subsumer_0.`i_category` IN ('Sports', 'Books', 'Home')) AND (gen_subsumer_0.`d_date` >= DATE '1999-02-22') AND (gen_subsumer_0.`d_date` <= DATE '1999-03-24')
+ | GROUP BY gen_subsumer_0.`i_item_id`, gen_subsumer_0.`i_item_desc`, gen_subsumer_0.`i_category`, gen_subsumer_0.`i_class`, gen_subsumer_0.`i_current_price`) gen_subquery_0 ) gen_subquery_1
+ |ORDER BY gen_subquery_1.`i_category` ASC NULLS FIRST, gen_subquery_1.`i_class` ASC NULLS FIRST, gen_subquery_1.`i_item_id` ASC NULLS FIRST, gen_subquery_1.`i_item_desc` ASC NULLS FIRST, `revenueratio` ASC NULLS FIRST
""".stripMargin.trim),
- // q76
+ // q76
+ // this case requires a rule of PushAggregateThroughUnion for rewrite to work, which is not implemented for now
("case_10",
"""
- |SELECT dt.d_date, dt.d_moy, dt.d_year, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc,
+ |SELECT dt.d_date, dt.d_moy, dt.d_year, dt.d_qoy, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc,
| substr(item.i_item_desc, 1, 30) itemdesc, item.i_category, item.i_class,
| item.i_manager_id, item.i_current_price, item.i_item_sk, store_sales.ss_store_sk,
| SUM(store_sales.ss_ext_sales_price) sum_agg,
@@ -463,7 +451,7 @@ object TestTPCDS_1_4_Batch {
|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
- |GROUP BY dt.d_date, dt.d_moy, dt.d_year, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc,
+ |GROUP BY dt.d_date, dt.d_moy, dt.d_qoy, dt.d_year, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc,
| substr(item.i_item_desc, 1, 30), item.i_category, item.i_category_id,
| item.i_class, item.i_class_id, item.i_current_price, item.i_manager_id,
| item.i_item_sk, store_sales.ss_store_sk
@@ -501,15 +489,12 @@ object TestTPCDS_1_4_Batch {
| limit 100
""".stripMargin.trim,
"""
- |
- |
- |
- """.stripMargin.trim),
+ """.stripMargin.trim),
// the following four cases involve a MV of catalog_sales, item, date_dim
// q20
("case_11",
"""
- |SELECT cs_ship_addr_sk , d_date, d_year, d_qoy, d_moy, i_category,i_item_sk, i_item_id,
+ |SELECT cs_ship_addr_sk , d_date, d_year, d_qoy, d_moy, i_category, cs_ship_addr_sk,i_item_sk, i_item_id,
| i_item_desc, i_class, i_current_price, i_brand_id, i_class_id, i_category_id, i_manufact_id,
| SUM(cs_ext_sales_price) sales_amt,
| SUM(cs_quantity*cs_list_price) sales,
@@ -546,19 +531,19 @@ object TestTPCDS_1_4_Batch {
| FROM
| (SELECT gen_subsumer_0.`i_item_desc`, gen_subsumer_0.`i_category`, gen_subsumer_0.`i_class`, gen_subsumer_0.`i_current_price`, sum(gen_subsumer_0.`sales_amt`) AS `itemrevenue`, sum(gen_subsumer_0.`sales_amt`) AS `_w0`, sum(gen_subsumer_0.`sales_amt`) AS `_w1`, gen_subsumer_0.`i_item_id`
| FROM
- | (SELECT `cs_ship_addr_sk`, `d_date`, `d_year`, `d_qoy`, `d_moy`, `i_category`, `cs_ship_addr_sk`, `i_item_sk`, `i_item_id`, `i_item_desc`, `i_class`, `i_current_price`, `i_brand_id`, `i_class_id`, `i_category_id`, `i_manufact_id`, sum(`cs_ext_sales_price`) AS `sales_amt`, sum((CAST(CAST(`cs_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(`cs_list_price` AS DECIMAL(12,2)))) AS `sales`, sum(`cs_ext_discount_amt`) AS `excess discount amount`, count(1) AS `number_sales`
+ | (SELECT catalog_sales.`cs_ship_addr_sk`, date_dim.`d_date`, date_dim.`d_year`, date_dim.`d_qoy`, date_dim.`d_moy`, item.`i_category`, catalog_sales.`cs_ship_addr_sk`, item.`i_item_sk`, item.`i_item_id`, item.`i_item_desc`, item.`i_class`, item.`i_current_price`, item.`i_brand_id`, item.`i_class_id`, item.`i_category_id`, item.`i_manufact_id`, sum(catalog_sales.`cs_ext_sales_price`) AS `sales_amt`, sum((CAST(CAST(catalog_sales.`cs_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(catalog_sales.`cs_list_price` AS DECIMAL(12,2)))) AS `sales`, sum(catalog_sales.`cs_ext_discount_amt`) AS `excess discount amount`, count(1) AS `number_sales`
| FROM
| catalog_sales
- | INNER JOIN item ON (`cs_item_sk` = `i_item_sk`)
- | INNER JOIN date_dim ON (`cs_sold_date_sk` = `d_date_sk`)
- | GROUP BY `i_brand_id`, `i_class_id`, `i_category_id`, `i_item_id`, `i_item_desc`, `i_category`, `i_class`, `i_current_price`, `i_manufact_id`, `d_date`, `d_moy`, `d_qoy`, `d_year`, `cs_ship_addr_sk`, `i_item_sk`) gen_subsumer_0
+ | INNER JOIN item ON (catalog_sales.`cs_item_sk` = item.`i_item_sk`)
+ | INNER JOIN date_dim ON (catalog_sales.`cs_sold_date_sk` = date_dim.`d_date_sk`)
+ | GROUP BY item.`i_brand_id`, item.`i_class_id`, item.`i_category_id`, item.`i_item_id`, item.`i_item_desc`, item.`i_category`, item.`i_class`, item.`i_current_price`, item.`i_manufact_id`, date_dim.`d_date`, date_dim.`d_moy`, date_dim.`d_qoy`, date_dim.`d_year`, catalog_sales.`cs_ship_addr_sk`, item.`i_item_sk`) gen_subsumer_0
| WHERE
| (gen_subsumer_0.`i_category` IN ('Sports', 'Books', 'Home')) AND (gen_subsumer_0.`d_date` >= DATE '1999-02-22') AND (gen_subsumer_0.`d_date` <= DATE '1999-03-24')
| GROUP BY gen_subsumer_0.`i_item_id`, gen_subsumer_0.`i_item_desc`, gen_subsumer_0.`i_category`, gen_subsumer_0.`i_class`, gen_subsumer_0.`i_current_price`) gen_subquery_0 ) gen_subquery_1
|ORDER BY gen_subquery_1.`i_category` ASC NULLS FIRST, gen_subquery_1.`i_class` ASC NULLS FIRST, gen_subquery_1.`i_item_id` ASC NULLS FIRST, gen_subquery_1.`i_item_desc` ASC NULLS FIRST, `revenueratio` ASC NULLS FIRST
|LIMIT 100
""".stripMargin.trim),
- // q32
+ // q32 - no rewrite
("case_12",
"""
|SELECT cs_ship_addr_sk , d_date, d_year, d_qoy, d_moy, i_category, cs_ship_addr_sk,i_item_sk, i_item_id,
@@ -591,18 +576,15 @@ object TestTPCDS_1_4_Batch {
|limit 100
""".stripMargin.trim,
"""
- |
- |
- |
- """.stripMargin.trim),
- // q58 debug
+ """.stripMargin.trim),
+ // q58 debug - no rewrite
("case_13",
"""
|SELECT cs_ship_addr_sk , d_date, d_year, d_qoy, d_moy, i_category, cs_ext_sales_price, cs_ship_addr_sk, i_item_sk, i_item_id,
| i_item_desc, i_class, i_current_price, i_brand_id, i_class_id, i_category_id, i_manufact_id,
| SUM(cs_ext_sales_price) sales_amt,
| SUM(cs_ext_sales_price)*100/sum(sum(cs_ext_sales_price)) over
- | (partition by i_class) as revenueratio
+ | (partition by i_class) as revenueratio,
| SUM(cs_quantity*cs_list_price) sales,
| SUM(cs_ext_discount_amt) as `excess discount amount`,
| count(*) number_sales
@@ -610,7 +592,7 @@ object TestTPCDS_1_4_Batch {
|WHERE cs_item_sk = i_item_sk
| AND cs_sold_date_sk = d_date_sk
|GROUP BY i_brand_id, i_class_id, i_category_id, i_item_id, i_item_desc, i_category, i_class,
- | i_current_price, i_manufact_id, d_date, d_moy, d_qoy, d_year, cs_ship_addr_sk, i_item_sk
+ | i_current_price, i_manufact_id, d_date, d_moy, d_qoy, d_year, cs_ext_sales_price, cs_ship_addr_sk, i_item_sk
""".stripMargin.trim,
"""
| with ss_items as
@@ -668,11 +650,8 @@ object TestTPCDS_1_4_Batch {
| limit 100
""".stripMargin.trim,
"""
- |
- |
- |
- """.stripMargin.trim),
- // q76
+ """.stripMargin.trim),
+ // q76 - as case 10, require the rule PushAggregateThroughUnion
("case_14",
"""
|SELECT cs_ship_addr_sk , d_date, d_year, d_qoy, d_moy, i_category, cs_ext_sales_price, cs_ship_addr_sk, i_item_sk, i_item_id,
@@ -685,7 +664,8 @@ object TestTPCDS_1_4_Batch {
|WHERE cs_item_sk = i_item_sk
| AND cs_sold_date_sk = d_date_sk
|GROUP BY i_brand_id, i_class_id, i_category_id, i_item_id, i_item_desc, i_category, i_class,
- | i_current_price, i_manufact_id, d_date, d_moy, d_qoy, d_year, cs_ship_addr_sk, i_item_sk
+ | i_current_price, i_manufact_id, d_date, d_moy, d_qoy, d_year, cs_ship_addr_sk, i_item_sk,
+ | cs_ext_sales_price
""".stripMargin.trim,
"""
| SELECT
@@ -720,10 +700,7 @@ object TestTPCDS_1_4_Batch {
| limit 100
""".stripMargin.trim,
"""
- |
- |
- |
- """.stripMargin.trim),
+ """.stripMargin.trim),
// the following two cases involve a MV of store_sales and customer
// q23a
("case_15",
@@ -777,10 +754,45 @@ object TestTPCDS_1_4_Batch {
| limit 100
""".stripMargin.trim,
"""
- |
- |
- |
- """.stripMargin.trim),
+ |SELECT sum(gen_subquery_4.`sales`) AS `sum(sales)`
+ |FROM
+ | (SELECT (CAST(CAST(catalog_sales.`cs_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(catalog_sales.`cs_list_price` AS DECIMAL(12,2))) AS `sales`
+ | FROM
+ | catalog_sales
+ | LEFT SEMI JOIN (SELECT item.`i_item_sk` AS `item_sk`, count(1) AS `count(1)`
+ | FROM
+ | store_sales
+ | INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
+ | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`)
+ | GROUP BY substring(item.`i_item_desc`, 1, 30), item.`i_item_sk`, date_dim.`d_date`) gen_subquery_0 ON (gen_subquery_0.`count(1)` > 4L) AND (catalog_sales.`cs_item_sk` = gen_subquery_0.`item_sk`)
+ | LEFT SEMI JOIN (SELECT customer.`c_customer_sk`, sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales`
+ | FROM
+ | customer
+ | INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`)
+ | GROUP BY customer.`c_customer_sk`) gen_subquery_1 ON (catalog_sales.`cs_bill_customer_sk` = gen_subquery_1.`c_customer_sk`)
+ | INNER JOIN date_dim ON (date_dim.`d_year` = 2000) AND (date_dim.`d_moy` = 2) AND (catalog_sales.`cs_sold_date_sk` = date_dim.`d_date_sk`)
+ | WHERE
+ | (CAST(`sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#219 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#222 as decimal(12,2)))), DecimalType(18,2)))` AS DECIMAL(38,8)) > (0.500000BD * CAST((SELECT max(gen_expression_0_0.`csales`) AS `tpcds_cmax` FROM (SELECT sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` FROM store_sales INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) GROUP BY customer.`c_customer_sk`) gen_expression_0_0 ) AS DECIMAL(32,6))))
+ | UNION ALL
+ | SELECT (CAST(CAST(web_sales.`ws_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(web_sales.`ws_list_price` AS DECIMAL(12,2))) AS `sales`
+ | FROM
+ | web_sales
+ | LEFT SEMI JOIN (SELECT item.`i_item_sk` AS `item_sk`, count(1) AS `count(1)`
+ | FROM
+ | store_sales
+ | INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
+ | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`)
+ | GROUP BY substring(item.`i_item_desc`, 1, 30), item.`i_item_sk`, date_dim.`d_date`) gen_subquery_2 ON (gen_subquery_2.`count(1)` > 4L) AND (web_sales.`ws_item_sk` = gen_subquery_2.`item_sk`)
+ | LEFT SEMI JOIN (SELECT customer.`c_customer_sk`, sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales`
+ | FROM
+ | customer
+ | INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`)
+ | GROUP BY customer.`c_customer_sk`) gen_subquery_3 ON (web_sales.`ws_bill_customer_sk` = gen_subquery_3.`c_customer_sk`)
+ | INNER JOIN date_dim ON (date_dim.`d_year` = 2000) AND (date_dim.`d_moy` = 2) AND (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`)
+ | WHERE
+ | (CAST(`sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#219 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#222 as decimal(12,2)))), DecimalType(18,2)))` AS DECIMAL(38,8)) > (0.500000BD * CAST((SELECT max(gen_expression_1_0.`csales`) AS `tpcds_cmax` FROM (SELECT sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` FROM store_sales INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) GROUP BY customer.`c_customer_sk`) gen_expression_1_0 ) AS DECIMAL(32,6))))) gen_subquery_4
+ |LIMIT 100
+ """.stripMargin.trim),
// q23b
("case_16",
"""
@@ -791,7 +803,6 @@ object TestTPCDS_1_4_Batch {
| GROUP BY c_customer_sk
""".stripMargin.trim,
"""
- |
| 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
@@ -851,17 +862,17 @@ object TestTPCDS_1_4_Batch {
| FROM
| store_sales
| INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
- | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`)
+ | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`)
| GROUP BY substring(item.`i_item_desc`, 1, 30), item.`i_item_sk`, date_dim.`d_date`) gen_subquery_0 ON (gen_subquery_0.`count(1)` > 4L) AND (catalog_sales.`cs_item_sk` = gen_subquery_0.`item_sk`)
| LEFT SEMI JOIN (SELECT customer.`c_customer_sk`, sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales`
| FROM
- | customer
+ | customer
| INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`)
| GROUP BY customer.`c_customer_sk`) gen_subquery_1 ON (catalog_sales.`cs_bill_customer_sk` = gen_subquery_1.`c_customer_sk`)
| INNER JOIN customer ON (catalog_sales.`cs_bill_customer_sk` = customer.`c_customer_sk`)
| INNER JOIN date_dim ON (date_dim.`d_year` = 2000) AND (date_dim.`d_moy` = 2) AND (catalog_sales.`cs_sold_date_sk` = date_dim.`d_date_sk`)
| WHERE
- | (CAST(`sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#219 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#222 as decimal(12,2)))), DecimalType(18,2)))` AS DECIMAL(38,8)) > (0.500000BD * CAST((SELECT max(gen_expression_0_0.`csales`) AS `tpcds_cmax` FROM (SELECT sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` FROM store_sales INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) GROUP BY customer.`c_customer_sk`) gen_expression_0_0 ) AS DECIMAL(32,6))))) gen_subquery_2
+ | (CAST(`sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#219 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#222 as decimal(12,2)))), DecimalType(18,2)))` AS DECIMAL(38,8)) > (0.500000BD * CAST((SELECT max(gen_expression_0_0.`csales`) AS `tpcds_cmax` FROM (SELECT sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` FROM store_sales INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) GROUP BY customer.`c_customer_sk`) gen_expression_0_0 ) AS DECIMAL(32,6))))) gen_subquery_2
| GROUP BY gen_subquery_2.`c_last_name`, gen_subquery_2.`c_first_name`
| UNION ALL
| SELECT gen_subquery_5.`c_last_name`, gen_subquery_5.`c_first_name`, sum((CAST(CAST(gen_subquery_5.`ws_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(gen_subquery_5.`ws_list_price` AS DECIMAL(12,2)))) AS `sales`
@@ -873,17 +884,17 @@ object TestTPCDS_1_4_Batch {
| FROM
| store_sales
| INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
- | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`)
+ | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`)
| GROUP BY substring(item.`i_item_desc`, 1, 30), item.`i_item_sk`, date_dim.`d_date`) gen_subquery_3 ON (gen_subquery_3.`count(1)` > 4L) AND (web_sales.`ws_item_sk` = gen_subquery_3.`item_sk`)
| LEFT SEMI JOIN (SELECT customer.`c_customer_sk`, sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales`
| FROM
- | customer
+ | customer
| INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`)
| GROUP BY customer.`c_customer_sk`) gen_subquery_4 ON (web_sales.`ws_bill_customer_sk` = gen_subquery_4.`c_customer_sk`)
| INNER JOIN customer ON (web_sales.`ws_bill_customer_sk` = customer.`c_customer_sk`)
| INNER JOIN date_dim ON (date_dim.`d_year` = 2000) AND (date_dim.`d_moy` = 2) AND (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`)
| WHERE
- | (CAST(`sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#219 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#222 as decimal(12,2)))), DecimalType(18,2)))` AS DECIMAL(38,8)) > (0.500000BD * CAST((SELECT max(gen_expression_1_0.`csales`) AS `tpcds_cmax` FROM (SELECT sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` FROM store_sales INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) GROUP BY customer.`c_customer_sk`) gen_expression_1_0 ) AS DECIMAL(32,6))))) gen_subquery_5
+ | (CAST(`sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#219 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#222 as decimal(12,2)))), DecimalType(18,2)))` AS DECIMAL(38,8)) > (0.500000BD * CAST((SELECT max(gen_expression_1_0.`csales`) AS `tpcds_cmax` FROM (SELECT sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` FROM store_sales INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) GROUP BY customer.`c_customer_sk`) gen_expression_1_0 ) AS DECIMAL(32,6))))) gen_subquery_5
| GROUP BY gen_subquery_5.`c_last_name`, gen_subquery_5.`c_first_name`) gen_subquery_6
|ORDER BY gen_subquery_6.`c_last_name` ASC NULLS FIRST, gen_subquery_6.`c_first_name` ASC NULLS FIRST, gen_subquery_6.`sales` ASC NULLS FIRST
|LIMIT 100
@@ -1026,9 +1037,187 @@ object TestTPCDS_1_4_Batch {
| LIMIT 100
""".stripMargin.trim,
"""
- |
- |
- |
+ |SELECT gen_subquery_1.`customer_id`, gen_subquery_1.`customer_first_name`, gen_subquery_1.`customer_last_name`, gen_subquery_1.`customer_preferred_cust_flag`, gen_subquery_1.`customer_birth_country`, gen_subquery_1.`customer_login`, gen_subquery_1.`customer_email_address`
+ |FROM
+ | (SELECT gen_subsumer_0.`customer_id` AS `customer_id`, sum(gen_subsumer_0.`year_total`) AS `year_total`
+ | FROM
+ | (SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, date_dim.`d_year` AS `dyear`, date_dim.`d_date` AS `ddate`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(store_sales.`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
+ | FROM
+ | customer
+ | INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`)
+ | INNER JOIN date_dim ON (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`, date_dim.`d_date`) gen_subsumer_0
+ | WHERE
+ | (gen_subsumer_0.`dyear` = 2001)
+ | GROUP BY gen_subsumer_0.`customer_id`, gen_subsumer_0.`customer_first_name`, gen_subsumer_0.`customer_last_name`, gen_subsumer_0.`customer_preferred_cust_flag`, gen_subsumer_0.`customer_birth_country`, gen_subsumer_0.`customer_login`, gen_subsumer_0.`customer_email_address`, gen_subsumer_0.`dyear`
+ | HAVING (sum(gen_subsumer_0.`year_total`) > 0E-13BD)
+ | UNION ALL
+ | SELECT customer.`c_customer_id` AS `customer_id`, sum((CAST((((CAST(catalog_sales.`cs_ext_list_price` AS DECIMAL(8,2)) - CAST(catalog_sales.`cs_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(catalog_sales.`cs_ext_discount_amt` AS DECIMAL(8,2))) + CAST(catalog_sales.`cs_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`
+ | FROM
+ | customer
+ | INNER JOIN catalog_sales ON (customer.`c_customer_sk` = catalog_sales.`cs_bill_customer_sk`)
+ | INNER JOIN date_dim ON (catalog_sales.`cs_sold_date_sk` = date_dim.`d_date_sk`)
+ | WHERE
+ | false
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`
+ | HAVING (`year_total` > 0E-13BD)
+ | UNION ALL
+ | SELECT customer.`c_customer_id` AS `customer_id`, sum((CAST((((CAST(web_sales.`ws_ext_list_price` AS DECIMAL(8,2)) - CAST(web_sales.`ws_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(web_sales.`ws_ext_discount_amt` AS DECIMAL(8,2))) + CAST(web_sales.`ws_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`
+ | FROM
+ | customer
+ | INNER JOIN web_sales ON (customer.`c_customer_sk` = web_sales.`ws_bill_customer_sk`)
+ | INNER JOIN date_dim ON (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`)
+ | WHERE
+ | false
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`
+ | HAVING (`year_total` > 0E-13BD)) gen_subquery_0
+ | INNER JOIN (SELECT gen_subsumer_1.`customer_id` AS `customer_id`, gen_subsumer_1.`customer_first_name` AS `customer_first_name`, gen_subsumer_1.`customer_last_name` AS `customer_last_name`, gen_subsumer_1.`customer_preferred_cust_flag` AS `customer_preferred_cust_flag`, gen_subsumer_1.`customer_birth_country` AS `customer_birth_country`, gen_subsumer_1.`customer_login` AS `customer_login`, gen_subsumer_1.`customer_email_address` AS `customer_email_address`, sum(gen_subsumer_1.`year_total`) AS `year_total`
+ | FROM
+ | (SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, date_dim.`d_year` AS `dyear`, date_dim.`d_date` AS `ddate`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(store_sales.`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
+ | FROM
+ | customer
+ | INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`)
+ | INNER JOIN date_dim ON (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`, date_dim.`d_date`) gen_subsumer_1
+ | WHERE
+ | (gen_subsumer_1.`dyear` = 2002)
+ | GROUP BY gen_subsumer_1.`customer_id`, gen_subsumer_1.`customer_first_name`, gen_subsumer_1.`customer_last_name`, gen_subsumer_1.`customer_preferred_cust_flag`, gen_subsumer_1.`customer_birth_country`, gen_subsumer_1.`customer_login`, gen_subsumer_1.`customer_email_address`, gen_subsumer_1.`dyear`
+ | UNION ALL
+ | SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, sum((CAST((((CAST(catalog_sales.`cs_ext_list_price` AS DECIMAL(8,2)) - CAST(catalog_sales.`cs_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(catalog_sales.`cs_ext_discount_amt` AS DECIMAL(8,2))) + CAST(catalog_sales.`cs_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`
+ | FROM
+ | customer
+ | INNER JOIN catalog_sales ON (customer.`c_customer_sk` = catalog_sales.`cs_bill_customer_sk`)
+ | INNER JOIN date_dim ON (catalog_sales.`cs_sold_date_sk` = date_dim.`d_date_sk`)
+ | WHERE
+ | false
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`
+ | UNION ALL
+ | SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, sum((CAST((((CAST(web_sales.`ws_ext_list_price` AS DECIMAL(8,2)) - CAST(web_sales.`ws_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(web_sales.`ws_ext_discount_amt` AS DECIMAL(8,2))) + CAST(web_sales.`ws_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`
+ | FROM
+ | customer
+ | INNER JOIN web_sales ON (customer.`c_customer_sk` = web_sales.`ws_bill_customer_sk`)
+ | INNER JOIN date_dim ON (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`)
+ | WHERE
+ | false
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`) gen_subquery_1 ON (gen_subquery_1.`customer_id` = gen_subquery_0.`customer_id`)
+ | INNER JOIN (SELECT gen_subsumer_2.`customer_id` AS `customer_id`, sum(gen_subsumer_2.`year_total`) AS `year_total`
+ | FROM
+ | (SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, date_dim.`d_year` AS `dyear`, date_dim.`d_date` AS `ddate`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(store_sales.`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
+ | FROM
+ | customer
+ | INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`)
+ | INNER JOIN date_dim ON (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`, date_dim.`d_date`) gen_subsumer_2
+ | WHERE
+ | false
+ | GROUP BY gen_subsumer_2.`customer_id`, gen_subsumer_2.`customer_first_name`, gen_subsumer_2.`customer_last_name`, gen_subsumer_2.`customer_preferred_cust_flag`, gen_subsumer_2.`customer_birth_country`, gen_subsumer_2.`customer_login`, gen_subsumer_2.`customer_email_address`, gen_subsumer_2.`dyear`
+ | HAVING (sum(gen_subsumer_2.`year_total`) > 0E-13BD)
+ | UNION ALL
+ | SELECT customer.`c_customer_id` AS `customer_id`, sum((CAST((((CAST(catalog_sales.`cs_ext_list_price` AS DECIMAL(8,2)) - CAST(catalog_sales.`cs_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(catalog_sales.`cs_ext_discount_amt` AS DECIMAL(8,2))) + CAST(catalog_sales.`cs_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`
+ | FROM
+ | customer
+ | INNER JOIN catalog_sales ON (customer.`c_customer_sk` = catalog_sales.`cs_bill_customer_sk`)
+ | INNER JOIN date_dim ON (date_dim.`d_year` = 2001) AND (catalog_sales.`cs_sold_date_sk` = date_dim.`d_date_sk`)
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`
+ | HAVING (`year_total` > 0E-13BD)
+ | UNION ALL
+ | SELECT customer.`c_customer_id` AS `customer_id`, sum((CAST((((CAST(web_sales.`ws_ext_list_price` AS DECIMAL(8,2)) - CAST(web_sales.`ws_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(web_sales.`ws_ext_discount_amt` AS DECIMAL(8,2))) + CAST(web_sales.`ws_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`
+ | FROM
+ | customer
+ | INNER JOIN web_sales ON (customer.`c_customer_sk` = web_sales.`ws_bill_customer_sk`)
+ | INNER JOIN date_dim ON (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`)
+ | WHERE
+ | false
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`
+ | HAVING (`year_total` > 0E-13BD)) gen_subquery_2 ON (gen_subquery_0.`customer_id` = gen_subquery_2.`customer_id`)
+ | INNER JOIN (SELECT gen_subsumer_3.`customer_id` AS `customer_id`, sum(gen_subsumer_3.`year_total`) AS `year_total`
+ | FROM
+ | (SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, date_dim.`d_year` AS `dyear`, date_dim.`d_date` AS `ddate`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(store_sales.`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
+ | FROM
+ | customer
+ | INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`)
+ | INNER JOIN date_dim ON (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`, date_dim.`d_date`) gen_subsumer_3
+ | WHERE
+ | false
+ | GROUP BY gen_subsumer_3.`customer_id`, gen_subsumer_3.`customer_first_name`, gen_subsumer_3.`customer_last_name`, gen_subsumer_3.`customer_preferred_cust_flag`, gen_subsumer_3.`customer_birth_country`, gen_subsumer_3.`customer_login`, gen_subsumer_3.`customer_email_address`, gen_subsumer_3.`dyear`
+ | UNION ALL
+ | SELECT customer.`c_customer_id` AS `customer_id`, sum((CAST((((CAST(catalog_sales.`cs_ext_list_price` AS DECIMAL(8,2)) - CAST(catalog_sales.`cs_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(catalog_sales.`cs_ext_discount_amt` AS DECIMAL(8,2))) + CAST(catalog_sales.`cs_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`
+ | FROM
+ | customer
+ | INNER JOIN catalog_sales ON (customer.`c_customer_sk` = catalog_sales.`cs_bill_customer_sk`)
+ | INNER JOIN date_dim ON (date_dim.`d_year` = 2002) AND (catalog_sales.`cs_sold_date_sk` = date_dim.`d_date_sk`)
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`
+ | UNION ALL
+ | SELECT customer.`c_customer_id` AS `customer_id`, sum((CAST((((CAST(web_sales.`ws_ext_list_price` AS DECIMAL(8,2)) - CAST(web_sales.`ws_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(web_sales.`ws_ext_discount_amt` AS DECIMAL(8,2))) + CAST(web_sales.`ws_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`
+ | FROM
+ | customer
+ | INNER JOIN web_sales ON (customer.`c_customer_sk` = web_sales.`ws_bill_customer_sk`)
+ | INNER JOIN date_dim ON (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`)
+ | WHERE
+ | false
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`) gen_subquery_3
+ | INNER JOIN (SELECT gen_subsumer_4.`customer_id` AS `customer_id`, sum(gen_subsumer_4.`year_total`) AS `year_total`
+ | FROM
+ | (SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, date_dim.`d_year` AS `dyear`, date_dim.`d_date` AS `ddate`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(store_sales.`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
+ | FROM
+ | customer
+ | INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`)
+ | INNER JOIN date_dim ON (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`, date_dim.`d_date`) gen_subsumer_4
+ | WHERE
+ | false
+ | GROUP BY gen_subsumer_4.`customer_id`, gen_subsumer_4.`customer_first_name`, gen_subsumer_4.`customer_last_name`, gen_subsumer_4.`customer_preferred_cust_flag`, gen_subsumer_4.`customer_birth_country`, gen_subsumer_4.`customer_login`, gen_subsumer_4.`customer_email_address`, gen_subsumer_4.`dyear`
+ | HAVING (sum(gen_subsumer_4.`year_total`) > 0E-13BD)
+ | UNION ALL
+ | SELECT customer.`c_customer_id` AS `customer_id`, sum((CAST((((CAST(catalog_sales.`cs_ext_list_price` AS DECIMAL(8,2)) - CAST(catalog_sales.`cs_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(catalog_sales.`cs_ext_discount_amt` AS DECIMAL(8,2))) + CAST(catalog_sales.`cs_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`
+ | FROM
+ | customer
+ | INNER JOIN catalog_sales ON (customer.`c_customer_sk` = catalog_sales.`cs_bill_customer_sk`)
+ | INNER JOIN date_dim ON (catalog_sales.`cs_sold_date_sk` = date_dim.`d_date_sk`)
+ | WHERE
+ | false
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`
+ | HAVING (`year_total` > 0E-13BD)
+ | UNION ALL
+ | SELECT customer.`c_customer_id` AS `customer_id`, sum((CAST((((CAST(web_sales.`ws_ext_list_price` AS DECIMAL(8,2)) - CAST(web_sales.`ws_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(web_sales.`ws_ext_discount_amt` AS DECIMAL(8,2))) + CAST(web_sales.`ws_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`
+ | FROM
+ | customer
+ | INNER JOIN web_sales ON (customer.`c_customer_sk` = web_sales.`ws_bill_customer_sk`)
+ | INNER JOIN date_dim ON (date_dim.`d_year` = 2001) AND (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`)
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`
+ | HAVING (`year_total` > 0E-13BD)) gen_subquery_4 ON (gen_subquery_0.`customer_id` = gen_subquery_4.`customer_id`)
+ | INNER JOIN (SELECT gen_subsumer_5.`customer_id` AS `customer_id`, sum(gen_subsumer_5.`year_total`) AS `year_total`
+ | FROM
+ | (SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, date_dim.`d_year` AS `dyear`, date_dim.`d_date` AS `ddate`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(store_sales.`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
+ | FROM
+ | customer
+ | INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`)
+ | INNER JOIN date_dim ON (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`, date_dim.`d_date`) gen_subsumer_5
+ | WHERE
+ | false
+ | GROUP BY gen_subsumer_5.`customer_id`, gen_subsumer_5.`customer_first_name`, gen_subsumer_5.`customer_last_name`, gen_subsumer_5.`customer_preferred_cust_flag`, gen_subsumer_5.`customer_birth_country`, gen_subsumer_5.`customer_login`, gen_subsumer_5.`customer_email_address`, gen_subsumer_5.`dyear`
+ | UNION ALL
+ | SELECT customer.`c_customer_id` AS `customer_id`, sum((CAST((((CAST(catalog_sales.`cs_ext_list_price` AS DECIMAL(8,2)) - CAST(catalog_sales.`cs_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(catalog_sales.`cs_ext_discount_amt` AS DECIMAL(8,2))) + CAST(catalog_sales.`cs_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`
+ | FROM
+ | customer
+ | INNER JOIN catalog_sales ON (customer.`c_customer_sk` = catalog_sales.`cs_bill_customer_sk`)
+ | INNER JOIN date_dim ON (catalog_sales.`cs_sold_date_sk` = date_dim.`d_date_sk`)
+ | WHERE
+ | false
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`
+ | UNION ALL
+ | SELECT customer.`c_customer_id` AS `customer_id`, sum((CAST((((CAST(web_sales.`ws_ext_list_price` AS DECIMAL(8,2)) - CAST(web_sales.`ws_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(web_sales.`ws_ext_discount_amt` AS DECIMAL(8,2))) + CAST(web_sales.`ws_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`
+ | FROM
+ | customer
+ | INNER JOIN web_sales ON (customer.`c_customer_sk` = web_sales.`ws_bill_customer_sk`)
+ | INNER JOIN date_dim ON (date_dim.`d_year` = 2002) AND (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`)
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`) gen_subquery_5
+ |WHERE
+ | (gen_subquery_0.`customer_id` = gen_subquery_3.`customer_id`) AND (CASE WHEN (gen_subquery_2.`year_total` > 0E-13BD) THEN (gen_subquery_3.`year_total` / gen_subquery_2.`year_total`) ELSE CAST(NULL AS DECIMAL(38,23)) END > CASE WHEN (gen_subquery_0.`year_total` > 0E-13BD) THEN (gen_subquery_1.`year_total` / gen_subquery_0.`year_total`) ELSE CAST(NULL AS DECIMAL(38,23)) END) AND (gen_subquery_0.`customer_id` = gen_subquery_5.`customer_id`) AND (CASE WHEN (gen_subquery_2.`year_total` > 0E-13BD) THEN (gen_subquery_3.`year_total` / gen_subquery_2.`year_total`) ELSE CAST(NULL AS DECIMAL(38,23)) END > CASE WHEN (gen_subquery_4.`year_total` > 0E-13BD) THEN (gen_subquery_5.`year_total` / gen_subquery_4.`year_total`) ELSE CAST(NULL AS DECIMAL(38,23)) END)
+ |ORDER BY gen_subquery_1.`customer_id` ASC NULLS FIRST, gen_subquery_1.`customer_first_name` ASC NULLS FIRST, gen_subquery_1.`customer_last_name` ASC NULLS FIRST, gen_subquery_1.`customer_preferred_cust_flag` ASC NULLS FIRST, gen_subquery_1.`customer_birth_country` ASC NULLS FIRST, gen_subquery_1.`customer_login` ASC NULLS FIRST, gen_subquery_1.`customer_email_address` ASC NULLS FIRST
+ |LIMIT 100
""".stripMargin.trim),
//q11
("case_18",
@@ -1128,12 +1317,12 @@ object TestTPCDS_1_4_Batch {
|FROM
| (SELECT gen_subsumer_0.`customer_id` AS `customer_id`, sum(gen_subsumer_0.`year_total1`) AS `year_total`
| FROM
- | (SELECT `c_customer_id` AS `customer_id`, `c_first_name` AS `customer_first_name`, `c_last_name` AS `customer_last_name`, `c_preferred_cust_flag` AS `customer_preferred_cust_flag`, `c_birth_country` AS `customer_birth_country`, `c_login` AS `customer_login`, `c_email_address` AS `customer_email_address`, `d_year` AS `dyear`, `d_date` AS `ddate`, sum((CAST((((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
+ | (SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, date_dim.`d_year` AS `dyear`, date_dim.`d_date` AS `ddate`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(store_sales.`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
| FROM
| customer
- | INNER JOIN store_sales ON (`c_customer_sk` = `ss_customer_sk`)
- | INNER JOIN date_dim ON (`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`, `d_date`) gen_subsumer_0
+ | INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`)
+ | INNER JOIN date_dim ON (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`, date_dim.`d_date`) gen_subsumer_0
| WHERE
| (gen_subsumer_0.`dyear` = 2001)
| GROUP BY gen_subsumer_0.`customer_id`, gen_subsumer_0.`customer_first_name`, gen_subsumer_0.`customer_last_name`, gen_subsumer_0.`dyear`, gen_subsumer_0.`customer_preferred_cust_flag`, gen_subsumer_0.`customer_birth_country`, gen_subsumer_0.`customer_login`, gen_subsumer_0.`customer_email_address`
@@ -1150,12 +1339,12 @@ object TestTPCDS_1_4_Batch {
| HAVING (`year_total` > 0.00BD)) gen_subquery_0
| INNER JOIN (SELECT gen_subsumer_1.`customer_id` AS `customer_id`, gen_subsumer_1.`customer_preferred_cust_flag` AS `customer_preferred_cust_flag`, sum(gen_subsumer_1.`year_total1`) AS `year_total`
| FROM
- | (SELECT `c_customer_id` AS `customer_id`, `c_first_name` AS `customer_first_name`, `c_last_name` AS `customer_last_name`, `c_preferred_cust_flag` AS `customer_preferred_cust_flag`, `c_birth_country` AS `customer_birth_country`, `c_login` AS `customer_login`, `c_email_address` AS `customer_email_address`, `d_year` AS `dyear`, `d_date` AS `ddate`, sum((CAST((((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
+ | (SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, date_dim.`d_year` AS `dyear`, date_dim.`d_date` AS `ddate`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(store_sales.`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
| FROM
| customer
- | INNER JOIN store_sales ON (`c_customer_sk` = `ss_customer_sk`)
- | INNER JOIN date_dim ON (`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`, `d_date`) gen_subsumer_1
+ | INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`)
+ | INNER JOIN date_dim ON (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`, date_dim.`d_date`) gen_subsumer_1
| WHERE
| (gen_subsumer_1.`dyear` = 2002)
| GROUP BY gen_subsumer_1.`customer_id`, gen_subsumer_1.`customer_first_name`, gen_subsumer_1.`customer_last_name`, gen_subsumer_1.`dyear`, gen_subsumer_1.`customer_preferred_cust_flag`, gen_subsumer_1.`customer_birth_country`, gen_subsumer_1.`customer_login`, gen_subsumer_1.`customer_email_address`
@@ -1170,12 +1359,12 @@ object TestTPCDS_1_4_Batch {
| GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`) gen_subquery_1 ON (gen_subquery_1.`customer_id` = gen_subquery_0.`customer_id`)
| INNER JOIN (SELECT gen_subsumer_2.`customer_id` AS `customer_id`, sum(gen_subsumer_2.`year_total1`) AS `year_total`
| FROM
- | (SELECT `c_customer_id` AS `customer_id`, `c_first_name` AS `customer_first_name`, `c_last_name` AS `customer_last_name`, `c_preferred_cust_flag` AS `customer_preferred_cust_flag`, `c_birth_country` AS `customer_birth_country`, `c_login` AS `customer_login`, `c_email_address` AS `customer_email_address`, `d_year` AS `dyear`, `d_date` AS `ddate`, sum((CAST((((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
+ | (SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, date_dim.`d_year` AS `dyear`, date_dim.`d_date` AS `ddate`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(store_sales.`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
| FROM
| customer
- | INNER JOIN store_sales ON (`c_customer_sk` = `ss_customer_sk`)
- | INNER JOIN date_dim ON (`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`, `d_date`) gen_subsumer_2
+ | INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`)
+ | INNER JOIN date_dim ON (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`, date_dim.`d_date`) gen_subsumer_2
| WHERE
| false
| GROUP BY gen_subsumer_2.`customer_id`, gen_subsumer_2.`customer_first_name`, gen_subsumer_2.`customer_last_name`, gen_subsumer_2.`dyear`, gen_subsumer_2.`customer_preferred_cust_flag`, gen_subsumer_2.`customer_birth_country`, gen_subsumer_2.`customer_login`, gen_subsumer_2.`customer_email_address`
@@ -1190,12 +1379,12 @@ object TestTPCDS_1_4_Batch {
| HAVING (`year_total` > 0.00BD)) gen_subquery_2 ON (gen_subquery_0.`customer_id` = gen_subquery_2.`customer_id`)
| INNER JOIN (SELECT gen_subsumer_3.`customer_id` AS `customer_id`, sum(gen_subsumer_3.`year_total1`) AS `year_total`
| FROM
- | (SELECT `c_customer_id` AS `customer_id`, `c_first_name` AS `customer_first_name`, `c_last_name` AS `customer_last_name`, `c_preferred_cust_flag` AS `customer_preferred_cust_flag`, `c_birth_country` AS `customer_birth_country`, `c_login` AS `customer_login`, `c_email_address` AS `customer_email_address`, `d_year` AS `dyear`, `d_date` AS `ddate`, sum((CAST((((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
+ | (SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, date_dim.`d_year` AS `dyear`, date_dim.`d_date` AS `ddate`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(store_sales.`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
| FROM
| customer
- | INNER JOIN store_sales ON (`c_customer_sk` = `ss_customer_sk`)
- | INNER JOIN date_dim ON (`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`, `d_date`) gen_subsumer_3
+ | INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`)
+ | INNER JOIN date_dim ON (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`, date_dim.`d_date`) gen_subsumer_3
| WHERE
| false
| GROUP BY gen_subsumer_3.`customer_id`, gen_subsumer_3.`customer_first_name`, gen_subsumer_3.`customer_last_name`, gen_subsumer_3.`dyear`, gen_subsumer_3.`customer_preferred_cust_flag`, gen_subsumer_3.`customer_birth_country`, gen_subsumer_3.`customer_login`, gen_subsumer_3.`customer_email_address`
@@ -1262,10 +1451,7 @@ object TestTPCDS_1_4_Batch {
| limit 100
""".stripMargin.trim,
"""
- |
- |
- |
- """.stripMargin.trim),
+ """.stripMargin.trim),
//q74
("case_20",
"""
@@ -1346,9 +1532,9 @@ object TestTPCDS_1_4_Batch {
"""
|SELECT gen_subquery_1.`customer_id`, gen_subquery_1.`customer_first_name`, gen_subquery_1.`customer_last_name`
|FROM
- | (SELECT gen_subsumer_0.`customer_id` AS `customer_id`, gen_subsumer_0.`year_total_74` AS `year_total`
+ | (SELECT gen_subsumer_0.`customer_id` AS `customer_id`, sum(gen_subsumer_0.`year_total_74`) AS `year_total`
| FROM
- | (SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, date_dim.`d_year` AS `dyear`, date_dim.`d_date` AS `ddate`, date_dim.`d_month_seq`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, makedecimal(sum(unscaledvalue(store_sales.`ss_net_paid`))) AS `year_total_74`, 's' AS `sale_type`
+ | (SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, date_dim.`d_year` AS `dyear`, date_dim.`d_date` AS `ddate`, date_dim.`d_month_seq`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum(store_sales.`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
| FROM
| customer
| INNER JOIN store_sales ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`)
@@ -1357,9 +1543,9 @@ object TestTPCDS_1_4_Batch {
| WHERE
| (gen_subsumer_0.`dyear` IN (2001, 2002)) AND (gen_subsumer_0.`dyear` = 2001)
| GROUP BY gen_subsumer_0.`customer_id`, gen_subsumer_0.`customer_first_name`, gen_subsumer_0.`customer_last_name`, gen_subsumer_0.`dyear`
- | HAVING (gen_subsumer_0.`year_total_74` > 0.00BD)
+ | HAVING (sum(gen_subsumer_0.`year_total_74`) > 0.00BD)
| UNION ALL
- | SELECT customer.`c_customer_id` AS `customer_id`, makedecimal(sum(unscaledvalue(web_sales.`ws_net_paid`))) AS `year_total`
+ | SELECT customer.`c_customer_id` AS `customer_id`, sum(web_sales.`ws_net_paid`) AS `year_total`
| FROM
| customer
| INNER JOIN web_sales ON (customer.`c_customer_sk` = web_sales.`ws_bill_customer_sk`)
@@ -1368,19 +1554,19 @@ object TestTPCDS_1_4_Batch {
| false
| GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, date_dim.`d_year`
| HAVING (`year_total` > 0.00BD)) gen_subquery_0
- | INNER JOIN (SELECT gen_subsumer_1.`customer_id` AS `customer_id`, gen_subsumer_1.`customer_first_name` AS `customer_first_name`, gen_subsumer_1.`customer_last_name` AS `customer_last_name`, gen_subsumer_1.`year_total_74` AS `year_total`
+ | INNER JOIN (SELECT gen_subsumer_1.`customer_id` AS `customer_id`, gen_subsumer_1.`customer_first_name` AS `customer_first_name`, gen_subsumer_1.`customer_last_name` AS `customer_last_name`, sum(gen_subsumer_1.`year_total_74`) AS `year_total`
| FROM
- | (SELECT customer.`customer_id` AS `customer_id`, customer.`customer_first_name` AS `customer_first_name`, customer.`customer_last_name` AS `customer_last_name`, customer.`customer_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`customer_birth_country` AS `customer_birth_country`, customer.`customer_login` AS `customer_login`, customer.`customer_email_address` AS `customer_email_address`, date_dim.`dyear` AS `dyear`, date_dim.`ddate` AS `ddate`, date_dim.`d_month_seq`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, makedecimal(sum(unscaledvalue(store_sales.`ss_net_paid`))) AS `year_total_74`, 's' AS `sale_type`
+ | (SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, date_dim.`d_year` AS `dyear`, date_dim.`d_date` AS `ddate`, date_dim.`d_month_seq`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum(store_sales.`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type`
| FROM
| customer
| INNER JOIN store_sales ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`)
| INNER JOIN date_dim ON (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`)
- | GROUP BY customer.`customer_id`, customer.`customer_first_name`, customer.`customer_last_name`, customer.`customer_preferred_cust_flag`, customer.`customer_birth_country`, customer.`customer_login`, customer.`customer_email_address`, date_dim.`dyear`, date_dim.`ddate`, date_dim.`d_month_seq`) gen_subsumer_1
+ | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`, date_dim.`d_date`, date_dim.`d_month_seq`) gen_subsumer_1
| WHERE
| (gen_subsumer_1.`dyear` IN (2001, 2002)) AND (gen_subsumer_1.`dyear` = 2002)
| GROUP BY gen_subsumer_1.`customer_id`, gen_subsumer_1.`customer_first_name`, gen_subsumer_1.`customer_last_name`, gen_subsumer_1.`dyear`
| UNION ALL
- | SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, makedecimal(sum(unscaledvalue(web_sales.`ws_net_paid`))) AS `year_total`
+ | SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, sum(web_sales.`ws_net_paid`) AS `year_total`
| FR
<TRUNCATED>