From 5b516835897f2a487eedacdb605ac40d52f6b484 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 18 Dec 2014 16:38:52 -0500 Subject: [PATCH] Improve documentation about CASE and constant subexpressions. The possibility that constant subexpressions of a CASE might be evaluated at planning time was touched on in 9.17.1 (CASE expressions), but it really ought to be explained in 4.2.14 (Expression Evaluation Rules) which is the primary discussion of such topics. Add text and an example there, and revise the under CASE to link there. Back-patch to all supported branches, since it's acted like this for a long time (though 9.2+ is probably worse because of its more aggressive use of constant-folding via replanning of nominally-prepared statements). Pre-9.4, also back-patch text added in commit 0ce627d4 about CASE versus aggregate functions. Tom Lane and David Johnston, per discussion of bug #12273. --- doc/src/sgml/func.sgml | 12 +++++++----- doc/src/sgml/syntax.sgml | 31 +++++++++++++++++++++++++++++-- 2 files changed, 36 insertions(+), 7 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index ef69b94cf0..7934a1263e 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -11179,11 +11179,13 @@ SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END; - As described in , functions and - operators marked IMMUTABLE 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. + As described in , 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. diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 6f8b7e8b28..4b81b08882 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -2439,9 +2439,36 @@ SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END; - A limitation of this technique is that a CASE cannot + CASE is not a cure-all 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 , functions and + operators marked IMMUTABLE can be evaluated when + the query is planned rather than when it is executed. Thus for example + +SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab; + + 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 x > 0 so that the + ELSE arm would never be entered at run time. + + + + 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 arguments and local variables + can be inserted into queries as constants for planning purposes. + Within PL/pgSQL functions, for example, using an + IF-THEN-ELSE statement to protect + a risky computation is much safer than just nesting it in a + CASE expression. + + + + Another limitation of the same kind is that a CASE cannot prevent evaluation of an aggregate expression contained within it, - because aggregate expressions are computed before scalar + because aggregate expressions are computed before other expressions in a SELECT list or HAVING clause are considered. For example, the following query can cause a division-by-zero error despite seemingly having protected against it: -- 2.40.0