The count number does not match on UPDATE and DELETE.

[Leelst]

• Bitnine DB Tech Team

Describe the bug The count number does not match on UPDATE and DELETE. In AGE, when performing UPDATE and DELETE operations, even if there are changes made to the affected items, they are still indicated as 0.

How are you accessing AGE (Command line, driver, etc.)? Command line

What data setup do we need to do?

...
SELECT * from cypher('my_graph_name', $$
  CREATE (a:Part {part_num: '123'}), 
         (b:Part {part_num: '345'}), 
         (c:Part {part_num: '456'}), 
         (d:Part {part_num: '789'})
$$) as (a agtype);
...

What is the command that caused the error?

1. UPDATE

SELECT * FROM cypher ( 'tmp', $$ 
    MATCH (a:Part) WHERE a.part_num=~'3+' 
    SET a.part_num='333' 
$$) AS (a agtype) ;

Result

 a
---
(0 rows)

Check

SELECT * FROM cypher ( 'tmp', $$ 
    MATCH (a:Part) RETURN a 
$$) AS (a agtype) ;

                                          a
-------------------------------------------------------------------------------------
 {"id": 844424930131971, "label": "Part", "properties": {"part_num": "456"}}::vertex
 {"id": 844424930131972, "label": "Part", "properties": {"part_num": "789"}}::vertex
 {"id": 844424930131969, "label": "Part", "properties": {"part_num": "333"}}::vertex
 {"id": 844424930131970, "label": "Part", "properties": {"part_num": "333"}}::vertex
(4 rows)

There are two changed rows.

2. DELETE

Same goes for DELETE.

SELECT * FROM cypher ( 'tmp', $$ 
    MATCH (a:Part) WHERE a.part_num=~'3+' DELETE a 
$$) AS (a agtype) ;

Result

 a
---
(0 rows)

Check

SELECT * FROM cypher ( 'tmp', $$ MATCH (a:Part) return a $$) AS (a agtype) ;

                                          a
-------------------------------------------------------------------------------------
 {"id": 844424930131971, "label": "Part", "properties": {"part_num": "456"}}::vertex
 {"id": 844424930131972, "label": "Part", "properties": {"part_num": "789"}}::vertex
(2 rows)

You can see that the two rows containing the 3 have been deleted. However, it shows 0 as the number of deleted rows.

Expected behavior I want to see the number of changed or deleted rows. For comparison, the case of Agensgraph is shown. The test process is the same as above, and the process will be skipped.

Result (UPDATE)

UPDATE 2

Result (DELETE)

UPDATE 2

Environment (please complete the following information):

• Version: PostgreSQL 11.20 age 1.3.0

Additional context Add any other context about the problem here.

[Zainab-Saad]

In AGE, only the rows returned from the cypher query will be displayed in the table (even if you made changes to graph entities in your cypher query) and hence you need to use the RETURN clause to see the changes made to the graph entities. For example, in both the UPDATE and DELETE examples, you need to RETURN the rows as :

SELECT * FROM cypher ( 'tmp', $$                                        
MATCH (a:Part) WHERE a.part_num=~'3+'
SET a.part_num='333' RETURN a
$$) AS (a agtype) ;

and

SELECT * FROM cypher ( 'tmp', $$                                        
MATCH (a:Part) WHERE a.part_num=~'3+' DELETE a RETURN a
$$) AS (a agtype) ;

But, this could be an enhancement to indicate the number of graph entities created/updated/deleted even without RETURN statement

[titoausten]

This isn't a bug, you just missed a key clause in your statement. To see the expected result, you would have to use RETURN and specify the rows you want to see.

Reference: The AGE docs

[dukeofhazardz]

In order for you to display the updated or deleted rows, you have to use the RETURN clause in your queries. This clause allows you to specify which columns or expressions you want to return from the modified rows.

[AbdulSamad4068]

The issue you're encountering is not a bug but a misunderstanding of how to view the updated or deleted rows in Apache AgeDB. In AgeDB, only the rows returned from the Cypher query will be displayed in the table. To see the changes made to the graph entities, you need to use the RETURN clause in your queries. Here's how you can modify your UPDATE and DELETE queries to view the affected rows:

UPDATE query:

SELECT * FROM cypher ('tmp', $$
MATCH (a:Part) WHERE a.part_num =~ '3+' SET a.part_num = '333' RETURN a
$$) AS (a agtype);

DELETE query:

SELECT * FROM cypher ('tmp', $$
MATCH (a:Part) WHERE a.part_num =~ '3+' DELETE a RETURN a
$$) AS (a agtype);

without the RETURN clause, only the rows returned from the query will be displayed, which might not include the modified rows.

[Zeesh-an]

It appears there may be some confusion. Only the rows returned from a Cypher query are displayed in the table. To accurately observe the changes made to graph entities, RETURN clause in your queries can be used as mentioned by @AbdulSamad4068 . Here's an alternative explanation along with modified examples to demonstrate how you can adjust your UPDATE and DELETE queries to view the affected rows:

SELECT * FROM cypher ('tmp', $$
MATCH (a:Part)
WHERE a.part_num =~ '3+'
SET a.part_num = '333'
RETURN a
$$) AS (a agtype);

In this query, the 'part_num' property of nodes labeled as 'Part' is modified, specifically updating it to '333'. By including the RETURN clause and specifying 'a', the modified rows will be returned and displayed as part of the query result.

[M4rcxs]

you forgot to use the RETURN clause in your query.

SELECT * FROM cypher ( 'tmp', $$ 
    MATCH (a:Part) WHERE a.part_num=~'3+' 
    DELETE a 
    RETURN a
$$) AS (a agtype) ;

[github-actions[bot]]

This issue is stale because it has been open 45 days with no activity. Remove "Abondoned" label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been stalled for further 7 days with no activity.