Why does the Hybrid Query return an empty result

[dukeofhazardz]

I encountered an issue while performing a hybrid query using an SQL table and an AGE graph. The query is intended to retrieve rows from the SQL table that match the city values returned by a Cypher query on the AGE graph. However, the query does not return any results, even though there are matching city values present in both the SQL table and the AGE graph.

Steps to Reproduce:

• Create an SQL table named Persons with columns: id, name, city, and hired_year.
• Insert sample data into the Persons table.
• Create an AGE graph named test with label: Person and properties: name, city, and role.
• Perform the hybrid query, matching the city values from the SQL table with the city values returned by the Cypher query.

I created the SQL Table Persons.

demo=# SELECT * FROM Persons;
 id  |    name    |  city  | hired_year
-----+------------+--------+------------
 123 | "Daniel"   | Lagos  |       2023
 124 | "Emmanuel" | Vancouver  |       2018
 125 | "Grace"  | Sydney |       2021
 127 | "Sam"      | Lisbon  |       2020
 121 | "Precious" | Toronto |       2021
 122 | "Fred"     | Phoenix  |       2023
(6 rows)

And the AGE Graph test

demo=# SELECT * FROM cypher('test', $$
demo$# Match (u)
demo$# RETURN u
demo$# $$) as (a agtype);
                                                                a
---------------------------------------------------------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "Person", "properties": {"city": "Lagos", "name": "Daniel", "role": "Developer"}}::vertex
 {"id": 844424930131970, "label": "Person", "properties": {"city": "Vancouver", "name": "Emmanuel", "role": "Data Analyst"}}::vertex
 {"id": 844424930131971, "label": "Person", "properties": {"city": "Sydney", "name": "Grace", "role": "Accountant"}}::vertex
 {"id": 844424930131972, "label": "Person", "properties": {"city": "Lisbon", "name": "Sam", "role": "Designer"}}::vertex
 {"id": 844424930131973, "label": "Person", "properties": {"city": "Toronto", "name": "Precious", "role": "Designer"}}::vertex
 {"id": 844424930131974, "label": "Person", "properties": {"city": "Phoenix", "name": "Fred", "role": "Manager"}}::vertex
(6 rows)

While performing this hybrid query using an SQL Table and AGE graph

demo=# SELECT * FROM Persons AS p
demo-# WHERE p.city in (SELECT city FROM cypher('test', $$
demo$# MATCH (c: Person {city: 'Lagos'})
demo$# RETURN c.city
demo$# $$) AS (city VARCHAR));
 id | name | city | hired_year
----+------+------+------------
(0 rows)

It returns an empty table.

Expected Behavior: The hybrid query should return the rows from the Persons table that have matching city values with the city values returned by the Cypher query. The result should not be an empty table.

Actual Behavior: The hybrid query does not return any results. The table is empty, despite the existence of matching city values in both the SQL table and the AGE graph.

Additional Information:

PostgreSQL version: 11.17 AGE extension version: 1.3.0 Environment: Command Line

[jrgemignani]

@dukeofhazardz Did you run the interior cypher command to verify that its output is as expected? And that the formats are the same?

[dukeofhazardz]

@jrgemignani Yes I ran the interior cypher command thus:

demo=# SELECT city FROM cypher('test', $$
demo$# MATCH (c: Person {city: 'Lagos'})
demo$# RETURN c.city
demo$# $$) AS (city VARCHAR);
  city
---------
 "Lagos"
(1 row)

And the output is as expected, but the hybrid query returns an empty table

[jrgemignani]

It is likely that the representation returned is not the same as that stored in PG. For example, one may be terminated differently from the other. You might want to add on a toString function. I vaguely remember this issue happening before.

[Amr-Shams]

CloseSession=# (SELECT city FROM cypher('test2', $$MATCH (c:Person {city: 'Lagos'}) RETURN c.city$$) as (city VARCHAR(50)));
  city
---------
 "Lagos"
 "Lagos"
(2 rows)

CloseSession=# SELECT city from Persons p WHERE p.city = 'Lagos';
 city
-------
 Lagos
 Lagos
(2 rows)

the obvious difference is the double quote in the response of the cypher type so easily you can use a trimfunction to remove the double quotes from the city return by the cypher here is an updated query that ran

SELECT * FROM Persons p WHERE p.city IN (SELECT TRIM(both '"' from city) FROM cypher('test2', $$MATCH (c:Person {city: 'Lagos'}) RETURN c.city$$) as (city VARCHAR));

 id |  name  | city  | hired_year
----+--------+-------+------------
  1 | Daniel | Lagos |       2023
  7 | Daniel | Lagos |       2023

[dukeofhazardz]

@Amr-Shams Thanks for your input! I tried out the updated query you provided, and it worked perfectly for me. It seems that the inconsistency in quotes used when creating the tables and retrieving the data from the table was the issue. Removing the double quotes from the city returned by the cypher using the TRIM function resolved the problem. Thanks again for your help!