8000 SQL/JSON: Fix JSON_TABLE() column deparsing · postgrespro/postgres@6822285 · GitHub
[go: up one dir, main page]
Skip to content

Commit 6822285

Browse files
amitlanamitlan
committed
SQL/JSON: Fix JSON_TABLE() column deparsing
The deparsing code in get_json_expr_options() unnecessarily emitted the default column-specific ON ERROR / EMPTY behavior when the top-level ON ERROR behavior in JSON_TABLE was set to ERROR. Fix that by not overriding the column-specific default, determined based on the column's JsonExprOp in get_json_table_columns(), with JSON_BEHAVIOR_ERROR when that is the top-level ON ERROR behavior. Note that this only removes redundancy; the current deparsing output is not incorrect, just redundant. Reviewed-by: Jian He <jian.universality@gmail.com> Discussion: https://postgr.es/m/CACJufxEo4sUjKCYtda0_qt9tazqqKPmF1cqhW9KBOUeJFqQd2g@mail.gmail.com Backpatch-through: 17
1 parent 3422f5f commit 6822285

File tree

3 files changed

+27
-4
lines changed
  • test/regress

    • 3 files changed

    +27
    -4
    lines changed

    ‎src/backend/utils/adt/ruleutils.c

    Lines changed: 4 additions & 4 deletions
    Original file line numberDiff line numberDiff line change
    @@ -11719,7 +11719,6 @@ get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan,
    1171911719
    bool showimplicit)
    1172011720
    {
    1172111721
    StringInfo buf = context->buf;
    11722-
    JsonExpr *jexpr = castNode(JsonExpr, tf->docexpr);
    1172311722
    ListCell *lc_colname;
    1172411723
    ListCell *lc_coltype;
    1172511724
    ListCell *lc_coltypmod;
    @@ -11772,6 +11771,10 @@ get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan,
    1177211771
    if (ordinality)
    1177311772
    continue;
    1177411773

    11774+
    /*
    11775+
    * Set default_behavior to guide get_json_expr_options() on whether to
    11776+
    * to emit the ON ERROR / EMPTY clauses.
    11777+
    */
    1177511778
    if (colexpr->op == JSON_EXISTS_OP)
    1177611779
    {
    1177711780
    appendStringInfoString(buf, " EXISTS");
    @@ -11795,9 +11798,6 @@ get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan,
    1179511798
    default_behavior = JSON_BEHAVIOR_NULL;
    1179611799
    }
    1179711800

    11798-
    if (jexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
    11799-
    default_behavior = JSON_BEHAVIOR_ERROR;
    11800-
    1180111801
    appendStringInfoString(buf, " PATH ");
    1180211802

    1180311803
    get_json_path_spec(colexpr->path_spec, context, showimplicit);

    ‎src/test/regress/expected/sqljson_jsontable.out

    Lines changed: 18 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1132,3 +1132,21 @@ ERROR: invalid ON ERROR behavior for column "a"
    11321132
    LINE 1: ...M JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty obje...
    11331133
    ^
    11341134
    DETAIL: Only ERROR, TRUE, FALSE, or UNKNOWN is allowed in ON ERROR for EXISTS columns.
    1135+
    -- Test JSON_TABLE() column deparsing -- don't emit default ON ERROR / EMPTY
    1136+
    -- behavior
    1137+
    EXPLAIN VERBOSE SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$'));
    1138+
    QUERY PLAN
    1139+
    -----------------------------------------------------------------------------------------------------
    1140+
    Table Function Scan on "json_table" (cost=0.01..1.00 rows=100 width=32)
    1141+
    Output: a
    1142+
    Table Function Call: JSON_TABLE('"a"'::jsonb, '$' AS json_table_path_0 COLUMNS (a text PATH '$'))
    1143+
    (3 rows)
    1144+
    1145+
    EXPLAIN VERBOSE SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$') ERROR ON ERROR);
    1146+
    QUERY PLAN
    1147+
    --------------------------------------------------------------------------------------------------------------------
    1148+
    Table Function Scan on "json_table" (cost=0.01..1.00 rows=100 width=32)
    1149+
    Output: a
    1150+
    Table Function Call: JSON_TABLE('"a"'::jsonb, '$' AS json_table_path_0 COLUMNS (a text PATH '$') ERROR ON ERROR)
    1151+
    (3 rows)
    1152+

    ‎src/test/regress/sql/sqljson_jsontable.sql

    Lines changed: 5 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -542,3 +542,8 @@ SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int) NULL ON ERROR);
    542542
    SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int true on empty));
    543543
    SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int omit quotes true on error));
    544544
    SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty object on error));
    545+
    546+
    -- Test JSON_TABLE() column deparsing -- don't emit default ON ERROR / EMPTY
    547+
    -- behavior
    548+
    EXPLAIN VERBOSE SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$'));
    549+
    EXPLAIN VERBOSE SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$') ERROR ON ERROR);

    0 commit comments

    Comments
     (0)
    0