rules

Documentation

Overview

Package rules contains all SQL lint rules. Import this package to register all SQL rules with the unified registry.

Rules are automatically registered via init() functions when this package is imported:

import _ "github.com/leapstack-labs/leapsql/pkg/lint/sql/rules"

Rule Categories:

• AL (Aliasing): Rules about alias usage and naming
• AM (Ambiguous): Rules about ambiguous SQL constructs
• CV (Convention): Rules about SQL coding conventions
• RF (References): Rules about column and table references
• ST (Structure): Rules about SQL query structure

Package rules contains all SQL lint rules for statement-level analysis.

Rules are organized by prefix to indicate their category:

• al_*.go: Aliasing rules (table and column alias conventions)
• am_*.go: Ambiguous rules (potentially confusing constructs)
• cv_*.go: Convention rules (style and formatting preferences)
• rf_*.go: References rules (column and table reference patterns)
• st_*.go: Structure rules (query structure and organization)

Import this package to register all SQL rules:

import _ "github.com/leapstack-labs/leapsql/pkg/lint/sql/rules"

Index

• Variables

Variables

var AliasLength = sql.RuleDef{
	ID:          "AL06",
	Name:        "aliasing.length",
	Group:       "aliasing",
	Description: "Alias length should be between min and max characters.",
	Severity:    core.SeverityInfo,
	ConfigKeys:  []string{"min_length", "max_length"},
	Check:       checkAliasLength,

	Rationale: `Overly short aliases (single letters) lack meaning and make queries 
harder to understand. Overly long aliases add verbosity without improving clarity 
and may exceed database identifier limits. Balance brevity with descriptiveness.`,

	BadExample: `SELECT a.customer_name, b.order_total
FROM customers_with_active_subscriptions_table a
JOIN order_history_last_30_days b ON b.customer_id = a.id`,

	GoodExample: `SELECT cust.customer_name, orders.order_total
FROM customers_with_active_subscriptions_table cust
JOIN order_history_last_30_days orders ON orders.customer_id = cust.id`,

	Fix: "Choose aliases that are descriptive but concise, typically 2-10 characters. Use meaningful abbreviations.",
}

AliasLength enforces alias length constraints.

var AmbiguousColumnRef = sql.RuleDef{
	ID:          "AM06",
	Name:        "ambiguous.column_refs",
	Group:       "ambiguous",
	Description: "Unqualified column reference may be ambiguous with multiple tables.",
	Severity:    core.SeverityWarning,
	Check:       checkAmbiguousColumnRef,

	Rationale: `When multiple tables are joined, unqualified column names may exist in 
more than one table. The database may pick an unexpected source, or error out. 
Qualifying columns prevents ambiguity and makes the query self-documenting.`,

	BadExample: `SELECT name, email, created_at
FROM customers c
JOIN orders o ON o.customer_id = c.id`,

	GoodExample: `SELECT c.name, c.email, o.created_at
FROM customers c
JOIN orders o ON o.customer_id = c.id`,

	Fix: "Prefix column references with the table alias (e.g., c.name instead of name).",
}

AmbiguousColumnRef warns about unqualified column references with multiple tables.

var BlockedWords = sql.RuleDef{
	ID:          "CV09",
	Name:        "convention.blocked_words",
	Group:       "convention",
	Description: "Block dangerous SQL keywords like DELETE, DROP, TRUNCATE.",
	Severity:    core.SeverityWarning,
	ConfigKeys:  []string{"blocked_words"},
	Check:       checkBlockedWords,

	Rationale: `In data transformation pipelines (dbt, LeapSQL), destructive operations 
like DELETE, DROP, and TRUNCATE are usually mistakes. Models should be declarative 
transformations, not imperative modifications. Block these keywords to prevent accidents.`,

	BadExample: `-- This could accidentally delete production data
DELETE FROM customers WHERE status = 'inactive'`,

	GoodExample: `-- Use a filter in your SELECT instead
SELECT * FROM customers
WHERE status != 'inactive'`,

	Fix: "Remove or refactor destructive SQL statements. For data pipelines, use incremental logic or WHERE filters instead of DELETE/TRUNCATE.",
}

