0

I am trying to add support for the following query for an AGE project that converts Cypher queries to SQL:

MATCH p=({name: 'agens-graph'})-[{lang: 'java'}]->(m) RETURN *;

This successfully converts into the following:

SELECT * FROM cypher('test', $$ MATCH p=({name: 'agens-graph'})-[{lang: 'java'}]->(m) RETURN * $$) AS (v agtype);

However, this produces an error:

ERROR:  return row and column definition list do not match

Assuming I do not know the exact columns that will be produced with the RETURN *, is there a way to replace the AS (v agtype) to something else that will support the query (something like AS (* agtype))? Or is this not yet supported in AGE?

Ken W.
  • 397
  • 1
  • 13

12 Answers12

1

As per the documentation of RETURN clause, even if you are using RETURN *, you need to define the number of columns to be returned by the cypher query and so any alike of * agtype is not yet supported. For example:

SELECT *
FROM cypher('graph_name', $$
MATCH (a {name: 'A'})-[r]->(b)
RETURN *
$$) as (a agtype, b agtype, r agtype);

Reference: RETURN clause

Zainab Saad
  • 728
  • 1
  • 2
  • 8
1

At the moment, AS (* agtype)) is not yet supported in AGE.

Also, the reason for the error ERROR: return row and column definition list do not match is that you did not give the specific number of columns to be returned after using RETURN * which returns all elements.

Hence, this query:

SELECT * FROM cypher('test', $$ MATCH p=({name: 'agens-graph'})-[{lang: 'java'}]->(m) RETURN * $$) AS (v agtype);

should be;

SELECT * FROM cypher('test', $$ MATCH p=({name: 'agens-graph'})-[{lang: 'java'}]->(m) RETURN * $$) AS (name agtype, lang agtype, m agtype);

This way the error would not occur because the number of columns is given.

Tito
  • 289
  • 8
1

So far it's not supported that you can return all elements at once without specifying the elements to be returned, although you can RETURN *, it still required to specify the elements in AS (a agtype, b agtype, c agtype); as following:

SELECT * FROM cypher('test', $$ MATCH (a)-[r: REL]->(b) RETURN * $$) as (a agtype, b agtype, r agtype);

I hope this answers your question :).

ahmed_131313
  • 142
  • 6
1

In AGE, you are expected to provide a specific set of column definitions. In your case, you're using RETURN *, which returns all columns dynamically. Since the exact columns are not known in advance, you are getting the error.

Sadeed Ahmad
  • 83
  • 3
1

You are facing this error because the columns that the "cypher" function is returning is not equal to the number of columns that are stated in the 'AS' statement. Hence, the function is generating several columns but they are being changed into a single column with the identity 'v'.

Haseeb Ashraf
  • 205
  • 2
1

You have to specify the columns that will be returned in the query instead of just using asterisk * because it returns all columns.

Aadil Bashir
  • 111
  • 5
0
SELECT *
FROM cypher('test', $$ MATCH p=({name: 'agens-graph'})-[{lang: 'java'}]->(m) RETURN * $$)
  AS (data agtype);

We have to define what we are getting back as return expects a column number but you can use a placeholder value to keep it dynamic. It is usually advised to know the exact number.

Shanzay
  • 19
  • 3
0

Following this example:

MATCH (a {name: 'A'})-[r]->(b)
RETURN a, b, r

in:

(a {name: 'A'})-[r]->(b)

is the pattern you want to match. The RETURN clause specifies that you want to return all three variables (a, b, and r).

Marcos Silva
  • 115
  • 5
0

There is no support yet in AGE to return undetermined number of columns, but also as per the docs when you return * as in RETURN * it returns 3 columns mainly which you need to specify as (a agtype, b agtype, r agtype); and these 3 columns always represent the vertex, the edge, the path/relationship for every matched row.

Peter
  • 43
  • 4
0

The error that you are getting 'return row and column definition list do not match' is because you did not specify the number of columns to be returned. AS (* agtype) is not supported right now.

Prachi
  • 39
  • 3
0

While using the Cypher command RETURN * , you are requesting all columns dynamically, however according to AGE you have to provide specific set of column definitions in advance.

Hence to avoid this error, you have to explicitly specify the columns that will be returned in the query instead of just using * .

Shelender Kumar
  • 157
  • 9
0

When using RETURN * in your Cypher query, you are required to explicitly state the amount of columns that should be returned, per the documentation for the RETURN clause. This means that in AGType-based queries, there isn't yet support for a wildcard similar to *. Consider the following instance to help clarify this idea:

Using RETURN * alone isn't adequate when using the Cypher query inside the cypher() function to collect results from a graph with the name "graph_name," if you want to match a pattern where a node labeled "A" is connected to another node through a relationship "r." Instead, you must specifically state how many columns and which data types will be delivered.

SELECT *
FROM cypher('graph_name', $$
MATCH (a {name: 'A'})-[r]->(b)
RETURN *
$$) as (a agtype, b agtype, r agtype);

In this updated query, we are telling the computer to treat the Cypher query's results as three distinct columns, "a," "b," and "r," each of type "agtype." To guarantee that the query provides the desired data and complies with the guidelines controlling the RETURN clause in AGType-based queries, this level of specificity is required.

Raja Rakshak
  • 168
  • 2