MySQL Bugs: #107320: SPATIAL KEYS broke after updating 8.0.28 to 8.0.29

Bug #107320	SPATIAL KEYS broke after updating 8.0.28 to 8.0.29
Submitted:	17 May 2022 18:06	Modified:	14 Jun 2022 13:09
Reporter:	Александр Ммммммм	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.29	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Incorrect optimizer cost for SPATIAL KEYS in 8.0.29

How to repeat:
DROP DATABASE IF EXISTS test
;
CREATE DATABASE test
;
USE test
;
CREATE TABLE spatial_test (
id int unsigned NOT NULL AUTO_INCREMENT,
latitude DECIMAL(9,6) NOT NULL,
longitude DECIMAL(9,6) NOT NULL,
geo_point point GENERATED ALWAYS AS (st_srid(point(longitude,latitude),4326)) STORED NOT NULL SRID 4326,
PRIMARY KEY (id),
SPATIAL KEY geo_point (geo_point)
)
;
insert into spatial_test (latitude, longitude)
values  (42.062961,-75.417531),
(40.679891999999995,-73.58856800000001),
(40.694534999999995,-73.617074),
(42.912615,-78.80960300000001),
(40.888687,-73.843244),
(40.683413,-73.950925),
(40.685665,-73.952886),
(40.642286,-73.58779),
(40.625341999999996,-73.936484),
(40.638389000000004,-73.894312),
(40.660709999999995,-73.947144),
(40.807566,-73.672839),
(40.721486999999996,-73.571689),
(40.666323,-73.59686500000001),
(41.140708000000004,-74.030716),
(40.786561,-73.2025),
(43.021603999999996,-78.94675500000001),
(40.88115,-73.861068),
(40.878689,-73.858407),
(40.88089,-72.93521),
(41.08668,-73.78196),
(40.65469,-73.924188),
(42.886967999999996,-78.812669),
(40.751556,-73.35629300000001),
(40.695545,-73.953331),
(40.853233,-73.19061500000001),
(40.842200,-73.366398),
(40.850767,-73.031226),
(43.004866,-74.37765300000001),
(40.640413,-73.90517),
(40.758700,-73.281276),
(40.768370000000004,-72.834023),
(40.894420000000004,-73.83325),
(42.939322,-78.894273),
(42.63827,-73.7851),
(40.858785,-73.906997),
(40.836309,-73.83699),
(40.693351,-73.785911),
(42.793544,-73.95873900000001),
(40.839634999999994,-73.874679),
(40.640003,-73.894185),
(40.694985,-73.923198),
(40.701999,-73.637536),
(40.761728999999995,-73.56824300000001),
(40.662607,-73.57668199999999),
(40.886916,-73.059537),
(40.826959,-73.096982),
(42.929821999999994,-78.81813100000001),
(40.682981,-73.915594),
(40.839238,-73.85914),
(40.715438,-73.621994),
(40.683528,-73.582761),
(40.605748999999996,-73.750079),
(41.130061,-74.122251),
(40.883326,-72.947872),
(40.90445,-73.830977),
(43.109246999999996,-77.744574),
(40.711858,-73.389984),
(40.68705,-73.40536),
(43.062464,-77.62214200000001),
(40.775897,-72.959274),
(42.752821000000004,-78.892424),
(40.707249,-73.6645),
(40.659869,-73.496312),
(40.633148999999996,-74.133336),
(40.771053,-73.233588),
(40.660992,-73.379756),
(40.951736,-72.931435),
(42.936865,-78.817271),
(42.824558,-78.833261),
(43.045427000000004,-74.338778),
(40.668228000000006,-73.91494300000001),
(40.633728000000005,-73.900899),
(40.672869,-73.683151),
(40.74575,-73.18849),
(41.156690000000005,-73.82763),
(43.140546,-77.608452),
(43.137514,-77.64766),
(43.072286,-76.113502),
(40.807574,-73.923351),
(40.649375,-73.944437),
(40.769469,-73.537388),
(40.76562,-73.86666),
(40.838879999999996,-73.11603000000001),
(40.958237,-73.019348),
(43.183743,-77.583955),
(40.684802000000005,-73.874222),
(40.664014,-73.891587),
(40.700014,-73.588545),
(40.656855,-73.693841),
(40.948982,-72.919320),
(40.862635,-73.035723),
(40.809769,-73.133509),
(40.740959,-73.371307),
(40.764540000000004,-73.27445300000001),
(42.143309,-77.067203),
(43.154182,-77.62370899999999),
(40.856035,-73.909446),
(40.867103,-73.88614100000001),
(40.577623,-74.010256),
(40.659614000000005,-73.575817),
(40.71216,-73.446844),
(40.586551,-74.147583),
(40.844366,-73.070385),
(40.884223,-73.059525),
(42.993254,-78.183038),
(43.365493,-73.583975),
(40.901178,-73.867559),
(40.841439,-73.90977),
(40.684217,-73.936926),
(40.649491,-73.538969),
(41.01955,-73.69008000000001),
(42.902387,-78.83739200000001),
(42.912760999999996,-78.817024),
(42.926936,-78.802028),
(40.80871,-73.85118299999999),
(40.675795,-73.500787),
(40.681458,-73.597718),
(40.707891,-73.58428),
(40.840807,-73.054446),
(40.796919,-73.252967),
(43.029584,-76.180756),
(40.874008,-73.87684399999999),
(40.880183,-73.858842),
(40.644376,-73.527294),
(40.691492,-73.744316),
(40.860439,-73.863388),
(40.6977,-73.61424),
(40.682579,-73.610712),
(40.757681,-73.361827),
(43.113946999999996,-77.499176),
(40.819254,-72.862338),
(43.085475,-76.136318),
(40.875790,-73.853203),
(40.71755,-73.56451700000001),
(40.795641,-72.994585),
(40.78158,-73.18583000000001),
(40.77981,-72.84442),
(41.309131,-74.14896),
(40.79228,-72.95631),
(41.804626,-74.745733),
(42.800078000000006,-73.95144599999999),
(40.693974,-73.629992),
(40.661078,-73.414569),
(41.19097,-73.771026),
(42.997634000000005,-78.842303),
(40.876333,-73.886686),
(40.684570,-73.927131),
(40.693137,-73.630909),
(40.817514,-73.127238),
(40.748224,-73.366135),
(40.67391,-73.38296),
(40.86545,-72.80204),
(40.835468,-72.976601),
(41.693264,-87.658406),
(41.731858,-87.58645899999999),
(41.783534,-87.670684),
(38.525218,-90.016871),
(40.675353,-73.79623000000001),
(42.709713,-73.671086),
(40.652914,-73.912495),
(42.873945,-76.98633000000001),
(44.1661,-87.58677),
(41.784589000000004,-87.709958),
(41.56511,-87.57849499999999),
(43.146992,-77.644358),
(40.814465999999996,-73.863807),
(40.953250,-72.978031),
(43.162076,-77.641517),
(40.855084999999995,-73.885141),
(40.902817999999996,-73.842173),
(40.784427,-73.47269200000001),
(40.634471999999995,-73.65996700000001),
(40.766674,-73.553952),
(40.837270000000004,-73.12383),
(41.417499,-88.110501),
(42.749353,-87.794881),
(40.692594,-73.979976),
(40.650607,-73.939081),
(40.67388,-73.89409),
(41.147881,-73.940545),
(40.82917,-73.13048),
(41.581379,-87.709976),
(40.04221,-75.17675),
(40.052168,-75.182833),
(41.622924,-87.623400),
(40.240787,-75.294534),
(39.997609999999995,-75.163793),
(39.95107,-75.23748),
(41.538876,-87.690611),
(40.763692999999996,-74.20371800000001),
(40.796238,-73.50421700000001),
(40.997416,-72.299191),
(39.916913,-74.16641800000001),
(39.882836,-75.086155),
(40.203995,-74.749711),
(40.742100,-74.227300),
(40.629835,-74.390668),
(43.011025,-88.000603),
(41.77131,-87.57033),
(41.667798,-87.63882199999999),
(40.108602000000005,-74.123942),
(40.681701000000004,-73.769773),
(40.641694,-73.901149),
(40.678584,-73.591497),
(40.831935,-73.078686),
(40.874359999999996,-73.02011),
(40.61551,-75.464832),
(39.632539,-86.344762),
(39.957801,-86.032737),
(39.876678000000005,-86.210143),
(41.14929,-74.35401800000001),
(40.86804,-74.016991),
(39.390334,-74.539346),
(39.870356,-75.110098),
(41.101715,-85.143667),
(39.841775,-86.123442),
(40.021264,-75.060437),
(40.048359999999995,-76.298551),
(40.27621,-76.866066),
(41.678876,-87.61822099999999),
(41.699612,-87.606549),
(40.741865,-74.201007),
(40.84063,-73.8553),
(40.675125,-73.88655899999999),
(40.716235,-73.613116),
(40.637496999999996,-73.719071),
(40.693329,-73.587338),
(40.711312,-73.613393),
(40.719884,-73.432324),
(41.552565,-87.28268299999999),
(40.727019,-74.213641),
(39.691959000000004,-75.00891999999999),
(40.735896000000004,-74.251733),
(39.977034,-74.144287),
(39.690543,-75.109431),
(40.721559,-74.235949),
(41.67174,-87.69048000000001),
(39.874667,-86.211419),
(40.067225,-86.006325),
(39.794108,-86.089534),
(39.933640000000004,-75.2296),
(40.716395,-74.236661),
(39.812197,-86.096976),
(39.790471000000004,-86.289635),
(40.048374,-75.159103),
(40.988085,-74.604555),
(39.776140999999996,-86.123387),
(39.949755,-75.224515),
(41.650535,-88.090044),
(40.744222,-74.221799),
(41.773263,-87.681265),
(40.714289,-74.218542),
(40.770722,-74.238917),
(41.893823,-87.722101),
(40.588397,-74.08662199999999),
(40.727587,-73.294222),
(40.899935,-90.283508),
(38.280851,-85.74521899999999),
(40.74153,-74.249927),
(39.732392,-75.12711999999999),
(40.345128,-74.088608),
(40.728928,-74.237625),
(40.907291,-73.971909),
(42.9156,-78.821857),
(40.326757,-79.705021),
(41.743379,-87.722098),
(41.902876,-87.76598100000001),
(41.755001,-87.84514300000001),
(41.741321,-87.647716),
(42.416491,-87.861064),
(40.109019,-74.126305),
(39.760615,-86.111385),
(39.996024,-75.117574),
(40.729890000000005,-74.23235),
(40.738159,-74.221673),
(40.819164,-73.88837),
(40.636571,-74.153818),
(40.705857,-73.413384),
(41.481025,-87.741659),
(39.842314,-86.124593),
(41.727610999999996,-87.652759),
(40.222376000000004,-74.744843),
(40.052129,-74.531028),
(40.598102000000004,-74.294917),
(42.304045,-89.097676),
(-1.000000,-1.000000),
(41.818917,-87.688716),
(41.617999,-87.596703),
(42.974070000000005,-87.90480500000001),
(39.821179,-86.123755),
(40.01389,-75.08792),
(39.964783000000004,-75.819439),
(40.725077,-74.23169200000001),
(41.481729,-87.74653599999999),
(41.493451,-87.68711400000001),
(41.584421999999996,-87.596991),
(41.913685,-87.77582199999999),
(41.701857,-86.268965),
(41.536137,-87.333419),
(42.275216,-89.110241),
(40.725631,-74.218457),
(40.717459000000005,-74.279973),
(40.738616,-74.211384),
(40.871225,-74.022121),
(40.559034999999994,-89.639169),
(41.475614,-87.699124),
(39.810267,-86.096076),
(41.489271,-87.701582),
(40.752774,-74.188551),
(41.862218,-87.799815),
(40.617419,-74.280877),
(41.497762,-90.423120),
(40.136580,-85.674714),
(39.633139,-86.326904),
(39.829355,-85.975929),
(40.00028,-75.11756),
(41.640363,-87.69251),
(39.98963,-75.23554),
(39.780596,-86.107262),
(40.043779,-74.85575),
(40.809621,-74.128091),
(40.657051,-74.19540400000001),
(40.578618,-74.222840),
(39.484337,-75.01619000000001),
(40.408522,-74.213699),
(40.425962,-74.082899),
(39.830517,-75.064413),
(40.69858,-74.23671999999999),
(40.007739,-74.870319),
(40.634105,-73.893010),
(40.687005,-73.928686),
(40.697061,-73.61641800000001),
(40.692172,-73.622985),
(40.63681,-73.577837),
(40.670026,-73.58877700000001),
(41.585045,-87.363936),
(40.029671,-76.498183),
(41.744965,-87.574998),
(40.775764,-74.18464),
(40.714016,-74.21408100000001),
(41.8821,-87.65174),
(41.738805,-87.646999),
(39.774231,-89.659090),
(39.745432,-86.111826),
(39.824493,-85.973988),
(39.782098,-86.202198),
(39.777100,-86.219884),
(39.866788,-86.261635),
(39.808048,-86.170899),
(39.902491,-86.230244),
(39.777854,-86.107962),
(39.799224,-86.18603900000001),
(39.826883,-86.260188),
(39.833303,-85.997279),
(41.183973,-85.082213),
(41.146444,-85.106125),
(39.797346,-86.079475),
(39.822089,-86.158339),
(42.131040000000006,-80.09461),
(41.9455,-79.74938),
(41.697881,-87.661410),
(39.791425,-86.116918),
(40.389134000000006,-86.88199300000001),
(40.006715,-85.953245),
(39.802939,-84.250723),
(41.869554,-87.724118),
(39.975845,-82.96990799999999),
(40.669396,-73.584633),
(40.874268,-74.15665600000001),
(40.655546,-74.399153),
(40.917362,-74.159945),
(40.761214,-74.03961),
(39.65867,-75.032556),
(39.957149,-74.992975),
(40.633169,-74.146338),
(40.74422,-73.35862),
(39.659566999999996,-86.26129300000001),
(39.688179,-86.075987),
(39.922933,-82.821134),
(39.737064,-86.130062),
(39.686896000000004,-86.210358),
(40.066996,-76.473213),
(39.94477,-75.22561),
(39.944192,-82.961798),
(39.634252,-86.099427),
(39.816159000000006,-86.144622),
(40.017378,-82.967586),
(39.942517,-82.971264),
(39.660824,-86.205234),
(39.813408,-86.241546),
(41.613234999999996,-87.249792),
(40.800405,-81.402896),
(39.805015000000004,-84.254765),
(39.323126,-84.218816),
(39.494690000000006,-84.35295),
(39.946806,-84.032598),
(39.906818,-82.962456),
(43.078963,-87.947742),
(38.57808,-89.904466),
(40.632746999999995,-74.40706800000001),
(39.735685,-74.243078),
(39.730657,-75.469124),
(40.619774,-74.982222),
(39.897378,-75.104174),
(39.432619,-84.366850),
(39.16896,-84.457658),
(40.787331,-73.652556),
(40.719411,-73.528063),
(40.66037,-73.57130699999999),
(40.73005,-73.25842),
(40.87755,-73.12613),
(41.644296999999995,-87.62524300000001),
(41.708252,-87.610961),
(41.735569,-87.569265),
(42.050686999999996,-87.705689),
(40.60175,-75.407237),
(39.085159999999995,-75.547007),
(41.586709,-87.63295600000001),
(41.623123,-87.63809300000001),
(41.764494,-87.648385),
(41.62875,-87.576862),
(41.766988,-87.660586),
(41.754853,-88.281776),
(43.105906,-88.043754),
(42.955113,-77.2327),
(39.949509,-82.818844),
(40.743892,-74.223385),
(41.805972,-87.660392),
(41.633472,-83.556815),
(41.915327000000005,-87.791026),
(41.679749,-87.660589),
(41.704508,-87.645513),
(41.83873,-87.78110799999999),
(41.627218,-87.557016),
(41.904609,-87.772673),
(41.485085999999995,-87.700276),
(41.919635,-87.75514100000001),
(42.027734,-88.17851),
(40.667226,-73.873023),
(40.710043,-73.373758),
(40.77264,-72.85141999999999),
(41.447496,-81.650234),
(39.498355,-84.377903),
(41.404588000000004,-81.74229100000001),
(42.000785,-88.131641),
(41.695130999999996,-87.611355),
(39.77953,-84.23159799999999),
(42.11753,-80.02169),
(41.857313,-87.875675),
(39.824015,-86.128672),
(39.76347,-86.115661),
(39.930279999999996,-75.184),
(41.841868,-87.74520799999999),
(41.763478000000006,-87.681017),
(41.743001,-87.645442),
(41.702933,-87.635811),
(42.996176,-76.136891),
(40.203191,-74.750505),
(41.789233,-87.690893),
(41.426989,-81.522402),
(41.547619,-87.707881),
(41.701215999999995,-87.635068),
(41.342912,-80.984235),
(39.838546,-85.988439),
(39.772925,-86.094149),
(39.441947,-84.528886),
(39.948201,-83.071435),
(39.982969,-82.966852),
(41.450165000000005,-82.178923),
(39.513809,-74.571669),
(39.570411,-74.368152),
(39.716956,-74.979577),
(40.921978,-74.278401),
(40.647461,-74.243313),
(40.699739,-74.240962),
(39.893825,-75.117783),
(39.208746999999995,-84.592678),
(39.782169,-86.20490799999999),
(41.580937,-87.379085),
(39.791251,-86.206118),
(39.813538,-86.272822),
(39.811935999999996,-86.148787),
(40.010490000000004,-75.1419),
(38.522267,-90.007616),
(41.820842,-87.863700),
(41.727532,-87.587525),
(41.78326,-87.63704399999999),
(41.743883000000004,-87.62666999999999),
(38.547965,-90.145169),
(39.775431,-86.11401500000001),
(43.065197,-77.053818),
(41.474911,-74.533663),
(39.793428000000006,-86.11699),
(39.351332,-84.43289200000001),
(39.848391,-86.059665),
(39.962165999999996,-83.770965),
(41.058596,-81.511126),
(41.452353,-81.540672),
(39.746609,-75.34421800000001),
(39.902349,-74.94165100000001),
(40.715367,-74.245909),
(40.612959,-75.361609),
(40.824575,-81.325142),
(39.731321,-86.061764),
(41.722838,-87.636849),
(39.688502,-83.911860),
(39.755146,-86.138336),
(39.799864,-86.215265),
(39.822613,-86.119619),
(41.056905,-80.628405),
(41.747971,-87.584515),
(39.832678,-86.039775),
(39.867533,-86.19689),
(40.137723,-74.850027),
(41.126410,-85.056844),
(39.798602,-86.068884),
(41.879707,-87.683709),
(42.946307,-74.199539),
(39.873628000000004,-86.20006500000001),
(39.826855,-86.002931),
(39.788852,-86.216809),
(39.849734999999995,-85.985523),
(39.778330,-86.113422),
(39.746083,-74.980809),
(41.346794,-82.42724399999999),
(41.11085,-83.17479499999999),
(39.786195,-84.218324),
(39.63967,-86.10208100000001),
(43.034122,-77.121605),
(39.951404,-82.914313),
(39.991240999999995,-76.648061),
(41.519622999999996,-81.623918),
(41.410821999999996,-81.589316),
(43.020308,-88.076202),
(41.702028999999996,-87.806092),
(40.58275,-74.21525),
(39.818574,-75.02403100000001),
(40.825236,-73.520462),
(39.816894,-86.09983299999999),
(41.33514,-81.503164),
(35.641521,-97.496070),
(40.280659,-76.89155799999999),
(40.276633,-76.848949),
(41.611877,-87.597922),
(41.730807,-87.65476600000001),
(41.708937,-87.617817),
(41.970884999999996,-88.133235),
(39.782403,-86.11413399999999),
(39.704653,-86.08576500000001),
(39.802296000000005,-86.144334),
(39.832813,-86.10945699999999),
(41.798876,-87.670405),
(40.006208,-82.786746),
(40.066733,-82.407409),
(40.914336,-81.086153),
(36.128177,-95.9348),
(41.755292,-87.63526800000001),
(35.502454,-97.537141),
(43.543474,-89.469124),
(36.179772,-95.976134),
(43.096274,-87.991576),
(42.275923,-89.124166),
(41.56793,-87.220603),
(39.822707,-86.07640699999999),
(41.174876,-85.069884),
(41.053391,-85.043829),
(41.057623,-85.131721),
(40.764418,-74.24775600000001),
(40.782371000000005,-74.188941),
(40.229421,-74.758627),
(40.871196999999995,-73.534686),
(40.683237,-73.580866),
(39.787251,-86.207649),
(39.752345,-86.105202),
(39.809157,-86.097414),
(41.075545,-81.489926),
(41.518124,-87.725031),
(36.064091,-95.891722),
(39.504743,-84.391524),
(39.222345000000004,-84.574269),
(39.80681,-84.02343),
(39.193126,-84.459065),
(40.502617,-82.896227),
(41.51235,-87.750282),
(41.662619,-87.647981),
(42.392396999999995,-87.859207),
(39.782615,-85.766818),
(40.716145,-74.235850),
(39.435645,-84.196988),
(39.825752,-75.076421),
(40.724600,-74.203500),
(40.146179,-74.244315),
(39.896932,-84.335584),
(40.694084000000004,-73.571334),
(41.125859000000005,-73.926276),
(39.941454,-75.228179),
(39.685763,-86.056923),
(39.839009000000004,-86.132176),
(39.834157,-86.235034),
(40.233098,-74.779654),
(41.528386,-87.779544),
(41.558115,-81.562615),
(39.831466,-86.16725799999999),
(38.919290999999994,-75.57540999999999),
(39.92617,-75.17867),
(39.958908,-74.199487),
(41.789109,-87.770363),
(41.736378,-87.602651),
(41.063510,-85.096203),
(34.806838,-106.552024),
(36.315534,-95.601592),
(38.720149,-90.127656),
(39.388836,-84.565242),
(39.749598,-86.255886),
(39.817536,-86.144576),
(42.444525,-87.838676),
(36.095315,-95.759440),
(36.179727,-95.99653),
(39.87638,-74.98733399999999),
(40.940267999999996,-73.987462),
(40.862429999999996,-74.832987),
(40.064138,-74.927986),
(39.649086,-75.521618),
(40.097944,-74.093023),
(40.776713,-74.222220),
(39.756207,-74.884504),
(39.842222,-84.792227),
(40.480060,-79.785771),
(41.523398,-81.610484),
(41.477720,-81.611928),
(39.110785,-84.620415),
(40.194864,-76.735984),
(40.613221,-75.47374),
(41.636635,-87.59697299999999),
(41.748969,-87.55204599999999),
(41.865088,-87.81428000000001),
(36.083845000000004,-95.931218),
(39.777196999999994,-86.125235),
(40.636384,-73.959766),
(40.84681,-73.02982),
(40.782602,-73.061587),
(40.040978,-75.13297800000001),
(41.776500,-87.612236),
(39.97303,-82.96212),
(39.955183,-83.158210),
(39.407105,-84.545039),
(40.623455,-74.014503),
(39.821125,-86.110468),
(39.792348,-86.204516),
(39.908846000000004,-82.949763),
(39.795867,-86.08854000000001),
(39.820316,-86.026486),
(39.822946,-86.100554),
(41.862833,-87.731551),
(41.480845,-87.568280),
(40.221584,-74.724662),
(39.982578000000004,-75.046908),
(40.712984999999996,-74.237661),
(40.890303,-73.998807),
(40.756794,-74.172160),
(40.911263,-74.409324),
(40.888535,-74.58036),
(40.736155,-74.218088),
(39.777993,-86.116846),
(39.807826,-86.097989),
(39.783565,-86.12611899999999),
(39.814983000000005,-86.148161),
(39.780119,-86.120369),
(39.699106,-86.046976),
(39.87809,-86.28603199999999),
(39.860058,-86.263887),
(39.844282,-86.261209),
(39.742520,-86.323111),
(39.775661,-86.223309),
(39.806779,-86.231943),
(39.819373,-86.264416),
(39.784563,-86.203649),
(39.737514000000004,-86.244924),
(39.168183,-75.601151),
(39.944178,-75.276898),
(41.7492,-87.696616),
(39.951944,-83.035442),
(43.106376000000004,-87.990941),
(41.858878999999995,-87.651306),
(41.935875,-87.659558),
(41.773148,-87.573173),
(42.396165,-87.90571899999999),
(35.419709000000005,-97.44995),
(40.623488,-73.93256600000001),
(40.700506,-73.91743000000001),
(40.74994,-73.50884),
(40.811890,-72.876398),
(41.636898,-87.557792),
(41.856561,-87.72032),
(39.128054,-83.97583900000001),
(33.8077,-81.136227),
(41.559576,-87.54449),
(42.248046,-88.243225),
(39.130946,-75.525627),
(39.774958000000005,-86.090175),
(41.759175,-87.60578199999999),
(39.728131,-86.101420),
(33.717265999999995,-81.108197),
(36.115387,-95.853948),
(35.419048,-97.571844),
(35.742259999999995,-95.34862),
(41.573401000000004,-87.490123),
(41.50781,-74.02096),
(40.03395,-74.86229499999999),
(40.564479,-74.487561),
(41.109408,-74.638930),
(40.800165,-74.019143),
(39.533464,-74.645491),
(41.409116,-81.731269),
(40.829342,-73.900422),
(40.638857,-73.894521),
(40.750659999999996,-73.313633),
(39.796477,-86.088273),
(39.769234000000004,-86.11956500000001),
(39.97039,-75.24393),
(41.851874,-87.72913100000001),
(40.827685,-81.259995),
(35.857896999999994,-95.82749100000001),
(41.864585,-87.747508),
(41.515052000000004,-87.665853),
(41.656508,-87.736747),
(35.571201,-97.660172),
(39.837539,-86.131078),
(39.785102,-86.099746),
(39.685718,-86.056172),
(39.819470,-86.103890),
(41.866531,-80.77341700000001),
(39.433587,-84.486180),
(39.157871,-84.398033),
(41.745331,-87.615931),
(41.879896,-87.733476),
(35.528344,-97.591051),
(44.918675,-123.306700),
(39.510735,-84.40045),
(40.017552,-82.979178),
(39.103740,-84.607357),
(41.634645,-87.606341),
(41.589342,-87.601627),
(41.710137,-87.626289),
(41.663577000000004,-87.631241),
(41.214958,-111.953162),
(39.743773,-86.128632),
(39.925222,-75.15358),
(40.046079999999996,-75.17834),
(40.157799,-82.455534),
(42.989209,-87.968809),
(35.201771,-106.621426),
(41.922856,-87.776493),
(41.737628,-87.763108),
(41.620045000000005,-87.755145),
(39.801882,-86.063721),
(39.730920000000005,-86.154457),
(39.867152000000004,-86.082842),
(40.873164,-73.836762),
(40.685396999999995,-73.871752),
(40.694204,-73.619261),
(40.783194,-73.188743),
(39.506586,-84.40012800000001),
(41.41026,-81.99468900000001),
(41.755022,-87.607404),
(41.911744,-87.771147),
(36.070892,-95.88180200000001),
(40.228749,-74.767702),
(40.004839,-74.222287),
(40.38412,-74.41274),
(40.139146000000004,-74.24348),
(39.726099,-86.128693),
(39.732369,-86.132086),
(39.95811,-83.02879),
(40.664467,-111.98503999999998),
(40.689111,-112.011801),
(41.767390,-87.652571),
(36.07444,-95.844052),
(36.115035999999996,-95.968453),
(27.800281,-82.698916),
(30.171339,-85.676559),
(29.256758,-81.048323),
(26.956559999999996,-82.138705),
(30.407175,-87.370906),
(39.793007,-86.203950),
(26.593334999999996,-80.161785),
(42.365213,-123.32595500000001),
(44.919297,-123.01568999999999),
(39.962150,-82.013274),
(27.955330,-82.516410),
(28.545735999999998,-82.460216),
(25.976413,-80.407612),
(39.787217999999996,-86.100268),
(39.96922,-75.23456999999999),
(26.613462,-81.737485),
(41.772583,-87.652883),
(40.869777,-82.53616099999999),
(40.826974,-81.408739),
(41.154572,-80.662092),
(33.996613,-81.21680500000001),
(26.185739,-80.27369),
(26.567830999999998,-80.174483),
(41.627383,-87.57273199999999),
(39.820678,-86.151177),
(39.670036,-86.190843),
(41.543724,-87.715980),
(39.284647,-84.493792),
(28.379933,-82.673988),
(41.570686,-88.006764),
(26.130346,-80.162546),
(27.772243,-82.664162),
(40.73018,-73.44937900000001),
(40.699276,-73.61978),
(40.667196000000004,-73.582577),
(28.358944,-82.139142),
(39.787741,-86.118434),
(30.346180,-81.689457),
(25.914454,-80.232452),
(27.97118,-82.46544),
(28.596004999999998,-81.468362),
(41.667322,-87.634684),
(39.844181,-75.243143),
(39.918893,-74.174968),
(40.52361,-74.2672),
(40.734867,-74.251249),
(40.759387,-74.214388),
(40.794693,-81.391487),
(25.965018,-80.227145),
(39.742778,-86.135621),
(40.019634,-75.15268),
(46.849595,-122.865839),
(26.241302,-80.15414),
(41.763684999999995,-87.609967),
(41.759754,-87.627639),
(41.879156,-87.756738),
(41.447873,-81.69848499999999),
(39.991023999999996,-83.000851),
(40.708321999999995,-112.108023),
(40.695335,-112.05377800000001),
(47.118965,-122.262440),
(47.212641,-122.43626200000001),
(46.889837,-122.60983799999998),
(28.921946999999996,-82.619963),
(28.264046,-82.20332900000001),
(41.774192,-87.68180600000001),
(41.761003,-87.603854),
(41.745913,-87.56093299999999),
(41.763815,-87.671949),
(35.451751,-97.454270),
(35.581184,-97.540602),
(41.029993,-111.972772),
(26.199435,-80.140824),
(25.596027,-80.318586),
(29.01155,-82.073198),
(39.824831,-86.04047299999999),
(41.698009000000006,-87.648291),
(41.447258000000005,-81.597049),
(28.063999,-80.610517),
(41.815878999999995,-87.616135),
(41.791985,-87.66731700000001),
(26.662702000000003,-81.84251400000001),
(25.903581,-80.167541),
(30.259698999999998,-81.771889),
(41.474691,-82.137018),
(30.353861,-81.510728),
(30.190036,-81.619937),
(26.848971999999996,-80.148173),
(41.746881,-87.548254),
(41.841778000000005,-87.70093100000001),
(27.481553,-81.437520),
(26.000089000000003,-80.151652),
(25.860132,-80.229223),
(39.819587,-86.079734),
(39.766620,-86.012413),
(41.249704,-80.795099),
(47.984432,-122.17981499999999),
(47.699026,-117.37915100000001),
(28.568233000000003,-81.381354),
(29.014653000000003,-81.31781),
(41.510088,-87.659354),
(41.759428,-87.622034),
(41.527102,-87.757115),
(44.958556,-123.027069),
(40.841699,-73.868849),
(40.773327,-73.233047),
(40.765319,-73.216794),
(40.811324,-72.979295),
(40.78561,-73.18685),
(28.84693,-81.656564),
(41.784565,-87.660303),
(40.737057,-74.213295),
(26.539639,-80.104148),
(40.0036,-74.871877),
(39.879275,-74.162074),
(40.880249,-74.015514),
(40.755755,-74.217598),
(39.801838000000004,-75.059435),
(41.010624,-74.006208),
(40.59047,-74.42017),
(43.137529,-93.419721),
(39.783023,-86.096631),
(39.824459999999995,-86.114746),
(39.804429,-86.165409),
(39.816988,-86.141908),
(39.729131,-86.133023),
(39.817781,-86.106471),
(39.77642,-86.11219100000001),
(39.739087,-86.15952800000001),
(39.720099,-86.232497),
(39.891077,-85.94534499999999),
(39.832491999999995,-85.994881),
(39.828095,-86.061473),
(39.895786,-85.955819),
(39.829638,-86.169606),
(39.830605,-86.089671),
(39.768992,-86.31458),
(41.092078,-81.554796),
(46.12151,-122.95595800000001),
(47.494288,-122.184868),
(26.16935,-80.23761999999999),
(28.965404,-82.440595),
(30.384441,-81.715536),
(30.271799,-81.565628),
(41.588989,-87.775512),
(41.886139,-87.766258),
(35.403471,-97.482536),
(27.938014,-82.777381),
(27.999775,-82.752135),
(39.718540000000004,-86.24645699999999),
(39.810571,-86.094182),
(39.780834,-86.117252),
(41.883741,-87.749948),
(41.767353,-87.63378),
(41.192757,-112.028057),
(41.127055,-112.030851),
(39.837706,-86.265239),
(39.860284,-86.121888),
(29.061564,-81.25828100000001),
(41.767443,-87.681146),
(40.008611,-82.978505),
(27.99594,-82.78003000000001),
(27.761072,-82.649227),
(41.24904,-111.965351),
(39.842751,-75.399813),
(41.909408,-88.083392),
(38.923497999999995,-83.992541),
(30.392118,-81.715609),
(28.633560,-81.426786),
(28.290465,-82.691734),
(27.120976000000002,-82.439152),
(28.960421999999998,-81.184882),
(40.221061999999996,-111.64616799999999),
(30.352782,-81.636198),
(30.379573999999998,-87.10879399999999),
(39.750353000000004,-86.142128),
(39.82519,-86.022131),
(40.761502,-73.272050),
(30.400275,-81.724434),
(26.618027,-81.576492),
(26.625173,-81.566937),
(28.162621,-82.56673),
(28.016417999999998,-82.58710699999999),
(26.790131,-80.078351),
(42.324008,-87.850375),
(43.133412,-88.008202),
(41.217479,-111.95853600000001),
(41.538775,-81.61020400000001),
(37.963335,-87.528585),
(25.844425,-80.214903),
(42.720139,-77.870583),
(41.457102,-81.592386),
(41.564859999999996,-81.55611400000001),
(41.477461,-81.577938),
(39.954942,-86.036914),
(40.681349,-75.151371),
(40.265756,-74.752162),
(40.786568,-74.183669),
(39.922534999999996,-74.98352299999999),
(40.703725,-74.26382199999999),
(39.941346,-74.549492),
(40.034177,-74.958721),
(40.629878000000005,-74.26809200000001),
(40.44153,-74.52213),
(40.749732,-74.216883),
(40.552238,-74.537425),
(25.988979,-80.222739),
(25.73977,-80.402549),
(40.827263,-115.81138600000001),
(42.891506,-78.807064),
(39.102537,-84.58255600000001),
(39.749441,-84.16760500000001),
(45.456782000000004,-123.83920800000001),
(43.933578,-117.289751),
(34.972646000000005,-82.228893),
(35.114892,-82.404045),
(44.508249,-88.029093),
(28.061712,-82.534488),
(30.40035,-81.726384),
(30.328514,-81.463926)
;
SET @latitude=41.5814387, @longitude=-87.71004549999999, @distance=0.1
;
SET @lng_min = @longitude - @distance/abs(cos(radians(@latitude))*69)
, @lng_max = @longitude + @distance/abs(cos(radians(@latitude))*69)
, @lat_min = @latitude - (@distance/69)
, @lat_max = @latitude + (@distance/69)
;
SET @line = ST_SRID(LineString(Point(@lng_max, @lat_max), Point(@lng_min, @lat_min)), 4326)
;