BlockedWords warns about dangerous SQL keywords.

var ColumnCountMismatch = sql.RuleDef{
	ID:          "AM04",
	Name:        "ambiguous.column_count",
	Group:       "ambiguous",
	Description: "Mismatched column counts in set operation.",
	Severity:    core.SeverityError,
	Check:       checkColumnCountMismatch,

	Rationale: `Set operations (UNION, INTERSECT, EXCEPT) require all queries to have the 
same number of columns. A mismatch will cause a runtime error. This rule catches 
the issue at development time.`,

	BadExample: `SELECT id, name, email FROM customers
UNION ALL
SELECT id, name FROM suppliers`,

	GoodExample: `SELECT id, name, email FROM customers
UNION ALL
SELECT id, name, contact_email FROM suppliers`,

	Fix: "Ensure all queries in the set operation have the same number of columns.",
}

ColumnCountMismatch warns about mismatched column counts in set operations.

var ConsistentQualification = sql.RuleDef{
	ID:          "RF03",
	Name:        "references.consistent",
	Group:       "references",
	Description: "Column qualification style should be consistent.",
	Severity:    core.SeverityInfo,
	Check:       checkConsistentQualification,

	Rationale: `Mixing qualified and unqualified column references in the same query reduces readability. 
A consistent style makes it easier to understand which table each column comes from and helps 
reviewers quickly verify query correctness.`,

	BadExample: `SELECT customers.name, amount, customers.email
FROM customers
JOIN orders ON customers.id = orders.customer_id`,

	GoodExample: `SELECT customers.name, orders.amount, customers.email
FROM customers
JOIN orders ON customers.id = orders.customer_id`,

	Fix: "Use the same qualification style (qualified or unqualified) for all column references.",
}

ConsistentQualification enforces consistent column qualification style.

var ConstantExpression = sql.RuleDef{
	ID:          "ST10",
	Name:        "structure.constant_expression",
	Group:       "structure",
	Description: "Unnecessary constant expressions like WHERE 1=1 or WHERE true.",
	Severity:    core.SeverityInfo,
	Check:       checkConstantExpression,

	Rationale: `Constant expressions like WHERE 1=1 or WHERE true are often artifacts of dynamic SQL 
generation. In static SQL models, they add noise without affecting results. Removing them makes the 
query cleaner and easier to understand.`,

	BadExample: `SELECT *
FROM orders
WHERE 1=1
  AND status = 'active'`,

	GoodExample: `SELECT *
FROM orders
WHERE status = 'active'`,

	Fix: "Remove constant expressions from WHERE clauses.",
}

ConstantExpression warns about unnecessary constant expressions like WHERE 1=1.

var CountStyle = sql.RuleDef{
	ID:          "CV04",
	Name:        "convention.count_rows",
	Group:       "convention",
	Description: "Prefer COUNT(*) over COUNT(1) for counting rows.",
	Severity:    core.SeverityHint,
	Check:       checkCountStyle,

	Rationale: `COUNT(*) is the standard and most readable way to count rows. COUNT(1) 
achieves the same result but is less intuitive. Modern query optimizers treat them 
identically, so there's no performance benefit to COUNT(1). Use COUNT(*) for clarity.`,

	BadExample: `SELECT
    department,
    COUNT(1) AS employee_count
FROM employees
GROUP BY department`,

	GoodExample: `SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department`,

	Fix: "Replace COUNT(1) with COUNT(*) for counting rows.",
}

CountStyle enforces consistent COUNT style (COUNT(*) vs COUNT(1)).

