Обсуждение: BUG #13973: Constants resolved in then/else clauses
The following bug has been logged on the website: Bug reference: 13973 Logged by: Harry Townsend Email address: harry.townsend@eflowglobal.com PostgreSQL version: 9.1.20 Operating system: Windows Server 2008 Description: I attempted to create a safety check in a query using a "case when" statement such that if the condition evaluated to false, it would return (1 / 0) in order to nullify the entire transaction. With small conditions, this works fine. With larger ones, it seems that the constants in the "else" clause (didn't test for "then" clause) are evaluated before the condition, causing it to fail every time. If the "else" part requires a column value, it seems to force the condition to evaluate first though.
On Thu, Feb 18, 2016 at 6:57 AM, <harry.townsend@eflowglobal.com> wrote: > The following bug has been logged on the website: > > Bug reference: 13973 > Logged by: Harry Townsend > Email address: harry.townsend@eflowglobal.com > PostgreSQL version: 9.1.20 > Operating system: Windows Server 2008 > Description: > > I attempted to create a safety check in a query using a "case when" > statement such that if the condition evaluated to false, it would return = (1 > / 0) in order to nullify the entire transaction. With small conditions, > this > works fine. With larger ones, it seems that the constants in the "else" > clause (didn't test for "then" clause) are evaluated before the condition= , > causing it to fail every time. If the "else" part requires a column value= , > it seems to force the condition to evaluate first though. > =E2=80=8BSo, there is a note in the documentation that exactly addresses wh= at you are trying to do....=E2=80=8B http://www.postgresql.org/docs/current/static/functions-conditional.html =E2=80=8B""" =E2=80=8BAs described in Section 4.2.14, there are various situations in wh= ich subexpressions of an expression are evaluated at different times, so that the principle that "CASE evaluates only necessary subexpressions" is not ironclad.* For example a constant 1/0 subexpression will usually result in a division-by-zero failure* at planning time, even if it's within a CASE arm that would never be entered at run time. """ (emphasis mine) =E2=80=8BSo, not a bug. David J.
harry.townsend@eflowglobal.com wrote: > The following bug has been logged on the website: > > Bug reference: 13973 > Logged by: Harry Townsend > Email address: harry.townsend@eflowglobal.com > PostgreSQL version: 9.1.20 > Operating system: Windows Server 2008 > Description: > > I attempted to create a safety check in a query using a "case when" > statement such that if the condition evaluated to false, it would return (1 > / 0) in order to nullify the entire transaction. With small conditions, this > works fine. With larger ones, it seems that the constants in the "else" > clause (didn't test for "then" clause) are evaluated before the condition, > causing it to fail every time. If the "else" part requires a column value, > it seems to force the condition to evaluate first though. Can you show a complete example? -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Thu, Feb 18, 2016 at 6:57 AM, <harry.townsend@eflowglobal.com> wrote: >> I attempted to create a safety check in a query using a "case when" >> statement such that if the condition evaluated to false, it would return (1 >> / 0) in order to nullify the entire transaction. > âSo, there is a note in the documentation that exactly addresses what you > are trying to do....â > http://www.postgresql.org/docs/current/static/functions-conditional.html > â""" > âAs described in Section 4.2.14, there are various situations in which > subexpressions of an expression are evaluated at different times, so that > the principle that "CASE evaluates only necessary subexpressions" is not > ironclad.* For example a constant 1/0 subexpression will usually result in > a division-by-zero failure* at planning time, even if it's within a CASE > arm that would never be entered at run time. > """ (emphasis mine) Yeah. What you need to do is ensure that the failure-causing thing doesn't look like a constant subexpression. I'd suggest a more useful approach is create function fail() returns int as $$begin raise exception ...; end$$ language plpgsql volatile; .... CASE WHEN <test condition> THEN 0 ELSE fail() END ... The "volatile" marker on the function teaches the planner that the function has side-effects (viz, an exception) and so must not be speculatively evaluated. This'd also have the advantage of producing a much more useful error message (you might wanna consider adding parameters to the function, such as text to go into the error message). regards, tom lane