1# This is a testcase copied from mysql-test/main/gis.test 2 3--source include/have_innodb.inc 4-- source include/have_geometry.inc 5--source include/have_sequence.inc 6 7SET default_storage_engine=InnoDB; 8 9# 10# Spatial objects 11# 12 13--disable_warnings 14DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; 15--enable_warnings 16 17CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT); 18CREATE TABLE gis_line (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING); 19CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON); 20CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT); 21CREATE TABLE gis_multi_line (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING); 22CREATE TABLE gis_multi_polygon (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON); 23CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION); 24CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY); 25 26SHOW FIELDS FROM gis_point; 27SHOW FIELDS FROM gis_line; 28SHOW FIELDS FROM gis_polygon; 29SHOW FIELDS FROM gis_multi_point; 30SHOW FIELDS FROM gis_multi_line; 31SHOW FIELDS FROM gis_multi_polygon; 32SHOW FIELDS FROM gis_geometrycollection; 33SHOW FIELDS FROM gis_geometry; 34 35 36INSERT INTO gis_point VALUES 37(101, ST_PointFromText('POINT(10 10)')), 38(102, ST_PointFromText('POINT(20 10)')), 39(103, ST_PointFromText('POINT(20 20)')), 40(104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)')))); 41 42INSERT INTO gis_line VALUES 43(105, ST_LineFromText('LINESTRING(0 0,0 10,10 0)')), 44(106, ST_LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')), 45(107, ST_LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(40, 10))))); 46 47INSERT INTO gis_polygon VALUES 48(108, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')), 49(109, ST_PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')), 50(110, ST_PolyFromWKB(ST_AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))))); 51 52INSERT INTO gis_multi_point VALUES 53(111, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')), 54(112, ST_MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')), 55(113, ST_MPointFromWKB(ST_AsWKB(MultiPoint(Point(3, 6), Point(4, 10))))); 56 57INSERT INTO gis_multi_line VALUES 58(114, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')), 59(115, ST_MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')), 60(116, ST_MLineFromWKB(ST_AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))))); 61 62 63INSERT INTO gis_multi_polygon VALUES 64(117, ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')), 65(118, ST_MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')), 66(119, ST_MPolyFromWKB(ST_AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))))); 67 68INSERT INTO gis_geometrycollection VALUES 69(120, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')), 70(121, ST_GeometryFromWKB(ST_AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))))); 71 72INSERT into gis_geometry SELECT * FROM gis_point; 73INSERT into gis_geometry SELECT * FROM gis_line; 74INSERT into gis_geometry SELECT * FROM gis_polygon; 75INSERT into gis_geometry SELECT * FROM gis_multi_point; 76INSERT into gis_geometry SELECT * FROM gis_multi_line; 77INSERT into gis_geometry SELECT * FROM gis_multi_polygon; 78INSERT into gis_geometry SELECT * FROM gis_geometrycollection; 79 80SELECT fid, ST_AsText(g) FROM gis_point; 81SELECT fid, ST_AsText(g) FROM gis_line; 82SELECT fid, ST_AsText(g) FROM gis_polygon; 83SELECT fid, ST_AsText(g) FROM gis_multi_point; 84SELECT fid, ST_AsText(g) FROM gis_multi_line; 85SELECT fid, ST_AsText(g) FROM gis_multi_polygon; 86SELECT fid, ST_AsText(g) FROM gis_geometrycollection; 87SELECT fid, ST_AsText(g) FROM gis_geometry; 88 89SELECT fid, ST_Dimension(g) FROM gis_geometry; 90SELECT fid, ST_GeometryType(g) FROM gis_geometry; 91SELECT fid, ST_IsEmpty(g) FROM gis_geometry; 92SELECT fid, ST_AsText(ST_Envelope(g)) FROM gis_geometry; 93--replace_column 10 # 94explain extended select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry; 95 96SELECT fid, ST_X(g) FROM gis_point; 97SELECT fid, ST_Y(g) FROM gis_point; 98--replace_column 10 # 99explain extended select ST_X(g),ST_Y(g) FROM gis_point; 100 101SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line; 102SELECT fid, ST_AsText(ST_EndPoint(g)) FROM gis_line; 103SELECT fid, ST_Length(g) FROM gis_line; 104SELECT fid, ST_NumPoints(g) FROM gis_line; 105SELECT fid, ST_AsText(ST_PointN(g, 2)) FROM gis_line; 106SELECT fid, ST_IsClosed(g) FROM gis_line; 107--replace_column 10 # 108explain extended select ST_AsText(ST_StartPoint(g)),ST_AsText(ST_EndPoint(g)),ST_Length(g),ST_NumPoints(g),ST_AsText(ST_PointN(g, 2)),ST_IsClosed(g) FROM gis_line; 109 110SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon; 111SELECT fid, ST_Area(g) FROM gis_polygon; 112SELECT fid, ST_AsText(ST_ExteriorRing(g)) FROM gis_polygon; 113SELECT fid, ST_NumInteriorRings(g) FROM gis_polygon; 114SELECT fid, ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon; 115--replace_column 10 # 116explain extended select ST_AsText(ST_Centroid(g)),ST_Area(g),ST_AsText(ST_ExteriorRing(g)),ST_NumInteriorRings(g),ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon; 117 118SELECT fid, ST_IsClosed(g) FROM gis_multi_line; 119 120SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_multi_polygon; 121SELECT fid, ST_Area(g) FROM gis_multi_polygon; 122 123SELECT fid, ST_NumGeometries(g) from gis_multi_point; 124SELECT fid, ST_NumGeometries(g) from gis_multi_line; 125SELECT fid, ST_NumGeometries(g) from gis_multi_polygon; 126SELECT fid, ST_NumGeometries(g) from gis_geometrycollection; 127--replace_column 10 # 128explain extended SELECT fid, ST_NumGeometries(g) from gis_multi_point; 129 130SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point; 131SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_line; 132SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_polygon; 133SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_geometrycollection; 134SELECT fid, ST_AsText(ST_GeometryN(g, 1)) from gis_geometrycollection; 135--replace_column 10 # 136explain extended SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point; 137 138SELECT g1.fid as first, g2.fid as second, 139MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o, 140MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t, 141MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r 142FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; 143--replace_column 10 # 144explain extended SELECT g1.fid as first, g2.fid as second, 145MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o, 146MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t, 147MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r 148FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; 149 150DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; 151 152# 153# Check that ALTER TABLE doesn't loose geometry type 154# 155CREATE TABLE t1 ( 156 gp point, 157 ln linestring, 158 pg polygon, 159 mp multipoint, 160 mln multilinestring, 161 mpg multipolygon, 162 gc geometrycollection, 163 gm geometry 164); 165 166SHOW FIELDS FROM t1; 167ALTER TABLE t1 ADD fid INT NOT NULL; 168SHOW FIELDS FROM t1; 169DROP TABLE t1; 170 171SELECT ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_GeometryFromText('POINT(1 4)')))); 172explain extended SELECT ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_GeometryFromText('POINT(1 4)')))); 173explain extended SELECT ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_PointFromText('POINT(1 4)')))); 174SELECT ST_SRID(ST_GeomFromText('LineString(1 1,2 2)',101)); 175explain extended SELECT ST_SRID(ST_GeomFromText('LineString(1 1,2 2)',101)); 176#select ST_issimple(MultiPoint(Point(3, 6), Point(4, 10))), ST_issimple(Point(3, 6)),ST_issimple(ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),ST_issimple(ST_GeometryFromText('POINT(1 4)')), ST_issimple(ST_AsWKB(ST_GeometryFromText('POINT(1 4)'))); 177explain extended select ST_issimple(MultiPoint(Point(3, 6), Point(4, 10))), ST_issimple(Point(3, 6)); 178 179create table t1 (a geometry not null); 180insert into t1 values (ST_GeomFromText('Point(1 2)')); 181-- error 1416 182insert into t1 values ('Garbage'); 183-- error 1416 184insert IGNORE into t1 values ('Garbage'); 185alter table t1 add spatial index(a); 186 187drop table t1; 188 189# 190# Bug #5219: problem with range optimizer 191# 192 193create table t1(a geometry not null, spatial index(a)); 194insert into t1 values 195(ST_GeomFromText('POINT(1 1)')), (ST_GeomFromText('POINT(3 3)')), 196(ST_GeomFromText('POINT(4 4)')), (ST_GeomFromText('POINT(6 6)')); 197select ST_AsText(a) from t1 where 198 MBRContains(ST_GeomFromText('Polygon((0 0, 0 2, 2 2, 2 0, 0 0))'), a) 199 or 200 MBRContains(ST_GeomFromText('Polygon((2 2, 2 5, 5 5, 5 2, 2 2))'), a); 201select ST_AsText(a) from t1 where 202 MBRContains(ST_GeomFromText('Polygon((0 0, 0 2, 2 2, 2 0, 0 0))'), a) 203 and 204 MBRContains(ST_GeomFromText('Polygon((0 0, 0 7, 7 7, 7 0, 0 0))'), a); 205drop table t1; 206 207CREATE TABLE t1 (Coordinates POINT NOT NULL, SPATIAL INDEX(Coordinates)); 208INSERT INTO t1 VALUES(ST_GeomFromText('POINT(383293632 1754448)')); 209INSERT INTO t1 VALUES(ST_GeomFromText('POINT(564952612 157516260)')); 210INSERT INTO t1 VALUES(ST_GeomFromText('POINT(903994614 180726515)')); 211INSERT INTO t1 VALUES(ST_GeomFromText('POINT(98128178 141127631)')); 212INSERT INTO t1 VALUES(ST_GeomFromText('POINT(862547902 799334546)')); 213INSERT INTO t1 VALUES(ST_GeomFromText('POINT(341989013 850270906)')); 214INSERT INTO t1 VALUES(ST_GeomFromText('POINT(803302376 93039099)')); 215INSERT INTO t1 VALUES(ST_GeomFromText('POINT(857439153 817431356)')); 216INSERT INTO t1 VALUES(ST_GeomFromText('POINT(319757546 343162742)')); 217INSERT INTO t1 VALUES(ST_GeomFromText('POINT(826341972 717484432)')); 218INSERT INTO t1 VALUES(ST_GeomFromText('POINT(305066789 201736238)')); 219INSERT INTO t1 VALUES(ST_GeomFromText('POINT(626068992 616241497)')); 220INSERT INTO t1 VALUES(ST_GeomFromText('POINT(55789424 755830108)')); 221INSERT INTO t1 VALUES(ST_GeomFromText('POINT(802874458 312435220)')); 222INSERT INTO t1 VALUES(ST_GeomFromText('POINT(153795660 551723671)')); 223INSERT INTO t1 VALUES(ST_GeomFromText('POINT(242207428 537089292)')); 224INSERT INTO t1 VALUES(ST_GeomFromText('POINT(553478119 807160039)')); 225INSERT INTO t1 VALUES(ST_GeomFromText('POINT(694605552 457472733)')); 226INSERT INTO t1 VALUES(ST_GeomFromText('POINT(987886554 792733729)')); 227INSERT INTO t1 VALUES(ST_GeomFromText('POINT(598600363 850434457)')); 228INSERT INTO t1 VALUES(ST_GeomFromText('POINT(592068275 940589376)')); 229INSERT INTO t1 VALUES(ST_GeomFromText('POINT(700705362 395370650)')); 230INSERT INTO t1 VALUES(ST_GeomFromText('POINT(33628474 558144514)')); 231INSERT INTO t1 VALUES(ST_GeomFromText('POINT(212802006 353386020)')); 232INSERT INTO t1 VALUES(ST_GeomFromText('POINT(901307256 39143977)')); 233INSERT INTO t1 VALUES(ST_GeomFromText('POINT(70870451 206374045)')); 234INSERT INTO t1 VALUES(ST_GeomFromText('POINT(240880214 696939443)')); 235INSERT INTO t1 VALUES(ST_GeomFromText('POINT(822615542 296669638)')); 236INSERT INTO t1 VALUES(ST_GeomFromText('POINT(452769551 625489999)')); 237INSERT INTO t1 VALUES(ST_GeomFromText('POINT(609104858 606565210)')); 238INSERT INTO t1 VALUES(ST_GeomFromText('POINT(177213669 851312285)')); 239INSERT INTO t1 VALUES(ST_GeomFromText('POINT(143654501 730691787)')); 240INSERT INTO t1 VALUES(ST_GeomFromText('POINT(658472325 838260052)')); 241INSERT INTO t1 VALUES(ST_GeomFromText('POINT(188164520 646358878)')); 242INSERT INTO t1 VALUES(ST_GeomFromText('POINT(630993781 786764883)')); 243INSERT INTO t1 VALUES(ST_GeomFromText('POINT(496793334 223062055)')); 244INSERT INTO t1 VALUES(ST_GeomFromText('POINT(727354258 197498696)')); 245INSERT INTO t1 VALUES(ST_GeomFromText('POINT(618432704 760982731)')); 246INSERT INTO t1 VALUES(ST_GeomFromText('POINT(755643210 831234710)')); 247INSERT INTO t1 VALUES(ST_GeomFromText('POINT(114368751 656950466)')); 248INSERT INTO t1 VALUES(ST_GeomFromText('POINT(870378686 185239202)')); 249INSERT INTO t1 VALUES(ST_GeomFromText('POINT(863324511 111258900)')); 250INSERT INTO t1 VALUES(ST_GeomFromText('POINT(882178645 685940052)')); 251INSERT INTO t1 VALUES(ST_GeomFromText('POINT(407928538 334948195)')); 252INSERT INTO t1 VALUES(ST_GeomFromText('POINT(311430051 17033395)')); 253INSERT INTO t1 VALUES(ST_GeomFromText('POINT(941513405 488643719)')); 254INSERT INTO t1 VALUES(ST_GeomFromText('POINT(868345680 85167906)')); 255INSERT INTO t1 VALUES(ST_GeomFromText('POINT(219335507 526818004)')); 256INSERT INTO t1 VALUES(ST_GeomFromText('POINT(923427958 407500026)')); 257INSERT INTO t1 VALUES(ST_GeomFromText('POINT(173176882 554421738)')); 258INSERT INTO t1 VALUES(ST_GeomFromText('POINT(194264908 669970217)')); 259INSERT INTO t1 VALUES(ST_GeomFromText('POINT(777483793 921619165)')); 260INSERT INTO t1 VALUES(ST_GeomFromText('POINT(867468912 395916497)')); 261INSERT INTO t1 VALUES(ST_GeomFromText('POINT(682601897 623112122)')); 262INSERT INTO t1 VALUES(ST_GeomFromText('POINT(227151206 796970647)')); 263INSERT INTO t1 VALUES(ST_GeomFromText('POINT(280062588 97529892)')); 264INSERT INTO t1 VALUES(ST_GeomFromText('POINT(982209849 143387099)')); 265INSERT INTO t1 VALUES(ST_GeomFromText('POINT(208788792 864388493)')); 266INSERT INTO t1 VALUES(ST_GeomFromText('POINT(829327151 616717329)')); 267INSERT INTO t1 VALUES(ST_GeomFromText('POINT(199336688 140757201)')); 268INSERT INTO t1 VALUES(ST_GeomFromText('POINT(633750724 140850093)')); 269INSERT INTO t1 VALUES(ST_GeomFromText('POINT(629400920 502096404)')); 270INSERT INTO t1 VALUES(ST_GeomFromText('POINT(226017998 848736426)')); 271INSERT INTO t1 VALUES(ST_GeomFromText('POINT(28914408 149445955)')); 272INSERT INTO t1 VALUES(ST_GeomFromText('POINT(256236452 202091290)')); 273INSERT INTO t1 VALUES(ST_GeomFromText('POINT(703867693 450501360)')); 274INSERT INTO t1 VALUES(ST_GeomFromText('POINT(872061506 481351486)')); 275INSERT INTO t1 VALUES(ST_GeomFromText('POINT(372120524 739530418)')); 276INSERT INTO t1 VALUES(ST_GeomFromText('POINT(877267982 54722420)')); 277INSERT INTO t1 VALUES(ST_GeomFromText('POINT(362642540 104419188)')); 278INSERT INTO t1 VALUES(ST_GeomFromText('POINT(851693067 642705127)')); 279INSERT INTO t1 VALUES(ST_GeomFromText('POINT(201949080 833902916)')); 280INSERT INTO t1 VALUES(ST_GeomFromText('POINT(786092225 410737872)')); 281INSERT INTO t1 VALUES(ST_GeomFromText('POINT(698291409 615419376)')); 282INSERT INTO t1 VALUES(ST_GeomFromText('POINT(27455201 897628096)')); 283INSERT INTO t1 VALUES(ST_GeomFromText('POINT(756176576 661205925)')); 284INSERT INTO t1 VALUES(ST_GeomFromText('POINT(38478189 385577496)')); 285INSERT INTO t1 VALUES(ST_GeomFromText('POINT(163302328 264496186)')); 286INSERT INTO t1 VALUES(ST_GeomFromText('POINT(234313922 192216735)')); 287INSERT INTO t1 VALUES(ST_GeomFromText('POINT(413942141 490550373)')); 288INSERT INTO t1 VALUES(ST_GeomFromText('POINT(394308025 117809834)')); 289INSERT INTO t1 VALUES(ST_GeomFromText('POINT(941051732 266369530)')); 290INSERT INTO t1 VALUES(ST_GeomFromText('POINT(599161319 313172256)')); 291INSERT INTO t1 VALUES(ST_GeomFromText('POINT(5899948 476429301)')); 292INSERT INTO t1 VALUES(ST_GeomFromText('POINT(367894677 368542487)')); 293INSERT INTO t1 VALUES(ST_GeomFromText('POINT(580848489 219587743)')); 294INSERT INTO t1 VALUES(ST_GeomFromText('POINT(11247614 782797569)')); 295drop table t1; 296 297create table t1 select ST_GeomFromWKB(POINT(1,3)); 298show create table t1; 299drop table t1; 300 301SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 302CREATE TABLE `t1` (`object_id` bigint(20) unsigned NOT NULL default '0', `geo` 303geometry NOT NULL default ''); 304SET sql_mode = default; 305 306insert into t1 values ('85984',ST_GeomFromText('MULTIPOLYGON(((-115.006363 30736.305435,-114.992394 36.305202,-114.991219 36.305975,-114.991163 30836.306845,-114.989432 36.309452,-114.978275 36.312642,-114.977363 30936.311978,-114.975327 36.312344,-114.96502 36.31597,-114.963364 31036.313629,-114.961723 36.313721,-114.956398 36.316057,-114.951882 31136.320979,-114.947073 36.323475,-114.945207 36.326451,-114.945207 31236.326451,-114.944132 36.326061,-114.94003 36.326588,-114.924017 31336.334484,-114.923281 36.334146,-114.92564 36.331504,-114.94072 31436.319282,-114.945348 36.314812,-114.948091 36.314762,-114.951755 31536.316211,-114.952446 36.313883,-114.952644 36.309488,-114.944725 31636.313083,-114.93706 36.32043,-114.932478 36.323497,-114.924556 31736.327708,-114.922608 36.329715,-114.92009 36.328695,-114.912105 31836.323566,-114.901647 36.317952,-114.897436 36.313968,-114.895344 31936.309573,-114.891699 36.304398,-114.890569 36.303551,-114.886356 32036.302702,-114.885141 36.301351,-114.885709 36.297391,-114.892499 32136.290893,-114.902142 36.288974,-114.904941 36.288838,-114.905308 32236.289845,-114.906325 36.290395,-114.909916 36.289549,-114.914527 32336.287535,-114.918797 36.284423,-114.922982 36.279731,-114.924113 32436.277282,-114.924057 36.275817,-114.927733 36.27053,-114.929354 32536.269029,-114.929354 36.269029,-114.950856 36.268715,-114.950768 32636.264324,-114.960206 36.264293,-114.960301 36.268943,-115.006662 32736.268929,-115.008583 36.265619,-115.00665 36.264247,-115.006659 32836.246873,-115.006659 36.246873,-115.006838 36.247697,-115.010764 32936.247774,-115.015609 36.25113,-115.015765 36.254505,-115.029517 33036.254619,-115.038573 36.249317,-115.038573 36.249317,-115.023403 33136.25841,-115.023873 36.258994,-115.031845 36.259829,-115.03183 33236.261053,-115.025561 36.261095,-115.036417 36.274632,-115.033729 33336.276041,-115.032217 36.274851,-115.029845 36.273959,-115.029934 33436.274966,-115.025763 36.274896,-115.025406 36.281044,-115.028731 33536.284471,-115.036497 36.290377,-115.042071 36.291039,-115.026759 33636.298478,-115.008995 36.301966,-115.006363 36.305435),(-115.079835 33736.244369,-115.079735 36.260186,-115.076435 36.262369,-115.069758 33836.265,-115.070235 36.268757,-115.064542 36.268655,-115.061843 33936.269857,-115.062676 36.270693,-115.06305 36.272344,-115.059051 34036.281023,-115.05918 36.283008,-115.060591 36.285246,-115.061913 34136.290022,-115.062499 36.306353,-115.062499 36.306353,-115.060918 34236.30642,-115.06112 36.289779,-115.05713 36.2825,-115.057314 34336.279446,-115.060779 36.274659,-115.061366 36.27209,-115.057858 34436.26557,-115.055805 36.262883,-115.054688 36.262874,-115.047335 34536.25037,-115.044234 36.24637,-115.052434 36.24047,-115.061734 34636.23507,-115.061934 36.22677,-115.061934 36.22677,-115.061491 34736.225267,-115.062024 36.218194,-115.060134 36.218278,-115.060133 34836.210771,-115.057833 36.210771,-115.057433 36.196271,-115.062233 34936.196271,-115.062233 36.190371,-115.062233 36.190371,-115.065533 35036.190371,-115.071333 36.188571,-115.098331 36.188275,-115.098331 35136.188275,-115.098435 36.237569,-115.097535 36.240369,-115.097535 35236.240369,-115.093235 36.240369,-115.089135 36.240469,-115.083135 35336.240569,-115.083135 36.240569,-115.079835 35436.244369)))')),('85998',ST_GeomFromText('MULTIPOLYGON(((-115.333107 35536.264587,-115.333168 36.280638,-115.333168 36.280638,-115.32226 35636.280643,-115.322538 36.274311,-115.327222 36.274258,-115.32733 35736.263026,-115.330675 36.262984,-115.332132 36.264673,-115.333107 35836.264587),(-115.247239 36.247066,-115.247438 36.218267,-115.247438 35936.218267,-115.278525 36.219263,-115.278525 36.219263,-115.301545 36036.219559,-115.332748 36.219197,-115.332757 36.220041,-115.332757 36136.220041,-115.332895 36.233514,-115.349023 36.233479,-115.351489 36236.234475,-115.353681 36.237021,-115.357106 36.239789,-115.36519 36336.243331,-115.368156 36.243487,-115.367389 36.244902,-115.364553 36436.246014,-115.359219 36.24616,-115.356186 36.248025,-115.353347 36536.248004,-115.350813 36.249507,-115.339673 36.25387,-115.333069 36636.255018,-115.333069 36.255018,-115.333042 36.247767,-115.279039 36736.248666,-115.263639 36.247466,-115.263839 36.252766,-115.261439 36836.252666,-115.261439 36.247366,-115.247239 36.247066)))')); 369 370# Expected result is 115.31877315203187, but IA64 returns 115.31877315203188 371# due to fused multiply-add instructions. 372--replace_result 115.29706047613604 115.2970604672862 36.23335611157958 36.23335610879993 373select object_id, ST_geometrytype(geo), ST_ISSIMPLE(GEO), ST_ASTEXT(ST_centroid(geo)) from 374t1 where object_id=85998; 375 376# Expected result is 36.3310176346905, but IA64 returns 36.3310176346904 377# due to fused multiply-add instructions. 378--replace_result 114.86854470090232 114.86854472054372 36.34725217627852 36.34725218253213 379select object_id, ST_geometrytype(geo), ST_ISSIMPLE(GEO), ST_ASTEXT(ST_centroid(geo)) from 380t1 where object_id=85984; 381 382drop table t1; 383 384create table t1 (fl geometry not null); 385--error 1416 386insert into t1 values (1); 387--error 1416 388insert into t1 values (1.11); 389--error 1416 390insert into t1 values ("qwerty"); 391--error 1048 392insert into t1 values (ST_pointfromtext('point(1,1)')); 393 394drop table t1; 395 396select (ST_asWKT(ST_geomfromwkb((0x000000000140240000000000004024000000000000)))); 397select (ST_asWKT(ST_geomfromwkb((0x010100000000000000000024400000000000002440)))); 398 399--enable_metadata 400create table t1 (g GEOMETRY); 401select * from t1; 402select ST_asbinary(g) from t1; 403--disable_metadata 404drop table t1; 405 406create table t1 (a TEXT, b GEOMETRY NOT NULL, SPATIAL KEY(b)); 407alter table t1 disable keys; 408--error 1263 409load data infile '../../std_data/bad_gis_data.dat' into table t1; 410alter table t1 enable keys; 411drop table t1; 412 413# 414# Bug #26038: is null and bad data 415# 416 417create table t1 (a int, b blob); 418insert into t1 values (1, ''), (2, NULL), (3, '1'); 419select * from t1; 420 421--error ER_ILLEGAL_VALUE_FOR_TYPE 422select 423 ST_geometryfromtext(b) IS NULL, ST_geometryfromwkb(b) IS NULL, ST_astext(b) IS NULL, 424 ST_aswkb(b) IS NULL, ST_geometrytype(b) IS NULL, ST_centroid(b) IS NULL, 425 ST_envelope(b) IS NULL, ST_startpoint(b) IS NULL, ST_endpoint(b) IS NULL, 426 ST_exteriorring(b) IS NULL, ST_pointn(b, 1) IS NULL, ST_geometryn(b, 1) IS NULL, 427 ST_interiorringn(b, 1) IS NULL, multipoint(b) IS NULL, ST_isempty(b) IS NULL, 428 ST_issimple(b) IS NULL, ST_isclosed(b) IS NULL, ST_dimension(b) IS NULL, 429 ST_numgeometries(b) IS NULL, ST_numinteriorrings(b) IS NULL, ST_numpoints(b) IS NULL, 430 ST_area(b) IS NULL, ST_length(b) IS NULL, ST_srid(b) IS NULL, ST_x(b) IS NULL, 431 ST_y(b) IS NULL 432from t1; 433 434select 435 MBRwithin(b, b) IS NULL, MBRcontains(b, b) IS NULL, MBRoverlaps(b, b) IS NULL, 436 MBRequals(b, b) IS NULL, MBRdisjoint(b, b) IS NULL, ST_touches(b, b) IS NULL, 437 MBRintersects(b, b) IS NULL, ST_crosses(b, b) IS NULL 438from t1; 439 440--error ER_ILLEGAL_VALUE_FOR_TYPE 441select 442 point(b, b) IS NULL, linestring(b) IS NULL, polygon(b) IS NULL, multipoint(b) IS NULL, 443 multilinestring(b) IS NULL, multipolygon(b) IS NULL, 444 geometrycollection(b) IS NULL 445from t1; 446 447drop table t1; 448 449# 450# Bug #27164: Crash when mixing InnoDB and MyISAM Geospatial tables 451# 452CREATE TABLE t1(a POINT) ENGINE=MyISAM; 453INSERT INTO t1 VALUES (NULL); 454SELECT * FROM t1; 455DROP TABLE t1; 456 457# 458# Bug #30955 ST_geomfromtext() crasher 459# 460CREATE TABLE `t1` ( `col9` set('a'), `col89` date); 461INSERT IGNORE INTO `t1` VALUES ('','0000-00-00'); 462select ST_geomfromtext(col9,col89) as a from t1; 463DROP TABLE t1; 464 465# 466# Bug #31158 Spatial, Union, LONGBLOB vs BLOB bug (crops data) 467# 468 469CREATE TABLE t1 ( 470 geomdata polygon NOT NULL, 471 SPATIAL KEY index_geom (geomdata) 472) ENGINE=MyISAM DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED; 473 474CREATE TABLE t2 ( 475 geomdata polygon NOT NULL, 476 SPATIAL KEY index_geom (geomdata) 477) ENGINE=MyISAM DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED; 478 479CREATE TABLE t3 480select 481 ST_aswkb(ws.geomdata) AS geomdatawkb 482 from 483 t1 ws 484union 485 select 486 ST_aswkb(ws.geomdata) AS geomdatawkb 487 from 488 t2 ws; 489 490describe t3; 491 492drop table t1; 493drop table t2; 494drop table t3; 495 496# 497# Bug #30284 spatial key corruption 498# 499 500create table t1(col1 geometry default null,col15 geometrycollection not 501null,spatial index(col15),index(col1(15)))engine=innodb; 502# --error ER_CANT_CREATE_GEOMETRY_OBJECT 503insert into t1 set col15 = ST_GeomFromText('POINT(6 5)'); 504# --error ER_CANT_CREATE_GEOMETRY_OBJECT 505insert into t1 set col15 = ST_GeomFromText('POINT(6 5)'); 506check table t1 extended; 507drop table t1; 508 509--echo End of 4.1 tests 510 511# 512# Bug #12281 (Geometry: crash in trigger) 513# 514 515create table t1 (s1 geometry not null,s2 char(100)); 516create trigger t1_bu before update on t1 for each row set new.s1 = null; 517--error 1048 518insert into t1 values (null,null); 519drop table t1; 520 521# 522# Bug #10499 (function creation with GEOMETRY datatype) 523# 524--disable_warnings 525drop procedure if exists fn3; 526--enable_warnings 527create function fn3 () returns point deterministic return ST_GeomFromText("point(1 1)"); 528show create function fn3; 529select ST_astext(fn3()); 530drop function fn3; 531 532# 533# Bug #12267 (primary key over GIS) 534# 535create table t1(pt POINT); 536alter table t1 add primary key pti(pt); 537drop table t1; 538create table t1(pt GEOMETRY); 539--error 1170 540alter table t1 add primary key pti(pt); 541alter table t1 add primary key pti(pt(20)); 542drop table t1; 543 544 545create table t1 select ST_GeomFromText('point(1 1)'); 546desc t1; 547drop table t1; 548 549# 550# Bug #20691 (DEFAULT over NOT NULL field) 551# 552create table t1 (g geometry not null); 553--error ER_CANT_CREATE_GEOMETRY_OBJECT 554insert into t1 values(default); 555drop table t1; 556 557# 558# Bug #27300: create view with geometry functions lost columns types 559# 560CREATE TABLE t1 (a GEOMETRY); 561CREATE VIEW v1 AS SELECT ST_GeomFromwkb(ST_ASBINARY(a)) FROM t1; 562CREATE VIEW v2 AS SELECT a FROM t1; 563DESCRIBE v1; 564DESCRIBE v2; 565 566DROP VIEW v1,v2; 567DROP TABLE t1; 568 569# 570# Bug#24563: MBROverlaps does not seem to function propertly 571# Bug#54888: MBROverlaps missing in 5.1? 572# 573 574# Test all MBR* functions and their non-MBR-prefixed aliases, 575# using shifted squares to verify the spatial relations. 576 577create table t1 (name VARCHAR(100), square GEOMETRY); 578 579INSERT INTO t1 VALUES("center", ST_GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))')); 580 581INSERT INTO t1 VALUES("small", ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')); 582INSERT INTO t1 VALUES("big", ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); 583 584INSERT INTO t1 VALUES("up", ST_GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))')); 585INSERT INTO t1 VALUES("up2", ST_GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))')); 586INSERT INTO t1 VALUES("up3", ST_GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))')); 587 588INSERT INTO t1 VALUES("down", ST_GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))')); 589INSERT INTO t1 VALUES("down2", ST_GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))')); 590INSERT INTO t1 VALUES("down3", ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))')); 591 592INSERT INTO t1 VALUES("right", ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))')); 593INSERT INTO t1 VALUES("right2", ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))')); 594INSERT INTO t1 VALUES("right3", ST_GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))')); 595 596INSERT INTO t1 VALUES("left", ST_GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))')); 597INSERT INTO t1 VALUES("left2", ST_GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))')); 598INSERT INTO t1 VALUES("left3", ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))')); 599 600SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrcontains FROM t1 a1 JOIN t1 a2 ON MBRContains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 601SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrdisjoint FROM t1 a1 JOIN t1 a2 ON MBRDisjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 602SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrequals FROM t1 a1 JOIN t1 a2 ON MBREquals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 603SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrintersect FROM t1 a1 JOIN t1 a2 ON MBRIntersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 604SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbroverlaps FROM t1 a1 JOIN t1 a2 ON MBROverlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 605SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrtouches FROM t1 a1 JOIN t1 a2 ON MBRTouches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 606SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrwithin FROM t1 a1 JOIN t1 a2 ON MBRWithin( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 607 608SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRcontains FROM t1 a1 JOIN t1 a2 ON MBRContains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 609SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRdisjoint FROM t1 a1 JOIN t1 a2 ON MBRDisjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 610SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRequals FROM t1 a1 JOIN t1 a2 ON MBREquals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 611SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS intersects FROM t1 a1 JOIN t1 a2 ON MBRIntersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 612SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRoverlaps FROM t1 a1 JOIN t1 a2 ON MBROverlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 613SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS ST_touches FROM t1 a1 JOIN t1 a2 ON ST_Touches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 614SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS MBRwithin FROM t1 a1 JOIN t1 a2 ON MBRWithin( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 615 616# MBROverlaps needs a few more tests, with point and line dimensions 617 618SET @vert1 = ST_GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))'); 619SET @horiz1 = ST_GeomFromText('POLYGON ((-2 0, 2 0, -2 0))'); 620SET @horiz2 = ST_GeomFromText('POLYGON ((-1 0, 3 0, -1 0))'); 621SET @horiz3 = ST_GeomFromText('POLYGON ((2 0, 3 0, 2 0))'); 622SET @point1 = ST_GeomFromText('POLYGON ((0 0))'); 623SET @point2 = ST_GeomFromText('POLYGON ((-2 0))'); 624 625SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @vert1) GROUP BY a1.name; 626SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @horiz1) GROUP BY a1.name; 627SELECT MBROverlaps(@horiz1, @vert1) FROM DUAL; 628SELECT MBROverlaps(@horiz1, @horiz2) FROM DUAL; 629SELECT MBROverlaps(@horiz1, @horiz3) FROM DUAL; 630SELECT MBROverlaps(@horiz1, @point1) FROM DUAL; 631SELECT MBROverlaps(@horiz1, @point2) FROM DUAL; 632 633DROP TABLE t1; 634 635# 636# Bug#28763: Selecting geometry fields in UNION caused server crash. 637# 638create table t1(f1 geometry, f2 polygon, f3 linestring); 639select f1 from t1 union select f1 from t1; 640insert into t1 (f2,f3) values (ST_GeomFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))'), ST_GeomFromText('LINESTRING(0 0,1 1,2 2)')); 641select ST_AsText(f2),ST_AsText(f3) from t1; 642select ST_AsText(a) from (select f2 as a from t1 union select f3 from t1) t; 643create table t2 as select f2 as a from t1 union select f3 from t1; 644desc t2; 645select ST_AsText(a) from t2; 646drop table t1, t2; 647 648# 649# Bug #29166: MYsql crash when query is run 650# 651 652# The test query itself is not logged : too large output. 653# The real test is the second query : see if the first hasn't crashed the 654# server 655--disable_query_log 656--disable_result_log 657SELECT ST_AsText(ST_GeometryFromText(CONCAT( 658 'MULTIPOLYGON(((', 659 REPEAT ('-0.00000000001234567890123456789012 -0.123456789012345678,', 1000), 660 '-0.00000000001234567890123456789012 -0.123456789012345678', 661 ')))' 662))) AS a; 663--enable_result_log 664--enable_query_log 665SELECT 1; 666 667-- source include/gis_keys.inc 668 669# 670# Bug #31155 gis types in union'd select cause crash 671# 672 673create table `t1` (`col002` point)engine=innodb; 674insert into t1 values (),(),(); 675# --error ER_WRONG_ARGUMENTS 676select min(`col002`) from t1 union select `col002` from t1; 677drop table t1; 678 679--echo # 680--echo # Bug #47780: crash when comparing GIS items from subquery 681--echo # 682 683CREATE TABLE t1(a INT, b MULTIPOLYGON); 684INSERT INTO t1 VALUES 685 (0, 686 ST_GEOMFROMTEXT( 687 'multipolygon(((1 2,3 4,5 6,7 8,9 8,1 2,1 2),(7 6,5 4,3 2,1 2,3 4,7 6)))')); 688 689--echo # must not crash 690--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION 691SELECT 1 FROM t1 WHERE a <> (SELECT ST_GEOMETRYCOLLECTIONFROMWKB(b) FROM t1); 692 693DROP TABLE t1; 694 695--echo # 696--echo # Bug #49250 : spatial btree index corruption and crash 697--echo # Part one : spatial syntax check 698--echo # 699 700--error ER_PARSE_ERROR 701CREATE TABLE t1(col1 MULTIPOLYGON NOT NULL, 702 SPATIAL INDEX USING BTREE (col1)); 703CREATE TABLE t2(col1 MULTIPOLYGON NOT NULL); 704--error ER_PARSE_ERROR 705CREATE SPATIAL INDEX USING BTREE ON t2(col); 706--error ER_PARSE_ERROR 707ALTER TABLE t2 ADD SPATIAL INDEX USING BTREE (col1); 708 709DROP TABLE t2; 710 711--echo End of 5.0 tests 712 713 714# 715# Bug #11335 View redefines column types 716# 717create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); 718create view v1 as select * from t1; 719desc v1; 720drop view v1; 721drop table t1; 722 723# 724# Bug#44684: valgrind reports invalid reads in 725# Item_func_spatial_collection::val_str 726# 727--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION 728SELECT MultiPoint(12345,''); 729#SELECT MultiPoint(123451,''); 730#SELECT MultiPoint(1234512,''); 731#SELECT MultiPoint(12345123,''); 732 733--error ER_ILLEGAL_VALUE_FOR_TYPE 734#SELECT MultiLineString(12345,''); 735#SELECT MultiLineString(123451,''); 736#SELECT MultiLineString(1234512,''); 737#SELECT MultiLineString(12345123,''); 738 739--error ER_ILLEGAL_VALUE_FOR_TYPE 740#SELECT LineString(12345,''); 741#SELECT LineString(123451,''); 742#SELECT LineString(1234512,''); 743#SELECT LineString(12345123,''); 744 745--error ER_ILLEGAL_VALUE_FOR_TYPE 746#SELECT Polygon(12345,''); 747#SELECT Polygon(123451,''); 748#SELECT Polygon(1234512,''); 749#SELECT Polygon(12345123,''); 750 751# 752# Bug55531 crash with conversions of geometry types / strings 753# 754--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION 755SELECT 1 FROM (SELECT GREATEST(1,GEOMETRYCOLLECTION('00000','00000')) b FROM DUAL) AS d WHERE (LINESTRING(d.b)); 756 757 758--echo # 759--echo # BUG#51875: crash when loading data into geometry function ST_polyfromwkb 760--echo # 761SET @a=0x00000000030000000100000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440; 762SET @a=ST_POLYFROMWKB(@a); 763SET @a=0x00000000030000000000000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440; 764SET @a=ST_POLYFROMWKB(@a); 765 766 767# 768# Bug #57321 crashes and valgrind errors from spatial types 769# 770 771create table t1(a polygon NOT NULL)engine=innodb; 772# --error ER_CANT_CREATE_GEOMETRY_OBJECT 773--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 774insert into t1 values (ST_geomfromtext("point(0 1)")); 775# --error ER_CANT_CREATE_GEOMETRY_OBJECT 776--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 777insert into t1 values (ST_geomfromtext("point(1 0)")); 778select * from (select polygon(t1.a) as p from t1 order by t1.a) d; 779drop table t1; 780 781 782--echo # 783--echo # Test for bug #59888 "debug assertion when attempt to create spatial index 784--echo # on char > 31 bytes". 785--echo # 786create table t1(a char(32) not null) engine=innodb; 787# --error ER_SPATIAL_MUST_HAVE_GEOM_COL 788--error ER_WRONG_ARGUMENTS 789create spatial index i on t1 (a); 790drop table t1; 791 792 793--echo End of 5.1 tests 794 795# 796# Bug #50574 5.5.ST_x allows spatial indexes on non-spatial 797# columns, causing crashes! 798# Bug#11767480 SPATIAL INDEXES ON NON-SPATIAL COLUMNS 799# CAUSE CRASHES. 800# 801CREATE TABLE t0 (a BINARY(32) NOT NULL); 802# --error ER_SPATIAL_MUST_HAVE_GEOM_COL 803--error ER_WRONG_ARGUMENTS 804CREATE SPATIAL INDEX i on t0 (a); 805INSERT INTO t0 VALUES (1); 806 807# --error ER_SPATIAL_MUST_HAVE_GEOM_COL 808--error ER_WRONG_ARGUMENTS 809CREATE TABLE t1( 810 col0 BINARY NOT NULL, 811 col2 TIMESTAMP, 812 SPATIAL INDEX i1 (col0) 813) ENGINE=MyISAM; 814 815# Test other ways to add indices 816CREATE TABLE t1 ( 817 col0 BINARY NOT NULL, 818 col2 TIMESTAMP 819) ENGINE=MyISAM; 820 821# --error ER_SPATIAL_MUST_HAVE_GEOM_COL 822--error ER_WRONG_ARGUMENTS 823CREATE SPATIAL INDEX idx0 ON t1(col0); 824 825# --error ER_SPATIAL_MUST_HAVE_GEOM_COL 826--error ER_WRONG_ARGUMENTS 827ALTER TABLE t1 ADD SPATIAL INDEX i1 (col0); 828 829CREATE TABLE t2 ( 830 col0 INTEGER NOT NULL, 831 col1 POINT, 832 col2 POINT 833); 834 835# --error ER_TOO_MANY_KEY_PARTS 836--error ER_WRONG_ARGUMENTS 837CREATE SPATIAL INDEX idx0 ON t2 (col1, col2); 838 839# --error ER_TOO_MANY_KEY_PARTS 840--error ER_WRONG_ARGUMENTS 841CREATE TABLE t3 ( 842 col0 INTEGER NOT NULL, 843 col1 POINT, 844 col2 LINESTRING, 845 SPATIAL INDEX i1 (col1, col2) 846); 847 848# cleanup 849DROP TABLE t0, t1, t2; 850 851 852--echo # 853--echo # BUG#12414917 - ST_ISCLOSED() CRASHES ON 64-BIT BUILDS 854--echo # 855# --error ER_GIS_DATA_WRONG_ENDIANESS 856SELECT ST_ISCLOSED(CONVERT(CONCAT(' ', 0x2), BINARY(20))); 857 858--echo # 859--echo # BUG#12537203 - CRASH WHEN SUBSELECTING GLOBAL VARIABLES IN 860--echo # GEOMETRY FUNCTION ARGUMENTS 861--echo # 862--replace_regex /non geometric .* value/non geometric '' value/ 863--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION 864SELECT GEOMETRYCOLLECTION((SELECT @@OLD)); 865 866 867--echo End of 5.1 tests 868 869 870--echo # 871--echo # Bug#11908153: CRASH AND/OR VALGRIND ERRORS IN FIELD_BLOB::GET_KEY_IMAGE 872--echo # 873 874CREATE TABLE g1 875(a geometry NOT NULL, UNIQUE KEY i (a(151))) engine=innodb; 876 877INSERT INTO g1 VALUES (ST_geomfromtext('point(1 1)')); 878INSERT INTO g1 VALUES (ST_geomfromtext('point(1 2)')); 879 880FLUSH TABLES; 881 882--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION 883SELECT 1 FROM g1 884FORCE INDEX(i) WHERE a = date_sub(now(), interval 2808.4 year_month) 885; 886 887DROP TABLE g1; 888 889--echo # 890--echo # Bug#13013970 MORE CRASHES IN FIELD_BLOB::GET_KEY_IMAGE 891--echo # 892 893CREATE TABLE g1(a TEXT NOT NULL, KEY(a(255))); 894 895INSERT INTO g1 VALUES ('a'),('a'); 896SELECT 1 FROM g1 WHERE a >= ANY 897(SELECT 1 FROM g1 WHERE a = ST_geomfromtext('') OR a) ; 898 899DROP TABLE g1; 900 901--echo End of 5.5 tests 902 903 904# Conformance tests 905# 906# C.3.3 Geometry types and functions 907# 908 909--disable_warnings 910DROP DATABASE IF EXISTS gis_ogs; 911--enable_warnings 912 913CREATE DATABASE gis_ogs; 914USE gis_ogs; 915 916--echo # 917--echo # C.3.3.1 Geometry types and functions schema construction 918--echo # 919 920# TODO: WL#2377 921#CREATE TABLE spatial_ref_sys ( 922#ST_srid INTEGER NOT NULL PRIMARY KEY, 923#auth_name CHARACTER VARYING, 924#auth_srid INTEGER, 925#srtext CHARACTER VARYING(2048)); 926 927CREATE TABLE lakes ( 928 fid INTEGER NOT NULL PRIMARY KEY, 929 name CHARACTER VARYING(64), 930 shore POLYGON); 931 932CREATE TABLE road_segments ( 933 fid INTEGER NOT NULL PRIMARY KEY, 934 name CHARACTER VARYING(64), 935 aliases CHARACTER VARYING(64), 936 num_lanes INTEGER, 937 centerline LINESTRING); 938 939CREATE TABLE divided_routes ( 940 fid INTEGER NOT NULL PRIMARY KEY, 941 name CHARACTER VARYING(64), 942 num_lanes INTEGER, 943 centerlines MULTILINESTRING); 944 945CREATE TABLE forests ( 946 fid INTEGER NOT NULL PRIMARY KEY, 947 name CHARACTER VARYING(64), 948 boundary MULTIPOLYGON); 949 950CREATE TABLE bridges ( 951 fid INTEGER NOT NULL PRIMARY KEY, 952 name CHARACTER VARYING(64), 953 position POINT); 954 955CREATE TABLE streams ( 956 fid INTEGER NOT NULL PRIMARY KEY, 957 name CHARACTER VARYING(64), 958 centerline LINESTRING); 959 960CREATE TABLE buildings ( 961 fid INTEGER NOT NULL PRIMARY KEY, 962 address CHARACTER VARYING(64), 963 position POINT, 964 footprint POLYGON); 965 966CREATE TABLE ponds ( 967 fid INTEGER NOT NULL PRIMARY KEY, 968 name CHARACTER VARYING(64), 969 type CHARACTER VARYING(64), 970 shores MULTIPOLYGON); 971 972CREATE TABLE named_places ( 973 fid INTEGER NOT NULL PRIMARY KEY, 974 name CHARACTER VARYING(64), 975 boundary POLYGON); 976 977CREATE TABLE map_neatlines ( 978 fid INTEGER NOT NULL PRIMARY KEY, 979 neatline POLYGON); 980 981--echo # 982--echo # C.3.3.2 Geometry types and functions schema data loading 983--echo # 984 985# TODO: WL#2377 986#-- Spatial Reference System 987#INSERT INTO spatial_ref_sys VALUES 988#(101, 'POSC', 32214, 'PROJCS["UTM_ZONE_14N", 989#GEOGCS["World Geodetic System 72", 990#DATUM["WGS_72", 991#ELLIPSOID["NWL_10D", 6378135, 298.26]], 992#PRIMEM["Greenwich", 0], 993#UNIT["Meter", 1.0]], 994#PROJECTION["Transverse_Mercator"], 995#PARAMETER["False_Easting", 500000.0], 996#PARAMETER["False_Northing", 0.0], 997#PARAMETER["Central_Meridian", -99.0], 998#PARAMETER["Scale_Factor", 0.9996], 999#PARAMETER["Latitude_of_origin", 0.0], 1000#UNIT["Meter", 1.0]]'); 1001 1002--echo # Lakes 1003INSERT INTO lakes VALUES ( 1004101, 'BLUE LAKE', 1005ST_PolyFromText( 1006'POLYGON( 1007(52 18,66 23,73 9,48 6,52 18), 1008(59 18,67 18,67 13,59 13,59 18) 1009)', 1010101)); 1011 1012 1013--echo # Road Segments 1014 1015INSERT INTO road_segments VALUES(102, 'Route 5', NULL, 2, 1016ST_LineFromText( 1017'LINESTRING( 0 18, 10 21, 16 23, 28 26, 44 31 )' ,101)); 1018 1019INSERT INTO road_segments VALUES(103, 'Route 5', 'Main Street', 4, 1020ST_LineFromText( 1021'LINESTRING( 44 31, 56 34, 70 38 )' ,101)); 1022 1023INSERT INTO road_segments VALUES(104, 'Route 5', NULL, 2, 1024ST_LineFromText( 1025'LINESTRING( 70 38, 72 48 )' ,101)); 1026 1027INSERT INTO road_segments VALUES(105, 'Main Street', NULL, 4, 1028ST_LineFromText( 1029'LINESTRING( 70 38, 84 42 )' ,101)); 1030 1031INSERT INTO road_segments VALUES(106, 'Dirt Road by Green Forest', NULL, 10321, 1033ST_LineFromText( 1034'LINESTRING( 28 26, 28 0 )',101)); 1035 1036--echo # DividedRoutes 1037 1038INSERT INTO divided_routes VALUES(119, 'Route 75', 4, 1039ST_MLineFromText( 1040'MULTILINESTRING((10 48,10 21,10 0), 1041(16 0,16 23,16 48))', 101)); 1042 1043--echo # Forests 1044 1045INSERT INTO forests VALUES(109, 'Green Forest', 1046ST_MPolyFromText( 1047'MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26), 1048(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))', 1049101)); 1050 1051--echo # Bridges 1052 1053INSERT INTO bridges VALUES(110, 'Cam Bridge', ST_PointFromText( 1054'POINT( 44 31 )', 101)); 1055 1056--echo # Streams 1057 1058INSERT INTO streams VALUES(111, 'Cam Stream', 1059ST_LineFromText( 1060'LINESTRING( 38 48, 44 41, 41 36, 44 31, 52 18 )', 101)); 1061 1062INSERT INTO streams VALUES(112, NULL, 1063ST_LineFromText( 1064'LINESTRING( 76 0, 78 4, 73 9 )', 101)); 1065 1066--echo # Buildings 1067 1068INSERT INTO buildings VALUES(113, '123 Main Street', 1069ST_PointFromText( 1070'POINT( 52 30 )', 101), 1071ST_PolyFromText( 1072'POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )', 101)); 1073 1074INSERT INTO buildings VALUES(114, '215 Main Street', 1075ST_PointFromText( 1076'POINT( 64 33 )', 101), 1077ST_PolyFromText( 1078'POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )', 101)); 1079 1080 1081--echo # Ponds 1082 1083INSERT INTO ponds VALUES(120, NULL, 'Stock Pond', 1084ST_MPolyFromText( 1085'MULTIPOLYGON( ( ( 24 44, 22 42, 24 40, 24 44) ), 1086( ( 26 44, 26 40, 28 42, 26 44) ) )', 101)); 1087 1088--echo # Named Places 1089 1090INSERT INTO named_places VALUES(117, 'Ashton', 1091ST_PolyFromText( 1092'POLYGON( ( 62 48, 84 48, 84 30, 56 30, 56 34, 62 48) )', 101)); 1093 1094INSERT INTO named_places VALUES(118, 'Goose Island', 1095ST_PolyFromText( 1096'POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )', 101)); 1097 1098--echo # Map Neatlines 1099 1100INSERT INTO map_neatlines VALUES(115, 1101ST_PolyFromText( 1102'POLYGON( ( 0 0, 0 48, 84 48, 84 0, 0 0 ) )', 101)); 1103 1104--echo # 1105--echo # C.3.3.3 Geometry types and functions schema test queries 1106--echo 1107 1108# TODO: WL#2377 1109#--echo # Conformance Item T1 1110#SELECT f_table_name 1111#FROM geometry_columns; 1112# 1113#--echo # Conformance Item T2 1114#SELECT f_geometry_column 1115#FROM geometry_columns 1116#WHERE f_table_name = 'streams'; 1117# 1118#--echo # Conformance Item T3 1119#SELECT coord_dimension 1120#FROM geometry_columns 1121#WHERE f_table_name = 'streams'; 1122# 1123#--echo # Conformance Item T4 1124# 1125#SELECT ST_srid 1126#FROM geometry_columns 1127#WHERE f_table_name = 'streams'; 1128# 1129#--echo # Conformance Item T5 1130# 1131#SELECT srtext 1132#FROM SPATIAL_REF_SYS 1133#WHERE ST_SRID = 101; 1134# 1135 1136 1137--echo # Conformance Item T6 1138# TODO: ST_Dimension() alias 1139SELECT ST_Dimension(shore) 1140FROM lakes 1141WHERE name = 'Blue Lake'; 1142 1143--echo # Conformance Item T7 1144# TODO: ST_GeometryType() alias 1145SELECT ST_GeometryType(centerlines) 1146FROM divided_routes 1147WHERE name = 'Route 75'; 1148 1149--echo # Conformance Item T8 1150# TODO: ST_AsText() alias 1151SELECT ST_AsText(boundary) 1152FROM named_places 1153WHERE name = 'Goose Island'; 1154 1155--echo # Conformance Item T9 1156# TODO: ST_AsBinary(), ST_PolyFromWKB() aliases 1157SELECT ST_AsText(ST_PolyFromWKB(ST_AsBinary(boundary),101)) 1158FROM named_places 1159WHERE name = 'Goose Island'; 1160 1161--echo # Conformance Item T10 1162# TODO: ST_SRID() alias 1163SELECT ST_SRID(boundary) 1164FROM named_places 1165WHERE name = 'Goose Island'; 1166 1167--echo # Conformance Item T11 1168# TODO: ST_IsEmpty() alias 1169SELECT ST_IsEmpty(centerline) 1170FROM road_segments 1171WHERE name = 'Route 5' 1172AND aliases = 'Main Street'; 1173 1174# FIXME: get wrong result:0, expected 1. 1175#--echo # Conformance Item T12 1176# TODO: ST_IsSimple() alias 1177#SELECT ST_IsSimple(shore) 1178#FROM lakes 1179#WHERE name = 'Blue Lake'; 1180 1181# TODO: WL#2377 1182#--echo # Conformance Item T13 1183#SELECT ST_AsText(Boundary((boundary),101) 1184#FROM named_places 1185#WHERE name = 'Goose Island'; 1186 1187--echo # Conformance Item T14 1188# TODO: ST_Envelope( ) alias 1189# FIXME: we get anticlockwise, GIS suggests clockwise 1190SELECT ST_AsText(ST_Envelope(boundary)) 1191FROM named_places 1192WHERE name = 'Goose Island'; 1193 1194--echo # Conformance Item T15 1195# TODO: ST_X() alias 1196SELECT ST_X(position) 1197FROM bridges 1198WHERE name = 'Cam Bridge'; 1199 1200--echo # Conformance Item T16 1201# TODO: ST_Y() alias 1202SELECT ST_Y(position) 1203FROM bridges 1204WHERE name = 'Cam Bridge'; 1205 1206--echo # Conformance Item T17 1207# TODO: ST_StartPoint() alias 1208SELECT ST_AsText(ST_StartPoint(centerline)) 1209FROM road_segments 1210WHERE fid = 102; 1211 1212--echo # Conformance Item T18 1213# TODO: ST_EndPoint 1214SELECT ST_AsText(ST_EndPoint(centerline)) 1215FROM road_segments 1216WHERE fid = 102; 1217 1218# TODO: WL#2377 1219#--echo # Conformance Item T19 1220# TODO: ST_LineFromWKB() alias 1221#SELECT ST_IsClosed(LineFromWKB(ST_AsBinary(Boundary(boundary)),ST_SRID(boundary))) 1222#FROM named_places 1223#WHERE name = 'Goose Island'; 1224 1225# TODO: WL#2377 1226#--echo # Conformance Item T20 1227#SELECT IsRing(LineFromWKB(ST_AsBinary(Boundary(boundary)),ST_SRID(boundary))) 1228#FROM named_places 1229#WHERE name = 'Goose Island'; 1230 1231--echo # Conformance Item T21 1232# TODO: ST_Length() alias 1233SELECT ST_Length(centerline) 1234FROM road_segments 1235WHERE fid = 106; 1236 1237--echo # Conformance Item T22 1238# TODO: ST_NumPoints() alias 1239SELECT ST_NumPoints(centerline) 1240FROM road_segments 1241WHERE fid = 102; 1242 1243--echo # Conformance Item T23 1244# TODO: ST_PointN() alias 1245SELECT ST_AsText(ST_PointN(centerline, 1)) 1246FROM road_segments 1247WHERE fid = 102; 1248 1249--echo # Conformance Item T24 1250# TODO: ST_Centroid() alias 1251SELECT ST_AsText(ST_Centroid(boundary)) 1252FROM named_places 1253WHERE name = 'Goose Island'; 1254 1255# TODO: WL#2377 1256#--echo # Conformance Item T25 1257#SELECT MBRContains(boundary, PointOnSurface(boundary)) 1258#FROM named_places 1259#WHERE name = 'Goose Island'; 1260 1261--echo # Conformance Item T26 1262# TODO: ST_Area() alias 1263SELECT ST_Area(boundary) 1264FROM named_places 1265WHERE name = 'Goose Island'; 1266 1267--echo # Conformance Item T27 1268# TODO: ST_ExteriorRing() alias 1269SELECT ST_AsText(ST_ExteriorRing(shore)) 1270FROM lakes 1271WHERE name = 'Blue Lake'; 1272 1273--echo # Conformance Item T28 1274# TODO: ST_NumInteriorRings() alias 1275SELECT ST_NumInteriorRings(shore) 1276FROM lakes 1277WHERE name = 'Blue Lake'; 1278 1279--echo # Conformance Item T29 1280# TODO: ST_InteriorRingN() alias 1281SELECT ST_AsText(ST_InteriorRingN(shore, 1)) 1282FROM lakes 1283WHERE name = 'Blue Lake'; 1284 1285--echo # Conformance Item T30 1286# TODO: ST_NumGeometries() alias 1287SELECT ST_NumGeometries(centerlines) 1288FROM divided_routes 1289WHERE name = 'Route 75'; 1290 1291--echo # Conformance Item T31 1292# TODO: ST_GeometryN() alias 1293SELECT ST_AsText(ST_GeometryN(centerlines, 2)) 1294FROM divided_routes 1295WHERE name = 'Route 75'; 1296 1297--echo # Conformance Item T32 1298# TODO: ST_IsClosed() alias 1299SELECT ST_IsClosed(centerlines) 1300FROM divided_routes 1301WHERE name = 'Route 75'; 1302 1303--echo # Conformance Item T33 1304# TODO: ST_Length() alias 1305SELECT ST_Length(centerlines) 1306FROM divided_routes 1307WHERE name = 'Route 75'; 1308 1309--echo # Conformance Item T34 1310# TODO: ST_Centroid() alias 1311SELECT ST_AsText(ST_Centroid(shores)) 1312FROM ponds 1313WHERE fid = 120; 1314 1315# TODO: WL#2377 1316#--echo # Conformance Item T35 1317#SELECT MBRContains(shores, PointOnSurface(shores)) 1318#FROM ponds 1319#WHERE fid = 120; 1320 1321--echo # Conformance Item T36 1322# TODO: ST_Area() alias 1323SELECT ST_Area(shores) 1324FROM ponds 1325WHERE fid = 120; 1326 1327--echo # Conformance Item T37 1328# TODO: ST_PolyFromText() alias 1329SELECT ST_Equals(boundary, 1330ST_PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',101)) 1331FROM named_places 1332WHERE name = 'Goose Island'; 1333 1334--echo # Conformance Item T38 1335SELECT ST_Disjoint(centerlines, boundary) 1336FROM divided_routes, named_places 1337WHERE divided_routes.name = 'Route 75' 1338AND named_places.name = 'Ashton'; 1339 1340--echo # Conformance Item T39 1341SELECT ST_Touches(centerline, shore) 1342FROM streams, lakes 1343WHERE streams.name = 'Cam Stream' 1344AND lakes.name = 'Blue Lake'; 1345 1346# FIXME: wrong result: get 0, expected 1 1347#--echo # Conformance Item T40 1348#SELECT ST_Within(boundary, footprint) 1349#FROM named_places, buildings 1350#WHERE named_places.name = 'Ashton' 1351#AND buildings.address = '215 Main Street'; 1352 1353# FIXME: wrong result: get 0, expected 1 1354#--echo # Conformance Item T41 1355#SELECT ST_Overlaps(forests.boundary, named_places.boundary) 1356#FROM forests, named_places 1357#WHERE forests.name = 'Green Forest' 1358#AND named_places.name = 'Ashton'; 1359 1360--echo # Conformance Item T42 1361# FIXME: TODO: ST_Crosses() alias 1362SELECT ST_Crosses(road_segments.centerline, divided_routes.centerlines) 1363FROM road_segments, divided_routes 1364WHERE road_segments.fid = 102 1365AND divided_routes.name = 'Route 75'; 1366 1367--echo # Conformance Item T43 1368SELECT ST_Intersects(road_segments.centerline, divided_routes.centerlines) 1369FROM road_segments, divided_routes 1370WHERE road_segments.fid = 102 1371AND divided_routes.name = 'Route 75'; 1372 1373--echo # Conformance Item T44 1374SELECT ST_Contains(forests.boundary, named_places.boundary) 1375FROM forests, named_places 1376WHERE forests.name = 'Green Forest' 1377AND named_places.name = 'Ashton'; 1378 1379# TODO: WL#2377 1380#--echo # Conformance Item T45 1381#SELECT Relate(forests.boundary, named_places.boundary, 'TTTTTTTTT') 1382#FROM forests, named_places 1383#WHERE forests.name = 'Green Forest' 1384#AND named_places.name = 'Ashton'; 1385 1386--echo # Conformance Item T46 1387SELECT ST_Distance(position, boundary) 1388FROM bridges, named_places 1389WHERE bridges.name = 'Cam Bridge' 1390AND named_places.name = 'Ashton'; 1391 1392# FIXME: wrong result: NULL, expected 12 1393#--echo # Conformance Item T47 1394#SELECT ST_AsText(ST_Intersection(centerline, shore)) 1395#FROM streams, lakes 1396#WHERE streams.name = 'Cam Stream' 1397#AND lakes.name = 'Blue Lake'; 1398 1399--echo # Conformance Item T48 1400SELECT ST_AsText(ST_Difference(named_places.boundary, forests.boundary)) 1401FROM named_places, forests 1402WHERE named_places.name = 'Ashton' 1403AND forests.name = 'Green Forest'; 1404 1405#--echo # Conformance Item T49 1406SELECT ST_AsText(ST_Union(shore, boundary)) 1407FROM lakes, named_places 1408WHERE lakes.name = 'Blue Lake' 1409AND named_places.name = 'Goose Island'; 1410 1411--echo # Conformance Item T50 1412SELECT ST_AsText(ST_SymDifference(shore, boundary)) 1413FROM lakes, named_places 1414WHERE lakes.name = 'Blue Lake' 1415AND named_places.name = 'Ashton'; 1416 1417--echo # Conformance Item T51 1418SELECT count(*) 1419FROM buildings, bridges 1420WHERE ST_Contains(ST_Buffer(bridges.position, 15.0), buildings.footprint) = 1; 1421 1422# TODO: WL#2377 1423#--echo # Conformance Item T52 1424#SELECT ST_AsText(ConvexHull(shore)) 1425#FROM lakes 1426#WHERE lakes.name = 'Blue Lake'; 1427 1428--echo # 1429--echo # Bug#13362660 ASSERTION `FIELD_POS < FIELD_COUNT' FAILED. IN PROTOCOL_TEXT::STORE 1430--echo # 1431 1432--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION 1433SELECT ST_Union('', ''), md5(1); 1434 1435--echo # 1436--echo # MDEV-25758 InnoDB spatial indexes miss large geometry 1437--echo # fields after MDEV-25459 1438--echo # 1439CREATE TABLE t1(l LINESTRING NOT NULL, SPATIAL INDEX(l))ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; 1440SELECT GROUP_CONCAT(CONCAT(seq, ' ', seq) SEPARATOR ',') INTO @g FROM seq_0_to_190; 1441INSERT INTO t1 SET l=ST_GeomFromText(CONCAT('LINESTRING(',@g,',0 0)')); 1442SELECT COUNT(*) FROM t1 WHERE MBRIntersects(GeomFromText('Polygon((0 0,0 10,10 10,10 0,0 0))'), l); 1443DROP TABLE t1; 1444 1445DROP DATABASE gis_ogs; 1446