On Wed, Jan 6, 2021 at 9:39 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Merlin Moncure <mmoncure@gmail.com> writes:
> > On Tue, Jan 5, 2021 at 3:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> easteregg@verfriemelt.org writes:
> >>> i found, that the behaviour of variable assignment in combination with union is not working anymore:
> >>> DO $$
> >>> DECLARE t bool;
> >>> begin
> >>> t := a FROM ( SELECT true WHERE false ) t(a) UNION SELECT true AS a;
> >>> END $$;
> >>> is this an intended change or is it a bug?
>
> >> It's an intended change, or at least I considered the case and thought
> >> that it was useless because assignment will reject any result with more
> >> than one row. Do you have any non-toy example that wouldn't be as
> >> clear or clearer without using UNION? The above sure seems like an
> >> example of awful SQL code.
>
> > What is the definition of broken here? What is the behavior of the
> > query with the change and why?
>
> The OP is complaining that that gets a syntax error since c9d529848.
>
> > OP's query provably returns a single row and ought to always assign
> > true as written.
>
> My opinion is that (a) it's useless and (b) there has never been any
> documentation that claimed that you could do this.
Here is what the documentation says:
> variable { := | = } expression;
> As explained previously, the expression in such a statement is evaluated by means of an SQL SELECT command sent to
themain database engine.
This is valid SQL:
SELECT a FROM ( SELECT true WHERE false ) t(a) UNION SELECT true AS a;
So I'd argue that OP's query *is* syntactically valid per the rules as
I understand them.
and is my opinion entirely consistent with the documentation in that it
a) resolves exactly one row, and:
b) is made syntactically valid by prefixing the expression with SELECT.
Aesthetical considerations are irrelevant IMO.
merlin
