MySQL8.0.24の新しいGIS関数(2)～ST_LineInterpolatePoints()を試す

　ひとつ前のエントリ「MySQL8.0.24の新しいGIS関数(1)～LINESTRINGの経路点を求める～」で、LINESTRINGを 指定した割合ごとに区切ってPOINT集合を返す、ST_LineInterpolatePoints()関数を紹介しました。
　MySQL 8.0 のGIS機能(spatial機能)のウリは「測地系に対応したこと」ですので、この ST_LineInterpolatePoints()関数も測地系に対応しているのだと思います。平面座標と地球上の座標では「直線」の捉え方に差が出てきますので、このエントリではそのあたりを試してみたいと思います。

題材とするLINESTRING

　LINESTRING は2個以上の点(POINT)をつないでできる線です。2個以上なので、3個、4個、、、の点を順次つないだものを扱えるのですが、今回は2点間のシンプルなLINESTRINGで試してみることにします。扱う2点は、成田空港(日本)と シャルル・ド・ゴール空港(フランス)とします。測地系は WGS84 (4326)です。

空港名	北緯	東経
成田空港	35.793537 度	140.386548 度
シャルルドゴール空港	48.997026 度	2.281060 度

単純にLINESTRINGを表示してみる

　MySQL Workbench を使って、まずこの2点を結ぶLINESTRINGを表示してみます。

SELECT ST_GeomFromText(
  'LINESTRING(35.793537 140.386548, 48.997026 2.281060)', 4326
)

　少しわかりにくいのですが、東京(右のほう、北緯36度くらい)から、パリ(真ん中あたり、北緯49度くらい)に向けて直線が表示されています。実際の地球上の最短経路が表示されているわけではないようです。

経路点で区切って表示してみる

　そこで、経路点の集合を返してくれる ST_LineInterpolatePoints()関数を使って、このLINESTRINGを100分割したPOINTを表示してみましょう。 先ほどの ST_GeomFromText() を使って作成した SRID 4326 のLINESTRINGに対して、ST_LineInterpolatePoints()関数をカマしてみます。100分割なので第二引数には 0.01 を与えます。

SELECT ST_LineInterpolatePoints(
   ST_GeomFromText(
     'LINESTRING(35.793537 140.386548, 48.997026 2.281060 )', 4326
  ), 0.01
)

　美しいですね。ST_LineInterpolatePoints() は（そしておそらくは ST_LineInterpolatePoint()も）、きちんと地球が丸いことを知った上で経路上の点を求めてくれていることがわかりました。

ほんとに測地系を考慮した結果なのか？

　こういった実験は、予測した結果が「得られた」ケースとともに「得られなかった」ケースについても確認することで、より内容に確信が持てるようになるものです。ということで、平面座標である SRID 0 について、同じ2点間の座標の Interpolate を求めてみることにします。
　SQLは以下の通り。測地系の指定がなくなったことと、SRID 0 は Axis が SRID 4326 の場合とは異なるので x, y を入れ替えている点が先ほどとの違いです。
　

SELECT ST_LineInterpolatePoints(
   ST_GeomFromText(
     'LINESTRING(140.386548 35.793537, 2.281060 48.997026)'
  ), 0.01
)

　結果は・・・・

　直線になりました！　SRID 4326 でのあの曲線は、ちゃんと測地系を考慮してくれた結果だということが確認できました。

余談：日付変更線を越える場合

　MySQLの中での（SRIDを指定しての）地球は「丸い」ので、東経 180度と 西経180度はつながっています。それを確認してみましょう。サンフランシスコ国際空港（北緯 37.621438 度、西経 -122.376194 度）と成田空港との経路点を表示させてみます。

SELECT ST_LineInterpolatePoints(
   ST_GeomFromText(
     'LINESTRING(35.793537 140.386548, 37.621438 -122.376194)', 4326
  ), 0.01
)

　MySQL Workbench の Spatial View 機能がちょっとイケてなくて、全体を一画面中に表示することができない（縮尺変更が期待通りに動作しない）のですが、以下のように 東京(本当は千葉)を出発して、(緯線に対してやや北よりの進路を取り)日付変更線を越えて、サンフランシスコに到着していることが確認できます。

　得られた MULTIPOINT を CAST()関数を使って LINESTRING に戻してあげれば、元のLINESTRINGを刻んだLINESTRINGを生成することができ、例えば フレシェ値（あるいはハウスドルフ値）のより精度の高い結果を得るのに役に立つのかもしれません。
CAST()の使用例については私もまだ試すことができていないので、おいおい挑戦してみたいと思います。

追記

　折角なので CAST() で MULTIPOINT を LINESTRING に変換するのもやってみました。
分割数が多すぎると「きれいに」見えてしまうので、敢えて成田からシャゴールまでを 0.2 ごとに分割する例で試しました。

SELECT CAST(ST_LineInterpolatePoints(
   ST_GeomFromText(
     'LINESTRING(35.793537 140.386548, 48.997026 2.281060 )', 4326
  ), 0.2)
  AS LINESTRING )

