[I] Relationship created earlier in the same query is not matched after `WITH` and `UNWIND` [age]

["zihaozeng0021 (via GitHub)" <gi...@apache.org>]

zihaozeng0021 opened a new issue, #2490:
URL: https://github.com/apache/age/issues/2490

   **Describe the bug**
   
   A relationship created earlier in the same Cypher query is not matched as expected after a `WITH` and `UNWIND` pipeline.
   
   The query creates one relationship, expands two identical lookup items with `UNWIND`, and attempts to match that relationship once for each input row. The resulting relationship count should be `2`, but the query evaluates the condition as false and returns `"FAIL"`.
   
   **How are you accessing AGE (Command line, driver, etc.)?**
   
   * Command line using interactive `psql`
   * Executed through `docker exec`
   
   ```text
   docker exec -it test psql -U postgres -d postgres
   ```
   
   **What data setup do we need to do?**
   
   Start Apache AGE:
   
   ```text
   docker run --rm --name test -e POSTGRES_PASSWORD=agepass -p 5432:5432 apache/age:latest
   ```
   
   Connect using interactive `psql`:
   
   ```text
   docker exec -it test psql -U postgres -d postgres
   ```
   
   Load AGE, configure the search path, and create the graph:
   
   ```pgsql
   LOAD 'age';
   SET search_path = ag_catalog, public;
   SELECT create_graph('test');
   ```
   
   No separate graph data setup is required because the affected query creates the nodes and relationship itself.
   
   **What is the necessary configuration info needed?**
   
   * Docker image: `apache/age:latest`
   * PostgreSQL: `18.1`
   * Apache AGE: `1.7.0`
   * AGE extension installed by the Docker image
   * `search_path` set to `ag_catalog, public`
   * Operating system: WSL2, Ubuntu 24.04.1 LTS
   * Installation method: Docker
   
   **What is the command that caused the error?**
   
   ```pgsql
   SELECT *
   FROM cypher('test', $cypher$
     CREATE (a:N)-[:R {id:1}]->(b:N)
     WITH [{type:'R', id:1}, {type:'R', id:1}] AS items
     UNWIND items AS item
     MATCH ()-[r:R]->()
     WHERE r.id = item.id
     RETURN CASE
       WHEN count(r) = 2 THEN 'PASS'
       ELSE 'FAIL'
     END AS value
   $cypher$) AS (value agtype);
   ```
   
   Actual result:
   
   ```text
    value
   --------
    "FAIL"
   (1 row)
   ```
   
   **Expected behavior**
   
   `UNWIND` should produce two input rows.
   
   For each input row, the relationship created earlier in the same query should match `r.id = item.id`. The resulting `count(r)` should therefore be `2`, and the query should return:
   
   ```text
    value
   --------
    "PASS"
   (1 row)
   ```
   
   **Environment (please complete the following information):**
   
   * Apache AGE version: `1.7.0`
   * PostgreSQL version: `18.1`
   * Docker image: `apache/age:latest`
   * Operating system: WSL2, Ubuntu 24.04.1 LTS
   * Access method: interactive `psql` through `docker exec`
   
   **Additional context**
   
   The behavior was reproduced twice in clean Docker containers.
   


-- 
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: dev-unsubscribe@age.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] Relationship created earlier in the same query is not matched after `WITH` and `UNWIND` [age]

["gregfelice (via GitHub)" <gi...@apache.org>]

gregfelice commented on issue #2490:
URL: https://github.com/apache/age/issues/2490#issuecomment-5169431971

   The query as filed **passes on current `master`** — but it is masked rather than fixed, so I would not close this without linking it to the underlying defect.
   
   **Environment:** `master` @ 80141740 (1.8.0), PostgreSQL 18.4, built from source. You filed against 1.7.0.
   
   ```
    value
   --------
    "PASS"
   (1 row)
   ```
   
   ## Why it passes
   
   The rule that governs this class of bug is that a reading clause sees only the rows written by the **first input row** of a preceding writing clause. Your `CREATE (a:N)-[:R {id:1}]->(b:N)` runs from a single input row, so its one edge falls inside the visible window and the later `MATCH` finds it.
   
   Drive the same shape from two input rows and it still returns the wrong answer:
   
   ```sql
   SELECT * FROM cypher('g', $cypher$
     UNWIND [1,2] AS i
     CREATE (a:N)-[:R {id:i}]->(b:N)
     WITH [{id:1},{id:2}] AS items
     UNWIND items AS item
     MATCH ()-[r:R]->()
     WHERE r.id = item.id
     RETURN count(r) AS matched
   $cypher$) AS (matched agtype);
   ```
   
   Correct answer is `4` — four input rows reach the `MATCH`, each matching exactly one edge. Actual is `2`: only the edge created by the first input row (`id: 1`) is visible, so the two rows carrying `item.id = 2` match nothing. Both edges do persist.
   
   ## Underlying defect
   
   Same root cause as #2493, where I have written it up in full along with the code references: the snapshot's `curcid` advances once per clause while `CREATE` consumes one command id per input row, so everything written by input rows 2..n stays invisible for the remainder of the statement.
   
   Suggest tracking the fix under #2493 and keeping this issue open as the 1.7.0 report until that lands, or closing it with a pointer — whichever the maintainers prefer.
   


-- 
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: dev-unsubscribe@age.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org