Debian Codegraph @

mysql-test/suite/gis/t/spatial_indexing.test

raw
1
2 ############################################################################################
3 # Creating the spatial objects #
4 ############################################################################################
5
6 let $count=100;
7 --echo #Creating the spatial Geometry object
8 USE test;
9
10
11 ############################################################################################
12 # POINT: Check indexes for spatial data on same table with spatial index
13 ############################################################################################
14
15 CREATE TABLE gis_point_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g POINT
16 NOT NULL SRID 0,spatial index (g) ) engine=myisam;
17
18 let $i=$k;
19 while ($i)
20 {
21 --eval INSERT INTO gis_point_with_spatial_index VALUES ($i,ST_PointFromText('POINT(100 10)'))
22 dec $i;
23 }
24
25 select count(g) from gis_point_with_spatial_index where ST_AsText(g) like "%100 10%";
26 select ST_AsText(g) from gis_point_with_spatial_index where ST_AsText(g) like "%100 10%";
27
28 DROP TABLE gis_point_with_spatial_index;
29
30 ############################################################################################
31 # LINESTRING: Check indexes for spatial data on same table with spatial index
32 ############################################################################################
33
34 CREATE TABLE gis_line_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING
35 NOT NULL SRID 0, spatial index (g) ) engine=myisam;
36
37 let $i=$k;
38 while ($i)
39 {
40 --eval INSERT INTO gis_line_with_spatial_index VALUES ($i,ST_LineStringFromText('LINESTRING(100 10,10 100)'))
41 dec $i;
42 }
43
44 select count(g) from gis_line_with_spatial_index where ST_AsText(g) like "%100 10%";
45 select ST_AsText(g) from gis_line_with_spatial_index where ST_AsText(g) like "%100 10%";
46
47 DROP TABLE gis_line_with_spatial_index;
48
49 ############################################################################################
50 # POLYGON: Check indexes for spatial data on same table with spatial index
51 ############################################################################################
52
53 CREATE TABLE gis_polygon_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON
54 NOT NULL SRID 0, spatial index (g) ) engine=myisam;
55
56 let $i=$k;
57 while ($i)
58 {
59 --eval INSERT INTO gis_polygon_with_spatial_index VALUES ($i,ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))'))
60 dec $i;
61 }
62
63 select count(g) from gis_polygon_with_spatial_index where ST_AsText(g) like "%10 10%";
64 select ST_AsText(g) from gis_polygon_with_spatial_index where ST_AsText(g) like "%10 10%";
65
66 DROP TABLE gis_polygon_with_spatial_index;
67
68 ############################################################################################
69 # MULTIPOINT: Check indexes for spatial data on same table with spatial index
70 ############################################################################################
71
72 CREATE TABLE gis_multipoint_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT
73 NOT NULL SRID 0, spatial index (g) ) engine=myisam;
74
75 let $i=$k;
76 while ($i)
77 {
78 --eval INSERT INTO gis_multipoint_with_spatial_index VALUES ($i,ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)'))
79 dec $i;
80 }
81
82 select count(g) from gis_multipoint_with_spatial_index where ST_AsText(g) like "%10 10%";
83 select ST_AsText(g) from gis_multipoint_with_spatial_index where ST_AsText(g) like "%10 10%";
84
85 DROP TABLE gis_multipoint_with_spatial_index;
86
87 ############################################################################################
88 # MULTILINE: Check indexes for spatial data on same table with spatial index
89 ############################################################################################
90
91 CREATE TABLE gis_multiline_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING
92 NOT NULL SRID 0, spatial index (g) ) engine=myisam;
93
94 let $i=$k;
95 while ($i)
96 {
97 --eval INSERT INTO gis_multiline_with_spatial_index VALUES ($i,ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))'))
98 dec $i;
99 }
100
101 select count(g) from gis_multiline_with_spatial_index where ST_AsText(g) like "%10 0%";
102 select ST_AsText(g) from gis_multiline_with_spatial_index where ST_AsText(g) like "%10 0%";
103
104 DROP TABLE gis_multiline_with_spatial_index;
105
106 ############################################################################################
107 # MULTIPOLYGON: Check indexes for spatial data on same table with spatial index
108 ############################################################################################
109
110 CREATE TABLE gis_multipolygon_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON
111 NOT NULL SRID 0, spatial index (g) ) engine=myisam;
112
113 let $i=$k;
114 while ($i)
115 {
116 --eval INSERT INTO gis_multipolygon_with_spatial_index VALUES ($i,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)))'))
117 dec $i;
118 }
119
120 select count(g) from gis_multipolygon_with_spatial_index where ST_AsText(g) like "%10 10%";
121 select ST_AsText(g) from gis_multipolygon_with_spatial_index where ST_AsText(g) like "%10 10%";
122 select count(g) from gis_multipolygon_with_spatial_index where ST_AsText(g) like "%84 0%";
123 select ST_AsText(g) from gis_multipolygon_with_spatial_index where ST_AsText(g) like "%84 0%";
124
125 DROP TABLE gis_multipolygon_with_spatial_index;
126
127 ############################################################################################
128 # GEOMETRY COLLECTION: Check indexes for spatial data on same table with spatial index
129 ############################################################################################
130
131 CREATE TABLE gis_geometrycollection_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION
132 NOT NULL SRID 0, spatial index (g) ) engine=myisam;
133
134 let $i=$k;
135 while ($i)
136 {
137 --eval INSERT INTO gis_geometrycollection_with_spatial_index VALUES ($i,ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(10 10), LINESTRING(10 10,20 20))'))
138 dec $i;
139 }
140
141 select count(g) from gis_geometrycollection_with_spatial_index where ST_AsText(g) like "%10 10%";
142 select ST_AsText(g) from gis_geometrycollection_with_spatial_index where ST_AsText(g) like "%10 10%";
143
144 DROP TABLE gis_geometrycollection_with_spatial_index;
145
146 ############################################################################################
147 # GEOMETRY COLLECTION: Check indexes for spatial data on same table with spatial index
148 ############################################################################################
149
150 CREATE TABLE gis_geometry_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY
151 NOT NULL SRID 0, spatial index (g) ) engine=myisam;
152
153 let $i=$k;
154 while ($i)
155 {
156 --eval INSERT INTO gis_geometry_with_spatial_index VALUES ($i,ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(10 10), LINESTRING(10 10,20 20))'))
157 dec $i;
158 }
159
160 select count(g) from gis_geometry_with_spatial_index where ST_AsText(g) like "%10 10%";
161 select ST_AsText(g) from gis_geometry_with_spatial_index where ST_AsText(g) like "%10 10%";
162
163 DROP TABLE gis_geometry_with_spatial_index;
164
165 ############################################################################################
166 # Check indexes for spatial data and normal data on same table with spatial index
167 ############################################################################################
168 # Create some data for point table
169
170 CREATE TABLE gis_point_and_int_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING
171 NOT NULL SRID 0,spatial index (g) ) engine=myisam;
172
173 let $i=$k;
174 while ($i)
175 {
176 --eval INSERT INTO gis_point_and_int_with_spatial_index VALUES ($i,ST_PointFromText('POINT(100 10)'))
177 dec $i;
178 }
179
180 select count(g) from gis_point_and_int_with_spatial_index where ST_AsText(g) like "%100 10%";
181 select ST_AsText(g) from gis_point_and_int_with_spatial_index where ST_AsText(g) like "%100 10%";
182
183 DROP TABLE gis_point_and_int_with_spatial_index;
184
185 ############################################################################################
186 # Check indexes for spatial data and normal data on same table with spatial index
187 ############################################################################################
188 CREATE TABLE gis_point_and_int_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g POINT
189 NOT NULL SRID 0, a int, spatial index (g) ) engine=myisam;
190 # Inserting the values specific to the spatial objects #
191 --echo #INSERT valid entries to Point class
192 INSERT INTO gis_point_and_int_with_spatial_index VALUES
193 (101, ST_PointFromText('POINT(10 10)'),1),
194 (102, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))),21),
195 (103, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(100 20)'))),3),
196 (104, ST_GeomFromWKB(ST_AsWKB(ST_PointFromText('POINT(100 20)'))),3);
197
198 #check the index was created properly
199 SELECT COUNT(g) FROM gis_point_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%point%";
200 #the count should be 4
201
202 #drop the index
203 ALTER TABLE gis_point_and_int_with_spatial_index DROP INDEX g;
204 CREATE SPATIAL INDEX g ON gis_point_and_int_with_spatial_index (g);
205 #check the index was created properly
206 SELECT COUNT(g) FROM gis_point_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%point%";
207 #the count should be 4
208
209 #drop the spatial index
210 ALTER TABLE gis_point_and_int_with_spatial_index DROP INDEX g;
211 #create index on non-indexed non-spatial data
212 CREATE INDEX a ON gis_point_and_int_with_spatial_index (a) ;
213 #check for data consistency
214 select count(g) from gis_point_and_int_with_spatial_index where a like "%3%";
215 select ST_AsText(g) from gis_point_and_int_with_spatial_index where a like "%3%";
216
217 DROP TABLE gis_point_and_int_with_spatial_index;
218
219 ############################################################################################
220 #POINT: Check indexes for spatial data and normal data on same table with spatial index
221 ############################################################################################
222 CREATE TABLE gis_point_and_int_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g POINT
223 NOT NULL SRID 0, a int, index (a) ) engine=myisam;
224 # Inserting the values specific to the spatial objects #
225 --echo #INSERT valid entries to Point class
226 INSERT INTO gis_point_and_int_with_spatial_index VALUES
227 (101, ST_PointFromText('POINT(10 10)'),1),
228 (102, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))),21),
229 (103, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(100 20)'))),3),
230 (104, ST_GeomFromWKB(ST_AsWKB(ST_PointFromText('POINT(101 20)'))),3);
231
232 #check the index was created properly
233 select count(g) from gis_point_and_int_with_spatial_index where a like "%3%";
234 #the count should be 2
235 SELECT COUNT(a) FROM gis_point_and_int_with_spatial_index WHERE a LIKE "%3%";
236 #the count should be 2
237
238 #drop the non spatial index and recreate the spatial index
239 ALTER TABLE gis_point_and_int_with_spatial_index DROP INDEX a;
240 CREATE SPATIAL INDEX g ON gis_point_and_int_with_spatial_index (g);
241
242 #check the index was created properly
243 SELECT COUNT(g) FROM gis_point_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%point%";
244 #the count should be 2
245 SELECT COUNT(a) FROM gis_point_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%point%";
246 #the count should be 2
247
248 #create a spatial index on a non existing column
249 #CREATE SPATIAL INDEX g ON gis_point_and_int_with_spatial_index (b);
250
251 DROP TABLE gis_point_and_int_with_spatial_index ;
252
253 ############################################################################################
254 #LINESTRING: Check indexes for spatial data and normal data on same table with spatial index
255 ############################################################################################
256 CREATE TABLE gis_linestring_and_int_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING
257 NOT NULL SRID 0, a int, spatial index (g) ) engine=myisam;
258 # Inserting the values specific to the spatial objects #
259 --echo #INSERT valid entries to Point class
260 INSERT INTO gis_linestring_and_int_with_spatial_index VALUES
261 (201,ST_LineFromText('LINESTRING(0 0,0 10,10 0)'),1),
262 (202,ST_LineFromText('LINESTRING(1 1,1 10,10 20)'),2),
263 (203,ST_LineFromText('LINESTRING(1 1,1 10,10 20)'),2);
264 #(204, LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10)))),4);
265 #check the index was created properly
266 select count(g) from gis_linestring_and_int_with_spatial_index where ST_AsText(g) like "%0%";
267 select count(a) from gis_linestring_and_int_with_spatial_index where ST_AsText(g) like "%0%";
268 select count(a) from gis_linestring_and_int_with_spatial_index where ST_AsText(g) like "%10 10%";
269
270 #drop the spatial index
271 ALTER TABLE gis_linestring_and_int_with_spatial_index DROP INDEX g;
272 #create index on non-indexed non-spatial data
273 CREATE INDEX a ON gis_linestring_and_int_with_spatial_index (a) ;
274
275 #check the index created on a
276 SELECT COUNT(g) FROM gis_linestring_and_int_with_spatial_index WHERE a LIKE "%2%";
277
278 #drop the index
279 ALTER TABLE gis_linestring_and_int_with_spatial_index DROP INDEX a;
280 #create index on non-indexed spatial data
281 CREATE SPATIAL INDEX g ON gis_linestring_and_int_with_spatial_index (g) ;
282 #check the index created on a
283 SELECT COUNT(g) FROM gis_linestring_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%2%";
284
285 DROP TABLE gis_linestring_and_int_with_spatial_index ;
286
287 ############################################################################################
288 #MULTILINE: Check indexes for spatial data and normal data on same table with spatial index
289 ############################################################################################
290 CREATE TABLE gis_multilinestring_and_int_with_spatial_index (fid INTEGER PRIMARY KEY, g MULTILINESTRING
291 NOT NULL SRID 0, a int, spatial index (g) ) engine=myisam;
292 # Inserting the values specific to the spatial objects #
293 --echo #INSERT valid entries to Point class
294 INSERT INTO gis_multilinestring_and_int_with_spatial_index VALUES
295 (300,ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))'),1),
296 (301,ST_MultiLineStringFromText('MULTILINESTRING((130 48,180 21,10 90),(916 0,516 2373223,816 48))'),1),
297 (302,ST_MultiLineStringFromText('MULTILINESTRING((180 48,10 25891,160 0),(5416 0,19096 29093,19096 498))'),1);
298 #(204, LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10)))),4);
299 #check the index was created properly
300 select count(g) from gis_multilinestring_and_int_with_spatial_index where ST_AsText(g) like "%48%";
301 select count(a) from gis_multilinestring_and_int_with_spatial_index where ST_AsText(g) like "%48%";
302
303
304 #drop the spatial index
305 ALTER TABLE gis_multilinestring_and_int_with_spatial_index DROP INDEX g;
306 #create index on non-indexed non-spatial data
307 CREATE INDEX a ON gis_multilinestring_and_int_with_spatial_index (a) ;
308
309 #check the index created on a
310 SELECT COUNT(g) FROM gis_multilinestring_and_int_with_spatial_index WHERE a LIKE "%2%";
311
312 #drop the index
313 ALTER TABLE gis_multilinestring_and_int_with_spatial_index DROP INDEX a;
314 #create index on non-indexed spatial data
315 CREATE SPATIAL INDEX g ON gis_multilinestring_and_int_with_spatial_index (g) ;
316 #check the index created on a
317 SELECT COUNT(g) FROM gis_multilinestring_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%48%";
318
319 DROP TABLE gis_multilinestring_and_int_with_spatial_index ;
320
321 ############################################################################################
322 #POLYGON: Check indexes for spatial data and normal data on same table with spatial index
323 ############################################################################################
324 CREATE TABLE gis_polygon_and_int_with_spatial_index (fid INTEGER PRIMARY KEY, g POLYGON
325 NOT NULL SRID 0, a int, spatial index (g) ) engine=myisam;
326 # Inserting the values specific to the spatial objects #
327 --echo #INSERT valid entries to Point class
328 INSERT INTO gis_polygon_and_int_with_spatial_index VALUES
329 (300,ST_PolygonFromText('POLYGON((183670 10,2470 10,20 249380,10 254760,183670 10))'),12),
330 (301,ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))'),2),
331 (302,ST_PolygonFromText('POLYGON((110 10,320 310,2550 8520,1059 2590,110 10))'),2),
332 (303,ST_PolygonFromText('POLYGON((180 160,55620 5610,240 206560,10 285960,180 160))'),2);
333 #(204, LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10)))),4);
334 #check the index was created properly
335 select count(g) from gis_polygon_and_int_with_spatial_index where ST_AsText(g) like "%10 10%";
336 select count(a) from gis_polygon_and_int_with_spatial_index where ST_AsText(g) like "%10 10%";
337
338
339 #drop the spatial index
340 ALTER TABLE gis_polygon_and_int_with_spatial_index DROP INDEX g;
341 #create index on non-indexed non-spatial data
342 CREATE INDEX a ON gis_polygon_and_int_with_spatial_index (a) ;
343
344 #check the index created on a
345 SELECT COUNT(g) FROM gis_polygon_and_int_with_spatial_index WHERE a LIKE "%2%";
346
347 #drop the index
348 ALTER TABLE gis_polygon_and_int_with_spatial_index DROP INDEX a;
349 #create index on non-indexed spatial data
350 CREATE SPATIAL INDEX g ON gis_polygon_and_int_with_spatial_index (g) ;
351 #check the index created on a
352 SELECT COUNT(g) FROM gis_polygon_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%48%";
353
354 DROP TABLE gis_polygon_and_int_with_spatial_index ;
355
356 ############################################################################################
357 #MULTIPOLYGON: Check indexes for spatial data and normal data on same table with spatial index
358 ############################################################################################
359 CREATE TABLE gis_multipolygon_and_int_with_spatial_index (fid INTEGER PRIMARY KEY, g multiPOLYGON
360 NOT NULL SRID 0, a int, spatial index (g) ) engine=myisam;
361 # Inserting the values specific to the spatial objects #
362 --echo #INSERT valid entries to Point class
363 INSERT INTO gis_multipolygon_and_int_with_spatial_index VALUES
364 (1,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)))'),1),
365 (2,ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,864 0,8984 42,28 26),(2 18,66 348345723,73 9,48 6,2 18)),((519 18,67 18,348345767 13,59 13,519 18)))'),1),
366 (5,ST_MultiPolygonFromText('MULTIPOLYGON(((254397348 26,28 0,84 0,84 42,254397348 26),(52 18,634834576 23,73 9,48 34834576,52 18)),((59 18,67 18,67 13,59 13,59 18)))'),1),
367 (9,ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,834834574 42,28 26),(52 18,348345766 23,73 9,434834578 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))'),1);
368 #(204, LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10)))),4);
369 #check the index was created properly
370 select count(g) from gis_multipolygon_and_int_with_spatial_index where ST_AsText(g) like "%59 18%";
371 select count(a) from gis_multipolygon_and_int_with_spatial_index where ST_AsText(g) like "%59 18%";
372
373
374 #drop the spatial index
375 ALTER TABLE gis_multipolygon_and_int_with_spatial_index DROP INDEX g;
376 #create index on non-indexed non-spatial data
377 CREATE INDEX a ON gis_multipolygon_and_int_with_spatial_index (a) ;
378
379 #check the index created on a
380 SELECT COUNT(g) FROM gis_multipolygon_and_int_with_spatial_index WHERE a LIKE "%1%";
381
382 #drop the index
383 ALTER TABLE gis_multipolygon_and_int_with_spatial_index DROP INDEX a;
384 #create index on non-indexed spatial data
385 CREATE SPATIAL INDEX g ON gis_multipolygon_and_int_with_spatial_index (g) ;
386 #check the index created on a
387 SELECT COUNT(g) FROM gis_multipolygon_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%18%";
388
389 ############################################################################################
390 #Some erors around spatial index
391 ############################################################################################
392 DROP TABLE gis_multipolygon_and_int_with_spatial_index ;
393 --error ER_SPATIAL_UNIQUE_INDEX
394 CREATE TABLE gis_multipolygon_and_int_with_spatial_index (fid INTEGER NOT NULL, g MULTIPOLYGON PRIMARY KEY, a int) engine=myisam;
395
396 --error ER_SPATIAL_UNIQUE_INDEX
397 CREATE TABLE gis_multipolygon_and_int_with_spatial_index (fid INTEGER NOT NULL, g MULTIPOLYGON, a int, unique index (g) ) engine=myisam;
398
399 --error ER_SPATIAL_CANT_HAVE_NULL
400 CREATE TABLE gis_multipolygon_and_int_with_spatial_index (fid INTEGER NOT NULL, g MULTIPOLYGON, a int, spatial index (g) ) engine=myisam;
401
402 --error ER_SPATIAL_MUST_HAVE_GEOM_COL
403 CREATE TABLE gis_multipolygon_and_int_with_spatial_index (fid INTEGER NOT NULL, g MULTIPOLYGON, a int, spatial index (fid) ) engine=myisam;
404
405 CREATE TABLE gis_multipolygon_and_int_with_spatial_index (fid INTEGER PRIMARY KEY, g MULTIPOLYGON
406 NOT NULL SRID 0, a int, spatial index (g) ) engine=myisam;
407 # Inserting the values specific to the spatial objects #
408 --echo #INSERT valid entries to Point class
409 INSERT INTO gis_multipolygon_and_int_with_spatial_index VALUES
410 (1,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)))'),1),
411 (2,ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,864 0,8984 42,28 26),(2 18,66 348345723,73 9,48 6,2 18)),((519 18,67 18,348345767 13,59 13,519 18)))'),1),
412 (5,ST_MultiPolygonFromText('MULTIPOLYGON(((254397348 26,28 0,84 0,84 42,254397348 26),(52 18,634834576 23,73 9,48 34834576,52 18)),((59 18,67 18,67 13,59 13,59 18)))'),1),
413 (9,ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,834834574 42,28 26),(52 18,348345766 23,73 9,434834578 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))'),1);
414 #(204, LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10)))),4);
415 #check the index was created properly
416 select count(g) from gis_multipolygon_and_int_with_spatial_index where ST_AsText(g) like "%59 18%";
417 select count(a) from gis_multipolygon_and_int_with_spatial_index where ST_AsText(g) like "%59 18%";
418
419 #drop the spatial index
420 ALTER TABLE gis_multipolygon_and_int_with_spatial_index DROP INDEX g;
421 #create index on non-indexed non-spatial data
422 CREATE INDEX a ON gis_multipolygon_and_int_with_spatial_index (a) ;
423
424 #check the index created on a
425 SELECT COUNT(g) FROM gis_multipolygon_and_int_with_spatial_index WHERE a LIKE "%1%";
426
427 #drop the index
428 ALTER TABLE gis_multipolygon_and_int_with_spatial_index DROP INDEX a;
429 #create index on non-indexed spatial data
430 CREATE INDEX g ON gis_multipolygon_and_int_with_spatial_index (g) ;
431 DROP INDEX g ON gis_multipolygon_and_int_with_spatial_index;
432 #create index on non-indexed spatial data
433 --error ER_SPATIAL_MUST_HAVE_GEOM_COL
434 CREATE SPATIAL INDEX a ON gis_multipolygon_and_int_with_spatial_index (a) ;
435 #create index on non-indexed spatial data
436 CREATE SPATIAL INDEX g ON gis_multipolygon_and_int_with_spatial_index (g) ;
437 #check the index created on a
438 SELECT COUNT(g) FROM gis_multipolygon_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%18%";
439
440 DROP TABLE gis_multipolygon_and_int_with_spatial_index ;
441
442 --echo #
443 --echo # Bug #21087676 FORBID BUILDING NON-SPATIAL INDEX ON GEOMETRY COLUMNS
444 --echo #
445
446 --echo # Primary key on geometry column is not allowed.
447 --error ER_SPATIAL_UNIQUE_INDEX
448 CREATE TABLE t1 (g GEOMETRY SRID 0 PRIMARY KEY);
449 --error ER_SPATIAL_UNIQUE_INDEX
450 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL PRIMARY KEY);
451 CREATE TABLE t1 (g GEOMETRY SRID 0);
452 --error ER_SPATIAL_UNIQUE_INDEX
453 ALTER TABLE t1 ADD PRIMARY KEY (g);
454 DROP TABLE t1;
455
456 --echo # Unique index on geometry column is not allowed.
457 --error ER_SPATIAL_UNIQUE_INDEX
458 CREATE TABLE t1 (g GEOMETRY SRID 0 UNIQUE);
459 --error ER_SPATIAL_UNIQUE_INDEX
460 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL UNIQUE);
461 CREATE TABLE t1 (g GEOMETRY SRID 0);
462 --error ER_SPATIAL_UNIQUE_INDEX
463 CREATE UNIQUE INDEX g_idx ON t1(g);
464 --error ER_SPATIAL_UNIQUE_INDEX
465 ALTER TABLE t1 ADD UNIQUE INDEX (g);
466 DROP TABLE t1;
467 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
468 --error ER_SPATIAL_UNIQUE_INDEX
469 CREATE UNIQUE INDEX g_idx ON t1(g);
470 --error ER_SPATIAL_UNIQUE_INDEX
471 ALTER TABLE t1 ADD UNIQUE INDEX (g);
472 DROP TABLE t1;
473
474 --echo # Prefix index on geometry column is not allowed.
475 --error ER_WRONG_SUB_KEY
476 CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g(10)));
477 --error ER_WRONG_SUB_KEY
478 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g(10)));
479 CREATE TABLE t1 (g GEOMETRY SRID 0);
480 --error ER_WRONG_SUB_KEY
481 CREATE INDEX g_idx ON t1(g(10));
482 --error ER_WRONG_SUB_KEY
483 ALTER TABLE t1 ADD INDEX (g(10));
484 DROP TABLE t1;
485 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
486 --error ER_WRONG_SUB_KEY
487 CREATE INDEX g_idx ON t1(g(10));
488 --error ER_WRONG_SUB_KEY
489 ALTER TABLE t1 ADD INDEX (g(10));
490 DROP TABLE t1;
491
492 --echo # Multi-column index on geometry column is not allowed.
493 --error ER_TOO_MANY_KEY_PARTS
494 CREATE TABLE t1 (
495 g1 GEOMETRY SRID 0 NOT NULL,
496 g2 GEOMETRY SRID 0 NOT NULL,
497 INDEX (g1, g2)
498 );
499 --error ER_TOO_MANY_KEY_PARTS
500 CREATE TABLE t1 (
501 g1 GEOMETRY SRID 0,
502 g2 GEOMETRY SRID 0,
503 INDEX (g1, g2)
504 );
505 --error ER_TOO_MANY_KEY_PARTS
506 CREATE TABLE t1 (g GEOMETRY SRID 0, a INT, INDEX (g, a));
507 --error ER_TOO_MANY_KEY_PARTS
508 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, a INT, INDEX (g, a));
509 CREATE TABLE t1 (g1 GEOMETRY SRID 0, g2 GEOMETRY SRID 0, a INT);
510 --error ER_TOO_MANY_KEY_PARTS
511 CREATE INDEX g_idx ON t1(g1, g2);
512 --error ER_TOO_MANY_KEY_PARTS
513 CREATE INDEX g_idx ON t1(g1, a);
514 --error ER_TOO_MANY_KEY_PARTS
515 ALTER TABLE t1 ADD INDEX (g1, g2);
516 --error ER_TOO_MANY_KEY_PARTS
517 ALTER TABLE t1 ADD INDEX (g1, a);
518 DROP TABLE t1;
519 CREATE TABLE t1 (
520 g1 GEOMETRY SRID 0 NOT NULL,
521 g2 GEOMETRY SRID 0 NOT NULL,
522 a INT
523 );
524 --error ER_TOO_MANY_KEY_PARTS
525 CREATE INDEX g_idx ON t1(g1, g2);
526 --error ER_TOO_MANY_KEY_PARTS
527 CREATE INDEX g_idx ON t1(g1, a);
528 --error ER_TOO_MANY_KEY_PARTS
529 ALTER TABLE t1 ADD INDEX (g1, g2);
530 --error ER_TOO_MANY_KEY_PARTS
531 ALTER TABLE t1 ADD INDEX (g1, a);
532 DROP TABLE t1;
533
534 --echo # Explicit B-tree index on geometry column is not allowed.
535 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
536 CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g) USING BTREE);
537 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
538 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g) USING BTREE);
539 CREATE TABLE t1 (g GEOMETRY SRID 0);
540 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
541 CREATE INDEX g_idx ON t1(g) USING BTREE;
542 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
543 ALTER TABLE t1 ADD INDEX (g) USING BTREE;
544 DROP TABLE t1;
545 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
546 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
547 CREATE INDEX g_idx ON t1(g) USING BTREE;
548 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
549 ALTER TABLE t1 ADD INDEX (g) USING BTREE;
550 DROP TABLE t1;
551
552 --echo # Explicit hash index on geometry column is not allowed.
553 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
554 CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g) USING HASH);
555 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
556 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g) USING HASH);
557 CREATE TABLE t1 (g GEOMETRY SRID 0);
558 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
559 CREATE INDEX g_idx ON t1(g) USING HASH;
560 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
561 ALTER TABLE t1 ADD INDEX (g) USING HASH;
562 DROP TABLE t1;
563 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
564 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
565 CREATE INDEX g_idx ON t1(g) USING HASH;
566 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
567 ALTER TABLE t1 ADD INDEX (g) USING HASH;
568 DROP TABLE t1;
569
570 --echo # Explicit ASC index on geometry column is not allowed.
571 # Without SPATIAL keyword.
572 --error ER_WRONG_USAGE
573 CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g ASC));
574 --error ER_WRONG_USAGE
575 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g ASC));
576 CREATE TABLE t1 (g GEOMETRY SRID 0);
577 --error ER_WRONG_USAGE
578 CREATE INDEX g_idx ON t1(g ASC);
579 --error ER_WRONG_USAGE
580 ALTER TABLE t1 ADD INDEX (g ASC);
581 DROP TABLE t1;
582 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
583 --error ER_WRONG_USAGE
584 CREATE INDEX g_idx ON t1(g ASC);
585 --error ER_WRONG_USAGE
586 ALTER TABLE t1 ADD INDEX (g ASC);
587 DROP TABLE t1;
588 # With SPATIAL keyword.
589 --error ER_WRONG_USAGE
590 CREATE TABLE t1 (g GEOMETRY SRID 0, SPATIAL INDEX (g ASC));
591 --error ER_WRONG_USAGE
592 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, SPATIAL INDEX (g ASC));
593 CREATE TABLE t1 (g GEOMETRY SRID 0);
594 --error ER_WRONG_USAGE
595 CREATE SPATIAL INDEX g_idx ON t1(g ASC);
596 --error ER_WRONG_USAGE
597 ALTER TABLE t1 ADD SPATIAL INDEX (g ASC);
598 DROP TABLE t1;
599 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
600 --error ER_WRONG_USAGE
601 CREATE SPATIAL INDEX g_idx ON t1(g ASC);
602 --error ER_WRONG_USAGE
603 ALTER TABLE t1 ADD SPATIAL INDEX (g ASC);
604 DROP TABLE t1;
605
606 --echo # Explicit DESC index on geometry column is not allowed.
607 # Without SPATIAL keyword.
608 --error ER_WRONG_USAGE
609 CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g DESC));
610 --error ER_WRONG_USAGE
611 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g DESC));
612 CREATE TABLE t1 (g GEOMETRY SRID 0);
613 --error ER_WRONG_USAGE
614 CREATE INDEX g_idx ON t1(g DESC);
615 --error ER_WRONG_USAGE
616 ALTER TABLE t1 ADD INDEX (g DESC);
617 DROP TABLE t1;
618 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
619 --error ER_WRONG_USAGE
620 CREATE INDEX g_idx ON t1(g DESC);
621 --error ER_WRONG_USAGE
622 ALTER TABLE t1 ADD INDEX (g DESC);
623 DROP TABLE t1;
624 # With SPATIAL keyword.
625 --error ER_WRONG_USAGE
626 CREATE TABLE t1 (g GEOMETRY SRID 0, SPATIAL INDEX (g DESC));
627 --error ER_WRONG_USAGE
628 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, SPATIAL INDEX (g DESC));
629 CREATE TABLE t1 (g GEOMETRY SRID 0);
630 --error ER_WRONG_USAGE
631 CREATE SPATIAL INDEX g_idx ON t1(g DESC);
632 --error ER_WRONG_USAGE
633 ALTER TABLE t1 ADD SPATIAL INDEX (g DESC);
634 DROP TABLE t1;
635 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
636 --error ER_WRONG_USAGE
637 CREATE SPATIAL INDEX g_idx ON t1(g DESC);
638 --error ER_WRONG_USAGE
639 ALTER TABLE t1 ADD SPATIAL INDEX (g DESC);
640 DROP TABLE t1;
641
642 --echo # Fulltext index on geometry column is not allowed.
643 --error ER_BAD_FT_COLUMN
644 CREATE TABLE t1 (g GEOMETRY SRID 0, FULLTEXT INDEX (g));
645 --error ER_BAD_FT_COLUMN
646 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, FULLTEXT INDEX (g));
647 CREATE TABLE t1 (g GEOMETRY SRID 0);
648 --error ER_BAD_FT_COLUMN
649 CREATE FULLTEXT INDEX g_idx ON t1(g);
650 --error ER_BAD_FT_COLUMN
651 ALTER TABLE t1 ADD FULLTEXT INDEX (g);
652 DROP TABLE t1;
653 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
654 --error ER_BAD_FT_COLUMN
655 CREATE FULLTEXT INDEX g_idx ON t1(g);
656 --error ER_BAD_FT_COLUMN
657 ALTER TABLE t1 ADD FULLTEXT INDEX (g);
658 DROP TABLE t1;
659
660 --echo # Index on nullable geometry column is not allowed.
661 --error ER_SPATIAL_CANT_HAVE_NULL
662 CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g));
663 CREATE TABLE t1 (g GEOMETRY SRID 0);
664 --error ER_SPATIAL_CANT_HAVE_NULL
665 CREATE INDEX g_idx ON t1(g);
666 --error ER_SPATIAL_CANT_HAVE_NULL
667 ALTER TABLE t1 ADD INDEX (g);
668 DROP TABLE t1;
669 --error ER_SPATIAL_CANT_HAVE_NULL
670 CREATE TABLE t1 (g GEOMETRY SRID 0, SPATIAL INDEX (g));
671 CREATE TABLE t1 (g GEOMETRY SRID 0);
672 --error ER_SPATIAL_CANT_HAVE_NULL
673 CREATE SPATIAL INDEX g_idx ON t1(g);
674 --error ER_SPATIAL_CANT_HAVE_NULL
675 ALTER TABLE t1 ADD SPATIAL INDEX (g);
676 DROP TABLE t1;
677
678 --echo # Index on non-nullable geometry column is allowed.
679 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g));
680 SHOW CREATE TABLE t1;
681 DROP TABLE t1;
682 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
683 CREATE INDEX g_idx ON t1(g);
684 SHOW CREATE TABLE t1;
685 DROP TABLE t1;
686 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
687 ALTER TABLE t1 ADD INDEX (g), ALGORITHM=COPY;
688 SHOW CREATE TABLE t1;
689 DROP INDEX g ON t1;
690 ALTER TABLE t1 ADD INDEX (g), ALGORITHM=INPLACE;
691 SHOW CREATE TABLE t1;
692 DROP TABLE t1;
693 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, SPATIAL INDEX (g));
694 SHOW CREATE TABLE t1;
695 DROP TABLE t1;
696 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
697 CREATE SPATIAL INDEX g_idx ON t1(g);
698 SHOW CREATE TABLE t1;
699 DROP TABLE t1;
700 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
701 ALTER TABLE t1 ADD SPATIAL INDEX (g), ALGORITHM=COPY;
702 SHOW CREATE TABLE t1;
703 DROP INDEX g ON t1;
704 ALTER TABLE t1 ADD SPATIAL INDEX (g), ALGORITHM=INPLACE;
705 SHOW CREATE TABLE t1;
706 DROP TABLE t1;
707
708 --echo # Virtual generated geometry columns don't support spatial indexes.
709 CREATE TABLE t1 (x INTEGER,
710 y INTEGER,
711 g POINT AS (POINT(x, y)) VIRTUAL SRID 0 NOT NULL);
712 --error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
713 ALTER TABLE t1 ADD PRIMARY KEY (g);
714 --error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
715 CREATE INDEX g_idx ON t1(g);
716 --error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
717 CREATE INDEX g_idx ON t1(g(25));
718 --error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
719 CREATE INDEX g_idx ON t1(g) USING BTREE;
720 DROP TABLE t1;
721
722 --echo # Stored generated geometry columns follow the same rules as
723 --echo # non-generated columns.
724 CREATE TABLE t1 (x INTEGER,
725 y INTEGER,
726 g POINT AS (POINT(x, y)) STORED SRID 0 NOT NULL);
727 --error ER_SPATIAL_UNIQUE_INDEX
728 ALTER TABLE t1 ADD PRIMARY KEY (g);
729 CREATE INDEX g_idx ON t1(g);
730 DROP INDEX g_idx ON t1;
731 --error ER_WRONG_SUB_KEY
732 CREATE INDEX g_idx ON t1(g(25));
733 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
734 CREATE INDEX g_idx ON t1(g) USING BTREE;
735 DROP TABLE t1;
736
737 --echo #
738 --echo # Bug #27836608 INACCURATE ERROR THROWN WHILE CREATING SPATIAL INDEX OF
739 --echo # INVALID TYPE
740 --echo #
741
742 --echo # Explicit R-tree index on geometry column is allowed.
743 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g) USING RTREE);
744 DROP TABLE t1;
745 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
746 CREATE INDEX g_idx ON t1(g) USING RTREE;
747 DROP INDEX g_idx ON t1;
748 ALTER TABLE t1 ADD INDEX (g) USING RTREE;
749 DROP TABLE t1;
750
751 --echo # Explicit R-tree index on non-gometry column is not allowed.
752 --error ER_SPATIAL_MUST_HAVE_GEOM_COL
753 CREATE TABLE t1 (a INT NOT NULL, INDEX (a) USING RTREE);
754 CREATE TABLE t1 (a INT NOT NULL);
755 --error ER_SPATIAL_MUST_HAVE_GEOM_COL
756 CREATE INDEX a_idx ON t1(a) USING RTREE;
757 --error ER_SPATIAL_MUST_HAVE_GEOM_COL
758 ALTER TABLE t1 ADD INDEX (a) USING RTREE;
759 DROP TABLE t1;