Skip to content

Latest commit

 

History

History
920 lines (552 loc) · 15.2 KB

rules.md

File metadata and controls

920 lines (552 loc) · 15.2 KB

Rules

The following rules are available in this create. This list is generated from the rules module in the source code and can be turned on or off and configured in the config file.

Rule Index

Rule Code Rule Name Description
AL01 aliasing.table Implicit/explicit aliasing of table.
AL02 aliasing.column Implicit/explicit aliasing of columns.
AL03 aliasing.expression Column expression without alias. Use explicit AS clause.
AL04 aliasing.unique.table Table aliases should be unique within each clause.
AL05 aliasing.unused Tables should not be aliased if that alias is not used.
AL06 aliasing.lenght Identify aliases in from clause and join conditions
AL07 aliasing.forbid Avoid table aliases in from clauses and join conditions.
AL08 layout.cte_newline Column aliases should be unique within each clause.
AL09 aliasing.self_alias.column Find self-aliased columns and fix them
AM01 ambiguous.distinct Ambiguous use of 'DISTINCT' in a 'SELECT' statement with 'GROUP BY'.
AM02 ambiguous.union Look for UNION keyword not immediately followed by DISTINCT or ALL
AM06 ambiguous.column_references Inconsistent column references in 'GROUP BY/ORDER BY' clauses.
CP01 capitalisation.keywords Inconsistent capitalisation of keywords.
CP02 capitalisation.identifiers Inconsistent capitalisation of unquoted identifiers.
CP03 capitalisation.functions Inconsistent capitalisation of function names.
CP04 capitalisation.literals Inconsistent capitalisation of boolean/null literal.
CP05 capitalisation.types Inconsistent capitalisation of datatypes.
CV02 convention.coalesce Use 'COALESCE' instead of 'IFNULL' or 'NVL'.
CV03 convention.select_trailing_comma Trailing commas within select clause
CV04 convention.count_rows Use consistent syntax to express "count number of rows".
LT01 layout.spacing Inappropriate Spacing.
LT02 layout.indent Incorrect Indentation.
LT03 layout.operators Operators should follow a standard for being before/after newlines.
LT04 layout.commas Leading/Trailing comma enforcement.
LT05 layout.long_lines Line is too long.
LT06 layout.functions Function name not immediately followed by parenthesis.
LT07 layout.cte_bracket 'WITH' clause closing bracket should be on a new line.
LT08 layout.cte_newline Blank line expected but not found after CTE closing bracket.
LT09 layout.select_targets Select targets should be on a new line unless there is only one select target.
LT10 layout.select_modifiers 'SELECT' modifiers (e.g. 'DISTINCT') must be on the same line as 'SELECT'.
LT11 layout.set_operators Set operators should be surrounded by newlines.
LT12 layout.end_of_file Files must end with a single trailing newline.
RF01 references.from References cannot reference objects not present in 'FROM' clause.
RF03 references.consistent References should be consistent in statements with a single table.
ST01 structure.else_null Do not specify 'else null' in a case when statement (redundant).
ST02 structure.simple_case Unnecessary 'CASE' statement.
ST03 structure.unused_cte Query defines a CTE (common-table expression) but does not use it.
ST08 structure.distinct Looking for DISTINCT before a bracket

Rule Details

aliasing.table

Implicit/explicit aliasing of table.

Code: AL01

Fixable: Yes

Anti-pattern

In this example, the alias voo is implicit.

SELECT
    voo.a
FROM foo voo

Best practice

Add AS to make the alias explicit.

SELECT
    voo.a
FROM foo AS voo

aliasing.column

Implicit/explicit aliasing of columns.

Code: AL02

Fixable: No

Anti-pattern

In this example, the alias for column a is implicit.

SELECT
  a alias_col
FROM foo

Best practice

Add the AS keyword to make the alias explicit.

SELECT
    a AS alias_col
FROM foo

aliasing.expression

Column expression without alias. Use explicit AS clause.

Code: AL03

Fixable: No

Anti-pattern

In this example, there is no alias for both sums.

SELECT
    sum(a),
    sum(b)
FROM foo

Best practice

Add aliases.

SELECT
    sum(a) AS a_sum,
    sum(b) AS b_sum
FROM foo

aliasing.unique.table

Table aliases should be unique within each clause.

Code: AL04

Fixable: No

Anti-pattern

In this example, the alias t is reused for two different tables:

SELECT
    t.a,
    t.b
FROM foo AS t, bar AS t

-- This can also happen when using schemas where the
-- implicit alias is the table name:

SELECT
    a,
    b
FROM
    2020.foo,
    2021.foo

Best practice

Make all tables have a unique alias.

SELECT
    f.a,
    b.b
FROM foo AS f, bar AS b

