summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/innodb_gis/r/1.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/innodb_gis/r/1.result')
-rw-r--r--mysql-test/suite/innodb_gis/r/1.result1496
1 files changed, 1496 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb_gis/r/1.result b/mysql-test/suite/innodb_gis/r/1.result
new file mode 100644
index 00000000..35711a63
--- /dev/null
+++ b/mysql-test/suite/innodb_gis/r/1.result
@@ -0,0 +1,1496 @@
+SET default_storage_engine=InnoDB;
+SET innodb_strict_mode=OFF;
+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;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g point YES NULL
+SHOW FIELDS FROM gis_line;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g linestring YES NULL
+SHOW FIELDS FROM gis_polygon;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g polygon YES NULL
+SHOW FIELDS FROM gis_multi_point;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g multipoint YES NULL
+SHOW FIELDS FROM gis_multi_line;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g multilinestring YES NULL
+SHOW FIELDS FROM gis_multi_polygon;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g multipolygon YES NULL
+SHOW FIELDS FROM gis_geometrycollection;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g geometrycollection YES NULL
+SHOW FIELDS FROM gis_geometry;
+Field Type Null Key Default Extra
+fid int(11) NO PRI NULL
+g geometry YES NULL
+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;
+fid ST_AsText(g)
+101 POINT(10 10)
+102 POINT(20 10)
+103 POINT(20 20)
+104 POINT(10 20)
+SELECT fid, ST_AsText(g) FROM gis_line;
+fid ST_AsText(g)
+105 LINESTRING(0 0,0 10,10 0)
+106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+107 LINESTRING(10 10,40 10)
+SELECT fid, ST_AsText(g) FROM gis_polygon;
+fid ST_AsText(g)
+108 POLYGON((10 10,20 10,20 20,10 20,10 10))
+109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
+110 POLYGON((0 0,30 0,30 30,0 0))
+SELECT fid, ST_AsText(g) FROM gis_multi_point;
+fid ST_AsText(g)
+111 MULTIPOINT(0 0,10 10,10 20,20 20)
+112 MULTIPOINT(1 1,11 11,11 21,21 21)
+113 MULTIPOINT(3 6,4 10)
+SELECT fid, ST_AsText(g) FROM gis_multi_line;
+fid ST_AsText(g)
+114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
+115 MULTILINESTRING((10 48,10 21,10 0))
+116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
+SELECT fid, ST_AsText(g) FROM gis_multi_polygon;
+fid ST_AsText(g)
+117 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 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 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
+SELECT fid, ST_AsText(g) FROM gis_geometrycollection;
+fid ST_AsText(g)
+120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
+SELECT fid, ST_AsText(g) FROM gis_geometry;
+fid ST_AsText(g)
+101 POINT(10 10)
+102 POINT(20 10)
+103 POINT(20 20)
+104 POINT(10 20)
+105 LINESTRING(0 0,0 10,10 0)
+106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+107 LINESTRING(10 10,40 10)
+108 POLYGON((10 10,20 10,20 20,10 20,10 10))
+109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
+110 POLYGON((0 0,30 0,30 30,0 0))
+111 MULTIPOINT(0 0,10 10,10 20,20 20)
+112 MULTIPOINT(1 1,11 11,11 21,21 21)
+113 MULTIPOINT(3 6,4 10)
+114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
+115 MULTILINESTRING((10 48,10 21,10 0))
+116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
+117 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 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 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
+120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
+SELECT fid, ST_Dimension(g) FROM gis_geometry;
+fid ST_Dimension(g)
+101 0
+102 0
+103 0
+104 0
+105 1
+106 1
+107 1
+108 2
+109 2
+110 2
+111 0
+112 0
+113 0
+114 1
+115 1
+116 1
+117 2
+118 2
+119 2
+120 1
+121 1
+SELECT fid, ST_GeometryType(g) FROM gis_geometry;
+fid ST_GeometryType(g)
+101 POINT
+102 POINT
+103 POINT
+104 POINT
+105 LINESTRING
+106 LINESTRING
+107 LINESTRING
+108 POLYGON
+109 POLYGON
+110 POLYGON
+111 MULTIPOINT
+112 MULTIPOINT
+113 MULTIPOINT
+114 MULTILINESTRING
+115 MULTILINESTRING
+116 MULTILINESTRING
+117 MULTIPOLYGON
+118 MULTIPOLYGON
+119 MULTIPOLYGON
+120 GEOMETRYCOLLECTION
+121 GEOMETRYCOLLECTION
+SELECT fid, ST_IsEmpty(g) FROM gis_geometry;
+fid ST_IsEmpty(g)
+101 0
+102 0
+103 0
+104 0
+105 0
+106 0
+107 0
+108 0
+109 0
+110 0
+111 0
+112 0
+113 0
+114 0
+115 0
+116 0
+117 0
+118 0
+119 0
+120 0
+121 0
+SELECT fid, ST_AsText(ST_Envelope(g)) FROM gis_geometry;
+fid ST_AsText(ST_Envelope(g))
+101 POLYGON((10 10,10 10,10 10,10 10,10 10))
+102 POLYGON((20 10,20 10,20 10,20 10,20 10))
+103 POLYGON((20 20,20 20,20 20,20 20,20 20))
+104 POLYGON((10 20,10 20,10 20,10 20,10 20))
+105 POLYGON((0 0,10 0,10 10,0 10,0 0))
+106 POLYGON((10 10,20 10,20 20,10 20,10 10))
+107 POLYGON((10 10,40 10,40 10,10 10,10 10))
+108 POLYGON((10 10,20 10,20 20,10 20,10 10))
+109 POLYGON((0 0,50 0,50 50,0 50,0 0))
+110 POLYGON((0 0,30 0,30 30,0 30,0 0))
+111 POLYGON((0 0,20 0,20 20,0 20,0 0))
+112 POLYGON((1 1,21 1,21 21,1 21,1 1))
+113 POLYGON((3 6,4 6,4 10,3 10,3 6))
+114 POLYGON((10 0,16 0,16 48,10 48,10 0))
+115 POLYGON((10 0,10 0,10 48,10 48,10 0))
+116 POLYGON((1 2,21 2,21 8,1 8,1 2))
+117 POLYGON((28 0,84 0,84 42,28 42,28 0))
+118 POLYGON((28 0,84 0,84 42,28 42,28 0))
+119 POLYGON((0 0,3 0,3 3,0 3,0 0))
+120 POLYGON((0 0,10 0,10 10,0 10,0 0))
+121 POLYGON((3 6,44 6,44 9,3 9,3 6))
+explain extended select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_geometry ALL NULL NULL NULL NULL # #
+Warnings:
+Note 1003 select st_dimension(`test`.`gis_geometry`.`g`) AS `ST_Dimension(g)`,st_geometrytype(`test`.`gis_geometry`.`g`) AS `ST_GeometryType(g)`,st_isempty(`test`.`gis_geometry`.`g`) AS `ST_IsEmpty(g)`,st_astext(st_envelope(`test`.`gis_geometry`.`g`)) AS `ST_AsText(ST_Envelope(g))` from `test`.`gis_geometry`
+SELECT fid, ST_X(g) FROM gis_point;
+fid ST_X(g)
+101 10
+102 20
+103 20
+104 10
+SELECT fid, ST_Y(g) FROM gis_point;
+fid ST_Y(g)
+101 10
+102 10
+103 20
+104 20
+explain extended select ST_X(g),ST_Y(g) FROM gis_point;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_point ALL NULL NULL NULL NULL # #
+Warnings:
+Note 1003 select st_x(`test`.`gis_point`.`g`) AS `ST_X(g)`,st_y(`test`.`gis_point`.`g`) AS `ST_Y(g)` from `test`.`gis_point`
+SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line;
+fid ST_AsText(ST_StartPoint(g))
+105 POINT(0 0)
+106 POINT(10 10)
+107 POINT(10 10)
+SELECT fid, ST_AsText(ST_EndPoint(g)) FROM gis_line;
+fid ST_AsText(ST_EndPoint(g))
+105 POINT(10 0)
+106 POINT(10 10)
+107 POINT(40 10)
+SELECT fid, ST_Length(g) FROM gis_line;
+fid ST_Length(g)
+105 24.14213562373095
+106 40
+107 30
+SELECT fid, ST_NumPoints(g) FROM gis_line;
+fid ST_NumPoints(g)
+105 3
+106 5
+107 2
+SELECT fid, ST_AsText(ST_PointN(g, 2)) FROM gis_line;
+fid ST_AsText(ST_PointN(g, 2))
+105 POINT(0 10)
+106 POINT(20 10)
+107 POINT(40 10)
+SELECT fid, ST_IsClosed(g) FROM gis_line;
+fid ST_IsClosed(g)
+105 0
+106 1
+107 0
+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;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_line ALL NULL NULL NULL NULL # #
+Warnings:
+Note 1003 select st_astext(st_startpoint(`test`.`gis_line`.`g`)) AS `ST_AsText(ST_StartPoint(g))`,st_astext(st_endpoint(`test`.`gis_line`.`g`)) AS `ST_AsText(ST_EndPoint(g))`,st_length(`test`.`gis_line`.`g`) AS `ST_Length(g)`,st_numpoints(`test`.`gis_line`.`g`) AS `ST_NumPoints(g)`,st_astext(st_pointn(`test`.`gis_line`.`g`,2)) AS `ST_AsText(ST_PointN(g, 2))`,st_isclosed(`test`.`gis_line`.`g`) AS `ST_IsClosed(g)` from `test`.`gis_line`
+SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon;
+fid ST_AsText(ST_Centroid(g))
+108 POINT(15 15)
+109 POINT(25.416666666666668 25.416666666666668)
+110 POINT(20 10)
+SELECT fid, ST_Area(g) FROM gis_polygon;
+fid ST_Area(g)
+108 100
+109 2400
+110 450
+SELECT fid, ST_AsText(ST_ExteriorRing(g)) FROM gis_polygon;
+fid ST_AsText(ST_ExteriorRing(g))
+108 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+109 LINESTRING(0 0,50 0,50 50,0 50,0 0)
+110 LINESTRING(0 0,30 0,30 30,0 0)
+SELECT fid, ST_NumInteriorRings(g) FROM gis_polygon;
+fid ST_NumInteriorRings(g)
+108 0
+109 1
+110 0
+SELECT fid, ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
+fid ST_AsText(ST_InteriorRingN(g, 1))
+108 NULL
+109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+110 NULL
+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;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_polygon ALL NULL NULL NULL NULL # #
+Warnings:
+Note 1003 select st_astext(st_centroid(`test`.`gis_polygon`.`g`)) AS `ST_AsText(ST_Centroid(g))`,st_area(`test`.`gis_polygon`.`g`) AS `ST_Area(g)`,st_astext(st_exteriorring(`test`.`gis_polygon`.`g`)) AS `ST_AsText(ST_ExteriorRing(g))`,st_numinteriorrings(`test`.`gis_polygon`.`g`) AS `ST_NumInteriorRings(g)`,st_astext(st_interiorringn(`test`.`gis_polygon`.`g`,1)) AS `ST_AsText(ST_InteriorRingN(g, 1))` from `test`.`gis_polygon`
+SELECT fid, ST_IsClosed(g) FROM gis_multi_line;
+fid ST_IsClosed(g)
+114 0
+115 0
+116 0
+SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_multi_polygon;
+fid ST_AsText(ST_Centroid(g))
+117 POINT(57.98031067576927 17.854754130800433)
+118 POINT(57.98031067576927 17.854754130800433)
+119 POINT(2 2)
+SELECT fid, ST_Area(g) FROM gis_multi_polygon;
+fid ST_Area(g)
+117 1684.5
+118 1684.5
+119 4.5
+SELECT fid, ST_NumGeometries(g) from gis_multi_point;
+fid ST_NumGeometries(g)
+111 4
+112 4
+113 2
+SELECT fid, ST_NumGeometries(g) from gis_multi_line;
+fid ST_NumGeometries(g)
+114 2
+115 1
+116 2
+SELECT fid, ST_NumGeometries(g) from gis_multi_polygon;
+fid ST_NumGeometries(g)
+117 2
+118 2
+119 1
+SELECT fid, ST_NumGeometries(g) from gis_geometrycollection;
+fid ST_NumGeometries(g)
+120 2
+121 2
+explain extended SELECT fid, ST_NumGeometries(g) from gis_multi_point;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL # #
+Warnings:
+Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,st_numgeometries(`test`.`gis_multi_point`.`g`) AS `ST_NumGeometries(g)` from `test`.`gis_multi_point`
+SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
+fid ST_AsText(ST_GeometryN(g, 2))
+111 POINT(10 10)
+112 POINT(11 11)
+113 POINT(4 10)
+SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_line;
+fid ST_AsText(ST_GeometryN(g, 2))
+114 LINESTRING(16 0,16 23,16 48)
+115 NULL
+116 LINESTRING(2 5,5 8,21 7)
+SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_polygon;
+fid ST_AsText(ST_GeometryN(g, 2))
+117 POLYGON((59 18,67 18,67 13,59 13,59 18))
+118 POLYGON((59 18,67 18,67 13,59 13,59 18))
+119 NULL
+SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_geometrycollection;
+fid ST_AsText(ST_GeometryN(g, 2))
+120 LINESTRING(0 0,10 10)
+121 LINESTRING(3 6,7 9)
+SELECT fid, ST_AsText(ST_GeometryN(g, 1)) from gis_geometrycollection;
+fid ST_AsText(ST_GeometryN(g, 1))
+120 POINT(0 0)
+121 POINT(44 6)
+explain extended SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL # #
+Warnings:
+Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,st_astext(st_geometryn(`test`.`gis_multi_point`.`g`,2)) AS `ST_AsText(ST_GeometryN(g, 2))` from `test`.`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;
+first second w c o e d t i r
+120 120 1 1 0 1 0 0 1 0
+120 121 0 0 1 0 0 0 1 0
+121 120 0 0 1 0 0 0 1 0
+121 121 1 1 0 1 0 0 1 0
+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;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE g1 ALL NULL NULL NULL NULL # # Using temporary; Using filesort
+1 SIMPLE g2 ALL NULL NULL NULL NULL # # Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,mbrwithin(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,mbrcontains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,mbroverlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,mbrequals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,mbrdisjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,st_touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,mbrintersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,st_crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`
+DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
+CREATE TABLE t1 (
+gp point,
+ln linestring,
+pg polygon,
+mp multipoint,
+mln multilinestring,
+mpg multipolygon,
+gc geometrycollection,
+gm geometry
+);
+SHOW FIELDS FROM t1;
+Field Type Null Key Default Extra
+gp point YES NULL
+ln linestring YES NULL
+pg polygon YES NULL
+mp multipoint YES NULL
+mln multilinestring YES NULL
+mpg multipolygon YES NULL
+gc geometrycollection YES NULL
+gm geometry YES NULL
+ALTER TABLE t1 ADD fid INT NOT NULL;
+SHOW FIELDS FROM t1;
+Field Type Null Key Default Extra
+gp point YES NULL
+ln linestring YES NULL
+pg polygon YES NULL
+mp multipoint YES NULL
+mln multilinestring YES NULL
+mpg multipolygon YES NULL
+gc geometrycollection YES NULL
+gm geometry YES NULL
+fid int(11) NO NULL
+DROP TABLE t1;
+SELECT ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_GeometryFromText('POINT(1 4)'))));
+ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_GeometryFromText('POINT(1 4)'))))
+POINT(1 4)
+explain extended SELECT ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_GeometryFromText('POINT(1 4)'))));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select st_astext(st_geometryfromwkb(st_aswkb(st_geometryfromtext('POINT(1 4)')))) AS `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)'))));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select st_astext(st_geometryfromwkb(st_aswkb(st_geometryfromtext('POINT(1 4)')))) AS `ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_PointFromText('POINT(1 4)'))))`
+SELECT ST_SRID(ST_GeomFromText('LineString(1 1,2 2)',101));
+ST_SRID(ST_GeomFromText('LineString(1 1,2 2)',101))
+101
+explain extended SELECT ST_SRID(ST_GeomFromText('LineString(1 1,2 2)',101));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select srid(st_geometryfromtext('LineString(1 1,2 2)',101)) AS `ST_SRID(ST_GeomFromText('LineString(1 1,2 2)',101))`
+explain extended select ST_issimple(MultiPoint(Point(3, 6), Point(4, 10))), ST_issimple(Point(3, 6));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select st_issimple(multipoint(point(3,6),point(4,10))) AS `ST_issimple(MultiPoint(Point(3, 6), Point(4, 10)))`,st_issimple(point(3,6)) AS `ST_issimple(Point(3, 6))`
+create table t1 (a geometry not null);
+insert into t1 values (ST_GeomFromText('Point(1 2)'));
+insert into t1 values ('Garbage');
+ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
+insert IGNORE into t1 values ('Garbage');
+ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
+alter table t1 add spatial index(a);
+drop table t1;
+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);
+ST_AsText(a)
+POINT(1 1)
+POINT(3 3)
+POINT(4 4)
+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);
+ST_AsText(a)
+POINT(1 1)
+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;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `ST_GeomFromWKB(POINT(1,3))` geometry DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+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)))'));
+select object_id, ST_geometrytype(geo), ST_ISSIMPLE(GEO), ST_ASTEXT(ST_centroid(geo)) from
+t1 where object_id=85998;
+object_id ST_geometrytype(geo) ST_ISSIMPLE(GEO) ST_ASTEXT(ST_centroid(geo))
+85998 MULTIPOLYGON 1 POINT(115.2970604672862 -36.23335610879993)
+select object_id, ST_geometrytype(geo), ST_ISSIMPLE(GEO), ST_ASTEXT(ST_centroid(geo)) from
+t1 where object_id=85984;
+object_id ST_geometrytype(geo) ST_ISSIMPLE(GEO) ST_ASTEXT(ST_centroid(geo))
+85984 MULTIPOLYGON 1 POINT(-114.86854472054372 36.34725218253213)
+drop table t1;
+create table t1 (fl geometry not null);
+insert into t1 values (1);
+ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t1`.`fl`
+insert into t1 values (1.11);
+ERROR HY000: Cannot cast 'decimal' as 'geometry' in assignment of `test`.`t1`.`fl`
+insert into t1 values ("qwerty");
+ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
+insert into t1 values (ST_pointfromtext('point(1,1)'));
+ERROR 23000: Column 'fl' cannot be null
+drop table t1;
+select (ST_asWKT(ST_geomfromwkb((0x000000000140240000000000004024000000000000))));
+(ST_asWKT(ST_geomfromwkb((0x000000000140240000000000004024000000000000))))
+POINT(10 10)
+select (ST_asWKT(ST_geomfromwkb((0x010100000000000000000024400000000000002440))));
+(ST_asWKT(ST_geomfromwkb((0x010100000000000000000024400000000000002440))))
+POINT(10 10)
+create table t1 (g GEOMETRY);
+select * from t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def test t1 t1 g g 255 4294967295 0 Y 144 0 63
+g
+select ST_asbinary(g) from t1;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def ST_asbinary(g) 251 4294967295 0 Y 128 0 63
+ST_asbinary(g)
+drop table t1;
+create table t1 (a TEXT, b GEOMETRY NOT NULL, INDEX(b(5)));
+alter table t1 disable keys;
+Warnings:
+Note 1031 Storage engine InnoDB of the table `test`.`t1` doesn't have this option
+load data infile '../../std_data/bad_gis_data.dat' into table t1;
+ERROR 22004: Column set to default value; NULL supplied to NOT NULL column 'b' at row 1
+alter table t1 enable keys;
+Warnings:
+Note 1031 Storage engine InnoDB of the table `test`.`t1` doesn't have this option
+drop table t1;
+create table t1 (a int, b blob);
+insert into t1 values (1, ''), (2, NULL), (3, '1');
+select * from t1;
+a b
+1
+2 NULL
+3 1
+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;
+ERROR 22007: Illegal non geometric '`test`.`t1`.`b`' value found during parsing
+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;
+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
+1 1 1 1 1 1 1 1
+1 1 1 1 1 1 1 1
+1 1 1 1 1 1 1 1
+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;
+ERROR 22007: Illegal non geometric '`test`.`t1`.`b`' value found during parsing
+drop table t1;
+CREATE TABLE t1(a POINT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1;
+a
+NULL
+DROP TABLE t1;
+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;
+a
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (
+geomdata polygon NOT NULL,
+KEY index_geom (geomdata(10))
+) ENGINE=InnoDB DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED;
+Warnings:
+Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
+CREATE TABLE t2 (
+geomdata polygon NOT NULL,
+KEY index_geom (geomdata(10))
+) ENGINE=InnoDB DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED;
+Warnings:
+Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
+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;
+Field Type Null Key Default Extra
+geomdatawkb longblob YES NULL
+drop table t1;
+drop table t2;
+drop table t3;
+create table t1(col1 geometry default null,col15 geometrycollection not
+null, index(col15(5)),index(col1(15)))engine=InnoDB;
+insert into t1 set col15 = ST_GeomFromText('POINT(6 5)');
+insert into t1 set col15 = ST_GeomFromText('POINT(6 5)');
+check table t1 extended;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+drop table t1;
+End of 4.1 tests
+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;
+insert into t1 values (null,null);
+ERROR 23000: Column 's1' cannot be null
+drop table t1;
+drop procedure if exists fn3;
+create function fn3 () returns point deterministic return ST_GeomFromText("point(1 1)");
+show create function fn3;
+Function sql_mode Create Function character_set_client collation_connection Database Collation
+fn3 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS point
+ DETERMINISTIC
+return ST_GeomFromText("point(1 1)") latin1 latin1_swedish_ci latin1_swedish_ci
+select ST_astext(fn3());
+ST_astext(fn3())
+POINT(1 1)
+drop function fn3;
+create table t1(pt POINT);
+alter table t1 add primary key pti(pt);
+Warnings:
+Warning 1280 Name 'pti' ignored for PRIMARY key.
+drop table t1;
+create table t1(pt GEOMETRY);
+alter table t1 add primary key pti(pt);
+ERROR 42000: BLOB/TEXT column 'pt' used in key specification without a key length
+alter table t1 add primary key pti(pt(20));
+Warnings:
+Warning 1280 Name 'pti' ignored for PRIMARY key.
+drop table t1;
+create table t1 select ST_GeomFromText('point(1 1)');
+desc t1;
+Field Type Null Key Default Extra
+ST_GeomFromText('point(1 1)') geometry YES NULL
+drop table t1;
+create table t1 (g geometry not null);
+insert into t1 values(default);
+ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
+drop table t1;
+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;
+Field Type Null Key Default Extra
+ST_GeomFromwkb(ST_ASBINARY(a)) geometry YES NULL
+DESCRIBE v2;
+Field Type Null Key Default Extra
+a geometry YES NULL
+DROP VIEW v1,v2;
+DROP TABLE t1;
+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;
+mbrcontains
+center,small
+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;
+mbrdisjoint
+down3,left3,right3,up3
+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;
+mbrequals
+center
+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;
+mbrintersect
+big,center,down,down2,left,left2,right,right2,small,up,up2
+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;
+mbroverlaps
+down,left,right,up
+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;
+mbrtouches
+down2,left2,right2,up2
+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;
+mbrwithin
+big,center
+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;
+MBRcontains
+center,small
+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;
+MBRdisjoint
+down3,left3,right3,up3
+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;
+MBRequals
+center
+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;
+intersects
+big,center,down,down2,left,left2,right,right2,small,up,up2
+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;
+MBRoverlaps
+down,left,right,up
+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;
+ST_touches
+down2,left2,right2,up2
+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;
+MBRwithin
+big,center
+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;
+MBRoverlaps
+SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @horiz1) GROUP BY a1.name;
+MBRoverlaps
+SELECT MBROverlaps(@horiz1, @vert1) FROM DUAL;
+MBROverlaps(@horiz1, @vert1)
+0
+SELECT MBROverlaps(@horiz1, @horiz2) FROM DUAL;
+MBROverlaps(@horiz1, @horiz2)
+1
+SELECT MBROverlaps(@horiz1, @horiz3) FROM DUAL;
+MBROverlaps(@horiz1, @horiz3)
+0
+SELECT MBROverlaps(@horiz1, @point1) FROM DUAL;
+MBROverlaps(@horiz1, @point1)
+0
+SELECT MBROverlaps(@horiz1, @point2) FROM DUAL;
+MBROverlaps(@horiz1, @point2)
+0
+DROP TABLE t1;
+create table t1(f1 geometry, f2 polygon, f3 linestring);
+select f1 from t1 union select f1 from t1;
+f1
+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;
+ST_AsText(f2) ST_AsText(f3)
+POLYGON((10 10,20 10,20 20,10 20,10 10)) LINESTRING(0 0,1 1,2 2)
+select ST_AsText(a) from (select f2 as a from t1 union select f3 from t1) t;
+ST_AsText(a)
+POLYGON((10 10,20 10,20 20,10 20,10 10))
+LINESTRING(0 0,1 1,2 2)
+create table t2 as select f2 as a from t1 union select f3 from t1;
+desc t2;
+Field Type Null Key Default Extra
+a geometry YES NULL
+select ST_AsText(a) from t2;
+ST_AsText(a)
+POLYGON((10 10,20 10,20 20,10 20,10 10))
+LINESTRING(0 0,1 1,2 2)
+drop table t1, t2;
+SELECT 1;
+1
+1
+CREATE TABLE t1 (p POINT);
+CREATE TABLE t2 (p POINT, INDEX(p));
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref p p 28 const 1 Using where
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(3 4)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(3 4)'));
+EXPLAIN
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref p p 28 const # Using where
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+EXPLAIN
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+DROP TABLE t1, t2;
+End of 5.0 tests
+#
+# Test for bug #58650 "Failing assertion: primary_key_no == -1 ||
+# primary_key_no == 0".
+#
+drop table if exists t1;
+# The minimal test case.
+create table t1 (a int not null, b linestring not null, unique key b (b(12)), unique key a (a));
+drop table t1;
+# The original test case.
+create table t1 (a int not null, b linestring not null, unique key b (b(12)));
+create unique index a on t1(a);
+drop table t1;
+create table `t1` (`col002` point)engine=InnoDB;
+insert into t1 values (),(),();
+select min(`col002`) from t1 union select `col002` from t1;
+min(`col002`)
+NULL
+drop table t1;
+#
+# Bug #47780: crash when comparing GIS items from subquery
+#
+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)))'));
+# must not crash
+SELECT 1 FROM t1 WHERE a <> (SELECT ST_GEOMETRYCOLLECTIONFROMWKB(b) FROM t1);
+ERROR HY000: Illegal parameter data types int and geometry for operation '<>'
+DROP TABLE t1;
+#
+# Bug #49250 : spatial btree index corruption and crash
+# Part one : spatial syntax check
+#
+CREATE TABLE t1(col1 MULTIPOLYGON NOT NULL,
+SPATIAL INDEX USING BTREE (col1));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USING BTREE (col1))' at line 2
+CREATE TABLE t2(col1 MULTIPOLYGON NOT NULL);
+CREATE SPATIAL INDEX USING BTREE ON t2(col);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USING BTREE ON t2(col)' at line 1
+ALTER TABLE t2 ADD SPATIAL INDEX USING BTREE (col1);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USING BTREE (col1)' at line 1
+DROP TABLE t2;
+End of 5.0 tests
+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;
+Field Type Null Key Default Extra
+f1 tinyint(1) YES NULL
+f2 char(1) YES NULL
+f3 varchar(1) YES NULL
+f4 geometry YES NULL
+f5 datetime YES NULL
+drop view v1;
+drop table t1;
+SELECT MultiPoint(12345,'');
+ERROR HY000: Illegal parameter data type int for operation 'multipoint'
+SELECT 1 FROM (SELECT GREATEST(1,GEOMETRYCOLLECTION('00000','00000')) b FROM DUAL) AS d WHERE (LINESTRING(d.b));
+ERROR HY000: Illegal parameter data type varchar for operation 'geometrycollection'
+#
+# BUG#51875: crash when loading data into geometry function ST_polyfromwkb
+#
+SET @a=0x00000000030000000100000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440;
+SET @a=ST_POLYFROMWKB(@a);
+SET @a=0x00000000030000000000000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440;
+SET @a=ST_POLYFROMWKB(@a);
+create table t1(a polygon NOT NULL)engine=InnoDB;
+insert into t1 values (ST_geomfromtext("point(0 1)"));
+ERROR 22007: Incorrect POLYGON value: 'POINT(0 1)' for column `test`.`t1`.`a` at row 1
+insert into t1 values (ST_geomfromtext("point(1 0)"));
+ERROR 22007: Incorrect POLYGON value: 'POINT(1 0)' for column `test`.`t1`.`a` at row 1
+select * from (select polygon(t1.a) as p from t1 order by t1.a) d;
+p
+drop table t1;
+#
+# Test for bug #59888 "debug assertion when attempt to create spatial index
+# on char > 31 bytes".
+#
+create table t1(a char(32) not null) engine=InnoDB;
+create index i on t1 (a(5));
+drop table t1;
+End of 5.1 tests
+CREATE TABLE t0 (a BINARY(32) NOT NULL);
+CREATE UNIQUE INDEX i on t0 (a(10));
+INSERT INTO t0 VALUES (1);
+CREATE TABLE t1(
+col0 BINARY NOT NULL,
+col2 TIMESTAMP,
+INDEX i1 (col0)
+) ENGINE=InnoDB;
+CREATE TABLE t2 (
+col0 BINARY NOT NULL,
+col2 TIMESTAMP
+) ENGINE=InnoDB;
+CREATE INDEX idx0 ON t1(col0);
+Warnings:
+Note 1831 Duplicate index `idx0`. This is deprecated and will be disallowed in a future release
+CREATE TABLE t3 (
+col0 INTEGER NOT NULL,
+col1 POINT,
+col2 POINT
+);
+CREATE SPATIAL INDEX idx0 ON t2 (col1, col2);
+ERROR HY000: Incorrect arguments to SPATIAL INDEX
+CREATE TABLE t4 (
+col0 INTEGER NOT NULL,
+col1 POINT,
+col2 LINESTRING,
+INDEX i1 (col1(5), col2(5))
+);
+DROP TABLE IF EXISTS t0, t1, t2, t3,t4;
+#
+# BUG#12414917 - ST_ISCLOSED() CRASHES ON 64-BIT BUILDS
+#
+SELECT ST_ISCLOSED(CONVERT(CONCAT(' ', 0x2), BINARY(20)));
+ST_ISCLOSED(CONVERT(CONCAT(' ', 0x2), BINARY(20)))
+-1
+#
+# BUG#12537203 - CRASH WHEN SUBSELECTING GLOBAL VARIABLES IN
+# GEOMETRY FUNCTION ARGUMENTS
+#
+SELECT GEOMETRYCOLLECTION((SELECT @@OLD));
+ERROR HY000: Illegal parameter data type bigint for operation 'geometrycollection'
+End of 5.1 tests
+#
+# Bug#11908153: CRASH AND/OR VALGRIND ERRORS IN FIELD_BLOB::GET_KEY_IMAGE
+#
+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;
+SELECT 1 FROM g1
+FORCE INDEX(i) WHERE a = date_sub(now(), interval 2808.4 year_month)
+;
+ERROR HY000: Illegal parameter data types geometry and datetime for operation '='
+DROP TABLE g1;
+#
+# Bug#13013970 MORE CRASHES IN FIELD_BLOB::GET_KEY_IMAGE
+#
+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) ;
+1
+Warnings:
+Warning 1292 Truncated incorrect DECIMAL value: 'a'
+Warning 1292 Truncated incorrect DECIMAL value: 'a'
+DROP TABLE g1;
+End of 5.5 tests
+DROP DATABASE IF EXISTS gis_ogs;
+CREATE DATABASE gis_ogs;
+USE gis_ogs;
+#
+# C.3.3.1 Geometry types and functions schema construction
+#
+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);
+#
+# C.3.3.2 Geometry types and functions schema data loading
+#
+# 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));
+# 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));
+# 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));
+# 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));
+# Bridges
+INSERT INTO bridges VALUES(110, 'Cam Bridge', ST_PointFromText(
+'POINT( 44 31 )', 101));
+# 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));
+# 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));
+# 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));
+# 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));
+# Map Neatlines
+INSERT INTO map_neatlines VALUES(115,
+ST_PolyFromText(
+'POLYGON( ( 0 0, 0 48, 84 48, 84 0, 0 0 ) )', 101));
+#
+# C.3.3.3 Geometry types and functions schema test queries
+
+# Conformance Item T6
+SELECT ST_Dimension(shore)
+FROM lakes
+WHERE name = 'Blue Lake';
+ST_Dimension(shore)
+2
+# Conformance Item T7
+SELECT ST_GeometryType(centerlines)
+FROM divided_routes
+WHERE name = 'Route 75';
+ST_GeometryType(centerlines)
+MULTILINESTRING
+# Conformance Item T8
+SELECT ST_AsText(boundary)
+FROM named_places
+WHERE name = 'Goose Island';
+ST_AsText(boundary)
+POLYGON((67 13,67 18,59 18,59 13,67 13))
+# Conformance Item T9
+SELECT ST_AsText(ST_PolyFromWKB(ST_AsBinary(boundary),101))
+FROM named_places
+WHERE name = 'Goose Island';
+ST_AsText(ST_PolyFromWKB(ST_AsBinary(boundary),101))
+POLYGON((67 13,67 18,59 18,59 13,67 13))
+# Conformance Item T10
+SELECT ST_SRID(boundary)
+FROM named_places
+WHERE name = 'Goose Island';
+ST_SRID(boundary)
+101
+# Conformance Item T11
+SELECT ST_IsEmpty(centerline)
+FROM road_segments
+WHERE name = 'Route 5'
+AND aliases = 'Main Street';
+ST_IsEmpty(centerline)
+0
+# Conformance Item T14
+SELECT ST_AsText(ST_Envelope(boundary))
+FROM named_places
+WHERE name = 'Goose Island';
+ST_AsText(ST_Envelope(boundary))
+POLYGON((59 13,67 13,67 18,59 18,59 13))
+# Conformance Item T15
+SELECT ST_X(position)
+FROM bridges
+WHERE name = 'Cam Bridge';
+ST_X(position)
+44
+# Conformance Item T16
+SELECT ST_Y(position)
+FROM bridges
+WHERE name = 'Cam Bridge';
+ST_Y(position)
+31
+# Conformance Item T17
+SELECT ST_AsText(ST_StartPoint(centerline))
+FROM road_segments
+WHERE fid = 102;
+ST_AsText(ST_StartPoint(centerline))
+POINT(0 18)
+# Conformance Item T18
+SELECT ST_AsText(ST_EndPoint(centerline))
+FROM road_segments
+WHERE fid = 102;
+ST_AsText(ST_EndPoint(centerline))
+POINT(44 31)
+# Conformance Item T21
+SELECT ST_Length(centerline)
+FROM road_segments
+WHERE fid = 106;
+ST_Length(centerline)
+26
+# Conformance Item T22
+SELECT ST_NumPoints(centerline)
+FROM road_segments
+WHERE fid = 102;
+ST_NumPoints(centerline)
+5
+# Conformance Item T23
+SELECT ST_AsText(ST_PointN(centerline, 1))
+FROM road_segments
+WHERE fid = 102;
+ST_AsText(ST_PointN(centerline, 1))
+POINT(0 18)
+# Conformance Item T24
+SELECT ST_AsText(ST_Centroid(boundary))
+FROM named_places
+WHERE name = 'Goose Island';
+ST_AsText(ST_Centroid(boundary))
+POINT(63 15.5)
+# Conformance Item T26
+SELECT ST_Area(boundary)
+FROM named_places
+WHERE name = 'Goose Island';
+ST_Area(boundary)
+40
+# Conformance Item T27
+SELECT ST_AsText(ST_ExteriorRing(shore))
+FROM lakes
+WHERE name = 'Blue Lake';
+ST_AsText(ST_ExteriorRing(shore))
+LINESTRING(52 18,66 23,73 9,48 6,52 18)
+# Conformance Item T28
+SELECT ST_NumInteriorRings(shore)
+FROM lakes
+WHERE name = 'Blue Lake';
+ST_NumInteriorRings(shore)
+1
+# Conformance Item T29
+SELECT ST_AsText(ST_InteriorRingN(shore, 1))
+FROM lakes
+WHERE name = 'Blue Lake';
+ST_AsText(ST_InteriorRingN(shore, 1))
+LINESTRING(59 18,67 18,67 13,59 13,59 18)
+# Conformance Item T30
+SELECT ST_NumGeometries(centerlines)
+FROM divided_routes
+WHERE name = 'Route 75';
+ST_NumGeometries(centerlines)
+2
+# Conformance Item T31
+SELECT ST_AsText(ST_GeometryN(centerlines, 2))
+FROM divided_routes
+WHERE name = 'Route 75';
+ST_AsText(ST_GeometryN(centerlines, 2))
+LINESTRING(16 0,16 23,16 48)
+# Conformance Item T32
+SELECT ST_IsClosed(centerlines)
+FROM divided_routes
+WHERE name = 'Route 75';
+ST_IsClosed(centerlines)
+0
+# Conformance Item T33
+SELECT ST_Length(centerlines)
+FROM divided_routes
+WHERE name = 'Route 75';
+ST_Length(centerlines)
+96
+# Conformance Item T34
+SELECT ST_AsText(ST_Centroid(shores))
+FROM ponds
+WHERE fid = 120;
+ST_AsText(ST_Centroid(shores))
+POINT(25 42)
+# Conformance Item T36
+SELECT ST_Area(shores)
+FROM ponds
+WHERE fid = 120;
+ST_Area(shores)
+8
+# Conformance Item T37
+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';
+ST_Equals(boundary,
+ST_PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',1))
+1
+# Conformance Item T37
+# 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';
+ST_Equals(boundary,
+ST_PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',101))
+1
+# Conformance Item T38
+SELECT ST_Disjoint(centerlines, boundary)
+FROM divided_routes, named_places
+WHERE divided_routes.name = 'Route 75'
+AND named_places.name = 'Ashton';
+ST_Disjoint(centerlines, boundary)
+1
+# Conformance Item T39
+SELECT ST_Touches(centerline, shore)
+FROM streams, lakes
+WHERE streams.name = 'Cam Stream'
+AND lakes.name = 'Blue Lake';
+ST_Touches(centerline, shore)
+1
+# Conformance Item T42
+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';
+ST_Crosses(road_segments.centerline, divided_routes.centerlines)
+1
+# 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';
+ST_Intersects(road_segments.centerline, divided_routes.centerlines)
+1
+# 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';
+ST_Contains(forests.boundary, named_places.boundary)
+0
+# Conformance Item T46
+SELECT ST_Distance(position, boundary)
+FROM bridges, named_places
+WHERE bridges.name = 'Cam Bridge'
+AND named_places.name = 'Ashton';
+ST_Distance(position, boundary)
+12
+# 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';
+ST_AsText(ST_Difference(named_places.boundary, forests.boundary))
+POLYGON((56 34,62 48,84 48,84 42,56 34))
+SELECT ST_AsText(ST_Union(shore, boundary))
+FROM lakes, named_places
+WHERE lakes.name = 'Blue Lake'
+AND named_places.name = 'Goose Island';
+ST_AsText(ST_Union(shore, boundary))
+POLYGON((48 6,52 18,66 23,73 9,48 6))
+# Conformance Item T50
+SELECT ST_AsText(ST_SymDifference(shore, boundary))
+FROM lakes, named_places
+WHERE lakes.name = 'Blue Lake'
+AND named_places.name = 'Ashton';
+ST_AsText(ST_SymDifference(shore, boundary))
+MULTIPOLYGON(((48 6,52 18,66 23,73 9,48 6),(59 13,59 18,67 18,67 13,59 13)),((56 30,56 34,62 48,84 48,84 30,56 30)))
+# Conformance Item T51
+SELECT count(*)
+FROM buildings, bridges
+WHERE ST_Contains(ST_Buffer(bridges.position, 15.0), buildings.footprint) = 1;
+count(*)
+1
+DROP DATABASE gis_ogs;
+#
+# Bug#13362660 ASSERTION `FIELD_POS < FIELD_COUNT' FAILED. IN PROTOCOL_TEXT::STORE
+#
+SELECT ST_Union('', ''), md5(1);
+ERROR HY000: Illegal parameter data type varchar for operation 'st_union'
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-30 02:20:09 +0000