SQL入力エリアに赤バッテンがついていますが、実行自体は正しくできます（MySQL Workbench が、まだこの構文、または LINESTRING という型を認識していないということでしょうか）。
　結果は、カクッカクとした点を経由したLINESTRINGとなったことが見てわかります。
ただ、ひとつ重大な欠点に気づいてしまいまして、、、、、このLINESTRINGは、というかその元となったMULTIPOINTは、「始点を含まない」ということです。0.2ずつに区切る場合は、始点から 0.2 の位置、0.4の位置、、、、を MULTIPOINT として返しますから、0.0 の位置(始点)がここには含まれないのです。
　この用途で使用するときには一工夫が必要となりそうです。（元のLINESTRINGの始点を、InterpolatePoints で得られた MULTIPOINT の先頭にくっつけた上で CASTする等）

MySQL8.0.24の新しいGIS関数(1)～LINESTRINGの経路点を求める～

　MySQL 8.0.24 では Spatial(GIS)関数に、４つの新たな関数が実装されました。

ST_LineInterpolatePoint()
ST_LineInterpolatePoints()
ST_PointAtDistance()
ST_Collect()

　前者３つが、LINESTRING上のポイントを返す関数、最後のひとつが複数のジオメトリ値をひとつにまとめる関数です。このエントリでは、前者の３つについて紹介してみます。

準備

　関数を試すに当たって、何度も LINESTRING の値を使うので、あらかじめテーブルにつっこんでおきたいと思います。以下のテーブル t1 を作成し、LINESTRING 値を持つ２つのレコードを登録しておきます。測地系を指定する場合でも大きく変わるものではないので、今回の確認は平面座標系（"普通の" x, y 座標系）で行いました。

　ひとつめのデータが、(1,1)から(11,1) への2点を結ぶ最もシンプルな線。ふたつめが、(1,1)から (3,1), (3, 4) を経由して (6,4)へと結ぶ線です（イメージしてみてくださいね。スタート地点から右に２つ、そこから上に３つ、さらに右に３つと移動させて作った線ですよ）。

CREATE TABLE t1 (id integer, g GEOMETRY);

INSERT INTO t1 VALUES  (1, ST_GeomFromText('LINESTRING(1 1, 11 1)'));
INSERT INTO t1 VALUES  (2, ST_GeomFromText('LINESTRING(1 1, 3 1, 3 4, 6 4)'));

登録された内容も一応確認しておきます。線の長さも一緒に表示してみましょう。

mysql> SELECT id, ST_AsText(g), ST_LENGTH(g) FROM t1;
+------+-----------------------------+--------------+
| id   | ST_AsText(g)                | ST_LENGTH(g) |
+------+-----------------------------+--------------+
|    1 | LINESTRING(1 1,11 1)        |           10 |
|    2 | LINESTRING(1 1,3 1,3 4,6 4) |            8 |
+------+-----------------------------+--------------+
2 rows in set (0.00 sec)

　これから新しい関数を試していくために、以下の基本構文を理解しておきましょう。実行例のSQLが長くなりがちですが、このテンプレを理解しておくと、少しは読みやすくなると思います。

mysql> SELECT id, ST_AsText( ★試したい関数をここに書く★ ) p FROM t1;

.

距離を指定して LINESTRING 上のPOINTを返す関数 ST_PointAtDistance()

　
　距離を指定してLINESTRING上の位置を返してくれるのが、ST_PointAtDistance() 関数です。先ほど登録した線のそれぞれについて、まず、スタート位置から「距離３」のPOINTを返してもらいましょう。第一引数に LINESTRING、第二引数に求めたい距離を与えます。

mysql> SELECT id, ST_AsText( ST_PointAtDistance(g, 3) ) p FROM t1;
+------+------------+
| id   | p          |
+------+------------+
|    1 | POINT(4 1) |
|    2 | POINT(3 2) |
+------+------------+
2 rows in set (0.00 sec)

　冒頭で作成したそれぞれの線に対して、スタートから距離3の位置にあるPOINTが返されたことがわかります。分かりやすいように整数にしてみましたが、もちろん整数である必要はありません。

mysql> SELECT id, ST_AsText( ST_PointAtDistance(g, 4.7) ) p FROM t1;
+------+--------------+
| id   | p            |
+------+--------------+
|    1 | POINT(5.7 1) |
|    2 | POINT(3 3.7) |
+------+--------------+
2 rows in set (0.00 sec)

　第一引数で与えられた線よりも長い距離を指定した場合はエラーとなります。

mysql> SELECT id, ST_AsText( ST_PointAtDistance(g, 15) ) p FROM t1;
ERROR 1690 (22003): Distance value is out of range in 'st_pointatdistance'

　今回のように複数の行が処理対象となる場合、その中にひとつでも長さがオーバーしたものが存在するとエラーとなる点に注意が必要です（今回の２つの線は、長さがそれぞれ 10と8 でしたね）。

mysql> SELECT id, ST_AsText( ST_PointAtDistance(g, 9) ) p FROM t1;
ERROR 1690 (22003): Distance value is out of range in 'st_pointatdistance'

　距離8（今回のすべての線が収まっている長さ）を指定すれば、もちろんエラーなく動きます。このとき id=2のほう（長さ8）の結果は、この線の終点のPOINTとなっていることがわかります。

mysql> SELECT id, ST_AsText( ST_PointAtDistance(g, 8) ) p FROM t1;
+------+------------+
| id   | p          |
+------+------------+
|    1 | POINT(9 1) |
|    2 | POINT(6 4) |
+------+------------+
2 rows in set (0.00 sec)

割合を指定して LINESTRING 上のPOINT(s)を返す関数 ST_LineInterpolatePoint(s)()

