Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit789c5b0

Browse files
committed
Improve documentation about CASE and constant subexpressions.
The possibility that constant subexpressions of a CASE might be evaluatedat planning time was touched on in 9.17.1 (CASE expressions), but it reallyought to be explained in 4.2.14 (Expression Evaluation Rules) which is theprimary discussion of such topics. Add text and an example there, andrevise the <note> under CASE to link there.Back-patch to all supported branches, since it's acted like this for along time (though 9.2+ is probably worse because of its more aggressiveuse of constant-folding via replanning of nominally-prepared statements).Pre-9.4, also back-patch text added in commit0ce627d about CASE versusaggregate functions.Tom Lane and David Johnston, per discussion of bug #12273.
1 parent0edc237 commit789c5b0

File tree

2 files changed

+56
-5
lines changed

2 files changed

+56
-5
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -9983,11 +9983,13 @@ SELECT ... WHERE CASE WHEN x &lt;&gt; 0 THEN y/x &gt; 1.5 ELSE false END;
99839983

99849984
<note>
99859985
<para>
9986-
As described in <xref linkend="xfunc-volatility">, functions and
9987-
operators marked <literal>IMMUTABLE</literal> can be evaluated when
9988-
the query is planned rather than when it is executed. This means
9989-
that constant parts of a subexpression that is not evaluated during
9990-
query execution might still be evaluated during query planning.
9986+
As described in <xref linkend="syntax-express-eval">, there are various
9987+
situations in which subexpressions of an expression are evaluated at
9988+
different times, so that the principle that <quote><token>CASE</token>
9989+
evaluates only necessary subexpressions</quote> is not ironclad. For
9990+
example a constant <literal>1/0</> subexpression will usually result in
9991+
a division-by-zero failure at planning time, even if it's within
9992+
a <token>CASE</token> arm that would never be entered at run time.
99919993
</para>
99929994
</note>
99939995
</sect2>

‎doc/src/sgml/syntax.sgml

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2299,6 +2299,55 @@ SELECT ... WHERE CASE WHEN x &gt; 0 THEN y/x &gt; 1.5 ELSE false END;
22992299
example, it would be better to sidestep the problem by writing
23002300
<literal>y &gt; 1.5*x</> instead.)
23012301
</para>
2302+
2303+
<para>
2304+
<literal>CASE</> is not a cure-all for such issues, however.
2305+
One limitation of the technique illustrated above is that it does not
2306+
prevent early evaluation of constant subexpressions.
2307+
As described in <xref linkend="xfunc-volatility">, functions and
2308+
operators marked <literal>IMMUTABLE</literal> can be evaluated when
2309+
the query is planned rather than when it is executed. Thus for example
2310+
<programlisting>
2311+
SELECT CASE WHEN x &gt; 0 THEN x ELSE 1/0 END FROM tab;
2312+
</programlisting>
2313+
is likely to result in a division-by-zero failure due to the planner
2314+
trying to simplify the constant subexpression,
2315+
even if every row in the table has <literal>x &gt; 0</> so that the
2316+
<literal>ELSE</> arm would never be entered at run time.
2317+
</para>
2318+
2319+
<para>
2320+
While that particular example might seem silly, related cases that don't
2321+
obviously involve constants can occur in queries executed within
2322+
functions, since the values of function arguments and local variables
2323+
can be inserted into queries as constants for planning purposes.
2324+
Within <application>PL/pgSQL</> functions, for example, using an
2325+
<literal>IF</>-<literal>THEN</>-<literal>ELSE</> statement to protect
2326+
a risky computation is much safer than just nesting it in a
2327+
<literal>CASE</> expression.
2328+
</para>
2329+
2330+
<para>
2331+
Another limitation of the same kind is that a <literal>CASE</> cannot
2332+
prevent evaluation of an aggregate expression contained within it,
2333+
because aggregate expressions are computed before other
2334+
expressions in a <literal>SELECT</> list or <literal>HAVING</> clause
2335+
are considered. For example, the following query can cause a
2336+
division-by-zero error despite seemingly having protected against it:
2337+
<programlisting>
2338+
SELECT CASE WHEN min(employees) > 0
2339+
THEN avg(expenses / employees)
2340+
END
2341+
FROM departments;
2342+
</programlisting>
2343+
The <function>min()</> and <function>avg()</> aggregates are computed
2344+
concurrently over all the input rows, so if any row
2345+
has <structfield>employees</> equal to zero, the division-by-zero error
2346+
will occur before there is any opportunity to test the result of
2347+
<function>min()</>. Instead, use a <literal>WHERE</>
2348+
clause to prevent problematic input rows from
2349+
reaching an aggregate function in the first place.
2350+
</para>
23022351
</sect2>
23032352
</sect1>
23042353

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp