This is an automated email from the ASF dual-hosted git repository. jgemignani pushed a commit to branch PG15 in repository https://gitbox.apache.org/repos/asf/age.git
commit b5d7bbbac463ac384ea9e0178352dc7bcc179128 Author: Dehowe Feng <[email protected]> AuthorDate: Mon Oct 30 14:39:22 2023 -0700 Modify COUNT() to output agtype (#1311) Modified the make_function_expr logic to wrap the PG COUNT() function with a cast to agtype. This enables COUNT() to be used as a subquery in CASE. Also added logic for casting for future PG function additions. This modification passes all regression tests. Also added regression tests for COUNT in CASE statements. --- regress/expected/expr.out | 109 ++++++++++++++++++++++++ regress/sql/expr.sql | 56 ++++++++++++ src/backend/parser/cypher_gram.y | 179 +++++++++++++++++++++++++++++++++++++-- 3 files changed, 335 insertions(+), 9 deletions(-) diff --git a/regress/expected/expr.out b/regress/expected/expr.out index 476a0d84..3edb7dd6 100644 --- a/regress/expected/expr.out +++ b/regress/expected/expr.out @@ -6503,6 +6503,115 @@ $$ ) AS (case_statement agtype); {"id": 844424930131970, "label": "connected_to", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {"k": 1, "id": 2}}::edge (2 rows) +--CASE with count() +--count(*) +SELECT * FROM cypher('case_statement', $$ + MATCH (n) + RETURN n, CASE n.j + WHEN 1 THEN count(*) + ELSE 'not count' + END +$$ ) AS (j agtype, case_statement agtype); + j | 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" + {"id": 281474976710657, "label": "", "properties": {"i": 1, "id": 1}}::vertex | "not count" + {"id": 281474976710659, "label": "", "properties": {"i": 0, "j": 1, "id": 3}}::vertex | 1 + {"id": 281474976710660, "label": "", "properties": {"i": true, "j": false, "id": 4}}::vertex | "not count" + {"id": 281474976710662, "label": "", "properties": {"i": {}, "j": {"i": 1}, "id": 6}}::vertex | "not count" +(6 rows) + +--concatenated +SELECT * FROM cypher('case_statement', $$ + MATCH (n) MATCH (m) + RETURN n, CASE n.j + WHEN 1 THEN count(*) + ELSE 'not count' + END +$$ ) AS (j agtype, case_statement agtype); + j | 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" + {"id": 281474976710657, "label": "", "properties": {"i": 1, "id": 1}}::vertex | "not count" + {"id": 281474976710659, "label": "", "properties": {"i": 0, "j": 1, "id": 3}}::vertex | 6 + {"id": 281474976710660, "label": "", "properties": {"i": true, "j": false, "id": 4}}::vertex | "not count" + {"id": 281474976710662, "label": "", "properties": {"i": {}, "j": {"i": 1}, "id": 6}}::vertex | "not count" +(6 rows) + +--count(n) +SELECT * FROM cypher('case_statement', $$ + MATCH (n) + RETURN n, CASE n.j + WHEN 1 THEN count(n) + ELSE 'not count' + END +$$ ) AS (j agtype, case_statement agtype); + j | 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" + {"id": 281474976710657, "label": "", "properties": {"i": 1, "id": 1}}::vertex | "not count" + {"id": 281474976710659, "label": "", "properties": {"i": 0, "j": 1, "id": 3}}::vertex | 1 + {"id": 281474976710660, "label": "", "properties": {"i": true, "j": false, "id": 4}}::vertex | "not count" + {"id": 281474976710662, "label": "", "properties": {"i": {}, "j": {"i": 1}, "id": 6}}::vertex | "not count" +(6 rows) + +--concatenated +SELECT * FROM cypher('case_statement', $$ + MATCH (n) MATCH (m) + RETURN n, CASE n.j + WHEN 1 THEN count(n) + ELSE 'not count' + END +$$ ) AS (j agtype, case_statement agtype); + j | 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" + {"id": 281474976710657, "label": "", "properties": {"i": 1, "id": 1}}::vertex | "not count" + {"id": 281474976710659, "label": "", "properties": {"i": 0, "j": 1, "id": 3}}::vertex | 6 + {"id": 281474976710660, "label": "", "properties": {"i": true, "j": false, "id": 4}}::vertex | "not count" + {"id": 281474976710662, "label": "", "properties": {"i": {}, "j": {"i": 1}, "id": 6}}::vertex | "not count" +(6 rows) + +--count(1) +SELECT * FROM cypher('case_statement', $$ + MATCH (n) + RETURN n, CASE n.j + WHEN 1 THEN count(1) + ELSE 'not count' + END +$$ ) AS (j agtype, case_statement agtype); + j | 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" + {"id": 281474976710657, "label": "", "properties": {"i": 1, "id": 1}}::vertex | "not count" + {"id": 281474976710659, "label": "", "properties": {"i": 0, "j": 1, "id": 3}}::vertex | 1 + {"id": 281474976710660, "label": "", "properties": {"i": true, "j": false, "id": 4}}::vertex | "not count" + {"id": 281474976710662, "label": "", "properties": {"i": {}, "j": {"i": 1}, "id": 6}}::vertex | "not count" +(6 rows) + +--concatenated +SELECT * FROM cypher('case_statement', $$ + MATCH (n) MATCH (m) + RETURN n, CASE n.j + WHEN 1 THEN count(1) + ELSE 'not count' + END +$$ ) AS (j agtype, case_statement agtype); + j | 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" + {"id": 281474976710657, "label": "", "properties": {"i": 1, "id": 1}}::vertex | "not count" + {"id": 281474976710659, "label": "", "properties": {"i": 0, "j": 1, "id": 3}}::vertex | 6 + {"id": 281474976710660, "label": "", "properties": {"i": true, "j": false, "id": 4}}::vertex | "not count" + {"id": 281474976710662, "label": "", "properties": {"i": {}, "j": {"i": 1}, "id": 6}}::vertex | "not count" +(6 rows) + -- 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 bf9ffc94..931159b6 100644 --- a/regress/sql/expr.sql +++ b/regress/sql/expr.sql @@ -2718,6 +2718,62 @@ SELECT * FROM cypher('case_statement', $$ END $$ ) AS (case_statement agtype); +--CASE with count() + +--count(*) +SELECT * FROM cypher('case_statement', $$ + MATCH (n) + RETURN n, CASE n.j + WHEN 1 THEN count(*) + ELSE 'not count' + END +$$ ) AS (j agtype, case_statement agtype); + +--concatenated +SELECT * FROM cypher('case_statement', $$ + MATCH (n) MATCH (m) + RETURN n, CASE n.j + WHEN 1 THEN count(*) + ELSE 'not count' + END +$$ ) AS (j agtype, case_statement agtype); + +--count(n) +SELECT * FROM cypher('case_statement', $$ + MATCH (n) + RETURN n, CASE n.j + WHEN 1 THEN count(n) + ELSE 'not count' + END +$$ ) AS (j agtype, case_statement agtype); + +--concatenated +SELECT * FROM cypher('case_statement', $$ + MATCH (n) MATCH (m) + RETURN n, CASE n.j + WHEN 1 THEN count(n) + ELSE 'not count' + END +$$ ) AS (j agtype, case_statement agtype); + +--count(1) +SELECT * FROM cypher('case_statement', $$ + MATCH (n) + RETURN n, CASE n.j + WHEN 1 THEN count(1) + ELSE 'not count' + END +$$ ) AS (j agtype, case_statement agtype); + +--concatenated +SELECT * FROM cypher('case_statement', $$ + MATCH (n) MATCH (m) + RETURN n, CASE n.j + WHEN 1 THEN count(1) + 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 7a71160d..6ba8cc61 100644 --- a/src/backend/parser/cypher_gram.y +++ b/src/backend/parser/cypher_gram.y @@ -225,6 +225,9 @@ static Node *make_typecast_expr(Node *expr, char *typecast, int location); // functions 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); // setops static Node *make_set_op(SetOperation op, bool all_or_distinct, List *larg, @@ -1699,19 +1702,16 @@ expr_func_norm: * and there are no other aggregates in SQL that accept * '*' as parameter. * - * The FuncCall node is also marked agg_star = true, + * The FuncCall node is marked agg_star = true by make_star_function_expr, * so that later processing can detect what the argument * really was. */ - FuncCall *n = (FuncCall *)make_function_expr($1, NIL, @1); - n->agg_star = true; - $$ = (Node *)n; + FuncCall *n = (FuncCall *)make_star_function_expr($1, NIL, @1); + $$ = (Node *)n; } | func_name '(' DISTINCT expr_list ')' { - FuncCall *n = (FuncCall *)make_function_expr($1, $4, @1); - n->agg_order = NIL; - n->agg_distinct = true; + FuncCall *n = (FuncCall *)make_distinct_function_expr($1, $4, @1); $$ = (Node *)n; } ; @@ -2282,25 +2282,186 @@ static Node *make_function_expr(List *func_name, List *exprs, int location) * could be many. */ if (pg_strcasecmp(name, "count") == 0) + { + funcname = SystemFuncName("count"); + + /* build the function call */ + 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); + + return (Node *)fnode; + } + else + { + /* + * We don't qualify AGE functions here. This is done in the + * transform layer and allows us to know which functions are ours. + */ + funcname = func_name; + + /* build the function call */ + fnode = makeFuncCall(funcname, exprs, COERCE_SQL_SYNTAX, location); + } + } + /* all other functions are passed as is */ + else + { + fnode = makeFuncCall(func_name, exprs, COERCE_SQL_SYNTAX, location); + } + + /* return the node */ + return (Node *)fnode; +} + +/* + * function to make a function that has received a star-argument + */ +static Node *make_star_function_expr(List *func_name, List *exprs, int location) +{ + FuncCall *fnode; + + /* AGE function names are unqualified. So, their list size = 1 */ + if (list_length(func_name) == 1) + { + List *funcname; + char *name; + + /* get the name of the function */ + name = ((String*)linitial(func_name))->sval; + + /* + * Check for openCypher functions that are directly mapped to PG + * functions. We may want to find a better way to do this, as there + * could be many. + */ + if (pg_strcasecmp(name, "count") == 0) + { + funcname = SystemFuncName("count"); + + /* build the function call */ + fnode = makeFuncCall(funcname, exprs, COERCE_SQL_SYNTAX, 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); + + return (Node *)fnode; + } + else + { + /* + * We don't qualify AGE functions here. This is done in the + * transform layer and allows us to know which functions are ours. + */ + funcname = func_name; + + /* build the function call */ + fnode = makeFuncCall(funcname, exprs, COERCE_SQL_SYNTAX, location); + } + } + /* all other functions are passed as is */ + else + { + fnode = makeFuncCall(func_name, exprs, COERCE_SQL_SYNTAX, location); + } + + /* return the node */ + fnode->agg_star = true; + return (Node *)fnode; +} + +/* + * function to make a function that has received a distinct keyword + */ +static Node *make_distinct_function_expr(List *func_name, List *exprs, int location) +{ + FuncCall *fnode; + + /* AGE function names are unqualified. So, their list size = 1 */ + if (list_length(func_name) == 1) + { + List *funcname; + char *name; + + /* get the name of the function */ + name = ((String*)linitial(func_name))->sval; + + /* + * Check for openCypher functions that are directly mapped to PG + * functions. We may want to find a better way to do this, as there + * could be many. + */ + if (pg_strcasecmp(name, "count") == 0) + { funcname = SystemFuncName("count"); + + /* build the function call */ + fnode = makeFuncCall(funcname, exprs, COERCE_SQL_SYNTAX, location); + fnode->agg_order = NIL; + 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); + return (Node *)fnode; + } else + { /* * We don't qualify AGE functions here. This is done in the * transform layer and allows us to know which functions are ours. */ funcname = func_name; - /* build the function call */ - fnode = makeFuncCall(funcname, exprs, COERCE_SQL_SYNTAX, location); + /* build the function call */ + fnode = makeFuncCall(funcname, exprs, COERCE_SQL_SYNTAX, location); + } } /* all other functions are passed as is */ else + { fnode = makeFuncCall(func_name, exprs, COERCE_SQL_SYNTAX, location); + } /* return the node */ + fnode->agg_order = NIL; + fnode->agg_distinct = true; return (Node *)fnode; } +/* + * 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) +{ + List *funcname = list_make1(makeString("ag_catalog")); + + if (pg_strcasecmp(type, "float") == 0) + { + funcname = lappend(funcname, makeString("float8_to_agtype")); + } + else if (pg_strcasecmp(type, "int") == 0 || + pg_strcasecmp(type, "integer") == 0) + { + funcname = lappend(funcname, makeString("int8_to_agtype")); + } + else if (pg_strcasecmp(type, "bool") == 0 || + pg_strcasecmp(type, "boolean") == 0) + { + funcname = lappend(funcname, makeString("bool_to_agtype")); + } + else + { + ereport(ERROR, + (errmsg_internal("type \'%s\' not supported by AGE functions", + type))); + } + + return makeFuncCall(funcname, list_make1(fnode), COERCE_EXPLICIT_CAST, location); +} + /* function to create a unique name given a prefix */ static char *create_unique_name(char *prefix_name) {