　ここでは２つの関数を紹介します。ST_LineInterpolatePoint() と ST_LineInterpolatePoints() です。Point のところに複数形の s がついているか否かの違いです。
　まずは、sがつかないほう。第一引数には LINESTRING を、第二引数には求めたい割合の値を指定します。ここでは 0.4 の位置を求めてみましょう。

mysql> SELECT id, ST_AsText( ST_LineInterpolatePoint(g, 0.4) ) p FROM t1;
+------+--------------+
| id   | p            |
+------+--------------+
|    1 | POINT(5 1)   |
|    2 | POINT(3 2.2) |
+------+--------------+
2 rows in set (0.01 sec)

　それぞれ（長さ10の0.4である）４の位置のPOINT、および（長さ８の0.4である）3.2の位置にあるPOINTが返されていることがわかります。
　sがつく方の関数は、その名前のとおり複数の結果を返します。同様に 0.4 を第二引数に与えてみると、以下のようにそれぞれ２つのPOINTを持つ MULTIPOINT の値が得られます。

SELECT id, ST_AsText( ST_LineInterpolatePoints(g, 0.4) ) p FROM t1;
mysql> SELECT id, ST_AsText( ST_LineInterpolatePoints(g, 0.4) ) p FROM t1;
+------+-----------------------------+
| id   | p                           |
+------+-----------------------------+
|    1 | MULTIPOINT((5 1),(9 1))     |
|    2 | MULTIPOINT((3 2.2),(4.4 4)) |
+------+-----------------------------+

　各点は、スタート地点から 0.4 そして 0.8 の点となっていることがわかります（自身で確認してみてください）。
　今回は 0.4 を指定したので、0.4と0.8の2カ所のPOINTとなりましたが、もっと切り刻むこともできます。0.2で実行した結果が以下となります。

mysql> SELECT id, ST_AsText( ST_LineInterpolatePoints(g, 0.2) ) p FROM t1;
+------+---------------------------------------------------------------------------------+
| id   | p                                                                               |
+------+---------------------------------------------------------------------------------+
|    1 | MULTIPOINT((3 1),(5 1),(7 1),(9 1),(11 1))                                      |
|    2 | MULTIPOINT((2.6000000000000005 1),(3 2.2),(3 3.8000000000000007),(4.4 4),(6 4)) |
+------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

　第二引数は線上の位置を割合で示すものですから、１より大きい値や負の値ではエラーとなります。

mysql> SELECT id, ST_AsText( ST_LineInterpolatePoints(g, 3) ) p FROM t1;
ERROR 1690 (22003): Distance value is out of range in 'st_lineinterpolatepoints'

mysql> SELECT id, ST_AsText( ST_LineInterpolatePoints(g, -0.2) ) p FROM t1;
ERROR 1690 (22003): Distance value is out of range in 'st_lineinterpolatepoints'

割合関数と距離関数との非対称

　第一引数にLINESTRINGを与えて、第二引数に距離または割合を与えるという、それぞれ似通った感じのこれらの関数ですが、不思議なことに、「割合」のほうにはLINESTRINGの長さまで繰り返してMULTIPOINTを返す関数があるのに対して、「距離」のほうにはそれがありません。
　どういうシーンで使えるのかを想像してみたときに、私は真っ先に「マラソンコースをあらわす 42.195km の LINESTRING に対して例えば 5km ごとの地点の POINT を得る」みたいな活用を思いついたのですが、「ST_PointsAtDistance() 」のように "s" のついた関数は存在しないのです。

最後に

　大切なことを書いていませんでしたが、今回紹介した Spatial関数。どれも OpenGIS に対する MySQLの独自の拡張です。独自と言っても、ST_LineInterpolatePoint() は postGIS にも実装されていますし（ただしpostGISには ST_LineInterpolatePoints()関数が存在しない代わりに ST_LineInterpolatePoint()関数の第3引数で繰り返しを行うか否かを指定する）、ST_PointAtDistance()も検索してみると Informix のGIS関数には存在しているようで、まったくMySQL開発チームが勝手に思いつきで作ったものというわけではなさそうです。
　
　今回は平面座標でこれらの関数の動作を試してみましたが、WGS84などの測地系の上でこの関数を使ったときの動作については、今後試してみたいところです。地球の丸さを考慮した経路点を返してくれることを期待していますが、それをこの目で確認してみたいですね。

歓迎！感謝！MySQL 8.0 日本語マニュアル公開！

　2ヶ月程前から試験的に公開されていた MySQL 8.0 日本語マニュアルが、ユーザらからの指摘・修正提案を反映して、昨日正式公開されました。実現に尽力くださった関係者の皆様に、心から感謝を申し上げます。

mysql 16642:MySQL 8.0のリファレンスマニュアルの日本語化について

　旧バージョン（MySQL 5.6）のマニュアルにまるきり同じ文章があった部分はそれを採用し、それ以外の部分は基本的には機械翻訳（一部ユーザ指摘等により手動で修正）という仕組みなので、必ずしも自然な日本語ではない部分も多くあるのですが、それでもまず最初に目を通す文書が日本語になっているというのは、情報収集の効率が3桁か4桁くらい違います。助かる。

　日本語で見て、あれ？と思った時に英語のマニュアルを参照できると、意味を理解するのに役に立ちますので、日本語マニュアルと英語マニュアルを行ったり来たりする方法を紹介しておきます。とりあえず説明の都合上、英語マニュアルからスタートします。

