ユーザ用ツール

サイト用ツール


mysql:mysql8_gis_bug

差分

このページの2つのバージョン間の差分を表示します。

この比較画面へのリンク

両方とも前のリビジョン前のリビジョン
次のリビジョン
前のリビジョン
mysql:mysql8_gis_bug [2022/01/30 02:51] – [検証環境] aninecomysql:mysql8_gis_bug [2023/12/23 11:27] (現在) – [検証方法] anineco
行 1: 行 1:
 ====== MySQL 8のGIS機能(ST_Contains)のバグ ====== ====== MySQL 8のGIS機能(ST_Contains)のバグ ======
 ===== 検証環境 ===== ===== 検証環境 =====
-  * macOS Catalina 10.15.7 +  * macOS Sonoma 14.2.1 
-  * MySQL 8.0.22 Source distribution +  * MySQL 8.0.35 
-2021-07-08追記:以下の環境でもバグを確認 +2023-05-21: 
-  * macOS BigSur 11.4 +  * 公開 
-  * MySQL 8.0.25 Source distribution +2023-12-23
-2021-11-10追記:以下の環境でもバグを確認 +  * 改訂 
-  * macOS Monterey 12.0.1 +
-  * MySQL 8.0.27 Source distribution +
-2022-01-30追記 +
-  * https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html の「Note For upgrades from earlier versions of MySQL, you should recreate spatial indexes in tables that have them.」の記述に従って、インデクスを作り直してみたが、バグは変わらず。 +
-  * MBRContains関数の実行結果を追加。こちらは正常に動作する。 +
-  * MBRContains関数とST_Contains関数のANDをとれば、バグが回避できるかも。+
 ===== 検証方法 ===== ===== 検証方法 =====
