AirbnbによるオープンソースのBIツールのSuperset
Re:dashもちょろちょろ触っているのですが、
ダッシュボードのカスタマイズが柔軟そうだなー
と思いSupersetの環境を作ってみたのですがデモは動くんだけど、
手元のDBに接続してみるとうまくいかない・・・となりました。
原因
おそらくMySQL5.5.6(以下?)で発生するSTR_TO_DATE
をWHERE句に使うことによるバグ。
MySQL Bugs: #56271: STR_TO_DATE in date compare incorrect results
Supersetが内部でdateのフォーマットを整形した形でSQLを発行しているのですが、
その部分でSTR_TO_DATE
が使われているためだと思われます。
if target_type.upper() in ('DATETIME', 'DATE'): return "STR_TO_DATE('{}', '%Y-%m-%d %H:%i:%s')".format( dttm.strftime('%Y-%m-%d %H:%M:%S')) return "'{}'".format(dttm.strftime('%Y-%m-%d %H:%M:%S'))
superset/db_engine_specs.py at 37fb56c61ceb339079ff117971fc91bdd678db80 · airbnb/superset · GitHub
ifの条件にはまらなければ(DATETIME、DATEのカラムじゃなければ)大丈夫なのかな。
どんなSQLが発行されるか?
こんな。
mysql> SELECT COUNT(*) AS count -> FROM items -> WHERE created_at >= STR_TO_DATE('2000-01-01 00:00:00', '%Y-%m-%d %H:%i:%s') -> AND created_at <= STR_TO_DATE('2017-12-31 23:59:59', '%Y-%m-%d %H:%i:%s');
一見正しそうに見える。
STR_TO_DATE
の結果
mysql> SELECT STR_TO_DATE('2000-01-01 00:00:00', '%Y-%m-%d %H:%i:%s'); +---------------------------------------------------------+ | STR_TO_DATE('2000-01-01 00:00:00', '%Y-%m-%d %H:%i:%s') | +---------------------------------------------------------+ | 2000-01-01 00:00:00 | +---------------------------------------------------------+ mysql> SELECT STR_TO_DATE('2017-12-31 23:59:59', '%Y-%m-%d %H:%i:%s'); +---------------------------------------------------------+ | STR_TO_DATE('2017-12-31 23:59:59', '%Y-%m-%d %H:%i:%s') | +---------------------------------------------------------+ | 2017-12-31 23:59:59 | +---------------------------------------------------------+
たぶん大丈夫。
SQLの結果
mysql> SELECT COUNT(*) AS count -> FROM items -> WHERE created_at >= STR_TO_DATE('2000-01-01 00:00:00', '%Y-%m-%d %H:%i:%s') -> AND created_at <= STR_TO_DATE('2017-12-31 23:59:59', '%Y-%m-%d %H:%i:%s'); +-------+ | count | +-------+ | 0 | +-------+
0件・・・・・
STR_TO_DATE
を使わないで直接指定してみる
mysql> SELECT COUNT(*) AS count -> FROM items -> WHERE created_at >= '2000-01-01 00:00:00' -> AND created_at <= '2017-12-31 23:59:59'; +-------+ | count | +-------+ | 999 | +-------+
カウントがちゃんと取れる・・・・。
MySQLのバージョン確認
mysql> SELECT version(); +--------------+ | version() | +--------------+ | 5.5.6-rc-log | +--------------+
MySQLの別のバージョンで試す
mysql> SELECT version(); +-------------------+ | version() | +-------------------+ | 5.7.12-0ubuntu1.1 | +-------------------+ mysql> SELECT COUNT(*) AS count -> FROM items -> WHERE created_at >= STR_TO_DATE('2000-01-01 00:00:00', '%Y-%m-%d %H:%i:%s') -> AND created_at <= STR_TO_DATE('2017-12-31 23:59:59', '%Y-%m-%d %H:%i:%s'); +-------+ | count | +-------+ | 999 | +-------+ >|abc| mysql> SELECT COUNT(*) AS count -> FROM items -> WHERE created_at >= '2000-01-01 00:00:00' -> AND created_at <= '2017-12-31 23:59:59'; +-------+ | count | +-------+ | 999 | +-------+
どっちも取れる・・・!!!!
MySQLのリリースノートをあさったり
MySQL Bugs: #56271: STR_TO_DATE in date compare incorrect results
これだ!
51 Changes in MySQL 5.5.7 (2010-10-14, Release Candidate)
Comparison of one STR_TO_DATE() result with another could return incorrect results. (Bug #56271)
5.5.7で直ったっぽい!!!!
まとめ
ということで、5.5.7以上を使っていればたぶん大丈夫だと思われます。
5.5.6以下でもうまく行くケースはたぶんある。(TIMESTAMPとかのカラムをTime Columnに設定すればたぶん大丈夫)
strftimeで変換したあともう一度STR_TO_DATE
する必要あるのか・・な・・?