-- Also use explicit aliases when referencing two tables
-- with the same name from two different schemas.

SELECT
    f1.a,
    f2.b
FROM
    2020.foo AS f1,
    2021.foo AS f2

aliasing.unused

Tables should not be aliased if that alias is not used.

Code: AL05

Fixable: Yes

Anti-pattern

In this example, alias zoo is not used.

SELECT
    a
FROM foo AS zoo

Best practice

Use the alias or remove it. An unused alias makes code harder to read without changing any functionality.

SELECT
    zoo.a
FROM foo AS zoo

-- Alternatively...

SELECT
    a
FROM foo

aliasing.lenght

Identify aliases in from clause and join conditions

Code: AL06

Fixable: No

Anti-pattern

In this example, alias o is used for the orders table.

SELECT
    SUM(o.amount) as order_amount,
FROM orders as o

Best practice

Avoid aliases. Avoid short aliases when aliases are necessary.

See also: Rule_AL07.

SELECT
    SUM(orders.amount) as order_amount,
FROM orders

SELECT
    replacement_orders.amount,
    previous_orders.amount
FROM
    orders AS replacement_orders
JOIN
    orders AS previous_orders
    ON replacement_orders.id = previous_orders.replacement_id

aliasing.forbid

Avoid table aliases in from clauses and join conditions.

Code: AL07

Fixable: Yes

Anti-pattern

In this example, alias o is used for the orders table, and c is used for customers table.

SELECT
    COUNT(o.customer_id) as order_amount,
    c.name
FROM orders as o
JOIN customers as c on o.id = c.user_id

Best practice

Avoid aliases.

SELECT
    COUNT(orders.customer_id) as order_amount,
    customers.name
FROM orders
JOIN customers on orders.id = customers.user_id

-- Self-join will not raise issue

SELECT
    table1.a,
    table_alias.b,
FROM
    table1
    LEFT JOIN table1 AS table_alias ON
        table1.foreign_key = table_alias.foreign_key

layout.cte_newline

Column aliases should be unique within each clause.

Code: AL08

Fixable: No

Anti-pattern

In this example, alias o is used for the orders table, and c is used for customers table.

SELECT
    COUNT(o.customer_id) as order_amount,
    c.name
FROM orders as o
JOIN customers as c on o.id = c.user_id

Best practice

Avoid aliases.

SELECT
    COUNT(orders.customer_id) as order_amount,
    customers.name
FROM orders
JOIN customers on orders.id = customers.user_id

-- Self-join will not raise issue

SELECT
    table1.a,
    table_alias.b,
FROM
    table1
    LEFT JOIN table1 AS table_alias ON
        table1.foreign_key = table_alias.foreign_key

aliasing.self_alias.column

Find self-aliased columns and fix them

Code: AL09

Fixable: No

ambiguous.distinct

Ambiguous use of 'DISTINCT' in a 'SELECT' statement with 'GROUP BY'.

Code: AM01

Fixable: No

Anti-pattern

