Apache agedb produces error when ORDER BY and RETURN AS r is used. Unexpected behaviour

Question

When I execute these queries on apache ageDB

SELECT * FROM cypher('sample_graph', $$
    CREATE (:opt_match_v {prop: 'value1'})-[:opt_match_e]->(:opt_match_v {prop: 'value2'}),
           (:opt_match_v {prop: 'value3'})-[:opt_match_e]->(:opt_match_v {prop: 'value4'})
$$) AS (r agtype);

SELECT * FROM cypher('sample_graph', $$
    MATCH p=(n)-[]->(m)
    RETURN n.name AS custom_node
    ORDER BY custom_node
$$) AS (r agtype);

this query is logically and syntactically correct but I get this error.

ERROR: could not find rte for r

score 0 · Answer 1 · answered May 25 '23 at 17:26

First of all there is a syntax error. You use prop field in the properties but in your query you use name which doesn't exist. Here is a way to fix your query :

SELECT * FROM cypher('sample_graph', $$
    MATCH p=(n)-[]->(m) 
    WITH n.prop 
    AS custom_node
    RETURN custom_node
    ORDER BY custom_node   
$$) AS (r agtype);

You need to use WITH...AS after the matching clause to set the alias for n.prop and then return that value correctly ordered.

score 0 · Answer 2 · answered May 25 '23 at 17:31

First of all, ORDER BY is supposed to precede RETURN. Secondly, 'name' is not specified in properties hence the error.

The following statement should work:

SELECT * FROM cypher('sample_graph', $$
MATCH p=(n)-[]->(m)
WITH n.name AS custom_node
ORDER BY custom_node
RETURN custom_node
$$) as (r agtype);

score 0 · Answer 3 · answered May 25 '23 at 17:34

0

You can not use AS with RETURN clause.

Try something like this:

RETURN n.name
ORDER BY n.name

OR

WITH n.name AS custom_node
RETURN custom_node
ORDER BY custom_node

answered May 25 '23 at 17:34

Mohayu Din

433
9

score 0 · Answer 4 · answered May 25 '23 at 17:52

0

The error is because you are using AS with the RETURN clause. You cannot use AS with RETURN.

Documentation of RETURN CLAUSE

answered May 25 '23 at 17:52

Marcos Silva

115
5

score 0 · Answer 5 · answered May 26 '23 at 08:51

Try this one :

SELECT * FROM cypher('sample_graph', $$
    CREATE (:opt_match_v {prop: 'value1'})-[:opt_match_e]->(:opt_match_v {prop: 'value2'}),
           (:opt_match_v {prop: 'value3'})-[:opt_match_e]->(:opt_match_v {prop: 'value4'})
$$) AS r;

SELECT * FROM cypher('sample_graph', $$
    MATCH p=(n)-[]->(m)
    RETURN n.name AS custom_node
    ORDER BY custom_node
$$) AS r;

Bhaskar Sharma · Answer 6 · 2023-05-26T10:01:20.713

I ran the commands in AGE and ran through the following steps: -

Creating my data like this:

SELECT * FROM cypher('test', $$
CREATE (:opt_match_v {prop: 'value1'})-[:opt_match_e]->(:opt_match_v {prop: 'value2'}),
(:opt_match_v {prop: 'value3'})-[:opt_match_e]->(:opt_match_v {prop: 'value4'})
$$) AS (r agtype);

This command works perfectly fine:

SELECT * FROM cypher('test', $$
    MATCH p=(n)-[]->(m)
    RETURN n.prop AS custom_node ORDER BY n.prop
$$) AS (r agtype);
    r     
----------
 "value1"
 "value3"
(2 rows)

Hence, we can see, order by being after return is not really an issue. Using as with return is not an issue either.

However, you were correct, this command does not work.

test=# SELECT * FROM cypher('test', $$
    MATCH p=(n)-[]->(m)
    RETURN n.prop AS custom_node ORDER BY custom_node
$$) AS (r agtype);
2023-05-26 11:52:08.197 CEST [30849] ERROR:  could not find rte for custom_node at character 99
2023-05-26 11:52:08.197 CEST [30849] STATEMENT:  SELECT * FROM cypher('test', $$
        MATCH p=(n)-[]->(m)
        RETURN n.prop AS custom_node ORDER BY custom_node
    $$) AS (r agtype);
ERROR:  could not find rte for custom_node
LINE 3:     RETURN n.prop AS custom_node ORDER BY custom_node

Therefore, solution would be to use this query: -

test=# SELECT * FROM cypher('test', $$
    MATCH p=(n)-[]->(m)
    WITH n.prop as custom_node RETURN custom_node ORDER BY custom_node
$$) AS (r agtype);
    r     
----------
 "value1"
 "value3"
(2 rows)

score 0 · Answer 7 · answered May 26 '23 at 22:06

The issue is with your returning variable. You are trying to get the relationships between the nodes and trying to fetch the name variable of the vertexes but that name variable is not in the vertex.

So, what can be done here is you have to add the name property to the vertexes and after that, if you use that query then it will work fine.

Else you have to sort the query based on the prop value. You can use the prop property to sort it in ascending or descending order.

Ascending Order

MATCH p=(n)-[]->(m)
RETURN n.prop AS custom_node
ORDER BY custom_node

Descending Order

MATCH p=(n)-[]->(m)
RETURN n.prop AS custom_node
ORDER BY custom_node DESC

score 0 · Answer 8 · answered Jun 16 '23 at 17:24

You have to use the WITH clause to sort of select the part of the query you would like to order using ORDER BY, so ORDER BY is a sub-clause following the WITH clause, And finally your RETURN statement should come last. So your updated query would be;

SELECT * FROM cypher('sample_graph', $$
    MATCH p=(n)-[]->(m)
    WITH n.name AS custom_node
    ORDER BY n.name
    RETURN custom_node
$$) AS (r agtype);

To learn more about the WITH clause, you can read more here https://age.apache.org/age-manual/master/clauses/with.html

score 0 · Answer 9 · answered Jun 25 '23 at 11:11

The error is because 'name' is not there in the properties. Try using this:

SELECT *
FROM cypher('sample_graph', $$
MATCH p=(n)-[]->(m)
WITH n.prop
RETURN custom_node
ORDER BY custom_node
$$) AS r;

So what happens is that the paths 'p' between nodes 'n' and 'm' in the 'sample_graph' is retrieved. The 'WITH' clause is used to select the prop property of node 'n'. Then, the 'RETURN' clause is used to return the 'custom_node.' The 'ORDER BY' clause is applied to sort the result in ascending order based on custom_node.

score 0 · Answer 10 · answered Jun 25 '23 at 20:29

0

You should use the WITH clause when you want to alias in this scenario.. You should do WITH n.name AS custom_node check out the docs for more info.

answered Jun 25 '23 at 20:29

Peter

43
4

score 0 · Answer 11 · answered Aug 15 '23 at 08:37

ORDER BY should come after the WITH clause and before RETURN. Also, the name property isn't specified in the query. Corrected query is:

SELECT * FROM cypher('sample_graph', $$
    MATCH p=(n)-[]->(m)
    WITH n.name AS custom_node
    ORDER BY custom_node
    RETURN custom_node
$$) AS r;

WITH clause creates an alias and ORDER BY sorts the results based on this alias, and finally the RETURN statement displays the sorted values.

Apache agedb produces error when ORDER BY and RETURN AS r is used. Unexpected behaviour

11 Answers11