Debian Codegraph @

mysql-test/suite/gis/r/spatial_indexing.result

raw
1 #Creating the spatial Geometry object
2 USE test;
3 CREATE TABLE gis_point_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g POINT
4 NOT NULL SRID 0,spatial index (g) ) engine=myisam;
5 select count(g) from gis_point_with_spatial_index where ST_AsText(g) like "%100 10%";
6 count(g)
7 0
8 select ST_AsText(g) from gis_point_with_spatial_index where ST_AsText(g) like "%100 10%";
9 ST_AsText(g)
10 DROP TABLE gis_point_with_spatial_index;
11 CREATE TABLE gis_line_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING
12 NOT NULL SRID 0, spatial index (g) ) engine=myisam;
13 select count(g) from gis_line_with_spatial_index where ST_AsText(g) like "%100 10%";
14 count(g)
15 0
16 select ST_AsText(g) from gis_line_with_spatial_index where ST_AsText(g) like "%100 10%";
17 ST_AsText(g)
18 DROP TABLE gis_line_with_spatial_index;
19 CREATE TABLE gis_polygon_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON
20 NOT NULL SRID 0, spatial index (g) ) engine=myisam;
21 select count(g) from gis_polygon_with_spatial_index where ST_AsText(g) like "%10 10%";
22 count(g)
23 0
24 select ST_AsText(g) from gis_polygon_with_spatial_index where ST_AsText(g) like "%10 10%";
25 ST_AsText(g)
26 DROP TABLE gis_polygon_with_spatial_index;
27 CREATE TABLE gis_multipoint_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT
28 NOT NULL SRID 0, spatial index (g) ) engine=myisam;
29 select count(g) from gis_multipoint_with_spatial_index where ST_AsText(g) like "%10 10%";
30 count(g)
31 0
32 select ST_AsText(g) from gis_multipoint_with_spatial_index where ST_AsText(g) like "%10 10%";
33 ST_AsText(g)
34 DROP TABLE gis_multipoint_with_spatial_index;
35 CREATE TABLE gis_multiline_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING
36 NOT NULL SRID 0, spatial index (g) ) engine=myisam;
37 select count(g) from gis_multiline_with_spatial_index where ST_AsText(g) like "%10 0%";
38 count(g)
39 0
40 select ST_AsText(g) from gis_multiline_with_spatial_index where ST_AsText(g) like "%10 0%";
41 ST_AsText(g)
42 DROP TABLE gis_multiline_with_spatial_index;
43 CREATE TABLE gis_multipolygon_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON
44 NOT NULL SRID 0, spatial index (g) ) engine=myisam;
45 select count(g) from gis_multipolygon_with_spatial_index where ST_AsText(g) like "%10 10%";
46 count(g)
47 0
48 select ST_AsText(g) from gis_multipolygon_with_spatial_index where ST_AsText(g) like "%10 10%";
49 ST_AsText(g)
50 select count(g) from gis_multipolygon_with_spatial_index where ST_AsText(g) like "%84 0%";
51 count(g)
52 0
53 select ST_AsText(g) from gis_multipolygon_with_spatial_index where ST_AsText(g) like "%84 0%";
54 ST_AsText(g)
55 DROP TABLE gis_multipolygon_with_spatial_index;
56 CREATE TABLE gis_geometrycollection_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION
57 NOT NULL SRID 0, spatial index (g) ) engine=myisam;
58 select count(g) from gis_geometrycollection_with_spatial_index where ST_AsText(g) like "%10 10%";
59 count(g)
60 0
61 select ST_AsText(g) from gis_geometrycollection_with_spatial_index where ST_AsText(g) like "%10 10%";
62 ST_AsText(g)
63 DROP TABLE gis_geometrycollection_with_spatial_index;
64 CREATE TABLE gis_geometry_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY
65 NOT NULL SRID 0, spatial index (g) ) engine=myisam;
66 select count(g) from gis_geometry_with_spatial_index where ST_AsText(g) like "%10 10%";
67 count(g)
68 0
69 select ST_AsText(g) from gis_geometry_with_spatial_index where ST_AsText(g) like "%10 10%";
70 ST_AsText(g)
71 DROP TABLE gis_geometry_with_spatial_index;
72 CREATE TABLE gis_point_and_int_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING
73 NOT NULL SRID 0,spatial index (g) ) engine=myisam;
74 select count(g) from gis_point_and_int_with_spatial_index where ST_AsText(g) like "%100 10%";
75 count(g)
76 0
77 select ST_AsText(g) from gis_point_and_int_with_spatial_index where ST_AsText(g) like "%100 10%";
78 ST_AsText(g)
79 DROP TABLE gis_point_and_int_with_spatial_index;
80 CREATE TABLE gis_point_and_int_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g POINT
81 NOT NULL SRID 0, a int, spatial index (g) ) engine=myisam;
82 #INSERT valid entries to Point class
83 INSERT INTO gis_point_and_int_with_spatial_index VALUES
84 (101, ST_PointFromText('POINT(10 10)'),1),
85 (102, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))),21),
86 (103, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(100 20)'))),3),
87 (104, ST_GeomFromWKB(ST_AsWKB(ST_PointFromText('POINT(100 20)'))),3);
88 SELECT COUNT(g) FROM gis_point_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%point%";
89 COUNT(g)
90 4
91 ALTER TABLE gis_point_and_int_with_spatial_index DROP INDEX g;
92 CREATE SPATIAL INDEX g ON gis_point_and_int_with_spatial_index (g);
93 SELECT COUNT(g) FROM gis_point_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%point%";
94 COUNT(g)
95 4
96 ALTER TABLE gis_point_and_int_with_spatial_index DROP INDEX g;
97 CREATE INDEX a ON gis_point_and_int_with_spatial_index (a) ;
98 select count(g) from gis_point_and_int_with_spatial_index where a like "%3%";
99 count(g)
100 2
101 select ST_AsText(g) from gis_point_and_int_with_spatial_index where a like "%3%";
102 ST_AsText(g)
103 POINT(100 20)
104 POINT(100 20)
105 DROP TABLE gis_point_and_int_with_spatial_index;
106 CREATE TABLE gis_point_and_int_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g POINT
107 NOT NULL SRID 0, a int, index (a) ) engine=myisam;
108 #INSERT valid entries to Point class
109 INSERT INTO gis_point_and_int_with_spatial_index VALUES
110 (101, ST_PointFromText('POINT(10 10)'),1),
111 (102, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))),21),
112 (103, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(100 20)'))),3),
113 (104, ST_GeomFromWKB(ST_AsWKB(ST_PointFromText('POINT(101 20)'))),3);
114 select count(g) from gis_point_and_int_with_spatial_index where a like "%3%";
115 count(g)
116 2
117 SELECT COUNT(a) FROM gis_point_and_int_with_spatial_index WHERE a LIKE "%3%";
118 COUNT(a)
119 2
120 ALTER TABLE gis_point_and_int_with_spatial_index DROP INDEX a;
121 CREATE SPATIAL INDEX g ON gis_point_and_int_with_spatial_index (g);
122 SELECT COUNT(g) FROM gis_point_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%point%";
123 COUNT(g)
124 4
125 SELECT COUNT(a) FROM gis_point_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%point%";
126 COUNT(a)
127 4
128 DROP TABLE gis_point_and_int_with_spatial_index ;
129 CREATE TABLE gis_linestring_and_int_with_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING
130 NOT NULL SRID 0, a int, spatial index (g) ) engine=myisam;
131 #INSERT valid entries to Point class
132 INSERT INTO gis_linestring_and_int_with_spatial_index VALUES
133 (201,ST_LineFromText('LINESTRING(0 0,0 10,10 0)'),1),
134 (202,ST_LineFromText('LINESTRING(1 1,1 10,10 20)'),2),
135 (203,ST_LineFromText('LINESTRING(1 1,1 10,10 20)'),2);
136 select count(g) from gis_linestring_and_int_with_spatial_index where ST_AsText(g) like "%0%";
137 count(g)
138 3
139 select count(a) from gis_linestring_and_int_with_spatial_index where ST_AsText(g) like "%0%";
140 count(a)
141 3
142 select count(a) from gis_linestring_and_int_with_spatial_index where ST_AsText(g) like "%10 10%";
143 count(a)
144 0
145 ALTER TABLE gis_linestring_and_int_with_spatial_index DROP INDEX g;
146 CREATE INDEX a ON gis_linestring_and_int_with_spatial_index (a) ;
147 SELECT COUNT(g) FROM gis_linestring_and_int_with_spatial_index WHERE a LIKE "%2%";
148 COUNT(g)
149 2
150 ALTER TABLE gis_linestring_and_int_with_spatial_index DROP INDEX a;
151 CREATE SPATIAL INDEX g ON gis_linestring_and_int_with_spatial_index (g) ;
152 SELECT COUNT(g) FROM gis_linestring_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%2%";
153 COUNT(g)
154 2
155 DROP TABLE gis_linestring_and_int_with_spatial_index ;
156 CREATE TABLE gis_multilinestring_and_int_with_spatial_index (fid INTEGER PRIMARY KEY, g MULTILINESTRING
157 NOT NULL SRID 0, a int, spatial index (g) ) engine=myisam;
158 #INSERT valid entries to Point class
159 INSERT INTO gis_multilinestring_and_int_with_spatial_index VALUES
160 (300,ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))'),1),
161 (301,ST_MultiLineStringFromText('MULTILINESTRING((130 48,180 21,10 90),(916 0,516 2373223,816 48))'),1),
162 (302,ST_MultiLineStringFromText('MULTILINESTRING((180 48,10 25891,160 0),(5416 0,19096 29093,19096 498))'),1);
163 select count(g) from gis_multilinestring_and_int_with_spatial_index where ST_AsText(g) like "%48%";
164 count(g)
165 3
166 select count(a) from gis_multilinestring_and_int_with_spatial_index where ST_AsText(g) like "%48%";
167 count(a)
168 3
169 ALTER TABLE gis_multilinestring_and_int_with_spatial_index DROP INDEX g;
170 CREATE INDEX a ON gis_multilinestring_and_int_with_spatial_index (a) ;
171 SELECT COUNT(g) FROM gis_multilinestring_and_int_with_spatial_index WHERE a LIKE "%2%";
172 COUNT(g)
173 0
174 ALTER TABLE gis_multilinestring_and_int_with_spatial_index DROP INDEX a;
175 CREATE SPATIAL INDEX g ON gis_multilinestring_and_int_with_spatial_index (g) ;
176 SELECT COUNT(g) FROM gis_multilinestring_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%48%";
177 COUNT(g)
178 3
179 DROP TABLE gis_multilinestring_and_int_with_spatial_index ;
180 CREATE TABLE gis_polygon_and_int_with_spatial_index (fid INTEGER PRIMARY KEY, g POLYGON
181 NOT NULL SRID 0, a int, spatial index (g) ) engine=myisam;
182 #INSERT valid entries to Point class
183 INSERT INTO gis_polygon_and_int_with_spatial_index VALUES
184 (300,ST_PolygonFromText('POLYGON((183670 10,2470 10,20 249380,10 254760,183670 10))'),12),
185 (301,ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))'),2),
186 (302,ST_PolygonFromText('POLYGON((110 10,320 310,2550 8520,1059 2590,110 10))'),2),
187 (303,ST_PolygonFromText('POLYGON((180 160,55620 5610,240 206560,10 285960,180 160))'),2);
188 select count(g) from gis_polygon_and_int_with_spatial_index where ST_AsText(g) like "%10 10%";
189 count(g)
190 2
191 select count(a) from gis_polygon_and_int_with_spatial_index where ST_AsText(g) like "%10 10%";
192 count(a)
193 2
194 ALTER TABLE gis_polygon_and_int_with_spatial_index DROP INDEX g;
195 CREATE INDEX a ON gis_polygon_and_int_with_spatial_index (a) ;
196 SELECT COUNT(g) FROM gis_polygon_and_int_with_spatial_index WHERE a LIKE "%2%";
197 COUNT(g)
198 4
199 ALTER TABLE gis_polygon_and_int_with_spatial_index DROP INDEX a;
200 CREATE SPATIAL INDEX g ON gis_polygon_and_int_with_spatial_index (g) ;
201 SELECT COUNT(g) FROM gis_polygon_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%48%";
202 COUNT(g)
203 0
204 DROP TABLE gis_polygon_and_int_with_spatial_index ;
205 CREATE TABLE gis_multipolygon_and_int_with_spatial_index (fid INTEGER PRIMARY KEY, g multiPOLYGON
206 NOT NULL SRID 0, a int, spatial index (g) ) engine=myisam;
207 #INSERT valid entries to Point class
208 INSERT INTO gis_multipolygon_and_int_with_spatial_index VALUES
209 (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),
210 (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),
211 (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),
212 (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);
213 select count(g) from gis_multipolygon_and_int_with_spatial_index where ST_AsText(g) like "%59 18%";
214 count(g)
215 3
216 select count(a) from gis_multipolygon_and_int_with_spatial_index where ST_AsText(g) like "%59 18%";
217 count(a)
218 3
219 ALTER TABLE gis_multipolygon_and_int_with_spatial_index DROP INDEX g;
220 CREATE INDEX a ON gis_multipolygon_and_int_with_spatial_index (a) ;
221 SELECT COUNT(g) FROM gis_multipolygon_and_int_with_spatial_index WHERE a LIKE "%1%";
222 COUNT(g)
223 4
224 ALTER TABLE gis_multipolygon_and_int_with_spatial_index DROP INDEX a;
225 CREATE SPATIAL INDEX g ON gis_multipolygon_and_int_with_spatial_index (g) ;
226 SELECT COUNT(g) FROM gis_multipolygon_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%18%";
227 COUNT(g)
228 4
229 DROP TABLE gis_multipolygon_and_int_with_spatial_index ;
230 CREATE TABLE gis_multipolygon_and_int_with_spatial_index (fid INTEGER NOT NULL, g MULTIPOLYGON PRIMARY KEY, a int) engine=myisam;
231 ERROR HY000: Spatial indexes can't be primary or unique indexes.
232 CREATE TABLE gis_multipolygon_and_int_with_spatial_index (fid INTEGER NOT NULL, g MULTIPOLYGON, a int, unique index (g) ) engine=myisam;
233 ERROR HY000: Spatial indexes can't be primary or unique indexes.
234 CREATE TABLE gis_multipolygon_and_int_with_spatial_index (fid INTEGER NOT NULL, g MULTIPOLYGON, a int, spatial index (g) ) engine=myisam;
235 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
236 CREATE TABLE gis_multipolygon_and_int_with_spatial_index (fid INTEGER NOT NULL, g MULTIPOLYGON, a int, spatial index (fid) ) engine=myisam;
237 ERROR 42000: A SPATIAL index may only contain a geometrical type column
238 CREATE TABLE gis_multipolygon_and_int_with_spatial_index (fid INTEGER PRIMARY KEY, g MULTIPOLYGON
239 NOT NULL SRID 0, a int, spatial index (g) ) engine=myisam;
240 #INSERT valid entries to Point class
241 INSERT INTO gis_multipolygon_and_int_with_spatial_index VALUES
242 (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),
243 (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),
244 (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),
245 (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);
246 select count(g) from gis_multipolygon_and_int_with_spatial_index where ST_AsText(g) like "%59 18%";
247 count(g)
248 3
249 select count(a) from gis_multipolygon_and_int_with_spatial_index where ST_AsText(g) like "%59 18%";
250 count(a)
251 3
252 ALTER TABLE gis_multipolygon_and_int_with_spatial_index DROP INDEX g;
253 CREATE INDEX a ON gis_multipolygon_and_int_with_spatial_index (a) ;
254 SELECT COUNT(g) FROM gis_multipolygon_and_int_with_spatial_index WHERE a LIKE "%1%";
255 COUNT(g)
256 4
257 ALTER TABLE gis_multipolygon_and_int_with_spatial_index DROP INDEX a;
258 CREATE INDEX g ON gis_multipolygon_and_int_with_spatial_index (g) ;
259 DROP INDEX g ON gis_multipolygon_and_int_with_spatial_index;
260 CREATE SPATIAL INDEX a ON gis_multipolygon_and_int_with_spatial_index (a) ;
261 ERROR 42000: A SPATIAL index may only contain a geometrical type column
262 CREATE SPATIAL INDEX g ON gis_multipolygon_and_int_with_spatial_index (g) ;
263 SELECT COUNT(g) FROM gis_multipolygon_and_int_with_spatial_index WHERE ST_AsText(g) LIKE "%18%";
264 COUNT(g)
265 4
266 DROP TABLE gis_multipolygon_and_int_with_spatial_index ;
267 #
268 # Bug #21087676 FORBID BUILDING NON-SPATIAL INDEX ON GEOMETRY COLUMNS
269 #
270 # Primary key on geometry column is not allowed.
271 CREATE TABLE t1 (g GEOMETRY SRID 0 PRIMARY KEY);
272 ERROR HY000: Spatial indexes can't be primary or unique indexes.
273 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL PRIMARY KEY);
274 ERROR HY000: Spatial indexes can't be primary or unique indexes.
275 CREATE TABLE t1 (g GEOMETRY SRID 0);
276 ALTER TABLE t1 ADD PRIMARY KEY (g);
277 ERROR HY000: Spatial indexes can't be primary or unique indexes.
278 DROP TABLE t1;
279 # Unique index on geometry column is not allowed.
280 CREATE TABLE t1 (g GEOMETRY SRID 0 UNIQUE);
281 ERROR HY000: Spatial indexes can't be primary or unique indexes.
282 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL UNIQUE);
283 ERROR HY000: Spatial indexes can't be primary or unique indexes.
284 CREATE TABLE t1 (g GEOMETRY SRID 0);
285 CREATE UNIQUE INDEX g_idx ON t1(g);
286 ERROR HY000: Spatial indexes can't be primary or unique indexes.
287 ALTER TABLE t1 ADD UNIQUE INDEX (g);
288 ERROR HY000: Spatial indexes can't be primary or unique indexes.
289 DROP TABLE t1;
290 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
291 CREATE UNIQUE INDEX g_idx ON t1(g);
292 ERROR HY000: Spatial indexes can't be primary or unique indexes.
293 ALTER TABLE t1 ADD UNIQUE INDEX (g);
294 ERROR HY000: Spatial indexes can't be primary or unique indexes.
295 DROP TABLE t1;
296 # Prefix index on geometry column is not allowed.
297 CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g(10)));
298 ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
299 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g(10)));
300 ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
301 CREATE TABLE t1 (g GEOMETRY SRID 0);
302 CREATE INDEX g_idx ON t1(g(10));
303 ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
304 ALTER TABLE t1 ADD INDEX (g(10));
305 ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
306 DROP TABLE t1;
307 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
308 CREATE INDEX g_idx ON t1(g(10));
309 ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
310 ALTER TABLE t1 ADD INDEX (g(10));
311 ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
312 DROP TABLE t1;
313 # Multi-column index on geometry column is not allowed.
314 CREATE TABLE t1 (
315 g1 GEOMETRY SRID 0 NOT NULL,
316 g2 GEOMETRY SRID 0 NOT NULL,
317 INDEX (g1, g2)
318 );
319 ERROR 42000: Too many key parts specified; max 1 parts allowed
320 CREATE TABLE t1 (
321 g1 GEOMETRY SRID 0,
322 g2 GEOMETRY SRID 0,
323 INDEX (g1, g2)
324 );
325 ERROR 42000: Too many key parts specified; max 1 parts allowed
326 CREATE TABLE t1 (g GEOMETRY SRID 0, a INT, INDEX (g, a));
327 ERROR 42000: Too many key parts specified; max 1 parts allowed
328 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, a INT, INDEX (g, a));
329 ERROR 42000: Too many key parts specified; max 1 parts allowed
330 CREATE TABLE t1 (g1 GEOMETRY SRID 0, g2 GEOMETRY SRID 0, a INT);
331 CREATE INDEX g_idx ON t1(g1, g2);
332 ERROR 42000: Too many key parts specified; max 1 parts allowed
333 CREATE INDEX g_idx ON t1(g1, a);
334 ERROR 42000: Too many key parts specified; max 1 parts allowed
335 ALTER TABLE t1 ADD INDEX (g1, g2);
336 ERROR 42000: Too many key parts specified; max 1 parts allowed
337 ALTER TABLE t1 ADD INDEX (g1, a);
338 ERROR 42000: Too many key parts specified; max 1 parts allowed
339 DROP TABLE t1;
340 CREATE TABLE t1 (
341 g1 GEOMETRY SRID 0 NOT NULL,
342 g2 GEOMETRY SRID 0 NOT NULL,
343 a INT
344 );
345 CREATE INDEX g_idx ON t1(g1, g2);
346 ERROR 42000: Too many key parts specified; max 1 parts allowed
347 CREATE INDEX g_idx ON t1(g1, a);
348 ERROR 42000: Too many key parts specified; max 1 parts allowed
349 ALTER TABLE t1 ADD INDEX (g1, g2);
350 ERROR 42000: Too many key parts specified; max 1 parts allowed
351 ALTER TABLE t1 ADD INDEX (g1, a);
352 ERROR 42000: Too many key parts specified; max 1 parts allowed
353 DROP TABLE t1;
354 # Explicit B-tree index on geometry column is not allowed.
355 CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g) USING BTREE);
356 ERROR HY000: The index type BTREE is not supported for spatial indexes.
357 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g) USING BTREE);
358 ERROR HY000: The index type BTREE is not supported for spatial indexes.
359 CREATE TABLE t1 (g GEOMETRY SRID 0);
360 CREATE INDEX g_idx ON t1(g) USING BTREE;
361 ERROR HY000: The index type BTREE is not supported for spatial indexes.
362 ALTER TABLE t1 ADD INDEX (g) USING BTREE;
363 ERROR HY000: The index type BTREE is not supported for spatial indexes.
364 DROP TABLE t1;
365 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
366 CREATE INDEX g_idx ON t1(g) USING BTREE;
367 ERROR HY000: The index type BTREE is not supported for spatial indexes.
368 ALTER TABLE t1 ADD INDEX (g) USING BTREE;
369 ERROR HY000: The index type BTREE is not supported for spatial indexes.
370 DROP TABLE t1;
371 # Explicit hash index on geometry column is not allowed.
372 CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g) USING HASH);
373 ERROR HY000: The index type HASH is not supported for spatial indexes.
374 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g) USING HASH);
375 ERROR HY000: The index type HASH is not supported for spatial indexes.
376 CREATE TABLE t1 (g GEOMETRY SRID 0);
377 CREATE INDEX g_idx ON t1(g) USING HASH;
378 ERROR HY000: The index type HASH is not supported for spatial indexes.
379 ALTER TABLE t1 ADD INDEX (g) USING HASH;
380 ERROR HY000: The index type HASH is not supported for spatial indexes.
381 DROP TABLE t1;
382 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
383 CREATE INDEX g_idx ON t1(g) USING HASH;
384 ERROR HY000: The index type HASH is not supported for spatial indexes.
385 ALTER TABLE t1 ADD INDEX (g) USING HASH;
386 ERROR HY000: The index type HASH is not supported for spatial indexes.
387 DROP TABLE t1;
388 # Explicit ASC index on geometry column is not allowed.
389 CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g ASC));
390 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
391 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g ASC));
392 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
393 CREATE TABLE t1 (g GEOMETRY SRID 0);
394 CREATE INDEX g_idx ON t1(g ASC);
395 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
396 ALTER TABLE t1 ADD INDEX (g ASC);
397 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
398 DROP TABLE t1;
399 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
400 CREATE INDEX g_idx ON t1(g ASC);
401 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
402 ALTER TABLE t1 ADD INDEX (g ASC);
403 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
404 DROP TABLE t1;
405 CREATE TABLE t1 (g GEOMETRY SRID 0, SPATIAL INDEX (g ASC));
406 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
407 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, SPATIAL INDEX (g ASC));
408 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
409 CREATE TABLE t1 (g GEOMETRY SRID 0);
410 CREATE SPATIAL INDEX g_idx ON t1(g ASC);
411 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
412 ALTER TABLE t1 ADD SPATIAL INDEX (g ASC);
413 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
414 DROP TABLE t1;
415 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
416 CREATE SPATIAL INDEX g_idx ON t1(g ASC);
417 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
418 ALTER TABLE t1 ADD SPATIAL INDEX (g ASC);
419 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
420 DROP TABLE t1;
421 # Explicit DESC index on geometry column is not allowed.
422 CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g DESC));
423 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
424 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g DESC));
425 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
426 CREATE TABLE t1 (g GEOMETRY SRID 0);
427 CREATE INDEX g_idx ON t1(g DESC);
428 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
429 ALTER TABLE t1 ADD INDEX (g DESC);
430 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
431 DROP TABLE t1;
432 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
433 CREATE INDEX g_idx ON t1(g DESC);
434 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
435 ALTER TABLE t1 ADD INDEX (g DESC);
436 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
437 DROP TABLE t1;
438 CREATE TABLE t1 (g GEOMETRY SRID 0, SPATIAL INDEX (g DESC));
439 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
440 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, SPATIAL INDEX (g DESC));
441 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
442 CREATE TABLE t1 (g GEOMETRY SRID 0);
443 CREATE SPATIAL INDEX g_idx ON t1(g DESC);
444 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
445 ALTER TABLE t1 ADD SPATIAL INDEX (g DESC);
446 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
447 DROP TABLE t1;
448 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
449 CREATE SPATIAL INDEX g_idx ON t1(g DESC);
450 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
451 ALTER TABLE t1 ADD SPATIAL INDEX (g DESC);
452 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
453 DROP TABLE t1;
454 # Fulltext index on geometry column is not allowed.
455 CREATE TABLE t1 (g GEOMETRY SRID 0, FULLTEXT INDEX (g));
456 ERROR HY000: Column 'g' cannot be part of FULLTEXT index
457 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, FULLTEXT INDEX (g));
458 ERROR HY000: Column 'g' cannot be part of FULLTEXT index
459 CREATE TABLE t1 (g GEOMETRY SRID 0);
460 CREATE FULLTEXT INDEX g_idx ON t1(g);
461 ERROR HY000: Column 'g' cannot be part of FULLTEXT index
462 ALTER TABLE t1 ADD FULLTEXT INDEX (g);
463 ERROR HY000: Column 'g' cannot be part of FULLTEXT index
464 DROP TABLE t1;
465 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
466 CREATE FULLTEXT INDEX g_idx ON t1(g);
467 ERROR HY000: Column 'g' cannot be part of FULLTEXT index
468 ALTER TABLE t1 ADD FULLTEXT INDEX (g);
469 ERROR HY000: Column 'g' cannot be part of FULLTEXT index
470 DROP TABLE t1;
471 # Index on nullable geometry column is not allowed.
472 CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g));
473 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
474 CREATE TABLE t1 (g GEOMETRY SRID 0);
475 CREATE INDEX g_idx ON t1(g);
476 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
477 ALTER TABLE t1 ADD INDEX (g);
478 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
479 DROP TABLE t1;
480 CREATE TABLE t1 (g GEOMETRY SRID 0, SPATIAL INDEX (g));
481 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
482 CREATE TABLE t1 (g GEOMETRY SRID 0);
483 CREATE SPATIAL INDEX g_idx ON t1(g);
484 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
485 ALTER TABLE t1 ADD SPATIAL INDEX (g);
486 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
487 DROP TABLE t1;
488 # Index on non-nullable geometry column is allowed.
489 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g));
490 SHOW CREATE TABLE t1;
491 Table Create Table
492 t1 CREATE TABLE `t1` (
493 `g` geometry NOT NULL /*!80003 SRID 0 */,
494 SPATIAL KEY `g` (`g`)
495 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
496 DROP TABLE t1;
497 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
498 CREATE INDEX g_idx ON t1(g);
499 SHOW CREATE TABLE t1;
500 Table Create Table
501 t1 CREATE TABLE `t1` (
502 `g` geometry NOT NULL /*!80003 SRID 0 */,
503 SPATIAL KEY `g_idx` (`g`)
504 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
505 DROP TABLE t1;
506 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
507 ALTER TABLE t1 ADD INDEX (g), ALGORITHM=COPY;
508 SHOW CREATE TABLE t1;
509 Table Create Table
510 t1 CREATE TABLE `t1` (
511 `g` geometry NOT NULL /*!80003 SRID 0 */,
512 SPATIAL KEY `g` (`g`)
513 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
514 DROP INDEX g ON t1;
515 ALTER TABLE t1 ADD INDEX (g), ALGORITHM=INPLACE;
516 SHOW CREATE TABLE t1;
517 Table Create Table
518 t1 CREATE TABLE `t1` (
519 `g` geometry NOT NULL /*!80003 SRID 0 */,
520 SPATIAL KEY `g` (`g`)
521 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
522 DROP TABLE t1;
523 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, SPATIAL INDEX (g));
524 SHOW CREATE TABLE t1;
525 Table Create Table
526 t1 CREATE TABLE `t1` (
527 `g` geometry NOT NULL /*!80003 SRID 0 */,
528 SPATIAL KEY `g` (`g`)
529 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
530 DROP TABLE t1;
531 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
532 CREATE SPATIAL INDEX g_idx ON t1(g);
533 SHOW CREATE TABLE t1;
534 Table Create Table
535 t1 CREATE TABLE `t1` (
536 `g` geometry NOT NULL /*!80003 SRID 0 */,
537 SPATIAL KEY `g_idx` (`g`)
538 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
539 DROP TABLE t1;
540 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
541 ALTER TABLE t1 ADD SPATIAL INDEX (g), ALGORITHM=COPY;
542 SHOW CREATE TABLE t1;
543 Table Create Table
544 t1 CREATE TABLE `t1` (
545 `g` geometry NOT NULL /*!80003 SRID 0 */,
546 SPATIAL KEY `g` (`g`)
547 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
548 DROP INDEX g ON t1;
549 ALTER TABLE t1 ADD SPATIAL INDEX (g), ALGORITHM=INPLACE;
550 SHOW CREATE TABLE t1;
551 Table Create Table
552 t1 CREATE TABLE `t1` (
553 `g` geometry NOT NULL /*!80003 SRID 0 */,
554 SPATIAL KEY `g` (`g`)
555 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
556 DROP TABLE t1;
557 # Virtual generated geometry columns don't support spatial indexes.
558 CREATE TABLE t1 (x INTEGER,
559 y INTEGER,
560 g POINT AS (POINT(x, y)) VIRTUAL SRID 0 NOT NULL);
561 ALTER TABLE t1 ADD PRIMARY KEY (g);
562 ERROR HY000: 'Spatial index on virtual generated column' is not supported for generated columns.
563 CREATE INDEX g_idx ON t1(g);
564 ERROR HY000: 'Spatial index on virtual generated column' is not supported for generated columns.
565 CREATE INDEX g_idx ON t1(g(25));
566 ERROR HY000: 'Spatial index on virtual generated column' is not supported for generated columns.
567 CREATE INDEX g_idx ON t1(g) USING BTREE;
568 ERROR HY000: 'Spatial index on virtual generated column' is not supported for generated columns.
569 DROP TABLE t1;
570 # Stored generated geometry columns follow the same rules as
571 # non-generated columns.
572 CREATE TABLE t1 (x INTEGER,
573 y INTEGER,
574 g POINT AS (POINT(x, y)) STORED SRID 0 NOT NULL);
575 ALTER TABLE t1 ADD PRIMARY KEY (g);
576 ERROR HY000: Spatial indexes can't be primary or unique indexes.
577 CREATE INDEX g_idx ON t1(g);
578 DROP INDEX g_idx ON t1;
579 CREATE INDEX g_idx ON t1(g(25));
580 ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
581 CREATE INDEX g_idx ON t1(g) USING BTREE;
582 ERROR HY000: The index type BTREE is not supported for spatial indexes.
583 DROP TABLE t1;
584 #
585 # Bug #27836608 INACCURATE ERROR THROWN WHILE CREATING SPATIAL INDEX OF
586 # INVALID TYPE
587 #
588 # Explicit R-tree index on geometry column is allowed.
589 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g) USING RTREE);
590 DROP TABLE t1;
591 CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
592 CREATE INDEX g_idx ON t1(g) USING RTREE;
593 DROP INDEX g_idx ON t1;
594 ALTER TABLE t1 ADD INDEX (g) USING RTREE;
595 DROP TABLE t1;
596 # Explicit R-tree index on non-gometry column is not allowed.
597 CREATE TABLE t1 (a INT NOT NULL, INDEX (a) USING RTREE);
598 ERROR 42000: A SPATIAL index may only contain a geometrical type column
599 CREATE TABLE t1 (a INT NOT NULL);
600 CREATE INDEX a_idx ON t1(a) USING RTREE;
601 ERROR 42000: A SPATIAL index may only contain a geometrical type column
602 ALTER TABLE t1 ADD INDEX (a) USING RTREE;
603 ERROR 42000: A SPATIAL index may only contain a geometrical type column
604 DROP TABLE t1;