var DistinctVsGroupBy = sql.RuleDef{
	ID:          "ST08",
	Name:        "structure.distinct",
	Group:       "structure",
	Description: "Consider GROUP BY instead of DISTINCT when selecting columns for aggregation.",
	Severity:    core.SeverityInfo,
	Check:       checkDistinctVsGroupBy,

	Rationale: `Using GROUP BY instead of DISTINCT on simple column selections makes the query's intent 
clearer and positions the code better for future aggregation needs. GROUP BY explicitly shows which 
columns define the unique rows, while DISTINCT can be ambiguous in complex queries.`,

	BadExample: `SELECT DISTINCT department, location
FROM employees`,

	GoodExample: `SELECT department, location
FROM employees
GROUP BY department, location`,

	Fix: "Replace SELECT DISTINCT with GROUP BY on the same columns.",
}

DistinctVsGroupBy suggests using GROUP BY instead of DISTINCT for aggregates.

var DistinctWithGroupBy = sql.RuleDef{
	ID:          "AM01",
	Name:        "ambiguous.distinct",
	Group:       "ambiguous",
	Description: "Using DISTINCT with GROUP BY is redundant.",
	Severity:    core.SeverityWarning,
	Check:       checkDistinctWithGroupBy,

	Rationale: `GROUP BY already guarantees unique rows for the grouped columns. 
Adding DISTINCT is redundant and may confuse readers about the query's intent. 
It can also mislead developers into thinking DISTINCT is needed for correctness.`,

	BadExample: `SELECT DISTINCT department, COUNT(*)
FROM employees
GROUP BY department`,

	GoodExample: `SELECT department, COUNT(*)
FROM employees
GROUP BY department`,

	Fix: "Remove the DISTINCT keyword when using GROUP BY.",
}

DistinctWithGroupBy detects redundant DISTINCT with GROUP BY.

var ElseNull = sql.RuleDef{
	ID:          "ST01",
	Name:        "structure.else_null",
	Group:       "structure",
	Description: "ELSE NULL is redundant in CASE expressions.",
	Severity:    core.SeverityHint,
	Check:       checkElseNull,

	Rationale: `CASE expressions implicitly return NULL when no WHEN clause matches and no ELSE is specified. 
Writing ELSE NULL explicitly adds verbosity without changing behavior. Removing it keeps the query concise 
while maintaining the same semantics.`,

	BadExample: `SELECT
  CASE status
    WHEN 'active' THEN 1
    WHEN 'inactive' THEN 0
    ELSE NULL
  END AS status_code
FROM users`,

	GoodExample: `SELECT
  CASE status
    WHEN 'active' THEN 1
    WHEN 'inactive' THEN 0
  END AS status_code
FROM users`,

	Fix: "Remove the ELSE NULL clause from the CASE expression.",
}

ElseNull warns about redundant ELSE NULL in CASE expressions.

var ExpressionAlias = sql.RuleDef{
	ID:          "AL03",
	Name:        "aliasing.expression",
	Group:       "aliasing",
	Description: "Expression columns should have explicit aliases.",
	Severity:    core.SeverityInfo,
	Check:       checkExpressionAlias,

	Rationale: `Expressions without aliases produce auto-generated column names that vary 
by database (e.g., "?column?", "expr0", "count(*)"). Explicit aliases make query results 
predictable and self-documenting, improving usability for downstream consumers.`,

	BadExample: `SELECT
    first_name || ' ' || last_name,
    UPPER(email),
    COUNT(*)
FROM users
GROUP BY 1, 2`,

	GoodExample: `SELECT
    first_name || ' ' || last_name AS full_name,
    UPPER(email) AS email_upper,
    COUNT(*) AS user_count
FROM users
GROUP BY 1, 2`,

	Fix: "Add an explicit alias using AS to give the expression a meaningful name.",
}

ExpressionAlias recommends adding aliases to expression columns.

