Rules Reference

Rules in SQLFluff are implemented as crawlers. These are entities which work their way through the parsed structure of a query to evaluate a particular rule or set of rules. The intent is that the definition of each specific rule should be really streamlined and only contain the logic for the rule itself, with all the other mechanics abstracted away.

Specific Rules

Register all the rule classes with their corresponding rulesets (just std currently).

class Rule_L001(code, description, **kwargs)

Unnecessary trailing whitespace.

sqlfluff fix compatible.

Anti-pattern
The • character represents a space.
 SELECT
     a
 FROM foo••
Best practice
Remove trailing spaces.
SELECT
    a
FROM foo
class Rule_L002(code, description, **kwargs)

Mixed Tabs and Spaces in single whitespace.

Configuration
tab_space_size: The number of spaces to consider equal to one tab. Used in the fixing step of this rule. Must be one of range(0, 100).

sqlfluff fix compatible.

This rule will fail if a single section of whitespace contains both tabs and spaces.

Anti-pattern
The • character represents a space and the → character represents a tab.
In this example, the second line contains two spaces and one tab.
 SELECT
 ••→a
 FROM foo
Best practice
Change the line to use spaces only.
 SELECT
 ••••a
 FROM foo
class Rule_L003(code, description, **kwargs)

Indentation not consistent with previous lines.

sqlfluff fix compatible.

Configuration
indent_unit: Whether to use tabs or spaces to add new indents. Must be one of [‘space’, ‘tab’].

lint_templated_tokens: Should lines starting with a templating placeholder such as {{blah}} have their indentation linted. Must be one of [True, False].

tab_space_size: The number of spaces to consider equal to one tab. Used in the fixing step of this rule. Must be one of range(0, 100).

Note

This rule used to be _”Indentation length is not a multiple of tab_space_size”_, but was changed to be much smarter.

Anti-pattern
The • character represents a space.
In this example, the third line contains five spaces instead of four.
 SELECT
 ••••a,
 •••••b
 FROM foo
Best practice
Change the indentation to use a multiple of four spaces.
 SELECT
 ••••a,
 ••••b
 FROM foo
class Rule_L004(code, description, **kwargs)

Incorrect indentation type.

sqlfluff fix compatible.

Configuration
indent_unit: Whether to use tabs or spaces to add new indents. Must be one of [‘space’, ‘tab’].

tab_space_size: The number of spaces to consider equal to one tab. Used in the fixing step of this rule. Must be one of range(0, 100).

Note 1: spaces are only fixed to tabs if the number of spaces in the indent is an integer multiple of the tab_space_size config. Note 2: fixes are only applied to indents at the start of a line. Indents after other text on the same line are not fixed.

Anti-pattern
Using tabs instead of spaces when indent_unit config set to spaces (default).
 select
 ••••a,
    b
 from foo
Best practice
Change the line to use spaces only.
 select
 ••••a,
 ••••b
 from foo
class Rule_L005(code, description, **kwargs)

Commas should not have whitespace directly before them.

sqlfluff fix compatible.

Unless it’s an indent. Trailing/leading commas are dealt with in a different rule.

Anti-pattern
The • character represents a space.
There is an extra space in line two before the comma.
 SELECT
     a,
     b
 FROM foo
Best practice
Remove the space before the comma.
SELECT
    a,
    b
FROM foo
class Rule_L006(code, description, **kwargs)

Operators should be surrounded by a single whitespace.

sqlfluff fix compatible.

Anti-pattern
In this example, there is a space missing space between the operator and ‘b’.
SELECT
    a +b
FROM foo
Best practice
Keep a single space.
SELECT
    a + b
FROM foo
class Rule_L007(code, description, **kwargs)

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

Anti-pattern
The • character represents a space.
If operator_new_lines = after (or unspecified, as this is the default)
In this example, the operator ‘+’ should not be at the end of the second line.
SELECT
    a +
    b
FROM foo
Best practice
If operator_new_lines = after (or unspecified, as this is the default)
Place the operator after the newline.
SELECT
    a
    + b
FROM foo
If operator_new_lines = before
Place the operator before the newline.
SELECT
    a +
    b
