Rule reference

SQLClean has 45 built-in Rules. Default Rules run when [rules] select is not set; the others run when select names them by code or prefix. A Rule with a Safe fix is fixed by sqlclean fix; an Unsafe fix also needs --unsafe-fixes. See Selecting Rules and Safe and unsafe fixes.

Run sqlclean rules <CODE> for a Rule’s remediation.

Code	Finding	Default	Fix
SQC001	Comparison with NULL is never true	Yes	Unsafe
SQC002	Comma-separated sources create an implicit cartesian join	Yes	Safe
SQC003	Non-cross join has no meaningful condition	Yes	Safe
SQC004	Row selection is nondeterministic	Yes	—
SQC005	CTE is unreachable from the final query	Yes	Safe
SQC006	DISTINCT is redundant with the grouped output	Yes	Safe
SQC007	Positional set operation is vulnerable to column-order drift	Yes	—
SQC008	Bare UNION leaves duplicate handling implicit	No	Safe
SQC009	Table alias is duplicated in the same query scope	Yes	—
SQC010	Output alias is duplicated in the same select list	Yes	—
SQC011	Clause mixes positional and named references	No	—
SQC012	ELSE NULL is redundant	No	—
SQC013	DISTINCT is written as though it were a function	No	Safe
SQC014	Predicate compares identical constant values	No	—
SQC015	Empty statement follows another terminator	No	—
SQC016	Expression is aliased to its existing name	No	—
SQC017	Row count uses a constant expression	No	Safe
SQC018	ROW_NUMBER has no deterministic ordering	Yes	—
SQC019	NOT IN list contains NULL and can reject every row	Yes	—
SQC020	Set-operation branches project different column counts	Yes	—
SQC021	Query output has an uncontrolled column shape	No	—
SQC022	Calculated projection has no stable output name	No	—
SQC023	Table alias is never referenced	No	—
SQC024	WHERE predicate rejects NULL rows from a LEFT JOIN	No	—
SQC025	JOIN type is implicit	No	Safe
SQC026	ORDER BY mixes explicit and implicit directions	No	—
SQC027	Column reference is unqualified in a multi-relation query	No	—
SQC028	Single-relation query mixes qualified and unqualified columns	No	—
SQC029	Qualified reference uses a relation absent from this query scope	No	—
SQC030	CASE expression only converts a condition to TRUE or FALSE	No	Safe
SQC031	CASE expression is nested directly inside ELSE	No	—
SQC032	Joined relation contributes no referenced values	No	—
SQC033	Comment is not attached to the syntax node it explains	No	—
SQC034	Query keeps transformation logic outside top-level CTEs	No	—
SQC035	Terminal SELECT does not read plainly from the final top-level CTE	No	—
SQC036	CTE is nested inside another query	No	—
SQC037	Recursive CTE is not permitted	No	—
SQC038	CROSS JOIN creates an explicit cartesian product	No	—
SQC039	Inline query relation obscures data flow	Yes	—
SQC040	JOIN ON contains a computed expression	No	—
SQC041	Only the last top-level CTE may be named final, and it must use that name	No	—
SQC042	CTE output column is never read later in the query	No	—
SQC043	Ranking window sorts by more expressions than max_ranking_order_by	No	—
SQC044	String literal is longer than max_literal_length	No	—
SQC045	JSON payload is parsed more than once in one SELECT	No	—

SQC999 is the parse error finding. It is not a selectable Rule.

SQC043 and SQC044 read their limits from [rules.thresholds].

SQC001: NULL comparisons

= NULL and <> NULL are never true, so the filter matches no row:

SELECT order_id
FROM orders
WHERE cancelled_at = NULL

error[SQC001]: Comparison with NULL is never true
  --> models/open_orders.sql:3:20
   |
 3 | WHERE cancelled_at = NULL
   |                    ^
  = help: Use IS NULL or IS NOT NULL when testing for NULL.
  = fix: available with `sqlclean fix --unsafe-fixes`

The fix writes WHERE cancelled_at IS NULL. It is unsafe because the query then returns rows it did not return before.

SQC002 and SQC003: joins without keys

A comma between sources joins every row with every row unless a WHERE predicate filters them:

SELECT
  o.order_id,
  c.customer_name
FROM orders AS o, customers AS c
WHERE o.customer_id = c.customer_id

The SQC002 fix keeps the meaning and makes the cartesian product visible:

FROM orders AS o, customers AS c
FROM orders AS o
CROSS JOIN customers AS c
WHERE o.customer_id = c.customer_id

Move the predicate into JOIN customers AS c ON o.customer_id = c.customer_id by hand to make it a keyed join. SQC003 reports a JOIN whose condition is missing or constant, such as ON 1 = 1; its fix turns a JOIN without a condition into CROSS JOIN.

SQC005: unused CTEs

A CTE that no reachable query reads is dead code:

WITH paid AS (
  SELECT order_id FROM payments
),

refunded AS (
  SELECT order_id FROM refunds
)

SELECT order_id FROM paid

error[SQC005]: CTE is unreachable from the final query
  --> models/paid_orders.sql:5:1
   |
 5 | refunded AS (
   | ^^^^^^^^
  = help: Reference the CTE from the final query or another reachable CTE, or remove it.
  = fix: available with `sqlclean fix`

The fix removes refunded.

SQC018: unstable row numbers

Without ORDER BY in the window, the row numbered 1 can change between runs:

SELECT
  order_id,
  ROW_NUMBER() OVER (PARTITION BY customer_id) AS order_rank
FROM orders

error[SQC018]: ROW_NUMBER has no deterministic ordering

An ORDER BY is not always enough. Ties still rank in any order unless PARTITION BY and ORDER BY together cover a unique key of the ranked rows. SQLClean only knows a key it can prove from the query, such as the GROUP BY columns of a CTE. Ranking straight over a table is reported with a second message:

error[SQC018]: Ranking window order is not proven unique within its partition
  --> models/ranked.sql:3:3
   |
 3 |   ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id) AS order_rank
   |   ^^^^^^^^^^
  = help: PARTITION BY and ORDER BY must together cover a unique key of the ranked rows. End the ORDER BY with a unique, non-null tie-breaker column. If the rows have no unique column, build one upstream from the columns that identify a row (for example `HASH(source_partition, source_offset, line_index) AS row_key`) and order by it last.

Here the CTE’s GROUP BY makes customer_id, order_id a key, so the window passes:

WITH customer_orders AS (
  SELECT
    customer_id,
    order_id,
    MAX(ordered_at) AS ordered_at
  FROM orders
  GROUP BY
    customer_id,
    order_id
)

SELECT
  order_id,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id) AS order_rank
FROM customer_orders

SQC004 reports the same problem for LIMIT and OFFSET; see the example in Rules.

SQC019: NULL in NOT IN

x NOT IN (..., NULL) is never true, so the filter rejects every row:

SELECT order_id
FROM orders
WHERE status NOT IN ('cancelled', NULL)

error[SQC019]: NOT IN list contains NULL and can reject every row
  --> models/active_orders.sql:3:14
   |
 3 | WHERE status NOT IN ('cancelled', NULL)
   |              ^^^
  = help: Remove NULL from the list or express the anti-join with a NULL-safe NOT EXISTS.

SQC039: inline subqueries

A subquery in FROM hides a step of the query. Name it as a CTE instead:

-- Reported
SELECT order_id
FROM (SELECT order_id FROM orders WHERE amount > 0) AS paid

-- Passes
WITH paid AS (
  SELECT order_id
  FROM orders
  WHERE amount > 0
)

SELECT order_id
FROM paid

SQC034, SQC035 and SQC041: query structure

These opt-in Rules ask every query to do its work in top-level CTEs, name the last one final, and end with a plain SELECT of named columns from it. This query passes every built-in Rule:

WITH open_orders AS (
  SELECT
    order_id,
    amount
  FROM orders
  WHERE cancelled_at IS NULL
),

final AS (
  SELECT
    order_id,
    amount
  FROM open_orders
)

SELECT
  order_id,
  amount
FROM final

Ending with SELECT * FROM final is reported by SQC021, which asks for named output columns.