MySQL Bugs: #92159: st_intersects with srid 4326 unexpected values as a result

Bug #92159	st_intersects with srid 4326 unexpected values as a result
Submitted:	23 Aug 2018 13:37	Modified:	27 Aug 2018 8:41
Reporter:	w vd vorm	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S1 (Critical)
Version:	5.7/8.0	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
a POLYGON field used in st_intersect in the WHERE clause  changes the output value of this field if this same POLYGON field is also used as a SELECT field.

How to repeat:
CREATE TABLE  `mysql_bug` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `bounds` polygon NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  SPATIAL KEY `boundskey` (`bounds`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

INSERT INTO `mysql_bug` (`bounds`)VALUES
  ( ST_GeomFromText( "POLYGON((5.0631967879877395 52.30958883730479,5.062897243236728 52.30981069405652,5.060663619836996 52.31171742201622,5.060458339895537 52.31217777088448,5.060284148064529 52.31247876729871,5.060165132441595 52.31274435317078,5.06003152167485 52.312983221901234,5.059805155158196 52.31330940446082,5.059304042164561 52.31392558055251,5.060005343926405 52.314545303521285,5.060304213705726 52.3148490109568,5.060675308468418 52.31526011347344,5.061238403046716 52.31568838973036,5.061598482994666 52.315944107028336,5.061895025121778 52.31620970249339,5.062008825118752 52.31634407560335,5.062096460863965 52.31647529576293,5.062164867056544 52.316616589626996,5.06220768778821 52.31675509787845,5.062680735993036 52.31721830797045,5.062950067003499 52.31755905963844,5.063197706163205 52.318055333528555,5.0632 52.31806,5.0632 52.309586458333335,5.0631967879877395 52.30958883730479))",4326)),
  ( ST_GeomFromText( "POLYGON((5.058233599374992 52.31056728726207,5.058733874116929 52.31073352357895,5.059287704548174 52.31095717852761,5.059998382308607 52.31130862423394,5.06030715507687 52.31148861459802,5.060663619836996 52.31171742201622,5.062897243236728 52.30981069405652,5.0631967879877395 52.30958883730479,5.0632 52.309586458333335,5.06282 52.30963,5.0586598 52.3104828,5.0585473 52.3105045,5.0582294 52.3105659,5.058233599374992 52.31056728726207))",4326)),
  ( ST_GeomFromText( "POLYGON((5.0631953485236085 52.30958407884961,5.062659214062694 52.309312162386775,5.059507870604076 52.30791345981135,5.059344917879849 52.30848260932382,5.05915853902347 52.30894024045263,5.058857247356365 52.30953453118317,5.058231314941121 52.31056280634356,5.0582294 52.3105659,5.0585473 52.3105045,5.0586598 52.3104828,5.06282 52.30963,5.0632 52.309586458333335,5.0631953485236085 52.30958407884961))",4326)),
  ( ST_GeomFromText( "POLYGON((5.063194767604468 52.29547420949254,5.062690227009933 52.2958801134462,5.062512767511783 52.296042640973134,5.062898216319557 52.29620300078889,5.0631973122168565 52.2963497094025,5.0632 52.29635103448276,5.0632 52.29547,5.063194767604468 52.29547420949254))",4326)),
  ( ST_GeomFromText( "POLYGON((5.063198276816225 52.29635235935326,5.063036951986327 52.29647660497469,5.062786504184279 52.29672239004589,5.062585928521267 52.29702009729457,5.062457709836123 52.29735759224395,5.062349910451017 52.297787167501156,5.062259579011925 52.29805827625105,5.062073356104748 52.2985093152599,5.061932227268367 52.29880606931143,5.061002880058911 52.30031243043994,5.060596067282311 52.300854746070016,5.059910028600934 52.301669821934205,5.059526604073613 52.30206323206672,5.05928523074182 52.302289984795685,5.058997521450486 52.302533503117225,5.058576811725638 52.302860178307974,5.058467527036433 52.30343803237695,5.058286371885684 52.30417587505969,5.058174301748714 52.304531269914456,5.058034713627738 52.30490108422972,5.057889905233348 52.30522003123764,5.057585392364621 52.30578704651199,5.057393702087933 52.30610599189801,5.058153225206382 52.30665532442907,5.058612724343904 52.30702360112013,5.059020809381616 52.3073995758205,5.059507870604076 52.30791345981135,5.062659214062694 52.309312162386775,5.0631953485236085 52.30958407884961,5.0632 52.309586458333335,5.0632 52.29635103448276,5.063198276816225 52.29635235935326))",4326)),
  ( ST_GeomFromText( "POLYGON((5.047249914478091 52.308354523166905,5.047938086633207 52.30873231959459,5.050354476360618 52.30835964109198,5.050717749135228 52.30826544112054,5.051031985714968 52.30816453199174,5.051626227250136 52.30794227326824,5.052012115031732 52.307773802107164,5.05241616085965 52.307571953184066,5.052732190563674 52.30739792798974,5.053886104091538 52.306696194665356,5.056979771957886 52.30620295050775,5.0573852926968135 52.30611020235319,5.057568563667306 52.30581693646836,5.057887018950809 52.30522582523537,5.058027593207839 52.30491832410284,5.058168305577337 52.304547626279636,5.0582820780825735 52.304191864256126,5.058467527036433 52.30343803237695,5.058576811725638 52.302860178307974,5.058997521450486 52.302533503117225,5.05928523074182 52.302289984795685,5.059526604073613 52.30206323206672,5.0599100518476545 52.301669797478134,5.060596255688362 52.3008545118447,5.061002880058911 52.30031243043994,5.061932227268367 52.29880606931143,5.062073356104748 52.2985093152599,5.062259579011925 52.29805827625105,5.062349910451017 52.297787167501156,5.062457709836123 52.29735759224395,5.062585928521267 52.29702009729457,5.062786504184279 52.29672239004589,5.063036951986327 52.29647660497469,5.063198276816225 52.29635235935326,5.0632 52.29635103448276,5.06299 52.29638,5.06275 52.29644,5.06253 52.29652,5.06209 52.29679,5.06187 52.29704,5.06176 52.2972,5.06155 52.29761,5.06141 52.29781,5.0611 52.29816,5.06087 52.29838,5.05951 52.29932,5.0590178 52.2995797,5.0582984 52.2999041,5.0578958 52.3000369,5.0576507 52.3000718,5.0566854 52.3000713,5.0558245 52.3001449,5.0554765 52.3002194,5.0552443 52.300282,5.0550202 52.3003718,5.0547378 52.3005872,5.05463 52.30074,5.05422 52.30135,5.05417 52.30192,5.05394 52.30259,5.05363 52.30321,5.05349 52.30339,5.05316 52.30366,5.05294 52.3038,5.05264 52.30394,5.05154 52.30434,5.0513 52.30444,5.0500957 52.3050594,5.0499 52.30516,5.04961 52.30549,5.04954 52.30566,5.04934 52.30593,5.04916 52.30611,5.04897 52.30635,5.04895 52.30649,5.04898 52.30666,5.0493 52.30693,5.04946 52.30711,5.04948 52.30741,5.04933 52.3076,5.04918 52.3077,5.0477 52.30821,5.0472419 52.3083501,5.047249914478091 52.308354523166905))",4326)),
  ( ST_GeomFromText( "POLYGON((5.0631973122168565 52.2963497094025,5.062898216319557 52.29620300078889,5.062512767511783 52.296042640973134,5.062303937571084 52.296093276884925,5.062083274337445 52.29617112218673,5.061963027426664 52.29623020630378,5.06157915765964 52.2964935119331,5.061318320432491 52.29673249884841,5.0609982558500635 52.29722021455853,5.060805468104809 52.297440395189696,5.060633493419741 52.2976007215815,5.060178808756737 52.29789713822554,5.059143347649424 52.29841544647449,5.058574137290188 52.29873369966339,5.057866884055918 52.29907479881208,5.057474635301505 52.29922965831134,5.056639028816416 52.29933688851925,5.055582979211261 52.29959578529849,5.055186316482257 52.29970568687553,5.05504478851488 52.29974842408615,5.0548650752556945 52.299830579925406,5.054568251922504 52.299992756050514,5.054440859231443 52.30007724644895,5.054084272548518 52.30035406595318,5.053748278162423 52.30069639630447,5.053542683396387 52.30124471796993,5.053465400722221 52.30186227085317,5.053304198737945 52.30244193894316,5.053161472402754 52.30271311232375,5.053036164263916 52.302876883126004,5.052863435258825 52.303060199969956,5.052628512581061 52.30321010140145,5.052319335348239 52.30336934881136,5.051463868742208 52.30370170390886,5.0507979664019595 52.30400733066453,5.049430470444971 52.304748294758035,5.048839255674031 52.30500371999042,5.048554706604833 52.30509982781283,5.048048485605287 52.30524816497758,5.048062718283396 52.30534158421866,5.048048369580226 52.305506328613255,5.0479329115724205 52.30581878839221,5.047827054975874 52.30600415295253,5.0474821824001985 52.306480312501634,5.047651549508271 52.306713424670576,5.047920916851114 52.30718079315751,5.047721145364622 52.30736595614733,5.047418942662309 52.30756922271076,5.047381116943526 52.30761264883887,5.047241961905599 52.30834978237947,5.0472419 52.3083501,5.0477 52.30821,5.04918 52.3077,5.04933 52.3076,5.04948 52.30741,5.04946 52.30711,5.0493 52.30693,5.04898 52.30666,5.04895 52.30649,5.04897 52.30635,5.04916 52.30611,5.04934 52.30593,5.04954 52.30566,5.04961 52.30549,5.0499 52.30516,5.0500957 52.3050594,5.0513 52.30444,5.05154 52.30434,5.05264 52.30394,5.05294 52.3038,5.05316 52.30366,5.05349 52.30339,5.05363 52.30321,5.05394 52.30259,5.05417 52.30192,5.05422 52.30135,5.05463 52.30074,5.0547378 52.3005872,5.0550202 52.3003718,5.0552443 52.300282,5.0554765 52.3002194,5.0558245 52.3001449,5.0566854 52.3000713,5.0576507 52.3000718,5.0578958 52.3000369,5.0582984 52.2999041,5.0590178 52.2995797,5.05951 52.29932,5.06087 52.29838,5.0611 52.29816,5.06141 52.29781,5.06155 52.29761,5.06176 52.2972,5.06187 52.29704,5.06209 52.29679,5.06253 52.29652,5.06275 52.29644,5.06299 52.29638,5.0632 52.29635103448276,5.0631973122168565 52.2963497094025))",4326)),
  ( ST_GeomFromText( "POLYGON((5.058231314941121 52.31056280634356,5.058857247356365 52.30953453118317,5.05915853902347 52.30894024045263,5.059355065017444 52.30845260073951,5.059503159737966 52.30792015740651,5.05937912419807 52.30777071157701,5.059042018505754 52.30742081071795,5.058612724343904 52.30702360112013,5.058136639393473 52.306642860690836,5.057397365178076 52.30611223306025,5.056965864318106 52.30620527938734,5.053886104091538 52.306696194665356,5.052732190563674 52.30739792798974,5.052397056807738 52.307581935880336,5.052012115031732 52.307773802107164,5.051626227250136 52.30794227326824,5.0510285669765285 52.308165740560796,5.050717749135228 52.30826544112054,5.050354476360618 52.30835964109198,5.047938086633207 52.30873231959459,5.047249914478091 52.308354523166905,5.0472419 52.3083501,5.0471046 52.3085801,5.0470371 52.3087843,5.04703 52.30896,5.0470984 52.3090996,5.0472982 52.3092062,5.0475609 52.309248,5.0477233 52.3092615,5.0479137 52.3092651,5.0485159 52.3092746,5.0490459 52.309278,5.0495586 52.3092725,5.0497752 52.3092847,5.0499434 52.3093054,5.0501089 52.3093306,5.0504133 52.3093427,5.0520866 52.3093768,5.0538394 52.3094532,5.0552715 52.3095,5.0560446 52.3096318,5.05658 52.30967,5.05688 52.30967,5.05715 52.3097,5.05742 52.30979,5.05768 52.31,5.0582294 52.3105659,5.058231314941121 52.31056280634356))",4326));

/* Fucked ! random values changed to e.g. 5.0632 */
select id,st_AsText(`bounds`) from `mysql_bug` where st_intersects(Bounds, ST_GeomFromText("POLYGON((5.063198276816225 52.29635235935326,5.063036951986327 52.29647660497469,5.062786504184279 52.29672239004589,5.062585928521267 52.29702009729457,5.062457709836123 52.29735759224395,5.062349910451017 52.297787167501156,5.062259579011925 52.29805827625105,5.062073356104748 52.2985093152599,5.061932227268367 52.29880606931143,5.061002880058911 52.30031243043994,5.060596067282311 52.300854746070016,5.059910028600934 52.301669821934205,5.059526604073613 52.30206323206672,5.05928523074182 52.302289984795685,5.058997521450486 52.302533503117225,5.058576811725638 52.302860178307974,5.058467527036433 52.30343803237695,5.058286371885684 52.30417587505969,5.058174301748714 52.304531269914456,5.058034713627738 52.30490108422972,5.057889905233348 52.30522003123764,5.057585392364621 52.30578704651199,5.057393702087933 52.30610599189801,5.058153225206382 52.30665532442907,5.058612724343904 52.30702360112013,5.059020809381616 52.3073995758205,5.059507870604076 52.30791345981135,5.062659214062694 52.309312162386775,5.0631953485236085 52.30958407884961,5.0632 52.309586458333335,5.0632 52.29635103448276,5.063198276816225 52.29635235935326))",4326));

Suggested fix:
/*working, correct values returned */
select  id,st_AsText(`bounds`)  from `mysql_bug` t1
   inner join
  (select id from `mysql_bug` where st_intersects(Bounds, ST_GeomFromText("POLYGON((5.063198276816225 52.29635235935326,5.063036951986327 52.29647660497469,5.062786504184279 52.29672239004589,5.062585928521267 52.29702009729457,5.062457709836123 52.29735759224395,5.062349910451017 52.297787167501156,5.062259579011925 52.29805827625105,5.062073356104748 52.2985093152599,5.061932227268367 52.29880606931143,5.061002880058911 52.30031243043994,5.060596067282311 52.300854746070016,5.059910028600934 52.301669821934205,5.059526604073613 52.30206323206672,5.05928523074182 52.302289984795685,5.058997521450486 52.302533503117225,5.058576811725638 52.302860178307974,5.058467527036433 52.30343803237695,5.058286371885684 52.30417587505969,5.058174301748714 52.304531269914456,5.058034713627738 52.30490108422972,5.057889905233348 52.30522003123764,5.057585392364621 52.30578704651199,5.057393702087933 52.30610599189801,5.058153225206382 52.30665532442907,5.058612724343904 52.30702360112013,5.059020809381616 52.3073995758205,5.059507870604076 52.30791345981135,5.062659214062694 52.309312162386775,5.0631953485236085 52.30958407884961,5.0632 52.309586458333335,5.0632 52.29635103448276,5.063198276816225 52.29635235935326))",4326))) t2
  using(id);

Thank you for the bug report. Please print here the result of 2 queries, the second one it gives to me the below error:

,5.0632 52.309586458333335,5.0632 52.29635103448276,5.063198276816225 52.29635235935326))",4326))) t2;
ERROR 1052 (23000): Column 'id' in field list is ambiguous