var ForbidAlias = sql.RuleDef{
	ID:          "AL07",
	Name:        "aliasing.forbid",
	Group:       "aliasing",
	Description: "Forbidden alias patterns (e.g., single letters, t1/t2).",
	Severity:    core.SeverityWarning,
	ConfigKeys:  []string{"forbidden_patterns", "forbidden_names"},
	Check:       checkForbidAlias,

	Rationale: `Generic aliases like single letters (a, b, c) or numbered tables (t1, t2) 
provide no semantic meaning. They make queries harder to understand and maintain, 
especially in complex queries with multiple joins. Use descriptive aliases instead.`,

	BadExample: `SELECT a.name, b.total, c.date
FROM customers a
JOIN orders b ON b.customer_id = a.id
JOIN shipments c ON c.order_id = b.id`,

	GoodExample: `SELECT cust.name, ord.total, ship.date
FROM customers cust
JOIN orders ord ON ord.customer_id = cust.id
JOIN shipments ship ON ship.order_id = ord.id`,

	Fix: "Replace forbidden aliases with meaningful names that describe what the table represents in this query context.",
}

ForbidAlias warns about forbidden alias patterns.

var ImplicitJoin = sql.RuleDef{
	ID:          "AM05",
	Name:        "ambiguous.join",
	Group:       "ambiguous",
	Description: "Comma-separated tables create an implicit cross join.",
	Severity:    core.SeverityInfo,
	Check:       checkImplicitJoin,

	Rationale: `The old-style comma join syntax (FROM a, b WHERE a.id = b.id) is harder 
to read than explicit JOIN syntax. It's easy to accidentally create a cross join 
by forgetting the WHERE condition. Explicit JOINs make intent clear.`,

	BadExample: `SELECT c.name, o.total
FROM customers c, orders o
WHERE c.id = o.customer_id`,

	GoodExample: `SELECT c.name, o.total
FROM customers c
JOIN orders o ON c.id = o.customer_id`,

	Fix: "Replace comma-separated tables with explicit JOIN syntax (INNER JOIN, LEFT JOIN, etc.).",
}

ImplicitJoin warns about comma-separated tables (implicit cross join).

var IsNullComparison = sql.RuleDef{
	ID:          "CV05",
	Name:        "convention.is_null",
	Group:       "convention",
	Description: "Use IS NULL instead of = NULL for NULL comparisons.",
	Severity:    core.SeverityWarning,
	Check:       checkIsNullComparison,

	Rationale: `In SQL, NULL represents unknown, and comparing anything to NULL with = 
or != always yields NULL (unknown), not true or false. This is a common source of 
bugs. Use IS NULL or IS NOT NULL for correct NULL handling.`,

	BadExample: `SELECT * FROM orders
WHERE shipped_date = NULL`,

	GoodExample: `SELECT * FROM orders
WHERE shipped_date IS NULL`,

	Fix: "Replace = NULL with IS NULL, and != NULL with IS NOT NULL.",
}

IsNullComparison warns about using = NULL instead of IS NULL.

var JoinConditionOrder = sql.RuleDef{
	ID:          "ST09",
	Name:        "structure.join_condition_order",
	Group:       "structure",
	Description: "Join condition should reference left table first (e.g., a.id = b.id, not b.id = a.id).",
	Severity:    core.SeverityHint,
	Check:       checkJoinConditionOrder,

	Rationale: `Consistently ordering join conditions with the left (existing) table first improves readability. 
It follows the natural reading order of the query: FROM table_a JOIN table_b ON table_a.col = table_b.col. 
This convention makes it easier to trace relationships through the query.`,

	BadExample: `SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id`,

	GoodExample: `SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id`,

	Fix: "Reorder the join condition to reference the left table first.",
}

JoinConditionOrder checks that join conditions reference the left table first.

var JoinConditionTables = sql.RuleDef{
	ID:          "AM08",
	Name:        "ambiguous.join_condition",
	Group:       "ambiguous",
	Description: "Join condition should reference both tables being joined.",
	Severity:    core.SeverityWarning,
	Check:       checkJoinConditionTables,

	Rationale: `A JOIN condition that doesn't reference the joined table is likely a bug. 
It effectively creates a cross join filtered by the condition, which is rarely intended. 
Each JOIN's ON clause should reference both the preceding and joining tables.`,

	BadExample: `SELECT c.name, o.total
FROM customers c
JOIN orders o ON c.status = 'active'`,

	GoodExample: `SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id`,

	Fix: "Ensure the JOIN condition references columns from both the left and right tables.",
}