FROM foo
class Rule_L008(code, description, **kwargs)

Commas should be followed by a single whitespace unless followed by a comment.

sqlfluff fix compatible.

Anti-pattern
The • character represents a space.
In this example, there is no space between the comma and ‘zoo’.
SELECT
    *
FROM foo
WHERE a IN ('plop','zoo')
Best practice
Keep a single space after the comma.
 SELECT
     *
 FROM foo
 WHERE a IN ('plop','zoo')
class Rule_L009(code, description, **kwargs)

Files must end with a trailing newline.

sqlfluff fix compatible.

Anti-pattern
The content in file without ends without a trailing newline, the $ represents end of file.
 SELECT
     a
 FROM foo$

 -- Ending on an indented line means there is no newline at the end of the file, the • represents space.

 SELECT
 ••••a
 FROM
 ••••foo
 ••••$

 -- Ending on a semi-colon means the last line is not a newline.

 SELECT
     a
 FROM foo
 ;$
Best practice
Add trailing newline to the end, the $ character represents end of file.
 SELECT
     a
 FROM foo
 $

 -- Ensuring the last line is not indented so is just a newline.

 SELECT
 ••••a
 FROM
 ••••foo
 $

 -- Even when ending on a semi-colon, ensure there is a newline after

 SELECT
     a
 FROM foo
 ;
 $
class Rule_L010(code, description, **kwargs)

Inconsistent capitalisation of keywords.

sqlfluff fix compatible.

Configuration
capitalisation_policy: The capitalisation policy to enforce. Must be one of [‘consistent’, ‘upper’, ‘lower’, ‘capitalise’].

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
class Rule_L011(code, description, **kwargs)

Implicit/explicit aliasing of table.

sqlfluff fix compatible.

Aliasing of table to follow preference (explicit using an AS clause is default).

Anti-pattern
In this example, the alias ‘voo’ is implicit.
SELECT
    voo.a
FROM foo voo
Best practice
Add AS to make it explicit.
SELECT
    voo.a
FROM foo AS voo
class Rule_L012(code, description, **kwargs)

Implicit/explicit aliasing of columns.

Aliasing of columns to follow preference (explicit using an AS clause is default).

NB: This rule inherits its functionality from obj:Rule_L011 but is separate so that they can be enabled and disabled separately.

Anti-pattern
In this example, the alias for column ‘a’ is implicit.
SELECT
    a
FROM foo
Best practice
Add AS to make it explicit.
SELECT
    a AS alias_col
FROM foo
class Rule_L013(code, description, **kwargs)

Column expression without alias. Use explicit AS clause.

Configuration
allow_scalar: Whether or not to allow a single element in the select clause to be without an alias. Must be one of [True, False].

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
class Rule_L014(code, description, **kwargs)

Inconsistent capitalisation of unquoted identifiers.

Configuration
extended_capitalisation_policy: The capitalisation policy to enforce, extended with PascalCase. This is separate from capitalisation_policy as it should not be applied to keywords.. Must be one of [‘consistent’, ‘upper’, ‘lower’, ‘pascal’, ‘capitalise’].

unquoted_identifiers_policy: Types of unquoted identifiers to flag violations for. Must be one of [‘all’, ‘aliases’, ‘column_aliases’].

sqlfluff fix compatible.

The functionality for this rule is inherited from Rule_L010.

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
class Rule_L015(code, description, **kwargs)

DISTINCT used with parentheses.

sqlfluff fix compatible.

Anti-pattern
In this example, parenthesis are not needed and confuse
DISTINCT with a function. The parenthesis can also be misleading
in which columns they apply to.
SELECT DISTINCT(a), b FROM foo
Best practice
Remove parenthesis to be clear that the DISTINCT applies to
both columns.
SELECT DISTINCT a, b FROM foo
class Rule_L016(code, description, **kwargs)

Line is too long

sqlfluff fix compatible.

Configuration
ignore_comment_lines: Should lines that contain only whitespace and comments be ignored when linting line lengths. Must be one of [True, False].

indent_unit: Whether to use tabs or spaces to add new indents. Must be one of [‘space’, ‘tab’].

max_line_length: The maximum length of a line to allow without raising a violation. Must be one of range(0, 1000).

tab_space_size: The number of spaces to consider equal to one tab. Used in the fixing step of this rule. Must be one of range(0, 100).

class Rule_L017(code, description, **kwargs)

Function name not immediately followed by bracket.

sqlfluff fix compatible.

Anti-pattern
In this example, there is a space between the function and the parenthesis.
SELECT
    sum (a)
FROM foo
Best practice
Remove the space between the function and the parenthesis.
SELECT
    sum(a)
FROM foo
class Rule_L018(code, description, **kwargs)

WITH clause closing bracket should be aligned with WITH keyword.

sqlfluff fix compatible.

Anti-pattern
The • character represents a space.
In this example, the closing bracket is not aligned with WITH keyword.
 WITH zoo AS (
     SELECT a FROM foo
 ••••)

 SELECT * FROM zoo
Best practice
Remove the spaces to align the WITH keyword with the closing bracket.
WITH zoo AS (
    SELECT a FROM foo
)

SELECT * FROM zoo
class Rule_L019(code, description, **kwargs)

Leading/Trailing comma enforcement.

sqlfluff fix compatible.

Configuration
comma_style: The comma style to to enforce. Must be one of [‘leading’, ‘trailing’].

Anti-pattern
There is a mixture of leading and trailing commas.
SELECT
    a
    , b,
    c
FROM foo
Best practice
By default sqlfluff prefers trailing commas, however it
is configurable for leading commas. Whichever option you chose
it does expect you to be consistent.
SELECT
    a,
    b,
    c
FROM foo

-- Alternatively, set the configuration file to 'leading'
-- and then the following would be acceptable:

SELECT
    a
    , b
    , c
FROM foo
class Rule_L020(code, description, **kwargs)

Table aliases should be unique within each clause.

Anti-pattern
In this example, the alias ‘t’ is reused for two different ables:
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 alias's when referencing two tables with same name from two different schemas

SELECT
    f1.a,
    f2.b
FROM
    2020.foo AS f1,
    2021.foo AS f2
class Rule_L021(code, description, **kwargs)

Ambiguous use of DISTINCT in select statement with GROUP BY.

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
class Rule_L022(code, description, **kwargs)

Blank line expected but not found after CTE closing bracket.

sqlfluff fix compatible.

Anti-pattern
There is no blank line after the CTE closing bracket. In queries with many
CTEs this hinders readability.
WITH plop AS (
    SELECT * FROM foo
)
SELECT a FROM plop
Best practice
Add a blank line.
WITH plop AS (
    SELECT * FROM foo
)

SELECT a FROM plop
class Rule_L023(code, description, **kwargs)

Single whitespace expected after AS in WITH clause.

sqlfluff fix compatible.

Anti-pattern
WITH plop AS(
    SELECT * FROM foo
)

SELECT a FROM plop
Best practice
The • character represents a space.
Add a space after AS, to avoid confusing
it for a function.
 WITH plop AS(
     SELECT * FROM foo
 )

 SELECT a FROM plop
class Rule_L024(code, description, **kwargs)

Single whitespace expected after USING in JOIN clause.

sqlfluff fix compatible.

Anti-pattern
SELECT b
FROM foo
LEFT JOIN zoo USING(a)
Best practice
The • character represents a space.
Add a space after USING, to avoid confusing it
for a function.
 SELECT b
 FROM foo
 LEFT JOIN zoo USING(a)
expand_children: Optional[List[str]] = None
class Rule_L025(code, description, **kwargs)

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

sqlfluff fix compatible.

Anti-pattern
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
class Rule_L026(code, description, **kwargs)

References cannot reference objects not present in FROM clause.

Anti-pattern
In this example, the reference ‘vee’ has not been declared.
SELECT
    vee.a
FROM foo
Best practice
Remove the reference.
SELECT
    a
FROM foo
class Rule_L027(code, description, **kwargs)

References should be qualified if select has more than one referenced table/view.

NB: Except if they’re present in a USING clause.

