///|
fn read_and_parse(path : String) -> Statement {
let input = @fs.read_file_to_string(path) catch {
e => {
println("Read file error: \{e.to_string()}")
panic()
}
}
parse_sql(input) catch {
LexerError(e) => {
println(e)
panic()
}
ParserError(e) => {
println(e)
panic()
}
}
}
///|
test "TPCH 1" {
let stmt = read_and_parse("src/tpch/1.sql")
inspect(
stmt,
content=(
#|SELECT
#| l_returnflag,
#| l_linestatus,
#| sum(l_quantity) AS sum_qty,
#| sum(l_extendedprice) AS sum_base_price,
#| sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
#| sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
#| avg(l_quantity) AS avg_qty,
#| avg(l_extendedprice) AS avg_price,
#| avg(l_discount) AS avg_disc,
#| count(*) AS count_order
#|FROM
#| lineitem
#|WHERE
#| l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY (3)
#|GROUP BY
#| l_returnflag,
#| l_linestatus
#|ORDER BY
#| l_returnflag,
#| l_linestatus;
),
)
}
///|
test "TPCH 2" {
let stmt = read_and_parse("src/tpch/2.sql")
inspect(
stmt,
content=(
#|SELECT
#| s_acctbal,
#| s_name,
#| n_name,
#| p_partkey,
#| p_mfgr,
#| s_address,
#| s_phone,
#| s_comment
#|FROM
#| part,
#| supplier,
#| partsupp,
#| nation,
#| region
#|WHERE
#| p_partkey = ps_partkey
#| AND s_suppkey = ps_suppkey
#| AND p_size = 15
#| AND p_type LIKE '%BRASS'
#| AND s_nationkey = n_nationkey
#| AND n_regionkey = r_regionkey
#| AND r_name = 'EUROPE'
#| AND ps_supplycost = (
#| SELECT
#| min(ps_supplycost)
#| FROM
#| partsupp,
#| supplier,
#| nation,
#| region
#| WHERE
#| p_partkey = ps_partkey
#| AND s_suppkey = ps_suppkey
#| AND s_nationkey = n_nationkey
#| AND n_regionkey = r_regionkey
#| AND r_name = 'EUROPE'
#| )
#|ORDER BY
#| s_acctbal DESC,
#| n_name,
#| s_name,
#| p_partkey;
),
)
}
///|
test "TPCH 3" {
let stmt = read_and_parse("src/tpch/3.sql")
inspect(
stmt,
content=(
#|SELECT
#| l_orderkey,
#| sum(l_extendedprice * (1 - l_discount)) AS revenue,
#| o_orderdate,
#| o_shippriority
#|FROM
#| customer,
#| orders,
#| lineitem
#|WHERE
#| c_mktsegment = 'BUILDING'
#| AND c_custkey = o_custkey
#| AND l_orderkey = o_orderkey
#| AND o_orderdate < DATE '1995-03-15'
#| AND l_shipdate > DATE '1995-03-15'
#|GROUP BY
#| l_orderkey,
#| o_orderdate,
#| o_shippriority
#|ORDER BY
#| revenue DESC,
#| o_orderdate;
),
)
}
///|
test "TPCH 4" {
let stmt = read_and_parse("src/tpch/4.sql")
inspect(
stmt,
content=(
#|SELECT
#| o_orderpriority,
#| count(*) AS order_count
#|FROM
#| orders
#|WHERE
#| o_orderdate >= DATE '1993-07-01'
#| AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
#| AND EXISTS (
#| SELECT
#| *
#| FROM
#| lineitem
#| WHERE
#| l_orderkey = o_orderkey
#| AND l_commitdate < l_receiptdate
#| )
#|GROUP BY
#| o_orderpriority
#|ORDER BY
#| o_orderpriority;
),
)
}
///|
test "TPCH 5" {
let stmt = read_and_parse("src/tpch/5.sql")
inspect(
stmt,
content=(
#|SELECT
#| n_name,
#| sum(l_extendedprice * (1 - l_discount)) AS revenue
#|FROM
#| customer,
#| orders,
#| lineitem,
#| supplier,
#| nation,
#| region
#|WHERE
#| c_custkey = o_custkey
#| AND l_orderkey = o_orderkey
#| AND l_suppkey = s_suppkey
#| AND c_nationkey = s_nationkey
#| AND s_nationkey = n_nationkey
#| AND n_regionkey = r_regionkey
#| AND r_name = 'ASIA'
#| AND o_orderdate >= DATE '1994-01-01'
#| AND o_orderdate < DATE '1994-01-01' + INTERVAL '1' YEAR
#|GROUP BY
#| n_name
#|ORDER BY
#| revenue DESC;
),
)
}
///|
test "TPCH 6" {
let stmt = read_and_parse("src/tpch/6.sql")
inspect(
stmt,
content=(
#|SELECT
#| sum(l_extendedprice * l_discount) AS revenue
#|FROM
#| lineitem
#|WHERE
#| l_shipdate >= DATE '1994-01-01'
#| AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR
#| AND l_discount BETWEEN 0.06 - 0.01 AND 0.06 + 0.01
#| AND l_quantity < 24;
),
)
}
///|
test "TPCH 7" {
let stmt = read_and_parse("src/tpch/7.sql")
inspect(
stmt,
content=(
#|SELECT
#| supp_nation,
#| cust_nation,
#| l_year,
#| sum(volume) AS revenue
#|FROM
#| (
#| SELECT
#| n1.n_name AS supp_nation,
#| n2.n_name AS cust_nation,
#| EXTRACT(YEAR FROM l_shipdate) AS l_year,
#| l_extendedprice * (1 - l_discount) AS volume
#| FROM
#| supplier,
#| lineitem,
#| orders,
#| customer,
#| nation AS n1,
#| nation AS n2
#| WHERE
#| s_suppkey = l_suppkey
#| AND o_orderkey = l_orderkey
#| AND c_custkey = o_custkey
#| AND s_nationkey = n1.n_nationkey
#| AND c_nationkey = n2.n_nationkey
#| AND (
#| n1.n_name = 'FRANCE'
#| AND n2.n_name = 'GERMANY'
#| OR n1.n_name = 'GERMANY'
#| AND n2.n_name = 'FRANCE'
#| )
#| AND l_shipdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
#| ) AS shipping
#|GROUP BY
#| supp_nation,
#| cust_nation,
#| l_year
#|ORDER BY
#| supp_nation,
#| cust_nation,
#| l_year;
),
)
}
///|
test "TPCH 8" {
let stmt = read_and_parse("src/tpch/8.sql")
inspect(
stmt,
content=(
#|SELECT
#| o_year,
#| sum(CASE
#| WHEN nation = 'BRAZIL' THEN volume
#| ELSE 0
#| END) / 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 AS n1,
#| nation AS 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'
#| ) AS all_nations
#|GROUP BY
#| o_year
#|ORDER BY
#| o_year;
),
)
}
///|
test "TPCH 9" {
let stmt = read_and_parse("src/tpch/9.sql")
inspect(
stmt,
content=(
#|SELECT
#| nation,
#| o_year,
#| sum(amount) AS sum_profit
#|FROM
#| (
#| SELECT
#| n_name AS nation,
#| EXTRACT(YEAR FROM o_orderdate) AS o_year,
#| l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
#| FROM
#| part,
#| supplier,
#| lineitem,
#| partsupp,
#| orders,
#| nation
#| WHERE
#| s_suppkey = l_suppkey
#| AND ps_suppkey = l_suppkey
#| AND ps_partkey = l_partkey
#| AND p_partkey = l_partkey
#| AND o_orderkey = l_orderkey
#| AND s_nationkey = n_nationkey
#| AND p_name LIKE '%green%'
#| ) AS profit
#|GROUP BY
#| nation,
#| o_year
#|ORDER BY
#| nation,
#| o_year DESC;
),
)
}
///|
test "TPCH 10" {
let stmt = read_and_parse("src/tpch/10.sql")
inspect(
stmt,
content=(
#|SELECT
#| c_custkey,
#| c_name,
#| sum(l_extendedprice * (1 - l_discount)) AS revenue,
#| c_acctbal,
#| n_name,
#| c_address,
#| c_phone,
#| c_comment
#|FROM
#| customer,
#| orders,
#| lineitem,
#| nation
#|WHERE
#| c_custkey = o_custkey
#| AND l_orderkey = o_orderkey
#| AND o_orderdate >= DATE '1993-10-01'
#| AND o_orderdate < DATE '1993-10-01' + INTERVAL '3' MONTH
#| AND l_returnflag = 'R'
#| AND c_nationkey = n_nationkey
#|GROUP BY
#| c_custkey,
#| c_name,
#| c_acctbal,
#| c_phone,
#| n_name,
#| c_address,
#| c_comment
#|ORDER BY
#| revenue DESC;
),
)
}
///|
test "TPCH 11" {
let stmt = read_and_parse("src/tpch/11.sql")
inspect(
stmt,
content=(
#|SELECT
#| ps_partkey,
#| sum(ps_supplycost * ps_availqty) AS value
#|FROM
#| partsupp,
#| supplier,
#| nation
#|WHERE
#| ps_suppkey = s_suppkey
#| AND s_nationkey = n_nationkey
#| AND n_name = 'GERMANY'
#|GROUP BY
#| ps_partkey
#|HAVING
#| sum(ps_supplycost * ps_availqty) > (
#| SELECT
#| sum(ps_supplycost * ps_availqty) * 0.0001
#| FROM
#| partsupp,
#| supplier,
#| nation
#| WHERE
#| ps_suppkey = s_suppkey
#| AND s_nationkey = n_nationkey
#| AND n_name = 'GERMANY'
#| )
#|ORDER BY
#| value DESC;
),
)
}