BUG #12273: CASE Expression BUG

jaksits.tibor@gmail.com writes:

CREATE OR REPLACE FUNCTION __is_numeric_test_does_not_work(a_text_param
text)
RETURNS double precision AS
$BODY$DECLARE
ret double precision;
BEGIN
SELECT __is_numeric_test
INTO ret
FROM __is_numeric_test((CASE WHEN is_numeric(a_text_param) THEN
a_text_param::double precision ELSE 0.0::double precision END));
RETURN ret;
END;$BODY$
LANGUAGE plpgsql VOLATILE;

But the query "SELECT * FROM __is_numeric_test_does_not_work('')" I get an
error message:
invalid input syntax for type double precision: ""

You didn't show us what is_numeric() is, so it's impossible to reproduce
this example, but I imagine what is happening is that the value of
a_text_param is being substituted into the SELECT as a text constant, and
then constant-folding leads to attempting to simplify a_text_param::double
precision immediately.

We're unlikely to change this, because it would cripple optimization
attempts. The fact that const-simplification doesn't happen in the other
way you wrote the function is not more-correct behavior, it's just an
implementation artifact that you shouldn't rely on. What you need to do
is code this as an if-then-else sequence, not CASE, so that you don't
attempt to evaluate any expressions with undefined constant
subexpressions.

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

Tom Lane-2 wrote

jaksits.tibor@

writes:

CREATE OR REPLACE FUNCTION __is_numeric_test_does_not_work(a_text_param
text)
RETURNS double precision AS
$BODY$DECLARE
ret double precision;
BEGIN
SELECT __is_numeric_test
INTO ret
FROM __is_numeric_test((CASE WHEN is_numeric(a_text_param) THEN
a_text_param::double precision ELSE 0.0::double precision END));
RETURN ret;
END;$BODY$
LANGUAGE plpgsql VOLATILE;

But the query "SELECT * FROM __is_numeric_test_does_not_work('')" I get
an
error message:
invalid input syntax for type double precision: ""

You didn't show us what is_numeric() is, so it's impossible to reproduce
this example, but I imagine what is happening is that the value of
a_text_param is being substituted into the SELECT as a text constant, and
then constant-folding leads to attempting to simplify a_text_param::double
precision immediately.

We're unlikely to change this, because it would cripple optimization
attempts. The fact that const-simplification doesn't happen in the other
way you wrote the function is not more-correct behavior, it's just an
implementation artifact that you shouldn't rely on. What you need to do
is code this as an if-then-else sequence, not CASE, so that you don't
attempt to evaluate any expressions with undefined constant
subexpressions.

Note that this is documented here:

http://www.postgresql.org/docs/9.4/interactive/functions-conditional.html#FUNCTIONS-CASE

Specifically, the "Note" at the end of 9.17.1

Maybe an example would make this got-cha more memorable but it is noted in
the docs right next to the spot where it is described that CASE evaluation
does short-circuit during execution - just not always during planning.

David J.

--
View this message in context: http://postgresql.nabble.com/BUG-12273-CASE-Expression-BUG-tp5831307p5831325.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

--
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:

Tom Lane-2 wrote

We're unlikely to change this, because it would cripple optimization
attempts. The fact that const-simplification doesn't happen in the other
way you wrote the function is not more-correct behavior, it's just an
implementation artifact that you shouldn't rely on. What you need to do
is code this as an if-then-else sequence, not CASE, so that you don't
attempt to evaluate any expressions with undefined constant
subexpressions.

Note that this is documented here:
http://www.postgresql.org/docs/9.4/interactive/functions-conditional.html#FUNCTIONS-CASE
Specifically, the "Note" at the end of 9.17.1

Hmm ... I'd just been looking at 4.2.14:
http://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
and thinking that maybe it should mention this. Perhaps we ought to
relocate the text about constant subexpressions into 4.2.14 (and add an
example), and then link there from 9.17.1.

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

On Thu, Dec 18, 2014 at 10:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David G Johnston <david.g.johnston@gmail.com> writes:

