結果がおかしい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