Ambiguous Rules

This section contains 9 rule(s) related to ambiguous.

Rule Summary

Code	Name	Aliases	Fix Compatible
AM01	ambiguous.distinct	L021	❌
AM02	ambiguous.union	L033	✅
AM03	ambiguous.order_by	L037	✅
AM04	ambiguous.column_count	L044	❌
AM05	ambiguous.join	L051	✅
AM06	ambiguous.column_references	L054	❌
AM07	ambiguous.set_columns	L068	❌
AM08	ambiguous.join_condition	-	✅
AM09	ambiguous.order_by_limit	-	❌

---

AM01: ambiguous.distinct

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

Property	Value
Name	ambiguous.distinct
Aliases	L021
Groups	all, core, ambiguous
Auto-fixable	No ❌

When using GROUP BY a DISTINCT clause should not be necessary as every non-distinct SELECT clause must be included in the GROUP BY clause.

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

**Groups**: `all`, `core`, `ambiguous``

---

AM02: ambiguous.union

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

Property	Value
Name	ambiguous.union
Aliases	L033
Groups	all, core, ambiguous
Auto-fixable	Yes ✅

NOTE

This rule is only enabled for dialects that support UNION and UNION DISTINCT (ansi, bigquery, clickhouse, databricks, db2, hive, mysql, redshift, snowflake, and trino).

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

---

AM03: ambiguous.order_by

Ambiguous ordering directions for columns in order by clause.

Property	Value
Name	ambiguous.order_by
Aliases	L037
Groups	all, ambiguous
Auto-fixable	Yes ✅

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

---

AM04: ambiguous.column_count

Query produces an unknown number of result columns.

Property	Value
Name	ambiguous.column_count
Aliases	L044
Groups	all, ambiguous
Auto-fixable	No ❌

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

---

AM05: ambiguous.join

Join clauses should be fully qualified.

Property	Value
Name	ambiguous.join
Aliases	L051
Groups	all, ambiguous
Auto-fixable	Yes ✅

By default this rule is configured to enforce fully qualified INNER JOIN clauses, but not [LEFT/RIGHT/FULL] OUTER JOIN. If you prefer a stricter lint then this is configurable.

Anti-pattern

A join is used without specifying the kind of join.

SELECT
    foo
FROM bar
JOIN baz;

Best practice

Use INNER JOIN rather than JOIN.

SELECT
    foo
FROM bar
INNER JOIN baz;

Configuration

Option	Description
fully_qualify_join_types	Which types of JOIN clauses should be fully qualified? Must be one of ['inner', 'outer', 'both'].

---

AM06: ambiguous.column_references

Inconsistent column references in GROUP BY/ORDER BY clauses.

Property	Value
Name	ambiguous.column_references
Aliases	L054
Groups	all, core, ambiguous
Auto-fixable	No ❌

NOTE

ORDER BY clauses from WINDOW clauses are ignored by this rule.

Anti-pattern

A mix of implicit and explicit column references are used in a GROUP BY clause.

SELECT
    foo,
    bar,
    sum(baz) AS sum_value
FROM fake_table
GROUP BY
    foo, 2;

-- The same also applies to column
-- references in ORDER BY clauses.

SELECT
    foo,
    bar
FROM fake_table
ORDER BY
    1, bar;

Best practice

Reference all GROUP BY/ORDER BY columns either by name or by position.

-- GROUP BY: Explicit
SELECT
    foo,
    bar,
    sum(baz) AS sum_value
FROM fake_table
GROUP BY
    foo, bar;

-- ORDER BY: Explicit
SELECT
    foo,
    bar
FROM fake_table
ORDER BY
    foo, bar;

-- GROUP BY: Implicit
SELECT
    foo,
    bar,
    sum(baz) AS sum_value
FROM fake_table
GROUP BY
    1, 2;

-- ORDER BY: Implicit
SELECT
    foo,
    bar
FROM fake_table
ORDER BY
    1, 2;

Configuration

Option	Description
group_by_and_order_by_style	The expectation for using explicit column name references or implicit positional references. Must be one of ['consistent', 'implicit', 'explicit'].

---

AM07: ambiguous.set_columns

Queries within set query produce different numbers of columns.

Property	Value
Name	ambiguous.set_columns
Aliases	L068
Groups	all, ambiguous
Auto-fixable	No ❌

Anti-pattern

When writing set expressions, all queries must return the same number of columns.

WITH cte AS (
    SELECT
        a,
        b
    FROM foo
)
SELECT * FROM cte
UNION
SELECT
    c,
    d,
    e
 FROM t

Best practice

Always specify columns when writing set queries and ensure that they all seleect same number of columns

WITH cte AS (
    SELECT a, b FROM foo
)
SELECT
    a,
    b
FROM cte
UNION
SELECT
    c,
    d
FROM t

---

AM08: ambiguous.join_condition

Implicit cross join detected.

Property	Value
Name	ambiguous.join_condition
Groups	all, ambiguous
Auto-fixable	Yes ✅

Anti-pattern

Cross joins are valid, but rare in the wild - and more often created by mistake than on purpose. This rule catches situations where a cross join has been specified, but not explicitly and so the risk of a mistaken cross join is highly likely.

SELECT
    foo
FROM bar
JOIN baz;

Best practice

Use CROSS JOIN.

SELECT
    foo
FROM bar
CROSS JOIN baz;

---

AM09: ambiguous.order_by_limit

Use of LIMIT and OFFSET without ORDER BY may lead to non-deterministic results.

Property	Value
Name	ambiguous.order_by_limit
Groups	all, ambiguous
Auto-fixable	No ❌

When using LIMIT or OFFSET, it's generally recommended to include an ORDER BY clause to ensure deterministic results.

Anti-pattern

The following query has LIMIT and OFFSET without ORDER BY, which may return different results in successive executions.

SELECT *
FROM foo
LIMIT 10 OFFSET 5;

Best practice

Include an ORDER BY clause:

SELECT *
FROM foo
ORDER BY id
LIMIT 10 OFFSET 5;

---