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 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)',0));
450 ST_SRID(ST_GeomFromText('LineString(1 1,2 2)',0))
451 0
452 explain SELECT ST_SRID(ST_GeomFromText('LineString(1 1,2 2)',0));
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)',0)) AS `ST_SRID(ST_GeomFromText('LineString(1 1,2 2)',0))`
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);
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);
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) ENGINE=InnoDB ;
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);
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=InnoDB;
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
746 ) ENGINE=InnoDB DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED;
747 Warnings:
748 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
749 CREATE TABLE t2 (
750 geomdata polygon NOT NULL
751 ) ENGINE=InnoDB DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED;
752 Warnings:
753 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
754 CREATE TABLE t3
755 select
756 ST_aswkb(ws.geomdata) AS geomdatawkb
757 from
758 t1 ws
759 union
760 select
761 ST_aswkb(ws.geomdata) AS geomdatawkb
762 from
763 t2 ws;
764 describe t3;
765 Field Type Null Key Default Extra
766 geomdatawkb longblob YES NULL
767 drop table t1;
768 drop table t2;
769 drop table t3;
770 create table t1(col1 geometry default null,col15 geometrycollection not
771 null)engine=InnoDB;
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 insert into t1 set col15 = ST_GeomFromText('POINT(6 5)');
775 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
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 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
791 DETERMINISTIC
792 return ST_GeomFromText("point(1 1)") utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_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 ERROR HY000: Spatial indexes can't be primary or unique indexes.
800 drop table t1;
801 create table t1(pt GEOMETRY);
802 alter table t1 add primary key pti(pt);
803 ERROR HY000: Spatial indexes can't be primary or unique indexes.
804 alter table t1 add primary key pti(pt(20));
805 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
806 drop table t1;
807 create table t1 select ST_GeomFromText('point(1 1)');
808 desc t1;
809 Field Type Null Key Default Extra
810 ST_GeomFromText('point(1 1)') geometry YES NULL
811 drop table t1;
812 create table t1 (g geometry not null);
813 insert into t1 values(default);
814 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
815 drop table t1;
816 CREATE TABLE t1 (a GEOMETRY);
817 CREATE VIEW v1 AS SELECT ST_GeomFromwkb(ST_ASBINARY(a)) FROM t1;
818 CREATE VIEW v2 AS SELECT a FROM t1;
819 DESCRIBE v1;
820 Field Type Null Key Default Extra
821 ST_GeomFromwkb(ST_ASBINARY(a)) geometry YES NULL
822 DESCRIBE v2;
823 Field Type Null Key Default Extra
824 a geometry YES NULL
825 DROP VIEW v1,v2;
826 DROP TABLE t1;
827 create table t1 (name VARCHAR(100), square GEOMETRY);
828 INSERT INTO t1 VALUES("center", ST_GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));
829 INSERT INTO t1 VALUES("small", ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
830 INSERT INTO t1 VALUES("big", ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
831 INSERT INTO t1 VALUES("up", ST_GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
832 INSERT INTO t1 VALUES("up2", ST_GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
833 INSERT INTO t1 VALUES("up3", ST_GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
834 INSERT INTO t1 VALUES("down", ST_GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
835 INSERT INTO t1 VALUES("down2", ST_GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
836 INSERT INTO t1 VALUES("down3", ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
837 INSERT INTO t1 VALUES("right", ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
838 INSERT INTO t1 VALUES("right2", ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
839 INSERT INTO t1 VALUES("right3", ST_GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
840 INSERT INTO t1 VALUES("left", ST_GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
841 INSERT INTO t1 VALUES("left2", ST_GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
842 INSERT INTO t1 VALUES("left3", ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))'));
843 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;
844 mbrcontains
845 center,small
846 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;
847 mbrdisjoint
848 down3,left3,right3,up3
849 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;
850 mbrequals
851 center
852 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;
853 mbrintersect
854 big,center,down,down2,left,left2,right,right2,small,up,up2
855 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;
856 mbroverlaps
857 down,left,right,up
858 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;
859 mbrtouches
860 down2,left2,right2,up2
861 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;
862 mbrwithin
863 big,center
864 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;
865 MBRcontains
866 center,small
867 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;
868 MBRdisjoint
869 down3,left3,right3,up3
870 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;
871 MBRequals
872 center
873 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;
874 intersect
875 big,center,down,down2,left,left2,right,right2,small,up,up2
876 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;
877 MBRoverlaps
878 down,left,right,up
879 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;
880 ST_touches
881 down2,left2,right2,up2
882 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;
883 MBRwithin
884 big,center
885 SET @vert1 = ST_GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
886 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
887 SET @horiz1 = ST_GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
888 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
889 SET @horiz2 = ST_GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
890 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
891 SET @horiz3 = ST_GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
892 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
893 SET @point1 = ST_GeomFromText('POLYGON ((0 0))');
894 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
895 SET @point2 = ST_GeomFromText('POLYGON ((-2 0))');
896 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
897 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @vert1) GROUP BY a1.name;
898 MBRoverlaps
899 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @horiz1) GROUP BY a1.name;
900 MBRoverlaps
901 SELECT MBROverlaps(@horiz1, @vert1) FROM DUAL;
902 MBROverlaps(@horiz1, @vert1)
903 NULL
904 SELECT MBROverlaps(@horiz1, @horiz2) FROM DUAL;
905 MBROverlaps(@horiz1, @horiz2)
906 NULL
907 SELECT MBROverlaps(@horiz1, @horiz3) FROM DUAL;
908 MBROverlaps(@horiz1, @horiz3)
909 NULL
910 SELECT MBROverlaps(@horiz1, @point1) FROM DUAL;
911 MBROverlaps(@horiz1, @point1)
912 NULL
913 SELECT MBROverlaps(@horiz1, @point2) FROM DUAL;
914 MBROverlaps(@horiz1, @point2)
915 NULL
916 DROP TABLE t1;
917 create table t1(f1 geometry, f2 polygon, f3 linestring);
918 select f1 from t1 union select f1 from t1;
919 f1
920 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)'));
921 select ST_AsText(f2),ST_AsText(f3) from t1;
922 ST_AsText(f2) ST_AsText(f3)
923 POLYGON((10 10,20 10,20 20,10 20,10 10)) LINESTRING(0 0,1 1,2 2)
924 select ST_AsText(a) from (select f2 as a from t1 union select f3 from t1) t;
925 ST_AsText(a)
926 POLYGON((10 10,20 10,20 20,10 20,10 10))
927 LINESTRING(0 0,1 1,2 2)
928 create table t2 as select f2 as a from t1 union select f3 from t1;
929 desc t2;
930 Field Type Null Key Default Extra
931 a geometry YES NULL
932 select ST_AsText(a) from t2;
933 ST_AsText(a)
934 POLYGON((10 10,20 10,20 20,10 20,10 10))
935 LINESTRING(0 0,1 1,2 2)
936 drop table t1, t2;
937 SELECT 1;
938 1
939 1
940 CREATE TABLE t1 (p POINT);
941 CREATE TABLE t2 (p POINT);
942 INSERT INTO t1 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
943 INSERT INTO t2 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
944 SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
945 COUNT(*)
946 1
947 EXPLAIN
948 SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
949 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
950 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
951 Warnings:
952 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t2` where (`test`.`t2`.`p` = <cache>(st_pointfromtext('POINT(1 2)')))
953 SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
954 COUNT(*)
955 1
956 INSERT INTO t1 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
957 INSERT INTO t2 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
958 EXPLAIN
959 SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
960 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
961 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
962 Warnings:
963 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`p` = <cache>(st_pointfromtext('POINT(1 2)')))
964 SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
965 COUNT(*)
966 2
967 EXPLAIN
968 SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
969 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
970 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
971 Warnings:
972 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t2` where (`test`.`t2`.`p` = <cache>(st_pointfromtext('POINT(1 2)')))
973 SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
974 COUNT(*)
975 2
976 EXPLAIN
977 SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
978 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
979 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
980 Warnings:
981 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t2` where (`test`.`t2`.`p` = <cache>(st_pointfromtext('POINT(1 2)')))
982 SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
983 COUNT(*)
984 2
985 DROP TABLE t1, t2;
986 End of 5.0 tests
987 #
988 # Test for bug #58650 "Failing assertion: primary_key_no == -1 ||
989 # primary_key_no == 0".
990 #
991 drop table if exists t1;
992 # The minimal test case.
993 create table t1 (a int not null, b linestring not null, unique key a (a));
994 drop table t1;
995 # The original test case.
996 create table t1 (a int not null, b linestring not null);
997 create unique index a on t1(a);
998 drop table t1;
999 create table `t1` (`col002` point)engine=InnoDB;
1000 insert into t1 values (),(),();
1001 select min(`col002`) from t1 union select `col002` from t1;
1002 ERROR HY000: Incorrect arguments to min
1003 drop table t1;
1004 #
1005 # Bug #47780: crash when comparing GIS items from subquery
1006 #
1007 CREATE TABLE t1(a INT, b MULTIPOLYGON);
1008 INSERT INTO t1 VALUES
1009 (0,
1010 ST_GEOMFROMTEXT(
1011 '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)))'));
1012 # must not crash
1013 SELECT 1 FROM t1 WHERE a <> (SELECT ST_GEOMETRYCOLLECTIONFROMWKB(ST_AsWKB(b)) FROM t1);
1014 1
1015 DROP TABLE t1;
1016 #
1017 # Bug #49250 : spatial btree index corruption and crash
1018 # Part one : spatial syntax check
1019 #
1020 CREATE TABLE t1(col1 MULTIPOLYGON NOT NULL,
1021 SPATIAL INDEX USING BTREE (col1));
1022 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
1023 CREATE TABLE t2(col1 MULTIPOLYGON NOT NULL);
1024 CREATE SPATIAL INDEX USING BTREE ON t2(col);
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 ON t2(col)' at line 1
1026 ALTER TABLE t2 ADD SPATIAL INDEX USING BTREE (col1);
1027 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
1028 DROP TABLE t2;
1029 End of 5.0 tests
1030 create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime);
1031 Warnings:
1032 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1033 create view v1 as select * from t1;
1034 desc v1;
1035 Field Type Null Key Default Extra
1036 f1 tinyint(1) YES NULL
1037 f2 char(1) YES NULL
1038 f3 varchar(1) YES NULL
1039 f4 geometry YES NULL
1040 f5 datetime YES NULL
1041 drop view v1;
1042 drop table t1;
1043 SELECT MultiPoint(12345,'');
1044 ERROR 22007: Illegal non geometric '12345' value found during parsing
1045 SELECT 1 FROM (SELECT GREATEST(1,GEOMETRYCOLLECTION('00000','00000')) b FROM DUAL) AS d WHERE (LINESTRING(d.b));
1046 ERROR 22007: Illegal non geometric ''00000'' value found during parsing
1047 #
1048 # BUG#51875: crash when loading data into geometry function ST_polyfromwkb
1049 #
1050 SET @a=0x00000000030000000100000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440;
1051 SET @a=ST_POLYFROMWKB(@a);
1052 ERROR 22023: Invalid GIS data provided to function st_polyfromwkb.
1053 SET @a=0x00000000030000000000000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440;
1054 SET @a=ST_POLYFROMWKB(@a);
1055 ERROR 22023: Invalid GIS data provided to function st_polyfromwkb.
1056 create table t1(a polygon NOT NULL)engine=InnoDB;
1057 insert into t1 values (ST_geomfromtext("point(0 1)"));
1058 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
1059 insert into t1 values (ST_geomfromtext("point(1 0)"));
1060 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
1061 select * from (select polygon(t1.a) as p from t1 order by t1.a) d;
1062 p
1063 drop table t1;
1064 #
1065 # Test for bug #59888 "debug assertion when attempt to create spatial index
1066 # on char > 31 bytes".
1067 #
1068 create table t1(a char(32) not null) engine=InnoDB;
1069 create index i on t1 (a(5));
1070 drop table t1;
1071 End of 5.1 tests
1072 CREATE TABLE t0 (a BINARY(32) NOT NULL);
1073 CREATE UNIQUE INDEX i on t0 (a(10));
1074 INSERT INTO t0 VALUES (1);
1075 CREATE TABLE t1(
1076 col0 BINARY NOT NULL,
1077 col2 TIMESTAMP,
1078 INDEX i1 (col0)
1079 ) ENGINE=InnoDB;
1080 CREATE TABLE t2 (
1081 col0 BINARY NOT NULL,
1082 col2 TIMESTAMP
1083 ) ENGINE=InnoDB;
1084 CREATE INDEX idx0 ON t1(col0);
1085 Warnings:
1086 Warning 1831 Duplicate index 'idx0' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
1087 CREATE TABLE t3 (
1088 col0 INTEGER NOT NULL,
1089 col1 POINT,
1090 col2 POINT
1091 );
1092 CREATE SPATIAL INDEX idx0 ON t2 (col1, col2);
1093 ERROR 42000: Too many key parts specified; max 1 parts allowed
1094 CREATE TABLE t4 (
1095 col0 INTEGER NOT NULL,
1096 col1 POINT,
1097 col2 LINESTRING,
1098 INDEX i1 (col1(5), col2(5))
1099 );
1100 ERROR 42000: Too many key parts specified; max 1 parts allowed
1101 DROP TABLE IF EXISTS t0, t1, t2, t3,t4;
1102 Warnings:
1103 Note 1051 Unknown table 'test.t4'
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))) charset latin1;
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 0));
1203 # Road Segments
1204 INSERT INTO road_segments VALUES(102, 'Route 5', NULL, 2,
1205 ST_LineFromText(
1206 'LINESTRING( 0 18, 10 21, 16 23, 28 26, 44 31 )' ,0));
1207 INSERT INTO road_segments VALUES(103, 'Route 5', 'Main Street', 4,
1208 ST_LineFromText(
1209 'LINESTRING( 44 31, 56 34, 70 38 )' ,0));
1210 INSERT INTO road_segments VALUES(104, 'Route 5', NULL, 2,
1211 ST_LineFromText(
1212 'LINESTRING( 70 38, 72 48 )' ,0));
1213 INSERT INTO road_segments VALUES(105, 'Main Street', NULL, 4,
1214 ST_LineFromText(
1215 'LINESTRING( 70 38, 84 42 )' ,0));
1216 INSERT INTO road_segments VALUES(106, 'Dirt Road by Green Forest', NULL,
1217 1,
1218 ST_LineFromText(
1219 'LINESTRING( 28 26, 28 0 )',0));
1220 # DividedRoutes
1221 INSERT INTO divided_routes VALUES(119, 'Route 75', 4,
1222 ST_MLineFromText(
1223 'MULTILINESTRING((10 48,10 21,10 0),
1224 (16 0,16 23,16 48))', 0));
1225 # Forests
1226 INSERT INTO forests VALUES(109, 'Green Forest',
1227 ST_MPolyFromText(
1228 'MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),
1229 (52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))',
1230 0));
1231 # Bridges
1232 INSERT INTO bridges VALUES(110, 'Cam Bridge', ST_PointFromText(
1233 'POINT( 44 31 )', 0));
1234 # Streams
1235 INSERT INTO streams VALUES(111, 'Cam Stream',
1236 ST_LineFromText(
1237 'LINESTRING( 38 48, 44 41, 41 36, 44 31, 52 18 )', 0));
1238 INSERT INTO streams VALUES(112, NULL,
1239 ST_LineFromText(
1240 'LINESTRING( 76 0, 78 4, 73 9 )', 0));
1241 # Buildings
1242 INSERT INTO buildings VALUES(113, '123 Main Street',
1243 ST_PointFromText(
1244 'POINT( 52 30 )', 0),
1245 ST_PolyFromText(
1246 'POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )', 0));
1247 INSERT INTO buildings VALUES(114, '215 Main Street',
1248 ST_PointFromText(
1249 'POINT( 64 33 )', 0),
1250 ST_PolyFromText(
1251 'POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )', 0));
1252 # Ponds
1253 INSERT INTO ponds VALUES(120, NULL, 'Stock Pond',
1254 ST_MPolyFromText(
1255 'MULTIPOLYGON( ( ( 24 44, 22 42, 24 40, 24 44) ),
1256 ( ( 26 44, 26 40, 28 42, 26 44) ) )', 0));
1257 # Named Places
1258 INSERT INTO named_places VALUES(117, 'Ashton',
1259 ST_PolyFromText(
1260 'POLYGON( ( 62 48, 84 48, 84 30, 56 30, 56 34, 62 48) )', 0));
1261 INSERT INTO named_places VALUES(118, 'Goose Island',
1262 ST_PolyFromText(
1263 'POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )', 0));
1264 # Map Neatlines
1265 INSERT INTO map_neatlines VALUES(115,
1266 ST_PolyFromText(
1267 'POLYGON( ( 0 0, 0 48, 84 48, 84 0, 0 0 ) )', 0));
1268 #
1269 # C.3.3.3 Geometry types and functions schema test queries
1270
1271 # Conformance Item T6
1272 SELECT ST_Dimension(shore)
1273 FROM lakes
1274 WHERE name = 'Blue Lake';
1275 ST_Dimension(shore)
1276 2
1277 # Conformance Item T7
1278 SELECT ST_GeometryType(centerlines)
1279 FROM divided_routes
1280 WHERE name = 'Route 75';
1281 ST_GeometryType(centerlines)
1282 MULTILINESTRING
1283 # Conformance Item T8
1284 SELECT ST_AsText(boundary)
1285 FROM named_places
1286 WHERE name = 'Goose Island';
1287 ST_AsText(boundary)
1288 POLYGON((67 13,67 18,59 18,59 13,67 13))
1289 # Conformance Item T9
1290 SELECT ST_AsText(ST_PolyFromWKB(ST_AsBinary(boundary),0))
1291 FROM named_places
1292 WHERE name = 'Goose Island';
1293 ST_AsText(ST_PolyFromWKB(ST_AsBinary(boundary),0))
1294 POLYGON((67 13,67 18,59 18,59 13,67 13))
1295 # Conformance Item T10
1296 SELECT ST_SRID(boundary)
1297 FROM named_places
1298 WHERE name = 'Goose Island';
1299 ST_SRID(boundary)
1300 0
1301 # Conformance Item T11
1302 SELECT ST_IsEmpty(centerline)
1303 FROM road_segments
1304 WHERE name = 'Route 5'
1305 AND aliases = 'Main Street';
1306 ST_IsEmpty(centerline)
1307 0
1308 # Conformance Item T14
1309 SELECT ST_AsText(ST_Envelope(boundary))
1310 FROM named_places
1311 WHERE name = 'Goose Island';
1312 ST_AsText(ST_Envelope(boundary))
1313 POLYGON((59 13,67 13,67 18,59 18,59 13))
1314 # Conformance Item T15
1315 SELECT ST_X(position)
1316 FROM bridges
1317 WHERE name = 'Cam Bridge';
1318 ST_X(position)
1319 44
1320 # Conformance Item T16
1321 SELECT ST_Y(position)
1322 FROM bridges
1323 WHERE name = 'Cam Bridge';
1324 ST_Y(position)
1325 31
1326 # Conformance Item T17
1327 SELECT ST_AsText(ST_StartPoint(centerline))
1328 FROM road_segments
1329 WHERE fid = 102;
1330 ST_AsText(ST_StartPoint(centerline))
1331 POINT(0 18)
1332 # Conformance Item T18
1333 SELECT ST_AsText(ST_EndPoint(centerline))
1334 FROM road_segments
1335 WHERE fid = 102;
1336 ST_AsText(ST_EndPoint(centerline))
1337 POINT(44 31)
1338 # Conformance Item T21
1339 SELECT ST_Length(centerline)
1340 FROM road_segments
1341 WHERE fid = 106;
1342 ST_Length(centerline)
1343 26
1344 # Conformance Item T22
1345 SELECT ST_NumPoints(centerline)
1346 FROM road_segments
1347 WHERE fid = 102;
1348 ST_NumPoints(centerline)
1349 5
1350 # Conformance Item T23
1351 SELECT ST_AsText(ST_PointN(centerline, 1))
1352 FROM road_segments
1353 WHERE fid = 102;
1354 ST_AsText(ST_PointN(centerline, 1))
1355 POINT(0 18)
1356 # Conformance Item T24
1357 SELECT ST_AsText(ST_Centroid(boundary))
1358 FROM named_places
1359 WHERE name = 'Goose Island';
1360 ST_AsText(ST_Centroid(boundary))
1361 POINT(63 15.5)
1362 # Conformance Item T26
1363 SELECT ST_Area(boundary)
1364 FROM named_places
1365 WHERE name = 'Goose Island';
1366 ST_Area(boundary)
1367 40
1368 # Conformance Item T27
1369 SELECT ST_AsText(ST_ExteriorRing(shore))
1370 FROM lakes
1371 WHERE name = 'Blue Lake';
1372 ST_AsText(ST_ExteriorRing(shore))
1373 LINESTRING(52 18,66 23,73 9,48 6,52 18)
1374 # Conformance Item T28
1375 SELECT ST_NumInteriorRings(shore)
1376 FROM lakes
1377 WHERE name = 'Blue Lake';
1378 ST_NumInteriorRings(shore)
1379 1
1380 # Conformance Item T29
1381 SELECT ST_AsText(ST_InteriorRingN(shore, 1))
1382 FROM lakes
1383 WHERE name = 'Blue Lake';
1384 ST_AsText(ST_InteriorRingN(shore, 1))
1385 LINESTRING(59 18,67 18,67 13,59 13,59 18)
1386 # Conformance Item T30
1387 SELECT ST_NumGeometries(centerlines)
1388 FROM divided_routes
1389 WHERE name = 'Route 75';
1390 ST_NumGeometries(centerlines)
1391 2
1392 # Conformance Item T31
1393 SELECT ST_AsText(ST_GeometryN(centerlines, 2))
1394 FROM divided_routes
1395 WHERE name = 'Route 75';
1396 ST_AsText(ST_GeometryN(centerlines, 2))
1397 LINESTRING(16 0,16 23,16 48)
1398 # Conformance Item T32
1399 SELECT ST_IsClosed(centerlines)
1400 FROM divided_routes
1401 WHERE name = 'Route 75';
1402 ST_IsClosed(centerlines)
1403 0
1404 # Conformance Item T33
1405 SELECT ST_Length(centerlines)
1406 FROM divided_routes
1407 WHERE name = 'Route 75';
1408 ST_Length(centerlines)
1409 96
1410 # Conformance Item T34
1411 SELECT ST_AsText(ST_Centroid(shores))
1412 FROM ponds
1413 WHERE fid = 120;
1414 ST_AsText(ST_Centroid(shores))
1415 POINT(25 42)
1416 # Conformance Item T36
1417 SELECT ST_Area(shores)
1418 FROM ponds
1419 WHERE fid = 120;
1420 ST_Area(shores)
1421 8
1422 # Conformance Item T37
1423 # Geometry arguments' SRIDs must be identical.
1424 SELECT ST_Equals(boundary,
1425 ST_PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',0))
1426 FROM named_places
1427 WHERE name = 'Goose Island';
1428 ST_Equals(boundary,
1429 ST_PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',0))
1430 1
1431 # Conformance Item T38
1432 SELECT ST_Disjoint(centerlines, boundary)
1433 FROM divided_routes, named_places
1434 WHERE divided_routes.name = 'Route 75'
1435 AND named_places.name = 'Ashton';
1436 ST_Disjoint(centerlines, boundary)
1437 1
1438 # Conformance Item T39
1439 SELECT ST_Touches(centerline, shore)
1440 FROM streams, lakes
1441 WHERE streams.name = 'Cam Stream'
1442 AND lakes.name = 'Blue Lake';
1443 ST_Touches(centerline, shore)
1444 1
1445 # Conformance Item T42
1446 SELECT ST_Crosses(road_segments.centerline, divided_routes.centerlines)
1447 FROM road_segments, divided_routes
1448 WHERE road_segments.fid = 102
1449 AND divided_routes.name = 'Route 75';
1450 ST_Crosses(road_segments.centerline, divided_routes.centerlines)
1451 1
1452 # Conformance Item T43
1453 SELECT ST_Intersects(road_segments.centerline, divided_routes.centerlines)
1454 FROM road_segments, divided_routes
1455 WHERE road_segments.fid = 102
1456 AND divided_routes.name = 'Route 75';
1457 ST_Intersects(road_segments.centerline, divided_routes.centerlines)
1458 1
1459 # Conformance Item T44
1460 SELECT ST_Contains(forests.boundary, named_places.boundary)
1461 FROM forests, named_places
1462 WHERE forests.name = 'Green Forest'
1463 AND named_places.name = 'Ashton';
1464 ST_Contains(forests.boundary, named_places.boundary)
1465 0
1466 # Conformance Item T46
1467 SELECT ST_Distance(position, boundary)
1468 FROM bridges, named_places
1469 WHERE bridges.name = 'Cam Bridge'
1470 AND named_places.name = 'Ashton';
1471 ST_Distance(position, boundary)
1472 12
1473 # Conformance Item T48
1474 SELECT ST_AsText(ST_Difference(named_places.boundary, forests.boundary))
1475 FROM named_places, forests
1476 WHERE named_places.name = 'Ashton'
1477 AND forests.name = 'Green Forest';
1478 ST_AsText(ST_Difference(named_places.boundary, forests.boundary))
1479 POLYGON((84 42,84 48,62 48,56 34,84 42))
1480 SELECT ST_AsText(ST_Union(shore, boundary))
1481 FROM lakes, named_places
1482 WHERE lakes.name = 'Blue Lake'
1483 AND named_places.name = 'Goose Island';
1484 ST_AsText(ST_Union(shore, boundary))
1485 POLYGON((52 18,48 6,73 9,66 23,52 18))
1486 # Conformance Item T50
1487 SELECT ST_AsText(ST_SymDifference(shore, boundary))
1488 FROM lakes, named_places
1489 WHERE lakes.name = 'Blue Lake'
1490 AND named_places.name = 'Ashton';
1491 ST_AsText(ST_SymDifference(shore, boundary))
1492 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)))
1493 # Conformance Item T51
1494 SELECT count(*)
1495 FROM buildings, bridges
1496 WHERE ST_Contains(ST_Buffer(bridges.position, 15.0), buildings.footprint) = 1;
1497 count(*)
1498 1
1499 DROP DATABASE gis_ogs;
1500 #
1501 # Bug#13362660 ASSERTION `FIELD_POS < FIELD_COUNT' FAILED. IN PROTOCOL_TEXT::STORE
1502 #
1503 SELECT ST_Union('', ''), md5(1);
1504 ERROR 22023: Invalid GIS data provided to function st_union.