This is an automated email from the ASF dual-hosted git repository.
mtaha pushed a commit to branch PG16
in repository https://gitbox.apache.org/repos/asf/age.git
The following commit(s) were added to refs/heads/PG16 by this push:
new 28a3ac74 Fix issue 1767: CREATE TABLE AS SELECT * FROM cypher, errors
(#1804)
28a3ac74 is described below
commit 28a3ac7436932e01d13525a1583ff0c4af54b621
Author: John Gemignani <jrgemign...@gmail.com>
AuthorDate: Fri Apr 26 08:07:20 2024 -0700
Fix issue 1767: CREATE TABLE AS SELECT * FROM cypher, errors (#1804)
Fixed issue 1767: create table as select * from cypher()... throws
an error -
ERROR: unhandled cypher(cstring) function call
CREATE TABLE AS is a utility command and therefore is not seen by the
query tree walker. This is by design, per the query_tree_walker code
comments. This is why the cypher command wasn't transformed causing
the above error.
The fix was to add a check for utility commands in the Query section
of the convert_cypher_walker. If found, the embedded query from
CreateTableAsStmts nodes is extracted and processed.
NOTE: This only enables the utility command CreateTableAsStmts nodes.
Others will need to be added on a case by case basis.
Added regression tests.
---
regress/expected/cypher.out | 112 ++++++++++++++++++++++++++++++++++++
regress/sql/cypher.sql | 41 +++++++++++++
src/backend/parser/cypher_analyze.c | 21 +++++++
3 files changed, 174 insertions(+)
diff --git a/regress/expected/cypher.out b/regress/expected/cypher.out
index d2b569b5..31bafc6c 100644
--- a/regress/expected/cypher.out
+++ b/regress/expected/cypher.out
@@ -111,6 +111,115 @@ SELECT * FROM cypher('cypher', $$RETURN 0$$) AS (c oid);
ERROR: cannot cast type agtype to oid for column "c"
LINE 1: SELECT * FROM cypher('cypher', $$RETURN 0$$) AS (c oid);
^
+--
+-- Issue 1767: create table as select * from cypher()... throw an error
+-- This is due to the convert_cypher_walker not recognizing
+-- utility commands.
+-- create our graph
+SELECT * FROM create_graph('issue_1767');
+NOTICE: graph "issue_1767" has been created
+ create_graph
+--------------
+
+(1 row)
+
+-- add in 5 relationships
+SELECT * FROM cypher('issue_1767', $$ CREATE ()-[:knows]->() $$) AS (result
agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('issue_1767', $$ CREATE ()-[:knows]->() $$) AS (result
agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('issue_1767', $$ CREATE ()-[:knows]->() $$) AS (result
agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('issue_1767', $$ CREATE ()-[:knows]->() $$) AS (result
agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('issue_1767', $$ CREATE ()-[:knows]->() $$) AS (result
agtype);
+ result
+--------
+(0 rows)
+
+-- show the 5 relationships
+SELECT * FROM cypher('issue_1767', $$ MATCH ()-[e]->() RETURN e $$) as (e
agtype);
+ e
+---------------------------------------------------------------------------------------------------------------------------
+ {"id": 844424930131969, "label": "knows", "end_id": 281474976710658,
"start_id": 281474976710657, "properties": {}}::edge
+ {"id": 844424930131970, "label": "knows", "end_id": 281474976710660,
"start_id": 281474976710659, "properties": {}}::edge
+ {"id": 844424930131971, "label": "knows", "end_id": 281474976710662,
"start_id": 281474976710661, "properties": {}}::edge
+ {"id": 844424930131972, "label": "knows", "end_id": 281474976710664,
"start_id": 281474976710663, "properties": {}}::edge
+ {"id": 844424930131973, "label": "knows", "end_id": 281474976710666,
"start_id": 281474976710665, "properties": {}}::edge
+(5 rows)
+
+-- create table my_vertices from the vertices. should be 10 rows.
+CREATE TABLE my_vertices AS
+ (SELECT * FROM cypher('issue_1767', $$ MATCH (u) RETURN u $$) as (u
agtype));
+-- create table my_edges from the edges. should be 5 rows
+CREATE TABLE my_edges AS
+ (SELECT * FROM cypher('issue_1767', $$ MATCH ()-[e]->() RETURN e $$) as (e
agtype));
+-- create a table of 4 columns, u, e, v, p. should be 5 rows
+CREATE TABLE my_detailed_paths AS
+ (SELECT * FROM cypher('issue_1767', $$ MATCH p=(u)-[e]->(v) RETURN u,e,v,p
$$) as (u agtype, e agtype, v agtype, p agtype));
+-- dump out the tables
+SELECT * FROM my_vertices;
+ u
+----------------------------------------------------------------
+ {"id": 281474976710657, "label": "", "properties": {}}::vertex
+ {"id": 281474976710658, "label": "", "properties": {}}::vertex
+ {"id": 281474976710659, "label": "", "properties": {}}::vertex
+ {"id": 281474976710660, "label": "", "properties": {}}::vertex
+ {"id": 281474976710661, "label": "", "properties": {}}::vertex
+ {"id": 281474976710662, "label": "", "properties": {}}::vertex
+ {"id": 281474976710663, "label": "", "properties": {}}::vertex
+ {"id": 281474976710664, "label": "", "properties": {}}::vertex
+ {"id": 281474976710665, "label": "", "properties": {}}::vertex
+ {"id": 281474976710666, "label": "", "properties": {}}::vertex
+(10 rows)
+
+SELECT * FROM my_edges;
+ e
+---------------------------------------------------------------------------------------------------------------------------
+ {"id": 844424930131969, "label": "knows", "end_id": 281474976710658,
"start_id": 281474976710657, "properties": {}}::edge
+ {"id": 844424930131970, "label": "knows", "end_id": 281474976710660,
"start_id": 281474976710659, "properties": {}}::edge
+ {"id": 844424930131971, "label": "knows", "end_id": 281474976710662,
"start_id": 281474976710661, "properties": {}}::edge
+ {"id": 844424930131972, "label": "knows", "end_id": 281474976710664,
"start_id": 281474976710663, "properties": {}}::edge
+ {"id": 844424930131973, "label": "knows", "end_id": 281474976710666,
"start_id": 281474976710665, "properties": {}}::edge
+(5 rows)
+
+SELECT * FROM my_detailed_paths;
+ u |
e
| v
|
p
[...]
+----------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
+ {"id": 281474976710657, "label": "", "properties": {}}::vertex | {"id":
844424930131969, "label": "knows", "end_id": 281474976710658, "start_id":
281474976710657, "properties": {}}::edge | {"id": 281474976710658, "label": "",
"properties": {}}::vertex | [{"id": 281474976710657, "label": "", "properties":
{}}::vertex, {"id": 844424930131969, "label": "knows", "end_id":
281474976710658, "start_id": 281474976710657, "properties": {}}::edge, {"id":
281474976710658, "label": "", "properties" [...]
+ {"id": 281474976710659, "label": "", "properties": {}}::vertex | {"id":
844424930131970, "label": "knows", "end_id": 281474976710660, "start_id":
281474976710659, "properties": {}}::edge | {"id": 281474976710660, "label": "",
"properties": {}}::vertex | [{"id": 281474976710659, "label": "", "properties":
{}}::vertex, {"id": 844424930131970, "label": "knows", "end_id":
281474976710660, "start_id": 281474976710659, "properties": {}}::edge, {"id":
281474976710660, "label": "", "properties" [...]
+ {"id": 281474976710661, "label": "", "properties": {}}::vertex | {"id":
844424930131971, "label": "knows", "end_id": 281474976710662, "start_id":
281474976710661, "properties": {}}::edge | {"id": 281474976710662, "label": "",
"properties": {}}::vertex | [{"id": 281474976710661, "label": "", "properties":
{}}::vertex, {"id": 844424930131971, "label": "knows", "end_id":
281474976710662, "start_id": 281474976710661, "properties": {}}::edge, {"id":
281474976710662, "label": "", "properties" [...]
+ {"id": 281474976710663, "label": "", "properties": {}}::vertex | {"id":
844424930131972, "label": "knows", "end_id": 281474976710664, "start_id":
281474976710663, "properties": {}}::edge | {"id": 281474976710664, "label": "",
"properties": {}}::vertex | [{"id": 281474976710663, "label": "", "properties":
{}}::vertex, {"id": 844424930131972, "label": "knows", "end_id":
281474976710664, "start_id": 281474976710663, "properties": {}}::edge, {"id":
281474976710664, "label": "", "properties" [...]
+ {"id": 281474976710665, "label": "", "properties": {}}::vertex | {"id":
844424930131973, "label": "knows", "end_id": 281474976710666, "start_id":
281474976710665, "properties": {}}::edge | {"id": 281474976710666, "label": "",
"properties": {}}::vertex | [{"id": 281474976710665, "label": "", "properties":
{}}::vertex, {"id": 844424930131973, "label": "knows", "end_id":
281474976710666, "start_id": 281474976710665, "properties": {}}::edge, {"id":
281474976710666, "label": "", "properties" [...]
+(5 rows)
+
+-- cleanup
+DROP TABLE my_vertices;
+DROP TABLE my_edges;
+DROP TABLE my_detailed_paths;
+SELECT drop_graph('issue_1767', true);
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table issue_1767._ag_label_vertex
+drop cascades to table issue_1767._ag_label_edge
+drop cascades to table issue_1767.knows
+NOTICE: graph "issue_1767" has been dropped
+ drop_graph
+------------
+
+(1 row)
+
SELECT drop_graph('cypher', true);
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table cypher._ag_label_vertex
@@ -121,3 +230,6 @@ NOTICE: graph "cypher" has been dropped
(1 row)
+--
+-- End
+--
diff --git a/regress/sql/cypher.sql b/regress/sql/cypher.sql
index 6f375451..7ded61ee 100644
--- a/regress/sql/cypher.sql
+++ b/regress/sql/cypher.sql
@@ -68,4 +68,45 @@ SELECT (SELECT * FROM cypher('cypher', $$RETURN 0$$) AS r(c
agtype));
SELECT * FROM cypher('cypher', $$RETURN true$$) AS (c bool);
SELECT * FROM cypher('cypher', $$RETURN 0$$) AS (c oid);
+--
+-- Issue 1767: create table as select * from cypher()... throw an error
+-- This is due to the convert_cypher_walker not recognizing
+-- utility commands.
+
+-- create our graph
+SELECT * FROM create_graph('issue_1767');
+-- add in 5 relationships
+SELECT * FROM cypher('issue_1767', $$ CREATE ()-[:knows]->() $$) AS (result
agtype);
+SELECT * FROM cypher('issue_1767', $$ CREATE ()-[:knows]->() $$) AS (result
agtype);
+SELECT * FROM cypher('issue_1767', $$ CREATE ()-[:knows]->() $$) AS (result
agtype);
+SELECT * FROM cypher('issue_1767', $$ CREATE ()-[:knows]->() $$) AS (result
agtype);
+SELECT * FROM cypher('issue_1767', $$ CREATE ()-[:knows]->() $$) AS (result
agtype);
+-- show the 5 relationships
+SELECT * FROM cypher('issue_1767', $$ MATCH ()-[e]->() RETURN e $$) as (e
agtype);
+
+-- create table my_vertices from the vertices. should be 10 rows.
+CREATE TABLE my_vertices AS
+ (SELECT * FROM cypher('issue_1767', $$ MATCH (u) RETURN u $$) as (u
agtype));
+-- create table my_edges from the edges. should be 5 rows
+CREATE TABLE my_edges AS
+ (SELECT * FROM cypher('issue_1767', $$ MATCH ()-[e]->() RETURN e $$) as (e
agtype));
+-- create a table of 4 columns, u, e, v, p. should be 5 rows
+CREATE TABLE my_detailed_paths AS
+ (SELECT * FROM cypher('issue_1767', $$ MATCH p=(u)-[e]->(v) RETURN u,e,v,p
$$) as (u agtype, e agtype, v agtype, p agtype));
+
+-- dump out the tables
+SELECT * FROM my_vertices;
+SELECT * FROM my_edges;
+SELECT * FROM my_detailed_paths;
+
+-- cleanup
+DROP TABLE my_vertices;
+DROP TABLE my_edges;
+DROP TABLE my_detailed_paths;
+
+SELECT drop_graph('issue_1767', true);
SELECT drop_graph('cypher', true);
+
+--
+-- End
+--
diff --git a/src/backend/parser/cypher_analyze.c
b/src/backend/parser/cypher_analyze.c
index d69cc055..50ecdde7 100644
--- a/src/backend/parser/cypher_analyze.c
+++ b/src/backend/parser/cypher_analyze.c
@@ -211,6 +211,27 @@ static bool convert_cypher_walker(Node *node, ParseState
*pstate)
bool result = false;
Query *query = (Query *)node;
+ /*
+ * If this is a utility command, we need to unwrap the internal query
+ * and pass it as the query.
+ *
+ * NOTE: This code only "knows" about the following utility commands -
+ *
+ * CREATE TABLE AS
+ *
+ * Others need to be added on a case by case basis.
+ */
+ if (query->utilityStmt != NULL &&
+ IsA(query->utilityStmt, CreateTableAsStmt))
+ {
+ CreateTableAsStmt *ctas = (CreateTableAsStmt *)query->utilityStmt;
+
+ if (IsA(ctas->query, Query))
+ {
+ query = (Query *)ctas->query;
+ }
+ }
+
/*
* QTW_EXAMINE_RTES
* We convert RTE_FUNCTION (cypher()) to RTE_SUBQUERY (SELECT)
