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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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.
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)
So, 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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
"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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs