1# This is to test create GIS index with algorithm=inplace. 2 3--source include/have_innodb.inc 4--source include/have_debug.inc 5--source include/no_valgrind_without_big.inc 6 7# Create table with geometry column 8CREATE TABLE t1 (c1 INT, c2 GEOMETRY NOT NULL, c3 GEOMETRY NOT NULL) ENGINE=INNODB; 9 10# Insert enough values 11INSERT INTO t1 VALUES(1, Point(1,1), ST_GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))')); 12INSERT INTO t1 VALUES(2, Point(2,2), ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))')); 13INSERT INTO t1 VALUES(3, Point(3,3), ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))')); 14INSERT INTO t1 VALUES(4, Point(4,4), ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))')); 15INSERT INTO t1 VALUES(5, Point(5,5), ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')); 16INSERT INTO t1 VALUES(6, Point(6,6), ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))')); 17INSERT INTO t1 VALUES(7, Point(7,7), ST_GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))')); 18INSERT INTO t1 VALUES(8, Point(8,8), ST_GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))')); 19INSERT INTO t1 VALUES(9, Point(9,9), ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); 20 21INSERT INTO t1 SELECT * FROM t1; 22INSERT INTO t1 SELECT * FROM t1; 23INSERT INTO t1 SELECT * FROM t1; 24INSERT INTO t1 SELECT * FROM t1; 25 26INSERT INTO t1 SELECT * FROM t1; 27INSERT INTO t1 SELECT * FROM t1; 28INSERT INTO t1 SELECT * FROM t1; 29INSERT INTO t1 SELECT * FROM t1; 30INSERT INTO t1 SELECT * FROM t1; 31 32# create spatial index 33CREATE SPATIAL INDEX idx ON t1(c2); 34 35SELECT COUNT(*) FROM t1; 36 37SET @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))'); 38SELECT COUNT(*) FROM t1 WHERE MBRWithin(t1.c2, @g1); 39 40SET @g1 = ST_GeomFromText('Polygon((10 10,10 800,800 800,800 10,10 10))'); 41SELECT COUNT(*) FROM t1 WHERE MBRWithin(t1.c2, @g1); 42 43ALTER TABLE t1 DROP INDEX idx, ADD SPATIAL INDEX idx3(c2); 44 45SET SESSION debug="+d,row_merge_instrument_log_check_flush"; 46ALTER TABLE t1 DROP INDEX idx3, ADD SPATIAL INDEX idx4(c2), ADD SPATIAL INDEX idx5(c3); 47 48# Clean up. 49DROP TABLE t1; 50