E0034 case-enum-missing-labels

A CASE expression over an enum column misses some labels and has no ELSE clause.

What it reports

A simple CASE <enum column> WHEN 'label' ... or searched CASE WHEN <enum column> = 'label' ... that does not handle every enum value declared in the schema, when no ELSE clause is provided.

When a CASE expression has no ELSE clause, any unhandled values silently evaluate to NULL. This is especially prone to bugs after schema migrations (such as ALTER TYPE ... ADD VALUE), where a newly added enum variant silently falls through existing CASE expressions throughout the codebase.

Example

Given the schema:

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');
CREATE TABLE orders (id INT PRIMARY KEY, status order_status NOT NULL);

Simple CASE

SELECT CASE status
  WHEN 'pending' THEN 'Waiting'
  WHEN 'paid' THEN 'Paid'
  WHEN 'shipped' THEN 'On its way'
END
FROM orders;

warning[E0034]: CASE over enum column 'orders.status' does not handle: 'cancelled'
  --> q.sql:1:8
    |
  1 | SELECT CASE status
    |        ^^^^^^^^^^^
    = help: Add WHEN branches for the missing labels or an ELSE clause to handle them.

Searched CASE

SELECT CASE
  WHEN status = 'pending' THEN 'Waiting'
  WHEN status = 'paid' THEN 'Paid'
  WHEN status = 'shipped' THEN 'On its way'
END
FROM orders;

warning[E0034]: CASE over enum column 'orders.status' does not handle: 'cancelled'
  --> q.sql:1:8
    |
  1 | SELECT CASE
    |        ^^^^
    = help: Add WHEN branches for the missing labels or an ELSE clause to handle them.

How to fix

• Handle the missing labels explicitly with WHEN:

  SELECT CASE status
    WHEN 'pending' THEN 'Waiting'
    WHEN 'paid' THEN 'Paid'
    WHEN 'shipped' THEN 'On its way'
    WHEN 'cancelled' THEN 'Cancelled'
  END
  FROM orders;
  

• Or provide an ELSE clause to define the fallback value:

  SELECT CASE status
    WHEN 'pending' THEN 'Waiting'
    WHEN 'paid' THEN 'Paid'
    WHEN 'shipped' THEN 'On its way'
    ELSE 'Unknown'
  END
  FROM orders;
  

Notes

• Works for both PostgreSQL named enum types (CREATE TYPE ... AS ENUM) and MySQL inline ENUM(...) columns.
• Expressions that provide an ELSE branch are considered covered and remain clean.
• This rule is opt-in (pedantic) and disabled by default.

Configuring

# sqlsift.toml
[rules]
case-enum-missing-labels = "warn"   # or "error"

Or enable all pedantic rules from the command line:

sqlsift check -W pedantic

Or for a single line: -- sqlsift:disable E0034.