MySQL、"UDF" の名称を "Loadable Function" に変更

　MySQLのリファレンスマニュアルは日々更新されているのですが、本日、ちょっと大きめの用語の変更が加えられました。

　その変更とは、今まで User-Defined Function (UDF) と呼ばれていたものがすべて、Loadable Function になった、というものです。本日時点ではとりあえずマニュアル上の表記のみの変更であり、最新の MySQL 8.0.25 であっても特に動作が変更されたというものではありませんが、今後徐々に変化していくものと思われます。

　なお自画自賛ですが、この日記は、おそらくこの用語に関する世界で一番早い情報です。:-)

マニュアルの変更

　リファレンスマニュアルで、膨大な量の "UDF", "User-Defined Function" の表記が廃止され、ほぼすべてが本日の変更で "Loadable Function" に置き換えられました。一部、performance_schema の名前やモジュールの名前等に udf の表記は残っていますが、表題や説明文などはすべて置き換えが完了している模様です。確認をしながら結構大変な作業だと思いますが、こういう運営をきちんとしてくれるドキュメントチームが機能していることが、MySQLの魅力でもあると感じました。

変更の背景(想像)

　呼び方が変更された理由については想像するしかありませんが、UDF(ユーザ定義関数) という呼び方が、いかにも一般ユーザが作るものという印象が強いからかと想像しました。もちろん一般ユーザも自由に作って、自分のMySQLに機能を追加できるものなのですが、今は、(Enterpriseの) asymmetric_encrypt() 関数などの asymmetric_* 関数群やkeyring関係の関数など、公式のUDFもあるので、「ユーザ定義」という名称よりは「読み込み可能な」と称するのが適切と判断したのでしょう。

関連情報の追加

　マニュアルには、
9.6 Query Attributes に "Query Attribute Loadable Functions" の記述が追加されています。
https://dev.mysql.com/doc/refman/8.0/en/query-attributes.html

　Loadable Function の説明（元 UDF の説明）は、こちらです。
https://dev.mysql.com/doc/refman/8.0/en/function-loading.html

用語の整理

　User-Defined Function (UDF) が Loadable Function へと変更されたのに合わせて、今まで単純に SQL Function と称されることもあった関数たちは、built-in function または native function と明示的に呼ばれることになったようです。もちろん元々より built-in (native) function という名称はあったものなので、こちらは、Loadable Function に対する用語の確認といったところです。

performance_shcema は

MySQL 8.0.25時点ではもちろん、performance_schema のテーブル名や列名は変わっていません。今後どのように変更していくのか（MySQL 8.0 の中で付け替えが発生するのか、並行するのか、このままでいくのか等）注目です。

mysql> use performance_schema;
mysql> SELECT * FROM user_defined_functions;
+-------------------------------------------------+-----------------+----------+-------------+-----------------+
| UDF_NAME                                        | UDF_RETURN_TYPE | UDF_TYPE | UDF_LIBRARY | UDF_USAGE_COUNT |
+-------------------------------------------------+-----------------+----------+-------------+-----------------+
| asynchronous_connection_failover_delete_source  | char            | function | NULL        |               1 |
| asynchronous_connection_failover_add_source     | char            | function | NULL        |               1 |
| mysqlx_get_prepared_statement_id                | integer         | function | NULL        |               1 |
| mysqlx_generate_document_id                     | char            | function | NULL        |               1 |
| asynchronous_connection_failover_delete_managed | char            | function | NULL        |               1 |
| mysqlx_error                                    | char            | function | NULL        |               1 |
| innodb_redo_log_archive_flush                   | integer         | function | NULL        |               1 |
| innodb_redo_log_archive_stop                    | integer         | function | NULL        |               1 |
| asynchronous_connection_failover_add_managed    | char            | function | NULL        |               1 |
| innodb_redo_log_archive_start                   | integer         | function | NULL        |               1 |
+-------------------------------------------------+-----------------+----------+-------------+-----------------+
10 rows in set (0.01 sec)

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

　MySQL 8.0.24 では ST_Collect() 関数が実装されました。ジオメトリのデータを collect してくれそうな、見たままの機能を持った関数です。
　とりあえずどんな動きをするのか試してみたので、紹介します。なお、今回は 5/11に緊急リリースされた MySQL 8.0.25 にて動作確認をしています。

データ準備

　id、カテゴリ番号、そしてジオメトリの３つのカラムを持つテーブル t1 を作成し、データをつっこみます。

