diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/innodb_gis.result')
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb_gis.result | 610 |
1 files changed, 610 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/innodb_gis.result b/mysql-test/suite/innodb/r/innodb_gis.result new file mode 100644 index 00000000..4c89ad4b --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_gis.result @@ -0,0 +1,610 @@ +SET default_storage_engine=innodb; +DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; +CREATE TABLE gis_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT); +CREATE TABLE gis_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g LINESTRING); +CREATE TABLE gis_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POLYGON); +CREATE TABLE gis_multi_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOINT); +CREATE TABLE gis_multi_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTILINESTRING); +CREATE TABLE gis_multi_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOLYGON); +CREATE TABLE gis_geometrycollection (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRYCOLLECTION); +CREATE TABLE gis_geometry (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRY); +SHOW CREATE TABLE gis_point; +Table Create Table +gis_point CREATE TABLE `gis_point` ( + `fid` int(11) NOT NULL AUTO_INCREMENT, + `g` point DEFAULT NULL, + PRIMARY KEY (`fid`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SHOW FIELDS FROM gis_point; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL auto_increment +g point YES NULL +SHOW FIELDS FROM gis_line; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL auto_increment +g linestring YES NULL +SHOW FIELDS FROM gis_polygon; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL auto_increment +g polygon YES NULL +SHOW FIELDS FROM gis_multi_point; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL auto_increment +g multipoint YES NULL +SHOW FIELDS FROM gis_multi_line; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL auto_increment +g multilinestring YES NULL +SHOW FIELDS FROM gis_multi_polygon; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL auto_increment +g multipolygon YES NULL +SHOW FIELDS FROM gis_geometrycollection; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL auto_increment +g geometrycollection YES NULL +SHOW FIELDS FROM gis_geometry; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL auto_increment +g geometry YES NULL +INSERT INTO gis_point VALUES +(101, PointFromText('POINT(10 10)')), +(102, PointFromText('POINT(20 10)')), +(103, PointFromText('POINT(20 20)')), +(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)')))); +INSERT INTO gis_line VALUES +(105, LineFromText('LINESTRING(0 0,0 10,10 0)')), +(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')), +(107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10)))); +INSERT INTO gis_polygon VALUES +(108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')), +(109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')), +(110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))); +INSERT INTO gis_multi_point VALUES +(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')), +(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')), +(113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10)))); +INSERT INTO gis_multi_line VALUES +(114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')), +(115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')), +(116, MLineFromWKB(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, 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, 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, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))); +INSERT INTO gis_geometrycollection VALUES +(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')), +(121, GeometryFromWKB(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, AsText(g) FROM gis_point ORDER by fid; +fid AsText(g) +101 POINT(10 10) +102 POINT(20 10) +103 POINT(20 20) +104 POINT(10 20) +SELECT fid, AsText(g) FROM gis_line ORDER by fid; +fid 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, AsText(g) FROM gis_polygon ORDER by fid; +fid 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, AsText(g) FROM gis_multi_point ORDER by fid; +fid 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, AsText(g) FROM gis_multi_line ORDER by fid; +fid 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, AsText(g) FROM gis_multi_polygon ORDER by fid; +fid 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, AsText(g) FROM gis_geometrycollection ORDER by fid; +fid AsText(g) +120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10)) +121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9)) +SELECT fid, AsText(g) FROM gis_geometry ORDER by fid; +fid 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, Dimension(g) FROM gis_geometry ORDER by fid; +fid 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, GeometryType(g) FROM gis_geometry ORDER by fid; +fid 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, IsEmpty(g) FROM gis_geometry ORDER by fid; +fid 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, AsText(Envelope(g)) FROM gis_geometry ORDER by fid; +fid AsText(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 Dimension(g), GeometryType(g), IsEmpty(g), AsText(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 21 100.00 +Warnings: +Note 1003 select st_dimension(`test`.`gis_geometry`.`g`) AS `Dimension(g)`,st_geometrytype(`test`.`gis_geometry`.`g`) AS `GeometryType(g)`,st_isempty(`test`.`gis_geometry`.`g`) AS `IsEmpty(g)`,st_astext(st_envelope(`test`.`gis_geometry`.`g`)) AS `AsText(Envelope(g))` from `test`.`gis_geometry` +SELECT fid, X(g) FROM gis_point ORDER by fid; +fid X(g) +101 10 +102 20 +103 20 +104 10 +SELECT fid, Y(g) FROM gis_point ORDER by fid; +fid Y(g) +101 10 +102 10 +103 20 +104 20 +explain extended select X(g),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 4 100.00 +Warnings: +Note 1003 select st_x(`test`.`gis_point`.`g`) AS `X(g)`,st_y(`test`.`gis_point`.`g`) AS `Y(g)` from `test`.`gis_point` +SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid; +fid AsText(StartPoint(g)) +105 POINT(0 0) +106 POINT(10 10) +107 POINT(10 10) +SELECT fid, AsText(EndPoint(g)) FROM gis_line ORDER by fid; +fid AsText(EndPoint(g)) +105 POINT(10 0) +106 POINT(10 10) +107 POINT(40 10) +SELECT fid, GLength(g) FROM gis_line ORDER by fid; +fid GLength(g) +105 24.14213562373095 +106 40 +107 30 +SELECT fid, NumPoints(g) FROM gis_line ORDER by fid; +fid NumPoints(g) +105 3 +106 5 +107 2 +SELECT fid, AsText(PointN(g, 2)) FROM gis_line ORDER by fid; +fid AsText(PointN(g, 2)) +105 POINT(0 10) +106 POINT(20 10) +107 POINT(40 10) +SELECT fid, IsClosed(g) FROM gis_line ORDER by fid; +fid IsClosed(g) +105 0 +106 1 +107 0 +explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),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 3 100.00 +Warnings: +Note 1003 select st_astext(st_startpoint(`test`.`gis_line`.`g`)) AS `AsText(StartPoint(g))`,st_astext(st_endpoint(`test`.`gis_line`.`g`)) AS `AsText(EndPoint(g))`,st_length(`test`.`gis_line`.`g`) AS `GLength(g)`,st_numpoints(`test`.`gis_line`.`g`) AS `NumPoints(g)`,st_astext(st_pointn(`test`.`gis_line`.`g`,2)) AS `AsText(PointN(g, 2))`,st_isclosed(`test`.`gis_line`.`g`) AS `IsClosed(g)` from `test`.`gis_line` +SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid; +fid AsText(Centroid(g)) +108 POINT(15 15) +109 POINT(25.416666666666668 25.416666666666668) +110 POINT(20 10) +SELECT fid, Area(g) FROM gis_polygon ORDER by fid; +fid Area(g) +108 100 +109 2400 +110 450 +SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon ORDER by fid; +fid AsText(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, NumInteriorRings(g) FROM gis_polygon ORDER by fid; +fid NumInteriorRings(g) +108 0 +109 1 +110 0 +SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid; +fid AsText(InteriorRingN(g, 1)) +108 NULL +109 LINESTRING(10 10,20 10,20 20,10 20,10 10) +110 NULL +explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(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 3 100.00 +Warnings: +Note 1003 select st_astext(st_centroid(`test`.`gis_polygon`.`g`)) AS `AsText(Centroid(g))`,st_area(`test`.`gis_polygon`.`g`) AS `Area(g)`,st_astext(st_exteriorring(`test`.`gis_polygon`.`g`)) AS `AsText(ExteriorRing(g))`,st_numinteriorrings(`test`.`gis_polygon`.`g`) AS `NumInteriorRings(g)`,st_astext(st_interiorringn(`test`.`gis_polygon`.`g`,1)) AS `AsText(InteriorRingN(g, 1))` from `test`.`gis_polygon` +SELECT fid, IsClosed(g) FROM gis_multi_line ORDER by fid; +fid IsClosed(g) +114 0 +115 0 +116 0 +SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid; +fid AsText(Centroid(g)) +117 POINT(57.98031067576927 17.854754130800433) +118 POINT(57.98031067576927 17.854754130800433) +119 POINT(2 2) +SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid; +fid Area(g) +117 1684.5 +118 1684.5 +119 4.5 +SELECT fid, NumGeometries(g) from gis_multi_point ORDER by fid; +fid NumGeometries(g) +111 4 +112 4 +113 2 +SELECT fid, NumGeometries(g) from gis_multi_line ORDER by fid; +fid NumGeometries(g) +114 2 +115 1 +116 2 +SELECT fid, NumGeometries(g) from gis_multi_polygon ORDER by fid; +fid NumGeometries(g) +117 2 +118 2 +119 1 +SELECT fid, NumGeometries(g) from gis_geometrycollection ORDER by fid; +fid NumGeometries(g) +120 2 +121 2 +explain extended SELECT fid, 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 3 100.00 +Warnings: +Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,st_numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point` +SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid; +fid AsText(GeometryN(g, 2)) +111 POINT(10 10) +112 POINT(11 11) +113 POINT(4 10) +SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line ORDER by fid; +fid AsText(GeometryN(g, 2)) +114 LINESTRING(16 0,16 23,16 48) +115 NULL +116 LINESTRING(2 5,5 8,21 7) +SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon ORDER by fid; +fid AsText(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, AsText(GeometryN(g, 2)) from gis_geometrycollection ORDER by fid; +fid AsText(GeometryN(g, 2)) +120 LINESTRING(0 0,10 10) +121 LINESTRING(3 6,7 9) +SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection ORDER by fid; +fid AsText(GeometryN(g, 1)) +120 POINT(0 0) +121 POINT(44 6) +explain extended SELECT fid, AsText(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 3 100.00 +Warnings: +Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,st_astext(st_geometryn(`test`.`gis_multi_point`.`g`,2)) AS `AsText(GeometryN(g, 2))` from `test`.`gis_multi_point` +SELECT g1.fid as first, g2.fid as second, +Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o, +Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, +Intersects(g1.g, g2.g) as i, 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, +Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o, +Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, +Intersects(g1.g, g2.g) as i, 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 2 100.00 Using temporary; Using filesort +1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,st_within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,st_contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,mbroverlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,st_equals(`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 ( +a INTEGER PRIMARY KEY AUTO_INCREMENT, +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 +a int(11) NO PRI NULL auto_increment +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; +SHOW FIELDS FROM t1; +Field Type Null Key Default Extra +a int(11) NO PRI NULL auto_increment +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) YES NULL +DROP TABLE t1; +create table t1 (pk integer primary key auto_increment, a geometry not null); +insert into t1 (a) values (GeomFromText('Point(1 2)')); +insert into t1 (a) values ('Garbage'); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +insert IGNORE into t1 (a) values ('Garbage'); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +drop table t1; +create table t1 (pk integer primary key auto_increment, fl geometry not null); +insert into t1 (fl) values (1); +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t1`.`fl` +insert into t1 (fl) values (1.11); +ERROR HY000: Cannot cast 'decimal' as 'geometry' in assignment of `test`.`t1`.`fl` +insert into t1 (fl) values ("qwerty"); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +insert into t1 (fl) values (pointfromtext('point(1,1)')); +ERROR 23000: Column 'fl' cannot be null +drop table t1; +End of 4.1 tests +CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY); +INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))')); +INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')); +INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); +INSERT INTO t1 VALUES("up", GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))')); +INSERT INTO t1 VALUES("up2", GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))')); +INSERT INTO t1 VALUES("up3", GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))')); +INSERT INTO t1 VALUES("down", GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))')); +INSERT INTO t1 VALUES("down2", GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))')); +INSERT INTO t1 VALUES("down3", GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))')); +INSERT INTO t1 VALUES("right", GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))')); +INSERT INTO t1 VALUES("right2", GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))')); +INSERT INTO t1 VALUES("right3", GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))')); +INSERT INTO t1 VALUES("left", GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))')); +INSERT INTO t1 VALUES("left2", GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))')); +INSERT INTO t1 VALUES("left3", 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 mbrequal FROM t1 a1 JOIN t1 a2 ON MBREqual( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +mbrequal +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 contains FROM t1 a1 JOIN t1 a2 ON Contains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +contains +center,small +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS disjoint FROM t1 a1 JOIN t1 a2 ON Disjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +disjoint +down3,left3,right3,up3 +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS equals FROM t1 a1 JOIN t1 a2 ON Equals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +equals +center +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS intersects FROM t1 a1 JOIN t1 a2 ON Intersects( 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 overlaps FROM t1 a1 JOIN t1 a2 ON Overlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +overlaps +down,left,right,up +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS touches FROM t1 a1 JOIN t1 a2 ON Touches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +touches +down2,left2,right2,up2 +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS within FROM t1 a1 JOIN t1 a2 ON Within( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +within +big,center +SET @vert1 = GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))'); +SET @horiz1 = GeomFromText('POLYGON ((-2 0, 2 0, -2 0))'); +SET @horiz2 = GeomFromText('POLYGON ((-1 0, 3 0, -1 0))'); +SET @horiz3 = GeomFromText('POLYGON ((2 0, 3 0, 2 0))'); +SET @point1 = GeomFromText('POLYGON ((0 0))'); +SET @point2 = GeomFromText('POLYGON ((-2 0))'); +SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @vert1) GROUP BY a1.name; +overlaps +SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @horiz1) GROUP BY a1.name; +overlaps +SELECT Overlaps(@horiz1, @vert1) FROM DUAL; +Overlaps(@horiz1, @vert1) +0 +SELECT Overlaps(@horiz1, @horiz2) FROM DUAL; +Overlaps(@horiz1, @horiz2) +1 +SELECT Overlaps(@horiz1, @horiz3) FROM DUAL; +Overlaps(@horiz1, @horiz3) +0 +SELECT Overlaps(@horiz1, @point1) FROM DUAL; +Overlaps(@horiz1, @point1) +0 +SELECT Overlaps(@horiz1, @point2) FROM DUAL; +Overlaps(@horiz1, @point2) +0 +DROP TABLE t1; +End of 5.0 tests +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 (g geometry not null, spatial gk(g)) engine=innodb; +drop table t1; +create table t1(id int not null primary key, g1 geometry not null, spatial index(g1)); +insert into t1 values(1, polygonfromtext('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))')); +explain select id from t1 where contains(g1, pointfromtext('POINT(1 1)')); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range g1 g1 34 NULL 1 Using where +select id from t1 where contains(g1, pointfromtext('POINT(1 1)')); +id +1 +drop table t1; |