オラクルSQL初見殺しクイズ ５問

オラクルSQLの初見殺しをクイズ形式にしてみました。
基本初心者向けですが、オラクルに慣れた人でももしかしたら「ん？」てなるかもしれません。
５問だけなので暇つぶしにでもどうぞ。

# 問題

## テストデータ

第1,2,3問で使用します。テーブルには以下の２行が格納されています。

```sql:テストデータ作成
CREATE TABLE test_tbl
  (
     n    NUMBER,
     vstr VARCHAR2(10),
     cstr CHAR(10)
  );

INSERT INTO test_tbl VALUES (1, 'AAA', 'AAA');
INSERT INTO test_tbl VALUES (2, 'XXX', 'XXX');
COMMIT;

SELECT * FROM test_tbl;

         N VSTR       CSTR
---------- ---------- ----------
         1 AAA        AAA
         2 XXX        XXX
```


## 第１問

以下のSQLで、最初のクエリで正しく２行返されるのに対して２番目のクエリで行が返されない理由を述べよ。

```sql:問題1
> SELECT * FROM test_tbl WHERE ROWNUM = 2 OR ROWNUM = 1;

         N VSTR       CSTR
---------- ---------- ----------
         1 AAA        AAA
         2 XXX        XXX


> SELECT * FROM test_tbl WHERE ROWNUM = 2;

no rows selected
```

## 第２問

以下のSQLで、最初のクエリが示すとおりカラム``VSTR``において'BBB'でもNULLでもない行が存在しているにもかかわらず、二番目のクエリで行が返されない理由を述べよ。

```sql:問題2
> SELECT vstr FROM test_tbl WHERE vstr = 'AAA';

VSTR
----------
AAA


> SELECT * FROM test_tbl WHERE vstr NOT IN ( 'BBB', NULL );

no rows selected
```


## 第３問

以下のSQLで、最初のクエリでカラム``VSTR``および``CSTR``が共に``'AAA'``であるという条件で行が返されるにもかかわらず、二番目のクエリの``VSTR = CSTR``で行が返されない理由を述べよ。

```sql:問題3
> SELECT * FROM test_tbl WHERE vstr = 'AAA' AND cstr = 'AAA';

         N VSTR       CSTR
---------- ---------- ----------
         1 AAA        AAA


> SELECT * FROM test_tbl WHERE vstr = cstr;

no rows selected
```


## 第４問

以下のクエリの``Q'Q'Q'Q``がシングルクォート``'``のみを返す理由を述べよ。

```sql:問題4
> SELECT Q'Q'Q'Q FROM DUAL;

Q
-
'
```

## 第５問

オラクルのDATE型では、1日を数値の``1``、1秒を``0.000011574...``(1/24/60/60)として計算することができることはよく知られている。以下のSQLにおいて、最初のクエリでBETWEEN条件に1未満の数値である``0.999942``を加算した場合は次の日は含まれないが、二番目のクエリで``0.999943``を加算した場合に次の日が含まれてしまう理由を述べよ。

```sql:問題５
> SELECT 'true' answer FROM DUAL
  WHERE  DATE'2019-1-2' 
             BETWEEN DATE'2019-1-1' AND 
                     DATE'2019-1-1' + 0.9999942;

no rows selected


> SELECT 'true' answer FROM DUAL
  WHERE  DATE'2019-1-2' 
             BETWEEN DATE'2019-1-1' AND 
                     DATE'2019-1-1' + 0.9999943;

ANSWER
------
true
```

# 解答

以下解答です。
|
|
|
|
|
|
|
|

## 第１問

``ROWNUM``での出力制限は一行目から連続している場合のみ有効です。例えば、``ROWNUM >= 1``は有効ですが``ROWNUM > 1``は(一行目を出力する条件が他にないかぎり)無効となります。また``ROWNUM <= 1 OR ROWNUM >= 3``の様に一行目からの連続が途切れている場合は、一行目から途切れているところまでが有効となります。

## 第２問

NOT IN句の中にNULLがあるとどの様な場合でも偽となります。これは３値論理演算の結果であり、オラクルに限った動作ではなく他のDBでも同様です。