CREATE TABLE t1 (id integer, cat integer, g GEOMETRY);
INSERT INTO t1 VALUES (1, 1, ST_GeomFromText('POINT(1 1)'));
INSERT INTO t1 VALUES (2, 2, ST_GeomFromText('POINT(2 1)'));
INSERT INTO t1 VALUES (3, 1, ST_GeomFromText('POINT(3 1)'));
INSERT INTO t1 VALUES (4, 1, ST_GeomFromText('POINT(3 2)'));
INSERT INTO t1 VALUES (5, 2, ST_GeomFromText('POINT(2 1)'));
INSERT INTO t1 VALUES (6, 3, ST_GeomFromText('POINT(3 3)'));

内容確認。内部的にはジオメトリのデータはバイナリになっているので、人間が目視できる形に「変換」するために、ST_AsText() を使うのでしたね。

mysql> SELECT id, cat, ST_AsText(g) FROM t1 ORDER BY id;
+------+------+--------------+
| id   | cat  | ST_AsText(g) |
+------+------+--------------+
|    1 |    1 | POINT(1 1)   |
|    2 |    2 | POINT(2 1)   |
|    3 |    1 | POINT(3 1)   |
|    4 |    1 | POINT(3 2)   |
|    5 |    2 | POINT(2 1)   |
|    6 |    3 | POINT(3 3)   |
+------+------+--------------+

単純に全部まとめる

　いわゆる SELECT SUM(col1) FROM t1; みたいな、GROUP BY を指定しないグルーピングです。

mysql> SELECT ST_AsText( ST_Collect(g) ) FROM t1;
+-------------------------------------------------+
| ST_AsText( ST_Collect(g) )                      |
+-------------------------------------------------+
| MULTIPOINT((1 1),(2 1),(3 1),(3 2),(2 1),(3 3)) |
+-------------------------------------------------+

　6件すべての POINT が、ひとつの MULTIPOINT に集約されました。
ST_Collect() の集約では、DISTINCT をかけることもできます。重複除去です。

mysql> SELECT ST_AsText( ST_Collect(DISTINCT g) ) FROM t1; 
+-------------------------------------------+
| ST_AsText( ST_Collect(DISTINCT g) )       |
+-------------------------------------------+
| MULTIPOINT((1 1),(2 1),(3 1),(3 2),(3 3)) |
+-------------------------------------------+

　2件あった (2 1) の重複が排除された結果が得られました。

普通にグルーピング

　今度は、GROUP BY を明記する、普通のグルーピングを試してみます。

mysql> SELECT cat, ST_AsText( ST_Collect(g) ) FROM t1 GROUP BY cat;
+------+-------------------------------+
| cat  | ST_AsText( ST_Collect(g) )    |
+------+-------------------------------+
|    1 | MULTIPOINT((1 1),(3 1),(3 2)) |
|    2 | MULTIPOINT((2 1),(2 1))       |
|    3 | MULTIPOINT((3 3))             |
+------+-------------------------------+

　カテゴリ(cat)ごとに、グルーピングしてみました。カテゴリが等しいPOINT値ごとに集約されていることがわかります。 ST_Collect() は集約を行う関数ですから、結果として点がひとつだけになった場合でも、集合型である MULTIPOINT 型を返すのですね（cat=3）。

POINT 型以外も混在する場合

　ここで、新たに LINESTRING 型の値を持つ行を追加してみます。

INSERT INTO t1 VALUES (7, 3, ST_GeomFromText('LINESTRING(3 2, 1 0, 4 3)'));

　この行が加わった状態で、先ほどと同様に、カテゴリ(cat)ごとに ST_Collect() を使ってグルーピングしてみると、、、

mysql> SELECT cat, ST_AsText( ST_Collect(g) ) FROM t1 GROUP BY cat;
+------+--------------------------------------------------------+
| cat  | ST_AsText( ST_Collect(g) )                             |
+------+--------------------------------------------------------+
|    1 | MULTIPOINT((1 1),(3 1),(3 2))                          |
|    2 | MULTIPOINT((2 1),(2 1))                                |
|    3 | GEOMETRYCOLLECTION(POINT(3 3),LINESTRING(3 2,1 0,4 3)) |
+------+--------------------------------------------------------+

　カテゴリ3のものは、POINT と LINESTRING が混在した GEOMETRYCOLLECTION型になっていることがわかります。
　長くなるので実行例はひとつだけにしますが、すべてが LINESTRING型の時には MULTILINESTRINGに、すべてが POLYGON型の場合は MULTIPOLYGON型に、混在の時には GEOMETRYCOLLECTION型になります。

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

mysql> SELECT cat, ST_AsText( ST_Collect(g) ) FROM t1 GROUP BY cat;
+------+--------------------------------------------------------+
| cat  | ST_AsText( ST_Collect(g) )                             |
+------+--------------------------------------------------------+
|    1 | MULTIPOINT((1 1),(3 1),(3 2))                          |
|    2 | MULTIPOINT((2 1),(2 1))                                |
|    3 | GEOMETRYCOLLECTION(POINT(3 3),LINESTRING(3 2,1 0,4 3)) |
|    4 | MULTILINESTRING((1 2,3 4,5 6),(3 5,6 8))               |
+------+--------------------------------------------------------+

