Debian Codegraph @

mysql-test/suite/innodb_gis/t/alter_spatial_index.test

raw
1 # ******************************************************************
2 # Test Alter table add spatial idex asc/desc comments
3 # Test error Alter table modify column with No not null option
4 # Test error Alter table modify column with null option
5 # Test table column having both indexes spatial and Btree
6 # Test error Alter table spatial index using hash/Btree
7 # Test modify column from point to multipoint,line to multiline
8 # Test modify column from mutipoint to point,multiline to line
9 # Test discard & import tablepsace
10 # spatial index on temp tables
11 # Unique constraint on spatial index column Geometry
12 # Unique constraint on spatial index column POINT
13 # Modify Engine Innodb to Myisam to InnoDB
14 # Check Foreign Key constraint on Point column
15 # Check Foreign Key constraint on Geometry column
16 # ******************************************************************
17
18 CALL mtr.add_suppression("but MySQL is asking statistics for 2 columns. Have you mixed");
19 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Tablespace for table .* is set as discarded");
20
21
22 let MYSQLD_DATADIR= `select @@datadir`;
23
24 CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL SRID 0,
25 c3 LINESTRING NOT NULL SRID 0,c4 POLYGON NOT NULL SRID 0,c5 GEOMETRY NOT NULL SRID 0)
26 ENGINE=InnoDB;
27
28 CREATE TABLE tab1(c1 int NOT NULL PRIMARY KEY,c2 MULTIPOINT NOT NULL SRID 0,
29 c3 MULTILINESTRING NOT NULL SRID 0,c4 MULTIPOLYGON NOT NULL SRID 0,c5 GEOMETRY NOT NULL SRID 0)
30 ENGINE=InnoDB;
31
32 INSERT INTO tab1 SELECT * FROM tab;
33
34 INSERT INTO tab(c1,c2,c3,c4,c5)
35 VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
36 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
37 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
38
39
40 INSERT INTO tab(c1,c2,c3,c4,c5)
41 VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),
42 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),
43 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
44
45 INSERT INTO tab(c1,c2,c3,c4,c5)
46 VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'),
47 ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'),
48 ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'));
49
50 INSERT INTO tab(c1,c2,c3,c4,c5)
51 VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'),
52 ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'),
53 ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'));
54
55 INSERT INTO tab(c1,c2,c3,c4,c5)
56 VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'),
57 ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'),
58 ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'));
59
60 INSERT INTO tab(c1,c2,c3,c4,c5)
61 VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
62 ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'),
63 ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'));
64
65 INSERT INTO tab(c1,c2,c3,c4,c5)
66 VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
67 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
68 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
69
70 INSERT INTO tab(c1,c2,c3,c4,c5)
71 VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
72 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
73 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
74
75
76 INSERT INTO tab(c1,c2,c3,c4,c5)
77 VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'),
78 ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'),
79 ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'));
80
81
82 INSERT INTO tab(c1,c2,c3,c4,c5)
83 VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'),
84 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'),
85 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
86
87
88 ALTER TABLE tab ADD SPATIAL INDEX idx2(c2);
89
90 --error ER_WRONG_USAGE
91 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC);
92
93 --error ER_WRONG_USAGE
94 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 ASC);
95
96 ALTER TABLE tab ADD SPATIAL KEY idx3(c3);
97
98 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4) COMMENT 'testing spatial index on Polygon';
99
100 ALTER TABLE tab ADD SPATIAL KEY idx5(c5) COMMENT 'testing spatial index on Geometry';
101
102 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
103 ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE;
104
105
106 # Test the MBRContains
107 SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
108
109 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
110
111 UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
112 WHERE MBRContains(tab.c4, @g1);
113
114 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
115
116 DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
117
118 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
119
120
121 SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)');
122
123 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
124
125 UPDATE tab SET C2 = ST_GeomFromText('POINT(100 100)')
126 WHERE MBRContains(tab.c4, @g1);
127
128 DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
129
130 # Test the MBRWithin
131 SET @g1 = ST_GeomFromText( 'POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))');
132
133 SELECT c1,ST_AsText(c2),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1);
134
135 UPDATE tab SET C2 = ST_GeomFromText('POINT(200 200)')
136 WHERE MBRWithin(tab.c4, @g1);
137
138 SELECT c1,ST_AsText(c2),ST_AsText(c4) FROM tab WHERE MBRWithin(tab.c4, @g1);
139
140 DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
141
142 --error ER_SPATIAL_CANT_HAVE_NULL
143 ALTER TABLE tab MODIFY COLUMN c2 MULTIPOINT SRID 0;
144
145 --error ER_SPATIAL_CANT_HAVE_NULL
146 ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING SRID 0;
147
148 --error ER_SPATIAL_CANT_HAVE_NULL
149 ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON SRID 0;
150
151 --error ER_SPATIAL_CANT_HAVE_NULL
152 ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING NULL SRID 0;
153
154 --error ER_SPATIAL_CANT_HAVE_NULL
155 ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON NULL SRID 0;
156
157 --error ER_SPATIAL_CANT_HAVE_NULL
158 ALTER TABLE tab MODIFY COLUMN c4 Geometry NULL SRID 0;
159
160 --error ER_SPATIAL_CANT_HAVE_NULL
161 ALTER TABLE tab CHANGE COLUMN c2 c22 POINT SRID 0;
162
163 --error ER_SPATIAL_CANT_HAVE_NULL
164 ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING SRID 0;
165
166 --error ER_SPATIAL_CANT_HAVE_NULL
167 ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON SRID 0;
168
169 --error ER_SPATIAL_MUST_HAVE_GEOM_COL
170 ALTER TABLE tab add SPATIAL INDEX idx1(c1);
171
172 --error ER_PARSE_ERROR
173 ALTER TABLE tab ADD SPATIAL INDEX idx6(c2) USING BTREE;
174
175 --error ER_PARSE_ERROR
176 ALTER TABLE tab ADD SPATIAL INDEX idx6(c2) USING HASH;
177
178 --error ER_CANT_CREATE_GEOMETRY_OBJECT
179 ALTER TABLE tab CHANGE c2 c2 MULTIPOINT NOT NULL SRID 0 FIRST, ALGORITHM=COPY;
180
181 --error ER_CANT_CREATE_GEOMETRY_OBJECT
182 ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING NOT NULL SRID 0,ALGORITHM=COPY;
183
184 --error ER_CANT_CREATE_GEOMETRY_OBJECT
185 ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON NOT NULL SRID 0;
186
187 SHOW CREATE TABLE tab;
188
189 --replace_column 7 #
190 SHOW INDEX FROM tab;
191
192 # g1 is an invalid polygon. It contains a spike.
193 SET @g1 = ST_GeomFromText('POLYGON((20 20,30 30,40 40,50 50,40 50,30 40,30 30,20 20))');
194
195 --echo # Invalid polygon.
196 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1);
197
198 --echo # Invalid polygon.
199 UPDATE tab SET C2 = ST_GeomFromText('POINT(1000 1000)')
200 WHERE ST_Crosses(tab.c4, @g1);
201
202 --echo # Invalid polygon.
203 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1);
204
205 --echo # Invalid polygon.
206 DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
207
208 ALTER TABLE tab CHANGE COLUMN c2 c22 POINT NOT NULL SRID 0;
209
210 ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING NOT NULL SRID 0;
211
212 ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON NOT NULL SRID 0;
213
214 SHOW CREATE TABLE tab;
215
216 --replace_column 7 #
217 SHOW INDEX FROM tab;
218
219 ALTER TABLE tab CHANGE COLUMN c22 c2 POINT NOT NULL SRID 0;
220
221 ALTER TABLE tab CHANGE COLUMN c33 c3 LINESTRING NOT NULL SRID 0;
222
223 ALTER TABLE tab CHANGE COLUMN c44 c4 POLYGON NOT NULL SRID 0;
224
225 SHOW CREATE TABLE tab;
226
227 --replace_column 7 #
228 SHOW INDEX FROM tab;
229
230 ALTER TABLE tab DISABLE KEYS;
231
232 SHOW WARNINGS;
233
234 SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
235
236 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1);
237
238 UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
239 WHERE MBREquals(tab.c4, @g1);
240
241 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1);
242
243 DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
244
245 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1);
246
247 ALTER TABLE tab DROP PRIMARY KEY;
248
249 --error ER_SPATIAL_UNIQUE_INDEX
250 ALTER TABLE tab ADD PRIMARY KEY(c2) ;
251
252 SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
253
254 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1);
255
256 UPDATE tab SET C2 = ST_GeomFromText('POINT(3000 3000)')
257 WHERE ST_Touches(tab.c4, @g1);
258
259 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1);
260
261 DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
262
263 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1);
264
265 FLUSH TABLE tab FOR EXPORT;
266
267 --copy_file $MYSQLD_DATADIR/test/tab.ibd $MYSQLD_DATADIR/test/tab.ibd.bk
268
269 UNLOCK TABLES;
270
271 ALTER TABLE tab DISCARD TABLESPACE;
272
273 --disable_warnings
274
275 --error ER_TABLESPACE_DISCARDED
276 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab;
277
278 --copy_file $MYSQLD_DATADIR/test/tab.ibd.bk $MYSQLD_DATADIR/test/tab.ibd
279
280 --remove_file $MYSQLD_DATADIR/test/tab.ibd.bk
281
282 --disable_query_log
283
284 ALTER TABLE tab IMPORT TABLESPACE;
285
286 --enable_query_log
287
288 CHECK TABLE tab;
289
290 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab ORDER BY c1;
291
292 SET @g1 = ST_GeomFromText('LINESTRING( 3010 3010,4010 4010,5010 5010)');
293
294 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) order by c1;
295
296 UPDATE tab SET c2 = ST_GeomFromText('POINT(4000 4000)')
297 WHERE MBRIntersects(tab.c4, @g1);
298
299 --error ER_CANT_CREATE_GEOMETRY_OBJECT
300 UPDATE tab SET c4 = ST_GeomFromText('POINT(4000 4000)')
301 WHERE MBRIntersects(tab.c4, @g1);
302
303 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
304
305 DELETE FROM tab WHERE MBRIntersects(tab.c4, @g1);
306
307 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
308
309 INSERT INTO tab SELECT * FROM tab1;
310
311 ALTER TABLE tab DROP INDEX idx2;
312
313 # Check spatial index on temp tables
314
315 CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab where c1 = c2;
316
317 INSERT INTO temp_tab SELECT * FROM tab;
318
319 CREATE SPATIAL INDEX idx2 ON temp_tab(c2);
320
321 CREATE SPATIAL INDEX idx3 ON temp_tab(c3);
322
323 CREATE SPATIAL INDEX idx4 ON temp_tab(c4);
324
325 CREATE SPATIAL INDEX idx5 ON temp_tab(c5);
326
327 SHOW CREATE TABLE temp_tab;
328
329 SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
330
331 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1) ORDER BY c1;
332
333 # The following comments will be removed once the patch is available
334 UPDATE temp_tab SET C2 = ST_GeomFromText('POINT(1000 1000)')
335 WHERE MBRContains(temp_tab.c4, @g1);
336
337 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1);
338
339 # Sever crashes Here so commented, will be removed later
340 DELETE FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1);
341
342 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1) ORDER BY c1;
343
344 # Check Unique constraint on spatial index column POINT
345
346 SHOW CREATE TABLE tab;
347
348 --replace_column 7 #
349 SHOW INDEX FROM tab;
350
351 DELETE FROM tab;
352
353 --error ER_SPATIAL_UNIQUE_INDEX
354 ALTER TABLE tab ADD PRIMARY KEY(c2);
355
356 CREATE SPATIAL INDEX idx2 ON tab(c2);
357
358 --error ER_SPATIAL_UNIQUE_INDEX
359 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c2);
360
361 SHOW CREATE TABLE tab;
362
363 --replace_column 7 #
364 SHOW INDEX FROM tab;
365
366 INSERT INTO tab(c1,c2,c3,c4,c5)
367 VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
368 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
369 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
370
371 # Check Unique constraint on spatial index column Geometry
372
373 DELETE FROM tab;
374
375 --error ER_WRONG_SUB_KEY
376 ALTER TABLE tab ADD PRIMARY KEY(c5(10));
377
378 --error ER_WRONG_SUB_KEY
379 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c5(10));
380
381 SHOW CREATE TABLE tab;
382
383 --replace_column 7 #
384 SHOW INDEX FROM tab;
385
386 INSERT INTO tab(c1,c2,c3,c4,c5)
387 VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
388 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
389 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
390
391 #cleanup
392 DROP TABLE tab,tab1,temp_tab;
393
394 --enable_warnings
395
396 # Check Modify POINT to GEOMETRY and GEOMETRY to POINT
397 CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL SRID 0,
398 c3 LINESTRING NOT NULL SRID 0,c4 POLYGON NOT NULL SRID 0,c5 GEOMETRY NOT NULL SRID 0)
399 ENGINE=InnoDB;
400
401 INSERT INTO tab(c1,c2,c3,c4,c5)
402 VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
403 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
404 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
405
406
407 INSERT INTO tab(c1,c2,c3,c4,c5)
408 VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),
409 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),
410 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
411
412 INSERT INTO tab(c1,c2,c3,c4,c5)
413 VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'),
414 ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'),
415 ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'));
416
417 INSERT INTO tab(c1,c2,c3,c4,c5)
418 VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'),
419 ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'),
420 ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'));
421
422 INSERT INTO tab(c1,c2,c3,c4,c5)
423 VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'),
424 ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'),
425 ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'));
426
427 INSERT INTO tab(c1,c2,c3,c4,c5)
428 VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
429 ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'),
430 ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'));
431
432 INSERT INTO tab(c1,c2,c3,c4,c5)
433 VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
434 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
435 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
436
437 INSERT INTO tab(c1,c2,c3,c4,c5)
438 VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
439 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
440 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
441
442
443 INSERT INTO tab(c1,c2,c3,c4,c5)
444 VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'),
445 ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'),
446 ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'));
447
448
449 INSERT INTO tab(c1,c2,c3,c4,c5)
450 VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'),
451 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'),
452 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
453
454 ANALYZE TABLE tab;
455
456 ALTER TABLE tab ADD SPATIAL INDEX idx2(c2);
457
458 ALTER TABLE tab ADD SPATIAL KEY idx3(c3);
459
460 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4) COMMENT 'testing spatial index on Polygon';
461
462 ALTER TABLE tab ADD SPATIAL KEY idx5(c5) COMMENT 'testing spatial index on Geometry';
463
464 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
465 ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE;
466
467
468 ALTER TABLE tab MODIFY COLUMN c2 GEOMETRY NOT NULL SRID 0;
469
470 --error ER_CANT_CREATE_GEOMETRY_OBJECT
471 ALTER TABLE tab MODIFY COLUMN c3 POLYGON NOT NULL SRID 0;
472
473 --error ER_INVALID_USE_OF_NULL
474 ALTER TABLE tab add COLUMN c7 POINT NOT NULL;
475
476 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
477 ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INPLACE, LOCK=NONE;
478
479 --error ER_INVALID_USE_OF_NULL
480 ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INPLACE, LOCK=SHARED;
481
482 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
483 ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INSTANT;
484
485 SHOW CREATE TABLE tab;
486
487 --replace_column 7 #
488 SHOW INDEX FROM tab;
489
490 SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
491
492 UPDATE tab SET C2 = ST_GeomFromText('POINT(1000 1000)')
493 WHERE MBRContains(tab.c4, @g1);
494
495 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
496
497 DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
498
499 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
500
501 --error ER_CANT_CREATE_GEOMETRY_OBJECT
502 ALTER TABLE tab MODIFY COLUMN c2 POLYGON NOT NULL SRID 0;
503
504 ALTER TABLE tab MODIFY COLUMN c4 GEOMETRY NOT NULL SRID 0;
505
506 SHOW CREATE TABLE tab;
507
508 --replace_column 7 #
509 SHOW INDEX FROM tab;
510
511 ANALYZE TABLE tab;
512
513 SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
514
515 SET @g2 = ST_GeomFromText('LINESTRING(140 140,150 150,160 160)');
516
517 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1)
518 AND MBREquals(tab.c3,@g2) ORDER BY c1;
519
520 UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
521 WHERE MBREquals(tab.c4, @g1) AND MBREquals(tab.c3,@g2);
522
523 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1)
524 AND MBREquals(tab.c3,@g2) ORDER BY c1;
525
526 DELETE FROM tab WHERE MBREquals(tab.c4, @g1) AND MBREquals(tab.c3,@g2);
527
528 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1)
529 AND MBREquals(tab.c3,@g2) ORDER BY c1;
530
531 ANALYZE TABLE tab;
532
533 SET @g1 = ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))');
534
535 SET @g2 = ST_GeomFromText('LINESTRING(1 1,2 2,3 3)');
536
537 # When Point type data exist in the column allow DDL operation
538 ALTER TABLE tab MODIFY COLUMN c2 POINT NOT NULL SRID 0;
539
540 ALTER TABLE tab MODIFY COLUMN c3 LINESTRING NOT NULL SRID 0;
541
542 ALTER TABLE tab MODIFY COLUMN c4 POLYGON NOT NULL SRID 0;
543
544 SHOW CREATE TABLE tab;
545
546 --replace_column 7 #
547 SHOW INDEX FROM tab;
548
549 ANALYZE TABLE tab;
550
551 SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
552
553 SET @g2 = ST_GeomFromText('LINESTRING(1 1,2 2,3 3)');
554
555 # Should be 0 rows affected
556 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
557 AND ST_Touches(tab.c3,@g2);
558
559 # Should be 0 rows affected
560 UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
561 WHERE ST_Touches(tab.c4, @g1) AND ST_Touches(tab.c3,@g2);
562
563 # Should be 0 rows affected
564 DELETE FROM tab WHERE ST_Touches(tab.c4, @g1) AND ST_Touches(tab.c3,@g2);
565
566 # Should be 0 rows affected
567 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
568 AND ST_Touches(tab.c3,@g2);
569
570 # should be 1 row affected
571 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
572 OR ST_Touches(tab.c3,@g2);
573
574 # should be 1 row affected
575 UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
576 WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2);
577
578 # should be 1 row affected
579 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
580 OR ST_Touches(tab.c3,@g2);
581
582 # should be 1 row affected
583 DELETE FROM tab WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2);
584
585 # Should be Empty set
586 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
587 OR ST_Touches(tab.c3,@g2);
588
589 --error ER_SPATIAL_MUST_HAVE_GEOM_COL
590 ALTER TABLE tab MODIFY COLUMN c4 INT NOT NULL;
591
592 --error ER_SPATIAL_MUST_HAVE_GEOM_COL
593 ALTER TABLE tab MODIFY COLUMN c4 BLOB NOT NULL;
594
595 # Test InnoDB to Myisam to InnoDB
596 ALTER TABLE tab ENGINE Myisam;
597
598 ALTER TABLE tab ENGINE InnoDB;
599
600 ANALYZE TABLE tab;
601
602 SET @g1 = ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))');
603
604 SET @g2 = ST_GeomFromText('LINESTRING(400 400,500 500,600 700)');
605
606 SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) AND MBRWithin(tab.c3, @g2);
607
608 --error ER_CANT_CREATE_GEOMETRY_OBJECT
609 UPDATE tab SET c2 = ST_GeomFromText('POINT(2000 2000)'),
610 c3=ST_GeomFromText('POINT(2000 2000)')
611 WHERE MBRWithin(tab.c4, @g1) AND MBRWithin(tab.c3, @g2);
612
613 SET @g1 = ST_GeomFromText('POINT(2000 2000)');
614
615 SET @g2 = ST_GeomFromText('POINT(2000 2000)');
616
617 SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c2, @g1) AND MBRWithin(tab.c3, @g2);
618
619 DELETE FROM tab WHERE MBRWithin(tab.c2, @g1) AND MBRWithin(tab.c3, @g2);
620
621 SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c2, @g1) AND MBRWithin(tab.c3, @g2);
622
623 #cleanup
624 DROP TABLE tab;
625
626 # Check Foreign Key constraint on Point column
627 CREATE TABLE parent (id POINT SRID 0 NOT NULL) ENGINE=InnoDB;
628
629 CREATE TABLE child (id GEOMETRY NOT NULL, parent_id POINT NOT NULL SRID 0) ENGINE=InnoDB;
630
631 ALTER TABLE parent ADD SPATIAL INDEX idx1(id);
632
633 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id);
634
635 ANALYZE TABLE parent;
636 ANALYZE TABLE child;
637
638 SHOW CREATE TABLE parent;
639
640 SHOW CREATE TABLE child;
641
642 SHOW INDEX FROM parent;
643
644 --replace_column 7 #
645 SHOW INDEX FROM child;
646
647 --error ER_FK_NO_INDEX_CHILD
648 ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ;
649
650 --error ER_FK_NO_INDEX_CHILD
651 ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ON DELETE CASCADE ;
652
653 #cleanup
654 DROP table child,parent;
655
656 # Check Foreign Key constraint on Geometry column
657 CREATE TABLE parent (id GEOMETRY SRID 0 NOT NULL) ENGINE=InnoDB;
658
659 CREATE TABLE child (id GEOMETRY NOT NULL, parent_id GEOMETRY NOT NULL SRID 0) ENGINE=InnoDB;
660
661 ALTER TABLE parent ADD SPATIAL INDEX idx1(id) ;
662
663 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id);
664
665 ANALYZE TABLE parent;
666 ANALYZE TABLE child;
667
668 SHOW CREATE TABLE parent;
669
670 SHOW CREATE TABLE child;
671
672 SHOW INDEX FROM parent;
673
674 SHOW INDEX FROM child;
675
676 --error ER_FK_NO_INDEX_CHILD
677 ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ;
678
679 #cleanup
680 DROP table child,parent;
681
682 # Check add spatial index when table already has rows (inplace).
683 create table t1 (c1 int) engine=innodb;
684 insert into t1 values(NULL);
685
686 # Add spatial index fail, since geometry column can't be null.
687 --error ER_SPATIAL_CANT_HAVE_NULL
688 alter table t1 add b geometry, add spatial index(b), algorithm=inplace;
689
690 # Add spatial index fail, since there's invalid geo data.
691 # The case has to be commented because it no longer fails and following cases
692 # don't expect the effect of such a statement.
693 #--error ER_CANT_CREATE_GEOMETRY_OBJECT
694 # alter table t1 add b geometry not null, add spatial index(b), algorithm=inplace;
695
696 # Add a geometry column.
697 alter table t1 add b geometry, algorithm=inplace;
698
699 # Add spatial index fail, since there's a NULL or invalid geo data.
700 # The case has to be commented because it no longer fails and following cases
701 # don't expect the effect of such a statement.
702 #--error ER_CANT_CREATE_GEOMETRY_OBJECT
703 #alter table t1 add spatial index(b), algorithm=inplace;
704
705 # Update invalide geo data to point(0 0).
706 update t1 set b = st_geomfromtext('point(0 0)');
707
708 # Add spatial index success.
709 --error ER_SPATIAL_CANT_HAVE_NULL
710 alter table t1 add spatial index(b), algorithm=inplace;
711
712 # Delete rows.
713 delete from t1;
714
715 #cleanup
716 DROP table t1;
717
718 # Check add spatial index when table already has rows (copy).
719 create table t1 (c1 int) engine=innodb;
720 insert into t1 values(NULL);
721
722 # Add spatial index fail, since geometry column can't be null.
723 --error ER_SPATIAL_CANT_HAVE_NULL
724 alter table t1 add b geometry, add spatial index(b), algorithm=copy;
725
726 # Add spatial index fail, since there's a NULL or invalid geo data.
727 --error ER_INVALID_USE_OF_NULL
728 alter table t1 add b geometry not null, add spatial index(b), algorithm=copy;
729
730 # Add a geometry column.
731 --error ER_INVALID_USE_OF_NULL
732 alter table t1 add b geometry not null, algorithm=copy;
733
734 # Add spatial index.
735 # The case has to be commented because it no longer fails and following cases
736 # don't expect the effect of such a statement.
737 #--error ER_CANT_CREATE_GEOMETRY_OBJECT
738 #alter table t1 add spatial index(b), algorithm=copy;
739
740 # Update invalide geo data to point(0 0).
741 --error ER_BAD_FIELD_ERROR
742 update t1 set b = st_geomfromtext('point(0 0)');
743
744 # Add spatial index success.
745 --error ER_KEY_COLUMN_DOES_NOT_EXITS
746 alter table t1 add spatial index(b), algorithm=copy;
747
748 # Delete rows.
749 delete from t1;
750
751 #cleanup
752 DROP table t1;
753
754 --echo #
755 --echo # BUG#20111575 ALTER TABLE...ADD SPATIAL INDEX...LOCK NONE IS REFUSED
756 --echo # WITHOUT STATING A REASON
757 --echo #
758 CREATE TABLE t1(p point NOT NULL SRID 0) ENGINE=innodb;
759 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
760 ALTER TABLE t1 ADD SPATIAL INDEX(p), LOCK=NONE;
761 ALTER TABLE t1 ADD SPATIAL INDEX(p);
762 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
763 ALTER TABLE t1 FORCE, LOCK=NONE;
764 DROP TABLE t1;