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