Re: BUG #15237: I got "ERROR: source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression"

>>>>> "Andrew" == Andrew Gierth <andrew@tao11.riddles.org.uk> writes:

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

 >>> On PostgreSQL 11 Beta, I exec sql like "update
 >>> fvt_obj_operate_update_table_033 set (c_int) = (20) where c_int = 20;",
 >>> which only on column to set, got ""ERROR:  source for a multiple-column
 >>> UPDATE item must be a sub-SELECT or ROW() expression"".

 Tom> It's telling you what to do: use a ROW() expression, ie

 Tom> update fvt_obj_operate_update_table_033 set (c_int) = row(20)
 Tom> where c_int = 20;

 Andrew> Yeah, but (a) this used to work, and has worked since at least
 Andrew> as far back as 9.0, and (b) the spec requires it to work.

Looking at the previous discussion, in fact, it seems that you were
under the misapprehension that the spec requires the use of ROW there;
it does not. So this is actually broken in pg 10.

Here is the expansion with references according to sql2016 (leaving out
all the irrelevant bits):

14.15 <set clause list>

<multiple column assignment> ::=
   <set target list> <equals operator> <assigned row>

<assigned row> ::=
   <contextually typed row value expression>

7.2 <row value expression>

<contextually typed row value expression> ::=
    <contextually typed row value constructor>

7.1 <row value constructor>

<contextually typed row value constructor> ::=
       <common value expression>

6.28 <value expression>

<common value expression> ::=
       <numeric value expression>

<numeric value expression> ::= [snipped for brevity]

<numeric primary> ::=
       <value expression primary>

6.3 <value expression primary>

<value expression primary> ::=
       <parenthesized value expression>

(I'll stop here since (20) is obviously a <parenthesized value expression>)

At this point, we go back up the stack to 7.1 <row value constructor>,
where we find this syntax rule:

5) Let CTRVC be the <contextually typed row value constructor>.
   a) If CTRVC is a <common value expression>, <boolean value expression>,
      or <contextually typed value specification> X, then CTRVC is
      equivalent to:

         ROW ( X )

So by the time we get back to syntax rule 4 of 14.15 <set clause list>,
the <assigned row> term is not (20) but ROW( (20) ), and the expansion
into individual columns succeeds.

Another obvious way to see that ROW isn't required here by the spec is
to notice that INSERT ... VALUES (20); also doesn't need ROW, and that's
using the same syntax (specifically, <contextually typed row
value constructor> is used in both cases).

-- 
Andrew (irc:RhodiumToad)