``VSTR NOT IN ( 'BBB', NULL )``は、AND/ORで置換すると「``VSTR != 'BBB' AND VSTR != NULL``」となり、後者は常に``UNKNOWN``です。AND演算では``UNKNOWN``は真に優先されるため演算結果は常に``UNKNOWN``または偽となり、行は返されません。ちなみにOR演算子をつかって「``NOT (VSTR != 'BBB' OR VSTR != NULL)``」と置換しても「``VSTR != 'BBB'``」が、真であればNOT演算子で偽となり、偽であれば後者の``UNKNOWN``が優先されかつ``NOT UNKNOWN = UNKNOWN``なため結果は同じです。詳しくはオラクルの３値論理算を参照してください。

## 第３問

CSTRが``CHAR``型のカラムであることがミソです。``CHAR``型は``VARCHAR2``型と異なり可変長ではありません。値は常に指定された長さの文字列で格納されます。CSTRカラムの文字列長は１０なので、``'AAA'``は、後ろに７文字分スペースのついた``'AAA_______'``(＿はスペース)として格納されています。そして、ここがややこしいところですが、``CHAR``型のカラムに対してリテラル値で検索した場合、オラクルは暗黙的に文字列にスペースを付加して正しい文字列長で比較します。つまり、``CSTR = 'AAA'``は、``CSTR = 'AAA_______'``として実行されているわけです。ところが、リテラルでないカラム同士の比較の場合はこのスペースの付加は行われません。これが、``VSTR = 'AAA' AND CSTR = 'AAA'``が真であるのに、``VSTR = CSTR``が偽である理由です。

## 第４問

ちょっと意地悪な問題ですが、種を明かせばただの文字列リテラルの``Q``です。``Q’<marker>文字列<marker>'``という書式ですね。マーカーは同じ文字であればなんでも構わないためここでは、``Q``を使用しています（ただしマーカーに``[]、()、<>``等のカッコを使用した場合は開きカッコ綴じカッコのペアを使用します）。つまり、``Q'Q'Q'Q``の最初の``Q``は文字列リテラルの宣言、２つ目の``Q``は開始マーカー、３つ目の``Q``は終了マーカー、最後の``Q``はASの省略されたカラムのエイリアス（別名）です。オラクルはシングルクオートの直後ならスペースなしでもエイリアス記述ができます。
したがって、文字列リテラルで指定された文字列は、真ん中のシングルクオート``'``のみということになります。


## 第５問

DATE型を数値で計算する場合ですが、ある時刻から開始して0.999..秒までがその時刻で1秒を超えたときに次の時刻になる**わけではなく**、計算上ある時刻の±0.5秒がその時刻に含まれ、0.5+秒を加算または減算すれば異なる時刻になる実装の「ようです」（マニュアル等を発見できませんでした）。

結果、``0.9999942``は23時59分59秒＋0.5秒未満で収まっているためその日内の条件となりますが、``0.9999943``だと＋0.5秒を超えるためカウントアップし次の日も含まれる条件になっているというわけです。

ちなみにこれは数値の精度を上げてもその通りに動くので、誤差ではなくオラクルが意図的にやっていることなのでしょう。たとえば以下のように20桁精度でも、 0.0000057870370370370 **3** (0.4999999999999993秒) の加算では時刻が変わらず、0.0000057870370370370 **4** (0.5000000000000002秒) の加算で時刻が変わります

```sql:
-- 0.4999999999999993秒加算
> SELECT TO_CHAR(DATE'2019-1-1' + 0.00000578703703703703,
                'YYYY-MM-DD HH24:MI:SS') time FROM DUAL;

TIME
-------------------
2019-01-01 00:00:00   -- そのまま

-- 0.5000000000000002秒加算
> SELECT TO_CHAR(DATE'2019-1-1' + 0.00000578703703703704,
                'YYYY-MM-DD HH24:MI:SS') time FROM DUAL;

TIME
-------------------
2019-01-01 00:00:01   -- １秒カウント
```

ついでにいうとオラクルのDATE型は内部的には数値での計算と全く関連のないフォーマット(TYPE 12および13)で保存されています。つまり、わざわざそういう計算方法を選択しているということなのですが一般的な時刻の感覚とはズレてる気がします。


おしまい