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

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

Tom> For example, suppose f(...) returns a single-column tuple result.
Tom> This should be legal, if x matches the type of the single column:
Tom> update ... set (x) = f(...)
Tom> but if we try to do what you seem to have in mind, this would not
Tom> be:
Tom> update ... set (x) = (f(...))

>> The spec indeed says that this is not valid, since it would wrap an
>> additional ROW() around the result, and would try and assign the row
>> value to x.

Tom> I think that arguing that the spec requires that is fairly shaky,

I'll be more precise: the spec does not define any syntax that would
match UPDATE ... SET (x) = (f(...)) where f() is a function returning a
row type of degree 1 (or any other degree for that matter).

Specifically, the expansions of <contextually typed row value constructor>
don't allow a row-valued function to appear alone that way, while the
alternative, <row value special case>, requires a <nonparenthesized
value expression primary>.

The spec accordingly doesn't require that the construct be rejected, it
could be accepted as a (nonstandard) extension.

Tom> I'd also point out that with the rule that the extra parens
Tom> implicitly mean "ROW()", it's fairly unclear what should happen
Tom> with

Tom> update ... set (x) = ((select ...))

The spec doesn't say that parens (even extra parens) implicitly mean
ROW(), what it does say is that some branches of the syntax implicitly
add a ROW() and some do not.

Specifically, if the thing to the right of the = is a <common value
expression>, <boolean value expression>, or NULL/DEFAULT without parens,
then ROW() is added. ((select scalarcol from ...)) is a <common value
expression>, ((select ROW(scalarcol) from ...)) is, however, not.

Tom> But I repeat that this is a bad idea and we'd regret it in the
Tom> long run; treating extra parens as meaning ROW() in some cases is
Tom> just a fundamentally unsound idea from both syntactic and semantic
Tom> standpoints.

But that's not actually the requirement. Parens are significant to the
spec, but that doesn't mean they have to be significant to us in the
same way as long as we end up accepting all the spec's cases (or at
least the useful ones).

Here is a real problem case (where "rowcol" is a column with a row type
and "rowval" has that same type):

UPDATE ... SET (rowcol) = (rowval)

There literally isn't any way to write the above in the spec except as

UPDATE ... SET (rowcol) = ROW(rowval)
or
UPDATE ... SET (rowcol) = (select ROW(rowval) from ...)

If we don't try and handle SET (rowcol) = (rowval), then I think we can
make all the spec's cases work by this rule: if the thing on the RHS of
the assignment is not a row type, then treat it as a row type of degree
1. That works without needing special rules for parens, and while it
accepts a lot of possibilities that the spec rejects, I don't see any
problems there.

Tom> Given the extremely small number of complaints since v10 came out,
Tom> I think we should just stay with what we have.

I tried looking at what combinations were supported by other major
databases:

UPDATE ... SET (cols...) = (exprs...)

Supported by DB2 and SQLite, both of which allow 1 or more expressions.
Not supported by MySQL, MSSQL, Oracle.

UPDATE ... SET (cols...) = (select exprs...)

Supported by DB2, Oracle, SQLite, for 1 or more columns. Not supported
by MySQL, MSSQL. (Note that this syntax is apparently not valid in the
spec except for the case of a single non-rowtype column!)

UPDATE ... SET (cols...) = (DEFAULT,...)

Supported by DB2, for 1 or more columns. Not supported by MySQL, MSSQL,
Oracle, SQLite.

UPDATE ... SET (cols...) = ROW(...)
UPDATE ... SET (cols...) = nonparenthesized_row_expression
UPDATE ... SET (cols...) = (select ROW(...) from ...)

Not supported by anything that I could find. (HSQLDB claims to support
the full standard syntax, but I don't believe it yet.)

--
Andrew (irc:RhodiumToad)