Обсуждение: BUG #12273: CASE Expression BUG
The following bug has been logged on the website:
Bug reference: 12273
Logged by: Tibor Jaksits
Email address: jaksits.tibor@gmail.com
PostgreSQL version: 9.3.4
Operating system: Linux Debian 64-bit 4.7.2-5
Description:
I created a simple function that returns the number received parameter.
CREATE OR REPLACE FUNCTION __is_numeric_test(a_double_param double
precision)
RETURNS double precision AS
$BODY$DECLARE
BEGIN
RETURN a_double_param;
END;$BODY$
LANGUAGE plpgsql VOLATILE;
I created two other functions:
CREATE OR REPLACE FUNCTION __is_numeric_test_work(a_text_param text)
RETURNS double precision AS
$BODY$DECLARE
BEGIN
RETURN __is_numeric_test((CASE WHEN is_numeric(a_text_param) THEN
a_text_param::double precision ELSE 0.0::double precision END));
END;$BODY$
LANGUAGE plpgsql VOLATILE;
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;
The return value of the "SELECT * FROM __is_numeric_test_work('')" query is
0 (correct operation)
But the query "SELECT * FROM __is_numeric_test_does_not_work('')" I get an
error message:
invalid input syntax for type double precision: ""
CONTEXT: SQL statement "SELECT __is_numeric_test
FROM __is_numeric_test((CASE WHEN is_numeric(a_text_param)
THEN a_text_param::double precision ELSE 0.0::double precision END))"
PL/pgSQL function __is_numeric_test_does_not_work(text) line 5 at SQL
statement
********** Error **********
ERROR: invalid input syntax for type double precision: ""
SQL state: 22P02
Context: SQL statement "SELECT __is_numeric_test
FROM __is_numeric_test((CASE WHEN is_numeric(a_text_param)
THEN a_text_param::double precision ELSE 0.0::double precision END))"
PL/pgSQL function __is_numeric_test_does_not_work(text) line 5 at SQL
statement
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
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.
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
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-EXP= RESS-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. > > > =E2=80=8B+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. =E2=80=8B
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 diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index ef69b94..5ef1911 100644 *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *************** SELECT ... WHERE CASE WHEN x <> 0 *** 11179,11189 **** <note> <para> ! As described in <xref linkend="xfunc-volatility">, functions and ! operators marked <literal>IMMUTABLE</literal> can be evaluated when ! the query is planned rather than when it is executed. This means ! that constant parts of a subexpression that is not evaluated during ! query execution might still be evaluated during query planning. </para> </note> </sect2> --- 11179,11192 ---- <note> <para> ! As described in <xref linkend="syntax-express-eval">, there are various ! situations in which subexpressions of an expression are evaluated at ! different times, 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. For example a ! constant <literal>1/0</> subexpression will usually result in a ! division-by-zero failure at planning time, even if it's within ! a <token>CASE</token> arm that would never be entered at run time. </para> </note> </sect2> diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 6f8b7e8..d2ece37 100644 *** a/doc/src/sgml/syntax.sgml --- b/doc/src/sgml/syntax.sgml *************** SELECT ... WHERE CASE WHEN x > 0 THEN *** 2439,2445 **** </para> <para> ! A limitation of this technique is that a <literal>CASE</> cannot prevent evaluation of an aggregate expression contained within it, because aggregate expressions are computed before <quote>scalar</> expressions in a <literal>SELECT</> list or <literal>HAVING</> clause --- 2439,2472 ---- </para> <para> ! <literal>CASE</> is not a panacea for such issues, however. ! One limitation of the technique illustrated above is that it does not ! prevent early evaluation of constant subexpressions. ! As described in <xref linkend="xfunc-volatility">, functions and ! operators marked <literal>IMMUTABLE</literal> can be evaluated when ! the query is planned rather than when it is executed. Thus for example ! <programlisting> ! SELECT CASE WHEN x > 0 THEN x ELSE 1 / 0 END FROM tab; ! </programlisting> ! is likely to result in a division-by-zero failure due to the planner ! trying to simplify the constant subexpression, ! even if every row in the table has <literal>x > 0</> so that the ! <literal>ELSE</> arm would never be entered at run time. ! </para> ! ! <para> ! While that particular example might seem silly, related cases that don't ! obviously involve constants can occur in queries executed within ! functions, since the values of function parameters and local variables ! can be inserted into queries as constants for planning purposes. ! Thus for example in <application>PL/pgSQL</> functions, using an ! <literal>IF</>-<literal>THEN</>-<literal>ELSE</> statement to protect ! a risky computation is much safer than just nesting it in a ! <literal>CASE</> expression. ! </para> ! ! <para> ! Another limitation of the same kind is that a <literal>CASE</> cannot prevent evaluation of an aggregate expression contained within it, because aggregate expressions are computed before <quote>scalar</> expressions in a <literal>SELECT</> list or <literal>HAVING</> clause
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-EXP= RESS-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 a= n > >> 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 tha= t > could throw errors is at risk. I'm using divide-by-zero as the canonical > example in this area. > > > =E2=80=8B "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" =3D=3D "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. =E2=80=8B
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
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. > =E2=80=8BIs the example with the "WITH" CTE too involved to use instead?=E2= =80=8B > > > While it is a great word I don't really believe "panacea" is good for o= ur > > documentation given the diverse English language experiences of its > readers. > > Hm. "cure-all" maybe? > Maybe "miracle cure" - =E2=80=8Bthough "cure-all" isn't bad. Maybe solicit= the opinion of a couple of non-English speakers. > > "the values of function parameters" =3D=3D "function arguments" ... tho= ugh > not > > everyone is fully aware (or remembers) the difference between arguments > and > > parameters. > > OK. > > > "Thus for example in [...] functions..." -> Therefore, in [...] functio= ns > > 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. > =E2=80=8BYour's just didn't read that well to me...though the overall conte= nt was good. Maybe drop "Thus" and just lead with "Within PL/pgSQL functions, for example, using an IF [...]" =E2=80=8BDavid 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