A revenue rollup that returns the wrong number
LEFT JOIN invoices i ON i.account_id = a.account_id WHERE i.status = 'posted' ... WHERE account_id NOT IN (SELECT account_id FROM suspended_accounts)
Warned. Its findings are high, which the demo policy warns on and does not block. Filters in the ON clause and NOT EXISTS are allowed with no finding.
Without a key: Q-NULL-NOTIN at high. The collapsed LEFT join, Q-JOIN-LEFT-FILT at high, needs a trial or Team key.
Full SQL, engine output and its safe twin
Misstatement: a revenue rollup that runs without error and returns the wrong number — three different ways, including a billable-only filter that is provably always true
-- A revenue rollup that runs without error and returns the wrong number. WITH active AS ( SELECT account_id, region FROM accounts WHERE status = 'active' ), billable AS ( SELECT a.account_id, SUM(i.amount) AS revenue FROM active a LEFT JOIN invoices i ON i.account_id = a.account_id WHERE i.status = 'posted' AND (i.is_billable = TRUE OR i.is_billable = FALSE OR i.is_billable IS NULL) GROUP BY a.account_id ) SELECT * FROM billable WHERE account_id NOT IN (SELECT account_id FROM suspended_accounts);
Q-JOIN-LEFT-FILTOuter join's nullable side filtered in WHERE clause or by a later join. The unmatched rows the outer join keeps are dropped, likely a bug.
Q-NULL-NOTINNULL-logic hazard: NOT IN with a subquery that may return NULL. If the subquery returns any NULL, the NOT IN evaluates to UNKNOWN and filters all rows. Use NOT EXISTS or ensure the subquery has WHERE ... IS NOT NULL.
Q-PRED-TAUTOLOGYTautological predicate: condition is always true for non-NULL values (e.g., WHERE x=1 OR x<>1). The WHERE clause filters at most NULL rows.
lexega-sql 3.0.0 · snowflake · with a key
Same rollup, null-safe, really filtered. The join filters move into the ON clause so unmatched accounts survive, the billable filter actually filters, and NOT EXISTS is immune to a NULL in suspended_accounts.
-- The join filters live in the ON clause, so unmatched accounts survive;
-- NOT EXISTS is immune to a NULL in suspended_accounts.
WITH active AS (
SELECT account_id, region FROM accounts WHERE status = 'active'
),
billable AS (
SELECT a.account_id, SUM(i.amount) AS revenue
FROM active a
LEFT JOIN invoices i
ON i.account_id = a.account_id
AND i.status = 'posted'
AND i.is_billable = TRUE
GROUP BY a.account_id
)
SELECT * FROM billable b
WHERE NOT EXISTS (
SELECT 1 FROM suspended_accounts s WHERE s.account_id = b.account_id
);lexega-sql 3.0.0 · snowflake · with a key