Re: plpgsql variable assignment with union is broken

On Wed, Jan 6, 2021 at 11:07 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> čt 7. 1. 2021 v 4:20 odesílatel Merlin Moncure <mmoncure(at)gmail(dot)com> napsal:
>>
>> 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;
>
>
> ANSI SQL allows only SELECT INTO or var := SQL expression and SQL expression can be (subquery) too

This is PLPGSQL not ansi SQL so that's irrelevant. If queries along
the lines of:
var := FROM (SELECT ..) UNION ..

are narrowly broken, ok, but is that all that's going on here? I
guess I ought to test.

I have a 300k line pl/pgsql project, this thread is terrifying me. I
am going to be blunt here and say I am not comfortable with tightening
pl/pgsql syntax without an impact assessment, The point that this is
undocumanted behavior is weak, and it's already turning up problem
reports. IMO, expectation has been clearly set that
var := expression;

is more or less interchangeable with
SELECT INTO var expression;

Again, if this is narrowly confined to assignment into set query
operations, maybe this is not so bad. But is it?

merlin