　MySQL 8.0 マニュアル（英語）を見ると、右上に、[version 8.0]と書かれた部分があります。

　開いてみると、他のバージョンの名前が並んでいます。5.7 とか 8.0 Japanese とかがありますね。

ここで 8.0 Japanese を選んでみましょう。

　もともと英語で見ていたページに対応する 日本語ページが表示されました。日本語ページからも同様にして [8.0 English] を選択することで英語ページへと移動できます。

　ちなみに、知っておいて損はないと思いますが、URLの操作でバージョンを変更することもできます。

https://dev.mysql.com/doc/refman/8.0/ja/spatial-function-argument-handling.html

というページを見ているとき、URLの中程にある /ja/ の部分を /en/ に変えれば良いです。

　日本語マニュアルの、今後の修正対応の方針については、細かいことは決まっていないようですが、読みにくさの改善はともかく、明らかな誤訳があれば修正に尽力していただけるのではないかと期待しましょう。明らかな誤訳（たとえば意味が正反対になってしまっているとか、行動主体が違うとか）に気づいたら、 bugs.mysql.com にカテゴリ Japanese Documentation で報告すると良いと思います。報告したら、対応を願いながら気長に待ちましょう。

　マニュアルページの右上を開いてみて気づいたかと思いますが、英語以外の言語にマニュアルが翻訳され、提供されているって、すごいことなんです。世界中にいろんな言語があるのに、英語以外にマニュアルがあるのは Japanese だけ！　この素晴らしい事実に感謝しつつ、「日本のMySQLユーザを大切にすると、MySQL界にこんなに素晴らしいことがあるぞ！」と伝えるべく、みんな、それぞれで色んな事を試してみたり、それをブログとかで発表したり、バグ報告を積極的にしたりなどして、MySQL界に感謝の思いを形で返せたらいいですね。そうすれば、きっと次のメジャーバージョンでも、喜んで日本語マニュアルを作ってもらえるようになると思います。

　次のバージョンのMySQL日本語マニュアル(を作ってもらえる環境)を作るのは、私たちなのです！　

・・・とちょっと大きなことを言ったところで、まずは作ってもらった 8.0 日本語マニュアルを最大限に活用していきましょう！

最後に、
https://dev.mysql.com/doc/refman/8.0/ja/locale-support.html
のページ最後にある言語リストがきれいになったのは、私の貢献であるとアピールしておきたいと思います（ほとんど人にとっては役に立たない）。実は結構調べた(笑)

MySQLの暗黙の型変換～文字列と数字～の罠

　先日開催した Club MySQL で徳丸さんが面白いものを紹介してくれたのが印象に残ったので、整理してみます。

テーマ

　「文字列と数字の自動変換」です。

基礎知識

　MySQLは、なるべく可能な限り自動的に型変換を行って、ユーザの希望を叶えるべく努力をしてくれます。
以下の２つを見てください。

mysql> SELECT 1+2;
+-----+
| 1+2 |
+-----+
|   3 |
+-----+

mysql> SELECT '1'+2;
+-------+
| '1'+2 |
+-------+
|     3 |
+-------+

　前者はふつうに 数値 1 と 数値 2 を足し算したものですが、後者は、文字列 '1' と 数値 2を足し算したもので、厳密には演算不可能なものですが、MySQLは「いち と に を足したいんだよね？」と気を遣ってくれて、その結果 数値 1 と 数値 2 の和算を実施してくれます。
　この文字列変換は、必ずしもすぱっと文字→数値 に変換できる場合以外でも、読めるところまで頑張って読んで変換してくれます。

mysql> SELECT '1nin'+'2ko';
+--------------+
| '1nin'+'2ko' |
+--------------+
|            3 |
+--------------+

余談ですが、最初に出てきた「数字以外の文字」のところまでを数値変換してくれるので、以下のように、直観には反する結果を得たりもしますが、これもルールどおりです。

mysql> SELECT '1,234,567' + 3;
+-----------------+
| '1,234,567' + 3 |
+-----------------+
|               4 |
+-----------------+

　ここまでが、文字と数値の自動変換の基礎知識のお話。

自動変換されて直感とは異なる動作

　ここに、以下のような構造と値を持つ テーブル t1 があるとしましょう。

mysql> select * FROM t1;
+------+---------+--------+---------------------+
| id   | name    | himitu | created_at          |
+------+---------+--------+---------------------+
|    1 | watashi | NULL   | 2021-01-21 18:47:02 |
|    2 | oreore  | naisyo | 2021-01-21 18:47:41 |
+------+---------+--------+---------------------+

　ここで、WHERE句に name列を指定しての抽出の例をいくつか見てみます。
まず普通にズバリ一致。

mysql> SELECT * FROM t1 WHERE name='oreore';
+------+--------+--------+---------------------+
| id   | name   | himitu | created_at          |
+------+--------+--------+---------------------+
|    2 | oreore | naisyo | 2021-01-21 18:47:41 |
+------+--------+--------+---------------------+

　WHERE句に指定された 'oreore' に一致する行が取得されます。普通の動きです。
試しに 'oreore' のかわりに 'ore' にしてみると、そんなものに一致する行はないので、ゼロ行が得られます。

mysql> SELECT * FROM t1 WHERE name='ore';
Empty set (0.00 sec)

　ではここで、'ore' に 数値ゼロを加えたものを指定するように変えてみます。

