Bug #92937 st_isvalid returns invalid for a valid geometry.

Submitted:	24 Oct 2018 21:08	Modified:	24 Oct 2018 22:15
Reporter:	Nicolas Troncoso	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S2 (Serious)
Version:	5.7	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
I rant the same (or equivalent where applicable) query in:

MySql 5.6: Success
MySql 5.7.23: Failure
MySql 8.0.12: Success
Postgres server 10.5: Success
MSSQL 2016: Success

It looks like MySql 5.7 is more strict or enforcing things differently than other OGC complaint engines. 

How to repeat:
select st_isvalid(st_geomfromtext('POLYGON((-93.2454320369889 44.9790260760026,-93.2474158659694 44.9793346719775,-93.2478473452188 44.9793573813881,-93.249091098975 44.9794228419606,-93.2505043080274 44.9795136879663,-93.2517350936496 44.979792127155,-93.253087654042 44.9800981152758,-93.2542350000252 44.9803576779645,-93.2558361170231 44.9814066700418,-93.2580570276197 44.9825526686608,-93.2593782858492 44.983234443175,-93.2597422588382 44.9833424251328,-93.260119969511 44.9834544827282,-93.2613531748491 44.9838203448331,-93.2619935780312 44.9840103369575,-93.262404794026 44.9842281159748,-93.2630887800631 44.9846841065163,-93.2633305809615 44.9848453070254,-93.2641013129952 44.9854525499553,-93.2647853879752 44.9859915189635,-93.2658875149668 44.987181816028,-93.2665928769896 44.9880194329805,-93.2670778130206 44.9889893039633,-93.2676068339982 44.9897828359692,-93.2672544160384 44.9901033369894,-93.2668605910434 44.9902608670054,-93.2666636780063 44.9903133769808,-93.2663486189635 44.9904052699773,-93.2661254510287 44.9905627999933,-93.2658760290055 44.9906940750216,-93.2655740960177 44.9907859670288,-93.2652721640191 44.9908516050376,-93.2648914669676 44.9908647319918,-93.2644976419726 44.9907990949723,-93.2639200319739 44.9906546920005,-93.2636574820071 44.9905627999933,-93.2632505299679 44.9903658869562,-93.2624760080113 44.9899851899946,-93.2619902900199 44.9897095129837,-93.261793377972 44.98952572798,-93.2615045730176 44.9890531380219,-93.2614023690241 44.9888934439673,-93.2611720000377 44.9889539999971,-93.2606469209686 44.989092024977,-93.260062921017 44.9892550250294,-93.2591819210388 44.9895490250075,-93.2577599210235 44.9900280249734,-93.2565089209799 44.9904800250432,-93.2563479200305 44.9905430249806,-93.2561629200427 44.9906030249597,-93.2548939361662 44.9910499617563,-93.2535559202395 44.9915120251499,-93.2534129210194 44.9915610257008,-93.2520749202913 44.9920410260725,-93.2515659202909 44.9921930250781,-93.2504629207771 44.9925440249868,-93.2501629420161 44.9926538590984,-93.2499719207075 44.9929030252644,-93.2493759201126 44.9936860255797,-93.2488729211983 44.9943290253743,-93.2485979202078 44.9946800253729,-93.248285920209 44.9950830251615,-93.2481659200711 44.9952340259196,-93.2480309201408 44.9954110249988,-93.2477949200495 44.9957210249954,-93.2476799201422 44.9958910257303,-93.2476099206913 44.9959940258936,-93.2475549208529 44.9960850254136,-93.2475039202097 44.9961840257522,-93.2474689212037 44.9962690259848,-93.247429056146 44.9964148806422,-93.247392920307 44.9962450257773,-93.247314738914 44.995939994715,-93.2473339187553 44.9959090219738,-93.2473489182779 44.9958470243307,-93.2473179207154 44.9950860252999,-93.2473119202589 44.9949670252081,-93.2473179207154 44.9944570250717,-93.2473179207154 44.9937270256717,-93.2473109202128 44.9935370250037,-93.2472989207386 44.9928440252416,-93.2472919202359 44.9922670258826,-93.2473017570205 44.9916779662552,-93.2477089577222 44.9911680380645,-93.2478740308019 44.9909179366935,-93.247382920925 44.9910840251977,-93.2469639205784 44.9912430261448,-93.2467409200074 44.991320025289,-93.2463039207194 44.9914470249403,-93.2460319207665 44.9914800247534,-93.2445339200344 44.9914950252652,-93.2434939207278 44.991481025429,-93.2430549200885 44.9914750252423,-93.2419308126769 44.9914850788533,-93.2409469209936 44.9914870249863,-93.2396679207379 44.9914910250808,-93.2387709205615 44.991502025588,-93.2384229217804 44.9915060268517,-93.2383149210268 44.9915280240891,-93.2376659199662 44.9915510250598,-93.237164920065 44.9915680250344,-93.236861920841 44.9915680250344,-93.236651782194 44.9915679289867,-93.2367009200714 44.9916940249993,-93.2367009203412 44.9916940255389,-93.2367429207492 44.9917840251027,-93.2367919200411 44.9918800250331,-93.2368709209862 44.9920140250073,-93.2370139202062 44.992190025929,-93.2371869210795 44.9924010258567,-93.2375981256529 44.9928387600708,-93.2378379207934 44.9930940260685,-93.2379389209544 44.9932130260704,-93.2380239210971 44.9933290261137,-93.2380939199185 44.9934370258781,-93.2381549200335 44.9935510258292,-93.2382419201784 44.9937180250768,-93.2382989210983 44.9938480259457,-93.2383349200605 44.9939300249607,-93.2384349201754 44.9941730252849,-93.2385069209776 44.9944180259711,-93.2385779208344 44.9947140261314,-93.2386139223146 44.9948870344691,-93.2386499201976 44.9950600259895,-93.2386859210484 44.9953220252114,-93.2387099201766 44.9955070250192,-93.2387269199714 44.9956960260008,-93.2387379209283 44.9959410260576,-93.2387339206539 44.9963010264712,-93.2387179209053 44.9965390258454,-93.2387009200314 44.9967450251828,-93.2386659211153 44.9969190252029,-93.2386478291839 44.9971240130728,-93.2386087142507 44.997338080588,-93.238574920696 44.9975230250876,-93.2385019208369 44.9978450258173,-93.2384289199886 44.9981400249422,-93.2383609207199 44.998319025013,-93.2381938570808 44.9987069448796,-93.2381169209791 44.9988790258364,-93.238045920223 44.999046025174,-93.2379249201289 44.9992910258602,-93.2376829208401 44.9997370258331,-93.2375019204972 45.000040025237,-93.2373749209358 45.0002730251001,-93.2372269199564 45.0005100249677,-93.2371730524544 45.0005936924949,-93.2371329207477 45.0006560249556,-93.2368439209102 45.0010130260404,-93.2367029207032 45.0011910250758,-93.2364829200907 45.0014040250957,-93.2363863005273 45.0014833403539,-93.2362819208939 45.0015690257799,-93.2361819208689 45.0016580241285,-93.2360209209987 45.0017630260029,-93.2356889209769 45.0019730240858,-93.2356889204373 45.0019730243556,-93.2351749201164 45.002206025118,-93.2350518147994 45.0022549955416,-93.2348129209595 45.0023500260029,-93.2345989208035 45.0024330248842,-93.2342459209825 45.002524026023,-93.2340615946768 45.0025597621134,-93.2339519205547 45.002581025414,-93.2334569201207 45.0026380251647,-93.2329439199359 45.0026760252084,-93.2328335215897 45.0026773474816,-93.2326099219803 45.0026800260224,-93.2322089201255 45.0026660259164,-93.2320414171676 45.0026532807244,-93.2318409210516 45.0026380251647,-93.2315999209096 45.0026100250427,-93.2311859207036 45.0025430254603,-93.2306979209523 45.0024350249765,-93.2304169518808 45.0023499391284,-93.2302649210392 45.0022880250324,-93.2300389209594 45.0022240250487,-93.2297199208177 45.0021390258054,-93.2270259206896 45.0013770259191,-93.2268139208058 45.0013230250476,-93.2265589474474 45.0012704985247,-93.2261149218461 45.0011790257814,-93.2259411343359 45.0011552401522,-93.2256619210108 45.0011170249907,-93.2241239218515 45.0009790251919,-93.2235459210074 45.0009620253073,-93.2230559199947 45.0009570251666,-93.2226256417899 45.0009624909762,-93.2221899207982 45.0009680259437,-93.2214269209556 45.0010250259642,-93.220728920873 45.001093025143,-93.2202711649622 45.0011549850146,-93.2215328299761 45.0009000380064,-93.220068920924 45.0010390261602,-93.2189619201465 45.0013240250937,-93.2186114520968 45.0014317895955,-93.2178399208357 45.0016690258948,-93.216542921009 45.0021580258722,-93.2163677539387 45.0022359278458,-93.2163930849629 45.0024951239708,-93.2149679205031 45.0030770248149,-93.2149679199635 45.0030770250847,-93.2123789208105 45.0041480259107,-93.2122564527531 45.0041997726314,-93.2121659208805 45.0042380252048,-93.2109039200598 45.0047950258898,-93.2096309208903 45.0053420260237,-93.208982920955 45.0056250250449,-93.2083411204064 45.0058898279033,-93.2081299849508 45.005976941003,-93.2082401592759 45.0060194523159,-93.2078009999962 45.0061040000096,-93.2077590000378 45.0061120000188,-93.2077525039649 45.0061129989858,-93.2077459999779 45.0061140000211,-93.207540000011 45.0061539999771,-93.2075279999972 45.0060220000053,-93.2075459231258 45.0047195880585,-93.2076030000384 45.0005720000378,-93.207601219021 45.0001370049789,-93.2075959999854 44.9988619999614,-93.2075770000085 44.9955660000409,-93.2075678750374 44.9937016299863,-93.2075677090226 44.9936677759971,-93.2075580000317 44.9916839999867,-93.207560466962 44.9912958200361,-93.2075630898347 44.9908830222237,-93.2075660000409 44.9904250000238,-93.2075739999601 44.989504,-93.2075869490284 44.988076643982,-93.20758700002 44.9880710000166,-93.2075971933858 44.9867427600803,-93.2076191130116 44.9838865409611,-93.2076191140009 44.9838864719831,-93.2076191140009 44.983886443025,-93.2076192739903 44.9838655870273,-93.207640000036 44.9811649999948,-93.207643484819 44.9808420850143,-93.2076490000013 44.9803310000257,-93.2076564111345 44.9792355606626,-93.2076699999805 44.9772269999661,-93.2076730000289 44.9767540000071,-93.2076730000289 44.9767350000303,-93.2076721000773 44.9763669999671,-93.2076719999828 44.976325999965,-93.2076750000312 44.9760230000215,-93.2076767557776 44.9758573709809,-93.2076779999897 44.975740000011,-93.2076810000381 44.9752080000292,-93.2076830000404 44.9749190000119,-93.2076842639476 44.9747186819616,-93.2076869999551 44.9742850000028,-93.2076869999551 44.9741619999963,-93.2076920000058 44.9731660000209,-93.2076949999642 44.9726490000114,-93.2077029999734 44.9715120000088,-93.2077040000196 44.9713009999912,-93.2077040000196 44.971233,-93.2077109999826 44.9700229999615,-93.207714000031 44.9694399999661,-93.2077260000448 44.9680100000315,-93.2077330000079 44.9664229999615,-93.2077330287862 44.966416498043,-93.2077331629651 44.9663859960069,-93.2077347828239 44.9660183240165,-93.2077348157391 44.9660108680072,-93.2077410000171 44.9646070000331,-93.2077559999894 44.9628070000331,-93.2077579999917 44.9622410000122,-93.2077602438901 44.9614466530406,-93.2077634985366 44.9602945199662,-93.2077639999986 44.960117,-93.2077680000032 44.9600679999883,-93.2077700000055 44.9600340000391,-93.2077700000055 44.960006,-93.2077700000055 44.9598319999868,-93.2077700000055 44.9597619999963,-93.2077700000055 44.959692999962,-93.2077700000055 44.9596639999737,-93.2077700000055 44.9596320000268,-93.2077689999593 44.9590279999622,-93.2077769999685 44.9587430000395,-93.2077709999617 44.9585719999778,-93.2077680000032 44.9584259999899,-93.207766 44.9573119999689,-93.20778699998 44.9564020000024,-93.2077820000193 44.9560850000428,-93.2077738357939 44.955563969002,-93.2077709999617 44.9553829999555,-93.2077709999617 44.9552350130055,-93.2077709999617 44.9552269999561,-93.2077639999986 44.954932999978,-93.2077619999963 44.9547620000063,-93.2077610000401 44.9543080000242,-93.2077604251035 44.9540076219642,-93.2077590000378 44.9532630000375,-93.2077570000355 44.9532089999754,-93.2077510000286 44.9530560000244,-93.2099300385504 44.9543958011054,-93.2111439061263 44.955142158274,-93.2121374125515 44.9557530243926,-93.2124683439089 44.9559565005021,-93.2141902490308 44.9570152289996,-93.220140382043 44.9612962589652,-93.221303390619 44.9623012154688,-93.2230342707949 44.9637968701395,-93.225817865612 44.9662021762894,-93.227917920006 44.9652770249947,-93.228150919959 44.9651720259982,-93.2286669210016 44.9649650249959,-93.2290199200133 44.964837026018,-93.2292479209947 44.964764025979,-93.2298149199826 44.9646160249996,-93.2300539200323 44.9645630259729,-93.2305069209576 44.9644860260193,-93.2307569209751 44.9644540249933,-93.2310469210386 44.964424026038,-93.2313939200433 44.9643990249751,-93.2316789209554 44.9643880259966,-93.2319189199721 44.9643830260359,-93.2322509199939 44.9643810250443,-93.2327859210134 44.964384025992,-93.2330189209664 44.9643860250051,-93.2335409200374 44.9643860250051,-93.2361639210281 44.9643970249728,-93.2377009210406 44.9643910249659,-93.2386859209585 44.9643880250074,-93.239322921016 44.9643940250143,-93.241010919988 44.9643970249728,-93.2414009199865 44.9643970249728,-93.2417759210019 44.9644060260173,-93.242029921024 44.9644140250373,-93.2422389199602 44.9644300249657,-93.2425019210268 44.9644540249933,-93.2428839200269 44.9644940260285,-93.2431489210059 44.9645300249907,-93.2434239200178 44.9645690259798,-93.243689921043 44.9646220250065,-93.2439049209753 44.9646710250178,-93.2441459200381 44.9647340260345,-93.2442309200009 44.9647540259676,-93.2449139200214 44.9649390259553,-93.2450139210356 44.9649700260359,-93.2455099209761 44.9651440259661,-93.2466009210157 44.9655780250259,-93.2472649209693 44.9658320260372,-93.2473379210082 44.9658630250386,-93.2474149209618 44.9658950249855,-93.2476549209678 44.9659780250358,-93.2480769210031 44.9661070260385,-93.2485629200327 44.9662220260357,-93.2487979209773 44.9662690249656,-93.2492179210102 44.9663330250391,-93.249635514997 44.9663636249214,-93.2496819208239 44.966367025258,-93.2499809201333 44.9663810250044,-93.2502389211493 44.9663800249582,-93.2505799207767 44.9663700253064,-93.2507249201789 44.9663610249813,-93.2508449203168 44.9663530256017,-93.2510019202724 44.966335025671,-93.2511289209129 44.9663180257863,-93.2516229211209 44.966246025074,-93.2518489199417 44.9662130252609,-93.2519199206078 44.9662010257867,-93.25206492001 44.9661800257176,-93.2522168603798 44.9661540909785,-93.2523929819006 44.9661279602772,-93.2533349210198 44.9671660252319,-93.2534589203528 44.9672950276734,-93.2535039208093 44.9673730221872,-93.2536919203059 44.96756402479,-93.2538329206927 44.9676780253706,-93.2539349208099 44.9677600248353,-93.2540799211114 44.9678500252984,-93.2543540867217 44.967985930936,-93.2543427992408 44.9681171618076,-93.2544889176694 44.9680960268402,-93.2545709202817 44.9681030251845,-93.2546590682314 44.9681099329671,-93.25441292028 44.9686690252054,-93.2543259208545 44.9688170258251,-93.2541699189666 44.9690990252499,-93.254116921109 44.9691630256831,-93.2539569200258 44.9693752052515,-93.2538469211585 44.9692238449445,-93.2531738920115 44.9701249681528,-93.2528589210124 44.9704960260008,-93.2528069210426 44.970557026026,-93.2527319200121 44.9706490260418,-93.2526370079915 44.9707741280336,-93.2523168950287 44.9709819789852,-93.2520219209948 44.9713570249668,-93.2515149209968 44.9719630250336,-93.2514539199824 44.9720350260156,-93.2514079200195 44.9720830249917,-93.2513019209769 44.9722010250373,-93.2512069210026 44.9722940250093,-93.2511119210283 44.9724000250412,-93.2508619200216 44.9726850249639,-93.2505699199558 44.9730130249811,-93.2504789209754 44.9731010249923,-93.2499059200022 44.9736530259864,-93.2496989209784 44.9738490260318,-93.2496519199801 44.9738890249985,-93.2495929199573 44.9739340250053,-93.2490549199686 44.9743640249598,-93.2487209210237 44.9746070260035,-93.2486239209572 44.9746780249609,-93.2481119200091 44.9750510249849,-93.2474069210426 44.9755380249599,-93.2471109209722 44.9757870250212,-93.2468809199884 44.9760060249581,-93.2464789209654 44.9763930249981,-93.2463959210049 44.9764670259825,-93.2462669209916 44.9766030259589,-93.2461369200328 44.9767810259836,-93.2458939209677 44.9771730249951,-93.2456149209618 44.9776600249702,-93.2455679210427 44.9777700259959,-93.2454989200191 44.9779180249969,-93.2453969209811 44.9781500249937,-93.2453419199736 44.9782760260378,-93.2452370000275 44.9785509999587,-93.245080852989 44.9789602289914,-93.2454320369889 44.9790260760026))'));

Suggested fix:
Using MySql 8.0.12 as the standard. The query should return True.

Thank you for the bug report.
0369889 44.9790260760026))'))\G
*************************** 1. row ***************************
st_isvalid(st_geomfromtext('POLYGON((-93.2454320369889 44.9790260760026,-93.2474158659694 44.9793346719775,-93.2478473452188 44.9793573813881,-93.249091098975 44.9794228419606,-93.2505043080274 44.9795136879663,-93.2517350936496 44.979792127155,-93.253087: 1
1 row in set (0.00 sec)

mysql 8.0 > SHOW VARIABLES LIKE "%version%";
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| immediate_server_version | 999999                                 |
| innodb_version           | 8.0.14                                 |
| original_server_version  | 999999                                 |
| protocol_version         | 10                                     |
| slave_type_conversions   |                                        |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2                  |
| version                  | 8.0.14                                 |
| version_comment          | Source distribution BUILD: 2018-OCT-04 |
| version_compile_machine  | x86_64                                 |
| version_compile_os       | Win64                                  |
| version_compile_zlib     | 1.2.11                                 |
+--------------------------+----------------------------------------+
11 rows in set (0.01 sec)
------------------------------------------------------------------------------------------------------------------------------------------------------------
0369889 44.9790260760026))'))\G
*************************** 1. row ***************************
st_isvalid(st_geomfromtext('POLYGON((-93.2454320369889 44.9790260760026,-93.2474158659694 44.9793346719775,-93.2478473452188 44.9793573813881,-93.249091098975 44.9794228419606,-93.2505043080274 44.9795136879663,-93.2517350936496 44.979792127155,-93.253087: 0
1 row in set (0.00 sec)

mysql 5.7 > SHOW VARIABLES LIKE "%version%";
+-------------------------+----------------------------------------+
| Variable_name           | Value                                  |
+-------------------------+----------------------------------------+
| innodb_version          | 5.7.25                                 |
| protocol_version        | 10                                     |
| slave_type_conversions  |                                        |
| tls_version             | TLSv1,TLSv1.1                          |
| version                 | 5.7.25-log                             |
| version_comment         | Source distribution BUILD: 2018-OCT-04 |
| version_compile_machine | x86_64                                 |
| version_compile_os      | Win64                                  |
+-------------------------+----------------------------------------+
8 rows in set (0.03 sec)

mysql 5.7 >

Page generated in 0.053 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.