Re: plpgsql variable assignment with union is broken

čt 7. 1. 2021 v 17:29 odesílatel Merlin Moncure <mmoncure(at)gmail(dot)com> napsal:

> 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?

PLpgSQL_Expr: opt_target_list
<--><--><-->from_clause where_clause
<--><--><-->group_clause having_clause window_clause
<--><--><-->opt_sort_clause opt_select_limit opt_for_locking_clause
<--><--><--><-->{

So SELECT INTO and assignment are not fully compatible now.

Regards

Pavel

> merlin
>