mysql> SELECT * FROM t1 WHERE name='ore'+0;
+------+---------+--------+---------------------+
| id   | name    | himitu | created_at          |
+------+---------+--------+---------------------+
|    1 | watashi | NULL   | 2021-01-21 18:47:02 |
|    2 | oreore  | naisyo | 2021-01-21 18:47:41 |
+------+---------+--------+---------------------+
2 rows in set, 3 warnings (0.00 sec)

　なんと、テーブルの全件が得られました！
徳丸さんの講演の中では、数値を加えるのではなく文字列を加えるものだったので、それも試してみます。

mysql> SELECT * FROM t1 WHERE name='ore'+'nanika';
+------+---------+--------+---------------------+
| id   | name    | himitu | created_at          |
+------+---------+--------+---------------------+
|    1 | watashi | NULL   | 2021-01-21 18:47:02 |
|    2 | oreore  | naisyo | 2021-01-21 18:47:41 |
+------+---------+--------+---------------------+
2 rows in set, 4 warnings (0.00 sec)

　こちらも t1 の全件が得られました。知らないとびっくりするような結果ですね。なので、ちゃんと知っておきましょうというのがこのブログの趣旨。

ポイントは「自動変換」

　数値の和算である　"+" の演算子は、その両脇は数値であることを期待します。
先ほど紹介した「'1nin'+'2ko'」が 3 になったのは、左辺 '1nin" を左から見ていって数値と判断できるところまで（つまり 1）を採用したものであり、「'1,234,567'」は、同様に左から見ていって 1 の部分だけが数値として判断された（2文字目 "," は、すでに数値ではない）ので、 1 として扱われたということです。
　とすると「'ore'」はどうでしょうか。"+" の演算子により、これを数値として扱おうとするとき、先ほどと同様に左から見ていきます。すると、いきなり 'o' (おー) が現れて、はい終了、この場合 ゼロと見做されます。’nanika' も同様にゼロと見做され、ゼロプラスゼロはゼロ。これは以下のクエリでも確認できます。

mysql> SELECT 'ore'+'nanika';
+----------------+
| 'ore'+'nanika' |
+----------------+
|              0 |
+----------------+
1 row in set, 2 warnings (0.00 sec)

　ところで、全件がマッチしてしまったクエリ。

WHERE name='ore'+'nanika';

　となっていました。この右辺 'ore'+'nanika' がゼロになることは先ほど示されました。
そうすると、さて、左辺はどのように扱われるのでしょうか。そう。数値に変換されるのです。テーブル t1 で言うと各行のnameの値、'watasi'と'oreore'が数値に変換（0になる）されるということです。左辺もゼロ、右辺もゼロ。ということでマッチしたというわけです。
　なので、先ほど「全件がマッチ」と書きましたが、name列の値として '96nin' のように数字で始まる文字列がある場合、その行はマッチしない、というのが正確な言い方になりますね。

　ちなみに、先ほどのクエリ、4 warnings と出ているのでワーニングを確認してみると、こんな感じ。切り捨てられていますよ、と言われていますね。WHERE句の右辺に使用した 'ore', 'nanika' が、数値変換で切り捨てられましたよ、というものと、列の値である 'watashi', 'oreore' が切り捨てられましたよ、というものが含まれていることがわかります。

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'watashi' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'ore'     |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'nanika'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'oreore'  |
+---------+------+---------------------------------------------+

　以上、知らないとちょっとコワいMySQLの自動変換のお話でした。まぁ文字列の列を検索キーにしている時にそこに数値を与えるなよ、とか、数値演算子を使うなよ、という当たり前の話なので、それほど怖がることでもないかなとも思います。正しい知識を付けましょう。

というのは先人たちも語っている

　という話は、じつは 5年ほどまえに そーだいさんもブログで紹介していました。さすが！

soudai1025.blogspot.com

『Club MySQL #5 ～SQLデータベースのセキュリティ』開催しました

　久々の「Club MySQL」となる、『Club MySQL #5 ～SQLデータベースのセキュリティ』を開催しました。

mysql.connpass.com

Club MySQL は、ひとりの講演者の話をじっくりと聞こう、という趣向の、日本MySQLユーザ会のイベントシリーズです。今回は、徳丸浩先生にお引き受けいただき、Webセキュリティの視点でデータベースとして注視すべき点について語っていただきました。テーマとスピーカーのパワーで、当ユーザ会のイベントとしては「驚くほどたくさんの」参加申込みをいただき、ありがとうございました。

ライトニングトーク

　メインスピーカーにも、膨大な経験をもとに90分間たっぷりと濃厚な話を聞かせていただきましたが、ライトニングトークだってパワフルです。
　まずは「中の人」山﨑さんからMySQLセキュリティ強化の歴史について紹介していただきました。昔のMySQLは「置いたらすぐに使える」ことを目指していたこともあって、初期ユーザ、初期データベースなどに「したい放題」だったところがあるのですが、最近は少しずつ（いや急速にかも）「カタくする方向」に進んでいます。どのバージョンでどんな施策が採られてきたのか、とても整理されていて、勉強になりました。LTでというお願いだったので（それでも10分くらいお話されていましたが(笑)）、ちょっと内容の濃さに対して慌ただしくなってしまったので、これにプラスしてその中から特に注目の施策についてもう少し掘り下げてお話を聞かせていただく機会を作りたいですね。
　yoku0825さんからは、過去のオラクルさんのMySQLセキュリティに関するセミナーを紹介する形で、DavidとJackの行動について語っていただきました。頭が良いのだか要領悪いのだかよくわからない David と Jack から我々はMySQLのデータを守ることができるのか。次々と悪いことを思いつく DavidとJackにドキドキしながら、話を聞かせていただきました。