JoinConditionTables warns when join condition doesn't reference both tables.

var NestedCase = sql.RuleDef{
	ID:          "ST04",
	Name:        "structure.nested_case",
	Group:       "structure",
	Description: "Nested CASE expressions reduce readability.",
	Severity:    core.SeverityInfo,
	Check:       checkNestedCase,

	Rationale: `Nested CASE expressions are difficult to read and understand. They often indicate complex 
business logic that could be simplified by restructuring the query, using CTEs, or extracting the logic 
into a separate model or view.`,

	BadExample: `SELECT
  CASE
    WHEN status = 'A' THEN
      CASE
        WHEN priority = 1 THEN 'High Active'
        ELSE 'Low Active'
      END
    ELSE 'Inactive'
  END AS label
FROM tasks`,

	GoodExample: `SELECT
  CASE
    WHEN status = 'A' AND priority = 1 THEN 'High Active'
    WHEN status = 'A' THEN 'Low Active'
    ELSE 'Inactive'
  END AS label
FROM tasks`,

	Fix: "Flatten nested CASE expressions by combining conditions, or extract complex logic into a CTE or separate model.",
}

NestedCase warns about nested CASE expressions which reduce readability.

var NotEqualOperator = sql.RuleDef{
	ID:          "CV01",
	Name:        "convention.not_equal",
	Group:       "convention",
	Description: "Prefer != over <> for not equal operator (NOT IMPLEMENTED: AST normalizes both operators).",
	Severity:    core.SeverityHint,
	Check:       checkNotEqualOperator,

	Rationale: `Using a consistent not-equal operator (either != or <>) throughout a 
codebase improves readability. The != operator is more common in modern programming 
languages, while <> is standard SQL. Pick one and use it consistently.`,

	BadExample: `SELECT * FROM orders
WHERE status <> 'cancelled'
  AND type != 'test'`,

	GoodExample: `SELECT * FROM orders
WHERE status != 'cancelled'
  AND type != 'test'`,

	Fix: "Use a consistent not-equal operator throughout your queries. This rule is not currently enforced due to AST limitations.",
}

NotEqualOperator recommends consistent not-equal operator usage.

NOT IMPLEMENTED: This rule cannot be implemented because the lexer normalizes both <> and != to the same NE token. The original source syntax is not preserved in the AST, making it impossible to detect which operator was used.

To implement this rule would require: - Adding an OriginalOp field to BinaryExpr to track the source syntax - Modifying the lexer to preserve the original operator form

See: plans/done/sqlfluff-linting-gaps.md for more details.

var OrderByAmbiguous = sql.RuleDef{
	ID:          "AM03",
	Name:        "ambiguous.order_by",
	Group:       "ambiguous",
	Description: "ORDER BY column may be ambiguous in set operation.",
	Severity:    core.SeverityWarning,
	Check:       checkOrderByAmbiguous,

	Rationale: `In set operations (UNION, INTERSECT, EXCEPT), column names from different 
queries may differ. Using column names in ORDER BY can be ambiguous and may behave 
differently across databases. Column positions (1, 2, 3) are unambiguous.`,

	BadExample: `SELECT name, email FROM customers
UNION ALL
SELECT company_name, contact_email FROM suppliers
ORDER BY name`,

	GoodExample: `SELECT name, email FROM customers
UNION ALL
SELECT company_name, contact_email FROM suppliers
ORDER BY 1`,

	Fix: "Use column positions (1, 2, etc.) instead of column names in ORDER BY for set operations.",
}

OrderByAmbiguous warns about ORDER BY in set operations with ambiguous columns.

var OrderByLimitWithUnion = sql.RuleDef{
	ID:          "AM09",
	Name:        "ambiguous.order_by_limit",
	Group:       "ambiguous",
	Description: "ORDER BY/LIMIT with set operation may have unexpected scope.",
	Severity:    core.SeverityWarning,
	Check:       checkOrderByLimitWithUnion,

	Rationale: `In set operations, ORDER BY and LIMIT without parentheses apply to the 
entire combined result, not individual queries. This behavior may be surprising. 
Use parentheses to make the intended scope explicit.`,

	BadExample: `SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers
ORDER BY name
LIMIT 10`,

	GoodExample: `-- To order/limit the final result:
(SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers)
ORDER BY name
LIMIT 10

-- To order/limit individual queries:
(SELECT name FROM customers ORDER BY name LIMIT 10)
UNION ALL
(SELECT name FROM suppliers ORDER BY name LIMIT 10)`,

	Fix: "Use parentheses to clarify whether ORDER BY/LIMIT applies to individual queries or the combined result.",
}

OrderByLimitWithUnion warns about ORDER BY/LIMIT ambiguity with set operations.

var PreferCoalesce = sql.RuleDef{
	ID:          "CV02",
	Name:        "convention.coalesce",
	Group:       "convention",
	Description: "Prefer COALESCE over IFNULL/NVL for better portability.",
	Severity:    core.SeverityHint,
	Check:       checkPreferCoalesce,

	Rationale: `COALESCE is ANSI SQL standard and works across all major databases. IFNULL 
(MySQL) and NVL (Oracle) are database-specific. Using COALESCE improves query 
portability and is more flexible as it can handle multiple arguments.`,

	BadExample: `SELECT
    IFNULL(phone, 'N/A') AS phone,
    NVL(email, 'unknown') AS email
FROM contacts`,

	GoodExample: `SELECT
    COALESCE(phone, 'N/A') AS phone,
    COALESCE(email, 'unknown') AS email
FROM contacts`,

	Fix: "Replace IFNULL or NVL with COALESCE for better SQL portability.",
}

PreferCoalesce recommends COALESCE over IFNULL/NVL.

var PreferLeftJoin = sql.RuleDef{
	ID:          "CV08",
	Name:        "convention.left_join",
	Group:       "convention",
	Description: "Prefer LEFT JOIN over RIGHT JOIN for consistency.",
	Severity:    core.SeverityHint,
	Check:       checkPreferLeftJoin,

	Rationale: `LEFT JOIN is more intuitive because it preserves all rows from the table 
you naturally read first (left to right). RIGHT JOIN can always be rewritten as 
LEFT JOIN by swapping table order. Consistently using LEFT JOIN improves readability.`,

	BadExample: `SELECT o.id, c.name
FROM orders o
RIGHT JOIN customers c ON c.id = o.customer_id`,

	GoodExample: `SELECT o.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id`,

	Fix: "Swap the table order and use LEFT JOIN instead of RIGHT JOIN.",
}

PreferLeftJoin recommends LEFT JOIN over RIGHT JOIN.

var PreferUsing = sql.RuleDef{
	ID:          "ST07",
	Name:        "structure.using",
	Group:       "structure",
	Description: "Prefer USING clause for simple equality joins on same-named columns.",
	Severity:    core.SeverityHint,
	Check:       checkPreferUsing,

	Rationale: `The USING clause is more concise than ON when joining tables on columns with identical names. 
It clearly communicates that the join is on matching column names and automatically deduplicates the join 
column in the result set.`,

	BadExample: `SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id`,

	GoodExample: `SELECT *
FROM orders o
JOIN customers c USING (customer_id)`,

	Fix: "Replace ON with USING when joining on columns that have the same name in both tables.",
}

PreferUsing recommends USING instead of ON for simple equality joins.

var QualifyColumns = sql.RuleDef{
	ID:          "RF02",
	Name:        "references.qualification",
	Group:       "references",
	Description: "Qualify column references in queries with multiple tables.",
	Severity:    core.SeverityWarning,
	Check:       checkQualifyColumns,

	Rationale: `In queries involving multiple tables, unqualified column names can be ambiguous. 
If two tables have a column with the same name, the query may fail or return unexpected results. 
Qualifying columns with table names or aliases makes the query explicit and prevents errors when schemas change.`,

	BadExample: `SELECT name, amount
FROM customers
JOIN orders ON customers.id = orders.customer_id`,

	GoodExample: `SELECT customers.name, orders.amount
FROM customers
JOIN orders ON customers.id = orders.customer_id`,

	Fix: "Prefix each column reference with its table name or alias.",
}

QualifyColumns recommends qualifying column references in multi-table queries.

var SelectColumnOrder = sql.RuleDef{
	ID:          "ST06",
	Name:        "structure.column_order",
	Group:       "structure",
	Description: "Wildcards should appear last in SELECT clause.",
	Severity:    core.SeverityHint,
	Check:       checkSelectColumnOrder,

	Rationale: `Placing explicit columns before wildcards improves readability and makes the query's output 
structure clearer. The explicitly named columns are typically the most important ones, so listing them 
first highlights their significance.`,

	BadExample: `SELECT *, created_at, updated_at
FROM orders`,

	GoodExample: `SELECT created_at, updated_at, *
FROM orders`,

	Fix: "Move wildcard expressions (* or table.*) to the end of the SELECT clause.",
}

SelectColumnOrder recommends putting wildcards last in SELECT.

var SelfAlias = sql.RuleDef{
	ID:          "AL09",
	Name:        "aliasing.self_alias",
	Group:       "aliasing",
	Description: "Table aliased to its own name is redundant.",
	Severity:    core.SeverityHint,
	Check:       checkSelfAlias,

	Rationale: `Aliasing a table to its own name (e.g., customers AS customers) adds 
verbosity without any benefit. It may indicate copy-paste errors or incomplete 
refactoring. Either use a shorter alias or remove the redundant alias entirely.`,

	BadExample: `SELECT customers.id, customers.name
FROM customers AS customers
WHERE customers.status = 'active'`,

	GoodExample: `SELECT customers.id, customers.name
FROM customers
WHERE customers.status = 'active'`,

	Fix: "Remove the redundant alias, or use a shorter meaningful alias if abbreviation is desired.",
}

SelfAlias warns about tables aliased to their own name.

var SimpleCaseConversion = sql.RuleDef{
	ID:          "ST02",
	Name:        "structure.simple_case",
	Group:       "structure",
	Description: "Searched CASE can be simplified to simple CASE expression.",
	Severity:    core.SeverityHint,
	Check:       checkSimpleCaseConversion,

	Rationale: `When all WHEN conditions compare the same column to different literal values using equality, 
a searched CASE can be rewritten as a simple CASE. Simple CASE expressions are more concise and clearly 
communicate the intent of mapping values from a single column.`,

	BadExample: `SELECT
  CASE
    WHEN status = 'A' THEN 'Active'
    WHEN status = 'I' THEN 'Inactive'
    WHEN status = 'P' THEN 'Pending'
  END AS status_label
FROM orders`,

	GoodExample: `SELECT
  CASE status
    WHEN 'A' THEN 'Active'
    WHEN 'I' THEN 'Inactive'
    WHEN 'P' THEN 'Pending'
  END AS status_label
FROM orders`,

	Fix: "Convert to simple CASE by moving the common column after CASE and removing it from WHEN clauses.",
}

SimpleCaseConversion suggests converting searched CASE to simple CASE when possible.

var UnionDistinct = sql.RuleDef{
	ID:          "AM02",
	Name:        "ambiguous.union",
	Group:       "ambiguous",
	Description: "UNION without ALL performs implicit DISTINCT which may be unintended.",
	Severity:    core.SeverityInfo,
	Check:       checkUnionDistinct,

	Rationale: `UNION (without ALL) automatically removes duplicate rows, which has 
performance implications and may not be the intended behavior. Explicitly using 
UNION ALL or UNION DISTINCT makes the intent clear and avoids accidental deduplication.`,

	BadExample: `SELECT name FROM customers
UNION
SELECT name FROM suppliers`,

	GoodExample: `-- If duplicates should be removed:
SELECT name FROM customers
UNION DISTINCT
SELECT name FROM suppliers

-- If duplicates should be kept:
SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers`,

	Fix: "Use UNION ALL if duplicates are acceptable, or UNION DISTINCT to make deduplication explicit.",
}

