carbondata-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jack...@apache.org
Subject [3/4] carbondata git commit: [CARBONDATA-2573] integrate carbonstore mv branch
Date Mon, 11 Jun 2018 13:25:53 GMT
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>

Mime
View raw message