BUG #13973: Constants resolved in then/else clauses

Started by Nonameabout 10 years ago4 messagesbugs
Jump to latest
#1Noname
harry.townsend@eflowglobal.com

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

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: Noname (#1)
Re: BUG #13973: Constants resolved in then/else clauses

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.

#3Alvaro Herrera
alvherre@2ndquadrant.com
In reply to: Noname (#1)
Re: BUG #13973: Constants resolved in then/else clauses

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

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: David G. Johnston (#2)
Re: BUG #13973: Constants resolved in then/else clauses

"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