gregfelice commented on issue #2491:
URL: https://github.com/apache/age/issues/2491#issuecomment-5169431812
Reproduced on `master` @ 80141740 (1.8.0), PostgreSQL 18.4. The title is
accurate — the `CREATE`s really are discarded, not merely unmatched. Digging
in, this query is hitting **two independent defects**, and they probably want
separate issues.
## Defect 1 — a later clause sees only the first input row's writes
The middle stage is where it goes wrong:
```cypher
UNWIND range(1,8) AS i CREATE (:N {id:i})
WITH count(*) AS ig
MATCH (a:N), (b:N) WHERE a.id <> b.id
CREATE (a)-[:R]->(b)
```
Run on its own: 8 nodes persist, **0 edges**. The `MATCH (a:N), (b:N)` sees
exactly one of the eight nodes, so the only pair it produces is `a = b`, which
`a.id <> b.id` then filters out. No input rows reach the edge `CREATE`.
The rule is that a reading clause sees only what the *first input row* of a
preceding writing clause wrote:
| Setup | Created | Visible to later `MATCH` | Persisted |
|---|---|---|---|
| `UNWIND range(1,2) AS i CREATE (:N {id:i})` | 2 | **1** | 2 |
| `UNWIND range(1,8) AS i CREATE (:N {id:i})` | 8 | **1** | 8 |
| 5 pre-existing, then create 3 in-statement | 3 | **6** (5 + 1) | 8 |
| `CREATE (:N),(:N),(:N)` — one input row | 3 | **3** ✅ | 3 |
This half is the same root cause as #2493 — full write-up and the code
references are in [my comment there](https://github.com/apache/age/issues/2493).
## Defect 2 — the writes are never executed at all
This is the "silently discarded" part, and it is not a consequence of Defect
1. Minimal reproduction, four lines, no second `CREATE` stage:
```sql
SELECT * FROM cypher('g', $cypher$
UNWIND [1,2] AS i CREATE (:N)-[:R {id:i}]->(:N)
WITH count(*) AS ig
MATCH ()-[r:R]->() RETURN count(r)
$cypher$) AS (v agtype);
```
Returns `0`, and afterwards the graph contains **zero nodes and zero
edges**. Nothing was written.
`EXPLAIN (ANALYZE)` shows why:
```
Aggregate (actual rows=1.00 loops=1)
-> Merge Join (actual rows=0.00 loops=1)
-> Merge Append (actual rows=0.00 loops=1)
-> Index Only Scan using _ag_label_vertex_pkey ... (actual
rows=0.00 loops=1)
-> Index Only Scan using "N_pkey" on "N" ... (actual
rows=0.00 loops=1)
-> Materialize (never executed)
-> Nested Loop (never executed)
-> Index Scan using "R_end_id_idx" on "R" r (never
executed)
-> ...
-> Custom Scan (Cypher Create) (never executed)
```
AGE's DML is a `CustomScan` buried inside the plan tree rather than a
top-level `ModifyTable`, so the planner is free to put it on either side of a
join. Here the other side of the merge join is empty — the vertex tables have
no rows yet, because the node that would create them is the side that never
runs — so the executor exhausts that input, concludes the join can produce
nothing, and never pulls the subtree containing the writes. Chicken-and-egg,
and completely silent.
The same thing happens in the query as filed in this issue, via a hash join
instead:
```
Hash Join (actual rows=0.00 loops=1)
-> Hash Join (never executed)
...
-> Custom Scan (Cypher Create) (never executed) <- the 8 nodes
...
-> Custom Scan (Cypher Create) (never executed) <- the edges
-> Hash (actual rows=0.00 loops=1)
-> Seq Scan on "N" _age_default_alias_6 (actual rows=0.00 loops=1)
```
An inner join with an empty hash build side lets PostgreSQL skip reading the
outer relation entirely, and the outer relation is where both `CREATE`s live.
Two different join strategies, same failure. So this is not one bad plan
shape — any executor node entitled to skip an input can silently swallow a
write.
Note this is plan-shape dependent, not simply "downstream `MATCH` is empty":
a two-clause query whose later `MATCH` targets a *different* empty label still
persists its writes correctly. It needs the DML subtree to land on the
skippable side of a join.
## Suggested split
| Defect | Symptom | Severity |
|---|---|---|
| 1 — first-input-row visibility | wrong answers | duplicate of #2493 |
| 2 — DML subtree never executed | **silent data loss** | needs its own
issue |
Happy to file the second one separately if that is preferred.
--
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]
