{"object":"sqlscan_ruleset","ruleset_version":"2026.07.24","updated":"2026-07-24","object_scanned":"one DML query (SELECT/UPDATE/DELETE) the caller is about to run (destructive/correctness safety + performance anti-patterns)","rules_total":14,"rules":[{"rule_id":"update_no_where","severity":"block","category":"destructive","flags":"UPDATE has no WHERE clause: it rewrites EVERY row in the table.","docs_ref":"https://www.postgresql.org/docs/current/sql-update.html"},{"rule_id":"delete_no_where","severity":"block","category":"destructive","flags":"DELETE has no WHERE clause: it removes EVERY row in the table.","docs_ref":"https://www.postgresql.org/docs/current/sql-delete.html"},{"rule_id":"where_tautology","severity":"block","category":"destructive","flags":"The WHERE clause is a constant tautology (e.g. 1=1 / true), so it matches every row — this UPDATE/DELETE effectively has no filter.","docs_ref":"https://www.postgresql.org/docs/current/sql-delete.html"},{"rule_id":"truncate","severity":"block","category":"destructive","flags":"TRUNCATE removes every row in the table and is not transaction-safe to undo mid-statement.","docs_ref":"https://www.postgresql.org/docs/current/sql-truncate.html"},{"rule_id":"not_in_subquery","severity":"caution","category":"correctness","flags":"NOT IN (subquery) returns no rows at all if the subquery yields a single NULL — a common, silent correctness trap.","docs_ref":"https://www.postgresql.org/docs/current/functions-subquery.html"},{"rule_id":"group_by_nonaggregate","severity":"caution","category":"correctness","flags":"A selected column is neither aggregated nor listed in GROUP BY: PostgreSQL rejects this, and MySQL/SQLite silently return an arbitrary row value.","docs_ref":"https://www.postgresql.org/docs/current/sql-select.html#SQL-GROUPBY"},{"rule_id":"cartesian_join","severity":"caution","category":"performance","flags":"A join has no ON/USING condition and no join predicate in WHERE — the result is a cartesian product (every row of each table paired with every row of the other).","docs_ref":"https://www.postgresql.org/docs/current/tutorial-join.html"},{"rule_id":"select_star","severity":"info","category":"performance","flags":"SELECT * returns every column, including large/unused ones; it also breaks silently when the table shape changes and blocks index-only scans.","docs_ref":"https://www.postgresql.org/docs/current/sql-select.html"},{"rule_id":"non_sargable_function","severity":"info","category":"performance","flags":"A function is applied to a column inside a predicate (e.g. lower(col)=...); this makes the predicate non-sargable, so a plain index on the column cannot be used.","docs_ref":"https://www.postgresql.org/docs/current/indexes-expressional.html"},{"rule_id":"leading_wildcard_like","severity":"info","category":"performance","flags":"A LIKE pattern begins with a wildcard (e.g. '%term'); a leading wildcard prevents a b-tree index from being used, forcing a full scan.","docs_ref":"https://www.postgresql.org/docs/current/indexes-types.html"},{"rule_id":"missing_limit","severity":"info","category":"performance","flags":"A SELECT has neither a LIMIT nor a WHERE clause: it scans and returns the whole table, which can be a large unbounded result.","docs_ref":"https://www.postgresql.org/docs/current/queries-limit.html"},{"rule_id":"large_offset","severity":"caution","category":"performance","flags":"A large OFFSET forces the engine to scan and discard all the skipped rows on every page — deep pagination gets slower the further you page.","docs_ref":"https://www.postgresql.org/docs/current/queries-limit.html"},{"rule_id":"count_distinct","severity":"info","category":"performance","flags":"COUNT(DISTINCT col) must build and de-duplicate the whole set; on a large table it is memory- and CPU-heavy.","docs_ref":"https://www.postgresql.org/docs/current/functions-aggregate.html"},{"rule_id":"distinct_after_join","severity":"info","category":"performance","flags":"SELECT DISTINCT together with a JOIN is often a band-aid over duplicate rows produced by a one-to-many join fan-out.","docs_ref":"https://www.postgresql.org/docs/current/queries-table-expressions.html"}],"categories":{"destructive":"deterministic data-loss guard from the query text alone (no schema needed) — the strong, verdict-block class","correctness":"deterministic result-correctness / cross-engine portability traps","performance":"heuristics; without your schema/indexes/EXPLAIN they cannot confirm a slow scan — caution/info, never a block (except a cartesian product with no filter at all)"},"dialects":[{"key":"postgres","label":"PostgreSQL","aliases":["postgres","postgresql","pg","psql","redshift"]},{"key":"mysql","label":"MySQL / MariaDB","aliases":["mysql","mariadb"]},{"key":"sqlite","label":"SQLite","aliases":["sqlite","sqlite3"]},{"key":"bigquery","label":"Google BigQuery","aliases":["bigquery","bq","standardsql"]},{"key":"snowflake","label":"Snowflake","aliases":["snowflake","snow"]}],"verdicts":["pass","caution","block"],"verdict_semantics":{"block":"a destructive/data-loss pattern (UPDATE/DELETE with no effective WHERE, TRUNCATE) or an unfiltered cartesian product","caution":"a correctness trap or a likely-costly pattern worth reviewing","pass":"no unsafe pattern detected (info-only performance hints may still appear)"},"options":{"indexed_columns":"optional list of column names; lifts a sargability finding (non-sargable function / leading-wildcard LIKE) from info to caution when the affected column is actually indexed"},"limits":{"max_body_bytes":262144,"max_statements":200,"batch_max":50,"large_offset_rows":1000},"source":"Own clean-room implementation of public SQL safety/performance best practice; SQL parsed with sqlglot (MIT).","disclaimer":"Automated query safety/performance indicators, not a guarantee. Destructive and correctness findings are deterministic from the query text; performance findings are heuristics — without your schema, indexes or an EXPLAIN plan they cannot confirm a slow scan, so treat caution/info as a prompt to review, not a verdict on actual runtime."}