Debian Codegraph @

mysql-test/suite/innodb_gis/r/gis.result

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