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

The following bug has been logged on the website:

Bug reference:      15237
Logged by:          zhou xiaowei
Email address:      110876189@qq.com
PostgreSQL version: 11beta1
Operating system:   centos
Description:

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"".
But I exec sql like "update fvt_obj_operate_update_table_033 set
(c_int,c_dp) = (20,1.11) where c_int = 20;", got successfull message.
Detail:
postgres=# \d fvt_obj_operate_update_table_033
               Table "public.fvt_obj_operate_update_table_033"
    Column    |            Type             | Collation | Nullable |
Default
--------------+-----------------------------+-----------+----------+---------
 c_char       | character(102400)           |           |          |
 c_varchar    | character varying(1024)     |           |          |
 c_int        | integer                     |           |          |
 c_dp         | double precision            |           |          |
 c_ts_without | timestamp without time zone |           |          |
Indexes:
    "fvt_obj_operate_update_index_033" UNIQUE, btree (c_int)

postgres=# update fvt_obj_operate_update_table_033 set (c_int) = (20) where
c_int = 20;
2018-06-12 09:46:39.877 CST [94901] ERROR:  source for a multiple-column
UPDATE item must be a sub-SELECT or ROW() expression at character 56
2018-06-12 09:46:39.877 CST [94901] STATEMENT:  update
fvt_obj_operate_update_table_033 set (c_int) = (20) where c_int = 20;
ERROR:  source for a multiple-column UPDATE item must be a sub-SELECT or
ROW() expression
LINE 1: ...e fvt_obj_operate_update_table_033 set (c_int) = (20) where ...
                                                             ^
postgres=# update fvt_obj_operate_update_table_033 set (c_int,c_dp) =
(20,1.11) where c_int = 20;
UPDATE 1
postgres=#

The cause of this error is because syntax rule conflict between "'(' a_expr
')' opt_indirection" and "implicit_row:    '(' expr_list ',' a_expr ')'".I
think "'(' a_expr ')'" is also a subset of row.
I want to modify it，but didn‘t got a good idea.
Please help,thanks!

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> 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"".

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

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

> But I exec sql like "update fvt_obj_operate_update_table_033 set
> (c_int,c_dp) = (20,1.11) where c_int = 20;", got successfull message.

(20) and row(20) are not the same thing, whereas (20,1.11) and
row(20,1.11) are the same thing.  Don't blame us, it was the SQL
committee's idea that "ROW" should be optional for the latter.

> The cause of this error is because syntax rule conflict between "'(' a_expr
> ')' opt_indirection" and "implicit_row:    '(' expr_list ',' a_expr ')'".I
> think "'(' a_expr ')'" is also a subset of row.
> I want to modify it, but didn‘t got a good idea.

Don't even think about it.  It's unfixable without creating even worse
problems, like causing an extra pair of parentheses to change the meaning
of an expression.

            regards, tom lane

>>>>> "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;

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

-- 
Andrew (irc:RhodiumToad)

>>>>> "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)

Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>  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;

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

As far as (a) goes, it was an intentional compatibility breakage,
cf commits 86182b189 and 906bfcad7, and as for (b), the SQL committee
is just nuts here.  They've overloaded the syntax to the point where
it's unresolvable if you try to allow everything the spec suggests
should be allowed.  The expectation in 906bfcad7 was that we'd move
towards the *other* thing the spec demands, namely that the RHS can
be any a_expr yielding a suitable row value.  We can't do that if
it's unclear what is or isn't a ROW() expression, because then the
intended semantics would be undecidable.  And I don't think we want
a situation in which adding "extra" parens changes a legal command
into an illegal one.  For example, suppose f(...) returns a single-column
tuple result.  This should be legal, if x matches the type of the single
column:

    update ... set (x) = f(...)

but if we try to do what you seem to have in mind, this would not be:

    update ... set (x) = (f(...))

That's sufficiently brain-dead that I don't think we want to go there.

            regards, tom lane

ok,thank your respone.I'll make it back to pg v9 syntax.


>>>>> "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;

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

--
Andrew (irc:RhodiumToad)