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)
|
|
|
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 |
|
Michel Cadot
Messages: 68658 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 #677836 is a reply to message #677829] |
Tue, 15 October 2019 02:41 |
|
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 |
|
Michel Cadot
Messages: 68658 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 |
|
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!
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 15 15:51:55 CDT 2024
|