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?
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
do $$ declare t bool; begin t := (SELECT true WHERE false UNION SELECT true ); end; $$;