徳丸先生の「ウェブセキュリティから見てデータベースってどうなのか解説する」

　このイベントの私の役割である「進行役」として、常に時間を気にしながら運営を進めているわけですが、今回徳丸先生にお願いした「90分くらいで」に対して、ほんとにきっかり 90分（プラス1分程度）でまとめてくださったことに驚きました。私も大概、何ヶ所かに伸び縮み可能な話題を入れておき、50分程度のセミナーならぴったり終わらせる自信はありますが、90分は誤差が出すぎてなかなか調整が難しいです。 徳丸先生の経験と職人技に、ちょっと感動しました。
　と、内容じゃないところの感動をまず紹介しましたが、もちろん内容にも感動しています！

　まず、Webセキュリティという観点、つまりMySQLを取り巻く実行環境全体が話題になる点が新鮮でした。MySQLユーザ会の勉強会というと、どうしてもMySQLにものすごく近い部分だけのお話－－いわばMySQLの「中から」－－になることが多かったので、MySQLの「外から」というのが今回のお話の視点でした。
　もう少し「データベース一般的」なお話が中心になるかと予想していたので、想像していた以上にMySQL固有のお話をたくさん入れていただいたのが嬉しかったです。私は、文字列の自動変換（'ab'+'c' のような）は知っていたものの、まさかカラム値のほうまでが自動変換の対象になっているとは認識していなかったので、この話は驚きました。後ほど本件は別ブログとして書こうと思います。
　SQLインジェクションに始まり、文字エンコーディング、型変換、同時実行時の重複データ、そしてTDE。最後にそれぞれの対策によって守れるもの守れないものの整理が、特に良かったです。時々「データファイルを暗号化しておいたら最強じゃん？」と考えている感じの人に遭いますが、正しい入り口（表門(おもてもん)）から来た人に対しては、いとも簡単にデータを渡してしまうので、いかに、正しい人だけ表門を通過させるかというのとセットで考える必要があるわけですね。

全体総括

　徳丸さんが、その前のLTで登場した DavidとJackを積極的に取り入れてくださった機転に、大笑いしました。これから動画をご覧になる方の中には、徳丸先生目当てでそこだけを視聴するつもりの方もいるかもしれませんが、ぜひLTもご覧いただけたらと思います。セットで見ると、徳丸さんの「ワザ」がわかると思います。
　今回の発表いただいた皆さんの内容、たいへん勉強になりました。DBMSセキュリティの分野では今後、悪いことをする人は David と Jack ということで定着しそうです（するのか？）。もう少しお話いただけそうな感じでもありましたので、またお誘いさせていただきます！このたびは、どうもありがとうございました。

坂井のオープニング説明資料(スライド)

オープニング資料：Club MySQL #5 SQLデータベースのセキュリティ from sakaik

www.slideshare.net

当日の模様(動画：日本MySQLユーザ会YouTubeチャンネル)

www.youtube.com

当日の模様(ツイート：Togetterまとめ)

togetter.com

体系的に学ぶ 安全なWebアプリケーションの作り方 第2版 脆弱性が生まれる原理と対策の実践

『JPUG & MyNA合同勉強会 -PLEASE 2021/4-』参加

エイプリルフールの騒動（当社比）から12日ほどが経った頃、yoku0825さんが勉強会を企画してくれました。

connpass.com

各DBMS（やそれ以外のソフトウェア）で PLEASE 句の動作を実装する試みが行われたことを受けて、せっかくだから技術的な内容を含む話を披露しあおうじゃないか、という感じで開催してくれました。形式としては、日本PostgreSQLユーザ会(JPUG)さんと、日本MySQLユーザ会(MyNA)の合同勉強会ということで、開催となりました。PostgreSQL(澤田さん)、MySQL(yokuさん)それぞれの「拡張」から話が広がっていったので、まさに合同勉強会というに相応しい会になったと思います。

　発表は、いかのように盛りだくさん。

イﾉﾚカ㌠ sakaik MySQﾚの新機能：PLEASE句について(仮)
🐘㌠ masahiko_sawada PostgreSQLの新機能: PLEASE句について(仮)
🐬㌠ yoku0825 MySQLの新機能: PLEASE句について(仮)
redis㌠ maruloop Redisの新機能: please getコマンドについて(仮) LT
SparkSQL㌠ miyakelp_ SparkSQLの新機能: PLEASE句について(仮) LT
Oracle㌠ nori_shinoda Oracleの新機能: PLEASE句について(失敗ver)(仮) LT
SQL Server㌠ Masayuki_Ozawa SQL Serverの新機能: PLEASE句について(仮) LT

　postgreSQL澤田さんの、ライブコーディング（その場でコードを修正し、ビルドして動かしてみせる）がとても刺激的でした。MySQLは多くのコードから参照されている部分に変更が入るため、再コンパイルが広い範囲に発生して、ちょっとこの時間でライブでサクサク見せるのは難しいということで「こちらにできあがったものが良いされてございます」メソッドで。
　日付が変わっちゃう！と急いで実装を試みてくださった方、ソースコードに手を加えることができないのに、主処理に渡る前になんとか処理してやろうと試みてくださった方々、大笑いしながら、楽しく聞かせていただきました。

