Debian Codegraph @

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

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