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(11) NOT NULL AUTO_INCREMENT,
15 `g` point DEFAULT NULL,
16 PRIMARY KEY (`fid`)
17 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
18 SHOW FIELDS FROM gis_point;
19 Field Type Null Key Default Extra
20 fid int(11) 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(11) 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(11) 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(11) 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(11) 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(11) 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(11) NO PRI NULL auto_increment
45 g geometrycollection YES NULL
46 SHOW FIELDS FROM gis_geometry;
47 Field Type Null Key Default Extra
48 fid int(11) NO PRI NULL auto_increment
49 g geometry YES NULL
50 INSERT INTO gis_point VALUES
51 (101, PointFromText('POINT(10 10)')),
52 (102, PointFromText('POINT(20 10)')),
53 (103, PointFromText('POINT(20 20)')),
54 (104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
55 INSERT INTO gis_line VALUES
56 (105, LineFromText('LINESTRING(0 0,0 10,10 0)')),
57 (106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
58 (107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10))));
59 INSERT INTO gis_polygon VALUES
60 (108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
61 (109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
62 (110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))));
63 INSERT INTO gis_multi_point VALUES
64 (111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
65 (112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
66 (113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10))));
67 INSERT INTO gis_multi_line VALUES
68 (114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
69 (115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
70 (116, MLineFromWKB(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, 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, 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, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))));
75 INSERT INTO gis_geometrycollection VALUES
76 (120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
77 (121, GeometryFromWKB(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, AsText(g) FROM gis_point ORDER by fid;
86 fid 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, AsText(g) FROM gis_line ORDER by fid;
92 fid 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, AsText(g) FROM gis_polygon ORDER by fid;
97 fid 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, AsText(g) FROM gis_multi_point ORDER by fid;
102 fid 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, AsText(g) FROM gis_multi_line ORDER by fid;
107 fid 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, AsText(g) FROM gis_multi_polygon ORDER by fid;
112 fid 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, AsText(g) FROM gis_geometrycollection ORDER by fid;
117 fid 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, AsText(g) FROM gis_geometry ORDER by fid;
121 fid 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, Dimension(g) FROM gis_geometry ORDER by fid;
144 fid 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, GeometryType(g) FROM gis_geometry ORDER by fid;
167 fid 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 GEOMETRYCOLLECTION
188 121 GEOMETRYCOLLECTION
189 SELECT fid, IsEmpty(g) FROM gis_geometry ORDER by fid;
190 fid 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, AsText(Envelope(g)) FROM gis_geometry ORDER by fid;
213 fid AsText(Envelope(g))
214 101 POLYGON((10 10,10 10,10 10,10 10,10 10))
215 102 POLYGON((20 10,20 10,20 10,20 10,20 10))
216 103 POLYGON((20 20,20 20,20 20,20 20,20 20))
217 104 POLYGON((10 20,10 20,10 20,10 20,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 POLYGON((10 10,40 10,40 10,10 10,10 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 POLYGON((10 0,10 0,10 48,10 48,10 0))
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 extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from gis_geometry;
236 id select_type table type possible_keys key key_len ref rows filtered Extra
237 1 SIMPLE gis_geometry ALL NULL NULL NULL NULL 21 100.00
238 Warnings:
239 Note 1003 select st_dimension(`test`.`gis_geometry`.`g`) AS `Dimension(g)`,st_geometrytype(`test`.`gis_geometry`.`g`) AS `GeometryType(g)`,st_isempty(`test`.`gis_geometry`.`g`) AS `IsEmpty(g)`,st_astext(st_envelope(`test`.`gis_geometry`.`g`)) AS `AsText(Envelope(g))` from `test`.`gis_geometry`
240 SELECT fid, X(g) FROM gis_point ORDER by fid;
241 fid X(g)
242 101 10
243 102 20
244 103 20
245 104 10
246 SELECT fid, Y(g) FROM gis_point ORDER by fid;
247 fid Y(g)
248 101 10
249 102 10
250 103 20
251 104 20
252 explain extended select X(g),Y(g) FROM gis_point;
253 id select_type table type possible_keys key key_len ref rows filtered Extra
254 1 SIMPLE gis_point ALL NULL NULL NULL NULL 4 100.00
255 Warnings:
256 Note 1003 select st_x(`test`.`gis_point`.`g`) AS `X(g)`,st_y(`test`.`gis_point`.`g`) AS `Y(g)` from `test`.`gis_point`
257 SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid;
258 fid AsText(StartPoint(g))
259 105 POINT(0 0)
260 106 POINT(10 10)
261 107 POINT(10 10)
262 SELECT fid, AsText(EndPoint(g)) FROM gis_line ORDER by fid;
263 fid AsText(EndPoint(g))
264 105 POINT(10 0)
265 106 POINT(10 10)
266 107 POINT(40 10)
267 SELECT fid, GLength(g) FROM gis_line ORDER by fid;
268 fid GLength(g)
269 105 24.14213562373095
270 106 40
271 107 30
272 SELECT fid, NumPoints(g) FROM gis_line ORDER by fid;
273 fid NumPoints(g)
274 105 3
275 106 5
276 107 2
277 SELECT fid, AsText(PointN(g, 2)) FROM gis_line ORDER by fid;
278 fid AsText(PointN(g, 2))
279 105 POINT(0 10)
280 106 POINT(20 10)
281 107 POINT(40 10)
282 SELECT fid, IsClosed(g) FROM gis_line ORDER by fid;
283 fid IsClosed(g)
284 105 0
285 106 1
286 107 0
287 explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM gis_line;
288 id select_type table type possible_keys key key_len ref rows filtered Extra
289 1 SIMPLE gis_line ALL NULL NULL NULL NULL 3 100.00
290 Warnings:
291 Note 1003 select st_astext(st_startpoint(`test`.`gis_line`.`g`)) AS `AsText(StartPoint(g))`,st_astext(st_endpoint(`test`.`gis_line`.`g`)) AS `AsText(EndPoint(g))`,st_length(`test`.`gis_line`.`g`) AS `GLength(g)`,st_numpoints(`test`.`gis_line`.`g`) AS `NumPoints(g)`,st_astext(st_pointn(`test`.`gis_line`.`g`,2)) AS `AsText(PointN(g, 2))`,st_isclosed(`test`.`gis_line`.`g`) AS `IsClosed(g)` from `test`.`gis_line`
292 SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid;
293 fid AsText(Centroid(g))
294 108 POINT(15 15)
295 109 POINT(25.416666666666668 25.416666666666668)
296 110 POINT(20 10)
297 SELECT fid, Area(g) FROM gis_polygon ORDER by fid;
298 fid Area(g)
299 108 100
300 109 2400
301 110 450
302 SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon ORDER by fid;
303 fid AsText(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, NumInteriorRings(g) FROM gis_polygon ORDER by fid;
308 fid NumInteriorRings(g)
309 108 0
310 109 1
311 110 0
312 SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid;
313 fid AsText(InteriorRingN(g, 1))
314 108 NULL
315 109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
316 110 NULL
317 explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM gis_polygon;
318 id select_type table type possible_keys key key_len ref rows filtered Extra
319 1 SIMPLE gis_polygon ALL NULL NULL NULL NULL 3 100.00
320 Warnings:
321 Note 1003 select st_astext(st_centroid(`test`.`gis_polygon`.`g`)) AS `AsText(Centroid(g))`,st_area(`test`.`gis_polygon`.`g`) AS `Area(g)`,st_astext(st_exteriorring(`test`.`gis_polygon`.`g`)) AS `AsText(ExteriorRing(g))`,st_numinteriorrings(`test`.`gis_polygon`.`g`) AS `NumInteriorRings(g)`,st_astext(st_interiorringn(`test`.`gis_polygon`.`g`,1)) AS `AsText(InteriorRingN(g, 1))` from `test`.`gis_polygon`
322 SELECT fid, IsClosed(g) FROM gis_multi_line ORDER by fid;
323 fid IsClosed(g)
324 114 0
325 115 0
326 116 0
327 SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid;
328 fid AsText(Centroid(g))
329 117 POINT(57.98031067576927 17.854754130800433)
330 118 POINT(57.98031067576927 17.854754130800433)
331 119 POINT(2 2)
332 SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid;
333 fid Area(g)
334 117 1684.5
335 118 1684.5
336 119 4.5
337 SELECT fid, NumGeometries(g) from gis_multi_point ORDER by fid;
338 fid NumGeometries(g)
339 111 4
340 112 4
341 113 2
342 SELECT fid, NumGeometries(g) from gis_multi_line ORDER by fid;
343 fid NumGeometries(g)
344 114 2
345 115 1
346 116 2
347 SELECT fid, NumGeometries(g) from gis_multi_polygon ORDER by fid;
348 fid NumGeometries(g)
349 117 2
350 118 2
351 119 1
352 SELECT fid, NumGeometries(g) from gis_geometrycollection ORDER by fid;
353 fid NumGeometries(g)
354 120 2
355 121 2
356 explain extended SELECT fid, NumGeometries(g) from gis_multi_point;
357 id select_type table type possible_keys key key_len ref rows filtered Extra
358 1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00
359 Warnings:
360 Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,st_numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point`
361 SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid;
362 fid AsText(GeometryN(g, 2))
363 111 POINT(10 10)
364 112 POINT(11 11)
365 113 POINT(4 10)
366 SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line ORDER by fid;
367 fid AsText(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, AsText(GeometryN(g, 2)) from gis_multi_polygon ORDER by fid;
372 fid AsText(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, AsText(GeometryN(g, 2)) from gis_geometrycollection ORDER by fid;
377 fid AsText(GeometryN(g, 2))
378 120 LINESTRING(0 0,10 10)
379 121 LINESTRING(3 6,7 9)
380 SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection ORDER by fid;
381 fid AsText(GeometryN(g, 1))
382 120 POINT(0 0)
383 121 POINT(44 6)
384 explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
385 id select_type table type possible_keys key key_len ref rows filtered Extra
386 1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00
387 Warnings:
388 Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,st_astext(st_geometryn(`test`.`gis_multi_point`.`g`,2)) AS `AsText(GeometryN(g, 2))` from `test`.`gis_multi_point`
389 SELECT g1.fid as first, g2.fid as second,
390 Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
391 Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
392 Intersects(g1.g, g2.g) as i, 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 extended SELECT g1.fid as first, g2.fid as second,
400 Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
401 Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
402 Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
403 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
404 id select_type table type possible_keys key key_len ref rows filtered Extra
405 1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
406 1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
407 Warnings:
408 Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,st_within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,st_contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,mbroverlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,st_equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,mbrdisjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,st_touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,mbrintersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,st_crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`
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(11) 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 geometrycollection 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(11) 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 geometrycollection YES NULL
443 gm geometry YES NULL
444 fid int(11) 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 (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 HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t1`.`fl`
456 insert into t1 (fl) values (1.11);
457 ERROR HY000: Cannot cast 'decimal' as 'geometry' in assignment of `test`.`t1`.`fl`
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 (pointfromtext('point(1,1)'));
461 ERROR 23000: Column 'fl' cannot be null
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", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));
466 INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
467 INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
468 INSERT INTO t1 VALUES("up", GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
469 INSERT INTO t1 VALUES("up2", GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
470 INSERT INTO t1 VALUES("up3", GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
471 INSERT INTO t1 VALUES("down", GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
472 INSERT INTO t1 VALUES("down2", GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
473 INSERT INTO t1 VALUES("down3", GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
474 INSERT INTO t1 VALUES("right", GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
475 INSERT INTO t1 VALUES("right2", GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
476 INSERT INTO t1 VALUES("right3", GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
477 INSERT INTO t1 VALUES("left", GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
478 INSERT INTO t1 VALUES("left2", GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
479 INSERT INTO t1 VALUES("left3", 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 mbrequal FROM t1 a1 JOIN t1 a2 ON MBREqual( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
487 mbrequal
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 contains FROM t1 a1 JOIN t1 a2 ON Contains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
502 contains
503 center,small
504 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS disjoint FROM t1 a1 JOIN t1 a2 ON Disjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
505 disjoint
506 down3,left3,right3,up3
507 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS equals FROM t1 a1 JOIN t1 a2 ON Equals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
508 equals
509 center
510 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS intersects FROM t1 a1 JOIN t1 a2 ON Intersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
511 intersects
512 big,center,down,down2,left,left2,right,right2,small,up,up2
513 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS overlaps FROM t1 a1 JOIN t1 a2 ON Overlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
514 overlaps
515 down,left,right,up
516 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS touches FROM t1 a1 JOIN t1 a2 ON Touches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
517 touches
518 down2,left2,right2,up2
519 SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS within FROM t1 a1 JOIN t1 a2 ON Within( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name;
520 within
521 big,center
522 SET @vert1 = GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
523 SET @horiz1 = GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
524 SET @horiz2 = GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
525 SET @horiz3 = GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
526 SET @point1 = GeomFromText('POLYGON ((0 0))');
527 SET @point2 = GeomFromText('POLYGON ((-2 0))');
528 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @vert1) GROUP BY a1.name;
529 overlaps
530 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @horiz1) GROUP BY a1.name;
531 overlaps
532 SELECT Overlaps(@horiz1, @vert1) FROM DUAL;
533 Overlaps(@horiz1, @vert1)
534 0
535 SELECT Overlaps(@horiz1, @horiz2) FROM DUAL;
536 Overlaps(@horiz1, @horiz2)
537 1
538 SELECT Overlaps(@horiz1, @horiz3) FROM DUAL;
539 Overlaps(@horiz1, @horiz3)
540 0
541 SELECT Overlaps(@horiz1, @point1) FROM DUAL;
542 Overlaps(@horiz1, @point1)
543 0
544 SELECT Overlaps(@horiz1, @point2) FROM DUAL;
545 Overlaps(@horiz1, @point2)
546 0
547 DROP TABLE t1;
548 End of 5.0 tests
549 CREATE TABLE t1 (p POINT);
550 CREATE TABLE t2 (p POINT, INDEX(p));
551 INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
552 INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
553 SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
554 COUNT(*)
555 1
556 EXPLAIN
557 SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
558 id select_type table type possible_keys key key_len ref rows Extra
559 1 SIMPLE t2 ref p p 28 const 1 Using where
560 SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
561 COUNT(*)
562 1
563 INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(3 4)'));
564 INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(3 4)'));
565 EXPLAIN
566 SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
567 id select_type table type possible_keys key key_len ref rows Extra
568 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
569 SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
570 COUNT(*)
571 1
572 EXPLAIN
573 SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
574 id select_type table type possible_keys key key_len ref rows Extra
575 1 SIMPLE t2 ref p p 28 const # Using where
576 SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
577 COUNT(*)
578 1
579 EXPLAIN
580 SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
581 id select_type table type possible_keys key key_len ref rows Extra
582 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
583 SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
584 COUNT(*)
585 1
586 DROP TABLE t1, t2;
587 End of 5.0 tests
588 #
589 # Test for bug #58650 "Failing assertion: primary_key_no == -1 ||
590 # primary_key_no == 0".
591 #
592 drop table if exists t1;
593 # The minimal test case.
594 create table t1 (a int not null, b linestring not null, unique key b (b(12)), unique key a (a));
595 drop table t1;
596 # The original test case.
597 create table t1 (a int not null, b linestring not null, unique key b (b(12)));
598 create unique index a on t1(a);
599 drop table t1;
600 create table t1 (g geometry not null, spatial gk(g)) engine=innodb;
601 DROP TABLE t1;
602 create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb;
603 connect a,localhost,root,,;
604 connection a;
605 start transaction;
606 insert into t1 values(1, Point(1,1));
607 connect con1,localhost,root,,;
608 connection con1;
609 set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))');
610 set transaction isolation level read uncommitted;
611 select count(*) from t1 where ST_Within(t1.c2, @g1);
612 count(*)
613 1
614 disconnect con1;
615 connection a;
616 commit;
617 disconnect a;
618 connection default;
619 drop table t1;
620 #
621 # MDEV-38372 ASAN error in InnoDB on malformed WKB
622 #
623 create table t1 (
624 id int auto_increment primary key,
625 geom geometry not null,
626 spatial index (geom)
627 ) engine=innodb;
628 insert t1 (geom) values (X'000000000107000000FF000000');
629 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
630 drop table t1;
631 # End of 10.6 tests