　私自身も、騒ぎを起こした責任を取って冒頭、少々お話をさせていただきました。PLEASE句自体の話は、もう元ネタをご覧いただければそれがすべてなので、その周辺の話として、感謝・経緯・そしてエイプリルフールのネタに対するポリシーといった内容についてお話をしました。一応資料も公開しましたので、ご笑覧いただけましたらと思います。MySQﾚというのは、実は立体的なMySQLだったという説明も含んでいますので、スライドを行ったり来たりしながらご確認ください:-)

PLEASE時代に即したエイプリルフール from sakaik

www.slideshare.net

　これにて、一連のエイプリルフール狂想曲は一旦のおひらきとなります。たぶん。
たまたまちょうど良くハマったネタと、すごい技術力が集まると、こんなに面白いことになるのだという、素敵な体験をさせていただきました。改めて、乗っかってくださった皆さんありがとうございました。

動画もMySQLユーザ会の YouTube チャンネルで公開されています。なんか私のスライド、ちっちゃく出てるなーと思ったら、そうだ。セカンドモニタを縦にして使っているので、そのせいだ！！（だいたいイベント発表の時には横置きに戻しているのですが、このときは忘れたみたい）

www.youtube.com

　また、MyNAとJPUGの共同主催の勉強会ですので、MySQLユーザ会のイベントへの登壇者数にもカウントします。(2021年は MySQLユーザ会関連イベントに 41人以上の人に登壇していただきたい、という目標を掲げております～MySQL 41 Speakers～）

■2021/04/12 MyNA & JPUG合同 PLEASE
04(2). @sakaik さん: MySQﾚの新機能：PLEASE句について(仮)
09. @masahiko_sawada さん: PostgreSQLの新機能: PLEASE句について(仮)
10. @yoku0825 さん: MySQLの新機能: PLEASE句について(仮)
11. @maruloop さん: Redisの新機能: please getコマンドについて(仮)
12. @miyakelp_ さん: SparkSQLの新機能: PLEASE句について(仮)
13. @nori_shinoda さん: Oracleの新機能: PLEASE句について(失敗ver)(仮)
14. @Masayuki_Ozawa さん: SQL Serverの新機能: PLEASE句について(仮)

　楽しいお話をありがとうございました。

時代に即したMySQﾚの新機能：PLEASE句

　最近は、会社などの組織において仕事の指示をする場合に、単に上司が命令をするだけでは組織は動かないと言われています。部下に仕事をしてもらうには－－そう、まさにこの「してもらう」の気持ちこそが本質なのですが－－「命令」ではなく「依頼」の形を取ることで、お互いに気持ちよく仕事をすることができ、より良いチームとなるのです。

　この世の中の流れは近年、ソフトウェアの世界にも強く適用されるようになってきました。ソフトウェアに於いても、常に、より中立的な立場での対応が求められてきています。

　MySQﾚも例外ではなく、最近の修正ではレプリケーションの master-slave を source-replica と呼ぶように変更したり、blacklist を blocklist に変更したりなどの話題を目にした方も多いと思います。
　これら一連のポリティカリーにコレクトな対応に今回新たに加わったのが、冒頭で紹介した「依頼」の構文です。例を見てみましょう。まずシンプルなSELECT文である、

SELECT * FROM t1;

という命令。これは現代のポリティカルコレクトネス的にはアウトです。人間だからといってコンピュータに一方的に「命令」するのは、よろしくありません。MySQﾚの最新バージョンでは、この問題に対応しています。

SELECT * FROM t1 PLEASE;

命令ではなく、依頼。これが新しいデータベースの活用法です。

PLEASE句は、先頭に配置することもできます。

PLEASE UPDATE t2 SET c1=100 WHERE id=1;

命令ではなく、依頼。
ちょっとした気遣いで、依頼する側もされる側も気持ちよく仕事ができるのです。

　ある実験によると、PLEASEを付けて一ヶ月間運用を続けたシステムは、そうでないシステムと比べて、約6%のパフォーマンス向上が得られたとのことです（モーツァルトを聴かせて運用しているMySQﾚの性能が3%ほど高いという話と似ていますね）。

　コンピュータと人間とが共に気持ちよく過ごせる社会へ向けて、MySQﾚも進化し続けているのですね。頼もしいことです。

.

　なお、蛇足ながら本日4月1日でございますことを付記し、本アーティクルをおしまいとしたいと思います。
Please DO NOT believe this article.

[MySQL]

.

追記

　yoku0825 さんが、MySQL で実際に動くパッチを書いてくださいました。
blog.gmo.media

• PLEASE句をエラーとせずに受け入れること
• PLEASE句がついていないぶっきらぼうな「命令」には1秒間のサボタージュをしてから結果を返すこと
• PLEASE句がついていないぶっきらぼうな「命令」にはwarningを出すこと。エラーコードにもこだわり
• PLEASE句が書いていない場合にはエラーにするための SQL_MODE ("STRICT_PLEASE_MODE")の追加

の対応が為されているようです。バイナリは配布せず、ソースコードのパッチのみの公開。MySQL 5.6 をベースにしたのは、ビルドの速さ（トライ＆エラーを繰り返しやすい）を重視して、かな。（MySQL 8.0 のビルドには結構時間がかかる）

　久々に、「才能の無駄遣い」と絶賛したくなるようなワザを目の当たりにしました。徐々にできあがっていく様子を yoku0825さんのtwitterで伺ったりしていくのは、面白かったです。

追記2

　あとは、結果を返してくれたことに対してお礼を言える THANK YOU 構文の実装が待ち望まれます。実装されれば、かなり MySQﾚとの距離を近づけるのに役に立ってくれそうです。

あとは、すごく頑張って抽出／集計して結果を返してくれたのに、いつも貰いっぱなしなのが気になっているので、THANK YOU 構文とかあると、よりMySQﾚとの良い関係を築けそう。

— 坂井 恵(SAKAI Kei) (@sakaik) 2021年4月1日

追記3

　澤田さんが PostgreSQL に実装してくれました！ PLEASE句がないとインデックスを使ってくれないというのが特に新奇性！　みんな、DBMSにちゃんと感謝してるんですね！

面白い！！

PostgreSQLにもPLEASE句実装してみた。PLEASE句がないと1秒待たされるし、インデックスを使ってくれなくなります。 https://t.co/3xS3dikU4M pic.twitter.com/khfnM2PWG4

— Masahiko Sawada (@masahiko_sawada) 2021年4月1日

　さらに、THANK YOU 文まで実装！

たしかにその通りですね！！
いつも頑張ってくれているPostgreSQLにお礼を言うために実装しました！ pic.twitter.com/jjWPI2nsKC

— Masahiko Sawada (@masahiko_sawada) 2021年4月1日

追記4

　MySQﾚ について、「マイエスキューレ」という声がありますが（それはそれでイタリアではきっとそう呼ばれているのだろうと信じることにして）、実はMySQLだったというタネアカシ:-)

