čt 7. 1. 2021 v 4:20 odesílatel Merlin Moncure <mmonc...@gmail.com> napsal:
> On Tue, Jan 5, 2021 at 3:40 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > > > > easter...@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; $$; Regards Pavel > merlin > > >