Tom Lane-2 wrote

We're unlikely to change this, because it would cripple optimization
attempts. The fact that const-simplification doesn't happen in the

other

way you wrote the function is not more-correct behavior, it's just an
implementation artifact that you shouldn't rely on. What you need to do
is code this as an if-then-else sequence, not CASE, so that you don't
attempt to evaluate any expressions with undefined constant
subexpressions.

Note that this is documented here:

http://www.postgresql.org/docs/9.4/interactive/functions-conditional.html#FUNCTIONS-CASE

Specifically, the "Note" at the end of 9.17.1

Hmm ... I'd just been looking at 4.2.14:

http://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
and thinking that maybe it should mention this. Perhaps we ought to
relocate the text about constant subexpressions into 4.2.14 (and add an
example), and then link there from 9.17.1.

​+1

Something like:

Before "A limitation of this technique [...]"

The are two limitations to this technique: planner optimizations may occur
and aggregate expressions will be evaluated.

The aggregate expression limitation exists because aggregate expressions
[continue as-is...]

The planner optimization limitation exists because [i'm not sure what to
write here...]

The question is how detailed do we need to get here...is it an issue
specific to casting or is there some other interplay happening? The fact
that it is a constant doesn't seem to be enough. Is it only because this
was attempted in pl/pgsql - which has unique planning mechanics compared to
SQL (functions and/or raw) - and should, probably also, be addressed there
(though not sure where you'd put it...).

The answer you provided basically resolved to: avoid the in-query SQL CASE
and instead use a pl/pgsql IF to perform the conditional. It didn't matter
for this inquiry but the fact that both SQL and pl/pgsql have - differently
behaving - CASE expressions/statements may factor into any explanation.

David J.

​

David Johnston <david.g.johnston@gmail.com> writes:

On Thu, Dec 18, 2014 at 10:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Hmm ... I'd just been looking at 4.2.14:
http://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
and thinking that maybe it should mention this. Perhaps we ought to
relocate the text about constant subexpressions into 4.2.14 (and add an
example), and then link there from 9.17.1.

+1

Something like:
Before "A limitation of this technique [...]"
The are two limitations to this technique: planner optimizations may occur
and aggregate expressions will be evaluated.

Yeah, I've just been working on some text to put there. I'm still
wordsmithing it, but right now it's as attached.

The question is how detailed do we need to get here...is it an issue
specific to casting or is there some other interplay happening?

It's not particularly specific to casting, any constant subexpression that
could throw errors is at risk. I'm using divide-by-zero as the canonical
example in this area.

regards, tom lane

On Thu, Dec 18, 2014 at 1:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Johnston <david.g.johnston@gmail.com> writes:

On Thu, Dec 18, 2014 at 10:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Hmm ... I'd just been looking at 4.2.14:

http://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL

and thinking that maybe it should mention this. Perhaps we ought to
relocate the text about constant subexpressions into 4.2.14 (and add an
example), and then link there from 9.17.1.

+1

Something like:
Before "A limitation of this technique [...]"
The are two limitations to this technique: planner optimizations may

occur

and aggregate expressions will be evaluated.

Yeah, I've just been working on some text to put there. I'm still
wordsmithing it, but right now it's as attached.

The question is how detailed do we need to get here...is it an issue
specific to casting or is there some other interplay happening?

It's not particularly specific to casting, any constant subexpression that
could throw errors is at risk. I'm using divide-by-zero as the canonical
example in this area.

​
"so that the principle that <quote><token>CASE</token>
does not evaluate any subexpressions that are not needed to determine
the result</quote> is not ironclad."

so the principle that [...] only evaluates necessary subexpressions is not
ironclad.

The problem with your example is that the following does not error:

SELECT CASE WHEN false THEN 1/0 ELSE 1 END;

which is where I threw up my hands and realized I do not know
enough...though a more complete example will fail:

WITH tbl (v) AS ( VALUES (1),(2),(3) )
SELECT CASE WHEN v > 0 THEN v ELSE 1/0 END
FROM tbl;

Some observations/suggestions:

While it is a great word I don't really believe "panacea" is good for our
documentation given the diverse English language experiences of its readers.

"the values of function parameters" == "function arguments" ... though not
everyone is fully aware (or remembers) the difference between arguments and
parameters.

"Thus for example in [...] functions..." -> Therefore, in [...] functions
it is more reliable to use an [...] statement to protect a risky [...]
statement compared to using a nested CASE expression.

David J.
​

David Johnston <david.g.johnston@gmail.com> writes:

"so that the principle that <quote><token>CASE</token>
does not evaluate any subexpressions that are not needed to determine
the result</quote> is not ironclad."

so the principle that [...] only evaluates necessary subexpressions is not
ironclad.

OK, done that way.

The problem with your example is that the following does not error:

SELECT CASE WHEN false THEN 1/0 ELSE 1 END;

That's why that isn't the example ;-). In this case, since the WHEN is a
constant, the constant-expression simplifier itself figures out that it
can throw away the THEN expression and replace the whole CASE with the
ELSE expression. So it doesn't bother const-simplifying the THEN, hence
no error.

