patternMinor
Why are some aggregates treated differently in the RHS of a rule in the model clause?
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:
What am I missing about the processing of rules that explains this behaviour?
SQLFiddle here
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
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.