1 2 Previous Next 27 Replies Latest reply on Nov 14, 2017 7:54 PM by Solomon Yakobson Go to original post
      • 15. Re: Oracle bug? Aggregates in CASE expressions negate short-circuit evaluation.
        mathguy

        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.

          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

            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.

              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

                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

                  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

                    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

                      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.

                        What!?

                         

                        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.

                        1 person found this helpful
                        • 24. Re: Oracle bug? Aggregates in CASE expressions negate short-circuit evaluation.
                          mathguy

                          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

                            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

                              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

                                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.

                                1 2 Previous Next