Join the world’s largest interactive community dedicated to Oracle technologies.

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.

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

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".)

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.

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.

HI,

Sven W. wrote:

 

...

       Which db version did you run it?

...

12.1.0.2.0.

Sorry, I should have said that earlier.

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.

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-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.

Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

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.