-- MySQL 8.0.28
EXPLAIN ANALYZE
SELECT *
FROM spatial_test
WHERE MBRContains(@line, geo_point)
;

-> Filter: mbrcontains(<cache>((@line)),spatial_test.geo_point)  (cost=64.16 rows=142) (actual time=0.125..0.127 rows=1 loops=1)
    -> Index range scan on spatial_test using geo_point over (geo_point unprintable_geometry_value)  (cost=64.16 rows=142) (actual time=0.111..0.113 rows=1 loops=1)

-- MySQL 8.0.29
EXPLAIN ANALYZE
SELECT *
FROM spatial_test
WHERE MBRContains(@line, geo_point)
;

-> Filter: mbrcontains(<cache>((@line)),spatial_test.geo_point)  (cost=100.25 rows=1000) (actual time=2.308..10.035 rows=1 loops=1)
    -> Table scan on spatial_test  (cost=100.25 rows=1000) (actual time=0.047..1.415 rows=1000 loops=1)

MySQL prefer full table scan (does not use SPATIAL geo_point INDEX) and work slow

-- MySQL 8.0.29 + FORCE INDEX
EXPLAIN ANALYZE
SELECT *
FROM spatial_test FORCE INDEX (geo_point)
WHERE MBRContains(@line, geo_point)
;

-> Filter: mbrcontains(<cache>((@line)),spatial_test.geo_point)  (cost=113776.91 rows=252837) (actual time=0.166..0.169 rows=1 loops=1)
    -> Index range scan on spatial_test using geo_point over (geo_point unprintable_geometry_value)  (cost=113776.91 rows=252837) (actual time=0.141..0.143 rows=1 loops=1)

Work fast and fine, but it becomes clear that the optimizer incorrectly calculated cost and rows
rows=252837 - table has only 1000 rows

Hello Александр Ммммммм,

Thank you for the report and test case.

regards,
Umesh

Disregard previous--moved to respin.

Documented fix as follows in the MySQL 8.0.30 changelog:

    Upgrading to MySQL 8.0.29 led to issues with existing spatial
    indexes. The root cause of the problem was a change in how
    geographic area computations were performed by the included
    Boost library, which was upgraded to version 1.77.0 in MySQL
    8.0.29. We fix this by ensuring that we accommodate the new
    method whenever such computations are performed.

Closed.