TPC-H Q21 — Suppliers Who Kept Orders Waiting

TPC-H benchmark · all TPC-H queries

Suppliers who delayed orders they could fill

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  s_name,
  COUNT(*) AS numwait
FROM supplier, lineitem AS l1, orders, nation
WHERE
  s_suppkey = l1.l_suppkey
  AND o_orderkey = l1.l_orderkey
  AND o_orderstatus = 'F'
  AND l1.l_receiptdate > l1.l_commitdate
  AND EXISTS(
    SELECT
      *
    FROM lineitem AS l2
    WHERE
      l2.l_orderkey = l1.l_orderkey AND l2.l_suppkey <> l1.l_suppkey
  )
  AND NOT EXISTS(
    SELECT
      *
    FROM lineitem AS l3
    WHERE
      l3.l_orderkey = l1.l_orderkey
      AND l3.l_suppkey <> l1.l_suppkey
      AND l3.l_receiptdate > l3.l_commitdate
  )
  AND s_nationkey = n_nationkey
  AND n_name = 'SAUDI ARABIA'
GROUP BY
  s_name
ORDER BY
  numwait DESC,
  s_name
LIMIT 100

Representative DataFrame

Expression family (Polars, PySpark, DataFusion), default parameters.

def q21_expression_impl(ctx: DataFrameContext) -> Any:
    supplier = ctx.get_table("supplier")
    lineitem = ctx.get_table("lineitem")
    orders = ctx.get_table("orders")
    nation = ctx.get_table("nation")
    col = ctx.col
    lit = ctx.lit

    params = get_tpch_parameters(21)
    nation_name = params["nation_name"]

    target_suppliers = (
        supplier.join(nation, left_on="s_nationkey", right_on="n_nationkey")
        .filter(col("n_name") == lit(nation_name))
        .select(col("s_suppkey").alias("target_suppkey"), col("s_name"))
    )

    valid_orders = orders.filter(col("o_orderstatus") == lit("F")).select(col("o_orderkey").alias("valid_orderkey"))

    candidates = (
        lineitem.filter(col("l_receiptdate") > col("l_commitdate"))
        .join(target_suppliers, left_on="l_suppkey", right_on="target_suppkey")
        .join(valid_orders, left_on="l_orderkey", right_on="valid_orderkey")
        .select("l_orderkey", "l_suppkey", "s_name")
    )

    candidate_orders = candidates.select(col("l_orderkey").alias("cand_orderkey")).unique()

    suppliers_per_order = (
        lineitem.join(candidate_orders, left_on="l_orderkey", right_on="cand_orderkey", how="semi")
        .group_by("l_orderkey")
        .agg(col("l_suppkey").n_unique().alias("num_suppliers"))
        .select(col("l_orderkey").alias("supp_orderkey"), col("num_suppliers"))
    )

    late_suppliers_per_order = (
        lineitem.filter(col("l_receiptdate") > col("l_commitdate"))
        .join(candidate_orders, left_on="l_orderkey", right_on="cand_orderkey", how="semi")
        .group_by("l_orderkey")
        .agg(col("l_suppkey").n_unique().alias("num_late_suppliers"))
        .select(col("l_orderkey").alias("late_orderkey"), col("num_late_suppliers"))
    )

    return (
        candidates.join(suppliers_per_order, left_on="l_orderkey", right_on="supp_orderkey")
        .join(late_suppliers_per_order, left_on="l_orderkey", right_on="late_orderkey")
        .filter(col("num_suppliers") > lit(1))
        .filter(col("num_late_suppliers") == lit(1))
        .group_by("s_name")
        .agg(col("l_orderkey").count().alias("numwait"))
        .sort(["numwait", "s_name"], descending=[True, False])
        .limit(100)
    )

Get the exact query for your run

The blocks above are representative. To get the precise statement a specific platform runs at a specific scale, substitute your own values:

from benchbox import TPCH

bench = TPCH(scale_factor=0.01)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['21'])

Parameter substitution for this benchmark is seed-driven; pass a seed for a specific stream’s parameters:

print(bench.get_query(21, scale_factor=0.01, seed=42, dialect="datafusion"))

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('tpch', '21', family="expression").source)  # or family="pandas"

Source

Query text: benchbox/_binaries/tpc-h/templates/queries/21.sql