Re: [HACKERS] PostgreSQL 10 parenthesized single-column updates can produce errors

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/906bfcad7ba7cb3863fe0e2a7810be8e3cd84fbd).

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

rob@robmccoll.com

205.422.0909