Re: plpgsql variable assignment with union is broken

On Tue, Jan 5, 2021 at 3:40 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> easteregg(at)verfriemelt(dot)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?

OP's query provably returns a single row and ought to always assign
true as written. A real world example might evaluate multiple
condition branches so that the assignment resolves true if any branch
is true. It could be rewritten with 'OR' of course.

Is this also "broken"?
t := a FROM ( SELECT 'something' WHERE _Flag) t(a) UNION SELECT
'something else' AS a WHERE NOT _Flag;

What about this?
SELECT INTO t true WHERE false
UNION select true;

merlin