diff options
Diffstat (limited to 'mysql-test/suite/innodb_gis/t/create_spatial_index.test')
| -rw-r--r-- | mysql-test/suite/innodb_gis/t/create_spatial_index.test | 1175 |
1 files changed, 1175 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb_gis/t/create_spatial_index.test b/mysql-test/suite/innodb_gis/t/create_spatial_index.test new file mode 100644 index 00000000000..d25a2e79793 --- /dev/null +++ b/mysql-test/suite/innodb_gis/t/create_spatial_index.test @@ -0,0 +1,1175 @@ +# ********************************************************* +# Test Multiple Spatial Indexes on compression table +# Test spatial index with table having primary key column +# Test Spatial index with Create Index different clauses +# Test Spatial index with spatial relationship functions +# Test Spatial index with MBR spatial relationship functions +# Test Spatial index columns with DML & SELECT queries +# Test Spatial index with procedures +# Test Delete & Update & check status of the table +# Test spatial index with table have no primary key column +# Test spatial index with table have no auto_increment +# Test spatial index with check constraint +# ********************************************************** +--source include/have_innodb.inc +--source include/have_innodb_16k.inc +--source include/have_geometry.inc + + +# Check spatial index functionality on compress table with Primary key +CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL, +c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL) +ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; + +# Check spatial index functionality with Create Index clause options +CREATE SPATIAL INDEX idx1 on tab(c2 ASC); +CREATE SPATIAL INDEX idx2 on tab(c3 DESC) COMMENT 'wl6968'; +CREATE SPATIAL INDEX idx3 on tab(c4 ASC) KEY_BLOCK_SIZE=8 ; +CREATE SPATIAL INDEX idx4 on tab(c5 DESC) KEY_BLOCK_SIZE=4 +COMMENT 'Spatial index on Geometry type column'; + +# Check index type +SHOW INDEXES FROM tab; + +# Populate some spatial data +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'), +ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), +ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); + + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'), +ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'), +ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))')); + + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'), +ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'), +ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))')); + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'), +ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'), +ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))')); + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'), +ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'), +ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))')); + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), +ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'), +ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))')); + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), +ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), +ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), +ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), +ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); + + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'), +ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'), +ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))')); + + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'), +ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'), +ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))')); + +ANALYZE TABLE tab; + +# Check the spatial relationship between 2 GIS shapes + +# Test the MBRContains +SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRContains(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRContains(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); + +# Test the MBRWithin +SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRWithin(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); + +# Test the ST_Crosses +SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE ST_Crosses(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING( 10 10,30 30,40 40)'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_CRosses(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE ST_Crosses(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); + +# Test the MBRDisjoint +SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRDisjoint(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); + +# Test the MBREquals +SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBREquals(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); + +# Test the MBRintersects +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRintersects(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRintersects(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); + +# Test the Overelaps +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBROverlaps(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING(7 1,30 30,1010 3010,1010 2010,3010 3010,4010 4010,5010 5010 )'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBROverlaps(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); + +# Test the ST_Touches +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE ST_Touches(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING( 100 100,200 200,300 300)'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE ST_Touches(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); + +# Test the MBRContains +SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRContains(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); + +SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRWithin(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); + +# Test the MBRDisjoint +SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRDisjoint(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); + +# Test the MBREquals +SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBREquals(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); + +# Test the MBRintersects +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRintersects(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRintersects(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); + +# Test the MBROverelaps +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBROverlaps(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); + +# Test the MBRTouches +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRTouches(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1); + +# Test with Procedure +delimiter |; + +CREATE PROCEDURE proc_wl6968() +BEGIN + +SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRContains(tab.c4, @g1); +EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); + +END | + +delimiter ;| + +CALL proc_wl6968(); + +# Test the Delete & Update +SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +DELETE FROM tab WHERE MBRContains(tab.c4, @g1); + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +CHECK TABLE tab; + +SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)'); + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +DELETE FROM tab WHERE MBRContains(tab.c4, @g1); + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +CHECK TABLE tab; + +SET @g1 = ST_GeomFromText('POLYGON((100 200,1010 1010,1020 1020,500 300,300 200,100 300,100 200))'); + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; + +DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; + +CHECK TABLE tab; + +SET @g1 = ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'); + +SET @g2 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); + +UPDATE tab SET C4 = @g2 WHERE ST_Crosses(tab.c4, @g1); + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g2) ORDER BY c1; + +CHECK TABLE tab; + +# Cleanup +DROP TABLE tab; +DROP PROCEDURE proc_wl6968; + +# End of Testcase compress table with Primary key + +# Check spatial index functionality on compress table No Primary key +CREATE TABLE tab(c1 int ,c2 POINT NOT NULL, +c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL) +ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; + +# Check spatial index functionality with Create Index clause options +CREATE SPATIAL INDEX idx1 on tab(c2 ASC); +CREATE SPATIAL INDEX idx2 on tab(c3 DESC) COMMENT 'wl6968'; +CREATE SPATIAL INDEX idx3 on tab(c4 ASC) KEY_BLOCK_SIZE=2 ; +CREATE SPATIAL INDEX idx4 on tab(c5 DESC) KEY_BLOCK_SIZE=8 +COMMENT 'Spatial index on Geometry type column'; + +# Check index type +SHOW INDEXES FROM tab; + +# Populate some spatial data +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'), +ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), +ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); + + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'), +ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'), +ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))')); + + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'), +ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'), +ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))')); + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'), +ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'), +ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))')); + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'), +ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'), +ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))')); + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), +ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'), +ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))')); + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), +ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), +ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), +ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), +ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); + + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'), +ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'), +ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))')); + + +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'), +ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'), +ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))')); + +ANALYZE TABLE tab; + +# Check the spatial relationship between 2 GIS shapes + +# Test the MBRContains +SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRContains(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRContains(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); + +# Test the MBRWithin +SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRWithin(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); + +# Test the ST_Crosses +SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE ST_Crosses(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING( 10 10,30 30,40 40)'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_CRosses(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE ST_Crosses(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); + +# Test the MBRDisjoint +SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRDisjoint(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); + +# Test the MBREquals +SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBREquals(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); + +# Test the MBRintersects +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRintersects(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRintersects(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); + +# Test the Overelaps +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBROverlaps(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING(7 1,30 30,1010 3010,1010 2010,3010 3010,4010 4010,5010 5010 )'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBROverlaps(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); + +# Test the ST_Touches +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE ST_Touches(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING( 100 100,200 200,300 300)'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE ST_Touches(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); + +# Test the MBRContains +SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRContains(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); + +SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRWithin(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); + +# Test the MBRDisjoint +SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRDisjoint(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); + +# Test the MBREquals +SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBREquals(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); + +# Test the MBRintersects +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRintersects(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRintersects(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); + +# Test the MBROverelaps +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBROverlaps(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); + +# Test the MBRTouches +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRTouches(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1); + +# Test with Procedure +delimiter |; + +CREATE PROCEDURE proc_wl6968() +BEGIN + +SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBREquals(tab.c4, @g1); +EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); + +END | + +delimiter ;| + +CALL proc_wl6968(); + +# Test the Delete & Update +SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; + +DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; + +CHECK TABLE tab; + +SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))'); + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; + +DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; + +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); + +SET @g2 = ST_GeomFromText( 'POLYGON((1 1,2 2,3 3,10 3,5 1,1 1))'); + +UPDATE tab SET C4 = @g2 WHERE MBROverlaps(tab.c4, @g1); + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +CHECK TABLE tab; + +# Cleanup +DROP TABLE tab; +DROP PROCEDURE proc_wl6968; + +# End of Testcase compress table No Primary key + +# Check spatial index functionality on compress table with auto_increment +CREATE TABLE tab(c1 int AUTO_INCREMENT PRIMARY KEY,c2 POINT NOT NULL, +c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL) +ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; + +# Check spatial index functionality with Create Index clause options +CREATE SPATIAL INDEX idx1 on tab(c2 ASC); +CREATE SPATIAL INDEX idx2 on tab(c3 DESC) COMMENT 'wl6968'; +CREATE SPATIAL INDEX idx3 on tab(c4 ASC) KEY_BLOCK_SIZE=16 ; +CREATE SPATIAL INDEX idx4 on tab(c5 DESC) KEY_BLOCK_SIZE=16 +COMMENT 'Spatial index on Geometry type column'; + +# Check index type +SHOW INDEXES FROM tab; + +# Populate some spatial data +INSERT INTO tab(c2,c3,c4,c5) +VALUES(ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'), +ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), +ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); + + +INSERT INTO tab(c2,c3,c4,c5) +VALUES(ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'), +ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'), +ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))')); + + +INSERT INTO tab(c2,c3,c4,c5) +VALUES(ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'), +ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'), +ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))')); + +INSERT INTO tab(c2,c3,c4,c5) +VALUES(ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'), +ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'), +ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))')); + +INSERT INTO tab(c2,c3,c4,c5) +VALUES(ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'), +ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'), +ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))')); + +INSERT INTO tab(c2,c3,c4,c5) +VALUES(ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), +ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'), +ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))')); + +INSERT INTO tab(c2,c3,c4,c5) +VALUES(ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), +ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), +ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); + +INSERT INTO tab(c2,c3,c4,c5) +VALUES(ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), +ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), +ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); + + +INSERT INTO tab(c2,c3,c4,c5) +VALUES(ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'), +ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'), +ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))')); + + +INSERT INTO tab(c2,c3,c4,c5) +VALUES(ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'), +ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'), +ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))')); + +ANALYZE TABLE tab; + +# Check the spatial relationship between 2 GIS shapes + +# Test the MBRContains +SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRContains(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRContains(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); + +# Test the MBRWithin +SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRWithin(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); + +# Test the ST_Crosses +SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE ST_Crosses(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING( 10 10,30 30,40 40)'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_CRosses(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE ST_Crosses(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); + +# Test the MBRDisjoint +SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRDisjoint(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); + +# Test the MBREquals +SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBREquals(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); + +# Test the MBRintersects +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRintersects(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRintersects(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); + +# Test the Overelaps +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBROverlaps(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING(7 1,30 30,1010 3010,1010 2010,3010 3010,4010 4010,5010 5010 )'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBROverlaps(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); + +# Test the ST_Touches +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE ST_Touches(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING( 100 100,200 200,300 300)'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE ST_Touches(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); + +# Test the MBRContains +SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRContains(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); + +SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRWithin(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); + +# Test the MBRDisjoint +SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRDisjoint(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); + +# Test the MBREquals +SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBREquals(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); + +# Test the MBRintersects +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRintersects(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); + +SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRintersects(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); + +# Test the MBROverelaps +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBROverlaps(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); + +# Test the MBRTouches +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); + +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; + +EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') +WHERE MBRTouches(tab.c4, @g1); + +EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1); + +# Test the Delete & Update +SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; + +DELETE FROM tab WHERE MBREquals(tab.c4, @g1); + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; + +CHECK TABLE tab; + +SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +DELETE FROM tab WHERE MBRIntersects(tab.c4, @g1); + +SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; + +CHECK TABLE tab; + +# Cleanup +DROP TABLE tab; + +# End of Testcase compress table with Auto_increment + +# Test check constraint on spatial column +CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(c1 > 0) ) ENGINE=InnoDB; + +CREATE SPATIAL INDEX idx1 ON tab(c1) ; + +SHOW CREATE TABLE tab; + +SHOW INDEX FROM tab; + +set @g1 = ST_GeomFromText('POINT(-1 -2)'); + +SELECT ST_AsText(c1) FROM tab; + +DROP table tab; + +# repro case for bug#20451454 - FAILING ASSERTION: LOW_MATCH +# < DTUPLE_GET_N_FIELDS_CMP(TUPLE) +create table `t1`(`a` geometry not null,`b` linestring not null, +primary key (`b`(192),`a`(141)),spatial key (`b`)) engine=innodb; +insert into `t1` values( + point(1,1), + linestring(point(1,1),point(1,1)) +); + +# --error ER_GIS_INVALID_DATA +--error ER_BAD_NULL_ERROR +insert into `t1` values +( + polygon( + linestring(point(1,1),point(1,1)), + linestring(point(1,1),point(11,1)) + ), + linestring(point(1,1),point(1,1)) +); +select 1 from t1 where st_intersects( + geometrycollection(point(1,-1)),b +); +drop table t1; + +# Reproduce case for updating statistic after droping stats info table. +CREATE TABLE t1(c1 POINT NOT NULL); +DROP TABLE mysql.innodb_table_stats; +CALL mtr.add_suppression("InnoDB: Table `mysql`.`innodb_table_stats` not found."); +CALL mtr.add_suppression("InnoDB: Fetch of persistent statistics requested for table `test`.`t1` but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead."); +CREATE SPATIAL INDEX idx2 ON t1(c1); + +DROP TABLE t1; +CREATE TABLE mysql.innodb_table_stats ( + database_name varchar(64) COLLATE utf8_bin NOT NULL, + table_name varchar(64) COLLATE utf8_bin NOT NULL, + last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + n_rows bigint(20) unsigned NOT NULL, + clustered_index_size bigint(20) unsigned NOT NULL, + sum_of_other_index_sizes bigint(20) unsigned NOT NULL, + PRIMARY KEY (`database_name`,`table_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; |