You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by ta...@apache.org on 2018/01/18 04:27:49 UTC
[09/12] impala git commit: IMPALA-5478: Run TPCDS queries with
decimal_v2 enabled
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q4.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q4.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q4.test
new file mode 100644
index 0000000..5cf56c3
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q4.test
@@ -0,0 +1,126 @@
+====
+---- QUERY: TPCDS-Q4
+with year_total as (
+ select c_customer_id customer_id,
+ c_first_name customer_first_name,
+ c_last_name customer_last_name,
+ c_preferred_cust_flag customer_preferred_cust_flag,
+ c_birth_country customer_birth_country,
+ c_login customer_login,
+ c_email_address customer_email_address,
+ d_year dyear,
+ sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total,
+ 's' sale_type
+ from customer,
+ store_sales,
+ date_dim
+ where c_customer_sk = ss_customer_sk
+ and ss_sold_date_sk = d_date_sk
+ group by c_customer_id,
+ c_first_name,
+ c_last_name,
+ c_preferred_cust_flag,
+ c_birth_country,
+ c_login,
+ c_email_address,
+ d_year
+ union all
+ select c_customer_id customer_id,
+ c_first_name customer_first_name,
+ c_last_name customer_last_name,
+ c_preferred_cust_flag customer_preferred_cust_flag,
+ c_birth_country customer_birth_country,
+ c_login customer_login,
+ c_email_address customer_email_address,
+ d_year dyear,
+ sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2) ) year_total,
+ 'c' sale_type
+ from customer,
+ catalog_sales,
+ date_dim
+ where c_customer_sk = cs_bill_customer_sk
+ and cs_sold_date_sk = d_date_sk
+ group by c_customer_id,
+ c_first_name,
+ c_last_name,
+ c_preferred_cust_flag,
+ c_birth_country,
+ c_login,
+ c_email_address,
+ d_year
+union all
+ select c_customer_id customer_id,
+ c_first_name customer_first_name,
+ c_last_name customer_last_name,
+ c_preferred_cust_flag customer_preferred_cust_flag,
+ c_birth_country customer_birth_country,
+ c_login customer_login,
+ c_email_address customer_email_address,
+ d_year dyear,
+ sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2) ) year_total,
+ 'w' sale_type
+ from customer,
+ web_sales,
+ date_dim
+ where c_customer_sk = ws_bill_customer_sk
+ and ws_sold_date_sk = d_date_sk
+ group by c_customer_id,
+ c_first_name,
+ c_last_name,
+ c_preferred_cust_flag,
+ c_birth_country,
+ c_login,
+ c_email_address,
+ d_year
+ )
+ select t_s_secyear.customer_id,
+ t_s_secyear.customer_first_name,
+ t_s_secyear.customer_last_name,
+ t_s_secyear.customer_preferred_cust_flag
+ from year_total t_s_firstyear,
+ year_total t_s_secyear,
+ year_total t_c_firstyear,
+ year_total t_c_secyear,
+ year_total t_w_firstyear,
+ year_total t_w_secyear
+ where t_s_secyear.customer_id = t_s_firstyear.customer_id
+ and t_s_firstyear.customer_id = t_c_secyear.customer_id
+ and t_s_firstyear.customer_id = t_c_firstyear.customer_id
+ and t_s_firstyear.customer_id = t_w_firstyear.customer_id
+ and t_s_firstyear.customer_id = t_w_secyear.customer_id
+ and t_s_firstyear.sale_type = 's'
+ and t_c_firstyear.sale_type = 'c'
+ and t_w_firstyear.sale_type = 'w'
+ and t_s_secyear.sale_type = 's'
+ and t_c_secyear.sale_type = 'c'
+ and t_w_secyear.sale_type = 'w'
+ and t_s_firstyear.dyear = 2001
+ and t_s_secyear.dyear = 2001+1
+ and t_c_firstyear.dyear = 2001
+ and t_c_secyear.dyear = 2001+1
+ and t_w_firstyear.dyear = 2001
+ and t_w_secyear.dyear = 2001+1
+ and t_s_firstyear.year_total > 0
+ and t_c_firstyear.year_total > 0
+ and t_w_firstyear.year_total > 0
+ and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end
+ > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end
+ and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end
+ > case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end
+ order by t_s_secyear.customer_id,
+ t_s_secyear.customer_first_name,
+ t_s_secyear.customer_last_name,
+ t_s_secyear.customer_email_address
+limit 100;
+---- RESULTS
+'AAAAAAAABIJBAAAA','James','White','N'
+'AAAAAAAADIIOAAAA','David','Carroll','Y'
+'AAAAAAAAIJCIBAAA','Thomas','Oneal','Y'
+'AAAAAAAAKJBLAAAA','Kerry','Davis','Y'
+'AAAAAAAAMFPFBAAA','Jamie','Woods','Y'
+'AAAAAAAANJAMAAAA','Thaddeus','Griffin','N'
+'AAAAAAAANJOLAAAA','Debra','Underwood','Y'
+'AAAAAAAAPEHEBAAA','Edith','Molina','Y'
+---- TYPES
+STRING,STRING,STRING,STRING
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q40.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q40.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q40.test
new file mode 100644
index 0000000..ce19c5b
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q40.test
@@ -0,0 +1,131 @@
+====
+---- QUERY: TPCDS-Q40
+select
+ w_state
+ ,i_item_id
+ ,sum(case when (cast(d_date as timestamp) < cast ('2000-03-11' as timestamp))
+ then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_before
+ ,sum(case when (cast(d_date as timestamp) >= cast ('2000-03-11' as timestamp))
+ then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_after
+ from
+ catalog_sales left outer join catalog_returns on
+ (cs_order_number = cr_order_number
+ and cs_item_sk = cr_item_sk)
+ ,warehouse
+ ,item
+ ,date_dim
+ where
+ i_current_price between 0.99 and 1.49
+ and i_item_sk = cs_item_sk
+ and cs_warehouse_sk = w_warehouse_sk
+ and cs_sold_date_sk = d_date_sk
+ and cast(d_date as timestamp) between (cast ('2000-03-11' as timestamp) - interval 30 days)
+ and (cast ('2000-03-11' as timestamp) + interval 30 days)
+ group by
+ w_state,i_item_id
+ order by w_state,i_item_id
+limit 100;
+---- RESULTS
+'TN','AAAAAAAAAABDAAAA',0.00,-82.10
+'TN','AAAAAAAAAACDAAAA',-216.54,158.04
+'TN','AAAAAAAAAAHDAAAA',186.55,0.00
+'TN','AAAAAAAAAALAAAAA',0.00,48.23
+'TN','AAAAAAAAABBDAAAA',98.61,332.71
+'TN','AAAAAAAAABDAAAAA',0.00,213.64
+'TN','AAAAAAAAACGCAAAA',63.68,0.00
+'TN','AAAAAAAAACHCAAAA',102.68,51.89
+'TN','AAAAAAAAACKCAAAA',128.93,44.82
+'TN','AAAAAAAAACLDAAAA',205.44,-948.62
+'TN','AAAAAAAAACOBAAAA',207.32,24.89
+'TN','AAAAAAAAACPDAAAA',87.75,53.99
+'TN','AAAAAAAAADGBAAAA',44.31,222.48
+'TN','AAAAAAAAADKBAAAA',0.00,-471.87
+'TN','AAAAAAAAAEADAAAA',58.24,0.00
+'TN','AAAAAAAAAEOCAAAA',19.91,214.70
+'TN','AAAAAAAAAFACAAAA',271.82,163.17
+'TN','AAAAAAAAAFADAAAA',2.35,28.32
+'TN','AAAAAAAAAFDCAAAA',-378.05,-303.27
+'TN','AAAAAAAAAGIDAAAA',307.61,-19.29
+'TN','AAAAAAAAAHDEAAAA',80.58,-476.72
+'TN','AAAAAAAAAHHAAAAA',8.27,155.10
+'TN','AAAAAAAAAHJBAAAA',39.24,0.00
+'TN','AAAAAAAAAIECAAAA',82.40,3.91
+'TN','AAAAAAAAAIEEAAAA',20.40,-151.09
+'TN','AAAAAAAAAIMCAAAA',24.47,-150.30
+'TN','AAAAAAAAAJACAAAA',49.09,82.10
+'TN','AAAAAAAAAJCAAAAA',121.18,63.78
+'TN','AAAAAAAAAJKBAAAA',27.94,8.97
+'TN','AAAAAAAAALBEAAAA',88.26,30.23
+'TN','AAAAAAAAALCEAAAA',93.52,92.02
+'TN','AAAAAAAAALECAAAA',64.20,15.16
+'TN','AAAAAAAAALNBAAAA',4.20,148.27
+'TN','AAAAAAAAAMBEAAAA',28.44,0.00
+'TN','AAAAAAAAAMPBAAAA',0.00,131.93
+'TN','AAAAAAAAANFEAAAA',0.00,-137.34
+'TN','AAAAAAAAAOBBAAAA',0.00,55.62
+'TN','AAAAAAAAAOIBAAAA',150.41,254.28
+'TN','AAAAAAAAAPBAAAAA',70.40,0.00
+'TN','AAAAAAAAAPJBAAAA',45.27,334.40
+'TN','AAAAAAAAAPLAAAAA',50.20,29.15
+'TN','AAAAAAAAAPLDAAAA',0.00,32.39
+'TN','AAAAAAAABAPDAAAA',93.42,145.87
+'TN','AAAAAAAABBIDAAAA',296.77,30.96
+'TN','AAAAAAAABDCEAAAA',-1771.08,-54.78
+'TN','AAAAAAAABDDDAAAA',111.12,280.59
+'TN','AAAAAAAABDJAAAAA',0.00,79.55
+'TN','AAAAAAAABEFDAAAA',0.00,3.43
+'TN','AAAAAAAABEODAAAA',269.90,297.58
+'TN','AAAAAAAABFMBAAAA',110.83,-941.40
+'TN','AAAAAAAABFNAAAAA',47.86,0.00
+'TN','AAAAAAAABFOCAAAA',46.34,83.52
+'TN','AAAAAAAABHPCAAAA',27.37,77.62
+'TN','AAAAAAAABIDBAAAA',196.62,5.57
+'TN','AAAAAAAABIGBAAAA',425.34,0.00
+'TN','AAAAAAAABIJBAAAA',209.63,0.00
+'TN','AAAAAAAABJFEAAAA',7.33,55.16
+'TN','AAAAAAAABKFAAAAA',0.00,138.14
+'TN','AAAAAAAABKMCAAAA',27.17,54.97
+'TN','AAAAAAAABLDEAAAA',170.29,0.00
+'TN','AAAAAAAABNHBAAAA',58.06,-337.89
+'TN','AAAAAAAABNIDAAAA',54.40,35.02
+'TN','AAAAAAAABNLAAAAA',0.00,168.38
+'TN','AAAAAAAABNLDAAAA',0.00,96.41
+'TN','AAAAAAAABNMCAAAA',202.41,49.53
+'TN','AAAAAAAABOCCAAAA',4.73,69.84
+'TN','AAAAAAAABOMBAAAA',63.67,163.49
+'TN','AAAAAAAACAAAAAAA',121.91,0.00
+'TN','AAAAAAAACAADAAAA',-1107.61,0.00
+'TN','AAAAAAAACAJCAAAA',115.81,173.05
+'TN','AAAAAAAACBCDAAAA',18.94,226.38
+'TN','AAAAAAAACBFAAAAA',0.00,97.41
+'TN','AAAAAAAACBIAAAAA',2.14,84.66
+'TN','AAAAAAAACBPBAAAA',95.44,26.68
+'TN','AAAAAAAACCABAAAA',160.43,135.86
+'TN','AAAAAAAACCHDAAAA',0.00,121.62
+'TN','AAAAAAAACCMDAAAA',-115.87,124.38
+'TN','AAAAAAAACDBCAAAA',16.62,3.40
+'TN','AAAAAAAACDECAAAA',-3114.60,0.00
+'TN','AAAAAAAACEEAAAAA',34.68,26.41
+'TN','AAAAAAAACELAAAAA',130.59,154.63
+'TN','AAAAAAAACELDAAAA',0.00,181.07
+'TN','AAAAAAAACFEAAAAA',3.78,-315.13
+'TN','AAAAAAAACFHDAAAA',0.00,1.80
+'TN','AAAAAAAACGFDAAAA',-386.87,96.92
+'TN','AAAAAAAACHHDAAAA',143.17,251.64
+'TN','AAAAAAAACHPCAAAA',0.17,198.29
+'TN','AAAAAAAACJCBAAAA',-918.65,270.96
+'TN','AAAAAAAACJDCAAAA',0.00,130.15
+'TN','AAAAAAAACJLAAAAA',63.96,91.27
+'TN','AAAAAAAACKFCAAAA',-540.59,35.64
+'TN','AAAAAAAACKHAAAAA',204.52,110.61
+'TN','AAAAAAAACKIAAAAA',18.43,-63.65
+'TN','AAAAAAAACLAEAAAA',116.07,0.00
+'TN','AAAAAAAACLGAAAAA',108.10,111.14
+'TN','AAAAAAAACLKAAAAA',143.05,19.59
+'TN','AAAAAAAACLLBAAAA',0.00,178.10
+'TN','AAAAAAAACLOBAAAA',-2200.72,14.13
+'TN','AAAAAAAACMADAAAA',71.42,-13.64
+'TN','AAAAAAAACMJAAAAA',0.00,358.31
+---- TYPES
+STRING, STRING, DECIMAL, DECIMAL
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q41.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q41.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q41.test
new file mode 100644
index 0000000..4286c76
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q41.test
@@ -0,0 +1,59 @@
+====
+---- QUERY: TPCDS-Q41
+select distinct(i_product_name)
+ from item i1
+ where i_manufact_id between 738 and 738+40
+ and (select count(*) as item_cnt
+ from item
+ where (i_manufact = i1.i_manufact and
+ ((i_category = 'Women' and
+ (i_color = 'powder' or i_color = 'khaki') and
+ (i_units = 'Ounce' or i_units = 'Oz') and
+ (i_size = 'medium' or i_size = 'extra large')
+ ) or
+ (i_category = 'Women' and
+ (i_color = 'brown' or i_color = 'honeydew') and
+ (i_units = 'Bunch' or i_units = 'Ton') and
+ (i_size = 'N/A' or i_size = 'small')
+ ) or
+ (i_category = 'Men' and
+ (i_color = 'floral' or i_color = 'deep') and
+ (i_units = 'N/A' or i_units = 'Dozen') and
+ (i_size = 'petite' or i_size = 'large')
+ ) or
+ (i_category = 'Men' and
+ (i_color = 'light' or i_color = 'cornflower') and
+ (i_units = 'Box' or i_units = 'Pound') and
+ (i_size = 'medium' or i_size = 'extra large')
+ ))) or
+ (i_manufact = i1.i_manufact and
+ ((i_category = 'Women' and
+ (i_color = 'midnight' or i_color = 'snow') and
+ (i_units = 'Pallet' or i_units = 'Pallet') and
+ (i_size = 'medium' or i_size = 'extra large')
+ ) or
+ (i_category = 'Women' and
+ (i_color = 'cyan' or i_color = 'papaya') and
+ (i_units = 'Cup' or i_units = 'Dram') and
+ (i_size = 'N/A' or i_size = 'small')
+ ) or
+ (i_category = 'Men' and
+ (i_color = 'orange' or i_color = 'frosted') and
+ (i_units = 'Each' or i_units = 'Tbl') and
+ (i_size = 'petite' or i_size = 'large')
+ ) or
+ (i_category = 'Men' and
+ (i_color = 'forest' or i_color = 'ghost') and
+ (i_units = 'Lb' or i_units = 'Bundle') and
+ (i_size = 'medium' or i_size = 'extra large')
+ )))) > 0
+ order by i_product_name
+ limit 100;
+---- RESULTS
+'ableationableought'
+'anticallyeingese'
+'callycallyeingese'
+'oughtationableought'
+---- TYPES
+STRING
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q42.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q42.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q42.test
new file mode 100644
index 0000000..f7e6b18
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q42.test
@@ -0,0 +1,41 @@
+====
+---- QUERY: TPCDS-Q42
+select
+ dt.d_year,
+ item.i_category_id,
+ item.i_category,
+ sum(ss_ext_sales_price)
+from
+ date_dim dt,
+ store_sales,
+ item
+where
+ dt.d_date_sk = store_sales.ss_sold_date_sk
+ and store_sales.ss_item_sk = item.i_item_sk
+ and item.i_manager_id = 1
+ and dt.d_moy = 11
+ and dt.d_year = 2000
+group by
+ dt.d_year,
+ item.i_category_id,
+ item.i_category
+order by
+ sum(ss_ext_sales_price) desc,
+ dt.d_year,
+ item.i_category_id,
+ item.i_category
+limit 100;
+---- RESULTS
+2000,7,'Home',458017.85
+2000,3,'Children',370261.29
+2000,2,'Men',368718.95
+2000,1,'Women',320132.43
+2000,10,'Electronics',281421.74
+2000,5,'Music',223420.70
+2000,4,'Shoes',221242.25
+2000,8,'Sports',200806.45
+2000,6,'Jewelry',167920.91
+2000,9,'Books',161721.11
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q43.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q43.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q43.test
new file mode 100644
index 0000000..efe0f22
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q43.test
@@ -0,0 +1,45 @@
+====
+---- QUERY: TPCDS-Q43
+select
+ s_store_name,
+ s_store_id,
+ sum(case when (d_day_name = 'Sunday') then ss_sales_price else null end) sun_sales,
+ sum(case when (d_day_name = 'Monday') then ss_sales_price else null end) mon_sales,
+ sum(case when (d_day_name = 'Tuesday') then ss_sales_price else null end) tue_sales,
+ sum(case when (d_day_name = 'Wednesday') then ss_sales_price else null end) wed_sales,
+ sum(case when (d_day_name = 'Thursday') then ss_sales_price else null end) thu_sales,
+ sum(case when (d_day_name = 'Friday') then ss_sales_price else null end) fri_sales,
+ sum(case when (d_day_name = 'Saturday') then ss_sales_price else null end) sat_sales
+from
+ date_dim,
+ store_sales,
+ store
+where
+ d_date_sk = ss_sold_date_sk
+ and s_store_sk = ss_store_sk
+ and s_gmt_offset = -5
+ and d_year = 2000
+group by
+ s_store_name,
+ s_store_id
+order by
+ s_store_name,
+ s_store_id,
+ sun_sales,
+ mon_sales,
+ tue_sales,
+ wed_sales,
+ thu_sales,
+ fri_sales,
+ sat_sales
+limit 100;
+---- RESULTS
+'able','AAAAAAAACAAAAAAA',517884.59,469230.50,505832.67,443696.30,479716.97,462447.50,503064.60
+'ation','AAAAAAAAHAAAAAAA',508811.68,474290.02,448808.84,492870.99,498127.64,474355.89,505906.68
+'bar','AAAAAAAAKAAAAAAA',496021.80,459933.01,479825.96,474630.24,482326.79,478330.87,505252.22
+'eing','AAAAAAAAIAAAAAAA',498752.97,476119.01,485965.24,454921.28,491953.89,476014.69,484633.67
+'ese','AAAAAAAAEAAAAAAA',493724.01,499637.85,452314.62,466232.23,481922.38,477933.29,500577.95
+'ought','AAAAAAAABAAAAAAA',505735.34,471490.23,463248.39,482690.52,485818.98,481816.20,491354.68
+---- TYPES
+STRING, STRING, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q46.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q46.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q46.test
new file mode 100644
index 0000000..a0bb3d1
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q46.test
@@ -0,0 +1,156 @@
+====
+---- QUERY: TPCDS-Q46
+select
+ c_last_name,
+ c_first_name,
+ ca_city,
+ bought_city,
+ ss_ticket_number,
+ amt,
+ profit
+from
+ (select
+ ss_ticket_number,
+ ss_customer_sk,
+ ca_city bought_city,
+ sum(ss_coupon_amt) amt,
+ sum(ss_net_profit) profit
+ from
+ store_sales,
+ date_dim,
+ store,
+ household_demographics,
+ customer_address
+ where
+ store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ and store_sales.ss_store_sk = store.s_store_sk
+ and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+ and store_sales.ss_addr_sk = customer_address.ca_address_sk
+ and (household_demographics.hd_dep_count = 4
+ or household_demographics.hd_vehicle_count = 3)
+ and date_dim.d_dow in (6, 0)
+ and date_dim.d_year in (1999, 1999 + 1, 1999 + 2)
+ and store.s_city in ('Fairview','Midway','Fairview','Fairview','Fairview')
+ group by
+ ss_ticket_number,
+ ss_customer_sk,
+ ss_addr_sk,
+ ca_city
+ ) dn,
+ customer,
+ customer_address current_addr
+where
+ ss_customer_sk = c_customer_sk
+ and customer.c_current_addr_sk = current_addr.ca_address_sk
+ and current_addr.ca_city <> bought_city
+order by
+ c_last_name,
+ c_first_name,
+ ca_city,
+ bought_city,
+ ss_ticket_number
+limit 100;
+---- RESULTS
+'Aaron','Charles','Jamestown','Red Hill',183217,2067.37,-8054.03
+'Aaron','Genevieve','Lakeside','Springtown',185247,1739.84,-1337.44
+'Aaron','Guy','Lakeside','Mount Pleasant',210815,1849.59,-11333.00
+'Abbott','Frederick','Green Acres','Oakland',7247,518.63,-13290.67
+'Abbott','Harriet','Wesley','Forest Hills',84405,800.12,-8540.46
+'Abel','Flora','Perry','Newtown',117310,8532.29,-28474.67
+'Abney','Louis','Gladstone','Green Acres',140967,7.52,-13382.19
+'Abrams','Amy','Salem','Pine Grove',231298,1132.44,-19986.97
+'Abrams','Dorothy','Lincoln','New Hope',99435,2640.46,-12712.81
+'Abreu','David','Brownsville','Mount Pleasant',189640,4026.11,-7711.87
+'Abreu','David','Brownsville','White Oak',55423,2943.20,-5695.81
+'Ackerman','NULL','Hopewell','Pleasant Grove',94468,5981.77,-11788.36
+'Ackley','Christopher','Bethel','Newport',95923,2996.52,-7797.50
+'Acosta','Bobby','Riverdale','Friendship',197899,60.50,-27188.42
+'Acosta','Eric','Fairfield','Shiloh',11072,9.85,-4142.15
+'Acosta','Johnnie','Centerville','Enterprise',69183,1581.96,-3015.22
+'Acosta','Wilfred','Jackson','Macedonia',190054,316.43,-913.72
+'Adair','Silvia','Bayview','Plainview',170324,4208.47,-9295.96
+'Adams','Alicia','Concord','Mount Zion',22029,3132.36,-12262.13
+'Adams','Carlena','Five Forks','Mount Olive',200385,220.23,-7642.46
+'Adams','Christopher','Highland Park','New Hope',89344,66.03,-8711.35
+'Adams','Christopher','Highland Park','Owens',150027,755.51,-5103.79
+'Adams','David','Bridgeport','Antioch',213366,9174.05,-14025.32
+'Adams','David','Bridgeport','Price',233836,1568.08,-20484.95
+'Adams','Earl','Mount Zion','Greenfield',99055,551.98,-6440.04
+'Adams','Henry','Saint James','Georgetown',231785,901.24,-18286.29
+'Adams','Hortencia','Wildwood','Friendship',69694,296.89,-11931.45
+'Adams','Isidro','Glenwood','Oak Grove',148351,923.30,-3177.00
+'Adams','Jane','Shiloh','Red Hill',106214,1434.41,-7320.39
+'Adams','Jay','Sulphur Springs','Bridgeport',195437,141.95,-6997.43
+'Adams','Jim','Pleasant Valley','Edgewater',136554,4677.64,-8308.27
+'Adams','Kimberly','Caledonia','Five Forks',105902,2112.44,-305.98
+'Adams','Magdalena','Glendale','Edgewood',70321,163.95,-3472.48
+'Adams','Patrick','Bridgeport','Edgewood',232741,523.33,-5830.63
+'Adams','Pedro','Pleasant Valley','Enterprise',139637,2968.84,-5251.73
+'Adams','Steven','Greenville','Hillcrest',13710,7235.33,-2345.79
+'Adams','Tim','Mount Zion','Deerfield',188255,1942.53,-11899.84
+'Adams','Tim','Mount Zion','Oak Grove',97273,3299.36,-16893.16
+'Adams','Tonya','Enterprise','Oak Hill',143974,1025.42,-9619.17
+'Adkins','Alica','Oakwood','Woodrow',131755,4255.95,-18319.64
+'Adkins','Debra','Bloomingdale','Wayland',231738,4340.02,-15037.93
+'Adkins','Debra','Helena','Deerfield',97428,1757.72,-4654.11
+'Adkins','Judith','Plainview','Riverview',61339,5414.10,-15150.39
+'Adkins','Melanie','Liberty','Riverside',41578,3471.30,-15110.65
+'Adkins','Michael','Five Forks','Mount Pleasant',158714,3565.11,-14888.46
+'Adkins','Virginia','Highland','Woodlawn',192086,0.00,-5100.88
+'Aguilar','Arlene','Ashland','Belmont',132623,5624.93,-6045.24
+'Aguilar','Douglas','Pine Grove','Brunswick',77899,166.17,-5251.06
+'Aguilar','Karen','Pleasant Hill','Buena Vista',47133,1868.27,-6121.78
+'Aguilar','Kimberly','Pleasant Hill','Jackson',192256,1.19,-7640.59
+'Aguilar','Lynda','Greenwood','Pleasant Valley',51477,978.54,-11859.19
+'Aguilar','Lynda','Greenwood','Woodrow',89360,803.59,-5283.17
+'Aguilar','Milton','Mount Zion','Friendship',162299,5305.56,-7522.86
+'Aguilar','Robert','Walnut Grove','Antioch',169539,2584.34,-5225.46
+'Aguilar','Tracy','Highland Park','Kingston',206717,2995.45,-3976.20
+'Aguilera','Michelle','Tipton','Shiloh',228010,3815.66,-11820.05
+'Ahmed','Maureen','Oak Hill','Union Hill',128051,7270.41,-13433.60
+'Aiken','James','Mount Olive','Crossroads',212463,0.00,-3126.87
+'Akin','Sharon','Harmony','Edgewood',201392,2962.97,-14053.85
+'Alaniz','Elia','Marion','Mount Zion',79719,3691.54,-10297.32
+'Albert','Donald','Red Hill','Macedonia',83899,2140.15,-9622.92
+'Albert','Jesse','Bunker Hill','Stringtown',139171,19429.35,-18276.98
+'Albert','Joseph','Richfield','Brownsville',63910,5982.83,-9276.46
+'Albert','Robert','Greenville','Royal',52927,0.00,-1929.68
+'Albertson','Sarah','Pleasant Valley','White Oak',82675,2311.79,-11496.78
+'Albright','Michelle','Hillcrest','Woodland',49024,6451.20,-12414.60
+'Albright','Vincent','Wildwood','Oakdale',7278,4425.56,-25696.83
+'Alcala','Nancy','Fairfield','Arlington',181927,711.70,-14419.56
+'Aldrich','Stephen','Shady Grove','Post Oak',194020,4141.47,-15841.39
+'Aldrich','Willard','Woodville','Arthur',11159,9577.24,1816.47
+'Aldrich','NULL','Salem','Woodville',107876,638.97,-6506.27
+'Aleman','Eddie','White Oak','Concord',25327,9293.14,-21425.24
+'Alexander','Beverly','Valley View','Five Forks',212635,0.00,-5122.61
+'Alexander','Donna','Liberty','Hamilton',218554,517.32,-10027.79
+'Alexander','Graham','Fairfield','Newtown',104061,1574.22,-4375.65
+'Alexander','Joseph','Winchester','Sutton',211390,6542.30,-20325.16
+'Alexander','Nancy','Riverside','Union Hill',184734,7944.54,-12244.04
+'Alexander','Paul','Springdale','Hopewell',190468,143.92,-9226.83
+'Alfonso','Mabel','Clifton','Red Hill',54976,2422.52,3133.04
+'Ali','Jennifer','Lake Forest','Liberty',40263,3467.53,-18495.55
+'Ali','Stephen','Lone Pine','Glenwood',175860,167.14,-12116.78
+'Allen','Andrea','Lakeside','Newport',11334,6.64,-2610.30
+'Allen','Curtis','Brentwood','Richardson',216964,0.00,-19124.72
+'Allen','Dana','Crossroads','Bunker Hill',112648,2164.34,-11973.83
+'Allen','Donita','Riverdale','Sunnyside',182017,711.62,-3681.42
+'Allen','Earl','Marion','Lincoln',64277,11935.26,-12108.31
+'Allen','Edward','Brownsville','Bunker Hill',155950,3267.59,-7671.84
+'Allen','Edward','Brownsville','Lakewood',162070,1359.35,-15806.88
+'Allen','Emily','Macedonia','Springdale',186035,1860.88,-10258.69
+'Allen','Jeanne','Waterloo','Summit',181784,448.50,-10244.62
+'Allen','Jennifer','Green Acres','Five Points',194912,536.52,-18001.88
+'Allen','John','Sulphur Springs','Five Forks',97314,463.68,-14328.20
+'Allen','Leslie','Five Forks','Ashland',77055,87.92,-9644.24
+'Allen','Mark','Lakeside','Riverside',191934,203.13,-19803.96
+'Allen','Marty','Greenwood','Riverview',1040,308.20,-1547.30
+'Allen','Matthew','Mount Pleasant','Mountain View',190723,2.77,-2219.34
+'Allen','Natalie','Oak Grove','Woodville',82315,2230.76,-14635.09
+'Allen','Robert','Oakdale','White Oak',92331,331.56,1122.69
+'Allen','Roger','Jamestown','Springdale',84977,407.65,-2525.30
+'Allen','Ronald','Wright','Pleasant Valley',8104,24.26,-1996.98
+---- TYPES
+STRING, STRING, STRING, STRING, BIGINT, DECIMAL, DECIMAL
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q50.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q50.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q50.test
new file mode 100644
index 0000000..873e3c1
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q50.test
@@ -0,0 +1,68 @@
+====
+---- QUERY: TPCDS-Q50
+select
+ s_store_name
+ ,s_company_id
+ ,s_street_number
+ ,s_street_name
+ ,s_street_type
+ ,s_suite_number
+ ,s_city
+ ,s_county
+ ,s_state
+ ,s_zip
+ ,sum(case when (sr_returned_date_sk - ss_sold_date_sk <= 30 ) then 1 else 0 end) as "30 days"
+ ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 30) and
+ (sr_returned_date_sk - ss_sold_date_sk <= 60) then 1 else 0 end ) as "31-60 days"
+ ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 60) and
+ (sr_returned_date_sk - ss_sold_date_sk <= 90) then 1 else 0 end) as "61-90 days"
+ ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 90) and
+ (sr_returned_date_sk - ss_sold_date_sk <= 120) then 1 else 0 end) as "91-120 days"
+ ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 120) then 1 else 0 end) as ">120 days"
+from
+ store_sales
+ ,store_returns
+ ,store
+ ,date_dim d1
+ ,date_dim d2
+where
+ d2.d_year = 2001
+and d2.d_moy = 8
+and ss_ticket_number = sr_ticket_number
+and ss_item_sk = sr_item_sk
+and ss_sold_date_sk = d1.d_date_sk
+and sr_returned_date_sk = d2.d_date_sk
+and ss_customer_sk = sr_customer_sk
+and ss_store_sk = s_store_sk
+group by
+ s_store_name
+ ,s_company_id
+ ,s_street_number
+ ,s_street_name
+ ,s_street_type
+ ,s_suite_number
+ ,s_city
+ ,s_county
+ ,s_state
+ ,s_zip
+order by s_store_name
+ ,s_company_id
+ ,s_street_number
+ ,s_street_name
+ ,s_street_type
+ ,s_suite_number
+ ,s_city
+ ,s_county
+ ,s_state
+ ,s_zip
+limit 100;
+---- RESULTS
+'able',1,'255','Sycamore ','Dr.','Suite 410','Midway','Williamson County','TN','31904',67,48,61,66,98
+'ation',1,'811','Lee ','Circle','Suite T','Midway','Williamson County','TN','31904',70,51,50,61,109
+'bar',1,'175','4th ','Court','Suite C','Midway','Williamson County','TN','31904',96,53,55,76,86
+'eing',1,'226','12th ','Lane','Suite D','Fairview','Williamson County','TN','35709',69,63,62,63,114
+'ese',1,'27','Lake ','Ln','Suite 260','Midway','Williamson County','TN','31904',58,57,55,54,106
+'ought',1,'767','Spring ','Wy','Suite 250','Midway','Williamson County','TN','31904',81,63,52,58,103
+---- TYPES
+STRING, INT, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BIGINT, BIGINT, BIGINT, BIGINT, BIGINT
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q51.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q51.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q51.test
new file mode 100644
index 0000000..2aa5146
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q51.test
@@ -0,0 +1,148 @@
+====
+---- QUERY: TPCDS-Q51
+WITH web_v1 as (
+select
+ ws_item_sk item_sk, d_date,
+ sum(sum(ws_sales_price))
+ over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row) cume_sales
+from web_sales
+ ,date_dim
+where ws_sold_date_sk=d_date_sk
+ and d_month_seq between 1200 and 1200+11
+ and ws_item_sk is not NULL
+group by ws_item_sk, d_date),
+store_v1 as (
+select
+ ss_item_sk item_sk, d_date,
+ sum(sum(ss_sales_price))
+ over (partition by ss_item_sk order by d_date rows between unbounded preceding and current row) cume_sales
+from store_sales
+ ,date_dim
+where ss_sold_date_sk=d_date_sk
+ and d_month_seq between 1200 and 1200+11
+ and ss_item_sk is not NULL
+group by ss_item_sk, d_date)
+ select *
+from (select item_sk
+ ,d_date
+ ,web_sales
+ ,store_sales
+ ,max(web_sales)
+ over (partition by item_sk order by d_date rows between unbounded preceding and current row) web_cumulative
+ ,max(store_sales)
+ over (partition by item_sk order by d_date rows between unbounded preceding and current row) store_cumulative
+ from (select case when web.item_sk is not null then web.item_sk else store.item_sk end item_sk
+ ,case when web.d_date is not null then web.d_date else store.d_date end d_date
+ ,web.cume_sales web_sales
+ ,store.cume_sales store_sales
+ from web_v1 web full outer join store_v1 store on (web.item_sk = store.item_sk
+ and web.d_date = store.d_date)
+ )x )y
+where web_cumulative > store_cumulative
+order by item_sk
+ ,d_date
+limit 100;
+---- RESULTS
+14,'2000-01-10',176.83,NULL,176.83,73.60
+14,'2000-01-21',NULL,75.29,176.83,75.29
+14,'2000-01-29',222.33,NULL,222.33,75.29
+14,'2000-02-02',224.01,NULL,224.01,75.29
+14,'2000-02-08',NULL,85.07,224.01,85.07
+14,'2000-02-19',NULL,98.60,224.01,98.60
+14,'2000-02-21',241.64,NULL,241.64,98.60
+14,'2000-02-22',NULL,99.83,241.64,99.83
+14,'2000-03-18',NULL,112.82,241.64,112.82
+14,'2000-03-23',251.15,NULL,251.15,112.82
+14,'2000-03-28',260.17,NULL,260.17,112.82
+14,'2000-03-31',370.74,NULL,370.74,112.82
+14,'2000-04-05',NULL,115.94,370.74,115.94
+14,'2000-04-15',445.30,NULL,445.30,115.94
+14,'2000-04-27',NULL,151.48,445.30,151.48
+14,'2000-05-03',NULL,176.89,445.30,176.89
+14,'2000-05-10',451.40,NULL,451.40,176.89
+14,'2000-05-21',NULL,238.39,451.40,238.39
+14,'2000-05-26',596.81,NULL,596.81,238.39
+14,'2000-05-29',NULL,242.51,596.81,242.51
+14,'2000-06-05',NULL,304.64,596.81,304.64
+14,'2000-07-03',623.77,NULL,623.77,304.64
+14,'2000-07-07',NULL,307.77,623.77,307.77
+14,'2000-07-18',NULL,320.04,623.77,320.04
+14,'2000-07-25',673.08,NULL,673.08,320.04
+14,'2000-08-10',NULL,411.48,673.08,411.48
+14,'2000-08-14',NULL,465.85,673.08,465.85
+14,'2000-08-21',NULL,541.45,673.08,541.45
+14,'2000-08-26',NULL,574.56,673.08,574.56
+19,'2000-01-02',56.96,49.68,56.96,49.68
+25,'2000-01-21',97.29,NULL,97.29,4.49
+25,'2000-01-28',192.46,NULL,192.46,4.49
+25,'2000-02-09',NULL,24.23,192.46,24.23
+25,'2000-02-11',NULL,98.99,192.46,98.99
+25,'2000-02-21',NULL,170.60,192.46,170.60
+25,'2000-02-22',NULL,185.05,192.46,185.05
+35,'2000-01-14',NULL,55.24,177.88,55.24
+35,'2000-01-16',NULL,95.92,177.88,95.92
+35,'2000-01-18',NULL,126.45,177.88,126.45
+35,'2000-01-19',NULL,167.07,177.88,167.07
+35,'2000-02-17',NULL,173.97,177.88,173.97
+35,'2000-02-22',270.43,NULL,270.43,173.97
+35,'2000-02-23',NULL,180.61,270.43,180.61
+35,'2000-03-03',NULL,181.99,270.43,181.99
+35,'2000-03-05',NULL,221.24,270.43,221.24
+35,'2000-03-06',NULL,266.41,270.43,266.41
+37,'2000-01-02',31.75,11.89,31.75,11.89
+37,'2000-01-04',NULL,17.15,31.75,17.15
+37,'2000-01-05',34.34,NULL,34.34,17.15
+37,'2000-01-06',NULL,29.67,34.34,29.67
+41,'2000-01-21',NULL,15.54,123.34,15.54
+41,'2000-02-03',NULL,21.04,123.34,21.04
+41,'2000-02-16',NULL,33.46,123.34,33.46
+41,'2000-02-20',NULL,37.46,123.34,37.46
+41,'2000-02-22',NULL,58.57,123.34,58.57
+41,'2000-03-05',NULL,70.06,123.34,70.06
+41,'2000-03-17',178.84,150.76,178.84,150.76
+41,'2000-04-26',263.14,NULL,263.14,254.88
+41,'2000-07-12',474.83,NULL,474.83,393.87
+41,'2000-07-18',NULL,421.23,474.83,421.23
+41,'2000-08-15',NULL,430.77,474.83,430.77
+49,'2000-01-18',NULL,2.51,4.58,2.51
+49,'2000-01-31',72.47,NULL,72.47,13.05
+49,'2000-02-13',NULL,70.68,72.47,70.68
+49,'2000-02-29',NULL,71.86,72.47,71.86
+49,'2000-04-17',225.29,NULL,225.29,219.03
+53,'2000-01-02',12.85,1.13,12.85,1.13
+53,'2000-01-08',119.24,NULL,119.24,1.13
+53,'2000-01-09',126.98,NULL,126.98,1.13
+53,'2000-01-15',NULL,3.20,126.98,3.20
+53,'2000-02-04',NULL,22.89,126.98,22.89
+53,'2000-02-05',NULL,64.45,126.98,64.45
+53,'2000-02-12',NULL,66.06,126.98,66.06
+56,'2000-01-02',41.57,17.31,41.57,17.31
+61,'2000-02-17',421.60,NULL,421.60,344.03
+61,'2000-03-01',NULL,411.33,421.60,411.33
+61,'2000-04-22',600.20,NULL,600.20,573.28
+71,'2000-01-02',13.92,2.88,13.92,2.88
+85,'2000-02-03',NULL,42.30,65.50,42.30
+85,'2000-02-16',NULL,42.95,65.50,42.95
+85,'2000-04-19',335.16,NULL,335.16,247.67
+85,'2000-04-23',NULL,252.83,335.16,252.83
+85,'2000-05-02',NULL,289.65,335.16,289.65
+85,'2000-05-11',NULL,312.62,335.16,312.62
+86,'2000-01-19',31.70,NULL,31.70,25.97
+86,'2000-02-03',151.26,NULL,151.26,91.16
+86,'2000-02-04',NULL,112.15,151.26,112.15
+89,'2000-01-12',NULL,28.84,181.56,28.84
+89,'2000-01-23',NULL,67.19,181.56,67.19
+89,'2000-01-30',NULL,104.65,181.56,104.65
+89,'2000-02-22',NULL,146.96,181.56,146.96
+89,'2000-02-25',NULL,147.02,181.56,147.02
+89,'2000-03-19',NULL,172.85,181.56,172.85
+89,'2000-03-20',191.66,NULL,191.66,172.85
+89,'2000-04-11',295.81,NULL,295.81,172.85
+89,'2000-04-13',NULL,203.86,295.81,203.86
+89,'2000-04-20',373.30,NULL,373.30,203.86
+89,'2000-04-23',NULL,219.74,373.30,219.74
+89,'2000-04-26',NULL,235.97,373.30,235.97
+89,'2000-05-04',NULL,248.05,373.30,248.05
+---- TYPES
+BIGINT, STRING, DECIMAL, DECIMAL, DECIMAL, DECIMAL
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q51a.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q51a.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q51a.test
new file mode 100644
index 0000000..c27ac06
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q51a.test
@@ -0,0 +1,166 @@
+====
+---- QUERY: TPCDS-Q51A
+WITH web_tv as (
+select
+ ws_item_sk item_sk, d_date, sum(ws_sales_price) sumws,
+ row_number()
+ over (partition by ws_item_sk order by d_date) rk
+from web_sales
+ ,date_dim
+where ws_sold_date_sk=d_date_sk
+ and d_month_seq between 1200 and 1200+11
+ and ws_item_sk is not NULL
+group by ws_item_sk, d_date
+),
+web_v1 as (
+select v1.item_sk, v1.d_date, v1.sumws, sum(v2.sumws) cume_sales
+from web_tv v1, web_tv v2
+where v1.item_sk = v2.item_sk and v1.rk >= v2.rk
+group by v1.item_sk, v1.d_date, v1.sumws
+),
+store_tv as (
+select
+ ss_item_sk item_sk, d_date, sum(ss_sales_price) sumss,
+ row_number()
+ over (partition by ss_item_sk order by d_date) rk
+from store_sales
+ ,date_dim
+where ss_sold_date_sk=d_date_sk
+ and d_month_seq between 1200 and 1200+11
+ and ss_item_sk is not NULL
+group by ss_item_sk, d_date
+),
+store_v1 as (
+select v1.item_sk, v1.d_date, v1.sumss, sum(v2.sumss) cume_sales
+from store_tv v1, store_tv v2
+where v1.item_sk = v2.item_sk and v1.rk >= v2.rk
+group by v1.item_sk, v1.d_date, v1.sumss
+),
+v as (
+select item_sk
+ ,d_date
+ ,web_sales
+ ,store_sales
+ ,row_number() over (partition by item_sk order by d_date) rk
+ from (select case when web.item_sk is not null then web.item_sk else store.item_sk end item_sk
+ ,case when web.d_date is not null then web.d_date else store.d_date end d_date
+ ,web.cume_sales web_sales
+ ,store.cume_sales store_sales
+ from web_v1 web full outer join store_v1 store on (web.item_sk = store.item_sk
+ and web.d_date = store.d_date)
+ ) x
+)
+ select *
+from(
+ select v1.item_sk, v1.d_date, v1.web_sales, v1.store_sales, max(v2.web_sales) web_cumulative, max(v2.store_sales) store_cumulative
+ from v v1, v v2
+ where v1.item_sk = v2.item_sk and v1.rk >= v2.rk
+ group by v1.item_sk, v1.d_date, v1.web_sales, v1.store_sales
+)x
+where web_cumulative > store_cumulative
+order by item_sk, d_date
+limit 100;
+---- RESULTS
+14,'2000-01-10',176.83,NULL,176.83,73.60
+14,'2000-01-21',NULL,75.29,176.83,75.29
+14,'2000-01-29',222.33,NULL,222.33,75.29
+14,'2000-02-02',224.01,NULL,224.01,75.29
+14,'2000-02-08',NULL,85.07,224.01,85.07
+14,'2000-02-19',NULL,98.60,224.01,98.60
+14,'2000-02-21',241.64,NULL,241.64,98.60
+14,'2000-02-22',NULL,99.83,241.64,99.83
+14,'2000-03-18',NULL,112.82,241.64,112.82
+14,'2000-03-23',251.15,NULL,251.15,112.82
+14,'2000-03-28',260.17,NULL,260.17,112.82
+14,'2000-03-31',370.74,NULL,370.74,112.82
+14,'2000-04-05',NULL,115.94,370.74,115.94
+14,'2000-04-15',445.30,NULL,445.30,115.94
+14,'2000-04-27',NULL,151.48,445.30,151.48
+14,'2000-05-03',NULL,176.89,445.30,176.89
+14,'2000-05-10',451.40,NULL,451.40,176.89
+14,'2000-05-21',NULL,238.39,451.40,238.39
+14,'2000-05-26',596.81,NULL,596.81,238.39
+14,'2000-05-29',NULL,242.51,596.81,242.51
+14,'2000-06-05',NULL,304.64,596.81,304.64
+14,'2000-07-03',623.77,NULL,623.77,304.64
+14,'2000-07-07',NULL,307.77,623.77,307.77
+14,'2000-07-18',NULL,320.04,623.77,320.04
+14,'2000-07-25',673.08,NULL,673.08,320.04
+14,'2000-08-10',NULL,411.48,673.08,411.48
+14,'2000-08-14',NULL,465.85,673.08,465.85
+14,'2000-08-21',NULL,541.45,673.08,541.45
+14,'2000-08-26',NULL,574.56,673.08,574.56
+19,'2000-01-02',56.96,49.68,56.96,49.68
+25,'2000-01-21',97.29,NULL,97.29,4.49
+25,'2000-01-28',192.46,NULL,192.46,4.49
+25,'2000-02-09',NULL,24.23,192.46,24.23
+25,'2000-02-11',NULL,98.99,192.46,98.99
+25,'2000-02-21',NULL,170.60,192.46,170.60
+25,'2000-02-22',NULL,185.05,192.46,185.05
+35,'2000-01-14',NULL,55.24,177.88,55.24
+35,'2000-01-16',NULL,95.92,177.88,95.92
+35,'2000-01-18',NULL,126.45,177.88,126.45
+35,'2000-01-19',NULL,167.07,177.88,167.07
+35,'2000-02-17',NULL,173.97,177.88,173.97
+35,'2000-02-22',270.43,NULL,270.43,173.97
+35,'2000-02-23',NULL,180.61,270.43,180.61
+35,'2000-03-03',NULL,181.99,270.43,181.99
+35,'2000-03-05',NULL,221.24,270.43,221.24
+35,'2000-03-06',NULL,266.41,270.43,266.41
+37,'2000-01-02',31.75,11.89,31.75,11.89
+37,'2000-01-04',NULL,17.15,31.75,17.15
+37,'2000-01-05',34.34,NULL,34.34,17.15
+37,'2000-01-06',NULL,29.67,34.34,29.67
+41,'2000-01-21',NULL,15.54,123.34,15.54
+41,'2000-02-03',NULL,21.04,123.34,21.04
+41,'2000-02-16',NULL,33.46,123.34,33.46
+41,'2000-02-20',NULL,37.46,123.34,37.46
+41,'2000-02-22',NULL,58.57,123.34,58.57
+41,'2000-03-05',NULL,70.06,123.34,70.06
+41,'2000-03-17',178.84,150.76,178.84,150.76
+41,'2000-04-26',263.14,NULL,263.14,254.88
+41,'2000-07-12',474.83,NULL,474.83,393.87
+41,'2000-07-18',NULL,421.23,474.83,421.23
+41,'2000-08-15',NULL,430.77,474.83,430.77
+49,'2000-01-18',NULL,2.51,4.58,2.51
+49,'2000-01-31',72.47,NULL,72.47,13.05
+49,'2000-02-13',NULL,70.68,72.47,70.68
+49,'2000-02-29',NULL,71.86,72.47,71.86
+49,'2000-04-17',225.29,NULL,225.29,219.03
+53,'2000-01-02',12.85,1.13,12.85,1.13
+53,'2000-01-08',119.24,NULL,119.24,1.13
+53,'2000-01-09',126.98,NULL,126.98,1.13
+53,'2000-01-15',NULL,3.20,126.98,3.20
+53,'2000-02-04',NULL,22.89,126.98,22.89
+53,'2000-02-05',NULL,64.45,126.98,64.45
+53,'2000-02-12',NULL,66.06,126.98,66.06
+56,'2000-01-02',41.57,17.31,41.57,17.31
+61,'2000-02-17',421.60,NULL,421.60,344.03
+61,'2000-03-01',NULL,411.33,421.60,411.33
+61,'2000-04-22',600.20,NULL,600.20,573.28
+71,'2000-01-02',13.92,2.88,13.92,2.88
+85,'2000-02-03',NULL,42.30,65.50,42.30
+85,'2000-02-16',NULL,42.95,65.50,42.95
+85,'2000-04-19',335.16,NULL,335.16,247.67
+85,'2000-04-23',NULL,252.83,335.16,252.83
+85,'2000-05-02',NULL,289.65,335.16,289.65
+85,'2000-05-11',NULL,312.62,335.16,312.62
+86,'2000-01-19',31.70,NULL,31.70,25.97
+86,'2000-02-03',151.26,NULL,151.26,91.16
+86,'2000-02-04',NULL,112.15,151.26,112.15
+89,'2000-01-12',NULL,28.84,181.56,28.84
+89,'2000-01-23',NULL,67.19,181.56,67.19
+89,'2000-01-30',NULL,104.65,181.56,104.65
+89,'2000-02-22',NULL,146.96,181.56,146.96
+89,'2000-02-25',NULL,147.02,181.56,147.02
+89,'2000-03-19',NULL,172.85,181.56,172.85
+89,'2000-03-20',191.66,NULL,191.66,172.85
+89,'2000-04-11',295.81,NULL,295.81,172.85
+89,'2000-04-13',NULL,203.86,295.81,203.86
+89,'2000-04-20',373.30,NULL,373.30,203.86
+89,'2000-04-23',NULL,219.74,373.30,219.74
+89,'2000-04-26',NULL,235.97,373.30,235.97
+89,'2000-05-04',NULL,248.05,373.30,248.05
+---- TYPES
+BIGINT, STRING, DECIMAL, DECIMAL, DECIMAL, DECIMAL
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q52.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q52.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q52.test
new file mode 100644
index 0000000..dfa1fc4
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q52.test
@@ -0,0 +1,130 @@
+====
+---- QUERY: TPCDS-Q52
+select
+ dt.d_year,
+ item.i_brand_id brand_id,
+ item.i_brand brand,
+ sum(ss_ext_sales_price) ext_price
+from
+ date_dim dt,
+ store_sales,
+ item
+where
+ dt.d_date_sk = store_sales.ss_sold_date_sk
+ and store_sales.ss_item_sk = item.i_item_sk
+ and item.i_manager_id = 1
+ and dt.d_moy = 11
+ and dt.d_year = 2000
+group by
+ dt.d_year,
+ item.i_brand,
+ item.i_brand_id
+order by
+ dt.d_year,
+ ext_price desc,
+ brand_id
+limit 100;
+---- RESULTS
+2000,2001002,'amalgimporto #2',133668.20
+2000,1003001,'exportiamalg #1',85038.62
+2000,3002002,'importoexporti #2',79104.47
+2000,3004002,'edu packexporti #2',78772.08
+2000,3003001,'exportiexporti #1',67105.01
+2000,2004002,'edu packimporto #2',65904.27
+2000,4001002,'amalgedu pack #2',56283.13
+2000,5002002,'importoscholar #2',54390.12
+2000,7016001,'corpnameless #1',51331.65
+2000,2002001,'importoimporto #1',50831.66
+2000,7001005,'amalgbrand #5',50727.31
+2000,9015009,'scholarunivamalg #9',49522.53
+2000,5004002,'edu packscholar #2',45205.15
+2000,4003001,'exportiedu pack #1',42810.52
+2000,5001002,'amalgscholar #2',42061.33
+2000,1003002,'exportiamalg #2',41130.32
+2000,2003002,'exportiimporto #2',39318.67
+2000,7007004,'brandbrand #4',38993.74
+2000,1002002,'importoamalg #2',38952.02
+2000,8003010,'exportinameless #10',37010.51
+2000,1002001,'importounivamalg #1',36393.47
+2000,4002001,'scholarmaxi #6',34103.67
+2000,1001002,'amalgamalg #2',32518.42
+2000,4004002,'edu packedu pack #2',32114.76
+2000,5001001,'edu packexporti #2',31507.22
+2000,6005005,'edu packnameless #8',31445.28
+2000,1001001,'amalgamalg #1',31305.49
+2000,4002001,'importoedu pack #1',31248.26
+2000,6005001,'scholarcorp #1',30955.09
+2000,1004001,'edu packamalg #1',30464.10
+2000,7009004,'maxibrand #4',29127.01
+2000,10010013,'univamalgamalg #13',29071.87
+2000,6012008,'importobrand #8',28799.91
+2000,8005009,'corpnameless #10',28231.03
+2000,5003001,'exportischolar #1',27336.64
+2000,5001001,'brandunivamalg #11',26418.90
+2000,3001002,'amalgexporti #2',25858.35
+2000,4004001,'edu packedu pack #1',25715.17
+2000,7009009,'exportibrand #10',25380.68
+2000,4004001,'maxinameless #8',23992.40
+2000,10014001,'maxibrand #4',23662.09
+2000,10009015,'maxiunivamalg #15',23576.97
+2000,3003001,'exportiedu pack #2',21959.63
+2000,10004004,'edu packunivamalg #4',21950.07
+2000,3002001,'importoexporti #1',21677.43
+2000,1004002,'edu packamalg #2',21563.27
+2000,2002002,'importoimporto #2',21502.53
+2000,2001001,'importoimporto #2',21106.22
+2000,9012003,'importounivamalg #3',21075.27
+2000,3003002,'exportiexporti #2',20711.54
+2000,10015013,'scholaramalgamalg #13',20610.41
+2000,7010005,'corpunivamalg #6',19821.13
+2000,4003001,'exportischolar #2',19693.88
+2000,9012008,'importounivamalg #8',19463.65
+2000,7010009,'univnameless #9',19176.91
+2000,7008004,'namelessbrand #4',19128.07
+2000,2003001,'exportiimporto #1',19074.52
+2000,6008005,'namelesscorp #5',19067.51
+2000,3004001,'edu packexporti #1',18504.78
+2000,5001001,'exportinameless #8',18493.00
+2000,10003016,'exportiunivamalg #16',18413.97
+2000,3001001,'maxibrand #8',18290.52
+2000,8003007,'edu packnameless #8',18265.99
+2000,7016007,'corpnameless #7',18103.37
+2000,5003002,'exportischolar #2',17930.96
+2000,8016004,'corpmaxi #4',17828.86
+2000,2004001,'importoexporti #2',17646.57
+2000,10004012,'edu packunivamalg #12',17608.05
+2000,8002009,'importonameless #9',17513.05
+2000,7004009,'importoimporto #2',17312.88
+2000,2004001,'edu packunivamalg #8',17084.17
+2000,9014006,'edu packunivamalg #6',16958.88
+2000,9015011,'exportiamalg #2',15875.77
+2000,10004005,'importounivamalg #6',15258.42
+2000,6008002,'namelesscorp #2',15199.06
+2000,6004002,'edu packcorp #2',14905.77
+2000,9013009,'exportiunivamalg #9',14704.96
+2000,7012010,'importonameless #10',14697.15
+2000,7013007,'exportinameless #7',14068.62
+2000,7008009,'namelessbrand #9',13759.65
+2000,7006007,'edu packamalg #2',13705.14
+2000,10012004,'importoamalgamalg #4',13514.95
+2000,6015006,'scholarbrand #6',13421.39
+2000,10002012,'importounivamalg #12',13080.55
+2000,9016003,'corpunivamalg #3',12936.25
+2000,6002004,'importocorp #4',12491.48
+2000,8004003,'edu packnameless #3',12480.24
+2000,6011008,'amalgbrand #8',12236.00
+2000,6003008,'exporticorp #8',11621.79
+2000,8005008,'scholarnameless #8',11609.84
+2000,4001001,'amalgedu pack #1',11110.78
+2000,7009010,'maxibrand #10',11061.00
+2000,3004001,'edu packscholar #2',11025.47
+2000,7012001,'amalgamalgamalg #2',10846.24
+2000,7016009,'univnameless #10',10454.42
+2000,7014001,'edu packnameless #1',9596.47
+2000,1002001,'importoamalg #1',9579.28
+2000,8010004,'univmaxi #4',9508.89
+2000,3001001,'amalgexporti #1',9373.84
+2000,6010005,'univbrand #5',9222.91
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q53.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q53.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q53.test
new file mode 100644
index 0000000..793ac71
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q53.test
@@ -0,0 +1,140 @@
+====
+---- QUERY: TPCDS-Q53
+select
+ *
+from
+ (select
+ i_manufact_id,
+ sum(ss_sales_price) sum_sales,
+ avg(sum(ss_sales_price)) over (partition by i_manufact_id) avg_quarterly_sales
+ from
+ item,
+ store_sales,
+ date_dim,
+ store
+ where
+ ss_item_sk = i_item_sk
+ and ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq in (1200, 1200 + 1, 1200 + 2, 1200 + 3, 1200 + 4, 1200 + 5, 1200 + 6, 1200 + 7, 1200 + 8, 1200 + 9, 1200 + 10, 1200 + 11)
+ and ((i_category in ('Books', 'Children', 'Electronics')
+ and i_class in ('personal', 'portable', 'reference', 'self-help')
+ and i_brand in ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9'))
+ or (i_category in ('Women', 'Music', 'Men')
+ and i_class in ('accessories', 'classical', 'fragrances', 'pants')
+ and i_brand in ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1')))
+ group by
+ i_manufact_id,
+ d_qoy
+ ) tmp1
+where
+ case when avg_quarterly_sales > 0 then abs (sum_sales - avg_quarterly_sales) / avg_quarterly_sales else null end > 0.1
+order by
+ avg_quarterly_sales,
+ sum_sales,
+ i_manufact_id
+limit 100
+---- RESULTS
+30,165.67,340.635000
+30,247.07,340.635000
+30,627.63,340.635000
+619,158.76,348.640000
+619,210.81,348.640000
+619,464.26,348.640000
+619,560.73,348.640000
+271,79.26,354.330000
+271,86.87,354.330000
+271,179.61,354.330000
+271,1071.58,354.330000
+827,82.44,356.590000
+827,320.05,356.590000
+827,666.52,356.590000
+296,188.61,369.125000
+296,265.76,369.125000
+296,655.24,369.125000
+308,200.28,385.982500
+308,214.07,385.982500
+308,489.17,385.982500
+308,640.41,385.982500
+486,178.80,400.530000
+486,455.08,400.530000
+486,468.01,400.530000
+486,500.23,400.530000
+554,191.48,407.975000
+554,346.80,407.975000
+554,660.97,407.975000
+208,151.84,410.385000
+208,207.02,410.385000
+208,533.75,410.385000
+208,748.93,410.385000
+662,199.83,412.135000
+662,300.61,412.135000
+662,460.94,412.135000
+662,687.16,412.135000
+394,264.65,413.405000
+394,272.02,413.405000
+394,674.12,413.405000
+221,183.63,416.082500
+221,534.62,416.082500
+221,552.44,416.082500
+621,232.20,418.115000
+621,624.80,418.115000
+507,76.98,419.465000
+507,505.36,419.465000
+507,644.24,419.465000
+316,36.25,423.035000
+316,158.85,423.035000
+316,697.32,423.035000
+316,799.72,423.035000
+56,110.81,423.097500
+56,371.77,423.097500
+56,500.77,423.097500
+56,709.04,423.097500
+517,194.87,426.287500
+517,313.41,426.287500
+517,482.41,426.287500
+517,714.46,426.287500
+411,364.09,426.892500
+411,519.60,426.892500
+247,313.42,427.682500
+247,353.08,427.682500
+247,628.37,427.682500
+652,228.75,434.752500
+652,314.98,434.752500
+652,365.90,434.752500
+652,829.38,434.752500
+129,301.69,436.452500
+129,325.26,436.452500
+129,550.47,436.452500
+129,568.39,436.452500
+99,164.24,438.667500
+99,183.75,438.667500
+99,393.58,438.667500
+99,1013.10,438.667500
+235,111.64,441.030000
+235,179.51,441.030000
+235,493.83,441.030000
+235,979.14,441.030000
+360,143.83,445.415000
+360,293.99,445.415000
+360,928.51,445.415000
+732,106.56,447.065000
+732,300.77,447.065000
+732,498.49,447.065000
+732,882.44,447.065000
+190,202.77,451.982500
+190,233.10,451.982500
+190,358.97,451.982500
+190,1013.09,451.982500
+147,212.53,455.892500
+147,366.10,455.892500
+147,553.92,455.892500
+147,691.02,455.892500
+665,283.25,456.087500
+665,357.22,456.087500
+665,691.20,456.087500
+85,325.28,456.280000
+85,350.10,456.280000
+---- TYPES
+INT, DECIMAL, DECIMAL
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q54.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q54.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q54.test
new file mode 100644
index 0000000..7130c4e
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q54.test
@@ -0,0 +1,60 @@
+====
+---- QUERY: TPCDS-Q54
+with my_customers as (
+ select distinct c_customer_sk
+ , c_current_addr_sk
+ from
+ ( select cs_sold_date_sk sold_date_sk,
+ cs_bill_customer_sk customer_sk,
+ cs_item_sk item_sk
+ from catalog_sales
+ union all
+ select ws_sold_date_sk sold_date_sk,
+ ws_bill_customer_sk customer_sk,
+ ws_item_sk item_sk
+ from web_sales
+ ) cs_or_ws_sales,
+ item,
+ date_dim,
+ customer
+ where sold_date_sk = d_date_sk
+ and item_sk = i_item_sk
+ and i_category = 'Women'
+ and i_class = 'maternity'
+ and c_customer_sk = cs_or_ws_sales.customer_sk
+ and d_moy = 12
+ and d_year = 1998
+ )
+ , my_revenue as (
+ select c_customer_sk,
+ sum(ss_ext_sales_price) as revenue
+ from my_customers,
+ store_sales,
+ customer_address,
+ store,
+ date_dim
+ where c_current_addr_sk = ca_address_sk
+ and ca_county = s_county
+ and ca_state = s_state
+ and ss_sold_date_sk = d_date_sk
+ and c_customer_sk = ss_customer_sk
+ and d_month_seq between (select distinct d_month_seq+1
+ from date_dim where d_year = 1998 and d_moy = 12 limit 1)
+ and (select distinct d_month_seq+3
+ from date_dim where d_year = 1998 and d_moy = 12 limit 1)
+ group by c_customer_sk
+ )
+ , segments as
+ (select cast((revenue/50) as int) as segment
+ from my_revenue
+ )
+ select segment, count(*) as num_customers, segment*50 as segment_base
+ from segments
+ group by segment
+ order by segment, num_customers
+ limit 100;
+---- RESULTS
+11860,1,593000
+---- TYPES
+INT, BIGINT, BIGINT
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q55.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q55.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q55.test
new file mode 100644
index 0000000..e522ffb
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q55.test
@@ -0,0 +1,127 @@
+====
+---- QUERY: TPCDS-Q55
+select
+ i_brand_id brand_id,
+ i_brand brand,
+ sum(ss_ext_sales_price) ext_price
+from
+ date_dim,
+ store_sales,
+ item
+where
+ d_date_sk = ss_sold_date_sk
+ and ss_item_sk = i_item_sk
+ and i_manager_id = 28
+ and d_moy = 12
+ and d_year = 1999
+group by
+ i_brand,
+ i_brand_id
+order by
+ ext_price desc,
+ i_brand_id
+limit 100;
+---- RESULTS
+1001001,'amalgamalg #1',136722.83
+2001001,'amalgimporto #1',133391.08
+5001001,'amalgscholar #1',118347.84
+5003001,'exportischolar #1',109595.01
+1002001,'importoamalg #1',105226.72
+5003002,'exportischolar #2',95810.25
+5004001,'edu packscholar #1',90624.90
+4001001,'amalgedu pack #1',83730.42
+1001002,'amalgamalg #2',81937.80
+4001002,'amalgedu pack #2',78799.33
+2002001,'importoimporto #1',77701.54
+3003001,'exportiexporti #1',66809.69
+4002001,'importoedu pack #1',65056.38
+5001002,'amalgscholar #2',63100.23
+4003001,'exportiedu pack #1',61067.16
+3001001,'amalgexporti #1',58157.56
+5002001,'importoscholar #1',57895.73
+2003001,'exportiimporto #1',54996.70
+4004002,'edu packedu pack #2',54650.86
+6016005,'corpbrand #5',50955.77
+1004001,'edu packamalg #1',50844.36
+6002001,'importocorp #1',48122.46
+7015003,'scholarnameless #3',47531.49
+1004002,'edu packamalg #2',46925.04
+6006006,'corpcorp #6',44504.94
+3002001,'importoexporti #1',44449.84
+5002002,'importoscholar #2',43736.13
+9004008,'edu packmaxi #8',43124.13
+6011006,'amalgbrand #6',42516.13
+9016003,'corpunivamalg #3',41651.94
+8009007,'maxinameless #7',40216.48
+9008008,'namelessmaxi #8',37294.08
+10012012,'importoamalgamalg #12',36166.35
+2004001,'edu packimporto #1',36056.65
+10003013,'exportiunivamalg #13',32619.62
+1003001,'exportiamalg #1',28084.23
+8010006,'univmaxi #6',27531.46
+8006005,'corpnameless #5',26468.77
+2004002,'edu packimporto #2',26438.94
+6014007,'edu packbrand #7',25884.76
+6005001,'scholarcorp #1',25849.68
+9005002,'scholarmaxi #2',25116.15
+9011003,'amalgunivamalg #3',25085.52
+10006017,'corpunivamalg #17',25085.08
+6009008,'maxicorp #8',24934.96
+7011009,'amalgnameless #9',24823.19
+9011009,'amalgunivamalg #9',24802.03
+8013007,'exportimaxi #7',24582.70
+8012005,'importomaxi #5',24370.92
+6003006,'exporticorp #6',23710.13
+3004001,'edu packexporti #1',23358.51
+1003002,'exportiamalg #2',23154.78
+7002004,'importobrand #4',23003.13
+9005011,'scholarmaxi #11',22313.86
+9013009,'exportiunivamalg #9',22303.83
+9003003,'exportimaxi #3',21736.62
+2001002,'amalgimporto #2',21466.13
+6011001,'amalgbrand #1',21162.91
+6008008,'namelesscorp #8',20636.40
+9016009,'corpunivamalg #9',20465.89
+10013009,'exportiamalgamalg #9',19610.85
+10009006,'maxiunivamalg #6',19564.84
+4002002,'importoedu pack #2',19399.39
+3002002,'importoexporti #2',19272.70
+6002002,'importocorp #2',19147.26
+3001002,'amalgexporti #2',19008.57
+6014001,'edu packbrand #1',18807.55
+8002010,'importonameless #10',18591.58
+7003005,'exportibrand #5',18527.36
+2003002,'exportiimporto #2',18318.61
+6005003,'scholarcorp #3',18167.51
+8009003,'maxinameless #3',17115.53
+8001003,'amalgnameless #3',17068.61
+7015007,'scholarnameless #7',16069.97
+9011008,'amalgunivamalg #8',15861.18
+8011009,'amalgmaxi #9',15785.74
+6014008,'edu packbrand #8',15668.28
+10016003,'corpamalgamalg #3',15322.94
+6015003,'scholarbrand #3',14890.37
+8005010,'scholarnameless #10',14620.95
+8004009,'edu packnameless #9',14529.33
+10004001,'edu packunivamalg #1',14101.90
+8006008,'corpnameless #8',13159.42
+10015001,'scholaramalgamalg #1',13119.59
+9015009,'scholarunivamalg #9',12679.95
+7006010,'corpbrand #10',11490.56
+10005001,'scholarunivamalg #1',11299.62
+6015001,'scholarbrand #1',11242.40
+6013007,'exportibrand #7',11162.36
+6006002,'corpcorp #2',10986.40
+10015011,'scholaramalgamalg #11',10980.10
+8004003,'edu packnameless #3',10945.08
+6001005,'amalgcorp #5',10108.20
+10014016,'edu packamalgamalg #16',9656.21
+6008001,'namelesscorp #1',9546.46
+6009003,'maxicorp #3',8425.25
+10011013,'amalgamalgamalg #13',7914.33
+7009005,'maxibrand #5',7859.23
+10006001,'corpunivamalg #1',6901.74
+9009002,'maximaxi #2',4025.61
+---- TYPES
+INT, STRING, DECIMAL
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q56.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q56.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q56.test
new file mode 100644
index 0000000..4283385
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q56.test
@@ -0,0 +1,171 @@
+====
+---- QUERY: TPCDS-Q56
+with ss as (
+ select i_item_id,sum(ss_ext_sales_price) total_sales
+ from
+ store_sales,
+ date_dim,
+ customer_address,
+ item
+ where i_item_id in (select
+ i_item_id
+from item
+where i_color in ('slate','blanched','burnished'))
+ and ss_item_sk = i_item_sk
+ and ss_sold_date_sk = d_date_sk
+ and d_year = 2001
+ and d_moy = 2
+ and ss_addr_sk = ca_address_sk
+ and ca_gmt_offset = -5
+ group by i_item_id),
+ cs as (
+ select i_item_id,sum(cs_ext_sales_price) total_sales
+ from
+ catalog_sales,
+ date_dim,
+ customer_address,
+ item
+ where
+ i_item_id in (select
+ i_item_id
+from item
+where i_color in ('slate','blanched','burnished'))
+ and cs_item_sk = i_item_sk
+ and cs_sold_date_sk = d_date_sk
+ and d_year = 2001
+ and d_moy = 2
+ and cs_bill_addr_sk = ca_address_sk
+ and ca_gmt_offset = -5
+ group by i_item_id),
+ ws as (
+ select i_item_id,sum(ws_ext_sales_price) total_sales
+ from
+ web_sales,
+ date_dim,
+ customer_address,
+ item
+ where
+ i_item_id in (select
+ i_item_id
+from item
+where i_color in ('slate','blanched','burnished'))
+ and ws_item_sk = i_item_sk
+ and ws_sold_date_sk = d_date_sk
+ and d_year = 2001
+ and d_moy = 2
+ and ws_bill_addr_sk = ca_address_sk
+ and ca_gmt_offset = -5
+ group by i_item_id)
+ select i_item_id ,sum(total_sales) total_sales
+ from (select * from ss
+ union all
+ select * from cs
+ union all
+ select * from ws) tmp1
+ group by i_item_id
+ order by total_sales, i_item_id
+ limit 100;
+---- RESULTS
+'AAAAAAAACENDAAAA',0.00
+'AAAAAAAAEPDEAAAA',0.00
+'AAAAAAAAIGBEAAAA',0.00
+'AAAAAAAAFBGBAAAA',3.52
+'AAAAAAAAINHBAAAA',7.28
+'AAAAAAAAMBGAAAAA',8.52
+'AAAAAAAAEOIDAAAA',14.02
+'AAAAAAAAALIBAAAA',48.62
+'AAAAAAAAEDDAAAAA',48.80
+'AAAAAAAAHOBDAAAA',49.50
+'AAAAAAAAMGCCAAAA',51.84
+'AAAAAAAAGBEAAAAA',54.53
+'AAAAAAAACKAEAAAA',66.30
+'AAAAAAAAKHOAAAAA',72.72
+'AAAAAAAANFPBAAAA',74.48
+'AAAAAAAAOFMBAAAA',83.22
+'AAAAAAAAKHMCAAAA',105.44
+'AAAAAAAAFOCEAAAA',105.98
+'AAAAAAAAENCBAAAA',109.20
+'AAAAAAAANGIBAAAA',111.00
+'AAAAAAAADGDEAAAA',121.74
+'AAAAAAAAEPADAAAA',126.08
+'AAAAAAAAINHAAAAA',127.92
+'AAAAAAAAHKJCAAAA',129.84
+'AAAAAAAAOLFBAAAA',132.16
+'AAAAAAAAPLEBAAAA',135.34
+'AAAAAAAAOHKDAAAA',136.36
+'AAAAAAAAEHOAAAAA',153.54
+'AAAAAAAAGJABAAAA',172.50
+'AAAAAAAAOCCBAAAA',200.93
+'AAAAAAAAGGFAAAAA',235.20
+'AAAAAAAAKIKBAAAA',236.95
+'AAAAAAAAMIOBAAAA',238.92
+'AAAAAAAAIPODAAAA',240.96
+'AAAAAAAACPDCAAAA',265.33
+'AAAAAAAAMKCEAAAA',268.37
+'AAAAAAAAKMPAAAAA',283.53
+'AAAAAAAACIBAAAAA',297.76
+'AAAAAAAAEIACAAAA',332.80
+'AAAAAAAAOGEEAAAA',339.65
+'AAAAAAAAMFMDAAAA',351.12
+'AAAAAAAAGHBCAAAA',359.90
+'AAAAAAAAIGDCAAAA',371.79
+'AAAAAAAACHLCAAAA',410.56
+'AAAAAAAAGMBDAAAA',418.46
+'AAAAAAAAIJMCAAAA',422.10
+'AAAAAAAAEJLBAAAA',442.50
+'AAAAAAAANDHCAAAA',460.07
+'AAAAAAAAAFNBAAAA',460.32
+'AAAAAAAAKDGDAAAA',479.88
+'AAAAAAAAKKNCAAAA',494.48
+'AAAAAAAAJAJBAAAA',518.30
+'AAAAAAAACCOBAAAA',522.92
+'AAAAAAAAAEKAAAAA',525.52
+'AAAAAAAAKJBDAAAA',527.15
+'AAAAAAAAGHEDAAAA',538.85
+'AAAAAAAAACGCAAAA',562.68
+'AAAAAAAAAEGAAAAA',572.32
+'AAAAAAAAEKLBAAAA',572.32
+'AAAAAAAAAHCBAAAA',604.38
+'AAAAAAAALFADAAAA',606.67
+'AAAAAAAAKFNDAAAA',617.96
+'AAAAAAAAOEKAAAAA',619.39
+'AAAAAAAAEEBEAAAA',626.40
+'AAAAAAAAKMBCAAAA',628.95
+'AAAAAAAAJHGDAAAA',631.81
+'AAAAAAAAOMLDAAAA',631.89
+'AAAAAAAAGNDDAAAA',645.99
+'AAAAAAAAEADAAAAA',648.20
+'AAAAAAAAKPKCAAAA',651.42
+'AAAAAAAAAKHCAAAA',657.04
+'AAAAAAAAOEIDAAAA',660.24
+'AAAAAAAAMKAEAAAA',691.02
+'AAAAAAAABLKAAAAA',691.26
+'AAAAAAAADEIBAAAA',726.72
+'AAAAAAAAKBHCAAAA',730.20
+'AAAAAAAADNJAAAAA',731.92
+'AAAAAAAAOFPBAAAA',737.28
+'AAAAAAAACPIBAAAA',737.64
+'AAAAAAAAEPPBAAAA',759.36
+'AAAAAAAALNHDAAAA',761.60
+'AAAAAAAAGKPDAAAA',773.56
+'AAAAAAAAKBCAAAAA',775.30
+'AAAAAAAAIBOCAAAA',777.48
+'AAAAAAAAOJBEAAAA',777.84
+'AAAAAAAAKFKBAAAA',780.41
+'AAAAAAAAPJCCAAAA',783.00
+'AAAAAAAACAKBAAAA',800.27
+'AAAAAAAAOFNDAAAA',806.19
+'AAAAAAAAGLIDAAAA',843.75
+'AAAAAAAAGDBAAAAA',868.77
+'AAAAAAAAOGFDAAAA',887.30
+'AAAAAAAACOLBAAAA',918.96
+'AAAAAAAAFFNCAAAA',923.35
+'AAAAAAAACCJDAAAA',955.08
+'AAAAAAAAMMCAAAAA',959.76
+'AAAAAAAACCPDAAAA',971.77
+'AAAAAAAAKNCEAAAA',998.60
+'AAAAAAAACNACAAAA',1010.70
+'AAAAAAAAKDEAAAAA',1019.52
+---- TYPES
+STRING, DECIMAL
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q6.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q6.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q6.test
new file mode 100644
index 0000000..11b5ec7
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q6.test
@@ -0,0 +1,75 @@
+====
+---- QUERY: TPCDS-Q6
+select a.ca_state state, count(*) cnt
+from customer_address a,
+ customer c,
+ store_sales s,
+ date_dim d,
+ item i
+where a.ca_address_sk = c.c_current_addr_sk
+ and c.c_customer_sk = s.ss_customer_sk
+ and s.ss_sold_date_sk = d.d_date_sk
+ and s.ss_item_sk = i.i_item_sk
+ and d.d_month_seq =
+ (select distinct (d_month_seq)
+ from date_dim
+ where d_year = 2001
+ and d_moy = 1
+ limit 1)
+ and i.i_current_price > 1.2 *
+ (select avg(j.i_current_price)
+ from item j
+ where j.i_category = i.i_category)
+group by a.ca_state
+having count(*) >= 10
+order by 2,1
+limit 100
+---- RESULTS: VERIFY_IS_EQUAL_SORTED
+'VT',14
+'ME',16
+'NJ',18
+'MD',19
+'AK',21
+'NH',21
+'MA',24
+'NV',26
+'OR',27
+'ND',28
+'UT',31
+'SD',35
+'ID',36
+'NM',36
+'WV',45
+'WA',46
+'SC',47
+'CO',48
+'AL',49
+'FL',49
+'LA',49
+'NY',52
+'WI',53
+'MT',55
+'PA',56
+'OK',57
+'CA',60
+'MS',69
+'NULL',73
+'AR',73
+'TN',74
+'IN',76
+'KS',80
+'MN',81
+'MI',83
+'MO',83
+'NE',87
+'IA',96
+'OH',102
+'KY',103
+'IL',106
+'NC',107
+'VA',137
+'GA',153
+'TX',228
+---- TYPES
+STRING, BIGINT
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q60.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q60.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q60.test
new file mode 100644
index 0000000..4f1059a
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q60.test
@@ -0,0 +1,181 @@
+====
+---- QUERY: TPCDS-Q60
+with ss as (
+ select
+ i_item_id,sum(ss_ext_sales_price) total_sales
+ from
+ store_sales,
+ date_dim,
+ customer_address,
+ item
+ where
+ i_item_id in (select
+ i_item_id
+from
+ item
+where i_category in ('Music'))
+ and ss_item_sk = i_item_sk
+ and ss_sold_date_sk = d_date_sk
+ and d_year = 1998
+ and d_moy = 9
+ and ss_addr_sk = ca_address_sk
+ and ca_gmt_offset = -5
+ group by i_item_id),
+ cs as (
+ select
+ i_item_id,sum(cs_ext_sales_price) total_sales
+ from
+ catalog_sales,
+ date_dim,
+ customer_address,
+ item
+ where
+ i_item_id in (select
+ i_item_id
+from
+ item
+where i_category in ('Music'))
+ and cs_item_sk = i_item_sk
+ and cs_sold_date_sk = d_date_sk
+ and d_year = 1998
+ and d_moy = 9
+ and cs_bill_addr_sk = ca_address_sk
+ and ca_gmt_offset = -5
+ group by i_item_id),
+ ws as (
+ select
+ i_item_id,sum(ws_ext_sales_price) total_sales
+ from
+ web_sales,
+ date_dim,
+ customer_address,
+ item
+ where
+ i_item_id in (select
+ i_item_id
+from
+ item
+where i_category in ('Music'))
+ and ws_item_sk = i_item_sk
+ and ws_sold_date_sk = d_date_sk
+ and d_year = 1998
+ and d_moy = 9
+ and ws_bill_addr_sk = ca_address_sk
+ and ca_gmt_offset = -5
+ group by i_item_id)
+ select
+ i_item_id
+,sum(total_sales) total_sales
+ from (select * from ss
+ union all
+ select * from cs
+ union all
+ select * from ws) tmp1
+ group by i_item_id
+ order by i_item_id
+ ,total_sales
+ limit 100;
+---- RESULTS
+'AAAAAAAAAABBAAAA',6287.84
+'AAAAAAAAAABEAAAA',1297.26
+'AAAAAAAAAADCAAAA',8936.88
+'AAAAAAAAAADEAAAA',20945.84
+'AAAAAAAAAAHAAAAA',7227.38
+'AAAAAAAAAANDAAAA',17117.56
+'AAAAAAAAAAOCAAAA',4287.89
+'AAAAAAAAABADAAAA',499.72
+'AAAAAAAAABBCAAAA',22704.66
+'AAAAAAAAABCEAAAA',22499.76
+'AAAAAAAAABDEAAAA',5527.74
+'AAAAAAAAABFBAAAA',4953.67
+'AAAAAAAAABFEAAAA',12523.22
+'AAAAAAAAABHCAAAA',20093.49
+'AAAAAAAAABIBAAAA',382.30
+'AAAAAAAAABICAAAA',1271.62
+'AAAAAAAAABKCAAAA',7355.85
+'AAAAAAAAABLBAAAA',786.05
+'AAAAAAAAABNAAAAA',8385.94
+'AAAAAAAAACACAAAA',11564.85
+'AAAAAAAAACBEAAAA',13046.23
+'AAAAAAAAACCDAAAA',14533.50
+'AAAAAAAAACDCAAAA',820.49
+'AAAAAAAAACLBAAAA',6761.17
+'AAAAAAAAACNCAAAA',19023.15
+'AAAAAAAAACODAAAA',7322.65
+'AAAAAAAAADACAAAA',4696.41
+'AAAAAAAAADBAAAAA',988.80
+'AAAAAAAAADDCAAAA',5661.21
+'AAAAAAAAADDEAAAA',1846.56
+'AAAAAAAAADEEAAAA',15327.48
+'AAAAAAAAADGEAAAA',4511.21
+'AAAAAAAAADHDAAAA',27866.80
+'AAAAAAAAADICAAAA',15624.49
+'AAAAAAAAADLCAAAA',399.00
+'AAAAAAAAADNDAAAA',6403.89
+'AAAAAAAAADPCAAAA',4246.28
+'AAAAAAAAAEBAAAAA',1946.34
+'AAAAAAAAAEBCAAAA',2021.69
+'AAAAAAAAAEBDAAAA',6664.64
+'AAAAAAAAAECCAAAA',11363.72
+'AAAAAAAAAECEAAAA',10198.96
+'AAAAAAAAAEFCAAAA',62.04
+'AAAAAAAAAEGDAAAA',9547.35
+'AAAAAAAAAEIBAAAA',3804.21
+'AAAAAAAAAEICAAAA',7097.51
+'AAAAAAAAAEKCAAAA',20954.40
+'AAAAAAAAAEPDAAAA',2162.31
+'AAAAAAAAAFBEAAAA',10916.01
+'AAAAAAAAAFCDAAAA',14657.18
+'AAAAAAAAAFDCAAAA',794.73
+'AAAAAAAAAFFAAAAA',9979.09
+'AAAAAAAAAFHCAAAA',2060.61
+'AAAAAAAAAFIAAAAA',705.60
+'AAAAAAAAAFJCAAAA',13594.18
+'AAAAAAAAAFMDAAAA',2354.86
+'AAAAAAAAAFNBAAAA',17475.06
+'AAAAAAAAAGBBAAAA',2052.41
+'AAAAAAAAAGCCAAAA',8040.70
+'AAAAAAAAAGCDAAAA',16455.28
+'AAAAAAAAAGDCAAAA',2495.73
+'AAAAAAAAAGDEAAAA',6750.35
+'AAAAAAAAAGFAAAAA',5985.82
+'AAAAAAAAAGHAAAAA',17532.15
+'AAAAAAAAAGHDAAAA',1220.67
+'AAAAAAAAAGJCAAAA',7564.93
+'AAAAAAAAAGKAAAAA',14082.90
+'AAAAAAAAAGKDAAAA',1891.00
+'AAAAAAAAAGNBAAAA',7856.78
+'AAAAAAAAAGOCAAAA',14920.31
+'AAAAAAAAAGPCAAAA',7986.28
+'AAAAAAAAAHCBAAAA',15074.94
+'AAAAAAAAAHECAAAA',11667.69
+'AAAAAAAAAHHDAAAA',2491.48
+'AAAAAAAAAHJDAAAA',4056.95
+'AAAAAAAAAHMAAAAA',15866.43
+'AAAAAAAAAHNDAAAA',7289.53
+'AAAAAAAAAIACAAAA',2603.30
+'AAAAAAAAAIBCAAAA',9.09
+'AAAAAAAAAIBEAAAA',6979.53
+'AAAAAAAAAIDAAAAA',32.70
+'AAAAAAAAAIDCAAAA',15307.29
+'AAAAAAAAAIIAAAAA',11268.14
+'AAAAAAAAAIIDAAAA',6837.36
+'AAAAAAAAAIJCAAAA',28318.16
+'AAAAAAAAAIMAAAAA',4013.06
+'AAAAAAAAAINBAAAA',7691.92
+'AAAAAAAAAINCAAAA',13190.42
+'AAAAAAAAAIODAAAA',17489.85
+'AAAAAAAAAIPDAAAA',6096.92
+'AAAAAAAAAJAEAAAA',30169.62
+'AAAAAAAAAJCCAAAA',17608.54
+'AAAAAAAAAJGBAAAA',14.10
+'AAAAAAAAAJJCAAAA',2763.55
+'AAAAAAAAAJMBAAAA',1013.43
+'AAAAAAAAAJMCAAAA',12220.00
+'AAAAAAAAAJNBAAAA',8076.70
+'AAAAAAAAAJODAAAA',5113.02
+'AAAAAAAAAKBAAAAA',2413.44
+'AAAAAAAAAKDAAAAA',15712.05
+---- TYPES
+STRING, DECIMAL
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q61.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q61.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q61.test
new file mode 100644
index 0000000..e8c2213
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q61.test
@@ -0,0 +1,49 @@
+====
+---- QUERY: TPCDS-Q61
+-- RESULT MISMATCH FROM ORIGINAL in DECIMAL value
+-- FIXED. CAST RESULT QUOTIENT TO DECIMAL(15, 4), TAKE ACTUAL RESULT AS EXPECTED
+select promotions,total,cast(promotions*100.00/total as decimal(15,4))
+from
+ (select sum(ss_ext_sales_price) promotions
+ from store_sales
+ ,store
+ ,promotion
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_promo_sk = p_promo_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -5
+ and i_category = 'Jewelry'
+ and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
+ and s_gmt_offset = -5
+ and d_year = 1998
+ and d_moy = 1) promotional_sales,
+ (select sum(ss_ext_sales_price) total
+ from store_sales
+ ,store
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -5
+ and i_category = 'Jewelry'
+ and s_gmt_offset = -5
+ and d_year = 1998
+ and d_moy = 1) all_sales
+order by promotions, total
+---- RESULTS
+969445.96,1772143.56,54.7047
+---- TYPES
+DECIMAL, DECIMAL, DECIMAL
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q62.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q62.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q62.test
new file mode 100644
index 0000000..bd2efec
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q62.test
@@ -0,0 +1,138 @@
+====
+---- QUERY: TPCDS-Q62
+select
+ substr(w_warehouse_name,1,20)
+ ,sm_type
+ ,web_name
+ ,sum(case when (ws_ship_date_sk - ws_sold_date_sk <= 30 ) then 1 else 0 end) as "30 days"
+ ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 30) and
+ (ws_ship_date_sk - ws_sold_date_sk <= 60) then 1 else 0 end ) as "31-60 days"
+ ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 60) and
+ (ws_ship_date_sk - ws_sold_date_sk <= 90) then 1 else 0 end) as "61-90 days"
+ ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 90) and
+ (ws_ship_date_sk - ws_sold_date_sk <= 120) then 1 else 0 end) as "91-120 days"
+ ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 120) then 1 else 0 end) as ">120 days"
+from
+ web_sales
+ ,warehouse
+ ,ship_mode
+ ,web_site
+ ,date_dim
+where
+ d_month_seq between 1200 and 1200 + 11
+and ws_ship_date_sk = d_date_sk
+and ws_warehouse_sk = w_warehouse_sk
+and ws_ship_mode_sk = sm_ship_mode_sk
+and ws_web_site_sk = web_site_sk
+group by
+ substr(w_warehouse_name,1,20)
+ ,sm_type
+ ,web_name
+order by substr(w_warehouse_name,1,20)
+ ,sm_type
+ ,web_name
+limit 100;
+---- RESULTS
+'Bad cards must make.','EXPRESS','site_0',251,248,268,277,0
+'Bad cards must make.','EXPRESS','site_1',262,304,290,267,0
+'Bad cards must make.','EXPRESS','site_2',287,290,293,286,0
+'Bad cards must make.','EXPRESS','site_3',283,260,267,290,0
+'Bad cards must make.','EXPRESS','site_4',281,310,309,321,0
+'Bad cards must make.','LIBRARY','site_0',254,265,226,231,0
+'Bad cards must make.','LIBRARY','site_1',198,231,211,201,0
+'Bad cards must make.','LIBRARY','site_2',181,172,190,176,0
+'Bad cards must make.','LIBRARY','site_3',196,214,173,226,0
+'Bad cards must make.','LIBRARY','site_4',241,241,247,236,0
+'Bad cards must make.','NEXT DAY','site_0',259,255,279,260,0
+'Bad cards must make.','NEXT DAY','site_1',287,334,295,300,0
+'Bad cards must make.','NEXT DAY','site_2',269,309,306,272,0
+'Bad cards must make.','NEXT DAY','site_3',281,322,308,277,0
+'Bad cards must make.','NEXT DAY','site_4',273,295,275,281,0
+'Bad cards must make.','OVERNIGHT','site_0',221,216,201,213,0
+'Bad cards must make.','OVERNIGHT','site_1',250,228,257,235,0
+'Bad cards must make.','OVERNIGHT','site_2',255,223,230,238,0
+'Bad cards must make.','OVERNIGHT','site_3',195,217,199,211,0
+'Bad cards must make.','OVERNIGHT','site_4',175,165,169,164,0
+'Bad cards must make.','REGULAR','site_0',176,239,220,233,0
+'Bad cards must make.','REGULAR','site_1',187,163,200,198,0
+'Bad cards must make.','REGULAR','site_2',190,231,195,208,0
+'Bad cards must make.','REGULAR','site_3',227,241,231,252,0
+'Bad cards must make.','REGULAR','site_4',258,245,260,245,0
+'Bad cards must make.','TWO DAY','site_0',222,235,235,223,0
+'Bad cards must make.','TWO DAY','site_1',264,248,237,227,0
+'Bad cards must make.','TWO DAY','site_2',181,230,231,207,0
+'Bad cards must make.','TWO DAY','site_3',182,158,209,185,0
+'Bad cards must make.','TWO DAY','site_4',203,218,198,221,0
+'Conventional childr','EXPRESS','site_0',311,308,336,287,0
+'Conventional childr','EXPRESS','site_1',250,269,273,260,0
+'Conventional childr','EXPRESS','site_2',255,280,235,276,0
+'Conventional childr','EXPRESS','site_3',269,292,303,278,0
+'Conventional childr','EXPRESS','site_4',275,324,268,330,0
+'Conventional childr','LIBRARY','site_0',212,211,225,250,0
+'Conventional childr','LIBRARY','site_1',218,249,244,204,0
+'Conventional childr','LIBRARY','site_2',203,215,222,205,0
+'Conventional childr','LIBRARY','site_3',163,181,180,193,0
+'Conventional childr','LIBRARY','site_4',189,208,207,191,0
+'Conventional childr','NEXT DAY','site_0',253,269,285,258,0
+'Conventional childr','NEXT DAY','site_1',289,244,254,269,0
+'Conventional childr','NEXT DAY','site_2',341,290,310,315,0
+'Conventional childr','NEXT DAY','site_3',309,294,328,270,0
+'Conventional childr','NEXT DAY','site_4',312,311,275,291,0
+'Conventional childr','OVERNIGHT','site_0',180,193,167,211,0
+'Conventional childr','OVERNIGHT','site_1',196,210,238,194,0
+'Conventional childr','OVERNIGHT','site_2',218,236,287,220,0
+'Conventional childr','OVERNIGHT','site_3',235,253,228,246,0
+'Conventional childr','OVERNIGHT','site_4',211,199,201,201,0
+'Conventional childr','REGULAR','site_0',209,262,232,207,0
+'Conventional childr','REGULAR','site_1',214,218,222,227,0
+'Conventional childr','REGULAR','site_2',166,193,184,183,0
+'Conventional childr','REGULAR','site_3',195,191,221,219,0
+'Conventional childr','REGULAR','site_4',248,236,244,256,0
+'Conventional childr','TWO DAY','site_0',192,225,232,225,0
+'Conventional childr','TWO DAY','site_1',248,239,233,251,0
+'Conventional childr','TWO DAY','site_2',230,214,237,201,0
+'Conventional childr','TWO DAY','site_3',236,190,190,222,0
+'Conventional childr','TWO DAY','site_4',197,159,202,176,0
+'Doors canno','EXPRESS','site_0',303,312,304,319,0
+'Doors canno','EXPRESS','site_1',321,278,313,297,0
+'Doors canno','EXPRESS','site_2',267,277,265,257,0
+'Doors canno','EXPRESS','site_3',263,245,258,285,0
+'Doors canno','EXPRESS','site_4',282,313,319,323,0
+'Doors canno','LIBRARY','site_0',201,210,201,200,0
+'Doors canno','LIBRARY','site_1',239,217,208,206,0
+'Doors canno','LIBRARY','site_2',229,233,244,241,0
+'Doors canno','LIBRARY','site_3',216,198,182,222,0
+'Doors canno','LIBRARY','site_4',176,163,170,216,0
+'Doors canno','NEXT DAY','site_0',289,287,289,311,0
+'Doors canno','NEXT DAY','site_1',253,293,256,274,0
+'Doors canno','NEXT DAY','site_2',275,242,265,261,0
+'Doors canno','NEXT DAY','site_3',347,299,296,278,0
+'Doors canno','NEXT DAY','site_4',280,300,325,334,0
+'Doors canno','OVERNIGHT','site_0',235,232,202,221,0
+'Doors canno','OVERNIGHT','site_1',189,177,179,174,0
+'Doors canno','OVERNIGHT','site_2',230,186,228,207,0
+'Doors canno','OVERNIGHT','site_3',231,251,228,242,0
+'Doors canno','OVERNIGHT','site_4',226,247,237,227,0
+'Doors canno','REGULAR','site_0',235,224,237,214,0
+'Doors canno','REGULAR','site_1',203,252,226,247,0
+'Doors canno','REGULAR','site_2',227,193,204,222,0
+'Doors canno','REGULAR','site_3',157,176,169,189,0
+'Doors canno','REGULAR','site_4',232,200,217,199,0
+'Doors canno','TWO DAY','site_0',190,165,199,200,0
+'Doors canno','TWO DAY','site_1',226,224,209,196,0
+'Doors canno','TWO DAY','site_2',229,236,220,235,0
+'Doors canno','TWO DAY','site_3',242,197,248,238,0
+'Doors canno','TWO DAY','site_4',204,213,230,221,0
+'Important issues liv','EXPRESS','site_0',240,250,239,260,0
+'Important issues liv','EXPRESS','site_1',291,287,283,311,0
+'Important issues liv','EXPRESS','site_2',274,312,300,293,0
+'Important issues liv','EXPRESS','site_3',282,298,307,285,0
+'Important issues liv','EXPRESS','site_4',285,271,268,290,0
+'Important issues liv','LIBRARY','site_0',218,230,209,201,0
+'Important issues liv','LIBRARY','site_1',165,160,153,182,0
+'Important issues liv','LIBRARY','site_2',213,209,234,222,0
+'Important issues liv','LIBRARY','site_3',237,258,219,238,0
+'Important issues liv','LIBRARY','site_4',224,243,277,237,0
+---- TYPES
+STRING, STRING, STRING, BIGINT, BIGINT, BIGINT, BIGINT, BIGINT
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/impala/blob/35a3e186/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q64.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q64.test b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q64.test
new file mode 100644
index 0000000..32d6779
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-decimal_v2-q64.test
@@ -0,0 +1,144 @@
+====
+---- QUERY: TPCDS-Q64
+-- RESULT MISMATCH FROM ORIGINAL
+-- ADDED ORDER BY COLUMNS.
+with cs_ui as
+ (select cs_item_sk
+ ,sum(cs_ext_list_price) as sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
+ from catalog_sales
+ ,catalog_returns
+ where cs_item_sk = cr_item_sk
+ and cs_order_number = cr_order_number
+ group by cs_item_sk
+ having sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
+cross_sales as
+ (select i_product_name product_name
+ ,i_item_sk item_sk
+ ,s_store_name store_name
+ ,s_zip store_zip
+ ,ad1.ca_street_number b_street_number
+ ,ad1.ca_street_name b_street_name
+ ,ad1.ca_city b_city
+ ,ad1.ca_zip b_zip
+ ,ad2.ca_street_number c_street_number
+ ,ad2.ca_street_name c_street_name
+ ,ad2.ca_city c_city
+ ,ad2.ca_zip c_zip
+ ,d1.d_year as syear
+ ,d2.d_year as fsyear
+ ,d3.d_year s2year
+ ,count(*) cnt
+ ,sum(ss_wholesale_cost) s1
+ ,sum(ss_list_price) s2
+ ,sum(ss_coupon_amt) s3
+ FROM store_sales
+ ,store_returns
+ ,cs_ui
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,customer
+ ,customer_demographics cd1
+ ,customer_demographics cd2
+ ,promotion
+ ,household_demographics hd1
+ ,household_demographics hd2
+ ,customer_address ad1
+ ,customer_address ad2
+ ,income_band ib1
+ ,income_band ib2
+ ,item
+ WHERE ss_store_sk = s_store_sk AND
+ ss_sold_date_sk = d1.d_date_sk AND
+ ss_customer_sk = c_customer_sk AND
+ ss_cdemo_sk= cd1.cd_demo_sk AND
+ ss_hdemo_sk = hd1.hd_demo_sk AND
+ ss_addr_sk = ad1.ca_address_sk and
+ ss_item_sk = i_item_sk and
+ ss_item_sk = sr_item_sk and
+ ss_ticket_number = sr_ticket_number and
+ ss_item_sk = cs_ui.cs_item_sk and
+ c_current_cdemo_sk = cd2.cd_demo_sk AND
+ c_current_hdemo_sk = hd2.hd_demo_sk AND
+ c_current_addr_sk = ad2.ca_address_sk and
+ c_first_sales_date_sk = d2.d_date_sk and
+ c_first_shipto_date_sk = d3.d_date_sk and
+ ss_promo_sk = p_promo_sk and
+ hd1.hd_income_band_sk = ib1.ib_income_band_sk and
+ hd2.hd_income_band_sk = ib2.ib_income_band_sk and
+ cd1.cd_marital_status <> cd2.cd_marital_status and
+ i_color in ('purple','burlywood','indian','spring','floral','medium') and
+ i_current_price between 64 and 64 + 10 and
+ i_current_price between 64 + 1 and 64 + 15
+group by i_product_name
+ ,i_item_sk
+ ,s_store_name
+ ,s_zip
+ ,ad1.ca_street_number
+ ,ad1.ca_street_name
+ ,ad1.ca_city
+ ,ad1.ca_zip
+ ,ad2.ca_street_number
+ ,ad2.ca_street_name
+ ,ad2.ca_city
+ ,ad2.ca_zip
+ ,d1.d_year
+ ,d2.d_year
+ ,d3.d_year
+)
+select cs1.product_name
+ ,cs1.store_name
+ ,cs1.store_zip
+ ,cs1.b_street_number
+ ,cs1.b_street_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 as s11
+ ,cs1.s2 as s21
+ ,cs1.s3 as s31
+ ,cs2.s1 as s12
+ ,cs2.s2 as s22
+ ,cs2.s3 as s32
+ ,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
+order by cs1.product_name
+ ,cs1.store_name
+ ,cs2.cnt
+ -- Added to fix result ordering
+ ,b_street_number desc
+ ,b_street_name desc
+ ,b_zip desc
+ ,s11 desc
+ ,s21 desc
+ ,s31 desc
+ ,s12 desc
+ ,s22 desc
+ ,s32 desc;
+---- RESULTS
+'n stableableantiought','able','31904','987','Hillcrest ','Fairbanks','46653','216','3rd ','Reno','40344',1999,1,55.42,80.91,954.53,59.08,76.80,0.00,2000,1
+'n stableableantiought','ation','31904','425','Green ','Enterprise','11757','772','Valley ','NULL','NULL',1999,1,16.24,25.17,0.00,44.27,58.43,0.00,2000,1
+'n stableableantiought','ation','31904','425','Green ','Enterprise','11757','772','Valley ','NULL','NULL',1999,1,16.24,25.17,0.00,7.92,11.80,0.00,2000,1
+'n stableableantiought','ation','31904','316','Valley Tenth','Pine Grove','74593','806','Wilson Main','Jackson','59583',1999,1,49.63,78.41,14.22,44.27,58.43,0.00,2000,1
+'n stableableantiought','ation','31904','316','Valley Tenth','Pine Grove','74593','806','Wilson Main','Jackson','59583',1999,1,49.63,78.41,14.22,7.92,11.80,0.00,2000,1
+'n stableableantiought','ation','31904','173','Park Maple','Sulphur Springs','68354','232','Franklin ','Fairfield','66192',1999,1,60.78,85.09,0.00,44.27,58.43,0.00,2000,1
+'n stableableantiought','ation','31904','173','Park Maple','Sulphur Springs','68354','232','Franklin ','Fairfield','66192',1999,1,60.78,85.09,0.00,7.92,11.80,0.00,2000,1
+'n stableableantiought','eing','35709','928','First Oak','Summit','40499','178','Johnson Hillcrest','Oakdale','59584',1999,1,63.82,121.25,0.00,58.19,85.53,0.00,2000,1
+'n stableableantiought','eing','35709','928','First Oak','Summit','40499','178','Johnson Hillcrest','Oakdale','59584',1999,1,63.82,121.25,0.00,30.28,49.65,0.00,2000,1
+'n stableableantiought','eing','35709','928','First Oak','Summit','40499','178','Johnson Hillcrest','Oakdale','59584',1999,1,63.82,121.25,0.00,10.25,12.09,0.00,2000,1
+---- TYPES
+STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, INT, BIGINT, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, INT, BIGINT
+====
\ No newline at end of file