Home » SQL & PL/SQL » SQL & PL/SQL » Why calculated a later invalid statement in a case-when after first true clause found? (PL/SQL Developer 11.0.6.1776 01.108073)
icon11.gif  Why calculated a later invalid statement in a case-when after first true clause found? [message #677818] Mon, 14 October 2019 02:45 Go to next message
Arthurbaa
Messages: 6
Registered: October 2019
Junior Member
Case-When works in a way, theoretically, that after the first True statement none of next clauses will be executed.
Nevertheless, in the following example the else will be tried to calculate in spite that the first then clause was true.

select case when 1=1 then 1
else sum(1/zero) end as eredmeny
from
(select 0 as zero from dual union all
select 0 as zero from dual union all
select 0 as zero from dual) d;

will result in "ORA-01476: Divisior is equal to zero"

The same happens if division by tero is a False Then clause:

select case when 1=1 then 1
when 1=0 then sum(1/zero)
else 2 end as eredmeny
from
(select 0 as zero from dual union all
select 0 as zero from dual union all
select 0 as zero from dual) d;

It seems as if calculation within sum() would be executed even if it is in an inactive part which should not be calculated.
In case division by zero exits sum(), no Error happens:

This code runs correctly:

select case when 1=1 then 1
else sum(1)/zero end as eredmeny
from
(select 0 as zero from dual union all
select 0 as zero from dual union all
select 0 as zero from dual) d
group by zero;

Is it a rule that aggregations will be run even in inactive case-when clauses, if they should have no impact on result?
Re: Why calculated a later invalid statement in a case-when after first true clause found? [message #677819 is a reply to message #677818] Mon, 14 October 2019 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 66725
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Aggregations are computed in a step before than the expressions in the select clause are.

[Updated on: Mon, 14 October 2019 10:46]

Report message to a moderator

Re: Why calculated a later invalid statement in a case-when after first true clause found? [message #677825 is a reply to message #677819] Mon, 14 October 2019 07:57 Go to previous messageGo to next message
Arthurbaa
Messages: 6
Registered: October 2019
Junior Member
Oh, thank you!
And do you know that or is this a mere suspicion?
And is this a side-effect, which may be planned to correct by Oracle or is this the official and intended way of operation.
As it seems to be even a very underoptimised way of calculations if every single aggregation will be calculated even if not needed. Even if it does not cause unnecessary Errors in run, but in the above case it even modifies result to the extent that logically correct calculations cannot run as stop with unnecessary error!
So do you know whether it is the intended operation for far future or will be corrected within a time?
Thank you for possible answer,
Arthurbaa
Re: Why calculated a later invalid statement in a case-when after first true clause found? [message #677828 is a reply to message #677825] Mon, 14 October 2019 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 66725
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
And do you know that or is this a mere suspicion?

Here's the execution plan for the first query:
SQL> select case when 1=1 then 1
  2  else sum(1/zero) end as eredmeny
  3  from
  4  (select 0 as zero from dual union all
  5  select 0 as zero from dual union all
  6  select 0 as zero from dual) d;
else sum(1/zero) end as eredmeny
          *
ERROR at line 2:
ORA-01476: divisor is equal to zero


SQL> select * from table(dbms_xplan.display_cursor(format=>'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID  2k9f3tb3jj5ud, child number 0
-------------------------------------
select case when 1=1 then 1 else sum(1/zero) end as eredmeny from
(select 0 as zero from dual union all select 0 as zero from dual union
all select 0 as zero from dual) d

Plan hash value: 2108775075

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     6 (100)|          |
|   1 |  SORT AGGREGATE  |      |     1 |     2 |            |          |
|   2 |   VIEW           |      |     3 |     6 |     6   (0)| 00:00:01 |
|   3 |    UNION-ALL     |      |       |       |            |          |
|   4 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   5 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   6 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) SUM(1/"ZERO")[22]
   2 - "ZERO"[NUMBER,1]
   3 - STRDEF[1]
As you can see (I removed the "Query Block Name / Object Alias" part which is irrelevant here), you have 3 accesses to DUAL table combined by a UNION-ALL to create an in-memory VIEW then you have the SORT AGGREGATE operation which also computes the aggregation expression(s) as you can see in last part: "1 - ... SUM(1/"ZERO")..." which means that at operation with id 1 this expression is computed. In the end, you have the SELECT operation which computes the selected expressions.
The computation of aggregation expressions are easier to see in this simple case:
SQL> select count(empno), sum(sal), avg(comm), max(mgr) from emp;
COUNT(EMPNO)   SUM(SAL)  AVG(COMM)   MAX(MGR)
------------ ---------- ---------- ----------
          14      29025        550       7902

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(format=>'all'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  6z986dag1mjxt, child number 0
-------------------------------------
select count(empno), sum(sal), avg(comm), max(mgr) from emp

Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    10 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT("COMM")[22], COUNT(*)[22], SUM("COMM")[22],
       SUM("SAL")[22], MAX("MGR")[22]
   2 - "MGR"[NUMBER,22], "SAL"[NUMBER,22], "COMM"[NUMBER,22]

Quote:
And is this a side-effect, which may be planned to correct by Oracle or is this the official and intended way of operation.

The later is correct, this is how it has always worked.

[Updated on: Mon, 14 October 2019 10:43]

Report message to a moderator

Re: Why calculated a later invalid statement in a case-when after first true clause found? [message #677829 is a reply to message #677828] Mon, 14 October 2019 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 66725
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And to answer your other point:

Quote:
As it seems to be even a very underoptimised way of calculations if every single aggregation will be calculated even if not needed.

Your case is very simple but imagine there are billions of rows and you aggregate them in hundreds of groups (GROUP BY) with many aggregate expressions (like in my last query), it is much faster to compute all the aggregate expressions while you are scanning the table(s) and building the groups than at a later step to scan again the interested rows and groups; you will then do twice the scan work (without speaking about the temporary space you need to have to store these interested rows). Scanning is slow, computing is fast.

Re: Why calculated a later invalid statement in a case-when after first true clause found? [message #677836 is a reply to message #677829] Tue, 15 October 2019 02:41 Go to previous messageGo to next message
Arthurbaa
Messages: 6
Registered: October 2019
Junior Member
Well, I see, thank you!
My original query was also on real big table, the example contained dual tables for sake of simplicity.
So I see, and also, why is it so.
Nevertheless, a solution could be if there would be a possibility for "Error" as a temporary value in value-set in aggregation results, and a stop with error would happen only if a such error value would become "active" in the select part.
Unfortunately, division by zero is an often possibility in codes that can be avoided by a check like case when in the example, and it can cause trouble if exactly this possibility cannot avoid division by zero.
Re: Why calculated a later invalid statement in a case-when after first true clause found? [message #677837 is a reply to message #677836] Tue, 15 October 2019 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 66725
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
a solution could be if there would be a possibility for "Error" as a temporary value in value-set in aggregation results
I agree.


Quote:
Unfortunately, division by zero is an often possibility in codes that can be avoided by a check like case when in the example, and it can cause trouble if exactly this possibility cannot avoid division by zero.

My scripts, when there is a division and a possibly 0 divisor, always contain something like "decode(zero, 0,to_number(NULL) /* or any relevant value */, 1/zero)" instead of "1/zero" to avoid such problem.

Re: Why calculated a later invalid statement in a case-when after first true clause found? [message #677838 is a reply to message #677837] Tue, 15 October 2019 04:00 Go to previous messageGo to next message
Arthurbaa
Messages: 6
Registered: October 2019
Junior Member
Well, my solution was also like .../decode(zero,0,1,zero), because its real value was irrelevant if zero=0, but division by zero was simply avoidable.
If one knows about this strange feature, it is really not so bad, but finding the cause needed hours. Twice, because it happened once earlier with me, and then I was not able to find the reason, and then another person has met the same problem, and further hours resulted in the suspicion...

Thank you for the answers!
Re: Why calculated a later invalid statement in a case-when after first true clause found? [message #677841 is a reply to message #677829] Tue, 15 October 2019 08:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2901
Registered: January 2010
Location: Connecticut, USA
Senior Member
And it is still a bug until it is documented as short-circuit rule exception.

SY.
Re: Why calculated a later invalid statement in a case-when after first true clause found? [message #677843 is a reply to message #677841] Tue, 15 October 2019 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 66725
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Bug or not a bug?

One can raise a SR with this simple test case (I failed to find if one already exists in MOS):
SQL> select decode(1,1,1,sum(1/0)) from dual;
select decode(1,1,1,sum(1/0)) from dual
                         *
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> select case when 1=1 then 1 else sum(1/0) end from dual;
select case when 1=1 then 1 else sum(1/0) end from dual
                                      *
ERROR at line 1:
ORA-01476: divisor is equal to zero
Re: Why calculated a later invalid statement in a case-when after first true clause found? [message #677845 is a reply to message #677843] Tue, 15 October 2019 10:32 Go to previous messageGo to next message
Arthurbaa
Messages: 6
Registered: October 2019
Junior Member
What is a SR?
Re: Why calculated a later invalid statement in a case-when after first true clause found? [message #677846 is a reply to message #677845] Tue, 15 October 2019 10:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2901
Registered: January 2010
Location: Connecticut, USA
Senior Member
Service Request you open with Oracle Support.

SY.
Re: Why calculated a later invalid statement in a case-when after first true clause found? [message #677847 is a reply to message #677846] Tue, 15 October 2019 10:39 Go to previous messageGo to next message
Arthurbaa
Messages: 6
Registered: October 2019
Junior Member
Oh, it would be nice, but I do not know how to do this...

It was a nice task for me to find at least one forum on internet where I can ask a question.

This here is my second try, the first one was on a forum, where, I think, I may have posted it in a wrong way, so no answer happened.
Re: Why calculated a later invalid statement in a case-when after first true clause found? [message #677849 is a reply to message #677847] Tue, 15 October 2019 11:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2901
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, your company needs to purchase Oracle Support before you can create SR.

SY.
Re: Why calculated a later invalid statement in a case-when after first true clause found? [message #677851 is a reply to message #677847] Tue, 15 October 2019 11:58 Go to previous message
Michel Cadot
Messages: 66725
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Oh, it would be nice, but I do not know how to do this...

Click on MOS and you are on our wiki page on this subject containing the basic information about it.
As Solomon said, your company needs a support contract with Oracle to have access to it.

Previous Topic: Oracle - path passing by multiple nodes
Next Topic: Syhthesizing
Goto Forum:
  


Current Time: Thu Dec 12 16:09:26 CST 2019