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 89--enable_info 90ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC); 91 92ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC); 93 94ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on Polygon'; 95 96ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry'; 97 98ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE; 99--disable_info 100 101# Test the MBRContains 102SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); 103 104SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1); 105 106UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') 107WHERE MBRContains(tab.c4, @g1); 108 109SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1); 110 111DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 112 113SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1); 114 115 116SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)'); 117 118SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1); 119 120UPDATE tab SET C2 = ST_GeomFromText('POINT(100 100)') 121WHERE MBRContains(tab.c4, @g1); 122 123DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 124 125# Test the MBRWithin 126SET @g1 = ST_GeomFromText( 'POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'); 127 128SELECT c1,ST_AsText(c2),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1); 129 130UPDATE tab SET C2 = ST_GeomFromText('POINT(200 200)') 131WHERE MBRWithin(tab.c4, @g1); 132 133SELECT c1,ST_AsText(c2),ST_AsText(c4) FROM tab WHERE MBRWithin(tab.c4, @g1); 134 135DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); 136 137--error ER_SPATIAL_CANT_HAVE_NULL 138ALTER TABLE tab MODIFY COLUMN c2 MULTIPOINT; 139 140--error ER_SPATIAL_CANT_HAVE_NULL 141ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING; 142 143--error ER_SPATIAL_CANT_HAVE_NULL 144ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON; 145 146--error ER_SPATIAL_CANT_HAVE_NULL 147ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING NULL; 148 149--error ER_SPATIAL_CANT_HAVE_NULL 150ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON NULL; 151 152--error ER_SPATIAL_CANT_HAVE_NULL 153ALTER TABLE tab MODIFY COLUMN c4 Geometry NULL; 154 155--error ER_SPATIAL_CANT_HAVE_NULL 156ALTER TABLE tab CHANGE COLUMN c2 c22 POINT; 157 158--error ER_SPATIAL_CANT_HAVE_NULL 159ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING; 160 161--error ER_SPATIAL_CANT_HAVE_NULL 162ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON; 163 164# --error ER_SPATIAL_MUST_HAVE_GEOM_COL 165--error ER_WRONG_ARGUMENTS 166ALTER TABLE tab add SPATIAL INDEX idx1(c1); 167 168--error ER_PARSE_ERROR 169ALTER TABLE tab ADD SPATIAL INDEX idx6(c2 ASC) USING BTREE; 170 171--error ER_PARSE_ERROR 172ALTER TABLE tab ADD SPATIAL INDEX idx6(c2 ASC) 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 183SHOW CREATE TABLE tab; 184 185--replace_column 7 # 186SHOW INDEX FROM tab; 187 188SET @g1 = ST_GeomFromText('POLYGON((20 20,30 30,40 40,50 50,40 50,30 40,30 30,20 20))'); 189 190SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1); 191 192UPDATE tab SET C2 = ST_GeomFromText('POINT(1000 1000)') 193WHERE ST_Crosses(tab.c4, @g1); 194 195SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1); 196 197DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); 198 199--enable_info 200ALTER TABLE tab CHANGE COLUMN c2 c22 POINT NOT NULL; 201 202ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING NOT NULL; 203 204ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON NOT NULL; 205--disable_info 206 207SHOW CREATE TABLE tab; 208 209--replace_column 7 # 210SHOW INDEX FROM tab; 211 212--enable_info 213ALTER TABLE tab CHANGE COLUMN c22 c2 POINT NOT NULL; 214 215ALTER TABLE tab CHANGE COLUMN c33 c3 LINESTRING NOT NULL; 216 217ALTER TABLE tab CHANGE COLUMN c44 c4 POLYGON NOT NULL; 218--disable_info 219 220SHOW CREATE TABLE tab; 221 222--replace_column 7 # 223SHOW INDEX FROM tab; 224 225ALTER TABLE tab DISABLE KEYS; 226 227SHOW WARNINGS; 228 229SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); 230 231SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1); 232 233UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)') 234WHERE MBREquals(tab.c4, @g1); 235 236SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1); 237 238DELETE FROM tab WHERE MBREquals(tab.c4, @g1); 239 240SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1); 241 242--enable_info 243ALTER TABLE tab DROP PRIMARY KEY; 244 245ALTER TABLE tab ADD PRIMARY KEY(c2) ; 246--disable_info 247 248SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); 249 250SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1); 251 252UPDATE tab SET C2 = ST_GeomFromText('POINT(3000 3000)') 253WHERE ST_Touches(tab.c4, @g1); 254 255SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1); 256 257DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); 258 259SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1); 260 261FLUSH TABLE tab FOR EXPORT; 262 263--copy_file $MYSQLD_DATADIR/test/tab.ibd $MYSQLD_DATADIR/test/tab.ibd.bk 264 265UNLOCK TABLES; 266 267ALTER TABLE tab DISCARD TABLESPACE; 268 269--disable_warnings 270 271--error ER_TABLESPACE_DISCARDED 272SELECT 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 281ALTER TABLE tab IMPORT TABLESPACE; 282 283ALTER TABLE tab DROP INDEX idx2; 284ALTER TABLE tab DROP INDEX idx3; 285ALTER TABLE tab DROP INDEX idx4; 286ALTER TABLE tab DROP INDEX idx5; 287ALTER TABLE tab DROP INDEX idx6; 288 289SHOW CREATE TABLE tab; 290ALTER TABLE tab IMPORT TABLESPACE; 291--enable_query_log 292 293CHECK TABLE tab; 294 295SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab ORDER BY c1; 296 297SET @g1 = ST_GeomFromText('LINESTRING( 3010 3010,4010 4010,5010 5010)'); 298 299SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) order by c1; 300 301--error ER_DUP_ENTRY 302UPDATE tab SET c2 = ST_GeomFromText('POINT(4000 4000)') 303WHERE 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 309SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; 310 311DELETE FROM tab WHERE MBRIntersects(tab.c4, @g1); 312 313SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; 314 315INSERT INTO tab SELECT * FROM tab1; 316 317--enable_info 318ALTER 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 324SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 325CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab where c1 = c2; 326 327SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 328CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab; 329 330INSERT INTO temp_tab SELECT * FROM tab; 331 332CREATE SPATIAL INDEX idx2 ON temp_tab(c2); 333 334CREATE SPATIAL INDEX idx3 ON temp_tab(c3); 335 336CREATE SPATIAL INDEX idx4 ON temp_tab(c4); 337 338CREATE SPATIAL INDEX idx5 ON temp_tab(c5); 339 340SHOW CREATE TABLE temp_tab; 341 342SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); 343 344SELECT 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 347UPDATE temp_tab SET C2 = ST_GeomFromText('POINT(1000 1000)') 348WHERE MBRContains(temp_tab.c4, @g1); 349 350SELECT 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 353DELETE FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1); 354 355SELECT 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 359SHOW CREATE TABLE tab; 360 361--replace_column 7 # 362SHOW INDEX FROM tab; 363 364DELETE FROM tab; 365 366--enable_info 367ALTER TABLE tab ADD PRIMARY KEY(c2); 368 369CREATE SPATIAL INDEX idx2 ON tab(c2 ASC); 370 371ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c2); 372--disable_info 373 374SHOW CREATE TABLE tab; 375 376--replace_column 7 # 377SHOW INDEX FROM tab; 378 379INSERT INTO tab(c1,c2,c3,c4,c5) 380VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'), 381ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), 382ST_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 386DELETE FROM tab; 387 388--enable_info 389ALTER TABLE tab DROP PRIMARY KEY ; 390 391ALTER TABLE tab DROP KEY const_1; 392 393ALTER TABLE tab ADD PRIMARY KEY(c5(10)); 394 395ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c5(10)); 396--disable_info 397 398SHOW CREATE TABLE tab; 399 400--replace_column 7 # 401SHOW INDEX FROM tab; 402 403INSERT INTO tab(c1,c2,c3,c4,c5) 404VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'), 405ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), 406ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); 407 408#cleanup 409DROP TABLE tab,tab1,temp_tab; 410 411--enable_warnings 412 413# Check Modify POINT to GEOMETRY and GEOMETRY to POINT 414CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL, 415c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL) 416ENGINE=InnoDB; 417 418INSERT INTO tab(c1,c2,c3,c4,c5) 419VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'), 420ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), 421ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); 422 423 424INSERT INTO tab(c1,c2,c3,c4,c5) 425VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'), 426ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'), 427ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))')); 428 429INSERT INTO tab(c1,c2,c3,c4,c5) 430VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'), 431ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'), 432ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))')); 433 434INSERT INTO tab(c1,c2,c3,c4,c5) 435VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'), 436ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'), 437ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))')); 438 439INSERT INTO tab(c1,c2,c3,c4,c5) 440VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'), 441ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'), 442ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))')); 443 444INSERT INTO tab(c1,c2,c3,c4,c5) 445VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), 446ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'), 447ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))')); 448 449INSERT INTO tab(c1,c2,c3,c4,c5) 450VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), 451ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), 452ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); 453 454INSERT INTO tab(c1,c2,c3,c4,c5) 455VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), 456ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), 457ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); 458 459 460INSERT INTO tab(c1,c2,c3,c4,c5) 461VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'), 462ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'), 463ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))')); 464 465 466INSERT INTO tab(c1,c2,c3,c4,c5) 467VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'), 468ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'), 469ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))')); 470 471ANALYZE TABLE tab; 472 473--enable_info 474ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC); 475 476ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC); 477 478ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on Polygon'; 479 480ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry'; 481 482ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE; 483 484 485ALTER TABLE tab MODIFY COLUMN c2 GEOMETRY NOT NULL; 486 487--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 488ALTER TABLE tab MODIFY COLUMN c3 POLYGON NOT NULL; 489 490ALTER TABLE tab add COLUMN c7 POINT NOT NULL; 491--disable_info 492 493# instant add, supported 494ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INPLACE, LOCK=NONE; 495SELECT HEX(c8) FROM tab; 496BEGIN; 497INSERT INTO tab SELECT 0,c2,c3,c4,c5, 498ST_GeomFromText('POINT(67 89)'),ST_GeomFromText('POINT(67 89)') 499FROM tab LIMIT 1; 500SELECT HEX(c8) FROM tab; 501ROLLBACK; 502 503ALTER TABLE tab add COLUMN c9 POINT NOT NULL AFTER c5, ALGORITHM = INPLACE, LOCK=NONE; 504ALTER TABLE tab DROP COLUMN c9, ALGORITHM=INSTANT; 505 506SHOW CREATE TABLE tab; 507 508--replace_column 7 # 509SHOW INDEX FROM tab; 510 511SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); 512 513UPDATE tab SET C2 = ST_GeomFromText('POINT(1000 1000)') 514WHERE MBRContains(tab.c4, @g1); 515 516SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; 517 518DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 519 520SELECT 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 526ALTER TABLE tab MODIFY COLUMN c4 GEOMETRY NOT NULL; 527--disable_info 528 529SHOW CREATE TABLE tab; 530 531--replace_column 7 # 532SHOW INDEX FROM tab; 533 534ANALYZE TABLE tab; 535 536SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); 537 538SET @g2 = ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'); 539 540SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) 541AND MBREquals(tab.c3,@g2) ORDER BY c1; 542 543UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)') 544WHERE MBREquals(tab.c4, @g1) AND MBREquals(tab.c3,@g2); 545 546SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) 547AND MBREquals(tab.c3,@g2) ORDER BY c1; 548 549DELETE FROM tab WHERE MBREquals(tab.c4, @g1) AND MBREquals(tab.c3,@g2); 550 551SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) 552AND MBREquals(tab.c3,@g2) ORDER BY c1; 553 554ANALYZE TABLE tab; 555 556SET @g1 = ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'); 557 558SET @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 562ALTER TABLE tab MODIFY COLUMN c2 POINT NOT NULL; 563 564ALTER TABLE tab MODIFY COLUMN c4 POLYGON NOT NULL; 565--disable_info 566 567SHOW CREATE TABLE tab; 568 569--replace_column 7 # 570SHOW INDEX FROM tab; 571 572ANALYZE TABLE tab; 573 574SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); 575 576SET @g2 = ST_GeomFromText('LINESTRING(1 1,2 2,3 3)'); 577 578# Should be 0 rows affected 579SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) 580AND ST_Touches(tab.c3,@g2); 581 582# Should be 0 rows affected 583UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)') 584WHERE ST_Touches(tab.c4, @g1) AND ST_Touches(tab.c3,@g2); 585 586# Should be 0 rows affected 587DELETE FROM tab WHERE ST_Touches(tab.c4, @g1) AND ST_Touches(tab.c3,@g2); 588 589# Should be 0 rows affected 590SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) 591AND ST_Touches(tab.c3,@g2); 592 593# should be 1 row affected 594SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) 595OR ST_Touches(tab.c3,@g2); 596 597# should be 1 row affected 598UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)') 599WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2); 600 601# should be 1 row affected 602SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) 603OR ST_Touches(tab.c3,@g2); 604 605# should be 1 row affected 606DELETE FROM tab WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2); 607 608# Should be Empty set 609SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) 610OR ST_Touches(tab.c3,@g2); 611 612--enable_info 613# --error ER_SPATIAL_MUST_HAVE_GEOM_COL 614--error ER_WRONG_ARGUMENTS 615ALTER TABLE tab MODIFY COLUMN c4 INT NOT NULL; 616 617# --error ER_SPATIAL_MUST_HAVE_GEOM_COL 618--error ER_WRONG_ARGUMENTS 619ALTER TABLE tab MODIFY COLUMN c4 BLOB NOT NULL; 620--disable_info 621 622# Test InnoDB to Myisam to InnoDB 623ALTER TABLE tab ENGINE Myisam; 624 625ALTER TABLE tab ENGINE InnoDB; 626 627ANALYZE TABLE tab; 628 629SET @g1 = ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'); 630 631SET @g2 = ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'); 632 633SELECT 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 640SET @g1 = ST_GeomFromText('POINT(2000 2000)'); 641 642SET @g2 = ST_GeomFromText('POINT(2000 2000)'); 643 644SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c2, @g1) AND MBRWithin(tab.c3, @g2); 645 646DELETE FROM tab WHERE MBRWithin(tab.c2, @g1) AND MBRWithin(tab.c3, @g2); 647 648SELECT 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 651DROP TABLE tab; 652 653# Check Foreign Key constraint on Point column 654CREATE TABLE parent (id POINT, PRIMARY KEY(id)) ENGINE=InnoDB; 655 656CREATE TABLE child (id GEOMETRY NOT NULL, parent_id POINT NOT NULL) ENGINE=InnoDB; 657 658--enable_info 659ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC); 660 661ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC); 662--disable_info 663 664SHOW CREATE TABLE parent; 665 666SHOW CREATE TABLE child; 667 668SHOW INDEX FROM parent; 669 670--replace_column 7 # 671SHOW INDEX FROM child; 672 673# --error ER_CANNOT_ADD_FOREIGN 674--disable_result_log 675--error ER_CANT_CREATE_TABLE 676ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ; 677 678# --error ER_CANNOT_ADD_FOREIGN 679--error ER_CANT_CREATE_TABLE 680ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ON DELETE CASCADE ; 681--enable_result_log 682 683#cleanup 684DROP table child,parent; 685 686# Check Foreign Key constraint on Geometry column 687CREATE TABLE parent (id GEOMETRY, PRIMARY KEY(id(10))) ENGINE=InnoDB; 688 689CREATE TABLE child (id GEOMETRY NOT NULL, parent_id GEOMETRY NOT NULL) ENGINE=InnoDB; 690 691--enable_info 692ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC) ; 693 694ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC); 695--disable_info 696 697SHOW CREATE TABLE parent; 698 699SHOW CREATE TABLE child; 700 701SHOW INDEX FROM parent; 702 703SHOW INDEX FROM child; 704 705--disable_result_log 706# --error ER_BLOB_KEY_WITHOUT_LENGTH 707--error ER_CANT_CREATE_TABLE 708ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ; 709--enable_result_log 710 711#cleanup 712DROP table child,parent; 713 714# Check add spatial index when table already has rows (inplace). 715create table t1 (c1 int) engine=innodb; 716insert into t1 values(NULL); 717 718# Add spatial index fail, since geometry column can't be null. 719--error ER_SPATIAL_CANT_HAVE_NULL 720alter 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 729alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), 730add spatial index(b), algorithm=inplace; 731--disable_info 732DROP table t1; 733 734# Check add spatial index when table already has rows (copy). 735create table t1 (c1 int) engine=innodb; 736insert into t1 values(NULL); 737 738# Add spatial index fail, since geometry column can't be null. 739--error ER_SPATIAL_CANT_HAVE_NULL 740alter 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 744alter table t1 add b geometry not null, add spatial index(b), algorithm=copy; 745 746alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), 747add spatial index(b), algorithm=copy; 748DROP table t1; 749 750--echo # 751--echo # BUG#20111575 ALTER TABLE...ADD SPATIAL INDEX...LOCK NONE IS REFUSED 752--echo # WITHOUT STATING A REASON 753--echo # 754CREATE TABLE t1(p point NOT NULL) ENGINE=innodb; 755--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 756ALTER TABLE t1 ADD SPATIAL INDEX(p), LOCK=NONE; 757ALTER TABLE t1 ADD SPATIAL INDEX(p); 758--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 759ALTER TABLE t1 FORCE, LOCK=NONE; 760DROP TABLE t1; 761 762create table t1 (p point not null default if(unix_timestamp()>10,POINT(1,1),LineString(Point(0,0),Point(1,1)))) ENGINE=innodb; 763set timestamp=10; 764--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 765insert into t1 values(default); 766drop table t1; 767SET timestamp=default; 768 769create table t1 (p point not null default if(unix_timestamp()>10,POINT(1,1),LineString(Point(0,0),Point(1,1)))) ENGINE=innodb; 770set timestamp=10; 771--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 772alter table t1 add column i int; 773drop table t1; 774SET timestamp=default; 775 776CREATE OR REPLACE TABLE t1 (a INT) ENGINE=InnoDB; 777--error ER_CANT_CREATE_GEOMETRY_OBJECT 778ALTER TABLE t1 ADD COLUMN b POINT DEFAULT '0'; 779DROP TABLE t1; 780 781