Attaching patch... :-/
On Tue, Oct 31, 2017 at 4:27 PM, Rob McColl <r...@robmccoll.com> wrote:
> Between 9.6.5 and 10, the handling of parenthesized single-column UPDATE
> statements changed. In 9.6.5, they were treated identically to
> unparenthesized single-column UPDATES. In 10, they are treated as
> multiple-column updates. This results in this being valid in Postgres
> 9.6.5, but an error in Postgres 10:
>
> CREATE TABLE test (id INT PRIMARY KEY, data INT);
> INSERT INTO test VALUES (1, 1);
> UPDATE test SET (data) = (2) WHERE id = 1;
>
> In 10 and the current master, this produces the error:
>
> errmsg("source for a multiple-column UPDATE item must be a sub-SELECT or
> ROW() expression")
>
> I believe that this is not an intended change or behavior, but is instead
> an unintentional side effect of 906bfcad7ba7cb3863fe0e2a7810be8e3cd84fbd
> Improve handling of "UPDATE ... SET (column_list) = row_constructor". (
> https://github.com/postgres/postgres/commit/906bfcad7ba7cb3
> 863fe0e2a7810be8e3cd84fbd).
>
> This is a small patch to the grammar that restores the previous behavior
> by adding a rule to the set_clause rule and modifying the final rule of the
> set_clause rule to only match lists of more then one element. I'm not sure
> if there are more elegant or preferred ways to address this.
>
> Compiled and tested on Ubuntu 17.04 Linux 4.10.0-33-generic x86_64.
>
> Regression test added under the update test to cover the parenthesized
> single-column case.
>
> I see no reason this would affect performance.
>
> Thanks,
> -rob
>
> --
> Rob McColl
> @robmccoll
> r...@robmccoll.com
> 205.422.0909 <(205)%20422-0909>
>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
new file mode 100644
index 4c83a63..b5f4ccf
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** set_clause:
*** 10694,10712 ****
$1->val = (Node *) $3;
$$ = list_make1($1);
}
! | '(' set_target_list ')' '=' a_expr
{
! int ncolumns = list_length($2);
int i = 1;
ListCell *col_cell;
/* Create a MultiAssignRef source for each target */
foreach(col_cell, $2)
{
ResTarget *res_col = (ResTarget *) lfirst(col_cell);
MultiAssignRef *r = makeNode(MultiAssignRef);
! r->source = (Node *) $5;
r->colno = i;
r->ncolumns = ncolumns;
res_col->val = (Node *) r;
--- 10694,10720 ----
$1->val = (Node *) $3;
$$ = list_make1($1);
}
! | '(' set_target ')' '=' a_expr
{
! $2->val = (Node *) $5;
! $$ = list_make1($2);
! }
! | '(' set_target_list ',' set_target ')' '=' a_expr
! {
! int ncolumns;
int i = 1;
ListCell *col_cell;
+ $2 = lappend($2,$4);
+ ncolumns = list_length($2);
+
/* Create a MultiAssignRef source for each target */
foreach(col_cell, $2)
{
ResTarget *res_col = (ResTarget *) lfirst(col_cell);
MultiAssignRef *r = makeNode(MultiAssignRef);
! r->source = (Node *) $7;
r->colno = i;
r->ncolumns = ncolumns;
res_col->val = (Node *) r;
diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out
new file mode 100644
index cef70b1..90d33ad
*** a/src/test/regress/expected/update.out
--- b/src/test/regress/expected/update.out
*************** SELECT * FROM update_test;
*** 76,82 ****
100 | 21 |
(4 rows)
! UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo';
SELECT * FROM update_test;
a | b | c
-----+----+-------
--- 76,93 ----
100 | 21 |
(4 rows)
! -- parenthesized single column should be valid
! UPDATE update_test SET (c) = ('bungle') WHERE c = 'foo';
! SELECT * FROM update_test;
! a | b | c
! -----+----+--------
! 100 | 20 |
! 100 | 21 |
! 100 | 20 | bungle
! 100 | 21 | bungle
! (4 rows)
!
! UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'bungle';
SELECT * FROM update_test;
a | b | c
-----+----+-------
diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql
new file mode 100644
index 66d1fec..0ed5f6a
*** a/src/test/regress/sql/update.sql
--- b/src/test/regress/sql/update.sql
*************** UPDATE update_test SET a = v.* FROM (VAL
*** 51,57 ****
INSERT INTO update_test SELECT a,b+1,c FROM update_test;
SELECT * FROM update_test;
! UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo';
SELECT * FROM update_test;
UPDATE update_test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10;
SELECT * FROM update_test;
--- 51,60 ----
INSERT INTO update_test SELECT a,b+1,c FROM update_test;
SELECT * FROM update_test;
! -- parenthesized single column should be valid
! UPDATE update_test SET (c) = ('bungle') WHERE c = 'foo';
! SELECT * FROM update_test;
! UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'bungle';
SELECT * FROM update_test;
UPDATE update_test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10;
SELECT * FROM update_test;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
