Debian Codegraph @

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

raw
1 SET default_storage_engine=innodb;
2 DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
3 CREATE TABLE gis_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT);
4 CREATE TABLE gis_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g LINESTRING);
5 CREATE TABLE gis_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POLYGON);
6 CREATE TABLE gis_multi_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOINT);
7 CREATE TABLE gis_multi_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTILINESTRING);
8 CREATE TABLE gis_multi_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOLYGON);
9 CREATE TABLE gis_geometrycollection (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRYCOLLECTION);
10 CREATE TABLE gis_geometry (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRY);
11 SHOW CREATE TABLE gis_point;
12 Table Create Table
13 gis_point CREATE TABLE `gis_point` (
14 `fid` int NOT NULL AUTO_INCREMENT,
15 `g` point DEFAULT NULL,
16 PRIMARY KEY (`fid`)
17 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
18 SHOW FIELDS FROM gis_point;
19 Field Type Null Key Default Extra
20 fid int NO PRI NULL auto_increment
21 g point YES NULL
22 SHOW FIELDS FROM gis_line;
23 Field Type Null Key Default Extra
24 fid int NO PRI NULL auto_increment
25 g linestring YES NULL
26 SHOW FIELDS FROM gis_polygon;
27 Field Type Null Key Default Extra
28 fid int NO PRI NULL auto_increment
29 g polygon YES NULL
30 SHOW FIELDS FROM gis_multi_point;
31 Field Type Null Key Default Extra
32 fid int NO PRI NULL auto_increment
33 g multipoint YES NULL
34 SHOW FIELDS FROM gis_multi_line;
35 Field Type Null Key Default Extra
36 fid int NO PRI NULL auto_increment
37 g multilinestring YES NULL
38 SHOW FIELDS FROM gis_multi_polygon;
39 Field Type Null Key Default Extra
40 fid int NO PRI NULL auto_increment
41 g multipolygon YES NULL
42 SHOW FIELDS FROM gis_geometrycollection;
43 Field Type Null Key Default Extra
44 fid int NO PRI NULL auto_increment
45 g geomcollection YES NULL
46 SHOW FIELDS FROM gis_geometry;
47 Field Type Null Key Default Extra
48 fid int NO PRI NULL auto_increment
49 g geometry YES NULL
50 INSERT INTO gis_point VALUES
51 (101, ST_PointFromText('POINT(10 10)')),
52 (102, ST_PointFromText('POINT(20 10)')),
53 (103, ST_PointFromText('POINT(20 20)')),
54 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
55 INSERT INTO gis_line VALUES
56 (105, ST_LineFromText('LINESTRING(0 0,0 10,10 0)')),
57 (106, ST_LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
58 (107, ST_LineStringFromWKB(St_AsWKB(LineString(Point(10, 10), Point(40, 10)))));
59 INSERT INTO gis_polygon VALUES
60 (108, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
61 (109, ST_PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
62 (110, ST_PolyFromWKB(St_AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));
63 INSERT INTO gis_multi_point VALUES
64 (111, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
65 (112, ST_MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
66 (113, ST_MPointFromWKB(St_AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));
67 INSERT INTO gis_multi_line VALUES
68 (114, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
69 (115, ST_MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
70 (116, ST_MLineFromWKB(St_AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))));
71 INSERT INTO gis_multi_polygon VALUES
72 (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)))')),
73 (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)))')),
74 (119, ST_MPolyFromWKB(St_AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));
75 INSERT INTO gis_geometrycollection VALUES
76 (120, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
77 (121, ST_GeometryFromWKB(St_AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))));
78 INSERT into gis_geometry SELECT * FROM gis_point;
79 INSERT into gis_geometry SELECT * FROM gis_line;
80 INSERT into gis_geometry SELECT * FROM gis_polygon;
81 INSERT into gis_geometry SELECT * FROM gis_multi_point;
82 INSERT into gis_geometry SELECT * FROM gis_multi_line;
83 INSERT into gis_geometry SELECT * FROM gis_multi_polygon;
84 INSERT into gis_geometry SELECT * FROM gis_geometrycollection;
85 SELECT fid, ST_AsText(g) FROM gis_point ORDER by fid;
86 fid ST_AsText(g)
87 101 POINT(10 10)
88 102 POINT(20 10)
89 103 POINT(20 20)
90 104 POINT(10 20)
91 SELECT fid, ST_AsText(g) FROM gis_line ORDER by fid;
92 fid ST_AsText(g)
93 105 LINESTRING(0 0,0 10,10 0)
94 106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
95 107 LINESTRING(10 10,40 10)
96 SELECT fid, ST_AsText(g) FROM gis_polygon ORDER by fid;
97 fid ST_AsText(g)
98 108 POLYGON((10 10,20 10,20 20,10 20,10 10))
99 109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
100 110 POLYGON((0 0,30 0,30 30,0 0))
101 SELECT fid, ST_AsText(g) FROM gis_multi_point ORDER by fid;
102 fid ST_AsText(g)
103 111 MULTIPOINT((0 0),(10 10),(10 20),(20 20))
104 112 MULTIPOINT((1 1),(11 11),(11 21),(21 21))
105 113 MULTIPOINT((3 6),(4 10))
106 SELECT fid, ST_AsText(g) FROM gis_multi_line ORDER by fid;
107 fid ST_AsText(g)
108 114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
109 115 MULTILINESTRING((10 48,10 21,10 0))
110 116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
111 SELECT fid, ST_AsText(g) FROM gis_multi_polygon ORDER by fid;
112 fid ST_AsText(g)
113 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)))
114 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)))
115 119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
116 SELECT fid, ST_AsText(g) FROM gis_geometrycollection ORDER by fid;
117 fid ST_AsText(g)
118 120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
119 121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
120 SELECT fid, ST_AsText(g) FROM gis_geometry ORDER by fid;
121 fid ST_AsText(g)
122 101 POINT(10 10)
123 102 POINT(20 10)
124 103 POINT(20 20)
125 104 POINT(10 20)
126 105 LINESTRING(0 0,0 10,10 0)
127 106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
128 107 LINESTRING(10 10,40 10)
129 108 POLYGON((10 10,20 10,20 20,10 20,10 10))
130 109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
131 110 POLYGON((0 0,30 0,30 30,0 0))
132 111 MULTIPOINT((0 0),(10 10),(10 20),(20 20))
133 112 MULTIPOINT((1 1),(11 11),(11 21),(21 21))
134 113 MULTIPOINT((3 6),(4 10))
135 114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
136 115 MULTILINESTRING((10 48,10 21,10 0))
137 116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
138 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)))
139 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)))
140 119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
141 120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
142 121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
143 SELECT fid, ST_Dimension(g) FROM gis_geometry ORDER by fid;
144 fid ST_Dimension(g)
145 101 0
146 102 0
147 103 0
148 104 0
149 105 1
150 106 1
151 107 1
152 108 2
153 109 2
154 110 2
155 111 0
156 112 0
157 113 0
158 114 1
159 115 1
160 116 1
161 117 2
162 118 2
163 119 2
164 120 1
165 121 1
166 SELECT fid, ST_GeometryType(g) FROM gis_geometry ORDER by fid;
167 fid ST_GeometryType(g)
168 101 POINT
169 102 POINT
170 103 POINT
171 104 POINT
172 105 LINESTRING
173 106 LINESTRING
174 107 LINESTRING
175 108 POLYGON
176 109 POLYGON
177 110 POLYGON
178 111 MULTIPOINT
179 112 MULTIPOINT
180 113 MULTIPOINT
181 114 MULTILINESTRING
182 115 MULTILINESTRING
183 116 MULTILINESTRING
184 117 MULTIPOLYGON
185 118 MULTIPOLYGON
186 119 MULTIPOLYGON
187 120 GEOMCOLLECTION
188 121 GEOMCOLLECTION
189 SELECT fid, ST_IsEmpty(g) FROM gis_geometry ORDER by fid;
190 fid ST_IsEmpty(g)
191 101 0
192 102 0
193 103 0
194 104 0
195 105 0
196 106 0
197 107 0
198 108 0
199 109 0
200 110 0
201 111 0
202 112 0
203 113 0
204 114 0
205 115 0
206 116 0
207 117 0
208 118 0
209 119 0
210 120 0
211 121 0
212 SELECT fid, ST_AsText(ST_Envelope(g)) FROM gis_geometry ORDER by fid;
213 fid ST_AsText(ST_Envelope(g))
214 101 POINT(10 10)
215 102 POINT(20 10)
216 103 POINT(20 20)
217 104 POINT(10 20)
218 105 POLYGON((0 0,10 0,10 10,0 10,0 0))
219 106 POLYGON((10 10,20 10,20 20,10 20,10 10))
220 107 LINESTRING(10 10,40 10)
221 108 POLYGON((10 10,20 10,20 20,10 20,10 10))
222 109 POLYGON((0 0,50 0,50 50,0 50,0 0))
223 110 POLYGON((0 0,30 0,30 30,0 30,0 0))
224 111 POLYGON((0 0,20 0,20 20,0 20,0 0))
225 112 POLYGON((1 1,21 1,21 21,1 21,1 1))
226 113 POLYGON((3 6,4 6,4 10,3 10,3 6))
227 114 POLYGON((10 0,16 0,16 48,10 48,10 0))
228 115 LINESTRING(10 0,10 48)
229 116 POLYGON((1 2,21 2,21 8,1 8,1 2))
230 117 POLYGON((28 0,84 0,84 42,28 42,28 0))
231 118 POLYGON((28 0,84 0,84 42,28 42,28 0))
232 119 POLYGON((0 0,3 0,3 3,0 3,0 0))
233 120 POLYGON((0 0,10 0,10 10,0 10,0 0))
234 121 POLYGON((3 6,44 6,44 9,3 9,3 6))
235 explain select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry;
236 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
237 1 SIMPLE gis_geometry NULL ALL NULL NULL NULL NULL 21 100.00 NULL
238 Warnings:
239 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`
240 SELECT fid, ST_X(g) FROM gis_point ORDER by fid;
241 fid ST_X(g)
242 101 10
243 102 20
244 103 20
245 104 10
246 SELECT fid, ST_Y(g) FROM gis_point ORDER by fid;
247 fid ST_Y(g)
248 101 10
249 102 10
250 103 20
251 104 20
252 explain select ST_X(g),ST_Y(g) FROM gis_point;
253 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
254 1 SIMPLE gis_point NULL ALL NULL NULL NULL NULL 4 100.00 NULL
255 Warnings:
256 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`
257 SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line ORDER by fid;
258 fid ST_AsText(ST_StartPoint(g))
259 105 POINT(0 0)
260 106 POINT(10 10)
261 107 POINT(10 10)
262 SELECT fid, ST_AsText(ST_EndPoint(g)) FROM gis_line ORDER by fid;
263 fid ST_AsText(ST_EndPoint(g))
264 105 POINT(10 0)
265 106 POINT(10 10)
266 107 POINT(40 10)
267 SELECT fid, ST_Length(g) FROM gis_line ORDER by fid;
268 fid ST_Length(g)
269 105 24.14213562373095
270 106 40
271 107 30
272 SELECT fid, ST_NumPoints(g) FROM gis_line ORDER by fid;
273 fid ST_NumPoints(g)
274 105 3
275 106 5
276 107 2
277 SELECT fid, ST_AsText(ST_PointN(g, 2)) FROM gis_line ORDER by fid;
278 fid ST_AsText(ST_PointN(g, 2))
279 105 POINT(0 10)
280 106 POINT(20 10)
281 107 POINT(40 10)
282 SELECT fid, ST_IsClosed(g) FROM gis_line ORDER by fid;
283 fid ST_IsClosed(g)
284 105 0
285 106 1
286 107 0
287 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;
288 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
289 1 SIMPLE gis_line NULL ALL NULL NULL NULL NULL 3 100.00 NULL
290 Warnings:
291 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`
292 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon ORDER by fid;
293 fid ST_AsText(ST_Centroid(g))
294 108 POINT(15 15)
295 109 POINT(25.416666666666668 25.416666666666668)
296 110 POINT(20 10)
297 SELECT fid, ST_Area(g) FROM gis_polygon ORDER by fid;
298 fid ST_Area(g)
299 108 100
300 109 2400
301 110 450
302 SELECT fid, ST_AsText(ST_ExteriorRing(g)) FROM gis_polygon ORDER by fid;
303 fid ST_AsText(ST_ExteriorRing(g))
304 108 LINESTRING(10 10,20 10,20 20,10 20,10 10)
305 109 LINESTRING(0 0,50 0,50 50,0 50,0 0)
306 110 LINESTRING(0 0,30 0,30 30,0 0)
307 SELECT fid, ST_NumInteriorRings(g) FROM gis_polygon ORDER by fid;
308 fid ST_NumInteriorRings(g)
309 108 0
310 109 1
311 110 0
312 SELECT fid, ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid;
313 fid ST_AsText(ST_InteriorRingN(g, 1))
314 108 NULL
315 109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
316 110 NULL
317 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;
318 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
319 1 SIMPLE gis_polygon NULL ALL NULL NULL NULL NULL 3 100.00 NULL
320 Warnings:
321 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`
322 SELECT fid, ST_IsClosed(g) FROM gis_multi_line ORDER by fid;
323 fid ST_IsClosed(g)
324 114 0
325 115 0
326 116 0
327 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_multi_polygon ORDER by fid;
328 fid ST_AsText(ST_Centroid(g))
329 117 POINT(57.98031067576927 17.854754130800437)
330 118 POINT(57.98031067576927 17.854754130800437)
331 119 POINT(2 2)
332 SELECT fid, ST_Area(g) FROM gis_multi_polygon ORDER by fid;
333 fid ST_Area(g)
334 117 1684.5
335 118 1684.5
336 119 4.5
337 SELECT fid, ST_NumGeometries(g) from gis_multi_point ORDER by fid;
338 fid ST_NumGeometries(g)
339 111 4
340 112 4
341 113 2
342 SELECT fid, ST_NumGeometries(g) from gis_multi_line ORDER by fid;
343 fid ST_NumGeometries(g)
344 114 2
345 115 1
346 116 2
347 SELECT fid, ST_NumGeometries(g) from gis_multi_polygon ORDER by fid;
348 fid ST_NumGeometries(g)
349 117 2
350 118 2
351 119 1
352 SELECT fid, ST_NumGeometries(g) from gis_geometrycollection ORDER by fid;
353 fid ST_NumGeometries(g)
354 120 2
355 121 2
356 explain SELECT fid, ST_NumGeometries(g) from gis_multi_point;
357 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
358 1 SIMPLE gis_multi_point NULL ALL NULL NULL NULL NULL 3 100.00 NULL
359 Warnings:
360 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`
361 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point ORDER by fid;
362 fid ST_AsText(ST_GeometryN(g, 2))
363 111 POINT(10 10)
364 112 POINT(11 11)
365 113 POINT(4 10)
366 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_line ORDER by fid;
367 fid ST_AsText(ST_GeometryN(g, 2))
368 114 LINESTRING(16 0,16 23,16 48)
369 115 NULL
370 116 LINESTRING(2 5,5 8,21 7)
371 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_polygon ORDER by fid;
372 fid ST_AsText(ST_GeometryN(g, 2))
373 117 POLYGON((59 18,67 18,67 13,59 13,59 18))
374 118 POLYGON((59 18,67 18,67 13,59 13,59 18))
375 119 NULL
376 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_geometrycollection ORDER by fid;
377 fid ST_AsText(ST_GeometryN(g, 2))
378 120 LINESTRING(0 0,10 10)
379 121 LINESTRING(3 6,7 9)
380 SELECT fid, ST_AsText(ST_GeometryN(g, 1)) from gis_geometrycollection ORDER by fid;
381 fid ST_AsText(ST_GeometryN(g, 1))
382 120 POINT(0 0)
383 121 POINT(44 6)
384 explain SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
385 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
386 1 SIMPLE gis_multi_point NULL ALL NULL NULL NULL NULL 3 100.00 NULL
387 Warnings:
388 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`
389 SELECT g1.fid as first, g2.fid as second,
390 MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
391 MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
392 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
393 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
394 first second w c o e d t i r
395 120 120 1 1 0 1 0 0 1 0
396 120 121 0 0 1 0 0 0 1 0
397 121 120 0 0 1 0 0 0 1 0
398 121 121 1 1 0 1 0 0 1 0
399 explain SELECT g1.fid as first, g2.fid as second,
400 MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
401 MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
402 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
403 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
404 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
405 1 SIMPLE g1 NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
406 1 SIMPLE g2 NULL ALL NULL NULL NULL NULL 2 100.00 Using join buffer (hash join)
407 Warnings:
408 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`
409 DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
410 CREATE TABLE t1 (
411 a INTEGER PRIMARY KEY AUTO_INCREMENT,
412 gp point,
413 ln linestring,
414 pg polygon,
415 mp multipoint,
416 mln multilinestring,
417 mpg multipolygon,
418 gc geometrycollection,
419 gm geometry
420 );
421 SHOW FIELDS FROM t1;
422 Field Type Null Key Default Extra
423 a int NO PRI NULL auto_increment
424 gp point YES NULL
425 ln linestring YES NULL
426 pg polygon YES NULL
427 mp multipoint YES NULL
428 mln multilinestring YES NULL
429 mpg multipolygon YES NULL
430 gc geomcollection YES NULL
431 gm geometry YES NULL
432 ALTER TABLE t1 ADD fid INT;
433 SHOW FIELDS FROM t1;
434 Field Type Null Key Default Extra
435 a int NO PRI NULL auto_increment
436 gp point YES NULL
437 ln linestring YES NULL
438 pg polygon YES NULL
439 mp multipoint YES NULL
440 mln multilinestring YES NULL
441 mpg multipolygon YES NULL
442 gc geomcollection YES NULL
443 gm geometry YES NULL
444 fid int YES NULL
445 DROP TABLE t1;
446 create table t1 (pk integer primary key auto_increment, a geometry not null);
447 insert into t1 (a) values (ST_GeomFromText('Point(1 2)'));
448 insert into t1 (a) values ('Garbage');
449 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
450 insert IGNORE into t1 (a) values ('Garbage');
451 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
452 drop table t1;
453 create table t1 (pk integer primary key auto_increment, fl geometry not null);
454 insert into t1 (fl) values (1);
455 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
456 insert into t1 (fl) values (1.11);
457 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
458 insert into t1 (fl) values ("qwerty");
459 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
460 insert into t1 (fl) values (ST_pointfromtext('point(1,1)'));
461 ERROR 22023: Invalid GIS data provided to function st_pointfromtext.
462 drop table t1;
463 End of 4.1 tests
464 CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY);
465 INSERT INTO t1 VALUES("center", ST_GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));
466 INSERT INTO t1 VALUES("small", ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
467 INSERT INTO t1 VALUES("big", ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
468 INSERT INTO t1 VALUES("up", ST_GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
469 INSERT INTO t1 VALUES("up2", ST_GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
470 INSERT INTO t1 VALUES("up3", ST_GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
471 INSERT INTO t1 VALUES("down", ST_GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
472 INSERT INTO t1 VALUES("down2", ST_GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
473 INSERT INTO t1 VALUES("down3", ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
474 INSERT INTO t1 VALUES("right", ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
475 INSERT INTO t1 VALUES("right2", ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
476 INSERT INTO t1 VALUES("right3", ST_GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
477 INSERT INTO t1 VALUES("left", ST_GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
478 INSERT INTO t1 VALUES("left2", ST_GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
479 INSERT INTO t1 VALUES("left3", ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))'));
480 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;
481 mbrcontains
482 center,small
483 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;
484 mbrdisjoint
485 down3,left3,right3,up3
486 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;
487 mbrequals
488 center
489 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;
490 mbrintersect
491 big,center,down,down2,left,left2,right,right2,small,up,up2
492 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;
493 mbroverlaps
494 down,left,right,up
495 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;
496 mbrtouches
497 down2,left2,right2,up2
498 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;
499 mbrwithin
500 big,center
501 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;
502 MBRcontains
503 center,small
504 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;
505 MBRdisjoint
506 down3,left3,right3,up3
507 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;
508 MBRequals
509 center
510 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;
511 intersect
512 big,center,down,down2,left,left2,right,right2,small,up,up2
513 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;
514 MBRoverlaps
515 down,left,right,up
516 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;
517 ST_touches
518 down2,left2,right2,up2
519 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;
520 MBRwithin
521 big,center
522 SET @vert1 = ST_GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
523 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
524 SET @horiz1 = ST_GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
525 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
526 SET @horiz2 = ST_GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
527 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
528 SET @horiz3 = ST_GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
529 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
530 SET @point1 = ST_GeomFromText('POLYGON ((0 0))');
531 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
532 SET @point2 = ST_GeomFromText('POLYGON ((-2 0))');
533 ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
534 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @vert1) GROUP BY a1.name;
535 MBRoverlaps
536 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @horiz1) GROUP BY a1.name;
537 MBRoverlaps
538 SELECT MBROverlaps(@horiz1, @vert1) FROM DUAL;
539 MBROverlaps(@horiz1, @vert1)
540 NULL
541 SELECT MBROverlaps(@horiz1, @horiz2) FROM DUAL;
542 MBROverlaps(@horiz1, @horiz2)
543 NULL
544 SELECT MBROverlaps(@horiz1, @horiz3) FROM DUAL;
545 MBROverlaps(@horiz1, @horiz3)
546 NULL
547 SELECT MBROverlaps(@horiz1, @point1) FROM DUAL;
548 MBROverlaps(@horiz1, @point1)
549 NULL
550 SELECT MBROverlaps(@horiz1, @point2) FROM DUAL;
551 MBROverlaps(@horiz1, @point2)
552 NULL
553 DROP TABLE t1;
554 End of 5.0 tests
555 CREATE TABLE t1 (p POINT);
556 CREATE TABLE t2 (p POINT);
557 INSERT INTO t1 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
558 INSERT INTO t2 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
559 SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
560 COUNT(*)
561 1
562 EXPLAIN
563 SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
564 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
565 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
566 Warnings:
567 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t2` where (`test`.`t2`.`p` = <cache>(st_pointfromtext('POINT(1 2)')))
568 SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
569 COUNT(*)
570 1
571 INSERT INTO t1 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
572 INSERT INTO t2 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
573 EXPLAIN
574 SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
575 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
576 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
577 Warnings:
578 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`p` = <cache>(st_pointfromtext('POINT(1 2)')))
579 SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
580 COUNT(*)
581 2
582 EXPLAIN
583 SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
584 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
585 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
586 Warnings:
587 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t2` where (`test`.`t2`.`p` = <cache>(st_pointfromtext('POINT(1 2)')))
588 SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
589 COUNT(*)
590 2
591 EXPLAIN
592 SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
593 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
594 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
595 Warnings:
596 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t2` where (`test`.`t2`.`p` = <cache>(st_pointfromtext('POINT(1 2)')))
597 SELECT COUNT(*) FROM t2 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
598 COUNT(*)
599 2
600 DROP TABLE t1, t2;
601 End of 5.0 tests
602 #
603 # Test for bug #58650 "Failing assertion: primary_key_no == -1 ||
604 # primary_key_no == 0".
605 #
606 drop table if exists t1;
607 # The minimal test case.
608 create table t1 (a int not null, b linestring not null, unique key a (a));
609 drop table t1;
610 # The original test case.
611 create table t1 (a int not null, b linestring not null);
612 create unique index a on t1(a);
613 drop table t1;
614 create table t1 (g geometry not null SRID 0, spatial gk(g)) engine=innodb;
615 DROP TABLE t1;
616 create table t1 (c1 int, c2 geometry not null SRID 0, spatial index (c2))engine=innodb;
617 start transaction;
618 insert into t1 values(1, Point(1,1));
619 set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))');
620 set transaction isolation level read uncommitted;
621 select count(*) from t1 where ST_Within(t1.c2, @g1);
622 count(*)
623 1
624 commit;
625 drop table t1;