やりたいこと

あるログデータについて取得時間順に並べた場合、各レコードについて自身のレコードの一つ前のログデータの時間をを取得し、それを一つ前のログデータ取得時間用カラムへ登録したい。

具体的には、以下のテーブル(1)を(2)のように更新したい。

-- ログテーブル構成(最小限のカラムのみ)
create table example (
  col_dt     datetime not null,     -- ログ取得時間
  col_dt_prev datetime default null -- ひとつ前のログ取得時間
)
;

insert into example (col_dt) values 
('2017-12-01 12:00:00'), 
('2017-12-01 14:00:00'), 
('2017-12-01 15:00:00'), 
('2017-12-01 17:00:00')
;

-- col_dt, dol_dt_prevはdatetime型
-- (1) 

|---------------------+-------------+
| col_dt              | col_dt_prev |
|---------------------+-------------+
| 2017-12-01 12:00:00 | NULL        |
| 2017-12-01 14:00:00 | NULL        |
| 2017-12-01 15:00:00 | NULL        |
| 2017-12-01 17:00:00 | NULL        |
~---------------------+-------------+

-- (2)
+---------------------+---------------------+
| col_dt              | col_dt_prev         |
+---------------------+---------------------+
| 2017-12-01 12:00:00 | NULL                |
| 2017-12-01 14:00:00 | 2017-12-01 12:00:00 |
| 2017-12-01 15:00:00 | 2017-12-01 14:00:00 |
| 2017-12-01 17:00:00 | 2017-12-01 15:00:00 |
+---------------------+---------------------+

環境

環境要素	バージョンなど
DBMS	MariaDB 10.2.6

発生した事象

まず、問題なく求めていた結果が得られるかの確認のためSELECT文では、正しい結果が得られました。

-- SELECTで結果を確認
select
  col_dt
 ,lag(col_dt) over (order by col_dt) as col_dt_prev
from example
;

+---------------------+---------------------+
| col_dt              | col_dt_prev         |
+---------------------+---------------------+
| 2017-12-01 12:00:00 | NULL                |
| 2017-12-01 14:00:00 | 2017-12-01 12:00:00 |
| 2017-12-01 15:00:00 | 2017-12-01 14:00:00 |
| 2017-12-01 17:00:00 | 2017-12-01 15:00:00 |
+---------------------+---------------------+

しかし、テーブルを更新しようと同じ条件でアップデート文を実行したところ、以下のエラーが発生

-- UPDATEで更新
update example ex, (
    select
         col_dt
        ,lag(col_dt) over (order by col_dt) as col_dt_prev
    from example
) it
set ex.col_dt_prev = it.col_dt_prev
where ex.col_dt = it.col_dt
;

実行結果

ERROR 1292 (22007): Incorrect datetime value: '' for column 'col_dt_prev' at row 1

insert文でも同様のエラーが発生する。

分かったことをまとめると、

select文だけではエラーが発生しない。
update文, insert文とselect文を併用した場合にlag関数でエラーが発生する。

回避策

一度、Unix Timestampへ変換してから、lag関数を適用して、再度datetime型へ戻す。以下がそのSQLです。

-- UPDATEで更新
update example ex, (
    select
       col_dt
       -- datetime -> unix timestamp -> datetime
      ,from_unixtime(lag(unix_timestamp(col_dt)) over (order by col_dt)) as col_dt_prev
    from example
) it
set ex.col_dt_prev = it.col_dt_prev
where ex.col_dt = it.col_dt
;

原因は不明で根本的な解決策ではありませんが、上記の対応でエラーが発生しなくなりました。

(2017/12/15 追記)
コメントでアドバイスをいただきました。
mariadbのバージョン 10.2.11　では発生しませんでしたので、そちらへバージョンアップを行えば解決します。

Window function の lag関数で発生したエラーとその回避策

やりたいこと

環境

発生した事象

回避策