diff options
Diffstat (limited to 'mysql-test/r/gis.result')
| -rw-r--r-- | mysql-test/r/gis.result | 222 |
1 files changed, 211 insertions, 11 deletions
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index f77cd4d14bf..76f4f6accdb 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -1,5 +1,3 @@ -DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; -DROP VIEW IF EXISTS v1; 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); @@ -405,12 +403,12 @@ 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 1 1 0 +120 120 1 1 0 1 0 0 1 0 120 121 0 0 1 0 0 0 1 0 120 122 NULL NULL NULL NULL NULL NULL NULL NULL 120 123 NULL NULL NULL NULL NULL NULL NULL NULL 121 120 0 0 1 0 0 0 1 0 -121 121 1 1 0 1 0 1 1 0 +121 121 1 1 0 1 0 0 1 0 121 122 NULL NULL NULL NULL NULL NULL NULL NULL 121 123 NULL NULL NULL NULL NULL NULL NULL NULL 122 120 NULL NULL NULL NULL NULL NULL NULL NULL @@ -811,7 +809,7 @@ drop procedure if exists fn3; create function fn3 () returns point deterministic return GeomFromText("point(1 1)"); show create function fn3; Function sql_mode Create Function character_set_client collation_connection Database Collation -fn3 CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS point +fn3 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS point DETERMINISTIC return GeomFromText("point(1 1)") latin1 latin1_swedish_ci latin1_swedish_ci select astext(fn3()); @@ -879,7 +877,7 @@ 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 -big,center,down,down2,left,left2,right,right2,small,up,up2 +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 @@ -900,7 +898,7 @@ 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 -big,center,down,down2,left,left2,right,right2,small,up,up2 +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 @@ -1113,7 +1111,7 @@ DROP TABLE t0, t1, t2; # SELECT ISCLOSED(CONVERT(CONCAT(' ', 0x2), BINARY(20))); ISCLOSED(CONVERT(CONCAT(' ', 0x2), BINARY(20))) -NULL +-1 # # BUG#12537203 - CRASH WHEN SUBSELECTING GLOBAL VARIABLES IN # GEOMETRY FUNCTION ARGUMENTS @@ -1327,6 +1325,18 @@ WHERE name = 'Route 5' AND aliases = 'Main Street'; IsEmpty(centerline) 0 +# Conformance Item T12 +SELECT IsSimple(shore) +FROM lakes +WHERE name = 'Blue Lake'; +IsSimple(shore) +1 +# Conformance Item T13 +SELECT AsText(ST_Boundary(boundary)) +FROM named_places +WHERE name = 'Goose Island'; +AsText(ST_Boundary(boundary)) +LINESTRING(67 13,67 18,59 18,59 13,67 13) # Conformance Item T14 SELECT AsText(Envelope(boundary)) FROM named_places @@ -1357,6 +1367,17 @@ FROM road_segments WHERE fid = 102; AsText(EndPoint(centerline)) POINT(44 31) +SELECT IsClosed(LineFromWKB(AsBinary(Boundary(boundary)),SRID(boundary))) +FROM named_places +WHERE name = 'Goose Island'; +IsClosed(LineFromWKB(AsBinary(Boundary(boundary)),SRID(boundary))) +1 +# Conformance Item T20 +SELECT IsRing(LineFromWKB(AsBinary(Boundary(boundary)),SRID(boundary))) +FROM named_places +WHERE name = 'Goose Island'; +IsRing(LineFromWKB(AsBinary(Boundary(boundary)),SRID(boundary))) +1 # Conformance Item T21 SELECT GLength(centerline) FROM road_segments @@ -1381,6 +1402,11 @@ FROM named_places WHERE name = 'Goose Island'; AsText(Centroid(boundary)) POINT(63 15.5) +SELECT ST_Contains(boundary, PointOnSurface(boundary)) +FROM named_places +WHERE name = 'Goose Island'; +ST_Contains(boundary, PointOnSurface(boundary)) +1 # Conformance Item T26 SELECT Area(boundary) FROM named_places @@ -1435,6 +1461,12 @@ FROM ponds WHERE fid = 120; AsText(Centroid(shores)) POINT(25 42) +# Conformance Item T35 +SELECT Contains(shores, PointOnSurface(shores)) +FROM ponds +WHERE fid = 120; +Contains(shores, PointOnSurface(shores)) +1 # Conformance Item T36 SELECT Area(shores) FROM ponds @@ -1463,6 +1495,20 @@ WHERE streams.name = 'Cam Stream' AND lakes.name = 'Blue Lake'; ST_Touches(centerline, shore) 1 +# Conformance Item T40 +SELECT ST_Within(footprint, boundary) +FROM named_places, buildings +WHERE named_places.name = 'Ashton' +AND buildings.address = '215 Main Street'; +ST_Within(footprint, boundary) +1 +# 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'; +ST_Overlaps(forests.boundary, named_places.boundary) +1 # Conformance Item T42 SELECT Crosses(road_segments.centerline, divided_routes.centerlines) FROM road_segments, divided_routes @@ -1484,6 +1530,13 @@ WHERE forests.name = 'Green Forest' AND named_places.name = 'Ashton'; ST_Contains(forests.boundary, named_places.boundary) 0 +# Conformance Item T45 +SELECT ST_Relate(forests.boundary, named_places.boundary, 'TTTTTTTTT') +FROM forests, named_places +WHERE forests.name = 'Green Forest' +AND named_places.name = 'Ashton'; +ST_Relate(forests.boundary, named_places.boundary, 'TTTTTTTTT') +1 # Conformance Item T46 SELECT ST_Distance(position, boundary) FROM bridges, named_places @@ -1491,6 +1544,13 @@ WHERE bridges.name = 'Cam Bridge' AND named_places.name = 'Ashton'; ST_Distance(position, boundary) 12 +# Conformance Item T47 +SELECT AsText(ST_Intersection(centerline, shore)) +FROM streams, lakes +WHERE streams.name = 'Cam Stream' +AND lakes.name = 'Blue Lake'; +AsText(ST_Intersection(centerline, shore)) +POINT(52 18) # Conformance Item T48 SELECT AsText(ST_Difference(named_places.boundary, forests.boundary)) FROM named_places, forests @@ -1517,6 +1577,12 @@ FROM buildings, bridges WHERE ST_Contains(ST_Buffer(bridges.position, 15.0), buildings.footprint) = 1; count(*) 1 +# Conformance Item T52 +SELECT AsText(ConvexHull(shore)) +FROM lakes +WHERE lakes.name = 'Blue Lake'; +AsText(ConvexHull(shore)) +POLYGON((48 6,52 18,66 23,73 9,48 6)) DROP DATABASE gis_ogs; USE test; # @@ -1552,11 +1618,16 @@ insert into t1 values(geomfromtext("POINT(0 9.2233720368548e18)")); select equals(`a`,convert(`a` using utf8)) from `t1`; equals(`a`,convert(`a` using utf8)) 1 +0 +0 +Warnings: +Warning 1300 Invalid utf8 character string: '\xE0C' +Warning 1300 Invalid utf8 character string: '\xE0C' +select equals(`a`,left(`a`,23)) from `t1`; +equals(`a`,left(`a`,23)) +NULL NULL NULL -Warnings: -Warning 1300 Invalid utf8 character string: 'E043' -Warning 1300 Invalid utf8 character string: 'E043' drop table t1; # # MDEV-6883 ST_WITHIN crashes server if (0,0) is matched to POLYGON((0 0)) @@ -1590,6 +1661,9 @@ INSERT INTO g1 VALUES ('a'),('a'); SELECT 1 FROM g1 WHERE a >= ANY (SELECT 1 FROM g1 WHERE a = geomfromtext('') OR a) ; 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'a' DROP TABLE g1; # # Bug#16451878 GEOMETRY QUERY CRASHES SERVER @@ -1661,3 +1735,129 @@ SET optimizer_switch=@save_optimizer_switch; # # End 10.0 tests # +SHOW CREATE TABLE information_schema.geometry_columns; +Table Create Table +GEOMETRY_COLUMNS CREATE TEMPORARY TABLE `GEOMETRY_COLUMNS` ( + `F_TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '', + `F_TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', + `F_TABLE_NAME` varchar(64) NOT NULL DEFAULT '', + `F_GEOMETRY_COLUMN` varchar(64) NOT NULL DEFAULT '', + `G_TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '', + `G_TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', + `G_TABLE_NAME` varchar(64) NOT NULL DEFAULT '', + `G_GEOMETRY_COLUMN` varchar(64) NOT NULL DEFAULT '', + `STORAGE_TYPE` tinyint(2) NOT NULL DEFAULT '0', + `GEOMETRY_TYPE` int(7) NOT NULL DEFAULT '0', + `COORD_DIMENSION` tinyint(2) NOT NULL DEFAULT '0', + `MAX_PPR` tinyint(2) NOT NULL DEFAULT '0', + `SRID` smallint(5) NOT NULL DEFAULT '0' +) ENGINE=MEMORY DEFAULT CHARSET=utf8 +SHOW CREATE TABLE information_schema.spatial_ref_sys; +Table Create Table +SPATIAL_REF_SYS CREATE TEMPORARY TABLE `SPATIAL_REF_SYS` ( + `SRID` smallint(5) NOT NULL DEFAULT '0', + `AUTH_NAME` varchar(512) NOT NULL DEFAULT '', + `AUTH_SRID` int(5) NOT NULL DEFAULT '0', + `SRTEXT` varchar(2048) NOT NULL DEFAULT '' +) ENGINE=MEMORY DEFAULT CHARSET=utf8 +create table t1(g GEOMETRY, pt POINT); +create table t2(g LINESTRING, pl POLYGON); +select * from information_schema.geometry_columns where f_table_schema='test'; +F_TABLE_CATALOG F_TABLE_SCHEMA F_TABLE_NAME F_GEOMETRY_COLUMN G_TABLE_CATALOG G_TABLE_SCHEMA G_TABLE_NAME G_GEOMETRY_COLUMN STORAGE_TYPE GEOMETRY_TYPE COORD_DIMENSION MAX_PPR SRID +def test t1 def test t1 g 1 0 2 0 0 +def test t1 def test t1 pt 1 1 2 0 0 +def test t2 def test t2 g 1 2 2 0 0 +def test t2 def test t2 pl 1 3 2 0 0 +drop table t1, t2; +10.1 tests +create table t1(g GEOMETRY(9,4) REF_SYSTEM_ID=101, pt POINT(8,2), pg GEOMETRY REF_SYSTEM_ID=102); +SELECT SRID from information_schema.geometry_columns WHERE f_table_schema='test' and G_TABLE_NAME='t1'; +SRID +101 +0 +102 +drop table t1; +# Expect an int(1) column to be created +CREATE TABLE t1 AS SELECT CONTAINS(NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `CONTAINS(NULL, NULL)` int(1) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# MDEV-7334 valgrind warning "unitialized bytes" in 10.1. +# +CREATE TABLE t1 ( +gp point, +ln linestring, +pg polygon, +mp multipoint, +mln multilinestring, +mpg multipolygon, +gc geometrycollection, +gm geometry +); +ALTER TABLE t1 ADD fid INT NOT NULL; +select SRID from information_schema.geometry_columns where F_TABLE_NAME='t1'; +SRID +0 +0 +0 +0 +0 +0 +0 +0 +drop table t1; +# +# MDEV-7510 GIS: IsRing returns false for a primitive triangle. +# +select ST_IsRing(ST_LineFromText('LINESTRING(0 0,0 10,10 10,0 0)')); +ST_IsRing(ST_LineFromText('LINESTRING(0 0,0 10,10 10,0 0)')) +1 +select ST_IsRing(ST_LineFromText('LINESTRING(0 0,0 10,10 10,-10 -10, 0 -10, 0 0)')); +ST_IsRing(ST_LineFromText('LINESTRING(0 0,0 10,10 10,-10 -10, 0 -10, 0 0)')) +0 +# +# MDEV-7514 GIS: PointOnSurface returns NULL instead of the point. +# +SELECT ST_GEOMETRYTYPE(ST_PointOnSurface(ST_PolyFromText('POLYGON((-70.916 42.1002,-70.9468 42.0946,-70.9754 42.0875,-70.9749 42.0879,-70.9759 42.0897,-70.916 42.1002))'))); +ST_GEOMETRYTYPE(ST_PointOnSurface(ST_PolyFromText('POLYGON((-70.916 42.1002,-70.9468 42.0946,-70.9754 42.0875,-70.9749 42.0879,-70.9759 42.0897,-70.916 42.1002))'))) +NULL +# +# MDEV-7529 GIS: ST_Relate returns unexpected results for POINT relations +# +select ST_Relate(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)'),'T*F**FFF*') AS equals; +equals +1 +select ST_Relate(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)'),'T*****FF*') AS contains; +contains +1 +select ST_Relate(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)'),'T*F**F***') AS within; +within +1 +select ST_Relate(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(1 1)'),'FF*FF****') as disjoint; +disjoint +1 +select ST_Relate(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)'),'FF*FF****') as disjoint; +disjoint +0 +# +# MDEV-7528 GIS: Functions return NULL instead of specified -1 for NULL arguments. +# +select ST_IsRing(NULL); +ST_IsRing(NULL) +-1 +# +# MDEV-8675 Different results of GIS functions on NULL vs NOT NULL columns +# +CREATE TABLE t1 (g1 GEOMETRY NOT NULL,g2 GEOMETRY NULL); +CREATE TABLE t2 AS SELECT WITHIN(g1,g1) as w1,WITHIN(g2,g2) AS w2 FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `w1` int(1) DEFAULT NULL, + `w2` int(1) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1,t2; |