Changeset: fa65eaf33a74 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/fa65eaf33a74 Added Files: sql/benchmarks/tpch/Tests/q13a.reqtests sql/benchmarks/tpch/Tests/q13a.test sql/benchmarks/tpch/Tests/q14a.reqtests sql/benchmarks/tpch/Tests/q14a.test sql/benchmarks/tpch/Tests/q15a.reqtests sql/benchmarks/tpch/Tests/q15a.test sql/benchmarks/tpch/Tests/q8a.reqtests sql/benchmarks/tpch/Tests/q8a.test Modified Files: sql/benchmarks/tpch/Tests/All Branch: Mar2025 Log Message:
Add the tpch alternative queries. 8a and 14a can't run as is since they use things like decode(substring(p_type from 1 for 5), 'PROMO', ...) diffs (254 lines): diff --git a/sql/benchmarks/tpch/Tests/All b/sql/benchmarks/tpch/Tests/All --- a/sql/benchmarks/tpch/Tests/All +++ b/sql/benchmarks/tpch/Tests/All @@ -9,13 +9,17 @@ q5 q6 q7 q8 +#q8a -- no function decode q9 q10 q11 q12 q13 +q13a q14 +#q14a -- no function decode q15 +q15a q16 q17 q18 diff --git a/sql/benchmarks/tpch/Tests/q13a.reqtests b/sql/benchmarks/tpch/Tests/q13a.reqtests new file mode 100644 --- /dev/null +++ b/sql/benchmarks/tpch/Tests/q13a.reqtests @@ -0,0 +1,1 @@ +load diff --git a/sql/benchmarks/tpch/Tests/q13a.test b/sql/benchmarks/tpch/Tests/q13a.test new file mode 100644 --- /dev/null +++ b/sql/benchmarks/tpch/Tests/q13a.test @@ -0,0 +1,91 @@ +#Q13a +query II rowsort +create view orders_per_cust0 (custkey, ordercount) as + select + c_custkey, + count(o_orderkey) + from + customer left outer join orders on + c_custkey = o_custkey + and o_comment not like '%special%requests%' + group by + c_custkey; +select + ordercount, + count(*) as custdist +from + orders_per_cust0 +group by + ordercount +order by + custdist desc, + ordercount desc; +drop view orders_per_cust0 +---- +0 +500 +1 +1 +10 +64 +11 +68 +12 +62 +13 +52 +14 +54 +15 +45 +16 +46 +17 +41 +18 +38 +19 +44 +2 +1 +20 +48 +21 +47 +22 +33 +23 +27 +24 +30 +25 +21 +26 +15 +27 +17 +28 +6 +29 +5 +3 +2 +30 +2 +31 +1 +32 +5 +4 +6 +5 +14 +6 +33 +7 +49 +8 +61 +9 +62 + diff --git a/sql/benchmarks/tpch/Tests/q14a.reqtests b/sql/benchmarks/tpch/Tests/q14a.reqtests new file mode 100644 --- /dev/null +++ b/sql/benchmarks/tpch/Tests/q14a.reqtests @@ -0,0 +1,1 @@ +load diff --git a/sql/benchmarks/tpch/Tests/q14a.test b/sql/benchmarks/tpch/Tests/q14a.test new file mode 100644 --- /dev/null +++ b/sql/benchmarks/tpch/Tests/q14a.test @@ -0,0 +1,16 @@ +#Q14a +query R rowsort +select + 100.00 * sum(decode(substring(p_type from 1 for 5), 'PROMO', + l_extendedprice * (1-l_discount), 0)) / + sum(l_extendedprice * (1-l_discount)) as promo_revenue +from + lineitem, + part +where + l_partkey = p_partkey + and l_shipdate >= date '1995-09-01' + and l_shipdate < date '1995-09-01' + interval '1' month +---- +15.487 + diff --git a/sql/benchmarks/tpch/Tests/q15a.reqtests b/sql/benchmarks/tpch/Tests/q15a.reqtests new file mode 100644 --- /dev/null +++ b/sql/benchmarks/tpch/Tests/q15a.reqtests @@ -0,0 +1,1 @@ +load diff --git a/sql/benchmarks/tpch/Tests/q15a.test b/sql/benchmarks/tpch/Tests/q15a.test new file mode 100644 --- /dev/null +++ b/sql/benchmarks/tpch/Tests/q15a.test @@ -0,0 +1,40 @@ +#Q15a +query ITTTR nosort +with revenue (supplier_no, total_revenue) as ( + select + l_suppkey, + sum(l_extendedprice * (1-l_discount)) + from + lineitem + where + l_shipdate >= date '1996-01-01' + and l_shipdate < date '1996-01-01' + interval '3' month + group by + l_suppkey +) +select + s_suppkey, + s_name, + s_address, + s_phone, + total_revenue +from + supplier, + revenue +where + s_suppkey = supplier_no + and total_revenue = ( + select + max(total_revenue) + from + revenue + ) +order by + s_suppkey +---- +21 +Supplier#000000021 +81CavellcrJ0PQ3CPBID0Z0JwyJm0ka5igEs +12-253-590-5816 +1161099.464 + diff --git a/sql/benchmarks/tpch/Tests/q8a.reqtests b/sql/benchmarks/tpch/Tests/q8a.reqtests new file mode 100644 --- /dev/null +++ b/sql/benchmarks/tpch/Tests/q8a.reqtests @@ -0,0 +1,1 @@ +load diff --git a/sql/benchmarks/tpch/Tests/q8a.test b/sql/benchmarks/tpch/Tests/q8a.test new file mode 100644 --- /dev/null +++ b/sql/benchmarks/tpch/Tests/q8a.test @@ -0,0 +1,42 @@ +#Q8a +query IR nosort +select + o_year, + sum(decode(nation, 'BRAZIL', volume, 0)) / sum(volume) as mkt_share +from + ( + select + extract(year from o_orderdate) as o_year, + l_extendedprice * (1-l_discount) as volume, + n2.n_name as nation + from + part, + supplier, + lineitem, + orders, + customer, + nation n1, + nation n2, + region + where + p_partkey = l_partkey + and s_suppkey = l_suppkey + and l_orderkey = o_orderkey + and o_custkey = c_custkey + and c_nationkey = n1.n_nationkey + and n1.n_regionkey = r_regionkey + and r_name = 'AMERICA' + and s_nationkey = n2.n_nationkey + and o_orderdate between date '1995-01-01' and date '1996-12-31' + and p_type = 'ECONOMY ANODIZED STEEL' + ) all_nations + group by + o_year + order by + o_year +---- +1995 +0.000 +1996 +0.000 + _______________________________________________ checkin-list mailing list -- [email protected] To unsubscribe send an email to [email protected]