Window関数でも使える ST_Collect()

　ST_Collect() は集約関数のひとつですから、SUM() などと同じように Window関数でも使うことができます。　
　ST_AsText() があるぶんだけ少し複雑に見えますが、本質は Window関数の 「集約関数(カラム) OVER (PARTITION BY カラム) であり、この結果を我々人間にも分かるように変換してもらうために ST_AsText() をカマしているという形です。

 mysql> SELECT id, cat, ST_AsText(g), 
    ->        ST_AsText( ST_Collect(g) OVER (PARTITION BY cat) ) gw
    ->  FROM t1
    ->  ORDER BY id;
+------+------+-------------------------+--------------------------------------------------------+
| id   | cat  | ST_AsText(g)            | gw                                                     |
+------+------+-------------------------+--------------------------------------------------------+
|    1 |    1 | POINT(1 1)              | MULTIPOINT((1 1),(3 1),(3 2))                          |
|    2 |    2 | POINT(2 1)              | MULTIPOINT((2 1),(2 1))                                |
|    3 |    1 | POINT(3 1)              | MULTIPOINT((1 1),(3 1),(3 2))                          |
|    4 |    1 | POINT(3 2)              | MULTIPOINT((1 1),(3 1),(3 2))                          |
|    5 |    2 | POINT(2 1)              | MULTIPOINT((2 1),(2 1))                                |
|    6 |    3 | POINT(3 3)              | GEOMETRYCOLLECTION(POINT(3 3),LINESTRING(3 2,1 0,4 3)) |
|    7 |    3 | LINESTRING(3 2,1 0,4 3) | GEOMETRYCOLLECTION(POINT(3 3),LINESTRING(3 2,1 0,4 3)) |
|    8 |    4 | LINESTRING(1 2,3 4,5 6) | MULTILINESTRING((1 2,3 4,5 6),(3 5,6 8))               |
|    9 |    4 | LINESTRING(3 5,6 8)     | MULTILINESTRING((1 2,3 4,5 6),(3 5,6 8))               |
+------+------+-------------------------+--------------------------------------------------------+

　ということで、「データを格納し、検索して取り出す」というジオメトリデータ（空間データ、spatialデータ、GISデータ）の基本的な取り扱いから一歩進んで、データベースへの格納済みデータを MULTI* 型へとまとめることができるようになりました。

　相変わらず応用事例や実際の案件に関わっていない私ですので、正直なところ具体的な用途は今ひとつピンと来ていないのですが、ST_Collect() により行えるデータ操作の幅が一気に広がったような気がします。実際の案件で使ってみた方はぜひブログとかでお話を聞かせてください！　あるいはお手伝いさせてください(笑)

「MySQL8.0.24リリースノートでわいわい言う勉強会」開催しました

MySQLリリースノートでわいわい言う勉強会 8.0.24（Myリノベ8.0.24) を開催しました。

mysql.connpass.com

　MySQL 8.0.24 はMySQL 8.0シリーズの「メンテナンスリリースらしいメンテナンスリリース」で、新機能や機能の変更等は比較的おとなしめだったのですが、それでも参加者それぞれの視点から興味深い変更点を聞かせてもらうことができました。

MySQL 8.0.24 の Change log:
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.24 (2021-04-20, General Availability)

MySQL 8.0.24 でのパラメタ類の変更点(サンプル公開):
MySQL Parameters Dashboard (8.0.24)

エラーメッセージが増えた

　@mita2 さんからの発表。MySQLの接続が切れてしまった時の原因は様々ですが、そのエラーメッセージが今までよりもきめ細かく表示されるようになったというお話です（エラーコード 04031 の「アクティブじゃないから切られちゃったよ」の新設など。 wait_timeout や　interactive_timeout あたりの設定が影響）。
speakerdeck.com

　即座に @tom__bo さんが、本当に 4031 が返ってくることを、パケットを覗いて確認！ このインタラクティブさが、いいですね。

interactive_timeoutだけど4031が返ってくるようになってた pic.twitter.com/yWJE0fahHm

— :tom__bo: (@tom__bo) 2021年5月7日

DBUG_ASSERT が assert に

　「リリースノートの話ではないのですが・・」と前置きしてお話された @NayutaYanagisaw さん。一瞬「ん？？リリースノートではないとは何事？」と思ったのですが、ソースコードの話でした。もっと深い！(笑)
　MySQL 8.0.24 では、今までソースコード中で使われていた DBUG_ASSERT マクロが、すべて 標準の assert に置き換えられたとのこと。へぇぇぇ。少し前の話ですが MySQL 8.0.18 の時には、DBUG_ENTER と DBUG_RETURN (関数の入り口と出口に書かれていた)が、DBUG_TRACE(関数の入り口だけに書けば良い)に置き換わったとか。　特に 8.0.24 のほうの変更はリリースノートにも書かれていない、ソースコードを追っている人しか知らないような貴重な情報でした。
　詳しくは以下のブログに。
nayuta-yanagisawa.hatenablog.com