ちなみに、そろそろ種明かししますけど、あのマイエスキューレに見えるやつ。実は MySQL だったって気づいていましたか？　物事は立体的に考えると真実が見えてくるという教訓が、そこにはあります。 pic.twitter.com/KZVD1Wa9Cj

— 坂井 恵(SAKAI Kei) (@sakaik) 2021年4月1日

追記5

　オープンソースじゃないので Oracle database では無理だろうと思っていたら、中家さんが PL/SQL を使って遊んでくれました。pleaseをつけないと、ちゃんと遅くなってくれます！（・・・って、やりたいの、それだったっけ(笑)。付けると速くしたいんじゃなかったっけｗ）

差がわかりにくいけど、こんなのでよければ一応できたぽいです。ヒント句が表示されているのは単なるデバッグメッセージの消し忘れです。 pic.twitter.com/WnW5Sgl54e

— HiroyukiNakaie (@HNakaie) 2021年4月1日

　そしてソースコード。

実現優先で非常に手抜きですがソース pic.twitter.com/ldLvC8WfVp

— HiroyukiNakaie (@HNakaie) 2021年4月1日

追記6

　なんと、Spark SQL にも PLEASE句が！　PLEASEつけないと DISTINCT しちゃうとか、極悪(笑)。
そうか、PLEASEつけないと 100件までしか出力しない、みたいな抗議行動も考えられるのか、とヒントをもらいました。

miyakelp.hatenablog.jp

追記7

　SQL Serverでのトライ！　RDBMS本体側を書き換えることはできないので、TDSのProxy を噛まして、そのProxyで変換（PLEASE句の除去）やウェイトの挿入などを行っているようです。

Proxy でクエリを書き換えるという実装（遠い目）

SQL Server で PLEASE を実装してみる https://t.co/x4Cz1DMcEg

— Masayuki Ozawa (@Masayuki_Ozawa) 2021年4月1日

blog.engineer-memo.com

追記8

　Redis にまで！！
RDBMSに、プログラムに、コンピュータに対して、単に命令するばかりでなく、感謝を込めて「お願い」する文化が少しずつ広がっていて、嬉しいです！

Redisにもplease getコマンド実装しました。 pic.twitter.com/MHFqbKHacu

— maru (@maruloop) 2021年4月1日

追記9

　「4/1に間に合わなかったけど」と、SQLite3 への実装（パッチ）も公開されました。大丈夫です、まだエイプリルフール5日目です！

祭に乗り遅れてしまったけど書きました。 / “4/1に間に合わなかったけどSQLite3にPLEASE句を追加するパッチを書きました” https://t.co/8zoarqj9jY

— mattn (@mattn_jp) 2021年4月4日

追記10

　みなさんのTwitterでのやりとりが面白かったので、これは後生まで記録に残しておくべき！と、Togetterにまとめました。
togetter.com

たくさんの人に見ていただけて、トゥギャッター編集部さんの「本日のイチオシ」に選んでいただきました。みんなの開発パワー、すごい！

@sakaikさんのまとめ https://t.co/bKvwlAjirh を本日の編集部イチオシに選ばせていただきました！作成ありがとうございます、素晴らしい技術力ですね！

特集ページでも紹介しています。https://t.co/dJHzBtMsCO みてね！

— トゥギャッター編集部 (@tg_editor) 2021年4月4日

追記11

　それぞれの方が今回試みた技術的知見をそのままにしておくのは勿体ないということで、勉強会（発表会）が開催されることになりました。いや、べつにそんな大層な話じゃなくて「おもしろいからやろーぜー」というノリです。私も皆様の時間を奪った責任を取って1枠目で少しだけお話をさせていただきます。
connpass.com