結果がおかしいSQL集(Oracle11g Express Edition)

昔、US-OTNに投稿した
Report of bugs like results of SQL in Oracle11gXE.
https://community.oracle.com/thread/2223666
のOracle Database 11g Express Edition Release 11.2.0.2.0
で再現したものを集めて、日本語に翻訳してみました。
12cとか18cでも再現するかもしれません。

01 nth_Valueの結果がおかしい

with t(empno,sal,rn) as(
select 101, 720,1 from dual union
select 102, 850,2 from dual union
select 103,1250,3 from dual union
select 105,1700,4 from dual)
select
nth_Value(empno,rn) over(order by empno
Rows between Unbounded Preceding
         and Unbounded Following) as emp,
nth_Value(sal,rn) over(order by sal
Rows between Unbounded Preceding
         and Unbounded Following) as sal
from t;

| emp | sal |
|-----|-----|
| 101 | 720 |
| 101 | 720 |
| 101 | 720 |
| 101 | 720 |

正しい結果は下記です。
| emp |  sal |
|-----|------|
| 101 |  720 |
| 102 |  850 |
| 103 | 1250 |
| 104 | 1700 |

02 再帰With句で日付がインクリメントされない

with rec(dayc,LV) as(
select cast(date '2010-04-15' as date),1 from dual
union all
select cast(dayc+1 as date),LV+1
  from rec
 where LV<= 3)
select * from rec;

|                 dayc | LV |
|----------------------|----|
| 2010-04-15T00:00:00Z |  1 |
| 2010-04-14T00:00:00Z |  2 |
| 2010-04-13T00:00:00Z |  3 |
| 2010-04-12T00:00:00Z |  4 |

03 再帰With句で、InterVal型のSumが取得できない

with sumInter(Val) as(
select interVal '40' minute from dual union all
select interVal '15' minute from dual),
work(Rn,Val,recCnt) as(
select RowNum,Val,Count(*) over() from sumInter),
rec(Rn,Val,recCnt) as(
select Rn,Val,recCnt
  from work
 where Rn = 1
union all
select b.Rn,a.Val+b.Val,a.recCnt
  from rec a,work b
 where a.Rn+1 = b.Rn)
select * from rec;

| Rn |        Val | recCnt |
|----|------------|--------|
|  1 | 0 0:40:0.0 |      2 |
|  2 | 0 0:25:0.0 |      2 |

正しい結果は下記です。
| Rn |        Val | recCnt |
|----|------------|--------|
|  1 | 0 0:40:0.0 |      2 |
|  2 | 0 0:55:0.0 |      2 |

04 With句で、grouping setsが動作しない

with c(dayc,Val) as(
select date '2010-06-01', 10 from dual union
select date '2010-07-02', 20 from dual)
select sum(val) as Val
from (select to_char(dayc,'yyyy-mm-dd') as dayc,
      to_char(dayc,'mm') as mon,val
      from c)
group by grouping sets((),mon,dayc);

ORA-00904: "SYS_TBL_$1$"."VAL": invalid identifier

古いWith句の書き方だと動きます。
with c as(
select date '2010-06-01' dayc, 10 Val from dual union
select date '2010-07-02', 20 from dual)
select sum(val) as Val
from (select to_char(dayc,'yyyy-mm-dd') as dayc,
      to_char(dayc,'mm') as mon,val
      from c)
group by grouping sets((),mon,dayc);

| Val |
|-----|
|  10 |
|  20 |
|  30 |
|  10 |
|  20 |

05 With句経由のCube集計で、grouping関数の結果がおかしい

with cubeT2(ColA,ColB,ColC) as(
select 1,1,1 from dual)
select grouping_ID(ColA,ColB,ColC) as res1,
 grouping(ColA)*4
+grouping(ColB)*2
+grouping(ColC)*1 as res2
  from cubeT2
group by cube(ColA,ColB,ColC);

| res1 | res2 |
|------|------|
|    0 |    0 |
|    0 |    0 |
|    0 |    0 |
|    0 |    0 |
|    0 |    0 |
|    0 |    0 |
|    0 |    0 |
|    7 |    7 |

With句ではなく、CreateTableしたテーブルを使うと違う結果になります。

create table cubeT(ColA,ColB,ColC) as
select 1,1,1 from dual;

select grouping_ID(ColA,ColB,ColC) as res1,
 grouping(ColA)*4
+grouping(ColB)*2
+grouping(ColC)*1 as res2
  from cubeT
group by cube(ColA,ColB,ColC);

| res1 | res2 |
|------|------|
|    7 |    7 |
|    6 |    6 |
|    5 |    5 |
|    4 |    4 |
|    3 |    3 |
|    2 |    2 |
|    1 |    1 |
|    0 |    0 |

06 FetchFirstを使うと式が再評価される

番外編で、
If InLineView Has RandValue, FetchFirst does not work correct.
https://community.oracle.com/thread/3961531
を日本語に翻訳してみました。

select Val
from (select mod(dbms_random.random(),123) as Val
        from dict)
where Val >= 25
  and RowNum <= 5;

Val
---
 29
118
104
 64
 39

12c新機能のFetch Firstを使用すると、25以上でないValが出力されてしまいます。

select Val
from (select mod(dbms_random.random(),123) as Val
        from dict)
where Val >= 25
Fetch First 5 rows Only;

 Val
----
  -6
 -83
 -77
-108
 -17