終電23時15分って早くね?

都内のIT企業で働くカラオケ大好きエンジニアの雑記

MySQL5.5.6(以下?)でSupersetがうまく動かない(場合がある)

f:id:blue_goheimochi:20170210100609p:plain

AirbnbによるオープンソースのBIツールのSuperset

github.com

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 5.5 Release Notes

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する必要あるのか・・な・・?