From 240562bc615f1ac04deb5d87c810e33b97809d0c Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Sun, 2 Apr 2023 01:53:46 +0300 Subject: [PATCH v6 09/11] Enable non-parenthesized column references in dot notation --- src/backend/parser/gram.y | 3 + src/backend/parser/parse_expr.c | 136 +++++++++++++++++++++++++--- src/backend/parser/parse_target.c | 99 +++++++++++++++----- src/include/nodes/parsenodes.h | 4 +- src/include/parser/parse_expr.h | 4 +- src/test/regress/expected/jsonb.out | 84 ++++++++--------- src/test/regress/sql/jsonb.sql | 56 ++++++------ 7 files changed, 275 insertions(+), 111 deletions(-) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index a8ee79518ca..29f30d17823 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -18715,13 +18715,16 @@ makeColumnRef(char *colname, List *indirection, c->fields = lcons(makeString(colname), indirection); } i->arg = (Node *) c; + i->arg_is_colref = true; /* to distinguish from '(a.col.ref)[...]' case */ return (Node *) i; } else if (IsA(lfirst(l), A_Star)) { +#if 0 /* We only allow '*' at the end of a ColumnRef */ if (lnext(indirection, l) != NULL) parser_yyerror("improper use of \"*\""); +#endif } nfields++; } diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index b82d25c40a4..ea9ca3a6f71 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -71,7 +71,8 @@ static Node *transformXmlExpr(ParseState *pstate, XmlExpr *x); static Node *transformXmlSerialize(ParseState *pstate, XmlSerialize *xs); static Node *transformBooleanTest(ParseState *pstate, BooleanTest *b); static Node *transformCurrentOfExpr(ParseState *pstate, CurrentOfExpr *cexpr); -static Node *transformColumnRef(ParseState *pstate, ColumnRef *cref); +static Node *transformColumnRef(ParseState *pstate, ColumnRef *cref, + List **indirection, bool report_error); static Node *transformWholeRowRef(ParseState *pstate, ParseNamespaceItem *nsitem, int sublevels_up, int location); @@ -146,7 +147,7 @@ transformExprRecurse(ParseState *pstate, Node *expr) switch (nodeTag(expr)) { case T_ColumnRef: - result = transformColumnRef(pstate, (ColumnRef *) expr); + result = transformColumnRef(pstate, (ColumnRef *) expr, NULL, true); break; case T_ParamRef: @@ -158,7 +159,8 @@ transformExprRecurse(ParseState *pstate, Node *expr) break; case T_A_Indirection: - result = transformIndirection(pstate, (A_Indirection *) expr, NULL); + result = transformIndirection(pstate, (A_Indirection *) expr, + NULL, NULL, true); break; case T_A_ArrayExpr: @@ -434,20 +436,52 @@ unknown_attribute(ParseState *pstate, Node *relref, const char *attname, Node * transformIndirection(ParseState *pstate, A_Indirection *ind, - bool *trailing_star_expansion) + Node *transformed_arg, + bool *trailing_star_expansion, + bool report_error) { Node *last_srf = pstate->p_last_srf; - Node *result = transformExprRecurse(pstate, ind->arg); + Node *result; + List *indirection = NIL; List *subscripts = NIL; - int location = exprLocation(result); + int location; ListCell *i; + if (transformed_arg) + result = transformed_arg; + else if (IsA(ind->arg, ColumnRef)) + { + /* + * Get the trailing indirection after column name resolution, + * if argument is a non-parenthized column reference. + */ + List **trailing_indirection = ind->arg_is_colref ? + (compat_field_notation ? &indirection : &subscripts) : NULL; + + result = transformColumnRef(pstate, (ColumnRef *) ind->arg, + trailing_indirection, report_error); + if (!result) + return NULL; + } + else + result = transformExprRecurse(pstate, ind->arg); + + location = exprLocation(result); + + /* Prepend trailing indirection of ColumnRef, if any */ + if (indirection) + indirection = list_concat(indirection, ind->indirection); + else + indirection = ind->indirection; + /* * We have to split any field-selection operations apart from - * subscripting. Adjacent A_Indices nodes have to be treated as a single + * subscripting in "compat_field_notation" mode. + * + * Adjacent A_Indices nodes have to be treated as a single * multidimensional subscript operation. */ - foreach(i, ind->indirection) + foreach(i, indirection) { Node *n = lfirst(i); @@ -552,7 +586,12 @@ transformIndirection(ParseState *pstate, A_Indirection *ind, location); if (!newresult) + { + if (!report_error) + return NULL; + unknown_attribute(pstate, result, strVal(n), location); + } /* consume field select */ subscripts = list_delete_first(subscripts); @@ -805,10 +844,13 @@ transformColumnRefInternal(ParseState *pstate, ColumnRef *cref, int nfields, break; } - err->code = crerr; - err->nspname = nspname; - err->relname = relname; - err->colname = colname; + if (err) + { + err->code = crerr; + err->nspname = nspname; + err->relname = relname; + err->colname = colname; + } return node; } @@ -819,11 +861,16 @@ transformColumnRefInternal(ParseState *pstate, ColumnRef *cref, int nfields, * If you find yourself changing this code, see also ExpandColumnRefStar. */ static Node * -transformColumnRef(ParseState *pstate, ColumnRef *cref) +transformColumnRef(ParseState *pstate, ColumnRef *cref, List **p_indirection, + bool report_error) { Node *node = NULL; + List *indirection = NIL; ColRefError crerr; const char *err; + int num_fields; + int max_fields_to_try; + int min_fields_to_try; /* * Check to see if the column reference is in an invalid place within the @@ -913,8 +960,45 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) return node; } - node = transformColumnRefInternal(pstate, cref, list_length(cref->fields), - &crerr); + /* + * Try to interpret different prefixes of the field list as a + * column reference, starting from the longest prefix. + * 4 is a length of the longset possible 'cat.schema.tab.col' prefix. + * Prefix also can be delimited with the first occurence of '.*'. + * Remaining fields will be treated as indirections. + */ + num_fields = list_length(cref->fields); + max_fields_to_try = Min(4, num_fields); + + for (int j = 1; j < max_fields_to_try; j++) + { + if (IsA(list_nth(cref->fields, j), A_Star)) + { + max_fields_to_try = j + 1; + break; + } + } + + /* + * Don't try 'col.field' case because by the SQL standard, column + * should be the prefixed with table name when used in dot notation. + */ + min_fields_to_try = Min(max_fields_to_try, 2); + + for (int i = max_fields_to_try; i >= min_fields_to_try; i--) + { + /* The first occured error is the most interesting */ + ColRefError *pcrerr = i == max_fields_to_try ? &crerr : NULL; + + node = transformColumnRefInternal(pstate, cref, i, pcrerr); + + if (node) + { + /* Collect trailing indirections */ + indirection = list_copy_tail(cref->fields, i); + break; + } + } /* * Now give the PostParseColumnRefHook, if any, a chance. We pass the @@ -940,11 +1024,33 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) parser_errposition(pstate, cref->location))); } + if (node) + { + if (p_indirection) + { + /* Return trailing indirection if requested */ + *p_indirection = indirection; + } + else if (indirection) + { + /* Transform trailing indirection passing already transformed node */ + A_Indirection *ind = makeNode(A_Indirection); + + ind->arg = node; + ind->indirection = indirection; + + node = transformIndirection(pstate, ind, node, NULL, false); + } + } + /* * Throw error if no translation found. */ if (node == NULL) { + if (!report_error) + return NULL; + switch (crerr.code) { case CRERR_NO_COLUMN: diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 9041e424e1a..4e99725a739 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -45,11 +45,13 @@ static Node *transformAssignmentSubscripts(ParseState *pstate, Node *rhs, CoercionContext ccontext, int location); -static List *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, - bool make_target_entry); +static Node *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, + bool make_target_entry, ParseExprKind exprKind); static List *ExpandAllTables(ParseState *pstate, int location); static Node *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind, - bool make_target_entry, ParseExprKind exprKind); + bool make_target_entry, + bool report_error, + ParseExprKind exprKind); static List *ExpandSingleTable(ParseState *pstate, ParseNamespaceItem *nsitem, int sublevels_up, int location, bool make_target_entry); @@ -150,11 +152,18 @@ transformTargetList(ParseState *pstate, List *targetlist, if (IsA(llast(cref->fields), A_Star)) { /* It is something.*, expand into multiple items */ - p_target = list_concat(p_target, - ExpandColumnRefStar(pstate, - cref, - true)); - continue; + Node *columns = ExpandColumnRefStar(pstate, + cref, + true, + exprKind); + + if (!columns || IsA(columns, List)) + { + p_target = list_concat(p_target, (List *) columns); + continue; + } + + transformed = (Node *) columns; } } else if (IsA(res->val, A_Indirection)) @@ -166,9 +175,10 @@ transformTargetList(ParseState *pstate, List *targetlist, Node *columns = ExpandIndirectionStar(pstate, ind, true, + true, exprKind); - if (IsA(columns, List)) + if (!columns || IsA(columns, List)) { /* It is something.*, expand into multiple items */ p_target = list_concat(p_target, (List *) columns); @@ -246,9 +256,14 @@ transformExpressionList(ParseState *pstate, List *exprlist, if (IsA(llast(cref->fields), A_Star)) { /* It is something.*, expand into multiple items */ - result = list_concat(result, - ExpandColumnRefStar(pstate, cref, - false)); + Node *cols = ExpandColumnRefStar(pstate, cref, + false, exprKind); + + if (!cols || IsA(cols, List)) + result = list_concat(result, (List *) cols); + else + result = lappend(result, cols); + continue; } } @@ -259,7 +274,8 @@ transformExpressionList(ParseState *pstate, List *exprlist, if (IsA(llast(ind->indirection), A_Star)) { Node *cols = ExpandIndirectionStar(pstate, ind, - false, exprKind); + false, true, + exprKind); if (!cols || IsA(cols, List)) /* It is something.*, expand into multiple items */ @@ -1135,9 +1151,9 @@ checkInsertTargets(ParseState *pstate, List *cols, List **attrnos) * * The referenced columns are marked as requiring SELECT access. */ -static List * +static Node * ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, - bool make_target_entry) + bool make_target_entry, ParseExprKind exprKind) { List *fields = cref->fields; int numnames = list_length(fields); @@ -1153,7 +1169,7 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, * need not handle the make_target_entry==false case here. */ Assert(make_target_entry); - return ExpandAllTables(pstate, cref->location); + return (Node *) ExpandAllTables(pstate, cref->location); } else { @@ -1192,7 +1208,22 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, node = pstate->p_pre_columnref_hook(pstate, cref); if (node != NULL) - return ExpandRowReference(pstate, node, make_target_entry); + return (Node *) ExpandRowReference(pstate, node, make_target_entry); + } + + if (numnames > 2) + { + ListCell *lc; + + numnames = 0; + + foreach(lc, fields) + { + numnames++; + + if (IsA(lfirst(lc), A_Star)) + break; + } } switch (numnames) @@ -1257,7 +1288,7 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, errmsg("column reference \"%s\" is ambiguous", NameListToString(cref->fields)), parser_errposition(pstate, cref->location))); - return ExpandRowReference(pstate, node, make_target_entry); + return (Node *) ExpandRowReference(pstate, node, make_target_entry); } } @@ -1266,6 +1297,25 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, */ if (nsitem == NULL) { + //if (numnames > 2 || numnames != list_length(fields)) + { + A_Indirection *ind = makeNode(A_Indirection); + ColumnRef *cref2 = makeNode(ColumnRef); + Node *result; + + cref2->fields = list_copy_head(fields, numnames - 1); + cref2->location = cref->location; + + ind->arg = (Node *) cref2; + ind->indirection = list_copy_tail(fields, numnames - 1); + + result = ExpandIndirectionStar(pstate, ind, make_target_entry, + false, exprKind); + + if (result) + return result; + } + switch (crserr) { case CRSERR_NO_RTE: @@ -1292,8 +1342,8 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, /* * OK, expand the nsitem into fields. */ - return ExpandSingleTable(pstate, nsitem, levels_up, cref->location, - make_target_entry); + return (Node *) ExpandSingleTable(pstate, nsitem, levels_up, + cref->location, make_target_entry); } } @@ -1362,7 +1412,8 @@ ExpandAllTables(ParseState *pstate, int location) */ static Node * ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind, - bool make_target_entry, ParseExprKind exprKind) + bool make_target_entry, bool report_error, + ParseExprKind exprKind) { Node *expr; ParseExprKind sv_expr_kind; @@ -1374,10 +1425,14 @@ ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind, pstate->p_expr_kind = exprKind; /* Strip off the '*' to create a reference to the rowtype object */ - expr = transformIndirection(pstate, ind, &trailing_star_expansion); + expr = transformIndirection(pstate, ind, NULL, &trailing_star_expansion, + report_error); pstate->p_expr_kind = sv_expr_kind; + if (!expr) + return NULL; + /* '*' was consumed by generic type subscripting */ if (!trailing_star_expansion) return expr; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 0f9462493e3..8f1756004a1 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -474,15 +474,13 @@ typedef struct A_Indices * (foo).field1[42][7].field2 * would be represented with a single A_Indirection node having a 4-element * indirection list. - * - * Currently, A_Star must appear only as the last list element --- the grammar - * is responsible for enforcing this! */ typedef struct A_Indirection { NodeTag type; Node *arg; /* the thing being selected from */ List *indirection; /* subscripts and/or field names and/or * */ + bool arg_is_colref; /* arg is non-parenthized column reference */ } A_Indirection; /* diff --git a/src/include/parser/parse_expr.h b/src/include/parser/parse_expr.h index 7f1889e9d84..0aec8d53a66 100644 --- a/src/include/parser/parse_expr.h +++ b/src/include/parser/parse_expr.h @@ -24,6 +24,8 @@ extern Node *transformExpr(ParseState *pstate, Node *expr, ParseExprKind exprKin extern const char *ParseExprKindName(ParseExprKind exprKind); extern Node *transformIndirection(ParseState *pstate, A_Indirection *ind, - bool *trailing_star_expansion); + Node *transformed_arg, + bool *trailing_star_expansion, + bool report_error); #endif /* PARSE_EXPR_H */ diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index 95a93a582b1..8d1f53c994e 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -5824,19 +5824,19 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; -- dot notation CREATE TABLE test_jsonb_dot_notation AS SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb; -SELECT (jb).* FROM test_jsonb_dot_notation; +SELECT jb.* FROM test_jsonb_dot_notation; jb ------------------------------------------------------------------------------------------------------------------------------ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]] (1 row) -SELECT (jb).* FROM test_jsonb_dot_notation t; +SELECT jb.* FROM test_jsonb_dot_notation t; jb ------------------------------------------------------------------------------------------------------------------------------ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]] (1 row) -SELECT (t.jb).* FROM test_jsonb_dot_notation t; +SELECT t.jb.* FROM test_jsonb_dot_notation t; jb ------------------------------------------------------------------------------------------------------------------------------ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]] @@ -5858,7 +5858,7 @@ SELECT (t.jb).* FROM test_jsonb_dot_notation t; [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]] (1 row) -SELECT (jb).a FROM test_jsonb_dot_notation; +SELECT t.jb.a FROM test_jsonb_dot_notation t; a ------------------------------------------------------------------------- [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}] @@ -5870,55 +5870,55 @@ SELECT (jb).a FROM test_jsonb_dot_notation; [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}] (1 row) -SELECT (jb).b FROM test_jsonb_dot_notation; +SELECT t.jb.b FROM test_jsonb_dot_notation t; b --------------------------------------------------- [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}] (1 row) -SELECT (jb).c FROM test_jsonb_dot_notation; +SELECT t.jb.c FROM test_jsonb_dot_notation t; c --- (1 row) -SELECT (jb).a.b FROM test_jsonb_dot_notation; +SELECT t.jb.a.b FROM test_jsonb_dot_notation t; b ------------ ["c", "d"] (1 row) -SELECT (jb).a.* FROM test_jsonb_dot_notation; +SELECT t.jb.a.* FROM test_jsonb_dot_notation t; a ------------------------------------------- ["c", "d", "f", {"y": "yyy", "z": "zzz"}] (1 row) -SELECT (jb).a.*.b FROM test_jsonb_dot_notation; +SELECT t.jb.a.*.b FROM test_jsonb_dot_notation t; b --- (1 row) -SELECT (jb).a.*.x FROM test_jsonb_dot_notation; +SELECT t.jb.a.*.x FROM test_jsonb_dot_notation t; x --- (1 row) -SELECT (jb).a.*.y FROM test_jsonb_dot_notation; +SELECT t.jb.a.*.y FROM test_jsonb_dot_notation t; y ------- "yyy" (1 row) -SELECT (jb).a.*.* FROM test_jsonb_dot_notation; +SELECT t.jb.a.*.* FROM test_jsonb_dot_notation t; a ---------------- ["yyy", "zzz"] (1 row) -SELECT (jb).*.x FROM test_jsonb_dot_notation; +SELECT t.jb.*.x FROM test_jsonb_dot_notation t; x ------------------------------------------------------ [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}] @@ -5930,7 +5930,7 @@ SELECT (jb).*.x FROM test_jsonb_dot_notation t; [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}] (1 row) -SELECT ((jb).*).x FROM test_jsonb_dot_notation t; +SELECT (t.jb.*).x FROM test_jsonb_dot_notation t; x --- @@ -5948,86 +5948,86 @@ SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t; [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}] (1 row) -SELECT (jb).*.x FROM test_jsonb_dot_notation; +SELECT t.jb.*.x FROM test_jsonb_dot_notation t; x ------------------------------------------------------ [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}] (1 row) -SELECT (jb).*.x.* FROM test_jsonb_dot_notation; +SELECT t.jb.*.x.* FROM test_jsonb_dot_notation t; x ------------------------------ ["yyy", "zzz", "YYY", "ZZZ"] (1 row) -SELECT (jb).*.x.y FROM test_jsonb_dot_notation; +SELECT t.jb.*.x.y FROM test_jsonb_dot_notation t; y ---------------- ["yyy", "YYY"] (1 row) -SELECT (jb).*.x.z FROM test_jsonb_dot_notation; +SELECT t.jb.*.x.z FROM test_jsonb_dot_notation t; z ---------------- ["zzz", "ZZZ"] (1 row) -SELECT (jb).*.*.y FROM test_jsonb_dot_notation; +SELECT t.jb.*.*.y FROM test_jsonb_dot_notation t; y ---------------- ["yyy", "YYY"] (1 row) -SELECT (jb).*.*.* FROM test_jsonb_dot_notation; +SELECT t.jb.*.*.* FROM test_jsonb_dot_notation t; jb ------------------------------ ["yyy", "zzz", "YYY", "ZZZ"] (1 row) -SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation; +SELECT t.jb.*.*.*.* FROM test_jsonb_dot_notation t; jb ---- (1 row) -SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation; +SELECT t.jb.a.b.c.* FROM test_jsonb_dot_notation t; c --- (1 row) -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation; - QUERY PLAN --------------------------------------------- - Seq Scan on public.test_jsonb_dot_notation +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.* FROM test_jsonb_dot_notation t; + QUERY PLAN +---------------------------------------------- + Seq Scan on public.test_jsonb_dot_notation t Output: jb.* (2 rows) -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a FROM test_jsonb_dot_notation; - QUERY PLAN --------------------------------------------- - Seq Scan on public.test_jsonb_dot_notation +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a FROM test_jsonb_dot_notation t; + QUERY PLAN +---------------------------------------------- + Seq Scan on public.test_jsonb_dot_notation t Output: jb.a (2 rows) -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation; - QUERY PLAN --------------------------------------------- - Seq Scan on public.test_jsonb_dot_notation +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a[1] FROM test_jsonb_dot_notation t; + QUERY PLAN +---------------------------------------------- + Seq Scan on public.test_jsonb_dot_notation t Output: jb.a[1] (2 rows) -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_jsonb_dot_notation; - QUERY PLAN --------------------------------------------- - Seq Scan on public.test_jsonb_dot_notation +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a.*['b'] FROM test_jsonb_dot_notation t; + QUERY PLAN +---------------------------------------------- + Seq Scan on public.test_jsonb_dot_notation t Output: jb.a.*['b'::text] (2 rows) -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_jsonb_dot_notation; - QUERY PLAN --------------------------------------------- - Seq Scan on public.test_jsonb_dot_notation +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a.*[1:2]['b'].b FROM test_jsonb_dot_notation t; + QUERY PLAN +---------------------------------------------- + Seq Scan on public.test_jsonb_dot_notation t Output: jb.a.*[1:2][:'b'::text].b (2 rows) diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index e3b81efa2d0..1fc949aa7a6 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1580,38 +1580,38 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; CREATE TABLE test_jsonb_dot_notation AS SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb; -SELECT (jb).* FROM test_jsonb_dot_notation; -SELECT (jb).* FROM test_jsonb_dot_notation t; -SELECT (t.jb).* FROM test_jsonb_dot_notation t; +SELECT jb.* FROM test_jsonb_dot_notation; +SELECT jb.* FROM test_jsonb_dot_notation t; +SELECT t.jb.* FROM test_jsonb_dot_notation t; SELECT (jb).* FROM test_jsonb_dot_notation; SELECT (t.jb).* FROM test_jsonb_dot_notation; SELECT (t.jb).* FROM test_jsonb_dot_notation t; +SELECT t.jb.a FROM test_jsonb_dot_notation t; SELECT (jb).a FROM test_jsonb_dot_notation; -SELECT (jb).a FROM test_jsonb_dot_notation; -SELECT (jb).b FROM test_jsonb_dot_notation; -SELECT (jb).c FROM test_jsonb_dot_notation; -SELECT (jb).a.b FROM test_jsonb_dot_notation; -SELECT (jb).a.* FROM test_jsonb_dot_notation; -SELECT (jb).a.*.b FROM test_jsonb_dot_notation; -SELECT (jb).a.*.x FROM test_jsonb_dot_notation; -SELECT (jb).a.*.y FROM test_jsonb_dot_notation; -SELECT (jb).a.*.* FROM test_jsonb_dot_notation; -SELECT (jb).*.x FROM test_jsonb_dot_notation; +SELECT t.jb.b FROM test_jsonb_dot_notation t; +SELECT t.jb.c FROM test_jsonb_dot_notation t; +SELECT t.jb.a.b FROM test_jsonb_dot_notation t; +SELECT t.jb.a.* FROM test_jsonb_dot_notation t; +SELECT t.jb.a.*.b FROM test_jsonb_dot_notation t; +SELECT t.jb.a.*.x FROM test_jsonb_dot_notation t; +SELECT t.jb.a.*.y FROM test_jsonb_dot_notation t; +SELECT t.jb.a.*.* FROM test_jsonb_dot_notation t; +SELECT t.jb.*.x FROM test_jsonb_dot_notation t; SELECT (jb).*.x FROM test_jsonb_dot_notation t; -SELECT ((jb).*).x FROM test_jsonb_dot_notation t; +SELECT (t.jb.*).x FROM test_jsonb_dot_notation t; SELECT ((jb).*).x FROM test_jsonb_dot_notation t; SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t; -SELECT (jb).*.x FROM test_jsonb_dot_notation; -SELECT (jb).*.x.* FROM test_jsonb_dot_notation; -SELECT (jb).*.x.y FROM test_jsonb_dot_notation; -SELECT (jb).*.x.z FROM test_jsonb_dot_notation; -SELECT (jb).*.*.y FROM test_jsonb_dot_notation; -SELECT (jb).*.*.* FROM test_jsonb_dot_notation; -SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation; -SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation; - -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation; -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a FROM test_jsonb_dot_notation; -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation; -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_jsonb_dot_notation; -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_jsonb_dot_notation; +SELECT t.jb.*.x FROM test_jsonb_dot_notation t; +SELECT t.jb.*.x.* FROM test_jsonb_dot_notation t; +SELECT t.jb.*.x.y FROM test_jsonb_dot_notation t; +SELECT t.jb.*.x.z FROM test_jsonb_dot_notation t; +SELECT t.jb.*.*.y FROM test_jsonb_dot_notation t; +SELECT t.jb.*.*.* FROM test_jsonb_dot_notation t; +SELECT t.jb.*.*.*.* FROM test_jsonb_dot_notation t; +SELECT t.jb.a.b.c.* FROM test_jsonb_dot_notation t; + +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.* FROM test_jsonb_dot_notation t; +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a FROM test_jsonb_dot_notation t; +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a[1] FROM test_jsonb_dot_notation t; +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a.*['b'] FROM test_jsonb_dot_notation t; +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a.*[1:2]['b'].b FROM test_jsonb_dot_notation t; -- 2.25.1