SQL & PL/SQL

I first became aware of this issue while following a different thread - providing this for background only; it is NOT NEEDED to understand my current post.

EDIT:  Sorry, I should always include this information.... ORACLE VERSION:  12.1.0.2.0

CASE expressions are supposed to use short-circuit evaluation. WHEN conditions are evaluated in order; the THEN expression for the first WHEN branch that evaluates to "true" is evaluated and it becomes the value of the CASE expression. Further WHEN conditions and THEN expressions are not supposed to be evaluated (and, in most cases, they aren't).

Consider this table, with two columns of data type NUMBER - column names ID and VAL - and three rows:

create table tbl ( id, val ) as (
  select     level, 5 * power(10, 125)
  from       dual
  connect by level <= 3
);
select * from tbl;
        ID        VAL
---------- ----------
         1   5.0E+125
         2   5.0E+125
         3   5.0E+125

The sum of values in column VAL is 15 * power(10, 125). This value is above the upper limit for the NUMBER data type**, so if I try to compute SUM(VAL) I will get an error. Indeed:

select sum(val) from tbl;
Error starting at line : 20 in command -
select sum(val) from tbl
Error report -
SQL Error: ORA-01426: numeric overflow
01426. 00000 -  "numeric overflow"
*Cause:    Evaluation of an value expression causes an overflow/underflow.
*Action:   Reduce the operands.

(Pay no attention to the line number at the top of the error message; the SELECT statement was on line 20 in my code editor.)

Suppose, however, that I "hide" the SUM within a CASE expression where the SUM is not supposed to be calculated:

select case when 0 = 0 then 0 else sum(val) end
from   tbl
;
Error starting at line : 30 in command -
select case when 0 = 0 then 0 else sum(val) end
from   tbl
Error report -
SQL Error: ORA-01426: numeric overflow
01426. 00000 -  "numeric overflow"
*Cause:    Evaluation of an value expression causes an overflow/underflow.
*Action:   Reduce the operands.

This shouldn't happen. The first WHEN condition evaluates to TRUE, so the expression in the ELSE branch shouldn't be evaluated.

All I can guess is that - in order to save time - Oracle computes the SUM early, while grouping the rows. In this case there is only one group for the entire table; adding GROUP BY NULL, to comply with the SQL Standard, doesn't change anything - and in the thread where I first encountered this, there was actual grouping by a different column, and the same bug existed there.

The same bug exists if we change the CASE expression to    case when 0 = 1 then sum(val) else 0 end   -   here the WHEN condition is FALSE, but the THEN expression is still evaluated (because, as I speculate, Oracle does not evaluate it at the SELECT stage of processing the query; it starts summing while it is fetching each row from the table.)

Is this a known bug? A quick Google search didn't return anything, but perhaps I didn't know what to search for.

-------------------------

** Quick aside about the Documentation. For example here: https://docs.oracle.com/database/121/REFRN/GUID-963C79C9-9303-49FE-8F2D-C8AAF04D3095.htm#REFRN0041

This seems wrong, as my tests show. I believe the correct statement is that the limit is  9.999.... x 10^125,   not  9999.... x 10^125.    Right?