You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@age.apache.org by "dukeofhazardz (via GitHub)" <gi...@apache.org> on 2023/06/07 09:00:26 UTC
[GitHub] [age] dukeofhazardz opened a new issue, #972: Why does the Hybrid Query return an empty result
dukeofhazardz opened a new issue, #972:
URL: https://github.com/apache/age/issues/972
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.
I created the SQL Table `Persons`.
**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.
```
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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@age.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] jrgemignani commented on issue #972: Why does the Hybrid Query return an empty result
Posted by "jrgemignani (via GitHub)" <gi...@apache.org>.
jrgemignani commented on issue #972:
URL: https://github.com/apache/age/issues/972#issuecomment-1581559356
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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] Amr-Shams commented on issue #972: Why does the Hybrid Query return an empty result
Posted by "Amr-Shams (via GitHub)" <gi...@apache.org>.
Amr-Shams commented on issue #972:
URL: https://github.com/apache/age/issues/972#issuecomment-1583112592
```
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
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] jrgemignani commented on issue #972: Why does the Hybrid Query return an empty result
Posted by "jrgemignani (via GitHub)" <gi...@apache.org>.
jrgemignani commented on issue #972:
URL: https://github.com/apache/age/issues/972#issuecomment-1581244394
@dukeofhazardz Did you run the interior cypher command to verify that its output is as expected? And that the formats are the same?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] dukeofhazardz commented on issue #972: Why does the Hybrid Query return an empty result
Posted by "dukeofhazardz (via GitHub)" <gi...@apache.org>.
dukeofhazardz commented on issue #972:
URL: https://github.com/apache/age/issues/972#issuecomment-1581441938
@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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] dukeofhazardz commented on issue #972: Why does the Hybrid Query return an empty result
Posted by "dukeofhazardz (via GitHub)" <gi...@apache.org>.
dukeofhazardz commented on issue #972:
URL: https://github.com/apache/age/issues/972#issuecomment-1583451777
@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!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] dukeofhazardz closed issue #972: Why does the Hybrid Query return an empty result
Posted by "dukeofhazardz (via GitHub)" <gi...@apache.org>.
dukeofhazardz closed issue #972: Why does the Hybrid Query return an empty result
URL: https://github.com/apache/age/issues/972
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org