Anti-pattern
In this example, the reference ‘vee’ has not been declared
and the variables ‘a’ and ‘b’ are potentially ambiguous.
SELECT a, b
FROM foo
LEFT JOIN vee ON vee.a = foo.a
Best practice
Add the references.
SELECT foo.a, vee.b
FROM foo
LEFT JOIN vee ON vee.a = foo.a
class Rule_L028(code, description, **kwargs)

References should be consistent in statements with a single table.

Configuration
single_table_references: The expectation for references in single-table select. Must be one of [‘consistent’, ‘qualified’, ‘unqualified’].

Anti-pattern
In this example, only the field b is referenced.
SELECT
    a,
    foo.b
FROM foo
Best practice
Remove all the reference or reference all the fields.
SELECT
    a,
    b
FROM foo

-- Also good

SELECT
    foo.a,
    foo.b
FROM foo
class Rule_L029(code, description, **kwargs)

Keywords should not be used as identifiers.

Configuration
unquoted_identifiers_policy: Types of unquoted identifiers to flag violations for. Must be one of [‘all’, ‘aliases’, ‘column_aliases’].

Anti-pattern
In this example, SUM function is used as an alias.
SELECT
    sum.a
FROM foo AS sum
Best practice
Avoid keywords as the name of an alias.
SELECT
    vee.a
FROM foo AS vee
class Rule_L030(code, description, **kwargs)

Inconsistent capitalisation of function names.

Configuration
capitalisation_policy: The capitalisation policy to enforce. Must be one of [‘consistent’, ‘upper’, ‘lower’, ‘capitalise’].

sqlfluff fix compatible.

The functionality for this rule is inherited from Rule_L010.

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
class Rule_L031(code, description, **kwargs)

Avoid table aliases in from clauses and join conditions.

sqlfluff fix compatible.

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
    table.a,
    table_alias.b,
FROM
    table
    LEFT JOIN table AS table_alias ON table.foreign_key = table_alias.foreign_key
class TableAliasInfo(table_ref: sqlfluff.core.parser.segments.base.BaseSegment, whitespace_ref: sqlfluff.core.parser.segments.base.BaseSegment, alias_exp_ref: sqlfluff.core.parser.segments.base.BaseSegment, alias_identifier_ref: sqlfluff.core.parser.segments.base.BaseSegment)

Structure yielded by_filter_table_expressions().

property alias_exp_ref

Alias for field number 2

property alias_identifier_ref

Alias for field number 3

property table_ref

Alias for field number 0

property whitespace_ref

Alias for field number 1

class Rule_L032(code, description, **kwargs)

Prefer specifying join keys instead of using “USING”.

Anti-pattern
SELECT
    table_a.field_1,
    table_b.field_2
FROM
    table_a
INNER JOIN table_b USING (id)
Best practice
Specify the keys directly
SELECT
    table_a.field_1,
    table_b.field_2
FROM
    table_a
INNER JOIN table_b
    ON table_a.id = table_b.id
class Rule_L033(code, description, **kwargs)

UNION [DISTINCT|ALL] is preferred over just UNION.

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
class Rule_L034(code, description, **kwargs)

Use wildcards then simple targets before calculations and aggregates in select statements.

sqlfluff fix compatible.

Anti-pattern
select
    a,
    *,
    row_number() over (partition by id order by date) as y,
    b
from x
Best practice
Order “select” targets in ascending complexity
select
    *,
    a,
    b,
    row_number() over (partition by id order by date) as y
from x
class Rule_L035(code, description, **kwargs)

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

sqlfluff fix compatible.

Anti-pattern
select
    case
        when name like '%cat%' then 'meow'
        when name like '%dog%' then 'woof'
        else null
    end
from x
Best practice
Omit “else null”
select
    case
        when name like '%cat%' then 'meow'
        when name like '%dog%' then 'woof'
    end
from x
class Rule_L036(code, description, **kwargs)

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

sqlfluff fix compatible.

Anti-pattern
select
    *
from x
Best practice
select
    a,
    b,
    c
from x
class Rule_L037(code, description, **kwargs)

Ambiguous ordering directions for columns in order by clause.

sqlfluff fix compatible.

Anti-pattern
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
class Rule_L038(code, description, **kwargs)

Trailing commas within select clause.

