日付と時刻を分けて比較してはダメ

はじめに

改善作業でテストをしていたところ、エラーになるべきところがすり抜けてしまった。
要件としては、処理を実行した際に登録したテーブルデータが、別テーブルデータより後で登録されている必要があり、それ以外ならエラーとするというもの。

後で登録されているかは更新日時を抽出条件とする。

原因

各テーブルの更新日時は、varchar2型で更新日付(yyyyMMdd)と更新時間(HHmmss)と分けた設計がされている。※改修作業なので文句は言えぬ。

以下のように日付と時刻をそれぞれ分けて比較していたため、翌日の午前中に処理を実行したことでUPDATE_TIMEの値が条件を満たさずエラーにならなかった。

例

-- TESTデータ
UPDATE_DATE:20180530 
UPDATE_TIME:103015
 
-- 抽出SQL
SELECT * FROM TEST
WHERE UPDATE_DATE >= '20180529' AND UPDATE_TIME >= '172010'

対応

日付と時刻を結合した上で比較するようにした。

例

SELECT * FROM TEST
WHERE UPDATE_DATE || UPDATE_TIME >= '20180529' || '172010'

インデックスを効くようにする

【2019/02/27追記】
現在、OracleからPostgreSQLに移行作業中で性能検証をしている。
あるアプリケーションで大量データのテーブルに日時を条件にして抽出する処理があるが、PostgreSQLでは遅いことが分かった。
Oracleでは日時をダブルパイプ結合したインデックスを作成しており、これが効いているため速度上は問題がなかった。

試行錯誤した結果、UPDATE_DATEのみのインデックスを作成し、BETWEENの条件を追加することでインデックスが効くようになった。

例

SELECT * FROM TEST
WHERE 
UPDATE_DATE || UPDATE_TIME >  '20190218141435' AND
UPDATE_DATE || UPDATE_TIME <= '20190219141435' AND
UPDATE_DATE BETWEEN '20190218' AND '20190219'

PostgreSQL専用なら行値式

以前もらったコメントでこのような場合には行値式が使えることが分かりました。
UPDATE_DATEとUPDATE_TIMEでインデックスを作成すれば、インデックスが効くようになります。

例

SELECT * FROM TEST
WHERE 
(UPDATE_DATE, UPDATE_TIME) >  ('20190218','141435') AND
(UPDATE_DATE, UPDATE_TIME) <= ('20190219','141435')

Oracle でも行値式を使用できるのですが等価(=、!=、IN演算子)のみで、> や <= といった範囲条件演算子は使用できません。よって、「ORA-01796: リストではこの演算子は使用できません。」のエラーになってしまいます。

SQLServerは未だに行値式をサポートしていません。

最後に

別問題で悩んでいたためテスト作業を中途半端に残した状態で、翌日にテスト作業をしたことで見つかった。
そのままやっていたら、気がつかないままテスト完了しリリース後にバグの報告を受けていたかもしれない。

そもそもコーディングレビューすれば指摘された可能性もある、一人作業なので甘い部分があった。
こんなことにコーディング時に気が付かなくなっているようではダメなんだよね。
日付跨いだテスト項目を入れてないとダメだったかもと反省ばかり。