Module sqlpush 

Predicate pushdown — conservative, and narrower than the phrase sounds.

What this is NOT

It is not a rewrite engine that turns

  Filter(Join(A, B))   ->   Join(Filter(A), B)

on the basis of column ownership. That transformation is unsound in general, and the specific way it fails is already pinned in the semantic corpus:

  LEFT JOIN ... WHERE d.dname = 'eng'        2 rows
  LEFT JOIN ... ON ... AND d.dname = 'eng'   5 rows

Moving a predicate from WHERE to the join’s ON changes which rows get NULL-synthesised, so it changes the answer. Three-valued logic is what makes it dangerous: the outer rows survive the join and are then dropped by WHERE because a comparison against the synthesised NULL is UNKNOWN.

What it IS: a pre-filter on a relation that is never NULL-synthesised

A qualifying conjunct is COPIED to run against its own relation before the join. The WHERE clause is left untouched and still runs after the join.

Retaining the original is necessary but not sufficient, and getting that wrong is instructive. The first version of this module argued that a copy-not-move was safe for every join type, reasoning:

Removing rows from a relation can only create MORE unmatched rows on the other side; those get NULL-synthesised, and the retained WHERE then evaluates the same predicate against a NULL, yields UNKNOWN, and drops them.

That is wrong, and the semantic corpus caught it immediately. A predicate can be SATISFIED by a synthesised NULL:

  SELECT e.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id
   WHERE d.dname IS NULL

No dept row has a NULL dname, so pre-filtering dept empties it entirely; every emp row then becomes unmatched, gets NULL-extended, and IS NULL is TRUE for all of them. The answer went from 1 row to 5.

So the real condition is about NULL SYNTHESIS, not about retention:

A predicate may be pre-applied to relation R only if R is never NULL-synthesised in this query’s output.

When R cannot be synthesised, every output row carries a real R row, so the retained WHERE sees exactly the values the pre-filter saw, and the pre-filter can only remove rows the WHERE would have removed. When R CAN be synthesised, removing a row can manufacture an outer row whose values differ from anything the pre-filter examined — and whether that row survives depends on the predicate, which is not something to guess at.

nullable_bindings computes that set:

• a join’s right binding is nullable when the join is LEFT or FULL;
• every binding accumulated so far becomes nullable when a LATER join is RIGHT or FULL, because those synthesise NULLs across the whole left side — including the FROM relation.

An INNER (or CROSS) join synthesises nothing, which is why an all-inner query can push everything and is the common case.

Refusals are recorded, not silent

When a conjunct cannot be pushed, the reason is kept on the plan (Filter retained above join: ...). An optimiser that silently declines is impossible to audit — you cannot tell “correctly refused” from “forgot to look”. The reasons are inspectable in tests today and are the natural thing for EXPLAIN to show later.

Structs

Pushdown

What the planner decided, per relation, plus why it declined the rest.

Functions

nullable_bindings

The bindings this query can NULL-synthesise.

plan

Decide which WHERE conjuncts may be pre-applied to which relation.