Configuration
select_clause_trailing_comma: Should trailing commas within select clauses be required or forbidden. Must be one of [‘forbid’, ‘require’].

sqlfluff fix compatible.

For some database backends this is allowed. For some users this may be something they wish to enforce (in line with python best practice). Many database backends regard this as a syntax error, and as such the sqlfluff default is to forbid trailing commas in the select clause.

Anti-pattern
SELECT
    a, b,
FROM foo
Best practice
SELECT
    a, b
FROM foo
class Rule_L039(code, description, **kwargs)

Unnecessary whitespace found.

sqlfluff fix compatible.

Anti-pattern
SELECT
    a,        b
FROM foo
Best practice
Unless an indent or preceeding a comment, whitespace should
be a single space.
SELECT
    a, b
FROM foo
class Rule_L040(code, description, **kwargs)

Inconsistent capitalisation of boolean/null literal.

Configuration
capitalisation_policy: The capitalisation policy to enforce. Must be one of [‘consistent’, ‘upper’, ‘lower’, ‘capitalise’].

sqlfluff fix compatible.

The functionality for this rule is inherited from Rule_L010.

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 cases are used consistently
select
    a,
    NULL,
    TRUE,
    FALSE
from foo

-- Also good

select
    a,
    null,
    true,
    false
from foo
class Rule_L041(code, description, **kwargs)

SELECT clause modifiers such as DISTINCT must be on the same line as SELECT.

sqlfluff fix compatible.

Anti-pattern
select
    distinct a,
    b
from x
Best practice
select distinct
    a,
    b
from x
class Rule_L042(code, description, **kwargs)

Join/From clauses should not contain subqueries. Use CTEs instead.

Configuration
forbid_subquery_in: Which clauses should be linted for subqueries. Must be one of [‘join’, ‘from’, ‘both’].

By default this rule is configured to allow subqueries within FROM clauses but not within JOIN clauses. If you prefer a stricter lint then this is configurable.

NB: Some dialects don’t allow CTEs, and for those dialects this rule makes no sense and should be disabled.

Anti-pattern
select
    a.x, a.y, b.z
from a
join (
    select x, z from b
) using(x)
Best practice
with c as (
    select x, z from b
)
select
    a.x, a.y, c.z
from a
join c using(x)
class Rule_L043(code, description, **kwargs)

Unnecessary case when statement. Use the “when” condition itself.

sqlfluff fix compatible.

If a case when else statement returns booleans, we can reduce it to the “when” condition.

Anti-pattern
select
    case
        when fab > 0 then true else false end as is_fab
from fancy_table
Best practice
Reduce to “when” condition. Wrap with “coalesce”. If necessary, add
a “not” operator at the beginning.
select
    coalesce(fab > 0, false) as is_fab
from fancy_table
class Rule_L044(code, description, **kwargs)

Query produces an unknown number of result columns.

Anti-pattern
Querying all columns using * produces a query result where the number
or ordering of columns changes if the upstream table’s schema changes.
This should generally be avoided because it can cause slow performance,
cause important schema changes to go undetected, or break production code.
For example:
* If a query does SELECT t.* and is expected to return columns a, b,
and c, the actual columns returned will be wrong/different if columns
are added to or deleted from the input table.
* UNION and DIFFERENCE clauses require the inputs have the same number
of columns (and compatible types).
* JOIN queries may break due to new column name conflicts, e.g. the
query references a column “c” which initially existed in only one input
table but a column of the same name is added to another table.
* CREATE TABLE (<<column schema>>) AS SELECT *
WITH cte AS (
    SELECT * FROM foo
)

SELECT * FROM cte
UNION
SELECT a, b FROM t
Best practice
Somewhere along the “path” to the source data, specify columns explicitly.
WITH cte AS (
    SELECT * FROM foo
)

SELECT a, b FROM cte
UNION
SELECT a, b FROM t
class Rule_L045(code, description, **kwargs)

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

sqlfluff fix compatible.

Anti-pattern
Defining a CTE that is not used by the query is harmless, but it means
the code is unnecesary and could be removed.
WITH cte1 AS (
  SELECT a
  FROM t
),
cte2 AS (
  SELECT b
  FROM u
)

SELECT *
FROM cte1
Best practice
Remove unused CTEs.
WITH cte1 AS (
  SELECT a
  FROM t
)

SELECT *
FROM cte1
class Rule_L046(code, description, **kwargs)

Jinja tags should have a single whitespace on either side.

Anti-pattern
Jinja tags with either no whitespace or very long whitespace
are hard to read.
 SELECT {{    a     }} from {{ref('foo')}}
Best practice
A single whitespace surrounding Jinja tags, alternatively
longer gaps containing newlines are acceptable.
 SELECT {{ a }} from {{ ref('foo') }};
 SELECT {{ a }} from {{
     ref('foo')
 }};
class Rule_L047(code, description, **kwargs)

Use consistent syntax to express “count number of rows”.

Configuration
prefer_count_0: Should count(0) be preferred over count(*) and count(1)?. Must be one of [True, False].

prefer_count_1: Should count(1) be preferred over count(*) and count(0)?. Must be one of [True, False].

sqlfluff fix compatible.

Note

If both prefer_count_1 and prefer_count_0 are set to true then prefer_count_1 has precedence.

COUNT(*), COUNT(1), and even COUNT(0) are equivalent syntaxes in many SQL engines due to optimizers interpreting these instructions as “count number of rows in result”.

The ANSI-92 spec mentions the COUNT(*) syntax specifically as having a special meaning:

If COUNT(*) is specified, then the result is the cardinality of T.

So by default, SQLFluff enforces the consistent use of COUNT(*).

If the SQL engine you work with, or your team, prefers COUNT(1) or COUNT(0) over COUNT(*) you can configure this rule to consistently enforce your preference.

Anti-pattern
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
class Rule_L048(code, description, **kwargs)

Quoted literals should be surrounded by a single whitespace.

sqlfluff fix compatible.

Anti-pattern
In this example, there is a space missing space between the string ‘foo’
and the keyword AS.
SELECT
    'foo'AS bar
FROM foo
Best practice
Keep a single space.
SELECT
    'foo' AS bar
FROM foo

Implementation

class RuleSet(name, config_info)

Class to define a ruleset.

A rule set is instantiated on module load, but the references to each of its classes are instantiated at runtime. This means that configuration values can be passed to those rules live and be responsive to any changes in configuration from the path that the file is in.

Rules should be fetched using the get_rulelist() command which also handles any filtering (i.e. whitelisting and blacklisting).

New rules should be added to the instance of this class using the register() decorator. That decorator registers the class, but also performs basic type and name-convention checks.

The code for the rule will be parsed from the name, the description from the docstring. The eval function is assumed that it will be overriden by the subclass, and the parent class raises an error on this function if not overriden.

copy()

Return a copy of self with a separate register.

get_rulelist(config) List[sqlfluff.core.rules.base.BaseRule]

Use the config to return the appropriate rules.

We use the config both for whitelisting and blacklisting, but also for configuring the rules given the given config.

Returns

list of instantiated BaseRule.

register(cls, plugin=None)

Decorate a class with this to add it to the ruleset.

@myruleset.register
class Rule_L001(BaseRule):
    "Description of rule."

    def eval(self, **kwargs):
        return LintResult()

We expect that rules are defined as classes with the name Rule_XXXX where XXXX is of the form LNNN, where L is a letter (literally L for linting by default) and N is a three digit number.

If this receives classes by any other name, then it will raise a ValueError.

property valid_rule_name_regex

Defines the accepted pattern for rule names.

The first group captures the plugin name (optional), which must be capitalized. The second group captures the rule code.

Examples of valid rule names: * Rule_PluginName_L001 * Rule_L001

class BaseRule(code, description, **kwargs)

The base class for a rule.

Parameters
  • code (str) – The identifier for this rule, used in inclusion or exclusion.

  • description (str) – A human readable description of what this rule does. It will be displayed when any violations are found.

_eval(**kwargs)

Evaluate this rule against the current context.

This should indicate whether a linting violation has occurred and/or whether there is something to remember from this evaluation.

Note that an evaluate function should always accept **kwargs, but if it relies on any available kwargs, it should explicitly call them out at definition.

Returns

LintResult or None.

