Changeset: 644648c07573 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/644648c07573 Modified Files: sql/benchmarks/tpcds/Tests/one.test.in testing/sqllogictest.py Branch: Jul2021 Log Message:
Recreated tpcds test set from original templates. This version has all queries (even when there are multiple queries in a single file: they are supposed to be there since the second one is a refinement of the first), and also the alternative versions of queries. All queries are annotated with their number. Some minor tweaking had to be done here and there to make the query acceptable to MonetDB and to get the right kind of precision. This is usually indicated with a comment. Two queries currently fail: query 5 and query 70. The alternative versions (5a and 70a) work correctly. Query 5 and 5a give the same result it the "limit 100" clause is removed, and 5a with limit correctly gives the first 100 rows of the full result. Query 70 produces an incorrect value in the first column for the last two rows (it should be the same as the value in row 1). diffs (truncated from 12423 to 300 lines): diff --git a/sql/benchmarks/tpcds/Tests/one.test.in b/sql/benchmarks/tpcds/Tests/one.test.in --- a/sql/benchmarks/tpcds/Tests/one.test.in +++ b/sql/benchmarks/tpcds/Tests/one.test.in @@ -1366,957 +1366,721 @@ statement ok rollback query T rowsort -WITH customer_total_return AS - (SELECT sr_customer_sk AS ctr_customer_sk, - sr_store_sk AS ctr_store_sk, - sum(sr_return_amt) AS ctr_total_return - FROM store_returns, - date_dim - WHERE sr_returned_date_sk = d_date_sk - AND d_year = 2000 - GROUP BY sr_customer_sk, - sr_store_sk) -SELECT c_customer_id -FROM customer_total_return ctr1, - store, - customer -WHERE ctr1.ctr_total_return > - (SELECT avg(ctr_total_return)*1.2 - FROM customer_total_return ctr2 - WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk) - AND s_store_sk = ctr1.ctr_store_sk - AND s_state = 'TN' - AND ctr1.ctr_customer_sk = c_customer_sk -ORDER BY c_customer_id -LIMIT 100 +-- query 1 +with customer_total_return as +(select sr_customer_sk as ctr_customer_sk +,sr_store_sk as ctr_store_sk +,sum(SR_RETURN_AMT) as ctr_total_return +from store_returns +,date_dim +where sr_returned_date_sk = d_date_sk +and d_year =2000 +group by sr_customer_sk +,sr_store_sk) + select c_customer_id +from customer_total_return ctr1 +,store +,customer +where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 +from customer_total_return ctr2 +where ctr1.ctr_store_sk = ctr2.ctr_store_sk) +and s_store_sk = ctr1.ctr_store_sk +and s_state = 'TN' +and ctr1.ctr_customer_sk = c_customer_sk +order by c_customer_id +limit 100 ---- 100 values hashing to 9ace740d2b6b717b43d7cdee1ef41373 query IRRRRRRR rowsort -WITH wscs AS - (SELECT sold_date_sk, - sales_price - FROM - (SELECT ws_sold_date_sk sold_date_sk, - ws_ext_sales_price sales_price - FROM web_sales - UNION ALL SELECT cs_sold_date_sk sold_date_sk, - cs_ext_sales_price sales_price - FROM catalog_sales) sq1), - wswscs AS - (SELECT d_week_seq, - sum(CASE - WHEN (d_day_name='Sunday') THEN sales_price - ELSE NULL - END) sun_sales, - sum(CASE - WHEN (d_day_name='Monday') THEN sales_price - ELSE NULL - END) mon_sales, - sum(CASE - WHEN (d_day_name='Tuesday') THEN sales_price - ELSE NULL - END) tue_sales, - sum(CASE - WHEN (d_day_name='Wednesday') THEN sales_price - ELSE NULL - END) wed_sales, - sum(CASE - WHEN (d_day_name='Thursday') THEN sales_price - ELSE NULL - END) thu_sales, - sum(CASE - WHEN (d_day_name='Friday') THEN sales_price - ELSE NULL - END) fri_sales, - sum(CASE - WHEN (d_day_name='Saturday') THEN sales_price - ELSE NULL - END) sat_sales - FROM wscs, - date_dim - WHERE d_date_sk = sold_date_sk - GROUP BY d_week_seq) -SELECT d_week_seq1, - round(sun_sales1/sun_sales2, 2), - round(mon_sales1/mon_sales2, 2), - round(tue_sales1/tue_sales2, 2), - round(wed_sales1/wed_sales2, 2), - round(thu_sales1/thu_sales2, 2), - round(fri_sales1/fri_sales2, 2), - round(sat_sales1/sat_sales2, 2) -FROM - (SELECT wswscs.d_week_seq d_week_seq1, - sun_sales sun_sales1, - mon_sales mon_sales1, - tue_sales tue_sales1, - wed_sales wed_sales1, - thu_sales thu_sales1, - fri_sales fri_sales1, - sat_sales sat_sales1 - FROM wswscs, - date_dim - WHERE date_dim.d_week_seq = wswscs.d_week_seq - AND d_year = 2001) y, - (SELECT wswscs.d_week_seq d_week_seq2, - sun_sales sun_sales2, - mon_sales mon_sales2, - tue_sales tue_sales2, - wed_sales wed_sales2, - thu_sales thu_sales2, - fri_sales fri_sales2, - sat_sales sat_sales2 - FROM wswscs, - date_dim - WHERE date_dim.d_week_seq = wswscs.d_week_seq - AND d_year = 2001+1) z -WHERE d_week_seq1 = d_week_seq2-53 -ORDER BY d_week_seq1 +-- query 2 +with wscs as + (select sold_date_sk + ,sales_price + from (select ws_sold_date_sk sold_date_sk + ,ws_ext_sales_price sales_price + from web_sales + union all + select cs_sold_date_sk sold_date_sk + ,cs_ext_sales_price sales_price + from catalog_sales) sq1), + wswscs as + (select d_week_seq, + sum(case when (d_day_name='Sunday') then sales_price else null end) sun_sales, + sum(case when (d_day_name='Monday') then sales_price else null end) mon_sales, + sum(case when (d_day_name='Tuesday') then sales_price else null end) tue_sales, + sum(case when (d_day_name='Wednesday') then sales_price else null end) wed_sales, + sum(case when (d_day_name='Thursday') then sales_price else null end) thu_sales, + sum(case when (d_day_name='Friday') then sales_price else null end) fri_sales, + sum(case when (d_day_name='Saturday') then sales_price else null end) sat_sales + from wscs + ,date_dim + where d_date_sk = sold_date_sk + group by d_week_seq) + select d_week_seq1 + ,round(sun_sales1/sun_sales2,2) + ,round(mon_sales1/mon_sales2,2) + ,round(tue_sales1/tue_sales2,2) + ,round(wed_sales1/wed_sales2,2) + ,round(thu_sales1/thu_sales2,2) + ,round(fri_sales1/fri_sales2,2) + ,round(sat_sales1/sat_sales2,2) + from + (select wswscs.d_week_seq d_week_seq1 + ,sun_sales sun_sales1 + ,mon_sales mon_sales1 + ,tue_sales tue_sales1 + ,wed_sales wed_sales1 + ,thu_sales thu_sales1 + ,fri_sales fri_sales1 + ,sat_sales sat_sales1 + from wswscs,date_dim + where date_dim.d_week_seq = wswscs.d_week_seq and + d_year = 2001) y, + (select wswscs.d_week_seq d_week_seq2 + ,sun_sales sun_sales2 + ,mon_sales mon_sales2 + ,tue_sales tue_sales2 + ,wed_sales wed_sales2 + ,thu_sales thu_sales2 + ,fri_sales fri_sales2 + ,sat_sales sat_sales2 + from wswscs + ,date_dim + where date_dim.d_week_seq = wswscs.d_week_seq and + d_year = 2001+1) z + where d_week_seq1=d_week_seq2-53 + order by d_week_seq1 ---- 20104 values hashing to d389792e8b24a6c5158cc3543fc1a10d query IITR rowsort -SELECT dt.d_year, - item.i_brand_id brand_id, - item.i_brand brand, - sum(ss_ext_sales_price) sum_agg -FROM date_dim dt, - store_sales, - item -WHERE dt.d_date_sk = store_sales.ss_sold_date_sk - AND store_sales.ss_item_sk = item.i_item_sk - AND item.i_manufact_id = 128 - AND dt.d_moy=11 -GROUP BY dt.d_year, - item.i_brand, - item.i_brand_id -ORDER BY dt.d_year, - sum_agg DESC, - brand_id -LIMIT 100 +-- query 3 +select dt.d_year + ,item.i_brand_id brand_id + ,item.i_brand brand + ,sum(ss_ext_sales_price) sum_agg + from date_dim dt + ,store_sales + ,item + where dt.d_date_sk = store_sales.ss_sold_date_sk + and store_sales.ss_item_sk = item.i_item_sk + and item.i_manufact_id = 128 + and dt.d_moy=11 + group by dt.d_year + ,item.i_brand + ,item.i_brand_id + order by dt.d_year + ,sum_agg desc + ,brand_id + limit 100 ---- 356 values hashing to f622c5c5ffff908edcd28dd716f580df query TTTT rowsort -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 _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
