diff options
Diffstat (limited to 'mysql-test/suite/innodb_gis/t/1.test')
| -rw-r--r-- | mysql-test/suite/innodb_gis/t/1.test | 1441 |
1 files changed, 1441 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb_gis/t/1.test b/mysql-test/suite/innodb_gis/t/1.test new file mode 100644 index 00000000..99651842 --- /dev/null +++ b/mysql-test/suite/innodb_gis/t/1.test @@ -0,0 +1,1441 @@ +#***************************************************************** +# The Orginal name of the testcase : gis.test +# The following are the modification done +# 1.SET default_storage_engine=InnoDB; +# 2.MyiSam to Innodb where ever is required. +# 3.Spatial index to Index as Innodb does not support +#***************************************************************** +--source include/have_innodb.inc +-- source include/have_geometry.inc + +SET default_storage_engine=InnoDB; +SET innodb_strict_mode=OFF; + +# +# Spatial objects +# + +CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT); +CREATE TABLE gis_line (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING); +CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON); +CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT); +CREATE TABLE gis_multi_line (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING); +CREATE TABLE gis_multi_polygon (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON); +CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION); +CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY); + +SHOW FIELDS FROM gis_point; +SHOW FIELDS FROM gis_line; +SHOW FIELDS FROM gis_polygon; +SHOW FIELDS FROM gis_multi_point; +SHOW FIELDS FROM gis_multi_line; +SHOW FIELDS FROM gis_multi_polygon; +SHOW FIELDS FROM gis_geometrycollection; +SHOW FIELDS FROM gis_geometry; + + +INSERT INTO gis_point VALUES +(101, ST_PointFromText('POINT(10 10)')), +(102, ST_PointFromText('POINT(20 10)')), +(103, ST_PointFromText('POINT(20 20)')), +(104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)')))); + +INSERT INTO gis_line VALUES +(105, ST_LineFromText('LINESTRING(0 0,0 10,10 0)')), +(106, ST_LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')), +(107, ST_LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(40, 10))))); + +INSERT INTO gis_polygon VALUES +(108, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')), +(109, ST_PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')), +(110, ST_PolyFromWKB(ST_AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))))); + +INSERT INTO gis_multi_point VALUES +(111, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')), +(112, ST_MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')), +(113, ST_MPointFromWKB(ST_AsWKB(MultiPoint(Point(3, 6), Point(4, 10))))); + +INSERT INTO gis_multi_line VALUES +(114, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')), +(115, ST_MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')), +(116, ST_MLineFromWKB(ST_AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))))); + + +INSERT INTO gis_multi_polygon VALUES +(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)))')), +(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)))')), +(119, ST_MPolyFromWKB(ST_AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))))); + +INSERT INTO gis_geometrycollection VALUES +(120, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')), +(121, ST_GeometryFromWKB(ST_AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))))); + +INSERT into gis_geometry SELECT * FROM gis_point; +INSERT into gis_geometry SELECT * FROM gis_line; +INSERT into gis_geometry SELECT * FROM gis_polygon; +INSERT into gis_geometry SELECT * FROM gis_multi_point; +INSERT into gis_geometry SELECT * FROM gis_multi_line; +INSERT into gis_geometry SELECT * FROM gis_multi_polygon; +INSERT into gis_geometry SELECT * FROM gis_geometrycollection; + +SELECT fid, ST_AsText(g) FROM gis_point; +SELECT fid, ST_AsText(g) FROM gis_line; +SELECT fid, ST_AsText(g) FROM gis_polygon; +SELECT fid, ST_AsText(g) FROM gis_multi_point; +SELECT fid, ST_AsText(g) FROM gis_multi_line; +SELECT fid, ST_AsText(g) FROM gis_multi_polygon; +SELECT fid, ST_AsText(g) FROM gis_geometrycollection; +SELECT fid, ST_AsText(g) FROM gis_geometry; + +SELECT fid, ST_Dimension(g) FROM gis_geometry; +SELECT fid, ST_GeometryType(g) FROM gis_geometry; +SELECT fid, ST_IsEmpty(g) FROM gis_geometry; +SELECT fid, ST_AsText(ST_Envelope(g)) FROM gis_geometry; +--replace_column 9 # 10 # +explain extended select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry; + +SELECT fid, ST_X(g) FROM gis_point; +SELECT fid, ST_Y(g) FROM gis_point; +--replace_column 9 # 10 # +explain extended select ST_X(g),ST_Y(g) FROM gis_point; + +SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line; +SELECT fid, ST_AsText(ST_EndPoint(g)) FROM gis_line; +SELECT fid, ST_Length(g) FROM gis_line; +SELECT fid, ST_NumPoints(g) FROM gis_line; +SELECT fid, ST_AsText(ST_PointN(g, 2)) FROM gis_line; +SELECT fid, ST_IsClosed(g) FROM gis_line; +--replace_column 9 # 10 # +explain 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; + +SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon; +SELECT fid, ST_Area(g) FROM gis_polygon; +SELECT fid, ST_AsText(ST_ExteriorRing(g)) FROM gis_polygon; +SELECT fid, ST_NumInteriorRings(g) FROM gis_polygon; +SELECT fid, ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon; +--replace_column 9 # 10 # +explain 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; + +SELECT fid, ST_IsClosed(g) FROM gis_multi_line; + +SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_multi_polygon; +SELECT fid, ST_Area(g) FROM gis_multi_polygon; + +SELECT fid, ST_NumGeometries(g) from gis_multi_point; +SELECT fid, ST_NumGeometries(g) from gis_multi_line; +SELECT fid, ST_NumGeometries(g) from gis_multi_polygon; +SELECT fid, ST_NumGeometries(g) from gis_geometrycollection; +--replace_column 9 # 10 # +explain extended SELECT fid, ST_NumGeometries(g) from gis_multi_point; + +SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point; +SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_line; +SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_polygon; +SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_geometrycollection; +SELECT fid, ST_AsText(ST_GeometryN(g, 1)) from gis_geometrycollection; +--replace_column 9 # 10 # +explain extended SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point; + +SELECT g1.fid as first, g2.fid as second, +MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o, +MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t, +MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r +FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; +--replace_column 9 # 10 # +explain extended SELECT g1.fid as first, g2.fid as second, +MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o, +MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t, +MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r +FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; + +DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; + +# +# Check that ALTER TABLE doesn't loose geometry type +# +CREATE TABLE t1 ( + gp point, + ln linestring, + pg polygon, + mp multipoint, + mln multilinestring, + mpg multipolygon, + gc geometrycollection, + gm geometry +); + +SHOW FIELDS FROM t1; +ALTER TABLE t1 ADD fid INT NOT NULL; +SHOW FIELDS FROM t1; +DROP TABLE t1; + +SELECT ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_GeometryFromText('POINT(1 4)')))); +explain extended SELECT ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_GeometryFromText('POINT(1 4)')))); +explain extended SELECT ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_PointFromText('POINT(1 4)')))); +SELECT ST_SRID(ST_GeomFromText('LineString(1 1,2 2)',101)); +explain extended SELECT ST_SRID(ST_GeomFromText('LineString(1 1,2 2)',101)); +#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)'))); +explain extended select ST_issimple(MultiPoint(Point(3, 6), Point(4, 10))), ST_issimple(Point(3, 6)); + +create table t1 (a geometry not null); +insert into t1 values (ST_GeomFromText('Point(1 2)')); +-- error 1416 +insert into t1 values ('Garbage'); +-- error 1416 +insert IGNORE into t1 values ('Garbage'); + +# Now we support spatial index +alter table t1 add spatial index(a); + +drop table t1; + +# +# Bug #5219: problem with range optimizer +# + +create table t1(a geometry not null, index(a(5))); +insert into t1 values +(ST_GeomFromText('POINT(1 1)')), (ST_GeomFromText('POINT(3 3)')), +(ST_GeomFromText('POINT(4 4)')), (ST_GeomFromText('POINT(6 6)')); +select ST_AsText(a) from t1 where + MBRContains(ST_GeomFromText('Polygon((0 0, 0 2, 2 2, 2 0, 0 0))'), a) + or + MBRContains(ST_GeomFromText('Polygon((2 2, 2 5, 5 5, 5 2, 2 2))'), a); +select ST_AsText(a) from t1 where + MBRContains(ST_GeomFromText('Polygon((0 0, 0 2, 2 2, 2 0, 0 0))'), a) + and + MBRContains(ST_GeomFromText('Polygon((0 0, 0 7, 7 7, 7 0, 0 0))'), a); +drop table t1; + +CREATE TABLE t1 (Coordinates POINT NOT NULL, INDEX(Coordinates(10))); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(383293632 1754448)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(564952612 157516260)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(903994614 180726515)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(98128178 141127631)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(862547902 799334546)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(341989013 850270906)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(803302376 93039099)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(857439153 817431356)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(319757546 343162742)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(826341972 717484432)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(305066789 201736238)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(626068992 616241497)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(55789424 755830108)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(802874458 312435220)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(153795660 551723671)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(242207428 537089292)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(553478119 807160039)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(694605552 457472733)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(987886554 792733729)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(598600363 850434457)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(592068275 940589376)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(700705362 395370650)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(33628474 558144514)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(212802006 353386020)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(901307256 39143977)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(70870451 206374045)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(240880214 696939443)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(822615542 296669638)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(452769551 625489999)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(609104858 606565210)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(177213669 851312285)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(143654501 730691787)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(658472325 838260052)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(188164520 646358878)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(630993781 786764883)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(496793334 223062055)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(727354258 197498696)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(618432704 760982731)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(755643210 831234710)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(114368751 656950466)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(870378686 185239202)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(863324511 111258900)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(882178645 685940052)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(407928538 334948195)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(311430051 17033395)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(941513405 488643719)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(868345680 85167906)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(219335507 526818004)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(923427958 407500026)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(173176882 554421738)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(194264908 669970217)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(777483793 921619165)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(867468912 395916497)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(682601897 623112122)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(227151206 796970647)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(280062588 97529892)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(982209849 143387099)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(208788792 864388493)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(829327151 616717329)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(199336688 140757201)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(633750724 140850093)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(629400920 502096404)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(226017998 848736426)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(28914408 149445955)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(256236452 202091290)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(703867693 450501360)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(872061506 481351486)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(372120524 739530418)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(877267982 54722420)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(362642540 104419188)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(851693067 642705127)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(201949080 833902916)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(786092225 410737872)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(698291409 615419376)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(27455201 897628096)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(756176576 661205925)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(38478189 385577496)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(163302328 264496186)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(234313922 192216735)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(413942141 490550373)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(394308025 117809834)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(941051732 266369530)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(599161319 313172256)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(5899948 476429301)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(367894677 368542487)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(580848489 219587743)')); +INSERT INTO t1 VALUES(ST_GeomFromText('POINT(11247614 782797569)')); +drop table t1; + +create table t1 select ST_GeomFromWKB(POINT(1,3)); +show create table t1; +drop table t1; + +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; + +CREATE TABLE `t1` (`object_id` bigint(20) unsigned NOT NULL default '0', `geo` +geometry NOT NULL default '') ENGINE=InnoDB ; + +SET sql_mode = default; + +insert into t1 values ('85984',ST_GeomFromText('MULTIPOLYGON(((-115.006363 +36.305435,-114.992394 36.305202,-114.991219 36.305975,-114.991163 +36.306845,-114.989432 36.309452,-114.978275 36.312642,-114.977363 +36.311978,-114.975327 36.312344,-114.96502 36.31597,-114.963364 +36.313629,-114.961723 36.313721,-114.956398 36.316057,-114.951882 +36.320979,-114.947073 36.323475,-114.945207 36.326451,-114.945207 +36.326451,-114.944132 36.326061,-114.94003 36.326588,-114.924017 +36.334484,-114.923281 36.334146,-114.92564 36.331504,-114.94072 +36.319282,-114.945348 36.314812,-114.948091 36.314762,-114.951755 +36.316211,-114.952446 36.313883,-114.952644 36.309488,-114.944725 +36.313083,-114.93706 36.32043,-114.932478 36.323497,-114.924556 +36.327708,-114.922608 36.329715,-114.92009 36.328695,-114.912105 +36.323566,-114.901647 36.317952,-114.897436 36.313968,-114.895344 +36.309573,-114.891699 36.304398,-114.890569 36.303551,-114.886356 +36.302702,-114.885141 36.301351,-114.885709 36.297391,-114.892499 +36.290893,-114.902142 36.288974,-114.904941 36.288838,-114.905308 +36.289845,-114.906325 36.290395,-114.909916 36.289549,-114.914527 +36.287535,-114.918797 36.284423,-114.922982 36.279731,-114.924113 +36.277282,-114.924057 36.275817,-114.927733 36.27053,-114.929354 +36.269029,-114.929354 36.269029,-114.950856 36.268715,-114.950768 +36.264324,-114.960206 36.264293,-114.960301 36.268943,-115.006662 +36.268929,-115.008583 36.265619,-115.00665 36.264247,-115.006659 +36.246873,-115.006659 36.246873,-115.006838 36.247697,-115.010764 +36.247774,-115.015609 36.25113,-115.015765 36.254505,-115.029517 +36.254619,-115.038573 36.249317,-115.038573 36.249317,-115.023403 +36.25841,-115.023873 36.258994,-115.031845 36.259829,-115.03183 +36.261053,-115.025561 36.261095,-115.036417 36.274632,-115.033729 +36.276041,-115.032217 36.274851,-115.029845 36.273959,-115.029934 +36.274966,-115.025763 36.274896,-115.025406 36.281044,-115.028731 +36.284471,-115.036497 36.290377,-115.042071 36.291039,-115.026759 +36.298478,-115.008995 36.301966,-115.006363 36.305435),(-115.079835 +36.244369,-115.079735 36.260186,-115.076435 36.262369,-115.069758 +36.265,-115.070235 36.268757,-115.064542 36.268655,-115.061843 +36.269857,-115.062676 36.270693,-115.06305 36.272344,-115.059051 +36.281023,-115.05918 36.283008,-115.060591 36.285246,-115.061913 +36.290022,-115.062499 36.306353,-115.062499 36.306353,-115.060918 +36.30642,-115.06112 36.289779,-115.05713 36.2825,-115.057314 +36.279446,-115.060779 36.274659,-115.061366 36.27209,-115.057858 +36.26557,-115.055805 36.262883,-115.054688 36.262874,-115.047335 +36.25037,-115.044234 36.24637,-115.052434 36.24047,-115.061734 +36.23507,-115.061934 36.22677,-115.061934 36.22677,-115.061491 +36.225267,-115.062024 36.218194,-115.060134 36.218278,-115.060133 +36.210771,-115.057833 36.210771,-115.057433 36.196271,-115.062233 +36.196271,-115.062233 36.190371,-115.062233 36.190371,-115.065533 +36.190371,-115.071333 36.188571,-115.098331 36.188275,-115.098331 +36.188275,-115.098435 36.237569,-115.097535 36.240369,-115.097535 +36.240369,-115.093235 36.240369,-115.089135 36.240469,-115.083135 +36.240569,-115.083135 36.240569,-115.079835 +36.244369)))')),('85998',ST_GeomFromText('MULTIPOLYGON(((-115.333107 +36.264587,-115.333168 36.280638,-115.333168 36.280638,-115.32226 +36.280643,-115.322538 36.274311,-115.327222 36.274258,-115.32733 +36.263026,-115.330675 36.262984,-115.332132 36.264673,-115.333107 +36.264587),(-115.247239 36.247066,-115.247438 36.218267,-115.247438 +36.218267,-115.278525 36.219263,-115.278525 36.219263,-115.301545 +36.219559,-115.332748 36.219197,-115.332757 36.220041,-115.332757 +36.220041,-115.332895 36.233514,-115.349023 36.233479,-115.351489 +36.234475,-115.353681 36.237021,-115.357106 36.239789,-115.36519 +36.243331,-115.368156 36.243487,-115.367389 36.244902,-115.364553 +36.246014,-115.359219 36.24616,-115.356186 36.248025,-115.353347 +36.248004,-115.350813 36.249507,-115.339673 36.25387,-115.333069 +36.255018,-115.333069 36.255018,-115.333042 36.247767,-115.279039 +36.248666,-115.263639 36.247466,-115.263839 36.252766,-115.261439 +36.252666,-115.261439 36.247366,-115.247239 36.247066)))')); + +# Expected result is 115.31877315203187, but IA64 returns 115.31877315203188 +# due to fused multiply-add instructions. +--replace_result 115.29706047613604 115.2970604672862 36.23335611157958 36.23335610879993 +select object_id, ST_geometrytype(geo), ST_ISSIMPLE(GEO), ST_ASTEXT(ST_centroid(geo)) from +t1 where object_id=85998; + +# Expected result is 36.3310176346905, but IA64 returns 36.3310176346904 +# due to fused multiply-add instructions. +--replace_result 114.86854470090232 114.86854472054372 36.34725217627852 36.34725218253213 +select object_id, ST_geometrytype(geo), ST_ISSIMPLE(GEO), ST_ASTEXT(ST_centroid(geo)) from +t1 where object_id=85984; + +drop table t1; + +create table t1 (fl geometry not null); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +insert into t1 values (1); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +insert into t1 values (1.11); +--error 1416 +insert into t1 values ("qwerty"); +--error ER_BAD_NULL_ERROR +insert into t1 values (ST_pointfromtext('point(1,1)')); + +drop table t1; + +select (ST_asWKT(ST_geomfromwkb((0x000000000140240000000000004024000000000000)))); +select (ST_asWKT(ST_geomfromwkb((0x010100000000000000000024400000000000002440)))); + +--enable_metadata +create table t1 (g GEOMETRY); +select * from t1; +select ST_asbinary(g) from t1; +--disable_metadata +drop table t1; + +create table t1 (a TEXT, b GEOMETRY NOT NULL, INDEX(b(5))); +alter table t1 disable keys; +--error 1263 +load data infile '../../std_data/bad_gis_data.dat' into table t1; +alter table t1 enable keys; +drop table t1; + +# +# Bug #26038: is null and bad data +# + +create table t1 (a int, b blob); +insert into t1 values (1, ''), (2, NULL), (3, '1'); +select * from t1; + +--error ER_ILLEGAL_VALUE_FOR_TYPE +select + ST_geometryfromtext(b) IS NULL, ST_geometryfromwkb(b) IS NULL, ST_astext(b) IS NULL, + ST_aswkb(b) IS NULL, ST_geometrytype(b) IS NULL, ST_centroid(b) IS NULL, + ST_envelope(b) IS NULL, ST_startpoint(b) IS NULL, ST_endpoint(b) IS NULL, + ST_exteriorring(b) IS NULL, ST_pointn(b, 1) IS NULL, ST_geometryn(b, 1) IS NULL, + ST_interiorringn(b, 1) IS NULL, multipoint(b) IS NULL, ST_isempty(b) IS NULL, + ST_issimple(b) IS NULL, ST_isclosed(b) IS NULL, ST_dimension(b) IS NULL, + ST_numgeometries(b) IS NULL, ST_numinteriorrings(b) IS NULL, ST_numpoints(b) IS NULL, + ST_area(b) IS NULL, ST_length(b) IS NULL, ST_srid(b) IS NULL, ST_x(b) IS NULL, + ST_y(b) IS NULL +from t1; + +select + MBRwithin(b, b) IS NULL, MBRcontains(b, b) IS NULL, MBRoverlaps(b, b) IS NULL, + MBRequals(b, b) IS NULL, MBRdisjoint(b, b) IS NULL, ST_touches(b, b) IS NULL, + MBRintersects(b, b) IS NULL, ST_crosses(b, b) IS NULL +from t1; + +--error ER_ILLEGAL_VALUE_FOR_TYPE +select + point(b, b) IS NULL, linestring(b) IS NULL, polygon(b) IS NULL, multipoint(b) IS NULL, + multilinestring(b) IS NULL, multipolygon(b) IS NULL, + geometrycollection(b) IS NULL +from t1; + +drop table t1; + +# +# Bug #27164: Crash when mixing InnoDB and InnoDB Geospatial tables +# +CREATE TABLE t1(a POINT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +DROP TABLE t1; + +# +# Bug #30955 ST_geomfromtext() crasher +# +CREATE TABLE `t1` ( `col9` set('a'), `col89` date); +INSERT IGNORE INTO `t1` VALUES ('','0000-00-00'); +select ST_geomfromtext(col9,col89) as a from t1; +DROP TABLE t1; + +# +# Bug #31158 Spatial, Union, LONGBLOB vs BLOB bug (crops data) +# + +CREATE TABLE t1 ( + geomdata polygon NOT NULL, + KEY index_geom (geomdata(10)) +) ENGINE=InnoDB DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED; + +CREATE TABLE t2 ( + geomdata polygon NOT NULL, + KEY index_geom (geomdata(10)) +) ENGINE=InnoDB DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED; + +CREATE TABLE t3 +select + ST_aswkb(ws.geomdata) AS geomdatawkb + from + t1 ws +union + select + ST_aswkb(ws.geomdata) AS geomdatawkb + from + t2 ws; + +describe t3; + +drop table t1; +drop table t2; +drop table t3; + +# +# Bug #30284 spatial key corruption +# + +create table t1(col1 geometry default null,col15 geometrycollection not +null, index(col15(5)),index(col1(15)))engine=InnoDB; +# --error ER_CANT_CREATE_GEOMETRY_OBJECT +insert into t1 set col15 = ST_GeomFromText('POINT(6 5)'); +# --error ER_CANT_CREATE_GEOMETRY_OBJECT +insert into t1 set col15 = ST_GeomFromText('POINT(6 5)'); +check table t1 extended; +drop table t1; + +--echo End of 4.1 tests + +# +# Bug #12281 (Geometry: crash in trigger) +# + +create table t1 (s1 geometry not null,s2 char(100)); +create trigger t1_bu before update on t1 for each row set new.s1 = null; +--error 1048 +insert into t1 values (null,null); +drop table t1; + +# +# Bug #10499 (function creation with GEOMETRY datatype) +# +--disable_warnings +drop procedure if exists fn3; +--enable_warnings +create function fn3 () returns point deterministic return ST_GeomFromText("point(1 1)"); +show create function fn3; +select ST_astext(fn3()); +drop function fn3; + +# +# Bug #12267 (primary key over GIS) +# +create table t1(pt POINT); +alter table t1 add primary key pti(pt); +drop table t1; +create table t1(pt GEOMETRY); +--error 1170 +alter table t1 add primary key pti(pt); +alter table t1 add primary key pti(pt(20)); +drop table t1; + + +create table t1 select ST_GeomFromText('point(1 1)'); +desc t1; +drop table t1; + +# +# Bug #20691 (DEFAULT over NOT NULL field) +# +create table t1 (g geometry not null); +--error ER_CANT_CREATE_GEOMETRY_OBJECT +insert into t1 values(default); +drop table t1; + +# +# Bug #27300: create view with geometry functions lost columns types +# +CREATE TABLE t1 (a GEOMETRY); +CREATE VIEW v1 AS SELECT ST_GeomFromwkb(ST_ASBINARY(a)) FROM t1; +CREATE VIEW v2 AS SELECT a FROM t1; +DESCRIBE v1; +DESCRIBE v2; + +DROP VIEW v1,v2; +DROP TABLE t1; + +# +# Bug#24563: MBROverlaps does not seem to function propertly +# Bug#54888: MBROverlaps missing in 5.1? +# + +# Test all MBR* functions and their non-MBR-prefixed aliases, +# using shifted squares to verify the spatial relations. + +create table t1 (name VARCHAR(100), square GEOMETRY); + +INSERT INTO t1 VALUES("center", ST_GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))')); + +INSERT INTO t1 VALUES("small", ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')); +INSERT INTO t1 VALUES("big", ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); + +INSERT INTO t1 VALUES("up", ST_GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))')); +INSERT INTO t1 VALUES("up2", ST_GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))')); +INSERT INTO t1 VALUES("up3", ST_GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))')); + +INSERT INTO t1 VALUES("down", ST_GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))')); +INSERT INTO t1 VALUES("down2", ST_GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))')); +INSERT INTO t1 VALUES("down3", ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))')); + +INSERT INTO t1 VALUES("right", ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))')); +INSERT INTO t1 VALUES("right2", ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))')); +INSERT INTO t1 VALUES("right3", ST_GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))')); + +INSERT INTO t1 VALUES("left", ST_GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))')); +INSERT INTO t1 VALUES("left2", ST_GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))')); +INSERT INTO t1 VALUES("left3", ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))')); + +SELECT 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; +SELECT 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; +SELECT 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; +SELECT 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; +SELECT 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; +SELECT 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; +SELECT 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; + +SELECT 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; +SELECT 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; +SELECT 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; +SELECT 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; +SELECT 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; +SELECT 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; +SELECT 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; + +# MBROverlaps needs a few more tests, with point and line dimensions + +SET @vert1 = ST_GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))'); +SET @horiz1 = ST_GeomFromText('POLYGON ((-2 0, 2 0, -2 0))'); +SET @horiz2 = ST_GeomFromText('POLYGON ((-1 0, 3 0, -1 0))'); +SET @horiz3 = ST_GeomFromText('POLYGON ((2 0, 3 0, 2 0))'); +SET @point1 = ST_GeomFromText('POLYGON ((0 0))'); +SET @point2 = ST_GeomFromText('POLYGON ((-2 0))'); + +SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @vert1) GROUP BY a1.name; +SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @horiz1) GROUP BY a1.name; +SELECT MBROverlaps(@horiz1, @vert1) FROM DUAL; +SELECT MBROverlaps(@horiz1, @horiz2) FROM DUAL; +SELECT MBROverlaps(@horiz1, @horiz3) FROM DUAL; +SELECT MBROverlaps(@horiz1, @point1) FROM DUAL; +SELECT MBROverlaps(@horiz1, @point2) FROM DUAL; + +DROP TABLE t1; + +# +# Bug#28763: Selecting geometry fields in UNION caused server crash. +# +create table t1(f1 geometry, f2 polygon, f3 linestring); +select f1 from t1 union select f1 from t1; +insert 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)')); +select ST_AsText(f2),ST_AsText(f3) from t1; +select ST_AsText(a) from (select f2 as a from t1 union select f3 from t1) t; +create table t2 as select f2 as a from t1 union select f3 from t1; +desc t2; +select ST_AsText(a) from t2; +drop table t1, t2; + +# +# Bug #29166: MYsql crash when query is run +# + +# The test query itself is not logged : too large output. +# The real test is the second query : see if the first hasn't crashed the +# server +--disable_query_log +--disable_result_log +SELECT ST_AsText(ST_GeometryFromText(CONCAT( + 'MULTIPOLYGON(((', + REPEAT ('-0.00000000001234567890123456789012 -0.123456789012345678,', 1000), + '-0.00000000001234567890123456789012 -0.123456789012345678', + ')))' +))) AS a; +--enable_result_log +--enable_query_log +SELECT 1; + +-- source include/gis_keys.inc + +# +# Bug #31155 gis types in union'd select cause crash +# + +create table `t1` (`col002` point)engine=InnoDB; +insert into t1 values (),(),(); +# --error ER_WRONG_ARGUMENTS +select min(`col002`) from t1 union select `col002` from t1; +drop table t1; + +--echo # +--echo # Bug #47780: crash when comparing GIS items from subquery +--echo # + +CREATE TABLE t1(a INT, b MULTIPOLYGON); +INSERT INTO t1 VALUES + (0, + ST_GEOMFROMTEXT( + '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)))')); + +--echo # must not crash +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT 1 FROM t1 WHERE a <> (SELECT ST_GEOMETRYCOLLECTIONFROMWKB(b) FROM t1); + +DROP TABLE t1; + +--echo # +--echo # Bug #49250 : spatial btree index corruption and crash +--echo # Part one : spatial syntax check +--echo # + +--error ER_PARSE_ERROR +CREATE TABLE t1(col1 MULTIPOLYGON NOT NULL, + SPATIAL INDEX USING BTREE (col1)); +CREATE TABLE t2(col1 MULTIPOLYGON NOT NULL); +--error ER_PARSE_ERROR +CREATE SPATIAL INDEX USING BTREE ON t2(col); +--error ER_PARSE_ERROR +ALTER TABLE t2 ADD SPATIAL INDEX USING BTREE (col1); + +DROP TABLE t2; + +--echo End of 5.0 tests + + +# +# Bug #11335 View redefines column types +# +create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); +create view v1 as select * from t1; +desc v1; +drop view v1; +drop table t1; + +# +# Bug#44684: valgrind reports invalid reads in +# Item_func_spatial_collection::val_str +# +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT MultiPoint(12345,''); +#SELECT MultiPoint(123451,''); +#SELECT MultiPoint(1234512,''); +#SELECT MultiPoint(12345123,''); + +--error ER_ILLEGAL_VALUE_FOR_TYPE +#SELECT MultiLineString(12345,''); +#SELECT MultiLineString(123451,''); +#SELECT MultiLineString(1234512,''); +#SELECT MultiLineString(12345123,''); + +--error ER_ILLEGAL_VALUE_FOR_TYPE +#SELECT LineString(12345,''); +#SELECT LineString(123451,''); +#SELECT LineString(1234512,''); +#SELECT LineString(12345123,''); + +--error ER_ILLEGAL_VALUE_FOR_TYPE +#SELECT Polygon(12345,''); +#SELECT Polygon(123451,''); +#SELECT Polygon(1234512,''); +#SELECT Polygon(12345123,''); + +# +# Bug55531 crash with conversions of geometry types / strings +# +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT 1 FROM (SELECT GREATEST(1,GEOMETRYCOLLECTION('00000','00000')) b FROM DUAL) AS d WHERE (LINESTRING(d.b)); + + +--echo # +--echo # BUG#51875: crash when loading data into geometry function ST_polyfromwkb +--echo # +SET @a=0x00000000030000000100000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440; +SET @a=ST_POLYFROMWKB(@a); +SET @a=0x00000000030000000000000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440; +SET @a=ST_POLYFROMWKB(@a); + + +# +# Bug #57321 crashes and valgrind errors from spatial types +# + +create table t1(a polygon NOT NULL)engine=InnoDB; +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +insert into t1 values (ST_geomfromtext("point(0 1)")); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +insert into t1 values (ST_geomfromtext("point(1 0)")); +select * from (select polygon(t1.a) as p from t1 order by t1.a) d; +drop table t1; + + +--echo # +--echo # Test for bug #59888 "debug assertion when attempt to create spatial index +--echo # on char > 31 bytes". +--echo # +create table t1(a char(32) not null) engine=InnoDB; +#--error ER_SPATIAL_MUST_HAVE_GEOM_COL +create index i on t1 (a(5)); +drop table t1; + + +--echo End of 5.1 tests + +# +# Bug #50574 5.5.ST_x allows spatial indexes on non-spatial +# columns, causing crashes! +# Bug#11767480 SPATIAL INDEXES ON NON-SPATIAL COLUMNS +# CAUSE CRASHES. +# +CREATE TABLE t0 (a BINARY(32) NOT NULL); +#--error ER_SPATIAL_MUST_HAVE_GEOM_COL +CREATE UNIQUE INDEX i on t0 (a(10)); +INSERT INTO t0 VALUES (1); + +#--error ER_SPATIAL_MUST_HAVE_GEOM_COL +CREATE TABLE t1( + col0 BINARY NOT NULL, + col2 TIMESTAMP, + INDEX i1 (col0) +) ENGINE=InnoDB; + +# Test other ways to add indices +CREATE TABLE t2 ( + col0 BINARY NOT NULL, + col2 TIMESTAMP +) ENGINE=InnoDB; + +#--error ER_SPATIAL_MUST_HAVE_GEOM_COL +CREATE INDEX idx0 ON t1(col0); + +#--error ER_SPATIAL_MUST_HAVE_GEOM_COL +#ALTER TABLE t1 ADD INDEX i1 (col0); + +CREATE TABLE t3 ( + col0 INTEGER NOT NULL, + col1 POINT, + col2 POINT +); + +# --error ER_TOO_MANY_KEY_PARTS +--error ER_WRONG_ARGUMENTS +CREATE SPATIAL INDEX idx0 ON t2 (col1, col2); + + +CREATE TABLE t4 ( + col0 INTEGER NOT NULL, + col1 POINT, + col2 LINESTRING, + INDEX i1 (col1(5), col2(5)) +); + +# cleanup +DROP TABLE IF EXISTS t0, t1, t2, t3,t4; + + +--echo # +--echo # BUG#12414917 - ST_ISCLOSED() CRASHES ON 64-BIT BUILDS +--echo # +# --error ER_GIS_DATA_WRONG_ENDIANESS +SELECT ST_ISCLOSED(CONVERT(CONCAT(' ', 0x2), BINARY(20))); + +--echo # +--echo # BUG#12537203 - CRASH WHEN SUBSELECTING GLOBAL VARIABLES IN +--echo # GEOMETRY FUNCTION ARGUMENTS +--echo # +--replace_regex /non geometric .* value/non geometric '' value/ +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT GEOMETRYCOLLECTION((SELECT @@OLD)); + + +--echo End of 5.1 tests + + +--echo # +--echo # Bug#11908153: CRASH AND/OR VALGRIND ERRORS IN FIELD_BLOB::GET_KEY_IMAGE +--echo # + +CREATE TABLE g1 +(a geometry NOT NULL, UNIQUE KEY i (a(151))) engine=InnoDB; + +INSERT INTO g1 VALUES (ST_geomfromtext('point(1 1)')); +INSERT INTO g1 VALUES (ST_geomfromtext('point(1 2)')); + +FLUSH TABLES; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT 1 FROM g1 +FORCE INDEX(i) WHERE a = date_sub(now(), interval 2808.4 year_month) +; + +DROP TABLE g1; + +--echo # +--echo # Bug#13013970 MORE CRASHES IN FIELD_BLOB::GET_KEY_IMAGE +--echo # + +CREATE TABLE g1(a TEXT NOT NULL, KEY(a(255))); + +INSERT INTO g1 VALUES ('a'),('a'); +SELECT 1 FROM g1 WHERE a >= ANY +(SELECT 1 FROM g1 WHERE a = ST_geomfromtext('') OR a) ; + +DROP TABLE g1; + +--echo End of 5.5 tests + + +# Conformance tests +# +# C.3.3 Geometry types and functions +# + +--disable_warnings +DROP DATABASE IF EXISTS gis_ogs; +--enable_warnings + +CREATE DATABASE gis_ogs; +USE gis_ogs; + +--echo # +--echo # C.3.3.1 Geometry types and functions schema construction +--echo # + +# TODO: WL#2377 +#CREATE TABLE spatial_ref_sys ( +#ST_srid INTEGER NOT NULL PRIMARY KEY, +#auth_name CHARACTER VARYING, +#auth_srid INTEGER, +#srtext CHARACTER VARYING(2048)); + +CREATE TABLE lakes ( + fid INTEGER NOT NULL PRIMARY KEY, + name CHARACTER VARYING(64), + shore POLYGON); + +CREATE TABLE road_segments ( + fid INTEGER NOT NULL PRIMARY KEY, + name CHARACTER VARYING(64), + aliases CHARACTER VARYING(64), + num_lanes INTEGER, + centerline LINESTRING); + +CREATE TABLE divided_routes ( + fid INTEGER NOT NULL PRIMARY KEY, + name CHARACTER VARYING(64), + num_lanes INTEGER, + centerlines MULTILINESTRING); + +CREATE TABLE forests ( + fid INTEGER NOT NULL PRIMARY KEY, + name CHARACTER VARYING(64), + boundary MULTIPOLYGON); + +CREATE TABLE bridges ( + fid INTEGER NOT NULL PRIMARY KEY, + name CHARACTER VARYING(64), + position POINT); + +CREATE TABLE streams ( + fid INTEGER NOT NULL PRIMARY KEY, + name CHARACTER VARYING(64), + centerline LINESTRING); + +CREATE TABLE buildings ( + fid INTEGER NOT NULL PRIMARY KEY, + address CHARACTER VARYING(64), + position POINT, + footprint POLYGON); + +CREATE TABLE ponds ( + fid INTEGER NOT NULL PRIMARY KEY, + name CHARACTER VARYING(64), + type CHARACTER VARYING(64), + shores MULTIPOLYGON); + +CREATE TABLE named_places ( + fid INTEGER NOT NULL PRIMARY KEY, + name CHARACTER VARYING(64), + boundary POLYGON); + +CREATE TABLE map_neatlines ( + fid INTEGER NOT NULL PRIMARY KEY, + neatline POLYGON); + +--echo # +--echo # C.3.3.2 Geometry types and functions schema data loading +--echo # + +# TODO: WL#2377 +#-- Spatial Reference System +#INSERT INTO spatial_ref_sys VALUES +#(101, 'POSC', 32214, 'PROJCS["UTM_ZONE_14N", +#GEOGCS["World Geodetic System 72", +#DATUM["WGS_72", +#ELLIPSOID["NWL_10D", 6378135, 298.26]], +#PRIMEM["Greenwich", 0], +#UNIT["Meter", 1.0]], +#PROJECTION["Transverse_Mercator"], +#PARAMETER["False_Easting", 500000.0], +#PARAMETER["False_Northing", 0.0], +#PARAMETER["Central_Meridian", -99.0], +#PARAMETER["Scale_Factor", 0.9996], +#PARAMETER["Latitude_of_origin", 0.0], +#UNIT["Meter", 1.0]]'); + +--echo # Lakes +INSERT INTO lakes VALUES ( +101, 'BLUE LAKE', +ST_PolyFromText( +'POLYGON( +(52 18,66 23,73 9,48 6,52 18), +(59 18,67 18,67 13,59 13,59 18) +)', +101)); + + +--echo # Road Segments + +INSERT INTO road_segments VALUES(102, 'Route 5', NULL, 2, +ST_LineFromText( +'LINESTRING( 0 18, 10 21, 16 23, 28 26, 44 31 )' ,101)); + +INSERT INTO road_segments VALUES(103, 'Route 5', 'Main Street', 4, +ST_LineFromText( +'LINESTRING( 44 31, 56 34, 70 38 )' ,101)); + +INSERT INTO road_segments VALUES(104, 'Route 5', NULL, 2, +ST_LineFromText( +'LINESTRING( 70 38, 72 48 )' ,101)); + +INSERT INTO road_segments VALUES(105, 'Main Street', NULL, 4, +ST_LineFromText( +'LINESTRING( 70 38, 84 42 )' ,101)); + +INSERT INTO road_segments VALUES(106, 'Dirt Road by Green Forest', NULL, +1, +ST_LineFromText( +'LINESTRING( 28 26, 28 0 )',101)); + +--echo # DividedRoutes + +INSERT INTO divided_routes VALUES(119, 'Route 75', 4, +ST_MLineFromText( +'MULTILINESTRING((10 48,10 21,10 0), +(16 0,16 23,16 48))', 101)); + +--echo # Forests + +INSERT INTO forests VALUES(109, 'Green Forest', +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)))', +101)); + +--echo # Bridges + +INSERT INTO bridges VALUES(110, 'Cam Bridge', ST_PointFromText( +'POINT( 44 31 )', 101)); + +--echo # Streams + +INSERT INTO streams VALUES(111, 'Cam Stream', +ST_LineFromText( +'LINESTRING( 38 48, 44 41, 41 36, 44 31, 52 18 )', 101)); + +INSERT INTO streams VALUES(112, NULL, +ST_LineFromText( +'LINESTRING( 76 0, 78 4, 73 9 )', 101)); + +--echo # Buildings + +INSERT INTO buildings VALUES(113, '123 Main Street', +ST_PointFromText( +'POINT( 52 30 )', 101), +ST_PolyFromText( +'POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )', 101)); + +INSERT INTO buildings VALUES(114, '215 Main Street', +ST_PointFromText( +'POINT( 64 33 )', 101), +ST_PolyFromText( +'POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )', 101)); + + +--echo # Ponds + +INSERT INTO ponds VALUES(120, NULL, 'Stock Pond', +ST_MPolyFromText( +'MULTIPOLYGON( ( ( 24 44, 22 42, 24 40, 24 44) ), +( ( 26 44, 26 40, 28 42, 26 44) ) )', 101)); + +--echo # Named Places + +INSERT INTO named_places VALUES(117, 'Ashton', +ST_PolyFromText( +'POLYGON( ( 62 48, 84 48, 84 30, 56 30, 56 34, 62 48) )', 101)); + +INSERT INTO named_places VALUES(118, 'Goose Island', +ST_PolyFromText( +'POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )', 101)); + +--echo # Map Neatlines + +INSERT INTO map_neatlines VALUES(115, +ST_PolyFromText( +'POLYGON( ( 0 0, 0 48, 84 48, 84 0, 0 0 ) )', 101)); + +--echo # +--echo # C.3.3.3 Geometry types and functions schema test queries +--echo + +# TODO: WL#2377 +#--echo # Conformance Item T1 +#SELECT f_table_name +#FROM geometry_columns; +# +#--echo # Conformance Item T2 +#SELECT f_geometry_column +#FROM geometry_columns +#WHERE f_table_name = 'streams'; +# +#--echo # Conformance Item T3 +#SELECT coord_dimension +#FROM geometry_columns +#WHERE f_table_name = 'streams'; +# +#--echo # Conformance Item T4 +# +#SELECT ST_srid +#FROM geometry_columns +#WHERE f_table_name = 'streams'; +# +#--echo # Conformance Item T5 +# +#SELECT srtext +#FROM SPATIAL_REF_SYS +#WHERE ST_SRID = 101; +# + + +--echo # Conformance Item T6 +# TODO: ST_Dimension() alias +SELECT ST_Dimension(shore) +FROM lakes +WHERE name = 'Blue Lake'; + +--echo # Conformance Item T7 +# TODO: ST_GeometryType() alias +SELECT ST_GeometryType(centerlines) +FROM divided_routes +WHERE name = 'Route 75'; + +--echo # Conformance Item T8 +# TODO: ST_AsText() alias +SELECT ST_AsText(boundary) +FROM named_places +WHERE name = 'Goose Island'; + +--echo # Conformance Item T9 +# TODO: ST_AsBinary(), ST_PolyFromWKB() aliases +SELECT ST_AsText(ST_PolyFromWKB(ST_AsBinary(boundary),101)) +FROM named_places +WHERE name = 'Goose Island'; + +--echo # Conformance Item T10 +# TODO: ST_SRID() alias +SELECT ST_SRID(boundary) +FROM named_places +WHERE name = 'Goose Island'; + +--echo # Conformance Item T11 +# TODO: ST_IsEmpty() alias +SELECT ST_IsEmpty(centerline) +FROM road_segments +WHERE name = 'Route 5' +AND aliases = 'Main Street'; + +# FIXME: get wrong result:0, expected 1. +#--echo # Conformance Item T12 +# TODO: ST_IsSimple() alias +#SELECT ST_IsSimple(shore) +#FROM lakes +#WHERE name = 'Blue Lake'; + +# TODO: WL#2377 +#--echo # Conformance Item T13 +#SELECT ST_AsText(Boundary((boundary),101) +#FROM named_places +#WHERE name = 'Goose Island'; + +--echo # Conformance Item T14 +# TODO: ST_Envelope( ) alias +# FIXME: we get anticlockwise, GIS suggests clockwise +SELECT ST_AsText(ST_Envelope(boundary)) +FROM named_places +WHERE name = 'Goose Island'; + +--echo # Conformance Item T15 +# TODO: ST_X() alias +SELECT ST_X(position) +FROM bridges +WHERE name = 'Cam Bridge'; + +--echo # Conformance Item T16 +# TODO: ST_Y() alias +SELECT ST_Y(position) +FROM bridges +WHERE name = 'Cam Bridge'; + +--echo # Conformance Item T17 +# TODO: ST_StartPoint() alias +SELECT ST_AsText(ST_StartPoint(centerline)) +FROM road_segments +WHERE fid = 102; + +--echo # Conformance Item T18 +# TODO: ST_EndPoint +SELECT ST_AsText(ST_EndPoint(centerline)) +FROM road_segments +WHERE fid = 102; + +# TODO: WL#2377 +#--echo # Conformance Item T19 +# TODO: ST_LineFromWKB() alias +#SELECT ST_IsClosed(LineFromWKB(ST_AsBinary(Boundary(boundary)),ST_SRID(boundary))) +#FROM named_places +#WHERE name = 'Goose Island'; + +# TODO: WL#2377 +#--echo # Conformance Item T20 +#SELECT IsRing(LineFromWKB(ST_AsBinary(Boundary(boundary)),ST_SRID(boundary))) +#FROM named_places +#WHERE name = 'Goose Island'; + +--echo # Conformance Item T21 +# TODO: ST_Length() alias +SELECT ST_Length(centerline) +FROM road_segments +WHERE fid = 106; + +--echo # Conformance Item T22 +# TODO: ST_NumPoints() alias +SELECT ST_NumPoints(centerline) +FROM road_segments +WHERE fid = 102; + +--echo # Conformance Item T23 +# TODO: ST_PointN() alias +SELECT ST_AsText(ST_PointN(centerline, 1)) +FROM road_segments +WHERE fid = 102; + +--echo # Conformance Item T24 +# TODO: ST_Centroid() alias +SELECT ST_AsText(ST_Centroid(boundary)) +FROM named_places +WHERE name = 'Goose Island'; + +# TODO: WL#2377 +#--echo # Conformance Item T25 +#SELECT MBRContains(boundary, PointOnSurface(boundary)) +#FROM named_places +#WHERE name = 'Goose Island'; + +--echo # Conformance Item T26 +# TODO: ST_Area() alias +SELECT ST_Area(boundary) +FROM named_places +WHERE name = 'Goose Island'; + +--echo # Conformance Item T27 +# TODO: ST_ExteriorRing() alias +SELECT ST_AsText(ST_ExteriorRing(shore)) +FROM lakes +WHERE name = 'Blue Lake'; + +--echo # Conformance Item T28 +# TODO: ST_NumInteriorRings() alias +SELECT ST_NumInteriorRings(shore) +FROM lakes +WHERE name = 'Blue Lake'; + +--echo # Conformance Item T29 +# TODO: ST_InteriorRingN() alias +SELECT ST_AsText(ST_InteriorRingN(shore, 1)) +FROM lakes +WHERE name = 'Blue Lake'; + +--echo # Conformance Item T30 +# TODO: ST_NumGeometries() alias +SELECT ST_NumGeometries(centerlines) +FROM divided_routes +WHERE name = 'Route 75'; + +--echo # Conformance Item T31 +# TODO: ST_GeometryN() alias +SELECT ST_AsText(ST_GeometryN(centerlines, 2)) +FROM divided_routes +WHERE name = 'Route 75'; + +--echo # Conformance Item T32 +# TODO: ST_IsClosed() alias +SELECT ST_IsClosed(centerlines) +FROM divided_routes +WHERE name = 'Route 75'; + +--echo # Conformance Item T33 +# TODO: ST_Length() alias +SELECT ST_Length(centerlines) +FROM divided_routes +WHERE name = 'Route 75'; + +--echo # Conformance Item T34 +# TODO: ST_Centroid() alias +SELECT ST_AsText(ST_Centroid(shores)) +FROM ponds +WHERE fid = 120; + +# TODO: WL#2377 +#--echo # Conformance Item T35 +#SELECT MBRContains(shores, PointOnSurface(shores)) +#FROM ponds +#WHERE fid = 120; + +--echo # Conformance Item T36 +# TODO: ST_Area() alias +SELECT ST_Area(shores) +FROM ponds +WHERE fid = 120; + +--echo # Conformance Item T37 +# TODO: ST_PolyFromText() alias +# --error ER_GIS_DIFFERENT_SRIDS +SELECT ST_Equals(boundary, +ST_PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',1)) +FROM named_places +WHERE name = 'Goose Island'; + +--echo # Conformance Item T37 +--echo # Geometry arguments' SRIDs must be identical. +SELECT ST_Equals(boundary, +ST_PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',101)) +FROM named_places +WHERE name = 'Goose Island'; + + +--echo # Conformance Item T38 +SELECT ST_Disjoint(centerlines, boundary) +FROM divided_routes, named_places +WHERE divided_routes.name = 'Route 75' +AND named_places.name = 'Ashton'; + +--echo # Conformance Item T39 +SELECT ST_Touches(centerline, shore) +FROM streams, lakes +WHERE streams.name = 'Cam Stream' +AND lakes.name = 'Blue Lake'; + +# FIXME: wrong result: get 0, expected 1 +#--echo # Conformance Item T40 +#SELECT ST_Within(boundary, footprint) +#FROM named_places, buildings +#WHERE named_places.name = 'Ashton' +#AND buildings.address = '215 Main Street'; + +# FIXME: wrong result: get 0, expected 1 +#--echo # Conformance Item T41 +#SELECT ST_Overlaps(forests.boundary, named_places.boundary) +#FROM forests, named_places +#WHERE forests.name = 'Green Forest' +#AND named_places.name = 'Ashton'; + +--echo # Conformance Item T42 +# FIXME: TODO: ST_Crosses() alias +SELECT ST_Crosses(road_segments.centerline, divided_routes.centerlines) +FROM road_segments, divided_routes +WHERE road_segments.fid = 102 +AND divided_routes.name = 'Route 75'; + +--echo # Conformance Item T43 +SELECT ST_Intersects(road_segments.centerline, divided_routes.centerlines) +FROM road_segments, divided_routes +WHERE road_segments.fid = 102 +AND divided_routes.name = 'Route 75'; + +--echo # Conformance Item T44 +SELECT ST_Contains(forests.boundary, named_places.boundary) +FROM forests, named_places +WHERE forests.name = 'Green Forest' +AND named_places.name = 'Ashton'; + +# TODO: WL#2377 +#--echo # Conformance Item T45 +#SELECT Relate(forests.boundary, named_places.boundary, 'TTTTTTTTT') +#FROM forests, named_places +#WHERE forests.name = 'Green Forest' +#AND named_places.name = 'Ashton'; + +--echo # Conformance Item T46 +SELECT ST_Distance(position, boundary) +FROM bridges, named_places +WHERE bridges.name = 'Cam Bridge' +AND named_places.name = 'Ashton'; + +# FIXME: wrong result: NULL, expected 12 +#--echo # Conformance Item T47 +#SELECT ST_AsText(ST_Intersection(centerline, shore)) +#FROM streams, lakes +#WHERE streams.name = 'Cam Stream' +#AND lakes.name = 'Blue Lake'; + +--echo # Conformance Item T48 +SELECT ST_AsText(ST_Difference(named_places.boundary, forests.boundary)) +FROM named_places, forests +WHERE named_places.name = 'Ashton' +AND forests.name = 'Green Forest'; + +#--echo # Conformance Item T49 +SELECT ST_AsText(ST_Union(shore, boundary)) +FROM lakes, named_places +WHERE lakes.name = 'Blue Lake' +AND named_places.name = 'Goose Island'; + +--echo # Conformance Item T50 +SELECT ST_AsText(ST_SymDifference(shore, boundary)) +FROM lakes, named_places +WHERE lakes.name = 'Blue Lake' +AND named_places.name = 'Ashton'; + +--echo # Conformance Item T51 +SELECT count(*) +FROM buildings, bridges +WHERE ST_Contains(ST_Buffer(bridges.position, 15.0), buildings.footprint) = 1; + +# TODO: WL#2377 +#--echo # Conformance Item T52 +#SELECT ST_AsText(ConvexHull(shore)) +#FROM lakes +#WHERE lakes.name = 'Blue Lake'; + +DROP DATABASE gis_ogs; + +--echo # +--echo # Bug#13362660 ASSERTION `FIELD_POS < FIELD_COUNT' FAILED. IN PROTOCOL_TEXT::STORE +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT ST_Union('', ''), md5(1); |