Skip to content

SQL engine v2 pushes = NULL, <> NULL and IN lists with NULL down as conditions instead of matching no rows #3931

Open

Description

@kriszyp

Problem

SQL engine v2 (sql.engine: new, and auto when it does not fall back) pushes comparisons against a NULL literal down as Resource conditions. Under SQL three-valued logic each is UNKNOWN for every row, so it should match nothing; the legacy engine returns nothing for them.

SQL Pushed as Rows today (v2) SQL / legacy
b = NULL equals with an undefined value error or explicit-null rows, by attribute type none
b <> NULL / b != NULL ne branches plus the ne null guard present non-null rows none
b NOT IN (1, NULL) ne 1, ne undefined, ne null present rows other than 1 none

A NULL literal normalizes to { kind: 'literal', value: undefined } (AlaSQL's NullValue has no value), so the lowering never sees null.

Ranges against NULL (<, <=, >, >=, BETWEEN … NULL …) already stay residual after Match null and omitted values the same way whether a condition leads the scan or filters, so the expression evaluator answers them.

Where

sqlEngine/optimizer/whereToConditions.ts leafToCondition (binop and in cases).

Fix

Leave =/<> against NULL and an IN list containing NULL as residual, as the range case does. IS [NOT] NULL is the SQL spelling for a null test and is unaffected. Pin it with the SQL matrix in unitTests/resources/nullOmittedScanFilterParity.test.js.

Review-Deferral: f5b0507e1d2c3e18 severity=minor category=correctness
Review-Deferral: 79085ea064e892b8 severity=major category=correctness

🤖 Generated by Claude Opus 5.5 (Claude Code); posted via @kriszyp.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    Fields

    Priority

    P3

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions