ユーザ用ツール

サイト用ツール


mysql:mysql8_gis_bug

MySQL 8のGIS機能(ST_Contains)のバグ

検証環境

  • macOS Catalina 10.15.7
  • MySQL 8.0.22 Source distribution

2021-07-08追記:以下の環境でもバグを確認

  • macOS BigSur 11.4
  • MySQL 8.0.25 - Source distribution

検証方法

次のSQLファイルmysql8_bug_repro_geojson.sqlをMySQLで実行する。

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);

このSQLファイルでは、一つのPolygon @gと三つのPoint @g1@g2@g3を定義している。@gは国土数値情報(行政区域)から抜粋した静岡県賀茂郡南伊豆町に属する島嶼の一つで、以下の区域を表す(地理院地図の作図機能を利用して描画)。 上図の十字線を中心とする拡大図を示す。赤の部分がPolygon @gを表し、十字線の位置がPoint @g1を表す。したがって、Polygon @gはPoint @g1を含んでいる。 Point @g2@g3はほとんど同じ座標(小数点以下5桁まで一致)で、山梨県大月市の地点を表す。以下のそれぞれの図の十字線がPoint @g2@g3の位置を表す(当然、ほとんど同じ位置を示す)。この2地点はPolygon @gに含まれない。 mysql8_bug_repro_geojson.sqlを実行した結果は、下記のようになる。なお、比較のため、MySQL 5.7とMariaDB 10で実行した結果も示す。MySQL 5.7とMariaDB 10で実行する場合は、mysql8_bug_repro_geojson.sqlの一部を以下のように書き換える必要がある。

-- MySQL 8
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');
-- MySQL 5.7 / MariaDB 10
SET @g1=ST_GeomFromText(@p1,4326);
SET @g2=ST_GeomFromText(@p2,4326);
SET @g3=ST_GeomFromText(@p3,4326);

実行結果のまとめ:太字が誤答で、バグの存在を示している。

正答 MySQL 8.0 MySQL 5.7 MariaDB 10
ST_Contains(@g,@g1) 1 1 1 1
ST_Contains(@g,@g2) 0 1 0 0
ST_Contains(@g,@g3) 0 0 0 0

なお、PolygonをWKTで定義した場合(以下の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);
mysql/mysql8_gis_bug.txt · 最終更新: 2021/07/07 21:24 by anineco