LoginSignup
2

More than 5 years have passed since last update.

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

Posted at

オラクル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番目のクエリで行が返されない理由を述べよ。

問題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

第2問

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

問題2
> 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で行が返されない理由を述べよ。

問題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

第4問

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

問題4
> 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を加算した場合に次の日が含まれてしまう理由を述べよ。

問題5
> 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)で保存されています。つまり、わざわざそういう計算方法を選択しているということなのですが一般的な時刻の感覚とはズレてる気がします。

おしまい

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2