Debian Codegraph @

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

raw
1 SET default_storage_engine=InnoDB;
2 SET innodb_strict_mode=OFF;
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(11) NO PRI NULL
14 g point YES NULL
15 SHOW FIELDS FROM gis_line;
16 Field Type Null Key Default Extra
17 fid int(11) NO PRI NULL
18 g linestring YES NULL
19 SHOW FIELDS FROM gis_polygon;
20 Field Type Null Key Default Extra
21 fid int(11) 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(11) 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(11) 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(11) NO PRI NULL
34 g multipolygon YES NULL
35 SHOW FIELDS FROM gis_geometrycollection;
36 Field Type Null Key Default Extra
37 fid int(11) NO PRI NULL
38 g geometrycollection YES NULL
39 SHOW FIELDS FROM gis_geometry;
40 Field Type Null Key Default Extra
41 fid int(11) 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 GEOMETRYCOLLECTION
181 121 GEOMETRYCOLLECTION
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 POLYGON((10 10,10 10,10 10,10 10,10 10))
208 102 POLYGON((20 10,20 10,20 10,20 10,20 10))
209 103 POLYGON((20 20,20 20,20 20,20 20,20 20))
210 104 POLYGON((10 20,10 20,10 20,10 20,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 POLYGON((10 10,40 10,40 10,10 10,10 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 POLYGON((10 0,10 0,10 48,10 48,10 0))
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 extended select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry;
229 id select_type table type possible_keys key key_len ref rows filtered Extra
230 1 SIMPLE gis_geometry ALL NULL NULL NULL NULL # #
231 Warnings:
232 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`
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 extended select ST_X(g),ST_Y(g) FROM gis_point;
246 id select_type table type possible_keys key key_len ref rows filtered Extra
247 1 SIMPLE gis_point ALL NULL NULL NULL NULL # #
248 Warnings:
249 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`
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 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;
281 id select_type table type possible_keys key key_len ref rows filtered Extra
282 1 SIMPLE gis_line ALL NULL NULL NULL NULL # #
283 Warnings:
284 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`
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 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;
311 id select_type table type possible_keys key key_len ref rows filtered Extra
312 1 SIMPLE gis_polygon ALL NULL NULL NULL NULL # #
313 Warnings:
314 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`
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.854754130800433)
323 118 POINT(57.98031067576927 17.854754130800433)
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 extended SELECT fid, ST_NumGeometries(g) from gis_multi_point;
350 id select_type table type possible_keys key key_len ref rows filtered Extra
351 1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL # #
352 Warnings:
353 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`
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 extended SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
378 id select_type table type possible_keys key key_len ref rows filtered Extra
379 1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL # #
380 Warnings:
381 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`
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 extended 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 type possible_keys key key_len ref rows filtered Extra
398 1 SIMPLE g1 ALL NULL NULL NULL NULL # # Using temporary; Using filesort
399 1 SIMPLE g2 ALL NULL NULL NULL NULL # # Using join buffer (flat, BNL join)
400 Warnings:
401 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`
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 geometrycollection 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 geometrycollection YES NULL
433 gm geometry YES NULL
434 fid int(11) NO NULL
435 DROP TABLE t1;
436 SELECT ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_GeometryFromText('POINT(1 4)')))) AS val;
437 val
438 POINT(1 4)
439 explain extended SELECT ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_GeometryFromText('POINT(1 4)'))));
440 id select_type table type possible_keys key key_len ref rows filtered Extra
441 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
442 Warnings:
443 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)'))))`
444 explain extended SELECT ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_PointFromText('POINT(1 4)'))));
445 id select_type table type possible_keys key key_len ref rows filtered Extra
446 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
447 Warnings:
448 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)'))))`
449 SELECT ST_SRID(ST_GeomFromText('LineString(1 1,2 2)',101));
450 ST_SRID(ST_GeomFromText('LineString(1 1,2 2)',101))
451 101
452 explain extended SELECT ST_SRID(ST_GeomFromText('LineString(1 1,2 2)',101));
453 id select_type table type possible_keys key key_len ref rows filtered Extra
454 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
455 Warnings:
456 Note 1003 select srid(st_geometryfromtext('LineString(1 1,2 2)',101)) AS `ST_SRID(ST_GeomFromText('LineString(1 1,2 2)',101))`
457 explain extended select ST_issimple(MultiPoint(Point(3, 6), Point(4, 10))), ST_issimple(Point(3, 6));
458 id select_type table type possible_keys key key_len ref rows filtered Extra
459 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
460 Warnings:
461 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))`
462 create table t1 (a geometry not null);
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, index(a(5)));
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, INDEX(Coordinates(10)));
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(POINT(1,3));
579 show create table t1;
580 Table Create Table
581 t1 CREATE TABLE `t1` (
582 `ST_GeomFromWKB(POINT(1,3))` geometry DEFAULT NULL
583 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_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) ENGINE=InnoDB ;
588 SET sql_mode = default;
589 insert into t1 values ('85984',ST_GeomFromText('MULTIPOLYGON(((-115.006363
590 36.305435,-114.992394 36.305202,-114.991219 36.305975,-114.991163
591 36.306845,-114.989432 36.309452,-114.978275 36.312642,-114.977363
592 36.311978,-114.975327 36.312344,-114.96502 36.31597,-114.963364
593 36.313629,-114.961723 36.313721,-114.956398 36.316057,-114.951882
594 36.320979,-114.947073 36.323475,-114.945207 36.326451,-114.945207
595 36.326451,-114.944132 36.326061,-114.94003 36.326588,-114.924017
596 36.334484,-114.923281 36.334146,-114.92564 36.331504,-114.94072
597 36.319282,-114.945348 36.314812,-114.948091 36.314762,-114.951755
598 36.316211,-114.952446 36.313883,-114.952644 36.309488,-114.944725
599 36.313083,-114.93706 36.32043,-114.932478 36.323497,-114.924556
600 36.327708,-114.922608 36.329715,-114.92009 36.328695,-114.912105
601 36.323566,-114.901647 36.317952,-114.897436 36.313968,-114.895344
602 36.309573,-114.891699 36.304398,-114.890569 36.303551,-114.886356
603 36.302702,-114.885141 36.301351,-114.885709 36.297391,-114.892499
604 36.290893,-114.902142 36.288974,-114.904941 36.288838,-114.905308
605 36.289845,-114.906325 36.290395,-114.909916 36.289549,-114.914527
606 36.287535,-114.918797 36.284423,-114.922982 36.279731,-114.924113
607 36.277282,-114.924057 36.275817,-114.927733 36.27053,-114.929354
608 36.269029,-114.929354 36.269029,-114.950856 36.268715,-114.950768
609 36.264324,-114.960206 36.264293,-114.960301 36.268943,-115.006662
610 36.268929,-115.008583 36.265619,-115.00665 36.264247,-115.006659
611 36.246873,-115.006659 36.246873,-115.006838 36.247697,-115.010764
612 36.247774,-115.015609 36.25113,-115.015765 36.254505,-115.029517
613 36.254619,-115.038573 36.249317,-115.038573 36.249317,-115.023403
614 36.25841,-115.023873 36.258994,-115.031845 36.259829,-115.03183
615 36.261053,-115.025561 36.261095,-115.036417 36.274632,-115.033729
616 36.276041,-115.032217 36.274851,-115.029845 36.273959,-115.029934
617 36.274966,-115.025763 36.274896,-115.025406 36.281044,-115.028731
618 36.284471,-115.036497 36.290377,-115.042071 36.291039,-115.026759
619 36.298478,-115.008995 36.301966,-115.006363 36.305435),(-115.079835
620 36.244369,-115.079735 36.260186,-115.076435 36.262369,-115.069758
621 36.265,-115.070235 36.268757,-115.064542 36.268655,-115.061843
622 36.269857,-115.062676 36.270693,-115.06305 36.272344,-115.059051
623 36.281023,-115.05918 36.283008,-115.060591 36.285246,-115.061913
624 36.290022,-115.062499 36.306353,-115.062499 36.306353,-115.060918
625 36.30642,-115.06112 36.289779,-115.05713 36.2825,-115.057314
626 36.279446,-115.060779 36.274659,-115.061366 36.27209,-115.057858
627 36.26557,-115.055805 36.262883,-115.054688 36.262874,-115.047335
628 36.25037,-115.044234 36.24637,-115.052434 36.24047,-115.061734
629 36.23507,-115.061934 36.22677,-115.061934 36.22677,-115.061491
630 36.225267,-115.062024 36.218194,-115.060134 36.218278,-115.060133
631 36.210771,-115.057833 36.210771,-115.057433 36.196271,-115.062233
632 36.196271,-115.062233 36.190371,-115.062233 36.190371,-115.065533
633 36.190371,-115.071333 36.188571,-115.098331 36.188275,-115.098331
634 36.188275,-115.098435 36.237569,-115.097535 36.240369,-115.097535
635 36.240369,-115.093235 36.240369,-115.089135 36.240469,-115.083135
636 36.240569,-115.083135 36.240569,-115.079835
637 36.244369)))')),('85998',ST_GeomFromText('MULTIPOLYGON(((-115.333107
638 36.264587,-115.333168 36.280638,-115.333168 36.280638,-115.32226
639 36.280643,-115.322538 36.274311,-115.327222 36.274258,-115.32733
640 36.263026,-115.330675 36.262984,-115.332132 36.264673,-115.333107
641 36.264587),(-115.247239 36.247066,-115.247438 36.218267,-115.247438
642 36.218267,-115.278525 36.219263,-115.278525 36.219263,-115.301545
643 36.219559,-115.332748 36.219197,-115.332757 36.220041,-115.332757
644 36.220041,-115.332895 36.233514,-115.349023 36.233479,-115.351489
645 36.234475,-115.353681 36.237021,-115.357106 36.239789,-115.36519
646 36.243331,-115.368156 36.243487,-115.367389 36.244902,-115.364553
647 36.246014,-115.359219 36.24616,-115.356186 36.248025,-115.353347
648 36.248004,-115.350813 36.249507,-115.339673 36.25387,-115.333069
649 36.255018,-115.333069 36.255018,-115.333042 36.247767,-115.279039
650 36.248666,-115.263639 36.247466,-115.263839 36.252766,-115.261439
651 36.252666,-115.261439 36.247366,-115.247239 36.247066)))'));
652 select object_id, ST_geometrytype(geo), ST_ISSIMPLE(GEO), ST_ASTEXT(ST_centroid(geo)) from
653 t1 where object_id=85998;
654 object_id ST_geometrytype(geo) ST_ISSIMPLE(GEO) ST_ASTEXT(ST_centroid(geo))
655 85998 MULTIPOLYGON 1 POINT(115.2970604672862 -36.23335610879993)
656 select object_id, ST_geometrytype(geo), ST_ISSIMPLE(GEO), ST_ASTEXT(ST_centroid(geo)) from
657 t1 where object_id=85984;
658 object_id ST_geometrytype(geo) ST_ISSIMPLE(GEO) ST_ASTEXT(ST_centroid(geo))
659 85984 MULTIPOLYGON 1 POINT(-114.86854472054372 36.34725218253213)
660 drop table t1;
661 create table t1 (fl geometry not null);
662 insert into t1 values (1);
663 ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t1`.`fl`
664 insert into t1 values (1.11);
665 ERROR HY000: Cannot cast 'decimal' as 'geometry' in assignment of `test`.`t1`.`fl`
666 insert into t1 values ("qwerty");
667 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
668 insert into t1 values (ST_pointfromtext('point(1,1)'));
669 ERROR 23000: Column 'fl' cannot be null
670 drop table t1;
671 select (ST_asWKT(ST_geomfromwkb((0x000000000140240000000000004024000000000000)))) AS val;
672 val
673 POINT(10 10)
674 select (ST_asWKT(ST_geomfromwkb((0x010100000000000000000024400000000000002440)))) AS val;
675 val
676 POINT(10 10)
677 create table t1 (g GEOMETRY);
678 select * from t1;
679 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
680 def test t1 t1 g g 255 4294967295 0 Y 144 0 63
681 g
682 select ST_asbinary(g) from t1;
683 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
684 def ST_asbinary(g) 251 4294967295 0 Y 128 0 63
685 ST_asbinary(g)
686 drop table t1;
687 create table t1 (a TEXT, b GEOMETRY NOT NULL, INDEX(b(5)));
688 alter table t1 disable keys;
689 Warnings:
690 Note 1031 Storage engine InnoDB of the table `test`.`t1` doesn't have this option
691 load data infile '../../std_data/bad_gis_data.dat' into table t1;
692 ERROR 22004: Column set to default value; NULL supplied to NOT NULL column 'b' at row 1
693 alter table t1 enable keys;
694 Warnings:
695 Note 1031 Storage engine InnoDB of the table `test`.`t1` doesn't have this option
696 drop table t1;
697 create table t1 (a int, b blob);
698 insert into t1 values (1, ''), (2, NULL), (3, '1');
699 select * from t1;
700 a b
701 1
702 2 NULL
703 3 1
704 select
705 ST_geometryfromtext(b) IS NULL, ST_geometryfromwkb(b) IS NULL, ST_astext(b) IS NULL,
706 ST_aswkb(b) IS NULL, ST_geometrytype(b) IS NULL, ST_centroid(b) IS NULL,
707 ST_envelope(b) IS NULL, ST_startpoint(b) IS NULL, ST_endpoint(b) IS NULL,
708 ST_exteriorring(b) IS NULL, ST_pointn(b, 1) IS NULL, ST_geometryn(b, 1) IS NULL,
709 ST_interiorringn(b, 1) IS NULL, multipoint(b) IS NULL, ST_isempty(b) IS NULL,
710 ST_issimple(b) IS NULL, ST_isclosed(b) IS NULL, ST_dimension(b) IS NULL,
711 ST_numgeometries(b) IS NULL, ST_numinteriorrings(b) IS NULL, ST_numpoints(b) IS NULL,
712 ST_area(b) IS NULL, ST_length(b) IS NULL, ST_srid(b) IS NULL, ST_x(b) IS NULL,
713 ST_y(b) IS NULL
714 from t1;
715 ERROR 22007: Illegal non geometric '`test`.`t1`.`b`' value found during parsing
716 select
717 MBRwithin(b, b) IS NULL, MBRcontains(b, b) IS NULL, MBRoverlaps(b, b) IS NULL,
718 MBRequals(b, b) IS NULL, MBRdisjoint(b, b) IS NULL, ST_touches(b, b) IS NULL,
719 MBRintersects(b, b) IS NULL, ST_crosses(b, b) IS NULL
720 from t1;
721 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
722 1 1 1 1 1 1 1 1
723 1 1 1 1 1 1 1 1
724 1 1 1 1 1 1 1 1
725 select
726 point(b, b) IS NULL, linestring(b) IS NULL, polygon(b) IS NULL, multipoint(b) IS NULL,
727 multilinestring(b) IS NULL, multipolygon(b) IS NULL,
728 geometrycollection(b) IS NULL
729 from t1;
730 ERROR 22007: Illegal non geometric '`test`.`t1`.`b`' value found during parsing
731 drop table t1;
732 CREATE TABLE t1(a POINT) ENGINE=InnoDB;
733 INSERT INTO t1 VALUES (NULL);
734 SELECT * FROM t1;
735 a
736 NULL
737 DROP TABLE t1;
738 CREATE TABLE `t1` ( `col9` set('a'), `col89` date);
739 INSERT IGNORE INTO `t1` VALUES ('','0000-00-00');
740 select ST_geomfromtext(col9,col89) as a from t1;
741 a
742 NULL
743 DROP TABLE t1;
744 CREATE TABLE t1 (
745 geomdata polygon NOT NULL,
746 KEY index_geom (geomdata(10))
747 ) ENGINE=InnoDB DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED;
748 Warnings:
749 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
750 CREATE TABLE t2 (
751 geomdata polygon NOT NULL,
752 KEY index_geom (geomdata(10))
753 ) ENGINE=InnoDB DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED;
754 Warnings:
755 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
756 CREATE TABLE t3
757 select
758 ST_aswkb(ws.geomdata) AS geomdatawkb
759 from
760 t1 ws
761 union
762 select
763 ST_aswkb(ws.geomdata) AS geomdatawkb
764 from
765 t2 ws;
766 describe t3;
767 Field Type Null Key Default Extra
768 geomdatawkb longblob YES NULL
769 drop table t1;
770 drop table t2;
771 drop table t3;
772 create table t1(col1 geometry default null,col15 geometrycollection not
773 null, index(col15(5)),index(col1(15)))engine=InnoDB;
774 insert into t1 set col15 = ST_GeomFromText('POINT(6 5)');
775 insert into t1 set col15 = ST_GeomFromText('POINT(6 5)');
776 check table t1 extended;
777 Table Op Msg_type Msg_text
778 test.t1 check status OK
779 drop table t1;
780 End of 4.1 tests
781 create table t1 (s1 geometry not null,s2 char(100));
782 create trigger t1_bu before update on t1 for each row set new.s1 = null;
783 insert into t1 values (null,null);
784 ERROR 23000: Column 's1' cannot be null
785 drop table t1;
786 drop procedure if exists fn3;
787 create function fn3 () returns point deterministic return ST_GeomFromText("point(1 1)");
788 show create function fn3;
789 Function sql_mode Create Function character_set_client collation_connection Database Collation
790 fn3 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS point
791 DETERMINISTIC
792 return ST_GeomFromText("point(1 1)") latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
793 select ST_astext(fn3());
794 ST_astext(fn3())
795 POINT(1 1)
796 drop function fn3;
797 create table t1(pt POINT);
798 alter table t1 add primary key pti(pt);
799 Warnings:
800 Warning 1280 Name 'pti' ignored for PRIMARY key.
801 drop table t1;
802 create table t1(pt GEOMETRY);
803 alter table t1 add primary key pti(pt);
804 ERROR 42000: BLOB/TEXT column 'pt' used in key specification without a key length
805 alter table t1 add primary key pti(pt(20));
806 Warnings:
807 Warning 1280 Name 'pti' ignored for PRIMARY key.
808 drop table t1;
809 create table t1 select ST_GeomFromText('point(1 1)');
810 desc t1;
811 Field Type Null Key Default Extra
812 ST_GeomFromText('point(1 1)') geometry YES NULL
813 drop table t1;
814 create table t1 (g geometry not null);
815 insert into t1 values(default);
816 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
817 drop table t1;
818 CREATE TABLE t1 (a GEOMETRY);
819 CREATE VIEW v1 AS SELECT ST_GeomFromwkb(ST_ASBINARY(a)) FROM t1;
820 CREATE VIEW v2 AS SELECT a FROM t1;
821 DESCRIBE v1;
822 Field Type Null Key Default Extra
823 ST_GeomFromwkb(ST_ASBINARY(a)) geometry YES NULL
824 DESCRIBE v2;
825 Field Type Null Key Default Extra
826 a geometry YES NULL
827 DROP VIEW v1,v2;
828 DROP TABLE t1;
829 create table t1 (name VARCHAR(100), square GEOMETRY);
830 INSERT INTO t1 VALUES("center", ST_GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));
831 INSERT INTO t1 VALUES("small", ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
832 INSERT INTO t1 VALUES("big", ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
833 INSERT INTO t1 VALUES("up", ST_GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
834 INSERT INTO t1 VALUES("up2", ST_GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
835 INSERT INTO t1 VALUES("up3", ST_GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
836 INSERT INTO t1 VALUES("down", ST_GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
837 INSERT INTO t1 VALUES("down2", ST_GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
838 INSERT INTO t1 VALUES("down3", ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
839 INSERT INTO t1 VALUES("right", ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
840 INSERT INTO t1 VALUES("right2", ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
841 INSERT INTO t1 VALUES("right3", ST_GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
842 INSERT INTO t1 VALUES("left", ST_GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
843 INSERT INTO t1 VALUES("left2", ST_GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
844 INSERT INTO t1 VALUES("left3", ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))'));
845 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;
846 mbrcontains
847 center,small
848 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;
849 mbrdisjoint
850 down3,left3,right3,up3
851 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;
852 mbrequals
853 center
854 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;
855 mbrintersect
856 big,center,down,down2,left,left2,right,right2,small,up,up2
857 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;
858 mbroverlaps
859 down,left,right,up
860 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;
861 mbrtouches
862 down2,left2,right2,up2
863 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;
864 mbrwithin
865 big,center
866 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;
867 MBRcontains
868 center,small
869 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;
870 MBRdisjoint
871 down3,left3,right3,up3
872 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;
873 MBRequals
874 center
875 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;
876 intersects
877 big,center,down,down2,left,left2,right,right2,small,up,up2
878 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;
879 MBRoverlaps
880 down,left,right,up
881 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;
882 ST_touches
883 down2,left2,right2,up2
884 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;
885 MBRwithin
886 big,center
887 SET @vert1 = ST_GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
888 SET @horiz1 = ST_GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
889 SET @horiz2 = ST_GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
890 SET @horiz3 = ST_GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
891 SET @point1 = ST_GeomFromText('POLYGON ((0 0))');
892 SET @point2 = ST_GeomFromText('POLYGON ((-2 0))');
893 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @vert1) GROUP BY a1.name;
894 MBRoverlaps
895 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @horiz1) GROUP BY a1.name;
896 MBRoverlaps
897 SELECT MBROverlaps(@horiz1, @vert1) FROM DUAL;
898 MBROverlaps(@horiz1, @vert1)
899 0
900 SELECT MBROverlaps(@horiz1, @horiz2) FROM DUAL;
901 MBROverlaps(@horiz1, @horiz2)
902 1
903 SELECT MBROverlaps(@horiz1, @horiz3) FROM DUAL;
904 MBROverlaps(@horiz1, @horiz3)
905 0
906 SELECT MBROverlaps(@horiz1, @point1) FROM DUAL;
907 MBROverlaps(@horiz1, @point1)
908 0
909 SELECT MBROverlaps(@horiz1, @point2) FROM DUAL;
910 MBROverlaps(@horiz1, @point2)
911 0
912 DROP TABLE t1;
913 create table t1(f1 geometry, f2 polygon, f3 linestring);
914 select f1 from t1 union select f1 from t1;
915 f1
916 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)'));
917 select ST_AsText(f2),ST_AsText(f3) from t1;
918 ST_AsText(f2) ST_AsText(f3)
919 POLYGON((10 10,20 10,20 20,10 20,10 10)) LINESTRING(0 0,1 1,2 2)
920 select ST_AsText(a) from (select f2 as a from t1 union select f3 from t1) t;
921 ST_AsText(a)
922 POLYGON((10 10,20 10,20 20,10 20,10 10))
923 LINESTRING(0 0,1 1,2 2)
924 create table t2 as select f2 as a from t1 union select f3 from t1;
925 desc t2;
926 Field Type Null Key Default Extra
927 a geometry YES NULL
928 select ST_AsText(a) from t2;
929 ST_AsText(a)
930 POLYGON((10 10,20 10,20 20,10 20,10 10))
931 LINESTRING(0 0,1 1,2 2)
932 drop table t1, t2;
933 SELECT 1;
934 1
935 1
936 CREATE TABLE t1 (p POINT);
937 CREATE TABLE t2 (p POINT, INDEX(p));
938 INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
939 INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
940 SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
941 COUNT(*)
942 1
943 EXPLAIN
944 SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
945 id select_type table type possible_keys key key_len ref rows Extra
946 1 SIMPLE t2 ref p p 28 const 1 Using where
947 SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
948 COUNT(*)
949 1
950 INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(3 4)'));
951 INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(3 4)'));
952 EXPLAIN
953 SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
954 id select_type table type possible_keys key key_len ref rows Extra
955 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
956 SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
957 COUNT(*)
958 1
959 EXPLAIN
960 SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
961 id select_type table type possible_keys key key_len ref rows Extra
962 1 SIMPLE t2 ref p p 28 const # Using where
963 SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
964 COUNT(*)
965 1
966 EXPLAIN
967 SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
968 id select_type table type possible_keys key key_len ref rows Extra
969 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
970 SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
971 COUNT(*)
972 1
973 DROP TABLE t1, t2;
974 End of 5.0 tests
975 #
976 # Test for bug #58650 "Failing assertion: primary_key_no == -1 ||
977 # primary_key_no == 0".
978 #
979 drop table if exists t1;
980 # The minimal test case.
981 create table t1 (a int not null, b linestring not null, unique key b (b(12)), unique key a (a));
982 drop table t1;
983 # The original test case.
984 create table t1 (a int not null, b linestring not null, unique key b (b(12)));
985 create unique index a on t1(a);
986 drop table t1;
987 create table `t1` (`col002` point)engine=InnoDB;
988 insert into t1 values (),(),();
989 select min(`col002`) from t1 union select `col002` from t1;
990 min(`col002`)
991 NULL
992 drop table t1;
993 #
994 # Bug #47780: crash when comparing GIS items from subquery
995 #
996 CREATE TABLE t1(a INT, b MULTIPOLYGON);
997 INSERT INTO t1 VALUES
998 (0,
999 ST_GEOMFROMTEXT(
1000 '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)))'));
1001 # must not crash
1002 SELECT 1 FROM t1 WHERE a <> (SELECT ST_GEOMETRYCOLLECTIONFROMWKB(b) FROM t1);
1003 ERROR HY000: Illegal parameter data types int and geometry for operation '<>'
1004 DROP TABLE t1;
1005 #
1006 # Bug #49250 : spatial btree index corruption and crash
1007 # Part one : spatial syntax check
1008 #
1009 CREATE TABLE t1(col1 MULTIPOLYGON NOT NULL,
1010 SPATIAL INDEX USING BTREE (col1));
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 (col1))' at line 2
1012 CREATE TABLE t2(col1 MULTIPOLYGON NOT NULL);
1013 CREATE SPATIAL INDEX USING BTREE ON t2(col);
1014 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
1015 ALTER TABLE t2 ADD SPATIAL INDEX USING BTREE (col1);
1016 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
1017 DROP TABLE t2;
1018 End of 5.0 tests
1019 create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime);
1020 create view v1 as select * from t1;
1021 desc v1;
1022 Field Type Null Key Default Extra
1023 f1 tinyint(1) YES NULL
1024 f2 char(1) YES NULL
1025 f3 varchar(1) YES NULL
1026 f4 geometry YES NULL
1027 f5 datetime YES NULL
1028 drop view v1;
1029 drop table t1;
1030 SELECT MultiPoint(12345,'');
1031 ERROR HY000: Illegal parameter data type int for operation 'multipoint'
1032 SELECT 1 FROM (SELECT GREATEST(1,GEOMETRYCOLLECTION('00000','00000')) b FROM DUAL) AS d WHERE (LINESTRING(d.b));
1033 ERROR HY000: Illegal parameter data type varchar for operation 'geometrycollection'
1034 #
1035 # BUG#51875: crash when loading data into geometry function ST_polyfromwkb
1036 #
1037 SET @a=0x00000000030000000100000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440;
1038 SET @a=ST_POLYFROMWKB(@a);
1039 SET @a=0x00000000030000000000000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440;
1040 SET @a=ST_POLYFROMWKB(@a);
1041 create table t1(a polygon NOT NULL)engine=InnoDB;
1042 insert into t1 values (ST_geomfromtext("point(0 1)"));
1043 ERROR 22007: Incorrect POLYGON value: 'POINT(0 1)' for column `test`.`t1`.`a` at row 1
1044 insert into t1 values (ST_geomfromtext("point(1 0)"));
1045 ERROR 22007: Incorrect POLYGON value: 'POINT(1 0)' for column `test`.`t1`.`a` at row 1
1046 select * from (select polygon(t1.a) as p from t1 order by t1.a) d;
1047 p
1048 drop table t1;
1049 #
1050 # Test for bug #59888 "debug assertion when attempt to create spatial index
1051 # on char > 31 bytes".
1052 #
1053 create table t1(a char(32) not null) engine=InnoDB;
1054 create index i on t1 (a(5));
1055 drop table t1;
1056 End of 5.1 tests
1057 CREATE TABLE t0 (a BINARY(32) NOT NULL);
1058 CREATE UNIQUE INDEX i on t0 (a(10));
1059 INSERT INTO t0 VALUES (1);
1060 CREATE TABLE t1(
1061 col0 BINARY NOT NULL,
1062 col2 TIMESTAMP,
1063 INDEX i1 (col0)
1064 ) ENGINE=InnoDB;
1065 CREATE TABLE t2 (
1066 col0 BINARY NOT NULL,
1067 col2 TIMESTAMP
1068 ) ENGINE=InnoDB;
1069 CREATE INDEX idx0 ON t1(col0);
1070 Warnings:
1071 Note 1831 Duplicate index `idx0`. This is deprecated and will be disallowed in a future release
1072 CREATE TABLE t3 (
1073 col0 INTEGER NOT NULL,
1074 col1 POINT,
1075 col2 POINT
1076 );
1077 CREATE TABLE t4 (
1078 col0 INTEGER NOT NULL,
1079 col1 POINT,
1080 col2 LINESTRING,
1081 INDEX i1 (col1(5), col2(5))
1082 );
1083 DROP TABLE IF EXISTS t0, t1, t2, t3,t4;
1084 #
1085 # BUG#12414917 - ST_ISCLOSED() CRASHES ON 64-BIT BUILDS
1086 #
1087 SELECT ST_ISCLOSED(CONVERT(CONCAT(' ', 0x2), BINARY(20)));
1088 ST_ISCLOSED(CONVERT(CONCAT(' ', 0x2), BINARY(20)))
1089 -1
1090 #
1091 # BUG#12537203 - CRASH WHEN SUBSELECTING GLOBAL VARIABLES IN
1092 # GEOMETRY FUNCTION ARGUMENTS
1093 #
1094 SELECT GEOMETRYCOLLECTION((SELECT @@OLD));
1095 ERROR HY000: Illegal parameter data type bigint for operation 'geometrycollection'
1096 End of 5.1 tests
1097 #
1098 # Bug#11908153: CRASH AND/OR VALGRIND ERRORS IN FIELD_BLOB::GET_KEY_IMAGE
1099 #
1100 CREATE TABLE g1
1101 (a geometry NOT NULL, UNIQUE KEY i (a(151))) engine=InnoDB;
1102 INSERT INTO g1 VALUES (ST_geomfromtext('point(1 1)'));
1103 INSERT INTO g1 VALUES (ST_geomfromtext('point(1 2)'));
1104 FLUSH TABLES;
1105 SELECT 1 FROM g1
1106 FORCE INDEX(i) WHERE a = date_sub(now(), interval 2808.4 year_month)
1107 ;
1108 ERROR HY000: Illegal parameter data types geometry and datetime for operation '='
1109 DROP TABLE g1;
1110 #
1111 # Bug#13013970 MORE CRASHES IN FIELD_BLOB::GET_KEY_IMAGE
1112 #
1113 CREATE TABLE g1(a TEXT NOT NULL, KEY(a(255)));
1114 INSERT INTO g1 VALUES ('a'),('a');
1115 SELECT 1 FROM g1 WHERE a >= ANY
1116 (SELECT 1 FROM g1 WHERE a = ST_geomfromtext('') OR a) ;
1117 1
1118 Warnings:
1119 Warning 1292 Truncated incorrect DECIMAL value: 'a'
1120 Warning 1292 Truncated incorrect DECIMAL value: 'a'
1121 DROP TABLE g1;
1122 End of 5.5 tests
1123 DROP DATABASE IF EXISTS gis_ogs;
1124 CREATE DATABASE gis_ogs;
1125 USE gis_ogs;
1126 #
1127 # C.3.3.1 Geometry types and functions schema construction
1128 #
1129 CREATE TABLE lakes (
1130 fid INTEGER NOT NULL PRIMARY KEY,
1131 name CHARACTER VARYING(64),
1132 shore POLYGON);
1133 CREATE TABLE road_segments (
1134 fid INTEGER NOT NULL PRIMARY KEY,
1135 name CHARACTER VARYING(64),
1136 aliases CHARACTER VARYING(64),
1137 num_lanes INTEGER,
1138 centerline LINESTRING);
1139 CREATE TABLE divided_routes (
1140 fid INTEGER NOT NULL PRIMARY KEY,
1141 name CHARACTER VARYING(64),
1142 num_lanes INTEGER,
1143 centerlines MULTILINESTRING);
1144 CREATE TABLE forests (
1145 fid INTEGER NOT NULL PRIMARY KEY,
1146 name CHARACTER VARYING(64),
1147 boundary MULTIPOLYGON);
1148 CREATE TABLE bridges (
1149 fid INTEGER NOT NULL PRIMARY KEY,
1150 name CHARACTER VARYING(64),
1151 position POINT);
1152 CREATE TABLE streams (
1153 fid INTEGER NOT NULL PRIMARY KEY,
1154 name CHARACTER VARYING(64),
1155 centerline LINESTRING);
1156 CREATE TABLE buildings (
1157 fid INTEGER NOT NULL PRIMARY KEY,
1158 address CHARACTER VARYING(64),
1159 position POINT,
1160 footprint POLYGON);
1161 CREATE TABLE ponds (
1162 fid INTEGER NOT NULL PRIMARY KEY,
1163 name CHARACTER VARYING(64),
1164 type CHARACTER VARYING(64),
1165 shores MULTIPOLYGON);
1166 CREATE TABLE named_places (
1167 fid INTEGER NOT NULL PRIMARY KEY,
1168 name CHARACTER VARYING(64),
1169 boundary POLYGON);
1170 CREATE TABLE map_neatlines (
1171 fid INTEGER NOT NULL PRIMARY KEY,
1172 neatline POLYGON);
1173 #
1174 # C.3.3.2 Geometry types and functions schema data loading
1175 #
1176 # Lakes
1177 INSERT INTO lakes VALUES (
1178 101, 'BLUE LAKE',
1179 ST_PolyFromText(
1180 'POLYGON(
1181 (52 18,66 23,73 9,48 6,52 18),
1182 (59 18,67 18,67 13,59 13,59 18)
1183 )',
1184 101));
1185 # Road Segments
1186 INSERT INTO road_segments VALUES(102, 'Route 5', NULL, 2,
1187 ST_LineFromText(
1188 'LINESTRING( 0 18, 10 21, 16 23, 28 26, 44 31 )' ,101));
1189 INSERT INTO road_segments VALUES(103, 'Route 5', 'Main Street', 4,
1190 ST_LineFromText(
1191 'LINESTRING( 44 31, 56 34, 70 38 )' ,101));
1192 INSERT INTO road_segments VALUES(104, 'Route 5', NULL, 2,
1193 ST_LineFromText(
1194 'LINESTRING( 70 38, 72 48 )' ,101));
1195 INSERT INTO road_segments VALUES(105, 'Main Street', NULL, 4,
1196 ST_LineFromText(
1197 'LINESTRING( 70 38, 84 42 )' ,101));
1198 INSERT INTO road_segments VALUES(106, 'Dirt Road by Green Forest', NULL,
1199 1,
1200 ST_LineFromText(
1201 'LINESTRING( 28 26, 28 0 )',101));
1202 # DividedRoutes
1203 INSERT INTO divided_routes VALUES(119, 'Route 75', 4,
1204 ST_MLineFromText(
1205 'MULTILINESTRING((10 48,10 21,10 0),
1206 (16 0,16 23,16 48))', 101));
1207 # Forests
1208 INSERT INTO forests VALUES(109, 'Green Forest',
1209 ST_MPolyFromText(
1210 'MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),
1211 (52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))',
1212 101));
1213 # Bridges
1214 INSERT INTO bridges VALUES(110, 'Cam Bridge', ST_PointFromText(
1215 'POINT( 44 31 )', 101));
1216 # Streams
1217 INSERT INTO streams VALUES(111, 'Cam Stream',
1218 ST_LineFromText(
1219 'LINESTRING( 38 48, 44 41, 41 36, 44 31, 52 18 )', 101));
1220 INSERT INTO streams VALUES(112, NULL,
1221 ST_LineFromText(
1222 'LINESTRING( 76 0, 78 4, 73 9 )', 101));
1223 # Buildings
1224 INSERT INTO buildings VALUES(113, '123 Main Street',
1225 ST_PointFromText(
1226 'POINT( 52 30 )', 101),
1227 ST_PolyFromText(
1228 'POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )', 101));
1229 INSERT INTO buildings VALUES(114, '215 Main Street',
1230 ST_PointFromText(
1231 'POINT( 64 33 )', 101),
1232 ST_PolyFromText(
1233 'POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )', 101));
1234 # Ponds
1235 INSERT INTO ponds VALUES(120, NULL, 'Stock Pond',
1236 ST_MPolyFromText(
1237 'MULTIPOLYGON( ( ( 24 44, 22 42, 24 40, 24 44) ),
1238 ( ( 26 44, 26 40, 28 42, 26 44) ) )', 101));
1239 # Named Places
1240 INSERT INTO named_places VALUES(117, 'Ashton',
1241 ST_PolyFromText(
1242 'POLYGON( ( 62 48, 84 48, 84 30, 56 30, 56 34, 62 48) )', 101));
1243 INSERT INTO named_places VALUES(118, 'Goose Island',
1244 ST_PolyFromText(
1245 'POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )', 101));
1246 # Map Neatlines
1247 INSERT INTO map_neatlines VALUES(115,
1248 ST_PolyFromText(
1249 'POLYGON( ( 0 0, 0 48, 84 48, 84 0, 0 0 ) )', 101));
1250 #
1251 # C.3.3.3 Geometry types and functions schema test queries
1252
1253 # Conformance Item T6
1254 SELECT ST_Dimension(shore)
1255 FROM lakes
1256 WHERE name = 'Blue Lake';
1257 ST_Dimension(shore)
1258 2
1259 # Conformance Item T7
1260 SELECT ST_GeometryType(centerlines)
1261 FROM divided_routes
1262 WHERE name = 'Route 75';
1263 ST_GeometryType(centerlines)
1264 MULTILINESTRING
1265 # Conformance Item T8
1266 SELECT ST_AsText(boundary)
1267 FROM named_places
1268 WHERE name = 'Goose Island';
1269 ST_AsText(boundary)
1270 POLYGON((67 13,67 18,59 18,59 13,67 13))
1271 # Conformance Item T9
1272 SELECT ST_AsText(ST_PolyFromWKB(ST_AsBinary(boundary),101))
1273 FROM named_places
1274 WHERE name = 'Goose Island';
1275 ST_AsText(ST_PolyFromWKB(ST_AsBinary(boundary),101))
1276 POLYGON((67 13,67 18,59 18,59 13,67 13))
1277 # Conformance Item T10
1278 SELECT ST_SRID(boundary)
1279 FROM named_places
1280 WHERE name = 'Goose Island';
1281 ST_SRID(boundary)
1282 101
1283 # Conformance Item T11
1284 SELECT ST_IsEmpty(centerline)
1285 FROM road_segments
1286 WHERE name = 'Route 5'
1287 AND aliases = 'Main Street';
1288 ST_IsEmpty(centerline)
1289 0
1290 # Conformance Item T14
1291 SELECT ST_AsText(ST_Envelope(boundary))
1292 FROM named_places
1293 WHERE name = 'Goose Island';
1294 ST_AsText(ST_Envelope(boundary))
1295 POLYGON((59 13,67 13,67 18,59 18,59 13))
1296 # Conformance Item T15
1297 SELECT ST_X(position)
1298 FROM bridges
1299 WHERE name = 'Cam Bridge';
1300 ST_X(position)
1301 44
1302 # Conformance Item T16
1303 SELECT ST_Y(position)
1304 FROM bridges
1305 WHERE name = 'Cam Bridge';
1306 ST_Y(position)
1307 31
1308 # Conformance Item T17
1309 SELECT ST_AsText(ST_StartPoint(centerline))
1310 FROM road_segments
1311 WHERE fid = 102;
1312 ST_AsText(ST_StartPoint(centerline))
1313 POINT(0 18)
1314 # Conformance Item T18
1315 SELECT ST_AsText(ST_EndPoint(centerline))
1316 FROM road_segments
1317 WHERE fid = 102;
1318 ST_AsText(ST_EndPoint(centerline))
1319 POINT(44 31)
1320 # Conformance Item T21
1321 SELECT ST_Length(centerline)
1322 FROM road_segments
1323 WHERE fid = 106;
1324 ST_Length(centerline)
1325 26
1326 # Conformance Item T22
1327 SELECT ST_NumPoints(centerline)
1328 FROM road_segments
1329 WHERE fid = 102;
1330 ST_NumPoints(centerline)
1331 5
1332 # Conformance Item T23
1333 SELECT ST_AsText(ST_PointN(centerline, 1))
1334 FROM road_segments
1335 WHERE fid = 102;
1336 ST_AsText(ST_PointN(centerline, 1))
1337 POINT(0 18)
1338 # Conformance Item T24
1339 SELECT ST_AsText(ST_Centroid(boundary))
1340 FROM named_places
1341 WHERE name = 'Goose Island';
1342 ST_AsText(ST_Centroid(boundary))
1343 POINT(63 15.5)
1344 # Conformance Item T26
1345 SELECT ST_Area(boundary)
1346 FROM named_places
1347 WHERE name = 'Goose Island';
1348 ST_Area(boundary)
1349 40
1350 # Conformance Item T27
1351 SELECT ST_AsText(ST_ExteriorRing(shore))
1352 FROM lakes
1353 WHERE name = 'Blue Lake';
1354 ST_AsText(ST_ExteriorRing(shore))
1355 LINESTRING(52 18,66 23,73 9,48 6,52 18)
1356 # Conformance Item T28
1357 SELECT ST_NumInteriorRings(shore)
1358 FROM lakes
1359 WHERE name = 'Blue Lake';
1360 ST_NumInteriorRings(shore)
1361 1
1362 # Conformance Item T29
1363 SELECT ST_AsText(ST_InteriorRingN(shore, 1))
1364 FROM lakes
1365 WHERE name = 'Blue Lake';
1366 ST_AsText(ST_InteriorRingN(shore, 1))
1367 LINESTRING(59 18,67 18,67 13,59 13,59 18)
1368 # Conformance Item T30
1369 SELECT ST_NumGeometries(centerlines)
1370 FROM divided_routes
1371 WHERE name = 'Route 75';
1372 ST_NumGeometries(centerlines)
1373 2
1374 # Conformance Item T31
1375 SELECT ST_AsText(ST_GeometryN(centerlines, 2))
1376 FROM divided_routes
1377 WHERE name = 'Route 75';
1378 ST_AsText(ST_GeometryN(centerlines, 2))
1379 LINESTRING(16 0,16 23,16 48)
1380 # Conformance Item T32
1381 SELECT ST_IsClosed(centerlines)
1382 FROM divided_routes
1383 WHERE name = 'Route 75';
1384 ST_IsClosed(centerlines)
1385 0
1386 # Conformance Item T33
1387 SELECT ST_Length(centerlines)
1388 FROM divided_routes
1389 WHERE name = 'Route 75';
1390 ST_Length(centerlines)
1391 96
1392 # Conformance Item T34
1393 SELECT ST_AsText(ST_Centroid(shores))
1394 FROM ponds
1395 WHERE fid = 120;
1396 ST_AsText(ST_Centroid(shores))
1397 POINT(25 42)
1398 # Conformance Item T36
1399 SELECT ST_Area(shores)
1400 FROM ponds
1401 WHERE fid = 120;
1402 ST_Area(shores)
1403 8
1404 # Conformance Item T37
1405 SELECT ST_Equals(boundary,
1406 ST_PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',1))
1407 FROM named_places
1408 WHERE name = 'Goose Island';
1409 ST_Equals(boundary,
1410 ST_PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',1))
1411 1
1412 # Conformance Item T37
1413 # Geometry arguments' SRIDs must be identical.
1414 SELECT ST_Equals(boundary,
1415 ST_PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',101))
1416 FROM named_places
1417 WHERE name = 'Goose Island';
1418 ST_Equals(boundary,
1419 ST_PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',101))
1420 1
1421 # Conformance Item T38
1422 SELECT ST_Disjoint(centerlines, boundary)
1423 FROM divided_routes, named_places
1424 WHERE divided_routes.name = 'Route 75'
1425 AND named_places.name = 'Ashton';
1426 ST_Disjoint(centerlines, boundary)
1427 1
1428 # Conformance Item T39
1429 SELECT ST_Touches(centerline, shore)
1430 FROM streams, lakes
1431 WHERE streams.name = 'Cam Stream'
1432 AND lakes.name = 'Blue Lake';
1433 ST_Touches(centerline, shore)
1434 1
1435 # Conformance Item T42
1436 SELECT ST_Crosses(road_segments.centerline, divided_routes.centerlines)
1437 FROM road_segments, divided_routes
1438 WHERE road_segments.fid = 102
1439 AND divided_routes.name = 'Route 75';
1440 ST_Crosses(road_segments.centerline, divided_routes.centerlines)
1441 1
1442 # Conformance Item T43
1443 SELECT ST_Intersects(road_segments.centerline, divided_routes.centerlines)
1444 FROM road_segments, divided_routes
1445 WHERE road_segments.fid = 102
1446 AND divided_routes.name = 'Route 75';
1447 ST_Intersects(road_segments.centerline, divided_routes.centerlines)
1448 1
1449 # Conformance Item T44
1450 SELECT ST_Contains(forests.boundary, named_places.boundary)
1451 FROM forests, named_places
1452 WHERE forests.name = 'Green Forest'
1453 AND named_places.name = 'Ashton';
1454 ST_Contains(forests.boundary, named_places.boundary)
1455 0
1456 # Conformance Item T46
1457 SELECT ST_Distance(position, boundary)
1458 FROM bridges, named_places
1459 WHERE bridges.name = 'Cam Bridge'
1460 AND named_places.name = 'Ashton';
1461 ST_Distance(position, boundary)
1462 12
1463 # Conformance Item T48
1464 SELECT ST_AsText(ST_Difference(named_places.boundary, forests.boundary))
1465 FROM named_places, forests
1466 WHERE named_places.name = 'Ashton'
1467 AND forests.name = 'Green Forest';
1468 ST_AsText(ST_Difference(named_places.boundary, forests.boundary))
1469 POLYGON((56 34,62 48,84 48,84 42,56 34))
1470 SELECT ST_AsText(ST_Union(shore, boundary))
1471 FROM lakes, named_places
1472 WHERE lakes.name = 'Blue Lake'
1473 AND named_places.name = 'Goose Island';
1474 ST_AsText(ST_Union(shore, boundary))
1475 POLYGON((48 6,52 18,66 23,73 9,48 6))
1476 # Conformance Item T50
1477 SELECT ST_AsText(ST_SymDifference(shore, boundary))
1478 FROM lakes, named_places
1479 WHERE lakes.name = 'Blue Lake'
1480 AND named_places.name = 'Ashton';
1481 ST_AsText(ST_SymDifference(shore, boundary))
1482 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)))
1483 # Conformance Item T51
1484 SELECT count(*)
1485 FROM buildings, bridges
1486 WHERE ST_Contains(ST_Buffer(bridges.position, 15.0), buildings.footprint) = 1;
1487 count(*)
1488 1
1489 DROP DATABASE gis_ogs;
1490 #
1491 # Bug#13362660 ASSERTION `FIELD_POS < FIELD_COUNT' FAILED. IN PROTOCOL_TEXT::STORE
1492 #
1493 SELECT ST_Union('', ''), md5(1);
1494 ERROR HY000: Illegal parameter data type varchar for operation 'st_union'