The reason that this method is called _eval() and not eval is a bit of a hack with sphinx autodoc, to make it so that the rule documentation auto-generates nicely.

crawl(segment, dialect, parent_stack=None, siblings_pre=None, siblings_post=None, raw_stack=None, memory=None, fname=None, templated_file: Optional[TemplatedFile] = None)

Recursively perform the crawl operation on a given segment.

Returns

A tuple of (vs, raw_stack, fixes, memory)

static filter_meta(segments, keep_meta=False)

Filter the segments to non-meta.

Or optionally the opposite if keep_meta is True.

classmethod get_parent_of(segment, root_segment)

Return the segment immediately containing segment.

NB: This is recursive.

Parameters
  • segment – The segment to look for.

  • root_segment – Some known parent of the segment we’re looking for (although likely not the direct parent in question).

static matches_target_tuples(seg: sqlfluff.core.parser.segments.base.BaseSegment, target_tuples: List[Tuple[str, str]])

Does the given segment match any of the given type tuples.

class LintResult(anchor=None, fixes=None, memory=None, description=None)

A class to hold the results of a rule evaluation.

Parameters
  • anchor (BaseSegment, optional) – A segment which represents the position of the a problem. NB: Each fix will also hold its own reference to position, so this position is mostly for alerting the user to where the problem is.

  • fixes (list of LintFix, optional) – An array of any fixes which would correct this issue. If not present then it’s assumed that this issue will have to manually fixed.

  • memory (dict, optional) – An object which stores any working memory for the rule. The memory returned in any LintResult will be passed as an input to the next segment to be crawled.

  • description (str, optional) – A description of the problem identified as part of this result. This will override the description of the rule as what gets reported to the user with the problem if provided.

to_linting_error(rule)

Convert a linting result to a SQLLintError if appropriate.

class LintFix(edit_type, anchor: sqlfluff.core.parser.segments.base.BaseSegment, edit=None)

A class to hold a potential fix to a linting violation.

Parameters
  • edit_type (str) – One of create, edit, delete to indicate the kind of fix this represents.

  • anchor (BaseSegment) – A segment which represents the position that this fix should be applied at. For deletions it represents the segment to delete, for creations it implies the position to create at (with the existing element at this position to be moved after the edit), for an edit it implies the segment to be replaced.

  • edit (BaseSegment, optional) – For edit and create fixes, this hold the segment, or iterable of segments to create or replace at the given anchor point.

is_trivial()

Return true if the fix is trivial.

Trivial edits are: - Anything of zero length. - Any edits which result in themselves.

Removing these makes the routines which process fixes much faster.

The _eval function of each rule should take enough arguments that it can evaluate the position of the given segment in relation to its neighbors, and that the segment which finally “triggers” the error, should be the one that would be corrected OR if the rule relates to something that is missing, then it should flag on the segment FOLLOWING, the place that the desired element is missing.

Inline Ignoring Errors

SQLFluff features inline error ignoring. For example, the following will ignore the lack of whitespace surrounding the * operator.

a.a*a.b AS bad_1  -- noqa: L006

Multiple rules can be ignored by placing them in a comma-delimited list.

a.a *  a.b AS bad_2,  -- noqa: L007, L006

It is also possible to ignore non-rule based errors, and instead opt to ignore templating (TMP) & parsing (PRS) errors.

WHERE dt >= DATE_ADD(CURRENT_DATE(), INTERVAL -2 DAY) -- noqa: PRS

Should the need arise, not specifying specific rules to ignore will ignore all rules on the given line.

a.a*a.b AS bad_3  -- noqa

Ignoring line ranges

Similar to pylint’s “pylint” directive”, ranges of lines can be ignored by adding -- noqa:disable=<rule>[,...] | all to the line. Following this directive, specified rules (or all rules, if “all” was specified) will be ignored until a corresponding – noqa:enable=<rule>[,…] | all directive.

-- Ignore rule L012 from this line forward
SELECT col_a a FROM foo --noqa: disable=L012

-- Ignore all rules from this line forward
SELECT col_a a FROM foo --noqa: disable=all

-- Enforce all rules from this line forward
SELECT col_a a FROM foo --noqa: enable=all