オラクルSQLの初見殺しをクイズ形式にしてみました。
基本初心者向けですが、オラクルに慣れた人でももしかしたら「ん?」てなるかもしれません。
5問だけなので暇つぶしにでもどうぞ。
問題
テストデータ
第1,2,3問で使用します。テーブルには以下の2行が格納されています。
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
第1問
以下のSQLで、最初のクエリで正しく2行返されるのに対して2番目のクエリで行が返されない理由を述べよ。
> 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
第2問
以下のSQLで、最初のクエリが示すとおりカラムVSTR
において'BBB'でもNULLでもない行が存在しているにもかかわらず、二番目のクエリで行が返されない理由を述べよ。
> SELECT vstr FROM test_tbl WHERE vstr = 'AAA';
VSTR
----------
AAA
> SELECT * FROM test_tbl WHERE vstr NOT IN ( 'BBB', NULL );
no rows selected
第3問
以下のSQLで、最初のクエリでカラムVSTR
およびCSTR
が共に'AAA'
であるという条件で行が返されるにもかかわらず、二番目のクエリのVSTR = CSTR
で行が返されない理由を述べよ。
> 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
第4問
以下のクエリのQ'Q'Q'Q
がシングルクォート'
のみを返す理由を述べよ。
> SELECT Q'Q'Q'Q FROM DUAL;
Q
-
'
第5問
オラクルのDATE型では、1日を数値の1
、1秒を0.000011574...
(1/24/60/60)として計算することができることはよく知られている。以下のSQLにおいて、最初のクエリでBETWEEN条件に1未満の数値である0.999942
を加算した場合は次の日は含まれないが、二番目のクエリで0.999943
を加算した場合に次の日が含まれてしまう理由を述べよ。
> 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
解答
以下解答です。
|
|
|
|
|
|
|
|
第1問
ROWNUM
での出力制限は一行目から連続している場合のみ有効です。例えば、ROWNUM >= 1
は有効ですがROWNUM > 1
は(一行目を出力する条件が他にないかぎり)無効となります。またROWNUM <= 1 OR ROWNUM >= 3
の様に一行目からの連続が途切れている場合は、一行目から途切れているところまでが有効となります。
第2問
NOT IN句の中にNULLがあるとどの様な場合でも偽となります。これは3値論理演算の結果であり、オラクルに限った動作ではなく他の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
なため結果は同じです。詳しくはオラクルの3値論理算を参照してください。
第3問
CSTRがCHAR
型のカラムであることがミソです。CHAR
型はVARCHAR2
型と異なり可変長ではありません。値は常に指定された長さの文字列で格納されます。CSTRカラムの文字列長は10なので、'AAA'
は、後ろに7文字分スペースのついた'AAA_______'
(_はスペース)として格納されています。そして、ここがややこしいところですが、CHAR
型のカラムに対してリテラル値で検索した場合、オラクルは暗黙的に文字列にスペースを付加して正しい文字列長で比較します。つまり、CSTR = 'AAA'
は、CSTR = 'AAA_______'
として実行されているわけです。ところが、リテラルでないカラム同士の比較の場合はこのスペースの付加は行われません。これが、VSTR = 'AAA' AND CSTR = 'AAA'
が真であるのに、VSTR = CSTR
が偽である理由です。
第4問
ちょっと意地悪な問題ですが、種を明かせばただの文字列リテラルのQ
です。Q’<marker>文字列<marker>'
という書式ですね。マーカーは同じ文字であればなんでも構わないためここでは、Q
を使用しています(ただしマーカーに[]、()、<>
等のカッコを使用した場合は開きカッコ綴じカッコのペアを使用します)。つまり、Q'Q'Q'Q
の最初のQ
は文字列リテラルの宣言、2つ目のQ
は開始マーカー、3つ目のQ
は終了マーカー、最後のQ
はASの省略されたカラムのエイリアス(別名)です。オラクルはシングルクオートの直後ならスペースなしでもエイリアス記述ができます。
したがって、文字列リテラルで指定された文字列は、真ん中のシングルクオート'
のみということになります。
第5問
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秒) の加算で時刻が変わります
-- 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 -- 1秒カウント
ついでにいうとオラクルのDATE型は内部的には数値での計算と全く関連のないフォーマット(TYPE 12および13)で保存されています。つまり、わざわざそういう計算方法を選択しているということなのですが一般的な時刻の感覚とはズレてる気がします。
おしまい