gregfelice opened a new pull request, #2380:
URL: https://github.com/apache/age/pull/2380
## Summary
Fixes issue #2378 — `OPTIONAL MATCH` incorrectly drops null-preserving outer
rows when its `WHERE` clause contains a correlated sub-pattern predicate
(`EXISTS { ... }`, `COUNT { ... }`).
## Reproducer
```cypher
CREATE (a:Person {name:'Alice'}), (b:Person {name:'Bob'}), (c:Person
{name:'Charlie'}),
(a)-[:KNOWS]->(b), (a)-[:KNOWS]->(c);
MATCH (p:Person)
OPTIONAL MATCH (p)-[:KNOWS]->(friend:Person)
WHERE EXISTS { (friend)-[:KNOWS]->(:Person) }
RETURN p.name, friend.name;
```
**Before the fix:** `(0 rows)`
**After the fix:** `Alice | NULL`, `Bob | NULL`, `Charlie | NULL` — one row
per person, null-preserved, matching Cypher OPTIONAL MATCH semantics.
## Root cause
In `transform_cypher_match`, when the `WHERE` clause contains a list
comprehension or a sub-pattern (`has_list_comp_or_subquery` returns true), the
code takes a rewrite path that:
1. Detaches the `WHERE` from the `cypher_match` node (`match_self->where =
NULL`).
2. Transforms the match clause as a subquery via
`transform_cypher_clause_as_subquery`.
3. Attaches the detached `WHERE` as an outer filter on that subquery:
`query->jointree = makeFromExpr(p_joinlist, where_qual)`.
This rewrite is correct for non-optional `MATCH`, because the subquery's
output is the full set of matches and the outer `WHERE` filters that set.
But for `OPTIONAL MATCH`, the inner subquery produces a `LATERAL LEFT JOIN`
that already emits null-preserving rows for outer tuples with no right-hand
match. The outer `WHERE` filter then runs against those nulled rows and drops
them when the predicate evaluates to `NULL` or `false` on the nulled side. The
result is zero rows for every outer tuple whose optional matches all fail the
predicate — a direct violation of Cypher `OPTIONAL MATCH` semantics, which
require the outer row to survive with `NULL` in the optional columns.
## Fix
Two coordinated changes in `src/backend/parser/cypher_clause.c`:
1. **`transform_cypher_match`** — only take the `has_list_comp_or_subquery`
rewrite path for non-optional MATCH. For OPTIONAL MATCH, fall through to the
normal `transform_cypher_match_pattern` path so the WHERE remains attached to
the `cypher_match` node.
2. **`transform_cypher_optional_match_clause`** — detach `match_self->where`
before recursively transforming the right-hand side of the `LATERAL LEFT JOIN`,
so the inner transform does not double-apply or misresolve the predicate in a
fresh namespace. After both sides are transformed and the right-side namespace
item is in `pstate->p_namespace`, re-attach the transformed predicate as the
`LEFT JOIN`'s `ON` condition (`j->quals`). PostgreSQL's `LEFT JOIN ... ON
<pred>` correctly preserves left rows with null right columns when the `ON`
fails — which is exactly the semantics Cypher `OPTIONAL MATCH ... WHERE`
requires.
The fix restores the invariant that for every outer row, `OPTIONAL MATCH ...
WHERE pred` either emits the surviving right-hand matches (if any) or exactly
one row with nulls in the optional columns.
## Regression tests
Added to `regress/sql/cypher_match.sql` under a new `issue_2378` graph:
- Correlated `EXISTS` referencing the optional variable (`friend`) — the
primary reproducer.
- Correlated `EXISTS` referencing the outer variable (`p`) — the second bug
family.
- Non-correlated `EXISTS` — was already working; kept as a regression guard.
- Plain scalar predicate on the optional variable (`friend.name = 'Bob'`) —
was already working; guard.
- Constant-false `WHERE` — was already working; guard.
## Test plan
- [x] `make installcheck REGRESS=\"cypher_match cypher_merge cypher_with
cypher_subquery cypher_vle cypher_union cypher_call cypher_create cypher_set
cypher_delete cypher_remove cypher_unwind list_comprehension expr\"` — all 14
suites pass.
- [x] Issue reporter's primary reproducer now returns the expected 3 rows
with null-preserved friend.
- [x] Second variant from the issue body (EXISTS referencing outer `p`) now
preserves outer rows correctly.
- [x] Non-correlated EXISTS, plain predicates, and `WHERE false` continue to
work as before.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