DISTINCT and `GROUP BY are conflicting.

SELECT DISTINCT
    a
FROM foo
GROUP BY a

Best practice

Remove DISTINCT or GROUP BY. In our case, removing GROUP BY is better.

SELECT DISTINCT
    a
FROM foo

ambiguous.union

Look for UNION keyword not immediately followed by DISTINCT or ALL

Code: AM02

Fixable: Yes

Anti-pattern

In this example, UNION DISTINCT should be preferred over UNION, because explicit is better than implicit.

SELECT a, b FROM table_1
UNION
SELECT a, b FROM table_2

Best practice

Specify DISTINCT or ALL after UNION (note that DISTINCT is the default behavior).

SELECT a, b FROM table_1
UNION DISTINCT
SELECT a, b FROM table_2

ambiguous.column_references

Inconsistent column references in 'GROUP BY/ORDER BY' clauses.

Code: AM06

Fixable: No

Anti-pattern

In this example, the ORRDER BY clause mixes explicit and implicit order by column references.

SELECT
    a, b
FROM foo
ORDER BY a, b DESC

Best practice

If any columns in the ORDER BY clause specify ASC or DESC, they should all do so.

SELECT
    a, b
FROM foo
ORDER BY a ASC, b DESC

capitalisation.keywords

Inconsistent capitalisation of keywords.

Code: CP01

Fixable: Yes

Anti-pattern

In this example, select is in lower-case whereas FROM is in upper-case.

select
    a
FROM foo

Best practice

Make all keywords either in upper-case or in lower-case.

SELECT
    a
FROM foo

-- Also good

select
    a
from foo

capitalisation.identifiers

Inconsistent capitalisation of unquoted identifiers.

Code: CP02

Fixable: Yes

Anti-pattern

In this example, unquoted identifier a is in lower-case but B is in upper-case.

select
    a,
    B
from foo

Best practice

Ensure all unquoted identifiers are either in upper-case or in lower-case.

select
    a,
    b
from foo

-- Also good

select
    A,
    B
from foo

capitalisation.functions

Inconsistent capitalisation of function names.

Code: CP03

Fixable: Yes

Anti-pattern

In this example, the two SUM functions don’t have the same capitalisation.

SELECT
    sum(a) AS aa,
    SUM(b) AS bb
FROM foo

Best practice

Make the case consistent.

SELECT
    sum(a) AS aa,
    sum(b) AS bb
FROM foo

capitalisation.literals

Inconsistent capitalisation of boolean/null literal.

Code: CP04

Fixable: Yes

Anti-pattern

In this example, null and false are in lower-case whereas TRUE is in upper-case.

select
    a,
    null,
    TRUE,
    false
from foo

Best practice

Ensure all literal null/true/false literals are consistently upper or lower case

select
    a,
    NULL,
    TRUE,
    FALSE
from foo

-- Also good

select
    a,
    null,
    true,
    false
from foo

capitalisation.types

Inconsistent capitalisation of datatypes.

Code: CP05

Fixable: Yes

Anti-pattern

In this example, int and unsigned are in lower-case whereas VARCHAR is in upper-case.

CREATE TABLE t (
    a int unsigned,
    b VARCHAR(15)
);

Best practice

Ensure all datatypes are consistently upper or lower case

CREATE TABLE t (
    a INT UNSIGNED,
    b VARCHAR(15)
);

convention.coalesce

Use 'COALESCE' instead of 'IFNULL' or 'NVL'.

Code: CV02

Fixable: No

Anti-pattern

IFNULL or NVL are used to fill NULL values.

SELECT ifnull(foo, 0) AS bar,
FROM baz;

SELECT nvl(foo, 0) AS bar,
FROM baz;

Best practice

Use COALESCE instead. COALESCE is universally supported, whereas Redshift doesn’t support IFNULL and BigQuery doesn’t support NVL. Additionally, COALESCE is more flexible and accepts an arbitrary number of arguments.

SELECT coalesce(foo, 0) AS bar,
FROM baz;

convention.select_trailing_comma

Trailing commas within select clause

Code: CV03

Fixable: No

Anti-pattern

In this example, the last selected column has a trailing comma.

SELECT
    a,
    b,
FROM foo

Best practice

Remove the trailing comma.

SELECT
    a,
    b
FROM foo

convention.count_rows

Use consistent syntax to express "count number of rows".

Code: CV04

Fixable: No

Anti-pattern

In this example, count(1) is used to count the number of rows in a table.

select
    count(1)
from table_a

Best practice

Use count(*) unless specified otherwise by config prefer_count_1, or prefer_count_0 as preferred.

select
    count(*)
from table_a

layout.spacing

Inappropriate Spacing.

Code: LT01

Fixable: No

layout.indent

Incorrect Indentation.

Code: LT02

Fixable: Yes

layout.operators

Operators should follow a standard for being before/after newlines.

Code: LT03

Fixable: Yes

layout.commas

Leading/Trailing comma enforcement.

Code: LT04

Fixable: Yes

layout.long_lines

Line is too long.

Code: LT05

Fixable: Yes

layout.functions

Function name not immediately followed by parenthesis.

Code: LT06

Fixable: Yes

layout.cte_bracket

'WITH' clause closing bracket should be on a new line.

Code: LT07

Fixable: No

layout.cte_newline

Blank line expected but not found after CTE closing bracket.

Code: LT08

Fixable: Yes

layout.select_targets

Select targets should be on a new line unless there is only one select target.

Code: LT09

Fixable: Yes

layout.select_modifiers

'SELECT' modifiers (e.g. 'DISTINCT') must be on the same line as 'SELECT'.

Code: LT10

Fixable: Yes

layout.set_operators

Set operators should be surrounded by newlines.

Code: LT11

Fixable: Yes

layout.end_of_file

Files must end with a single trailing newline.

Code: LT12

Fixable: Yes

references.from

References cannot reference objects not present in 'FROM' clause.

Code: RF01

Fixable: No

references.consistent

References should be consistent in statements with a single table.

Code: RF03

Fixable: Yes

structure.else_null

Do not specify 'else null' in a case when statement (redundant).

Code: ST01

Fixable: No

structure.simple_case

Unnecessary 'CASE' statement.

Code: ST02

Fixable: No

structure.unused_cte

Query defines a CTE (common-table expression) but does not use it.

Code: ST03

Fixable: No

structure.distinct

Looking for DISTINCT before a bracket

Code: ST08

Fixable: No