Debian Codegraph @

mysql-test/suite/innodb_gis/t/1.test

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