Spatial(GIS)関数の追加

　私から。
ST_LineInterpolatePoint(), ST_LineInterpolatePoints(), ST_PointAtDistance() の各関数、ST_Collect() 関数、そして、CAST() や CONVERT()関数の spatilal 対応について紹介しました。最初の３つの関数は私の以下のブログで紹介しています。それ以外の関数は追々、書こうと思います。
MySQL8.0.24の新しいGIS関数(1)～LINESTRINGの経路点を求める～ - sakaikの日々雑感～(T)編
MySQL8.0.24の新しいGIS関数(2)～ST_LineInterpolatePoints()を試す - sakaikの日々雑感～(T)編

　個人的印象として、今回 CAST() 関数に色々加わっていて、このへん、問題動作の巣窟になりそうな予感がしています。大丈夫かな。。

MySQL 8.0.25

　私から。次のバージョンナンバーである MySQL 8.0.25。緊急リリースとして定期（次回7月）よりも前に出るんじゃないの？という予測について紹介しました。

　MySQLのマニュアル中には「この機能は 8.0.xxで追加されました」のようにバージョン番号が入っている部分がいくつもあります。将来リリースされるものについても（おそらく内部での開発が確定されたタイミングで）マニュアルに掲載されているのですが、先週のあるタイミングで、8.0.25についての言及がすべてひとつずつ後ろにずれる修正がなされたのです。その結果、現在のマニュアル中に MySQL 8.0.25 についての言及はゼロ。というのが左側の表で示していること。
　そして、このイベントの当日に加えられた修正が右のもの。MySQL 8.0.27 に関する言及の多くが 8.0.24 に変更されたものです。これは何かというと、そのバージョンで加えられた機能のほかに、実行例やファイル名などにもバージョン番号が含まれています。一昨日までのマニュアルではこれらのバージョンは「マニュアル中の最先端」のもの、つまり 8.0.27 となっていました。これが、一気に「現在リリースされている最新バージョン」に変更されたという事です。　おそらくマニュアルの元ファイルでは {CURRENT_VERSION} みたいな記述になっていて、その根拠となる数字（のポリシー）が変更になったのかなと想像しています。

　その他、いろいろな話を皆さんに教えていただきました。今回も勉強になり、皆さんありがとうございました！

今後について

　MySQL 8.0.20 から5回開催してきた 「リリースノートでわいわい言う勉強会」ですが、少し大きめに変更をしてみようと考えています。

（１）幹事を持ち回りに： 初回からずっと私が企画／進行役をやらせてもらっていましたが、率直に言って自分の「場作り」の限界を感じています。もっと自由に発言しあう場にしたいのに、「コンテンツ作り」を意識してか、どうしても場をカタくしてしまっている認識です。そこで、皆さんにお願いです。「次回以降、誰か幹事やってください！」
　いろんな人の、いろんな進行方法があって良いと思っていて、必ずしも初回から「大成功」する必要もないですから、あなたの思う「リリースノート勉強会の空気」を目指して、トライしてみていただけたらと思います。幹事というと少し固そうに感じる人もいるかもしれませんが、私の感覚としては「日直」または「班長さん」くらいの感覚です。リノベの場、リノベの雰囲気、リノベで得られる情報とかが好きな人は、ぜひ協力していただけたらと願います。ぐるぐる回るようになってきたら、私も「ときどきやる班長さんのひとり」として進行に手を挙げたいと思います。
　細かいことは個別相談ですが、最低限「日程決定＋当日進行」をしていただければ。　connpassのベース準備、会場(Zoom)用意は、こちらでやります。　今回の会の中では唐突だったこともあってか、次回幹事は決まりませんでしたので、またリリースが近くなった頃にTwitter の #mysql_jp で呼びかける予定です。

（２）動画の公開をやめます： ごく少数の方から公開動画に感謝の言葉もいただいていますが、絶対数として手間に見合う閲覧数を獲得できない状態で、分かりやすく言えばコスパが悪い、あるいは、苦労が報われないない状態。ということで、今回を最後に、リリースノート勉強会の動画公開は行わないことにします。（今回ぶん、撮れていた動画のサイズがなにかおかしいのですが、まぁ一応公開することにします）
　動画の公開をしないことで、幹事（班長さん、日直さん）をやりやすくなるのかなという面も期待しているので、(1)(2)併せて、何卒よろしくお願い申し上げる次第です。

当日動画：
www.youtube.com

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