HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Why are some aggregates treated differently in the RHS of a rule in the model clause?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
differentlywhytherhsaggregatesaretreatedrulesomeclause

Problem

With my limited understanding of the model clause, I'd expected the following three queries to return the same results because there are no nulls in the data, and the functions are equivalent:

select *
from (select level k, 100 v from dual connect by level<=2)
model return updated rows
dimension by (k)
measures (v, 0 shr)
rules ( shr[any] = v[cv()]/sum(v)[any] );  --plain sum
/*
         K          V        SHR
---------- ---------- ----------
         1        100        0.5 
         2        100        0.5 
*/
select *
from (select level k, 100 v from dual connect by level<=2)
model return updated rows
dimension by (k)
measures (v, 0 shr)
rules ( shr[any] = v[cv()]/nullif(sum(v)[any],0) ); --with nullif
/*
         K          V        SHR
---------- ---------- ----------
         1        100       0.25   <------\___ why?
         2        100       0.25   <------/
*/
select *
from (select level k, 100 v from dual connect by level<=2)
model return updated rows
dimension by (k)
measures (v, 0 shr)
rules ( shr[any] = v[cv()]/decode(sum(v)[any],0,null,sum(v)[any]) );  -- with decode
/*
         K          V        SHR
---------- ---------- ----------
         1        100        0.5 
         2        100        0.5 
*/


What am I missing about the processing of rules that explains this behaviour?

SQLFiddle here

Solution

In the Oracle Database SQL Language Reference one can read


NULLIF(expr1, expr2)
...
The NULLIF function is logically equivalent to the following CASE expression:
CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

but Oracle 11.2.0.3 on Linux gives the following

SQL>select *
2 from (select level k, 100 v from dual connect by level select *
2 from (select level k, 100 v from dual connect by level

Therefor this is (at least a documentation :-) bug in 11r2

One gets the same results in Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

Context

StackExchange Database Administrators Q#48293, answer score: 6

Revisions (0)

No revisions yet.