-次のSQLファイルmysql8_bug_repro_geojson.sqlをMySQLで実行する。 +SQLファイル[[https://anineco.nyanta.jp/easy-rgeocode-jpn/mysql8bug.sql.txt|mysql8bug.sql]]をMySQLで実行する。以下は、このファイルを説明するた一部を抜粋して表示したのである。
-<code sql mysql8_bug_repro_geojson.sql> +
-SET @area='{"type": "Polygon", "coordinates": [[[138.85805245, 34.61228747], [138.8580532, 34.61228431], [138.8580546, 34.61227953], [138.85805599, 34.61227475], [138.85805793, 34.61226843], [138.85806105, 34.61225887], [138.85806514, 34.61224605], [138.85806719, 34.61223956], [138.85806859, 34.61223459], [138.85806997, 34.61222954], [138.85807073, 34.61222612], [138.85807158, 34.61222259], [138.85807222, 34.61221908], [138.85807287, 34.61221547], [138.85807328, 34.61221168], [138.8580737, 34.6122078], [138.8580738, 34.61220582], [138.8580739, 34.61220384], [138.85807389, 34.61220231], [138.85807388, 34.61219915], [138.85807376, 34.61219599], [138.85807341, 34.61219284], [138.85807319, 34.6121905], [138.85807285, 34.61218824], [138.85807241, 34.61218608], [138.85807185, 34.61218392], [138.8580713, 34.61218194], [138.85807075, 34.61218068], [138.85807031, 34.61217941], [138.85806975, 34.61217833], [138.85806909, 34.61217726], [138.85806844, 34.61217636], [138.85806768, 34.61217546], [138.8580669, 34.61217465], [138.85806603, 34.61217402], [138.85806559, 34.61217375], [138.85806504, 34.61217358], [138.8580646, 34.61217331], [138.85806406, 34.61217313], [138.85806351, 34.61217304], [138.85806254, 34.61217286], [138.85806156, 34.61217277], [138.85806047, 34.61217286], [138.85805926, 34.61217305], [138.85805807, 34.61217323], [138.85805687, 34.6121736], [138.85805567, 34.61217396], [138.85805447, 34.61217441], [138.85805317, 34.61217496], [138.85805187, 34.61217559], [138.85805066, 34.61217623], [138.85804936, 34.61217696], [138.85804816, 34.61217777], [138.85804686, 34.61217859], [138.85804567, 34.6121794], [138.85804446, 34.61218031], [138.85804339, 34.6121813], [138.8580423, 34.61218221], [138.85804121, 34.6121832], [138.85804023, 34.61218428], [138.85803926, 34.61218528], [138.85803839, 34.61218636], [138.85803763, 34.61218745], [138.85803698, 34.61218862], [138.85803633, 34.6121897], [138.8580359, 34.61219069], [138.85803558, 34.61219169], [138.85803525, 34.61219277], [138.85803482, 34.61219422], [138.85803462, 34.61219584], [138.85803451, 34.61219737], [138.85803441, 34.61219945], [138.85803442, 34.61220197], [138.85803444, 34.61220648], [138.85803434, 34.61220846], [138.85803424, 34.6122099], [138.85803392, 34.61221135], [138.85803359, 34.6122127], [138.85803327, 34.6122136], [138.85803294, 34.61221441], [138.85803262, 34.61221504], [138.85803208, 34.61221587], [138.85803154, 34.61221677], [138.85803067, 34.61221785], [138.85802969, 34.61221894], [138.85802839, 34.61222029], [138.85802676, 34.61222183], [138.85802296, 34.61222553], [138.85802156, 34.61222689], [138.85802057, 34.61222797], [138.85801917, 34.61222978], [138.85801851, 34.61223069], [138.85801786, 34.61223168], [138.85801721, 34.61223259], [138.85801614, 34.61223457], [138.85801527, 34.61223656], [138.85801441, 34.61223863], [138.85801364, 34.6122407], [138.85801301, 34.61224278], [138.85801236, 34.61224495], [138.85801193, 34.61224711], [138.85801118, 34.61225036], [138.85801065, 34.61225369], [138.85801023, 34.61225704], [138.8580097, 34.61226145], [138.85800897, 34.61226822], [138.85800793, 34.61227831], [138.85800772, 34.61228048], [138.8580075, 34.61228166], [138.85800718, 34.61228382], [138.85800645, 34.61228914], [138.85800464, 34.61230078], [138.85800401, 34.61230502], [138.85800358, 34.61230925], [138.85800338, 34.6123124], [138.85800318, 34.61231557], [138.85800329, 34.61231881], [138.85800341, 34.61232088], [138.85800354, 34.61232304], [138.85800388, 34.61232521], [138.85800432, 34.61232746], [138.85800488, 34.61232962], [138.85800554, 34.61233187], [138.85800642, 34.61233413], [138.85800686, 34.61233521], [138.85800718, 34.61233575], [138.85800796, 34.61233737], [138.85800873, 34.61233863], [138.85800949, 34.61233988], [138.85801038, 34.61234105], [138.85801104, 34.61234195], [138.8580118, 34.61234277], [138.85801257, 34.61234358], [138.85801344, 34.6123442], [138.85801398, 34.61234465], [138.85801464, 34.61234501], [138.85801531, 34.61234528], [138.85801573, 34.61234546], [138.85801661, 34.61234564], [138.85801748, 34.61234581], [138.85801835, 34.61234572], [138.85801934, 34.61234563], [138.85802021, 34.61234544], [138.85802118, 34.61234517], [138.85802206, 34.61234481], [138.85802304, 34.61234435], [138.85802402, 34.61234381], [138.85802499, 34.61234327], [138.85802586, 34.61234254], [138.85802728, 34.61234155], [138.85802869, 34.61234037], [138.8580301, 34.61233902], [138.85803152, 34.61233775], [138.85803282, 34.61233631], [138.85803412, 34.61233486], [138.8580353, 34.61233331], [138.8580365, 34.61233187], [138.85803759, 34.61233034], [138.85803802, 34.61232979], [138.85803921, 34.61232799], [138.8580403, 34.61232618], [138.85804137, 34.61232438], [138.85804224, 34.61232266], [138.85804311, 34.61232086], [138.85804387, 34.61231914], [138.85804494, 34.61231652], [138.85804591, 34.612314], [138.85804667, 34.61231147], [138.85804742, 34.61230895], [138.85804827, 34.6123056], [138.85804935, 34.61230145], [138.85805074, 34.61229487], [138.85805202, 34.6122891], [138.85805223, 34.61228829], [138.85805245, 34.61228747]]]}'; +
-SET @g=ST_GeomFromGeoJSON(@area,1,4326); +
-SET @p1='POINT(138.858039 34.612259)'; +
-SET @p2='POINT(138.858056 35.684722)'; +
-SET @p3='POINT(138.858055 35.684722)'; +
-SET @g1=ST_GeomFromText(@p1,4326,'axis-order=long-lat'); +
-SET @g2=ST_GeomFromText(@p2,4326,'axis-order=long-lat'); +
-SET @g3=ST_GeomFromText(@p3,4326,'axis-order=long-lat'); +
-SELECT ST_Contains(@g,@g1),ST_Contains(@g,@g2),ST_Contains(@g,@g3); +
-</code> +
-このSQLファイルでは、一つのPolygon ''@g''と三つのPoint ''@g1''、''@g2''、''@g3''定義してい。''@g''は国土数値情報(行政区域)から抜粋し静岡県賀茂郡南伊豆町属する島嶼のつで、以下の区域を表す(地理院地図の作図機能を利用して描画)。 +
-{{ :mysql:area_location.png?nolink&600 |}} +
-上図の十字線を中心とする拡大図を示す。赤の分がPolygon ''@g''、十字線の位置がPoint ''@g1''を表す。したがっ、Polygon ''@g''はPoint ''@g1''を含んでいる。 +
-{{ :mysql:point_1_and_area.png?nolink&600 |}} +
-Point ''@g2''と''@g3''はほとんど同じ座標(小数点以下5桁まで一致)で、山梨県大月市の地点をす。以下のそれぞれの図の十字線がPoint ''@g2''と''@g3''の位置を表す(当然、ほとんど同じ位置をす)。この2地点はPolygon ''@g''に含まれない。 +
-{{ :mysql:point_2.png?nolink&600 |}} +
-{{ :mysql:point_3.png?nolink&600 |}} +
-mysql8_bug_repro_geojson.sqlを実行した結果は、下記ようになる。なお、比較のため、MySQL 5.7とMariaDB 10実行した結果も示す。MySQL 5.7とMariaDB 10で実行する場合は、mysql8_bug_repro_geojson.sqlの一部を以下のように書き換える必要がある。+
 <code sql> <code sql>
--- MySQL 8 +# 国土数値情報 行政区域(2022)岐阜県美濃市 
-SET @g1=ST_GeomFromText(@p1,4326,'axis-order=long-lat'); +SET @wkt='POLYGON((136.881404111811 35.6522255863419,136.881898274886 35.6520964140184, 
-SET @g2=ST_GeomFromText(@p2,4326,'axis-order=long-lat'); +(中略) 
-SET @g3=ST_GeomFromText(@p3,4326,'axis-order=long-lat'); +,136.880985227387 35.652280026802,136.881404111811 35.6522255863419))'; 
--- MySQL 5.7 / MariaDB 10 +SET @area=ST_GeomFromText(@wkt,4326/*!80003 ,'axis-order=long-lat' */); 
-SET @g1=ST_GeomFromText(@p1,4326); +# 天王山(岐阜県美濃市、標高537.6m) 
-SET @g2=ST_GeomFromText(@p2,4326); +SET @p0=ST_GeomFromText('POINT(136.866944 35.561111)',4326/*!80003 ,'axis-order=long-lat' */); 
-SET @g3=ST_GeomFromText(@p3,4326);+# 経度 -1/3600 付近 
 +SET @p1=ST_GeomFromText('POINT(136.866666 35.561111)',4326/*!80003 ,'axis-order=long-lat' */); 
 +SET @p2=ST_GeomFromText('POINT(136.866667 35.561111)',4326/*!80003 ,'axis-order=long-lat' */); 
 +SET @p3=ST_GeomFromText('POINT(136.866668 35.561111)',4326/*!80003 ,'axis-order=long-lat' */); 
 +# 期待される結果は全て 1 
 +SELECT ST_Contains(@area,@p0),ST_Contains(@area,@p1),ST_Contains(@area,@p2),ST_Contains(@area,@p3);
 </code> </code>
 +このSQLファイルでは、1つのPolygon ''@area''と4つのPoint ''@p0''、''@p1''、''@p2''、''@p3''を定義している。''@area''は国土数値情報の行政区域(2022年版)から抜粋した岐阜県美濃市のPolygonデータ、''@p0''は美濃市内の天王山(標高537.6m)の山頂を表すPointデータ、''@p1''、''@p2''、''@p3''は山頂から西へ約1秒の地点で、経度(単位:度)の小数点以下6桁目のみが異なる。この4点は明らかに美濃市内にある。実際に、Leafletを用いて地理院地図上に表示した結果を下記に示す。左上の[⌖]ボタンを押すと、''@area''と''@p2''辺りの位置が表示される。
 +{{url>https://anineco.github.io/easy-rgeocode-jpn/example.html?lon=136.866667&lat=35.561111 100%,480}}
 +''mysql8bug.sql''を実行した結果は、下の表のようになる。なお、比較のため、MySQL 5.7とMariaDB 10で実行した結果も示す。
 +
 実行結果のまとめ:**太字**が誤答で、バグの存在を示している。 実行結果のまとめ:**太字**が誤答で、バグの存在を示している。
-^                     ^ 正答 ^ MySQL 8.0 ^ MySQL 5.7 ^ MariaDB 10 ^ +^                     ^ 正答 ^ MySQL 8.0.35 ^ MySQL 5.7.40 ^ MariaDB 10.11.2 
-| ST_Contains(@g,@g1) |  1  |  1  |  1  |  1  | +| ST_Contains(@area,@p0) |  1  |  1  |  1  |  1  | 
-| ST_Contains(@g,@g2) |    **1**  |   |   | +| ST_Contains(@area,@p1) |  1  |  1  |  1  |  1  | 
-| ST_Contains(@g,@g3) |   |   |   |   |+| ST_Contains(@area,@p2) |  1   **0**  |   |   | 
 +| ST_Contains(@area,@p3) |   |   |   |   | 
 + 
 +なお、経度を136.866667に固定し、緯度を連続的に振ってやると、かなりの広範囲で同じバグが発生する。
  
-なお、PolygonをWKTで定義した場合(以下のSQL)でも、実行結果は変わらない。 
-<code sql mysql8_bug_repro_wkt.sql> 
-SET @area='POLYGON((138.85805245 34.61228747,138.8580532 34.61228431,138.8580546 34.61227953,138.85805599 34.61227475,138.85805793 34.61226843,138.85806105 34.61225887,138.85806514 34.61224605,138.85806719 34.61223956,138.85806859 34.61223459,138.85806997 34.61222954,138.85807073 34.61222612,138.85807158 34.61222259,138.85807222 34.61221908,138.85807287 34.61221547,138.85807328 34.61221168,138.8580737 34.6122078,138.8580738 34.61220582,138.8580739 34.61220384,138.85807389 34.61220231,138.85807388 34.61219915,138.85807376 34.61219599,138.85807341 34.61219284,138.85807319 34.6121905,138.85807285 34.61218824,138.85807241 34.61218608,138.85807185 34.61218392,138.8580713 34.61218194,138.85807075 34.61218068,138.85807031 34.61217941,138.85806975 34.61217833,138.85806909 34.61217726,138.85806844 34.61217636,138.85806768 34.61217546,138.8580669 34.61217465,138.85806603 34.61217402,138.85806559 34.61217375,138.85806504 34.61217358,138.8580646 34.61217331,138.85806406 34.61217313,138.85806351 34.61217304,138.85806254 34.61217286,138.85806156 34.61217277,138.85806047 34.61217286,138.85805926 34.61217305,138.85805807 34.61217323,138.85805687 34.6121736,138.85805567 34.61217396,138.85805447 34.61217441,138.85805317 34.61217496,138.85805187 34.61217559,138.85805066 34.61217623,138.85804936 34.61217696,138.85804816 34.61217777,138.85804686 34.61217859,138.85804567 34.6121794,138.85804446 34.61218031,138.85804339 34.6121813,138.8580423 34.61218221,138.85804121 34.6121832,138.85804023 34.61218428,138.85803926 34.61218528,138.85803839 34.61218636,138.85803763 34.61218745,138.85803698 34.61218862,138.85803633 34.6121897,138.8580359 34.61219069,138.85803558 34.61219169,138.85803525 34.61219277,138.85803482 34.61219422,138.85803462 34.61219584,138.85803451 34.61219737,138.85803441 34.61219945,138.85803442 34.61220197,138.85803444 34.61220648,138.85803434 34.61220846,138.85803424 34.6122099,138.85803392 34.61221135,138.85803359 34.6122127,138.85803327 34.6122136,138.85803294 34.61221441,138.85803262 34.61221504,138.85803208 34.61221587,138.85803154 34.61221677,138.85803067 34.61221785,138.85802969 34.61221894,138.85802839 34.61222029,138.85802676 34.61222183,138.85802296 34.61222553,138.85802156 34.61222689,138.85802057 34.61222797,138.85801917 34.61222978,138.85801851 34.61223069,138.85801786 34.61223168,138.85801721 34.61223259,138.85801614 34.61223457,138.85801527 34.61223656,138.85801441 34.61223863,138.85801364 34.6122407,138.85801301 34.61224278,138.85801236 34.61224495,138.85801193 34.61224711,138.85801118 34.61225036,138.85801065 34.61225369,138.85801023 34.61225704,138.8580097 34.61226145,138.85800897 34.61226822,138.85800793 34.61227831,138.85800772 34.61228048,138.8580075 34.61228166,138.85800718 34.61228382,138.85800645 34.61228914,138.85800464 34.61230078,138.85800401 34.61230502,138.85800358 34.61230925,138.85800338 34.6123124,138.85800318 34.61231557,138.85800329 34.61231881,138.85800341 34.61232088,138.85800354 34.61232304,138.85800388 34.61232521,138.85800432 34.61232746,138.85800488 34.61232962,138.85800554 34.61233187,138.85800642 34.61233413,138.85800686 34.61233521,138.85800718 34.61233575,138.85800796 34.61233737,138.85800873 34.61233863,138.85800949 34.61233988,138.85801038 34.61234105,138.85801104 34.61234195,138.8580118 34.61234277,138.85801257 34.61234358,138.85801344 34.6123442,138.85801398 34.61234465,138.85801464 34.61234501,138.85801531 34.61234528,138.85801573 34.61234546,138.85801661 34.61234564,138.85801748 34.61234581,138.85801835 34.61234572,138.85801934 34.61234563,138.85802021 34.61234544,138.85802118 34.61234517,138.85802206 34.61234481,138.85802304 34.61234435,138.85802402 34.61234381,138.85802499 34.61234327,138.85802586 34.61234254,138.85802728 34.61234155,138.85802869 34.61234037,138.8580301 34.61233902,138.85803152 34.61233775,138.85803282 34.61233631,138.85803412 34.61233486,138.8580353 34.61233331,138.8580365 34.61233187,138.85803759 34.61233034,138.85803802 34.61232979,138.85803921 34.61232799,138.8580403 34.61232618,138.85804137 34.61232438,138.85804224 34.61232266,138.85804311 34.61232086,138.85804387 34.61231914,138.85804494 34.61231652,138.85804591 34.612314,138.85804667 34.61231147,138.85804742 34.61230895,138.85804827 34.6123056,138.85804935 34.61230145,138.85805074 34.61229487,138.85805202 34.6122891,138.85805223 34.61228829,138.85805245 34.61228747))'; 
-SET @g=ST_GeomFromText(@area,4326,'axis-order=long-lat'); 
-SET @p1='POINT(138.858039 34.612259)'; 
-SET @p2='POINT(138.858056 35.684722)'; 
-SET @p3='POINT(138.858055 35.684722)'; 
-SET @g1=ST_GeomFromText(@p1,4326,'axis-order=long-lat'); 
-SET @g2=ST_GeomFromText(@p2,4326,'axis-order=long-lat'); 
-SET @g3=ST_GeomFromText(@p3,4326,'axis-order=long-lat'); 
-SELECT ST_Contains(@g,@g1),ST_Contains(@g,@g2),ST_Contains(@g,@g3); 
-</code> 
mysql/mysql8_gis_bug.1643478677.txt.gz · 最終更新: 2022/01/30 02:51 by anineco