While it is a great word I don't really believe "panacea" is good for our
documentation given the diverse English language experiences of its readers.

Hm. "cure-all" maybe?

"the values of function parameters" == "function arguments" ... though not
everyone is fully aware (or remembers) the difference between arguments and
parameters.

OK.

"Thus for example in [...] functions..." -> Therefore, in [...] functions
it is more reliable to use an [...] statement to protect a risky [...]
statement compared to using a nested CASE expression.

Meh. The same principle applies in other languages too, so I don't think
your wording is an improvement.

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

On Thu, Dec 18, 2014 at 1:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Johnston <david.g.johnston@gmail.com> writes:

"so that the principle that <quote><token>CASE</token>
does not evaluate any subexpressions that are not needed to determine
the result</quote> is not ironclad."

so the principle that [...] only evaluates necessary subexpressions is

not

ironclad.

OK, done that way.

The problem with your example is that the following does not error:

SELECT CASE WHEN false THEN 1/0 ELSE 1 END;

That's why that isn't the example ;-). In this case, since the WHEN is a
constant, the constant-expression simplifier itself figures out that it
can throw away the THEN expression and replace the whole CASE with the
ELSE expression. So it doesn't bother const-simplifying the THEN, hence
no error.

​Is the example with the "WITH" CTE too involved to use instead?​

While it is a great word I don't really believe "panacea" is good for our
documentation given the diverse English language experiences of its

readers.

Hm. "cure-all" maybe?

Maybe "miracle cure" - ​though "cure-all" isn't bad. Maybe solicit the
opinion of a couple of non-English speakers.

"the values of function parameters" == "function arguments" ... though

not

everyone is fully aware (or remembers) the difference between arguments

and

parameters.

OK.

"Thus for example in [...] functions..." -> Therefore, in [...] functions
it is more reliable to use an [...] statement to protect a risky [...]
statement compared to using a nested CASE expression.

Meh. The same principle applies in other languages too, so I don't think
your wording is an improvement.

​Your's just didn't read that well to me...though the overall content was
good. Maybe drop "Thus" and just lead with "Within PL/pgSQL functions, for
example, using an IF [...]"

​David J.

David Johnston <david.g.johnston@gmail.com> writes:

On Thu, Dec 18, 2014 at 1:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

That's why that isn't the example ;-). In this case, since the WHEN is a
constant, the constant-expression simplifier itself figures out that it
can throw away the THEN expression and replace the whole CASE with the
ELSE expression. So it doesn't bother const-simplifying the THEN, hence
no error.

​Is the example with the "WITH" CTE too involved to use instead?​

Doesn't seem like it really adds anything.

Meh. The same principle applies in other languages too, so I don't think
your wording is an improvement.

​Your's just didn't read that well to me...though the overall content was
good. Maybe drop "Thus" and just lead with "Within PL/pgSQL functions, for
example, using an IF [...]"

OK.

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