UnionDistinct warns about using UNION without ALL (implicit DISTINCT).

var UniqueColumnAlias = sql.RuleDef{
	ID:          "AL08",
	Name:        "aliasing.unique_column",
	Group:       "aliasing",
	Description: "Column aliases should be unique within SELECT clause.",
	Severity:    core.SeverityWarning,
	Check:       checkUniqueColumnAlias,

	Rationale: `Duplicate column aliases create ambiguity in the result set. Downstream 
consumers (reports, APIs, other queries) may not be able to reliably reference the 
correct column. Some databases will error, others will silently pick one column.`,

	BadExample: `SELECT
    first_name AS name,
    last_name AS name,
    company_name AS name
FROM contacts`,

	GoodExample: `SELECT
    first_name AS first_name,
    last_name AS last_name,
    company_name AS company_name
FROM contacts`,

	Fix: "Rename column aliases to be unique within the SELECT clause.",
}

UniqueColumnAlias warns about duplicate column aliases.

var UniqueTableAlias = sql.RuleDef{
	ID:          "AL04",
	Name:        "aliasing.unique_table",
	Group:       "aliasing",
	Description: "Table aliases should be unique within a query.",
	Severity:    core.SeverityError,
	Check:       checkUniqueTableAlias,

	Rationale: `Duplicate table aliases cause ambiguity when referencing columns. Most 
databases will reject queries with duplicate aliases. Even if accepted, it makes the 
query confusing and error-prone. Each table reference should have a unique alias.`,

	BadExample: `SELECT a.id, a.name
FROM customers a
JOIN orders a ON a.customer_id = a.id`,

	GoodExample: `SELECT c.id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id`,

	Fix: "Rename one of the duplicate aliases to be unique within the query.",
}

UniqueTableAlias warns about duplicate table aliases.

var UnusedCTE = sql.RuleDef{
	ID:          "ST03",
	Name:        "structure.unused_cte",
	Group:       "structure",
	Description: "CTE is defined but never referenced.",
	Severity:    core.SeverityWarning,
	Check:       checkUnusedCTE,

	Rationale: `Unused CTEs add complexity without benefit. They consume mental overhead 
for readers trying to understand the query, and may indicate incomplete refactoring 
or copy-paste errors. Removing them improves query clarity.`,

	BadExample: `WITH unused_cte AS (
    SELECT * FROM orders
),
active_customers AS (
    SELECT * FROM customers WHERE active = true
)
SELECT * FROM active_customers`,

	GoodExample: `WITH active_customers AS (
    SELECT * FROM customers WHERE active = true
)
SELECT * FROM active_customers`,

	Fix: "Remove the unused CTE definition, or reference it in your query if it was intended to be used.",
}

UnusedCTE warns about CTEs that are defined but never used.

var UnusedTableAlias = sql.RuleDef{
	ID:          "AL05",
	Name:        "aliasing.unused",
	Group:       "aliasing",
	Description: "Table alias is defined but not referenced.",
	Severity:    core.SeverityWarning,
	Check:       checkUnusedTableAlias,

	Rationale: `Unused table aliases add noise without providing clarity. They may indicate 
incomplete refactoring or copy-paste errors. If you alias a table, use that alias 
consistently to improve query readability.`,

	BadExample: `SELECT id, name, email
FROM customers c
WHERE status = 'active'`,

	GoodExample: `SELECT c.id, c.name, c.email
FROM customers c
WHERE c.status = 'active'`,

	Fix: "Either use the alias when referencing columns from this table, or remove the alias if it's not needed.",
}

UnusedTableAlias warns about defined but unused table aliases.