This is an automated email from the ASF dual-hosted git repository.
jgemignani pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/age.git
The following commit(s) were added to refs/heads/master by this push:
new 0bb5f20e Implement EXISTS subquery for CASE (#1345)
0bb5f20e is described below
commit 0bb5f20e4bd95950ccb85f916a236ceef2501497
Author: Dehowe Feng <[email protected]>
AuthorDate: Mon Nov 6 16:33:57 2023 -0800
Implement EXISTS subquery for CASE (#1345)
Modified logic of EXISTS to wrap itself in a agtype cast. This
allows EXISTS to work with in CASE statements, among other
implementations that may require an agtype.
Also renamed the wrapper function to be more generic to denote
its usage in other implementations if need be.
Added regression tests for EXISTS
---
regress/expected/expr.out | 90 ++++++++++++++++++++++++++++++++++------
regress/sql/expr.sql | 50 +++++++++++++++++++---
src/backend/parser/cypher_gram.y | 20 +++++----
3 files changed, 135 insertions(+), 25 deletions(-)
diff --git a/regress/expected/expr.out b/regress/expected/expr.out
index d7257228..43b40807 100644
--- a/regress/expected/expr.out
+++ b/regress/expected/expr.out
@@ -6514,8 +6514,8 @@ SELECT * FROM cypher('case_statement', $$
WHEN 1 THEN count(*)
ELSE 'not count'
END
-$$ ) AS (j agtype, case_statement agtype);
- j
| case_statement
+$$ ) AS (n agtype, case_statement agtype);
+ n
| case_statement
------------------------------------------------------------------------------------------------+----------------
{"id": 281474976710658, "label": "", "properties": {"i": "a", "j": "b", "id":
2}}::vertex | "not count"
{"id": 281474976710661, "label": "", "properties": {"i": [], "j": [0, 1, 2],
"id": 5}}::vertex | "not count"
@@ -6532,8 +6532,8 @@ SELECT * FROM cypher('case_statement', $$
WHEN 1 THEN count(*)
ELSE 'not count'
END
-$$ ) AS (j agtype, case_statement agtype);
- j
| case_statement
+$$ ) AS (n agtype, case_statement agtype);
+ n
| case_statement
------------------------------------------------------------------------------------------------+----------------
{"id": 281474976710658, "label": "", "properties": {"i": "a", "j": "b", "id":
2}}::vertex | "not count"
{"id": 281474976710661, "label": "", "properties": {"i": [], "j": [0, 1, 2],
"id": 5}}::vertex | "not count"
@@ -6550,8 +6550,8 @@ SELECT * FROM cypher('case_statement', $$
WHEN 1 THEN count(n)
ELSE 'not count'
END
-$$ ) AS (j agtype, case_statement agtype);
- j
| case_statement
+$$ ) AS (n agtype, case_statement agtype);
+ n
| case_statement
------------------------------------------------------------------------------------------------+----------------
{"id": 281474976710658, "label": "", "properties": {"i": "a", "j": "b", "id":
2}}::vertex | "not count"
{"id": 281474976710661, "label": "", "properties": {"i": [], "j": [0, 1, 2],
"id": 5}}::vertex | "not count"
@@ -6568,8 +6568,8 @@ SELECT * FROM cypher('case_statement', $$
WHEN 1 THEN count(n)
ELSE 'not count'
END
-$$ ) AS (j agtype, case_statement agtype);
- j
| case_statement
+$$ ) AS (n agtype, case_statement agtype);
+ n
| case_statement
------------------------------------------------------------------------------------------------+----------------
{"id": 281474976710658, "label": "", "properties": {"i": "a", "j": "b", "id":
2}}::vertex | "not count"
{"id": 281474976710661, "label": "", "properties": {"i": [], "j": [0, 1, 2],
"id": 5}}::vertex | "not count"
@@ -6586,8 +6586,8 @@ SELECT * FROM cypher('case_statement', $$
WHEN 1 THEN count(1)
ELSE 'not count'
END
-$$ ) AS (j agtype, case_statement agtype);
- j
| case_statement
+$$ ) AS (n agtype, case_statement agtype);
+ n
| case_statement
------------------------------------------------------------------------------------------------+----------------
{"id": 281474976710658, "label": "", "properties": {"i": "a", "j": "b", "id":
2}}::vertex | "not count"
{"id": 281474976710661, "label": "", "properties": {"i": [], "j": [0, 1, 2],
"id": 5}}::vertex | "not count"
@@ -6604,8 +6604,8 @@ SELECT * FROM cypher('case_statement', $$
WHEN 1 THEN count(1)
ELSE 'not count'
END
-$$ ) AS (j agtype, case_statement agtype);
- j
| case_statement
+$$ ) AS (n agtype, case_statement agtype);
+ n
| case_statement
------------------------------------------------------------------------------------------------+----------------
{"id": 281474976710658, "label": "", "properties": {"i": "a", "j": "b", "id":
2}}::vertex | "not count"
{"id": 281474976710661, "label": "", "properties": {"i": [], "j": [0, 1, 2],
"id": 5}}::vertex | "not count"
@@ -6615,6 +6615,72 @@ $$ ) AS (j agtype, case_statement agtype);
{"id": 281474976710662, "label": "", "properties": {"i": {}, "j": {"i": 1},
"id": 6}}::vertex | "not count"
(6 rows)
+--CASE with EXISTS()
+--exists(n.property)
+SELECT * FROM cypher('case_statement', $$
+ MATCH (n)
+ RETURN n, CASE exists(n.j)
+ WHEN true THEN 'property j exists'
+ ELSE 'property j does not exist'
+ END
+$$ ) AS (n agtype, case_statement agtype);
+ n
| case_statement
+------------------------------------------------------------------------------------------------+-----------------------------
+ {"id": 281474976710657, "label": "", "properties": {"i": 1, "id": 1}}::vertex
| "property j does not exist"
+ {"id": 281474976710658, "label": "", "properties": {"i": "a", "j": "b", "id":
2}}::vertex | "property j exists"
+ {"id": 281474976710659, "label": "", "properties": {"i": 0, "j": 1, "id":
3}}::vertex | "property j exists"
+ {"id": 281474976710660, "label": "", "properties": {"i": true, "j": false,
"id": 4}}::vertex | "property j exists"
+ {"id": 281474976710661, "label": "", "properties": {"i": [], "j": [0, 1, 2],
"id": 5}}::vertex | "property j exists"
+ {"id": 281474976710662, "label": "", "properties": {"i": {}, "j": {"i": 1},
"id": 6}}::vertex | "property j exists"
+(6 rows)
+
+--CASE evaluates to boolean true, is not a boolean, should hit ELSE
+SELECT * FROM cypher('case_statement', $$
+ MATCH (n)
+ RETURN n, CASE exists(n.j)
+ WHEN 1 THEN 'should not output me'
+ ELSE '1 is not a boolean'
+ END
+$$ ) AS (n agtype, case_statement agtype);
+ n
| case_statement
+------------------------------------------------------------------------------------------------+----------------------
+ {"id": 281474976710657, "label": "", "properties": {"i": 1, "id": 1}}::vertex
| "1 is not a boolean"
+ {"id": 281474976710658, "label": "", "properties": {"i": "a", "j": "b", "id":
2}}::vertex | "1 is not a boolean"
+ {"id": 281474976710659, "label": "", "properties": {"i": 0, "j": 1, "id":
3}}::vertex | "1 is not a boolean"
+ {"id": 281474976710660, "label": "", "properties": {"i": true, "j": false,
"id": 4}}::vertex | "1 is not a boolean"
+ {"id": 281474976710661, "label": "", "properties": {"i": [], "j": [0, 1, 2],
"id": 5}}::vertex | "1 is not a boolean"
+ {"id": 281474976710662, "label": "", "properties": {"i": {}, "j": {"i": 1},
"id": 6}}::vertex | "1 is not a boolean"
+(6 rows)
+
+--exists in WHEN, vacuously false because exists(n.j) evaluates to a boolean,
n is a vertex
+SELECT * FROM cypher('case_statement', $$
+ MATCH (n)
+ RETURN n, CASE n
+ WHEN exists(n.j) THEN 'should not output me'
+ ELSE 'n is a vertex, not a boolean'
+ END
+$$ ) AS (j agtype, case_statement agtype);
+ j
| case_statement
+------------------------------------------------------------------------------------------------+--------------------------------
+ {"id": 281474976710657, "label": "", "properties": {"i": 1, "id": 1}}::vertex
| "n is a vertex, not a boolean"
+ {"id": 281474976710658, "label": "", "properties": {"i": "a", "j": "b", "id":
2}}::vertex | "n is a vertex, not a boolean"
+ {"id": 281474976710659, "label": "", "properties": {"i": 0, "j": 1, "id":
3}}::vertex | "n is a vertex, not a boolean"
+ {"id": 281474976710660, "label": "", "properties": {"i": true, "j": false,
"id": 4}}::vertex | "n is a vertex, not a boolean"
+ {"id": 281474976710661, "label": "", "properties": {"i": [], "j": [0, 1, 2],
"id": 5}}::vertex | "n is a vertex, not a boolean"
+ {"id": 281474976710662, "label": "", "properties": {"i": {}, "j": {"i": 1},
"id": 6}}::vertex | "n is a vertex, not a boolean"
+(6 rows)
+
+--exists(*) (should fail)
+SELECT * FROM cypher('case_statement', $$
+ MATCH (n)
+ RETURN n, CASE n.j
+ WHEN 1 THEN exists(*)
+ ELSE 'not count'
+ END
+$$ ) AS (j agtype, case_statement agtype);
+ERROR: syntax error at or near "*"
+LINE 4: WHEN 1 THEN exists(*)
+ ^
-- RETURN * and (u)--(v) optional forms
SELECT create_graph('opt_forms');
NOTICE: graph "opt_forms" has been created
diff --git a/regress/sql/expr.sql b/regress/sql/expr.sql
index 0aa62a0b..6f7c5853 100644
--- a/regress/sql/expr.sql
+++ b/regress/sql/expr.sql
@@ -2726,7 +2726,7 @@ SELECT * FROM cypher('case_statement', $$
WHEN 1 THEN count(*)
ELSE 'not count'
END
-$$ ) AS (j agtype, case_statement agtype);
+$$ ) AS (n agtype, case_statement agtype);
--concatenated
SELECT * FROM cypher('case_statement', $$
@@ -2735,7 +2735,7 @@ SELECT * FROM cypher('case_statement', $$
WHEN 1 THEN count(*)
ELSE 'not count'
END
-$$ ) AS (j agtype, case_statement agtype);
+$$ ) AS (n agtype, case_statement agtype);
--count(n)
SELECT * FROM cypher('case_statement', $$
@@ -2744,7 +2744,7 @@ SELECT * FROM cypher('case_statement', $$
WHEN 1 THEN count(n)
ELSE 'not count'
END
-$$ ) AS (j agtype, case_statement agtype);
+$$ ) AS (n agtype, case_statement agtype);
--concatenated
SELECT * FROM cypher('case_statement', $$
@@ -2753,7 +2753,7 @@ SELECT * FROM cypher('case_statement', $$
WHEN 1 THEN count(n)
ELSE 'not count'
END
-$$ ) AS (j agtype, case_statement agtype);
+$$ ) AS (n agtype, case_statement agtype);
--count(1)
SELECT * FROM cypher('case_statement', $$
@@ -2762,7 +2762,7 @@ SELECT * FROM cypher('case_statement', $$
WHEN 1 THEN count(1)
ELSE 'not count'
END
-$$ ) AS (j agtype, case_statement agtype);
+$$ ) AS (n agtype, case_statement agtype);
--concatenated
SELECT * FROM cypher('case_statement', $$
@@ -2771,8 +2771,48 @@ SELECT * FROM cypher('case_statement', $$
WHEN 1 THEN count(1)
ELSE 'not count'
END
+$$ ) AS (n agtype, case_statement agtype);
+
+--CASE with EXISTS()
+
+--exists(n.property)
+SELECT * FROM cypher('case_statement', $$
+ MATCH (n)
+ RETURN n, CASE exists(n.j)
+ WHEN true THEN 'property j exists'
+ ELSE 'property j does not exist'
+ END
+$$ ) AS (n agtype, case_statement agtype);
+
+--CASE evaluates to boolean true, is not a boolean, should hit ELSE
+SELECT * FROM cypher('case_statement', $$
+ MATCH (n)
+ RETURN n, CASE exists(n.j)
+ WHEN 1 THEN 'should not output me'
+ ELSE '1 is not a boolean'
+ END
+$$ ) AS (n agtype, case_statement agtype);
+
+--exists in WHEN, vacuously false because exists(n.j) evaluates to a boolean,
n is a vertex
+SELECT * FROM cypher('case_statement', $$
+ MATCH (n)
+ RETURN n, CASE n
+ WHEN exists(n.j) THEN 'should not output me'
+ ELSE 'n is a vertex, not a boolean'
+ END
$$ ) AS (j agtype, case_statement agtype);
+--exists(*) (should fail)
+SELECT * FROM cypher('case_statement', $$
+ MATCH (n)
+ RETURN n, CASE n.j
+ WHEN 1 THEN exists(*)
+ ELSE 'not count'
+ END
+$$ ) AS (j agtype, case_statement agtype);
+
+
+
-- RETURN * and (u)--(v) optional forms
SELECT create_graph('opt_forms');
diff --git a/src/backend/parser/cypher_gram.y b/src/backend/parser/cypher_gram.y
index 6ba8cc61..4f7fddf5 100644
--- a/src/backend/parser/cypher_gram.y
+++ b/src/backend/parser/cypher_gram.y
@@ -227,7 +227,7 @@ static Node *make_typecast_expr(Node *expr, char *typecast,
int location);
static Node *make_function_expr(List *func_name, List *exprs, int location);
static Node *make_star_function_expr(List *func_name, List *exprs, int
location);
static Node *make_distinct_function_expr(List *func_name, List *exprs, int
location);
-static FuncCall *wrap_pg_funccall_to_agtype(Node* fnode, char *type, int
location);
+static FuncCall *node_to_agtype(Node* fnode, char *type, int location);
// setops
static Node *make_set_op(SetOperation op, bool all_or_distinct, List *larg,
@@ -1742,12 +1742,16 @@ expr_func_subexpr:
n->operName = NIL;
n->subselect = (Node *) sub;
n->location = @1;
- $$ = (Node *) n;
+ $$ = (Node *)node_to_agtype((Node *)n, "boolean", @1);
}
| EXISTS '(' property_value ')'
{
- $$ = make_function_expr(list_make1(makeString("exists")),
- list_make1($3), @2);
+ FuncCall *n;
+ n = makeFuncCall(list_make1(makeString("exists")),
+ list_make1($3), COERCE_SQL_SYNTAX, @2);
+
+ $$ = (Node *)node_to_agtype((Node *)n, "boolean", @2);
+
}
;
@@ -2289,7 +2293,7 @@ static Node *make_function_expr(List *func_name, List
*exprs, int location)
fnode = makeFuncCall(funcname, exprs, COERCE_SQL_SYNTAX, location);
/* build the cast to wrap the function call to return agtype. */
- fnode = wrap_pg_funccall_to_agtype((Node *)fnode, "integer",
location);
+ fnode = node_to_agtype((Node *)fnode, "integer", location);
return (Node *)fnode;
}
@@ -2345,7 +2349,7 @@ static Node *make_star_function_expr(List *func_name,
List *exprs, int location)
fnode->agg_star = true;
/* build the cast to wrap the function call to return agtype. */
- fnode = wrap_pg_funccall_to_agtype((Node *)fnode, "integer",
location);
+ fnode = node_to_agtype((Node *)fnode, "integer", location);
return (Node *)fnode;
}
@@ -2403,7 +2407,7 @@ static Node *make_distinct_function_expr(List *func_name,
List *exprs, int locat
fnode->agg_distinct = true;
/* build the cast to wrap the function call to return agtype. */
- fnode = wrap_pg_funccall_to_agtype((Node *)fnode, "integer",
location);
+ fnode = node_to_agtype((Node *)fnode, "integer", location);
return (Node *)fnode;
}
else
@@ -2434,7 +2438,7 @@ static Node *make_distinct_function_expr(List *func_name,
List *exprs, int locat
* helper function to wrap pg_function in the appropiate typecast function to
* interface with AGE components
*/
-static FuncCall *wrap_pg_funccall_to_agtype(Node * fnode, char *type, int
location)
+static FuncCall *node_to_agtype(Node * fnode, char *type, int location)
{
List *funcname = list_make1(makeString("ag_catalog"));