Hi Miquel,

You talk about the work around: the query should end on 

52.30958407884961,5.0632 52.309586458333335,5.0632 52.29635103448276,5.063198276816225 52.29635235935326))",4326))) t2
using(id);

Not 

52.29635103448276,5.063198276816225 52.29635235935326))",4326))) t2;

Below you will find the resultset of the first select query. 
Search for 5.0632 there are multiple points which now have this value. But were not inserted as such.

4, 'POLYGON((5.063194767604468 52.29547420949254,5.0632 52.29547,5.0632 52.29635103448276,5.0631973122168565 52.2963497094025,5.062898216319557 52.29620300078889,5.062512767511783 52.296042640973134,5.062690227009933 52.2958801134462,5.063194767604468 52.29547420949254))'
1, 'POLYGON((5.0631967879877395 52.30958883730479,5.0632 52.309586458333335,5.0632 52.31806,5.063197706163205 52.318055333528555,5.062950067003499 52.31755905963844,5.062680735993036 52.31721830797045,5.06220768778821 52.31675509787845,5.062164867056544 52.316616589626996,5.062096460863965 52.31647529576293,5.062008825118752 52.31634407560335,5.061895025121778 52.31620970249339,5.061598482994666 52.315944107028336,5.061238403046716 52.31568838973036,5.060675308468418 52.31526011347344,5.060304213705726 52.3148490109568,5.060005343926405 52.314545303521285,5.059304042164561 52.31392558055251,5.059805155158196 52.31330940446082,5.06003152167485 52.312983221901234,5.060165132441595 52.31274435317078,5.060284148064529 52.31247876729871,5.060458339895537 52.31217777088448,5.060663619836996 52.31171742201622,5.062897243236728 52.30981069405652,5.0631967879877395 52.30958883730479))'
2, 'POLYGON((5.058233599374992 52.31056728726207,5.0582294 52.3105659,5.0585473 52.3105045,5.0586598 52.3104828,5.06282 52.30963,5.0632 52.309586458333335,5.0631967879877395 52.30958883730479,5.062897243236728 52.30981069405652,5.060663619836996 52.31171742201622,5.06030715507687 52.31148861459802,5.059998382308607 52.31130862423394,5.059287704548174 52.31095717852761,5.058733874116929 52.31073352357895,5.058233599374992 52.31056728726207))'
3, 'POLYGON((5.0631953485236085 52.30958407884961,5.0632 52.309586458333335,5.06282 52.30963,5.0586598 52.3104828,5.0585473 52.3105045,5.0582294 52.3105659,5.058231314941121 52.31056280634356,5.058857247356365 52.30953453118317,5.05915853902347 52.30894024045263,5.059344917879849 52.30848260932382,5.059507870604076 52.30791345981135,5.062659214062694 52.309312162386775,5.0631953485236085 52.30958407884961))'
5, 'POLYGON((5.063198276816225 52.29635235935326,5.0632 52.29635103448276,5.0632 52.309586458333335,5.0631953485236085 52.30958407884961,5.062659214062694 52.309312162386775,5.059507870604076 52.30791345981135,5.059020809381616 52.3073995758205,5.058612724343904 52.30702360112013,5.058153225206382 52.30665532442907,5.057393702087933 52.30610599189801,5.057585392364621 52.30578704651199,5.057889905233348 52.30522003123764,5.058034713627738 52.30490108422972,5.058174301748714 52.304531269914456,5.058286371885684 52.30417587505969,5.058467527036433 52.30343803237695,5.058576811725638 52.302860178307974,5.058997521450486 52.302533503117225,5.05928523074182 52.302289984795685,5.059526604073613 52.30206323206672,5.059910028600934 52.301669821934205,5.060596067282311 52.300854746070016,5.061002880058911 52.30031243043994,5.061932227268367 52.29880606931143,5.062073356104748 52.2985093152599,5.062259579011925 52.29805827625105,5.062349910451017 52.297787167501156,5.062457709836123 52.29735759224395,5.062585928521267 52.29702009729457,5.062786504184279 52.29672239004589,5.063036951986327 52.29647660497469,5.063198276816225 52.29635235935326))' ....... I could not insert all the data here .....

Thank you for the feedback, verified as described.