Debian Codegraph @

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

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