On Wed, Jan 6, 2021 at 11:07 PM Pavel Stehule <pavel.steh...@gmail.com> wrote:
> č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 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