1

I am not able to get why this apache agedb behaviour is not returning correct result in the following query when alias in not defined

SELECT * from cypher('sample_graph', $$
CREATE (a:Part {part_num: '111'}), 
       (b:Part {part_num: '222'}), 
       (c:Part {part_num: '333'}) 
$$) as (a agtype);

When I execute the following query with alias, it returns the correct count, 3 as a result.

sampledb=# SELECT * from cypher('sample_graph', $$ match (a:Part) return count(*) $$) as (result agtype);

When I execute the following query without alias, it return incorrect count, 1 as a result.

sampledb=# SELECT * from cypher('sample_graph', $$ match (:Part) return count(*) $$) as (result agtype);
cybersam
  • 63,203
  • 6
  • 53
  • 76
Abdul
  • 69
  • 5
  • Both of your queries return `3` in neo4j. There seems to be a bug in Apache Age, and I see that someone (maybe you) has already submitted [an issue](https://github.com/apache/age/issues/945). – cybersam May 24 '23 at 22:34

2 Answers2

0

The query you are not having alias is like calling count on just a returning line of the cypher calling there would be not something to be returned from the query or something can be hold somewhere on the query (there is no default name can be used like SQL) if you need to return something you will not be able to.

in the other hand having an alias it is like RETURN (a), that query will return all nodes you have created and therefore they will be counted as 3 rows. Looking into what is going on using EXPLAIN ANALYZE will let you know that at the non-alias query there is not SeqScan at all while when having an alias there are SeqScan

Look at the SeqScan Part

Query explain without an alias:

test=#  SELECT * from cypher('sample_graph', $$ EXPLAIN ANALYZE match (:Part) return count(*)  $$) as (result agtype); 
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Subquery Scan on _  (cost=0.01..0.04 rows=1 width=32) (actual time=0.028..0.032 rows=1 loops=1)
   ->  Aggregate  (cost=0.01..0.02 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)
         ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.003..0.004 rows=1 loops=1)
 Planning Time: 0.115 ms
 Execution Time: 0.082 ms
(5 rows)

Query explain with an alias:

test=#  SELECT * from cypher('sample_graph', $$ EXPLAIN ANALYZE match (n:Part) return count(*)  $$) as (result agtype); 
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Subquery Scan on _  (cost=25.00..25.02 rows=1 width=32) (actual time=0.039..0.042 rows=1 loops=1)
   ->  Aggregate  (cost=25.00..25.01 rows=1 width=8) (actual time=0.030..0.032 rows=1 loops=1)
         ->  Seq Scan on "Part" n  (cost=0.00..22.00 rows=1200 width=0) (actual time=0.018..0.021 rows=3 loops=1)
 Planning Time: 0.150 ms
 Execution Time: 0.092 ms
(5 rows)
Mohamed Mokhtar - rrrokhtar
  • 678
  • 5
  • 12
0

In the first query where you define an alias for the result as (result agtype), the count(*) query returns a single value, which is the count of all nodes matched by the Cypher query. By specifying an alias, you're treating the result as a single value rather than multiple rows, resulting in the correct count of 3.

In the second query without an alias, the count(*) query is still returning the correct count of 3. However, since you're using SELECT *, it returns all columns of the row (in this case, a single column with the count). The incorrect count of 1 you mentioned might be due to misinterpretation or another issue not apparent from the provided query.

Abdullah Zia
  • 17
  • 4