-
15. Re: Oracle bug? Aggregates in CASE expressions negate short-circuit evaluation.
mathguy Nov 14, 2017 5:04 PM (in response to Frank Kulash)Reading your Reply 8 further, I don't understand how you read the following, from the documentation, to mean something other than what it says.
Oracle Database uses short-circuit evaluation. For a simple CASE expression, the database evaluates each comparison_expr value only before comparing it to expr, rather than evaluating all comparison_expr values before comparing any of them with expr. Consequently, Oracle never evaluates a comparison_expr if a previous comparison_expr is equal to expr.
This doesn't say the comparisons are not made; it says very specifically, and more than once, that the expressions themselves (in the WHEN conditions) are not evaluated unless needed.
I just showed, in a separate Reply above, that in the case of aggregate functions, the documentation does not agree with the actual behavior of the database. I also showed that in the ABSENCE of aggregates, the actual behavior does agree with the documentation (division by zero, for example, in a WHEN condition, does not automatically trigger an error.)
-
16. Re: Oracle bug? Aggregates in CASE expressions negate short-circuit evaluation.
Sven W. Nov 14, 2017 5:16 PM (in response to mathguy)It might be a strange case of documentation terminology.
Here is my hypothesis:
A "comparison expression|condition" is something like "sum(x) > 17" . This is not evaluated because of short circuit eval!
However the "sum(x)" operator is a "value expression" (with reference to the error message). The value might need to be provided and calculated, even with short circuit eval happening at a later point.
-
17. Re: Oracle bug? Aggregates in CASE expressions negate short-circuit evaluation.
mathguy Nov 14, 2017 5:16 PM (in response to Sven W.)No, "sum(x) > 17" is called a condition, not a comparison_expr. "Expressions" and "conditions" are very different things in Oracle (because there is no Boolean data type). Comparison expressions appear only in "simple" CASE expressions; conditions like the one you suggested appear in "searched" case expressions.
The example I gave is specifically of a simple case expression, and SUM(VAL) (not some logical condition that evaluates to true or false) is the comparison expression.
-
18. Re: Oracle bug? Aggregates in CASE expressions negate short-circuit evaluation.
Sven W. Nov 14, 2017 5:22 PM (in response to mathguy)Yes I just noticed the difference. Doesn't change my argument/hypothesis.
I still believe that oracle makes some logical distinction between the "evaluation" part and the "provide values" part.
We need to find the doc, where it is explained what a comparison expression is or what a condition is.
The sub parts (e.g. operands that make up a condition) are not necessarily included in the short circuit evaluation.
Nothing relevant in here
https://docs.oracle.com/database/121/SQLRF/conditions002.htm#SQLRF52107
-
19. Re: Oracle bug? Aggregates in CASE expressions negate short-circuit evaluation.
Frank Kulash Nov 14, 2017 5:21 PM (in response to mathguy)Hi,
mathguy wrote:
Reading your Reply 8 further, I don't understand how you read the following, from the documentation, to mean something other than what it says.
Oracle Database uses short-circuit evaluation. For a simple CASE expression, the database evaluates each comparison_expr value only before comparing it to expr, rather than evaluating all comparison_expr values before comparing any of them with expr. Consequently, Oracle never evaluates a comparison_expr if a previous comparison_expr is equal to expr.
This doesn't say the comparisons are not made; it says very specifically, and more than once, that the expressions themselves (in the WHEN conditions) are not evaluated unless needed.
I just showed, in a separate Reply above, that in the case of aggregate functions, the documentation does not agree with the actual behavior of the database. I also showed that in the ABSENCE of aggregates, the actual behavior does agree with the documentation (division by zero, for example, in a WHEN condition, does not automatically trigger an error.)
Sorry, I don't see any mistake in reply #8; I just didn't use the exact same terminology as the manual. (I said a "comparison is done" instead of "a comparison_expr value is evaluated".)
-
20. Re: Oracle bug? Aggregates in CASE expressions negate short-circuit evaluation.
Paulzip Nov 14, 2017 5:33 PM (in response to mathguy)I think aggregations (conditional or not) need to check columns referenced are valid for group by expressions, so anything involving columns whether conditional or not are evaluated.
-
21. Re: Oracle bug? Aggregates in CASE expressions negate short-circuit evaluation.
mathguy Nov 14, 2017 5:33 PM (in response to Frank Kulash)I was referring specifically to this, from your Reply:
Short-circuiting, apparently, applies only to the comparison expressions; it has nothing to do with the return expressions. Not only that, Oracle only claims that the comparisons will not be done: [ ... ]
I think I understand what you meant though - you were only referring to searched CASE expressions (as in my original message). The confusion is created by your mention of comparison expressions, which exist only in simple case expressions. I interpreted the above fragment to say: "comparison expressions are always evaluated, but they are not compared to expr unless needed." I showed above that in most cases the comparison expressions (in simple CASE expressions) are not evaluated if not needed, but they are evaluated when they involve aggregates.
-
22. Re: Oracle bug? Aggregates in CASE expressions negate short-circuit evaluation.
Frank Kulash Nov 14, 2017 6:37 PM (in response to mathguy)Hi,
mathguy wrote:
...
I think I understand what you meant though - you were only referring to searched CASE expressions (as in my original message). ...
Right. Your example only included a searched CASE expression, and reply #8 only included searched CASE expressions.
I agree with Sven, in particular with what he said in reply #16. The SQL Language manual makes it clear (to me, at least) that short-circuiting applies to whatever comes between a WHEN keyword and its corresponding THEN keyword. So if a searched CASE expression says
WHEN SUM (val) > 0 THEN
the documentation says that
SUM (val) > 0
will not be evaluated if a previous condition was true. It says nothing about whether
SUM (val)
will be evaluated or not.
If a simple CASE expression says
WHEN SUM (val) THEN
then the documentation says that
SUM (val)
will not be evaluated if a previous condition was true. For example:
SELECT CASE 0
WHEN 0
THEN 'Aardvark'
WHEN SUM (val)
THEN 'Baracuda'
END AS c
FROM tbl;
does not raise an error; it produces this output:
C
--------
Aardvark
Aardvark
Aardvark
Note that there are 3 rows in the result set, not just 1 (like there would be if an aggregate function was invoked).
EDIT: I tested in version 12.1.0.2.0.
-
23. Re: Oracle bug? Aggregates in CASE expressions negate short-circuit evaluation.
Sven W. Nov 14, 2017 6:09 PM (in response to Frank Kulash)1 person found this helpfulWhat!?
That looks like a bug to me! Which db version did you run it?
-- Edit:
Just tested it myself on 12.1.0.2 - SE
with tbl as (select 1 val from dual union all select 2 val from dual union all select 3 val from dual ) SELECT CASE 0 WHEN 0 THEN 'Aardvark' WHEN SUM (val) THEN 'Baracuda' END AS c FROM tbl;
Aardvark
Aardvark
Aardvark
with tbl as (select 1 val from dual union all select 2 val from dual union all select 3 val from dual ) SELECT CASE 6 WHEN 0 THEN 'Aardvark' WHEN SUM (val) THEN 'Baracuda' END AS c FROM tbl;
Baracuda
Wow! Thats a new one for my list of strange effects how the select clause can influence the number of rows returned.
-
24. Re: Oracle bug? Aggregates in CASE expressions negate short-circuit evaluation.
mathguy Nov 14, 2017 6:13 PM (in response to Frank Kulash)Perfect - we interpret the language in the documentation exactly in the same way.
I showed in Reply 14 that the presence of aggregate functions causes the database to behave in a way that contradicts the documentation (or at least our understanding of it).
-
25. Re: Oracle bug? Aggregates in CASE expressions negate short-circuit evaluation.
Frank Kulash Nov 14, 2017 6:40 PM (in response to Sven W.)HI,
Sven W. wrote:
...
Which db version did you run it?
...
12.1.0.2.0.
Sorry, I should have said that earlier.
-
26. Re: Oracle bug? Aggregates in CASE expressions negate short-circuit evaluation.
Solomon Yakobson Nov 14, 2017 7:32 PM (in response to Solomon Yakobson)And to show is it windowing related:
SQL> select case when 3 = count(*) over() then 0 else sum(val) over(partition by rowid) end
2 from tbl;
CASEWHEN3=COUNT(*)OVER()THEN0ELSESUM(VAL)OVER(PARTITIONBYROWID)END
------------------------------------------------------------------
0
0
0
SQL>
SY.
-
27. Re: Oracle bug? Aggregates in CASE expressions negate short-circuit evaluation.
Solomon Yakobson Nov 14, 2017 7:54 PM (in response to mathguy)mathguy wrote:
The Oracle documentation for SQL (not PL/SQL) specifically states that CASE expressions use short-circuit evaluation. However, that only applies to short-circuiting the evaluation of logical conditions in the WHEN parts, not the evaluation of expressions in the THEN parts.
And short circuit evaluation of logical conditions implies we stop evaluating when result is already known. Anyway, look at:
COALESCE
returns the first non-nullexpr
in the expression list. You must specify at least two expressions. If all occurrences ofexpr
evaluate to null, then the function returns null.Oracle Database uses short-circuit evaluation. The database evaluates each
expr
value and determines whether it isNULL
, rather than evaluating all of theexpr
values before determining whether any of them isNULL
.SQL> select coalesce('A',to_char(sum(val))) from tbl;
select coalesce('A',to_char(sum(val))) from tbl
*
ERROR at line 1:
ORA-01426: numeric overflow
SQL>
Looks like a bug to me. But I do realize implementing short-circuit evaluation for aggregate function would make statements holding resources for a longer time and in many cases result in performance degradation. For example, MAX(VAL) and SUM(VAL) would have to be calculated in separate passes.
SY.