Hi all, I made a super simple extension `https://github.com/Z-Xiao-M/pg_pgq2sql` to get the equivalent SQL of PGQ queries ?C it simply calls `pg_get_querydef` after `QueryRewrite` to fetch the SQL text.
However, I noticed that some FROM clauses were missing in the generated SQL
statements.
```
postgres=# SELECT * FROM pg_pgq2sql($$
SELECT common_name
FROM GRAPH_TABLE (
social_graph
MATCH (a IS users)-[]->(x IS users)<-[]-(b IS users)
WHERE a.name = 'Alice' AND b.name = 'Bob'
COLUMNS (x.name AS common_name)
)
$$);
pg_pgq2sql
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT common_name
+
FROM LATERAL ( SELECT users_1.name AS common_name
+
WHERE users.id = follows.follower AND users_1.id = follows.following
AND users_2.id = follows_1.follower AND users_1.id = follows_1.following AND
users.name::text = 'Alice'::text AND users_2.name::text = 'Bob'::text)
"graph_table"
(1 row)
```
I did a quick check and found the issue: some `rte->inFromCl` were set to
`false` (should be `true` here). Here??s a quick patch to fix it:
```
postgres@zxm-VMware-Virtual-Platform:~/code/postgres$ git diff
diff --git a/src/backend/rewrite/rewriteGraphTable.c
b/src/backend/rewrite/rewriteGraphTable.c
index 06f2f3442d8..d43704ef233 100644
--- a/src/backend/rewrite/rewriteGraphTable.c
+++ b/src/backend/rewrite/rewriteGraphTable.c
@@ -498,7 +498,7 @@ generate_query_for_graph_path(RangeTblEntry *rte, List
*graph_path)
*/
rel = table_open(pe->reloid, AccessShareLock);
pni = addRangeTableEntryForRelation(make_parsestate(NULL), rel,
AccessShareLock,
-
NULL, true, false);
+
NULL, true, true);
table_close(rel, NoLock);
path_query->rtable = lappend(path_query->rtable, pni->p_rte);
path_query->rteperminfos = lappend(path_query->rteperminfos,
pni->p_perminfo);
```
After applying the patch, the FROM clause is now complete:
```
postgres=# SELECT * FROM pg_pgq2sql($$
SELECT common_name
FROM GRAPH_TABLE (
social_graph
MATCH (a IS users)-[]->(x IS users)<-[]-(b IS users)
WHERE a.name = 'Alice' AND b.name = 'Bob'
COLUMNS (x.name AS common_name)
)
$$);
pg_pgq2sql
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT common_name
+
FROM LATERAL ( SELECT users_1.name AS common_name
+
FROM users,
+
follows,
+
users users_1,
+
follows follows_1,
+
users users_2
+
WHERE users.id = follows.follower AND users_1.id = follows.following
AND users_2.id = follows_1.follower AND users_1.id = follows_1.following AND
users.name::text = 'Alice'::text AND users_2.name::text = 'Bob'::text)
"graph_table"
(1 row)
postgres=#
postgres=# SELECT common_name
FROM GRAPH_TABLE (
social_graph
MATCH (a IS users)-[]->(x IS users)<-[]-(b IS users)
WHERE a.name = 'Alice' AND b.name = 'Bob'
COLUMNS (x.name AS common_name)
) ;
common_name
-------------
Charlie
(1 row)
postgres=# SELECT common_name
FROM LATERAL ( SELECT users_1.name AS common_name
FROM users,
follows,
users users_1,
follows follows_1,
users users_2
WHERE users.id = follows.follower AND users_1.id = follows.following
AND users_2.id = follows_1.follower AND users_1.id = follows_1.following AND
users.name::text = 'Alice'::text AND users_2.name::text = 'Bob'::text)
"graph_table";
common_name
-------------
Charlie
(1 row)
```
Curious to hear your thoughts/suggestions on this.
--
regards,
Man Zeng
0001-Correcting-the-value-of-rte-inFromCl-in-SQL-PGQ.patch
Description: Binary data
