unproven-join¶
Default severity: ignore
What it does¶
Reports a join in an aggregating query whose conditions match no primary or unique key on either side, so sqlty cannot tell whether it repeats rows.
[tool.sqlty]
schema = "schema.sql"
[tool.sqlty.rules]
unproven-join = "warn"
schema.sql:
CREATE TABLE orders (id INTEGER PRIMARY KEY, region TEXT NOT NULL, total NUMERIC NOT NULL);
CREATE TABLE targets (region TEXT NOT NULL, amount NUMERIC NOT NULL);
import decimal
from sqlty import SQL
# error: [unproven-join] "the join of orders and targets matches no primary or unique key, so how often it repeats their rows is unknown"
query = SQL[tuple[decimal.Decimal | None]]("""--sql
SELECT SUM(o.total) FROM orders o JOIN targets t ON t.region = o.region""")
Open in playgroundWhy is this bad?¶
fanout-aggregate and chasm-join
only report what keys prove. When no key covers a join, a region with two
targets doubles its orders, and neither rule sees it.
How to fix¶
Declare the key the data has: UNIQUE (region) on targets, or a unique
index. If the join may match several rows, aggregate that side first.
Configuration¶
Off by default: in a schema that declares no keys, every join of an aggregating query would be reported. Turn it on where the schema declares its keys:
[tool.sqlty.rules]
unproven-join = "warn"