MySQLのJSON Data Typeの値に対し、明示的なキャスト無しに `BETWEEN`, `IN()`, `GREATEST()`, `LEAST()` を使ってはならない
表題の通り、MySQLのJSON Data Typeの値に対しては、明示的なキャスト無しに BETWEEN
, IN()
, GREATEST()
そして LEAST()
を使ってはいけません。
MySQLむずかしい pic.twitter.com/YKoadLbaG2
— すぎゃーん💯 (@sugyan) 2021年9月7日
本記事はこれに係る話題で、id:sugyan さんに Slack で相談を受けて「僕もそれハマったことあるな」と調べたところ以下のドキュメントに辿りつきました。
これはMySQL 8.0のJSON Data Typeに関するドキュメントですが、このドキュメントの Comparison and Ordering of JSON Values
というセクションに
The following comparison operators and functions are not yet supported with JSON values:
- BETWEEN
- IN()
- GREATEST()
- LEAST()
A workaround for the comparison operators and functions just listed is to cast JSON values to a native MySQL numeric or string data type so they have a consistent non-JSON scalar type.
と明記されています。これらの演算子・関数については「未対応」ということみたいですね。
これがどういうことか実例を見てみましょう。
前提として、JSON_EXTRACT()
を使って取り出した値はその値自体がJSON Data Typeとして扱われます。
mysql> SELECT JSON_TYPE(JSON_EXTRACT('{"a":101,"b":99}', '$.a')); +----------------------------------------------------+ | JSON_TYPE(JSON_EXTRACT('{"a":101,"b":99}', '$.a')) | +----------------------------------------------------+ | INTEGER | +----------------------------------------------------+ 1 row in set (0.00 sec)
なおこの反例として、仮にこの値をキャストしてみると JSON_TYPE()
はエラーを返却します。
mysql> SELECT JSON_TYPE(CAST(JSON_EXTRACT('{"a":101,"b":99}', '$.b') AS SIGNED INTEGER)); ERROR 3146 (22032): Invalid data type for JSON data in argument 1 to function json_type; a JSON string or JSON type is required.
さて、このJSON Data Typeの値について単純な比較演算子を使って数値比較をしてみましょう。比較演算子についてドキュメントには
JSON values can be compared using the =, <, <=, >, >=, <>, !=, and <=> operators.
とあるので正常に動きそうです。
mysql> SELECT JSON_EXTRACT('{"a":101,"b":99}', '$.a') > JSON_EXTRACT('{"a":101,"b":99}', '$.b'); +-----------------------------------------------------------------------------------+ | JSON_EXTRACT('{"a":101,"b":99}', '$.a') > JSON_EXTRACT('{"a":101,"b":99}', '$.b') | +-----------------------------------------------------------------------------------+ | 1 | +-----------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
このSQLはJSON Data TypeがINTEGER
の値である 101
と 99
について比較したもの、すなわち 101 > 99
ですが、1
が返却されているので正しく動作していそうです。
一方で同じ値に対し GREATEST()
を利用するとどうなるでしょうか。
mysql> SELECT GREATEST(JSON_EXTRACT('{"a":101,"b":99}', '$.a'), JSON_EXTRACT('{"a":101,"b":99}', '$.b')); +--------------------------------------------------------------------------------------------+ | GREATEST(JSON_EXTRACT('{"a":101,"b":99}', '$.a'), JSON_EXTRACT('{"a":101,"b":99}', '$.b')) | +--------------------------------------------------------------------------------------------+ | 99 | +--------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
おやおや、期待した値は 101
なのですが 99
が返却されています。
これは辞書順なのでしょうか……? というところから暗黙的なキャストやその他もろもろを疑っていたのですが、結論としては冒頭の通り「未対応」なので使ってはいけないということのようです。
というわけで、提案されているワークアラウンドを実行してみましょう。JSON INTEGERをSIGNED INTEGERに明示的にキャストして試してみます。
mysql> SELECT GREATEST(CAST(JSON_EXTRACT('{"a":101,"b":99}', '$.a') AS SIGNED INTEGER), CAST(JSON_EXTRACT('{"a":101,"b":99}', '$.b') AS SIGNED INTEGER)); +--------------------------------------------------------------------------------------------------------------------------------------------+ | GREATEST(CAST(JSON_EXTRACT('{"a":101,"b":99}', '$.a') AS SIGNED INTEGER), CAST(JSON_EXTRACT('{"a":101,"b":99}', '$.b') AS SIGNED INTEGER)) | +--------------------------------------------------------------------------------------------------------------------------------------------+ | 101 | +--------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
はい、期待通りの結果になりましたね。良かった良かった。
そしてこの挙動について、実はMySQL側も警告メッセージを残していることがわかります。
mysql> SHOW WARNINGS; +---------+------+----------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------+ | Warning | 1235 | This version of MySQL doesn't yet support 'comparison of JSON in the LEAST and GREATEST operators' | +---------+------+----------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
--show-warnings
を有効にしておくとクエリの実行時に気付くこともできますが、この手のクエリを実行した時に失敗させる方法は見付かりませんでした。strictモードとかを色々いじってはみたのですが。なにか良い方法はあるんでしょうか?