Skip to content

duckdb: a masked scalar variable builds invalid SQL — the keyless absence restriction #482

Closed

Description

@FBumann

The duckdb half of #340, which was fixed on the polars engine only (#480).
Filed rather than fixed because it is latent today — but it stops being latent
the moment #399 merges main in, and #399 is what makes duckdb the default.

The behaviour

A variable declared foreach: [] with a where restricts every row of every
constraint that names it, all or nothing. The absence restriction in
relational/engines/duck/compiler.py builds that as WHERE EXISTS over the
presence relation, keyed by the restriction's dims — and a scalar has none, so
the key list and the join predicate both come out empty:

SELECT … FROM rel l WHERE EXISTS
  (SELECT 1 FROM (SELECT DISTINCT  FROM presence p) AS r WHERE )

Empty select list, empty WHERE. duckdb rejects it as a syntax error, so this
fails loudly — the opposite of the polars failure it mirrors, which read a
scalar's presence as always-present and silently kept a constraint the file does
not contain.

What is already right

Worth stating, because it narrows the fix to one place:

  • Presence construction already handles the empty case — it falls back to
    the UNIT column when there are no dims, which is what the SQL lane needed
    all along.
  • Restriction collection never had polars' not p.dims skip, so a
    dimensionless presence already reaches the restriction rather than being
    dropped on the way.

Only the join itself is wrong.

The fix

The same shape as #480: keyed, it stays a semi-join; keyless, it becomes a
cross join
, which is the honest reading of "does this scalar exist at all" —
every row survives a present scalar, none survives an absent one. In SQL that is
WHERE EXISTS (SELECT 1 FROM presence) with no correlation, which is simpler
than the keyed form rather than harder.

Check the labeller's restriction loop for the same empty-key assumption while
there; the polars engine had it in both places.

How to reproduce, once it is reachable

test_a_masked_out_scalar_variable_drops_the_row_that_uses_it in
tests/test_relational.py and the masked out / masked in pair in
tests/test_resolution_parity.py both cover this — run them under
pytest --engine duckdb. They cannot fail on main today because main has no
duck engine, and they cannot fail on #399 today because it still carries the
load-time refusal #480 removed. That gap closes on the merge, which is the
reason this is filed now rather than after.

Activity

  1. added
    bugFails loudly: exception or obviously wrong result
    area:engineLowering, IR, relational executor, the sink contract
    engine:duckdbSpecific to the duckdb engine — opt-in, the [duckdb] extra
    on Aug 9, 2026
  2. FBumann commented on Aug 9, 2026

    @FBumann
    CollaboratorAuthor

    Cross-referencing #399: this is a merge-blocker for that PR rather than independent work. Merging it brings main's removal of the load-time refusal onto the branch, which is exactly what makes this reachable — and #399 makes duckdb the default, so it would land on the shipped path.

  3. FBumann commented on Aug 14, 2026

    @FBumann
    CollaboratorAuthor

    Note

    The following content was generated by AI.

    Fixed on the #399 branch by #637 (commit 6c8a404, "the engine composes relations instead of writing SQL"): restrict_to in src/lpspec/relational/engines/duck/compiler.py handles empty on as an uncorrelated semi-join on TRUE — exactly the keyless cross-join semantics this issue prescribed — and both call sites (the absence propagation in the compiler and the labeller path in the executor) go through that one helper, covering the "check the labeller's restriction loop" note. main never shipped the duck engine, so the described bug now exists nowhere; the tests this issue named exist on the branch and the suite runs under --engine duckdb. Verified by code trace of origin/feat/duckdb-engine, not by executing the duck suite.

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

    area:engineLowering, IR, relational executor, the sink contractbugFails loudly: exception or obviously wrong resultengine:duckdbSpecific to the duckdb engine — opt-in, the [duckdb] extra

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions