Debian Codegraph @

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

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