Outer-to-inner join conversion fires on null-sensitive (COALESCE / IS NULL / CASE) conjuncts

Your Environment

• Presto version used: master
• Storage (HDFS/S3/GCS..): n/a (planner issue)
• Data source and connector used: any connector that enforces pushed scan filters (remaining-predicate pushdown with no residual)
• Deployment (Cloud or On-prem): n/a

Expected Behavior

For a query shaped like

SELECT ..., CASE WHEN (COALESCE(b.flag1, FALSE) OR COALESCE(b.flag2, FALSE)) THEN 1 ELSE 0 END AS blocklist
FROM a LEFT JOIN b ON a.id = b.id
WHERE blocklist = 1

the null-sensitive predicate over the inner-side columns must not end up baked into the inner scan with no residual filter above it. The inner (null-supplying) side of an outer join must not receive null-sensitive filters as scan-enforced predicates. (The sibling domain path already encodes this invariant: inner-side domains are pushed only when !domain.isNullAllowed().)

Current Behavior

PredicatePushDown.tryNormalizeToOuterToInnerJoin → canConvertOuterToInner tests each conjunct by binding inner-side symbols to NULL and folding. A null-sensitive conjunct that folds FALSE-on-null (e.g. CASE WHEN (COALESCE(...) OR COALESCE(...)) THEN 1 ELSE 0 END = 1) triggers conversion of the LEFT join to INNER. processInnerJoin then pushes the conjunct below the join, and connector pushdown bakes it into the TableScan layout (filter=(SWITCH(...))) with no residual FilterNode — the upper projection even folds to constant 1. From there, any downstream stage that evaluates the pushed shape with diverging null/three-valued semantics surfaces wrong rows, with no re-check left in the plan. The random()-guarded variant (non-deterministic, stays post-join, join stays outer) returns correct results, isolating the pushed path as the divergent one.

Possible Solution

Keep the (sound) outer-to-inner conversion, but withhold conjuncts containing null-sensitive constructs over inner-side variables (IF, IS NULL, CASE/SWITCH) from below-join pushdown, so they stay engine-evaluated above the join as a residual instead of being baked into the connector scan with no residual. Plain COALESCE comparisons are unaffected: COALESCE(x, c) <op> k with a failing default is equivalent to a plain null-rejecting comparison, so both conversion and pushdown are safe for it under SQL three-valued logic. The guard belongs where the sides are known (at inline time the join context is invisible), implemented unconditionally with no new session property. Symbol tracing follows transparent projections transitively and translates set-operation and exchange output-to-input mappings.

Steps to Reproduce

1. Run EXPLAIN on:

SELECT * FROM orders LEFT JOIN lineitem ON orders.orderkey = lineitem.orderkey
WHERE CASE WHEN (COALESCE(lineitem.returnflag, 'N') = 'R') THEN 1 ELSE 0 END = 1

1. Observe an InnerJoin with the CASE-derived filter pushed below the join onto the inner (lineitem) scan, and no residual filter above it.
2. Against a connector that enforces the pushed filter in the scan, rows contradicting the stored values can be returned (observed with boolean flag columns of this exact shape).

Screenshots (if appropriate)

Context

This blocks a wrong-results class where a filter born above an outer join (often via projection inlining of WHERE <alias> = ...) ends up scan-enforced with no residual. The general-conjunct pushdown path (processLimitedOuterJoin) only pushes equality-derived conjuncts inner-ward, so the conversion step is the leak.