This is an automated email from the ASF dual-hosted git repository.
dehowef pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-age.git
The following commit(s) were added to refs/heads/master by this push:
new a389bbc Implement CASE statement
a389bbc is described below
commit a389bbcf1979cb0c991441dad4faced81f7a2923
Author: Dehowe Feng <[email protected]>
AuthorDate: Mon May 24 12:53:15 2021 -0700
Implement CASE statement
Implemented PostgreSQL CASE statements into AGE.
CASE has the following forms:
CASE WHEN condition THEN result END
CASE expression WHEN value THEN result END
The logic that CASE uses is largely similar to that of PostgreSQL's
CASE logic.
---
regress/expected/expr.out | 93 ++++++++++++++++++++++
regress/sql/expr.sql | 37 +++++++++
src/backend/parser/cypher_expr.c | 144 ++++++++++++++++++++++++++++++++++-
src/backend/parser/cypher_gram.y | 85 +++++++++++++++++++--
src/backend/parser/cypher_keywords.c | 5 ++
5 files changed, 356 insertions(+), 8 deletions(-)
diff --git a/regress/expected/expr.out b/regress/expected/expr.out
index 384bff3..68ec278 100644
--- a/regress/expected/expr.out
+++ b/regress/expected/expr.out
@@ -5086,6 +5086,89 @@ $$) AS (i agtype);
{"key": "value"}
(9 rows)
+--CASE
+SELECT create_graph('case_statement');
+NOTICE: graph "case_statement" has been created
+ create_graph
+--------------
+
+(1 row)
+
+SELECT * FROM cypher('case_statement', $$CREATE ({i: 1, j: null})$$) AS
(result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('case_statement', $$CREATE ({i: 'a', j: 'b'})$$) AS
(result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('case_statement', $$CREATE ({i: 0, j: 1})$$) AS (result
agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('case_statement', $$CREATE ({i: true, j: false})$$) AS
(result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('case_statement', $$CREATE ({i: [], j: [0,1,2]})$$) AS
(result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('case_statement', $$CREATE ({i: {}, j: {i:1}})$$) AS
(result agtype);
+ result
+--------
+(0 rows)
+
+--CASE WHEN condition THEN result END
+SELECT * FROM cypher('case_statement', $$
+ MATCH (n)
+ RETURN n.i, n.j, CASE
+ WHEN null THEN 'should not return me'
+ WHEN n.i = 1 THEN 'i is 1'
+ WHEN n.j = 'b' THEN 'j is b'
+ WHEN n.i = 0 AND n.j = 1 THEN '0 AND 1'
+ WHEN n.i = true OR n.j = true THEN 'i or j true'
+ ELSE 'default'
+ END
+$$ ) AS (i agtype, j agtype, case_statement agtype);
+ i | j | case_statement
+------+-----------+----------------
+ 1 | null | "i is 1"
+ "a" | "b" | "j is b"
+ 0 | 1 | "0 AND 1"
+ true | false | "i or j true"
+ [] | [0, 1, 2] | "default"
+ {} | {"i": 1} | "default"
+(6 rows)
+
+--CASE expression WHEN value THEN result END
+SELECT * FROM cypher('case_statement', $$
+ MATCH (n)
+ RETURN n.j, CASE n.j
+ WHEN null THEN 'should not return me'
+ WHEN 'b' THEN 'b'
+ WHEN 1 THEN 1
+ WHEN false THEN false
+ WHEN [0,1,2] THEN [0,1,2]
+ WHEN {i:1} THEN {i:1}
+ ELSE 'not a or b'
+ END
+$$ ) AS (j agtype, case_statement agtype);
+ j | case_statement
+-----------+----------------
+ null | "not a or b"
+ "b" | "b"
+ 1 | 1
+ false | false
+ [0, 1, 2] | [0, 1, 2]
+ {"i": 1} | {"i": 1}
+(6 rows)
+
-- RETURN * and (u)--(v) optional forms
SELECT create_graph('opt_forms');
NOTICE: graph "opt_forms" has been created
@@ -5215,6 +5298,16 @@ NOTICE: graph "VLE" has been dropped
(1 row)
+SELECT * FROM drop_graph('case_statement', true);
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table case_statement._ag_label_vertex
+drop cascades to table case_statement._ag_label_edge
+NOTICE: graph "case_statement" has been dropped
+ drop_graph
+------------
+
+(1 row)
+
SELECT * FROM drop_graph('opt_forms', true);
NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to table opt_forms._ag_label_vertex
diff --git a/regress/sql/expr.sql b/regress/sql/expr.sql
index 41cee8e..b46de52 100644
--- a/regress/sql/expr.sql
+++ b/regress/sql/expr.sql
@@ -2113,6 +2113,42 @@ SELECT * FROM cypher('order_by', $$
ORDER BY u.i DESC
$$) AS (i agtype);
+--CASE
+SELECT create_graph('case_statement');
+SELECT * FROM cypher('case_statement', $$CREATE ({i: 1, j: null})$$) AS
(result agtype);
+SELECT * FROM cypher('case_statement', $$CREATE ({i: 'a', j: 'b'})$$) AS
(result agtype);
+SELECT * FROM cypher('case_statement', $$CREATE ({i: 0, j: 1})$$) AS (result
agtype);
+SELECT * FROM cypher('case_statement', $$CREATE ({i: true, j: false})$$) AS
(result agtype);
+SELECT * FROM cypher('case_statement', $$CREATE ({i: [], j: [0,1,2]})$$) AS
(result agtype);
+SELECT * FROM cypher('case_statement', $$CREATE ({i: {}, j: {i:1}})$$) AS
(result agtype);
+
+--CASE WHEN condition THEN result END
+SELECT * FROM cypher('case_statement', $$
+ MATCH (n)
+ RETURN n.i, n.j, CASE
+ WHEN null THEN 'should not return me'
+ WHEN n.i = 1 THEN 'i is 1'
+ WHEN n.j = 'b' THEN 'j is b'
+ WHEN n.i = 0 AND n.j = 1 THEN '0 AND 1'
+ WHEN n.i = true OR n.j = true THEN 'i or j true'
+ ELSE 'default'
+ END
+$$ ) AS (i agtype, j agtype, case_statement agtype);
+
+--CASE expression WHEN value THEN result END
+SELECT * FROM cypher('case_statement', $$
+ MATCH (n)
+ RETURN n.j, CASE n.j
+ WHEN null THEN 'should not return me'
+ WHEN 'b' THEN 'b'
+ WHEN 1 THEN 1
+ WHEN false THEN false
+ WHEN [0,1,2] THEN [0,1,2]
+ WHEN {i:1} THEN {i:1}
+ ELSE 'not a or b'
+ END
+$$ ) AS (j agtype, case_statement agtype);
+
-- RETURN * and (u)--(v) optional forms
SELECT create_graph('opt_forms');
SELECT * FROM cypher('opt_forms', $$CREATE
({i:1})-[:KNOWS]->({i:2})<-[:KNOWS]-({i:3})$$)AS (result agtype);
@@ -2145,6 +2181,7 @@ SELECT * FROM cypher('VLE', $$MATCH (u)-[*..5]-(v) RETURN
u, v$$) AS (u agtype,
-- Cleanup
--
SELECT * FROM drop_graph('VLE', true);
+SELECT * FROM drop_graph('case_statement', true);
SELECT * FROM drop_graph('opt_forms', true);
SELECT * FROM drop_graph('type_coercion', true);
SELECT * FROM drop_graph('order_by', true);
diff --git a/src/backend/parser/cypher_expr.c b/src/backend/parser/cypher_expr.c
index cd43874..ea4d888 100644
--- a/src/backend/parser/cypher_expr.c
+++ b/src/backend/parser/cypher_expr.c
@@ -28,6 +28,7 @@
#include "nodes/value.h"
#include "optimizer/tlist.h"
#include "parser/parse_coerce.h"
+#include "parser/parse_collate.h"
#include "parser/parse_expr.h"
#include "parser/parse_func.h"
#include "parser/cypher_clause.h"
@@ -78,6 +79,8 @@ static Node *transform_cypher_string_match(cypher_parsestate
*cpstate,
cypher_string_match *csm_node);
static Node *transform_cypher_typecast(cypher_parsestate *cpstate,
cypher_typecast *ctypecast);
+static Node *transform_CaseExpr(cypher_parsestate *cpstate,
+ CaseExpr *cexpr);
static Node *transform_CoalesceExpr(cypher_parsestate *cpstate,
CoalesceExpr *cexpr);
static Node *transform_SubLink(cypher_parsestate *cpstate, SubLink *sublink);
@@ -145,6 +148,10 @@ static Node
*transform_cypher_expr_recurse(cypher_parsestate *cpstate,
return expr;
}
+ case T_CaseExpr:
+ return transform_CaseExpr(cpstate, (CaseExpr *) expr);
+ case T_CaseTestExpr:
+ return expr;
case T_CoalesceExpr:
return transform_CoalesceExpr(cpstate, (CoalesceExpr *) expr);
case T_ExtensibleNode:
@@ -840,13 +847,141 @@ static Node *transform_CoalesceExpr(cypher_parsestate
*cpstate, CoalesceExpr
return (Node *) newcexpr;
}
+/*
+ * Code borrowed from PG's transformCaseExpr and updated for AGE
+ */
+static Node *transform_CaseExpr(cypher_parsestate *cpstate,CaseExpr
+ *cexpr)
+{
+ ParseState *pstate = &cpstate->pstate;
+ CaseExpr *newcexpr = makeNode(CaseExpr);
+ Node *last_srf = pstate->p_last_srf;
+ Node *arg;
+ CaseTestExpr *placeholder;
+ List *newargs;
+ List *resultexprs;
+ ListCell *l;
+ Node *defresult;
+ Oid ptype;
+
+ /* transform the test expression, if any */
+ arg = transform_cypher_expr_recurse(cpstate, (Node *) cexpr->arg);
+
+ /* generate placeholder for test expression */
+ if (arg)
+ {
+ if (exprType(arg) == UNKNOWNOID)
+ arg = coerce_to_common_type(pstate, arg, TEXTOID, "CASE");
+
+ assign_expr_collations(pstate, arg);
+
+ placeholder = makeNode(CaseTestExpr);
+ placeholder->typeId = exprType(arg);
+ placeholder->typeMod = exprTypmod(arg);
+ placeholder->collation = exprCollation(arg);
+ }
+ else
+ {
+ placeholder = NULL;
+ }
+
+ newcexpr->arg = (Expr *) arg;
+
+ /* transform the list of arguments */
+ newargs = NIL;
+ resultexprs = NIL;
+ foreach(l, cexpr->args)
+ {
+ CaseWhen *w = lfirst_node(CaseWhen, l);
+ CaseWhen *neww = makeNode(CaseWhen);
+ Node *warg;
+
+ warg = (Node *) w->expr;
+ if (placeholder)
+ {
+ /* shorthand form was specified, so expand... */
+ warg = (Node *) makeSimpleA_Expr(AEXPR_OP, "=",
+ (Node *) placeholder,
+ warg,
+ w->location);
+ }
+ neww->expr = (Expr *) transform_cypher_expr_recurse(cpstate, warg);
+
+ neww->expr = (Expr *) coerce_to_boolean(pstate,
+ (Node *) neww->expr,
+ "CASE/WHEN");
+
+ warg = (Node *) w->result;
+ neww->result = (Expr *) transform_cypher_expr_recurse(cpstate, warg);
+ neww->location = w->location;
+
+ newargs = lappend(newargs, neww);
+ resultexprs = lappend(resultexprs, neww->result);
+ }
+
+ newcexpr->args = newargs;
+
+ /* transform the default clause */
+ defresult = (Node *) cexpr->defresult;
+ if (defresult == NULL)
+ {
+ A_Const *n = makeNode(A_Const);
+
+ n->val.type = T_Null;
+ n->location = -1;
+ defresult = (Node *) n;
+ }
+ newcexpr->defresult = (Expr *) transform_cypher_expr_recurse(cpstate,
defresult);
+
+ resultexprs = lcons(newcexpr->defresult, resultexprs);
+
+ ptype = select_common_type(pstate, resultexprs, "CASE", NULL);
+ Assert(OidIsValid(ptype));
+ newcexpr->casetype = ptype;
+ /* casecollid will be set by parse_collate.c */
+
+ /* Convert default result clause, if necessary */
+ newcexpr->defresult = (Expr *)
+ coerce_to_common_type(pstate,
+ (Node *) newcexpr->defresult,
+ ptype,
+ "CASE/ELSE");
+
+ /* Convert when-clause results, if necessary */
+ foreach(l, newcexpr->args)
+ {
+ CaseWhen *w = (CaseWhen *) lfirst(l);
+
+ w->result = (Expr *)
+ coerce_to_common_type(pstate,
+ (Node *) w->result,
+ ptype,
+ "CASE/WHEN");
+ }
+
+ /* if any subexpression contained a SRF, complain */
+ if (pstate->p_last_srf != last_srf)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ /* translator: %s is name of a SQL construct, eg GROUP BY */
+ errmsg("set-returning functions are not allowed in %s",
+ "CASE"),
+ errhint("You might be able to move the set-returning function
into a LATERAL FROM item."),
+ parser_errposition(pstate,
+ exprLocation(pstate->p_last_srf))));
+
+ newcexpr->location = cexpr->location;
+
+ return (Node *) newcexpr;
+}
+
/* from PG's transformSubLink but reduced and hooked into our parser */
static Node *transform_SubLink(cypher_parsestate *cpstate, SubLink *sublink)
{
Node *result = (Node*)sublink;
Query *qtree;
ParseState *pstate = (ParseState*)cpstate;
-
+ const char *err = NULL;
/*
* Check to see if the sublink is in an invalid place within the query. We
* allow sublinks everywhere in SELECT/INSERT/UPDATE/DELETE, but generally
@@ -859,7 +994,9 @@ static Node *transform_SubLink(cypher_parsestate *cpstate,
SubLink *sublink)
break;
case EXPR_KIND_OTHER:
/* Accept sublink here; caller must throw error if wanted */
+
break;
+ case EXPR_KIND_SELECT_TARGET:
case EXPR_KIND_FROM_SUBSELECT:
case EXPR_KIND_WHERE:
/* okay */
@@ -869,6 +1006,11 @@ static Node *transform_SubLink(cypher_parsestate
*cpstate, SubLink *sublink)
errmsg_internal("unsupported SubLink"),
parser_errposition(pstate, sublink->location)));
}
+ if (err)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg_internal("%s", err),
+ parser_errposition(pstate, sublink->location)));
pstate->p_hasSubLinks = true;
diff --git a/src/backend/parser/cypher_gram.y b/src/backend/parser/cypher_gram.y
index 430da84..b8e7878 100644
--- a/src/backend/parser/cypher_gram.y
+++ b/src/backend/parser/cypher_gram.y
@@ -79,9 +79,9 @@
/* keywords in alphabetical order */
%token <keyword> ANALYZE AND AS ASC ASCENDING
BY
- COALESCE CONTAINS CREATE
+ CASE COALESCE CONTAINS CREATE
DELETE DESC DESCENDING DETACH DISTINCT
- ENDS EXISTS EXPLAIN
+ ELSE END_P ENDS EXISTS EXPLAIN
FALSE_P
IN IS
LIMIT
@@ -90,9 +90,9 @@
OR ORDER
REMOVE RETURN
SET SKIP STARTS
- TRUE_P
+ THEN TRUE_P
VERBOSE
- WHERE WITH
+ WHEN WHERE WITH
/* query */
%type <list> single_query query_part_init query_part_last
@@ -131,6 +131,10 @@
/* expression */
%type <node> expr expr_opt expr_atom expr_literal map list
+
+%type <node> expr_case expr_case_when expr_case_default
+%type <list> expr_case_when_list
+
%type <node> expr_var expr_func expr_func_norm expr_func_subexpr
%type <list> expr_list expr_list_opt map_keyval_list_opt map_keyval_list
%type <node> property_value
@@ -1347,6 +1351,7 @@ expr_atom:
{
$$ = $2;
}
+ | expr_case
| expr_var
| expr_func
;
@@ -1423,6 +1428,67 @@ list:
}
;
+expr_case:
+ CASE expr expr_case_when_list expr_case_default END_P
+ {
+ CaseExpr *n;
+
+ n = makeNode(CaseExpr);
+ n->casetype = InvalidOid;
+ n->arg = (Expr *) $2;
+ n->args = $3;
+ n->defresult = (Expr *) $4;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ | CASE expr_case_when_list expr_case_default END_P
+ {
+ CaseExpr *n;
+
+ n = makeNode(CaseExpr);
+ n->casetype = InvalidOid;
+ n->args = $2;
+ n->defresult = (Expr *) $3;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
+expr_case_when_list:
+ expr_case_when
+ {
+ $$ = list_make1($1);
+ }
+ | expr_case_when_list expr_case_when
+ {
+ $$ = lappend($1, $2);
+ }
+ ;
+
+expr_case_when:
+ WHEN expr THEN expr
+ {
+ CaseWhen *n;
+
+ n = makeNode(CaseWhen);
+ n->expr = (Expr *) $2;
+ n->result = (Expr *) $4;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
+expr_case_default:
+ ELSE expr
+ {
+ $$ = $2;
+ }
+ | /* EMPTY */
+ {
+ $$ = NULL;
+ }
+ ;
+
expr_var:
var_name
{
@@ -1505,6 +1571,7 @@ safe_keywords:
| ASC { $$ = pnstrdup($1, 3); }
| ASCENDING { $$ = pnstrdup($1, 9); }
| BY { $$ = pnstrdup($1, 2); }
+ | CASE { $$ = pnstrdup($1, 4); }
| COALESCE { $$ = pnstrdup($1, 8); }
| CONTAINS { $$ = pnstrdup($1, 8); }
| CREATE { $$ = pnstrdup($1, 6); }
@@ -1513,6 +1580,7 @@ safe_keywords:
| DESCENDING { $$ = pnstrdup($1, 10); }
| DETACH { $$ = pnstrdup($1, 6); }
| DISTINCT { $$ = pnstrdup($1, 8); }
+ | ELSE { $$ = pnstrdup($1, 4); }
| ENDS { $$ = pnstrdup($1, 4); }
| EXISTS { $$ = pnstrdup($1, 6); }
| IN { $$ = pnstrdup($1, 2); }
@@ -1527,14 +1595,17 @@ safe_keywords:
| SET { $$ = pnstrdup($1, 3); }
| SKIP { $$ = pnstrdup($1, 4); }
| STARTS { $$ = pnstrdup($1, 6); }
+ | THEN { $$ = pnstrdup($1, 4); }
+ | WHEN { $$ = pnstrdup($1, 4); }
| WHERE { $$ = pnstrdup($1, 5); }
| WITH { $$ = pnstrdup($1, 4); }
;
conflicted_keywords:
- FALSE_P { $$ = pnstrdup($1, 7); }
- | NULL_P { $$ = pnstrdup($1, 6); }
- | TRUE_P { $$ = pnstrdup($1, 6); }
+ END_P { $$ = pnstrdup($1, 5); }
+ | FALSE_P { $$ = pnstrdup($1, 7); }
+ | NULL_P { $$ = pnstrdup($1, 6); }
+ | TRUE_P { $$ = pnstrdup($1, 6); }
;
%%
diff --git a/src/backend/parser/cypher_keywords.c
b/src/backend/parser/cypher_keywords.c
index 0ff6129..64c6711 100644
--- a/src/backend/parser/cypher_keywords.c
+++ b/src/backend/parser/cypher_keywords.c
@@ -40,6 +40,7 @@ const ScanKeyword cypher_keywords[] = {
{"asc", ASC, RESERVED_KEYWORD},
{"ascending", ASCENDING, RESERVED_KEYWORD},
{"by", BY, RESERVED_KEYWORD},
+ {"case", CASE, RESERVED_KEYWORD},
{"coalesce", COALESCE, RESERVED_KEYWORD},
{"contains", CONTAINS, RESERVED_KEYWORD},
{"create", CREATE, RESERVED_KEYWORD},
@@ -48,6 +49,8 @@ const ScanKeyword cypher_keywords[] = {
{"descending", DESCENDING, RESERVED_KEYWORD},
{"detach", DETACH, RESERVED_KEYWORD},
{"distinct", DISTINCT, RESERVED_KEYWORD},
+ {"else", ELSE, RESERVED_KEYWORD},
+ {"end", END_P, RESERVED_KEYWORD},
{"ends", ENDS, RESERVED_KEYWORD},
{"exists", EXISTS, RESERVED_KEYWORD},
{"explain", EXPLAIN, RESERVED_KEYWORD},
@@ -65,8 +68,10 @@ const ScanKeyword cypher_keywords[] = {
{"set", SET, RESERVED_KEYWORD},
{"skip", SKIP, RESERVED_KEYWORD},
{"starts", STARTS, RESERVED_KEYWORD},
+ {"then", THEN, RESERVED_KEYWORD},
{"true", TRUE_P, RESERVED_KEYWORD},
{"verbose", VERBOSE, RESERVED_KEYWORD},
+ {"when", WHEN, RESERVED_KEYWORD},
{"where", WHERE, RESERVED_KEYWORD},
{"with", WITH, RESERVED_KEYWORD}
};
