diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-07-01 18:15:00 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-07-01 18:15:00 +0000 |
| commit | a2a2e32c02643a0cec111511220227703fda1cd5 (patch) | |
| tree | 69cc2b631234c2a8e026b9cd4d72676c61c594df /mysql-test/suite/innodb_gis | |
| parent | Releasing progress-linux version 1:10.11.8-1~progress7.99u1. (diff) | |
| download | mariadb-a2a2e32c02643a0cec111511220227703fda1cd5.tar.xz mariadb-a2a2e32c02643a0cec111511220227703fda1cd5.zip | |
Merging upstream version 1:11.4.2.
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/suite/innodb_gis')
23 files changed, 298 insertions, 181 deletions
diff --git a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result index 5365e7d0..100a5e94 100644 --- a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result +++ b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result @@ -759,17 +759,6 @@ ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), add spatial index(b), algorithm=copy; DROP table t1; -# -# BUG#20111575 ALTER TABLE...ADD SPATIAL INDEX...LOCK NONE IS REFUSED -# WITHOUT STATING A REASON -# -CREATE TABLE t1(p point NOT NULL) ENGINE=innodb; -ALTER TABLE t1 ADD SPATIAL INDEX(p), LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED -ALTER TABLE t1 ADD SPATIAL INDEX(p); -ALTER TABLE t1 FORCE, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED -DROP TABLE t1; create table t1 (p point not null default if(unix_timestamp()>10,POINT(1,1),LineString(Point(0,0),Point(1,1)))) ENGINE=innodb; set timestamp=10; insert into t1 values(default); diff --git a/mysql-test/suite/innodb_gis/r/check_rtree.result b/mysql-test/suite/innodb_gis/r/check_rtree.result index 0d7b25b6..10155fb5 100644 --- a/mysql-test/suite/innodb_gis/r/check_rtree.result +++ b/mysql-test/suite/innodb_gis/r/check_rtree.result @@ -1,5 +1,5 @@ create table t1 (i int, g geometry not null, spatial index (g))engine=innodb; -SET SESSION debug="+d,rtree_test_check_count"; +SET SESSION debug_dbug="+d,rtree_test_check_count"; insert into t1 values (1, POINT(1,1)); insert into t1 values (1, POINT(1.5,1.5)); insert into t1 values (1, POINT(3,3)); diff --git a/mysql-test/suite/innodb_gis/r/create_spatial_index.result b/mysql-test/suite/innodb_gis/r/create_spatial_index.result index d3c69294..30b41b41 100644 --- a/mysql-test/suite/innodb_gis/r/create_spatial_index.result +++ b/mysql-test/suite/innodb_gis/r/create_spatial_index.result @@ -57,10 +57,14 @@ ANALYZE TABLE tab; Table Op Msg_type Msg_text test.tab analyze status Engine-independent statistics collected test.tab analyze status OK +# 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; +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1+0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) @@ -74,7 +78,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) @@ -85,10 +89,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -99,10 +104,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) @@ -116,7 +122,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -127,6 +133,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -145,14 +152,15 @@ c1 ST_Astext(c4) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) @@ -166,7 +174,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -182,7 +190,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -194,10 +202,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) @@ -218,14 +227,15 @@ c1 ST_Astext(c4) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) @@ -239,7 +249,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) @@ -250,10 +260,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) @@ -267,7 +278,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -278,6 +289,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -296,14 +308,15 @@ c1 ST_Astext(c4) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) @@ -314,10 +327,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -333,7 +347,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -345,10 +359,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) @@ -359,10 +374,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) @@ -373,6 +389,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where +# Test with Procedure CREATE PROCEDURE proc_wl6968() BEGIN SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); @@ -383,11 +400,12 @@ EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); END | CALL proc_wl6968(); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; c1 ST_Astext(c4) @@ -486,6 +504,7 @@ ANALYZE TABLE tab; Table Op Msg_type Msg_text test.tab analyze status Engine-independent statistics collected test.tab analyze status OK +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -514,6 +533,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -528,6 +548,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -556,10 +577,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where; Using filesort +1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -574,10 +596,11 @@ c1 ST_Astext(c4) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -592,6 +615,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -623,6 +647,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -640,17 +665,18 @@ id select_type table type possible_keys key key_len ref rows Extra 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where; Using filesort +1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -679,6 +705,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -707,10 +734,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where; Using filesort +1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -725,10 +753,11 @@ c1 ST_Astext(c4) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -743,6 +772,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -774,6 +804,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -788,6 +819,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -802,6 +834,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where +# Test with Procedure CREATE PROCEDURE proc_wl6968() BEGIN SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); @@ -817,6 +850,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; c1 ST_Astext(c4) @@ -904,10 +938,11 @@ ANALYZE TABLE tab; Table Op Msg_type Msg_text test.tab analyze status Engine-independent statistics collected test.tab analyze status OK +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) @@ -921,7 +956,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) @@ -932,10 +967,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -946,10 +982,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) @@ -963,7 +1000,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -974,6 +1011,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -992,14 +1030,15 @@ c1 ST_Astext(c4) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) @@ -1010,10 +1049,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -1029,7 +1069,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -1041,10 +1081,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) @@ -1065,14 +1106,15 @@ c1 ST_Astext(c4) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) @@ -1086,7 +1128,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) @@ -1097,10 +1139,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) @@ -1114,7 +1157,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -1125,6 +1168,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra @@ -1143,14 +1187,15 @@ c1 ST_Astext(c4) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab ALL idx3 NULL NULL NULL 10 Using where +1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) @@ -1161,10 +1206,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -1180,7 +1226,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) @@ -1192,10 +1238,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) @@ -1206,10 +1253,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where +# 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort +1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) @@ -1220,6 +1268,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where +# 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; c1 ST_Astext(c4) @@ -1243,6 +1292,7 @@ CHECK TABLE tab; Table Op Msg_type Msg_text test.tab check status OK DROP TABLE tab; +# Test check constraint on spatial column CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(c1 > 0) ) ENGINE=InnoDB; ERROR HY000: Illegal parameter data types point and int for operation '>' CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(CAST(c1 AS BINARY) > 0) ) ENGINE=InnoDB; diff --git a/mysql-test/suite/innodb_gis/r/geometry.result b/mysql-test/suite/innodb_gis/r/geometry.result index c38e9e28..d4d4abe0 100644 --- a/mysql-test/suite/innodb_gis/r/geometry.result +++ b/mysql-test/suite/innodb_gis/r/geometry.result @@ -1093,7 +1093,7 @@ PARTITION pNorth VALUES IN (10,20,30) , PARTITION pEast VALUES IN (40,50,60) , PARTITION pWest VALUES IN (70,80,100) ); -ERROR 42000: The storage engine for the table doesn't support GEOMETRY +ERROR HY000: A BLOB field is not allowed in partition function #check start transaction commit & Rollback START TRANSACTION; DELETE FROM tab3; diff --git a/mysql-test/suite/innodb_gis/r/repeatable_spatial.result b/mysql-test/suite/innodb_gis/r/repeatable_spatial.result index 065312f6..561b1f77 100644 --- a/mysql-test/suite/innodb_gis/r/repeatable_spatial.result +++ b/mysql-test/suite/innodb_gis/r/repeatable_spatial.result @@ -19,8 +19,8 @@ COUNT(*) connect con1,localhost,root,,; connection con1; SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation REPEATABLE-READ SELECT COUNT(*) FROM tab; COUNT(*) @@ -32,8 +32,8 @@ c1 ST_AsText(c2) ST_AsText(c3) ST_AsText(c4) ST_AsText(c5) connect con2,localhost,root,,; connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED START TRANSACTION; INSERT INTO tab(c1,c2,c3,c4,c5) @@ -106,8 +106,8 @@ COUNT(*) connect con1,localhost,root,,; connection con1; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation REPEATABLE-READ SELECT COUNT(*) FROM tab; COUNT(*) @@ -119,8 +119,8 @@ c1 ST_AsText(c2) ST_AsText(c3) ST_AsText(c4) ST_AsText(c5) connect con2,localhost,root,,; connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED START TRANSACTION; INSERT INTO tab(c1,c2,c3,c4,c5) diff --git a/mysql-test/suite/innodb_gis/r/rollback.result b/mysql-test/suite/innodb_gis/r/rollback.result index 1efe5d28..1f10dc00 100644 --- a/mysql-test/suite/innodb_gis/r/rollback.result +++ b/mysql-test/suite/innodb_gis/r/rollback.result @@ -407,7 +407,7 @@ start transaction; update t1 set a=point(5,5), b=point(5,5), c=5 where i < 3; ERROR 23000: Duplicate entry '5' for key 'c' rollback; -set session debug="+d,row_mysql_crash_if_error"; +set session debug_dbug="+d,row_mysql_crash_if_error"; update t1 set a=point(5,5), b=point(5,5), c=5 where i < 3; ERROR HY000: Lost connection to server during query insert into t1 values(5, point(5,5), point(5,5), 5); diff --git a/mysql-test/suite/innodb_gis/r/rtree.result b/mysql-test/suite/innodb_gis/r/rtree.result index 2fd39f9c..9ddcb841 100644 --- a/mysql-test/suite/innodb_gis/r/rtree.result +++ b/mysql-test/suite/innodb_gis/r/rtree.result @@ -11,11 +11,11 @@ test.t1 analyze status OK set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); explain select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL g NULL NULL NULL 5 Using where +1 SIMPLE t1 range g g 34 NULL 5 Using where select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1); ST_astext(t1.g) -POINT(1 1) POINT(1.5 1.5) +POINT(1 1) set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); delete from t1 where MBRWithin(t1.g, @g1); check table t1; @@ -157,11 +157,11 @@ test.t1 analyze status OK set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); explain select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL g NULL NULL NULL 5 Using where +1 SIMPLE t1 range g g 34 NULL 5 Using where select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1); ST_astext(t1.g) -POINT(1 1) POINT(1.5 1.5) +POINT(1 1) set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); delete from t1 where MBRWithin(t1.g, @g1); check table t1; diff --git a/mysql-test/suite/innodb_gis/r/rtree_compress.result b/mysql-test/suite/innodb_gis/r/rtree_compress.result index 9b646985..5f210ca1 100644 --- a/mysql-test/suite/innodb_gis/r/rtree_compress.result +++ b/mysql-test/suite/innodb_gis/r/rtree_compress.result @@ -43,7 +43,7 @@ select count(*) from t1 where MBRWithin(t1.c2, @g1); count(*) 0 SET @saved_dbug = @@SESSION.debug_dbug; -SET DEBUG='+d,page_copy_rec_list_start_compress_fail'; +SET DEBUG_DBUG='+d,page_copy_rec_list_start_compress_fail'; delete from t1; select count(*) from t1 where MBRWithin(t1.c2, @g1); count(*) diff --git a/mysql-test/suite/innodb_gis/r/rtree_concurrent_srch.result b/mysql-test/suite/innodb_gis/r/rtree_concurrent_srch.result index 81b82691..9d25d0e9 100644 --- a/mysql-test/suite/innodb_gis/r/rtree_concurrent_srch.result +++ b/mysql-test/suite/innodb_gis/r/rtree_concurrent_srch.result @@ -58,8 +58,8 @@ insert into t1 select * from t1; insert into t1 select * from t1; connection a; set session transaction isolation level serializable; -select @@tx_isolation; -@@tx_isolation +select @@transaction_isolation; +@@transaction_isolation SERIALIZABLE start transaction; set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))'); @@ -69,8 +69,8 @@ count(*) connect b,localhost,root,,; set session transaction isolation level serializable; set session innodb_lock_wait_timeout = 1; -select @@tx_isolation; -@@tx_isolation +select @@transaction_isolation; +@@transaction_isolation SERIALIZABLE insert into t1 select * from t1; insert into t1 select * from t1; @@ -92,8 +92,8 @@ connection b; select @@innodb_lock_wait_timeout; @@innodb_lock_wait_timeout 1 -select @@tx_isolation; -@@tx_isolation +select @@transaction_isolation; +@@transaction_isolation SERIALIZABLE insert into t1 select * from t1; ERROR HY000: Lock wait timeout exceeded; try restarting transaction @@ -120,8 +120,8 @@ insert into t1 select * from t1; insert into t1 select * from t1; connection a; set session transaction isolation level serializable; -select @@tx_isolation; -@@tx_isolation +select @@transaction_isolation; +@@transaction_isolation SERIALIZABLE start transaction; set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))'); @@ -131,8 +131,8 @@ count(*) connection b; set session transaction isolation level serializable; set session innodb_lock_wait_timeout = 1; -select @@tx_isolation; -@@tx_isolation +select @@transaction_isolation; +@@transaction_isolation SERIALIZABLE insert into t1 select * from t1; insert into t1 select * from t1; @@ -158,8 +158,8 @@ connection b; select @@innodb_lock_wait_timeout; @@innodb_lock_wait_timeout 1 -select @@tx_isolation; -@@tx_isolation +select @@transaction_isolation; +@@transaction_isolation SERIALIZABLE INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 105, 200 105)')); ERROR HY000: Lock wait timeout exceeded; try restarting transaction @@ -227,8 +227,8 @@ end| CALL insert_t1(0, 1000); connection a; set session transaction isolation level serializable; -select @@tx_isolation; -@@tx_isolation +select @@transaction_isolation; +@@transaction_isolation SERIALIZABLE start transaction; set @g1 = ST_GeomFromText('Polygon((800 800, 800 1000, 1000 1000, 1000 800, 800 800))'); diff --git a/mysql-test/suite/innodb_gis/r/rtree_create_inplace.result b/mysql-test/suite/innodb_gis/r/rtree_create_inplace.result index 2016ce1b..9e0a3271 100644 --- a/mysql-test/suite/innodb_gis/r/rtree_create_inplace.result +++ b/mysql-test/suite/innodb_gis/r/rtree_create_inplace.result @@ -25,7 +25,7 @@ SELECT COUNT(*) FROM t1 WHERE MBRWithin(t1.c2, @g1); COUNT(*) 0 ALTER TABLE t1 DROP INDEX idx, ADD SPATIAL INDEX idx3(c2); -SET debug_dbug='+d,row_merge_instrument_log_check_flush'; +SET SESSION debug_dbug="+d,row_merge_instrument_log_check_flush"; ALTER TABLE t1 DROP INDEX idx3, ADD SPATIAL INDEX idx4(c2), ADD SPATIAL INDEX idx5(c3); ALTER TABLE t1 FORCE; DROP TABLE t1; diff --git a/mysql-test/suite/innodb_gis/r/rtree_multi_pk.result b/mysql-test/suite/innodb_gis/r/rtree_multi_pk.result index 3e6464df..bab710a5 100644 --- a/mysql-test/suite/innodb_gis/r/rtree_multi_pk.result +++ b/mysql-test/suite/innodb_gis/r/rtree_multi_pk.result @@ -11,11 +11,11 @@ test.t1 analyze status OK set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); explain select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL g NULL NULL NULL 5 Using where +1 SIMPLE t1 range g g 34 NULL 5 Using where select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1); ST_astext(t1.g) -POINT(1 1) POINT(1.5 1.5) +POINT(1 1) set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); delete from t1 where MBRWithin(t1.g, @g1); check table t1; @@ -63,10 +63,10 @@ name ST_AsText(square) small POLYGON((0 0,0 1,1 1,1 0,0 0)) SELECT name, ST_AsText(square) from t1 where MBRDisjoint(@p, square); name ST_AsText(square) -up3 POLYGON((0 3,0 5,2 5,2 3,0 3)) down3 POLYGON((0 -3,0 -1,2 -1,2 -3,0 -3)) -right3 POLYGON((3 0,3 2,5 2,5 0,3 0)) left3 POLYGON((-3 0,-3 2,-1 2,-1 0,-3 0)) +right3 POLYGON((3 0,3 2,5 2,5 0,3 0)) +up3 POLYGON((0 3,0 5,2 5,2 3,0 3)) SELECT name, ST_AsText(square) from t1 where MBREquals(@p, square); name ST_AsText(square) SELECT name, ST_AsText(square) from t1 where MBRIntersects(@p, square); diff --git a/mysql-test/suite/innodb_gis/r/rtree_optimize.result b/mysql-test/suite/innodb_gis/r/rtree_optimize.result new file mode 100644 index 00000000..4abdb5f2 --- /dev/null +++ b/mysql-test/suite/innodb_gis/r/rtree_optimize.result @@ -0,0 +1,44 @@ +CREATE TABLE t ( +id INT AUTO_INCREMENT, +c BINARY(226) DEFAULT '', +s POINT NOT NULL, +PRIMARY KEY(id,c) +) ENGINE=InnoDB; +INSERT INTO t (s) VALUES +(POINTFromText('POINT(0.78 0.72)')),(POINTFromText('POINT(0.44 0.21)')), +(POINTFromText('POINT(0.93 0.56)')),(POINTFromText('POINT(0.57 0.21)')), +(POINTFromText('POINT(0.12 0.65)')),(POINTFromText('POINT(0.20 0.96)')), +(POINTFromText('POINT(0.99 0.71)')),(POINTFromText('POINT(0.27 0.23)')), +(POINTFromText('POINT(0.68 0.14)')),(POINTFromText('POINT(0.20 0.05)')), +(POINTFromText('POINT(0.47 0.57)')),(POINTFromText('POINT(0.89 0.79)')), +(POINTFromText('POINT(0.09 0.57)')),(POINTFromText('POINT(0.58 0.52)')), +(POINTFromText('POINT(0.73 0.32)')),(POINTFromText('POINT(0.87 0.35)')), +(POINTFromText('POINT(0.60 0.12)')),(POINTFromText('POINT(0.14 0.17)')), +(POINTFromText('POINT(0.76 0.29)')),(POINTFromText('POINT(0.60 0.35)')), +(POINTFromText('POINT(0.48 0.69)')),(POINTFromText('POINT(0.79 0.45)')), +(POINTFromText('POINT(0.85 0.11)')),(POINTFromText('POINT(0.59 0.99)')), +(POINTFromText('POINT(0.95 0.18)')),(POINTFromText('POINT(0.78 0.49)')), +(POINTFromText('POINT(0.11 0.22)')),(POINTFromText('POINT(0.26 0.85)')), +(POINTFromText('POINT(0.28 0.10)')),(POINTFromText('POINT(0.45 0.25)')), +(POINTFromText('POINT(0.70 0.40)')),(POINTFromText('POINT(0.65 0.86)')), +(POINTFromText('POINT(0.69 0.98)')),(POINTFromText('POINT(0.56 0.11)')), +(POINTFromText('POINT(0.94 0.59)')),(POINTFromText('POINT(0.19 0.94)')), +(POINTFromText('POINT(0.82 0.85)')),(POINTFromText('POINT(0.74 0.07)')), +(POINTFromText('POINT(0.33 0.48)')),(POINTFromText('POINT(0.37 0.37)')), +(POINTFromText('POINT(0.40 0.08)')),(POINTFromText('POINT(0.45 0.74)')), +(POINTFromText('POINT(0.57 0.07)')),(POINTFromText('POINT(0.36 0.11)')), +(POINTFromText('POINT(0.94 0.60)')),(POINTFromText('POINT(0.75 0.76)')), +(POINTFromText('POINT(0.92 0.56)')),(POINTFromText('POINT(0.88 0.52)')), +(POINTFromText('POINT(0.49 0.24)')),(POINTFromText('POINT(0.96 0.08)')), +(POINTFromText('POINT(0.93 0.99)')),(POINTFromText('POINT(0.88 0.31)')), +(POINTFromText('POINT(0.93 0.78)')),(POINTFromText('POINT(0.62 0.50)')), +(POINTFromText('POINT(0.54 0.53)')),(POINTFromText('POINT(0.66 0.83)')), +(POINTFromText('POINT(0.21 0.87)')),(POINTFromText('POINT(0.42 0.28)')), +(POINTFromText('POINT(0.80 0.84)')),(POINTFromText('POINT(0.39 0.68)')), +(POINTFromText('POINT(0.05 0.24)')),(POINTFromText('POINT(0.05 0.58)')); +ALTER TABLE t ADD SPATIAL INDEX(s); +OPTIMIZE TABLE t; +Table Op Msg_type Msg_text +test.t optimize note Table does not support optimize, doing recreate + analyze instead +test.t optimize status OK +DROP TABLE t; diff --git a/mysql-test/suite/innodb_gis/t/alter_spatial_index.test b/mysql-test/suite/innodb_gis/t/alter_spatial_index.test index 6f30b38b..50364b97 100644 --- a/mysql-test/suite/innodb_gis/t/alter_spatial_index.test +++ b/mysql-test/suite/innodb_gis/t/alter_spatial_index.test @@ -747,18 +747,6 @@ alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), add spatial index(b), algorithm=copy; DROP table t1; ---echo # ---echo # BUG#20111575 ALTER TABLE...ADD SPATIAL INDEX...LOCK NONE IS REFUSED ---echo # WITHOUT STATING A REASON ---echo # -CREATE TABLE t1(p point NOT NULL) ENGINE=innodb; ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t1 ADD SPATIAL INDEX(p), LOCK=NONE; -ALTER TABLE t1 ADD SPATIAL INDEX(p); ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t1 FORCE, LOCK=NONE; -DROP TABLE t1; - create table t1 (p point not null default if(unix_timestamp()>10,POINT(1,1),LineString(Point(0,0),Point(1,1)))) ENGINE=innodb; set timestamp=10; --error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD diff --git a/mysql-test/suite/innodb_gis/t/check_rtree.test b/mysql-test/suite/innodb_gis/t/check_rtree.test index 09bba50d..a52c6d0b 100644 --- a/mysql-test/suite/innodb_gis/t/check_rtree.test +++ b/mysql-test/suite/innodb_gis/t/check_rtree.test @@ -9,7 +9,7 @@ create table t1 (i int, g geometry not null, spatial index (g))engine=innodb; # Turn on the geometry data print. -SET SESSION debug="+d,rtree_test_check_count"; +SET SESSION debug_dbug="+d,rtree_test_check_count"; # Insert values. insert into t1 values (1, POINT(1,1)); diff --git a/mysql-test/suite/innodb_gis/t/create_spatial_index.test b/mysql-test/suite/innodb_gis/t/create_spatial_index.test index 5278292b..178041d0 100644 --- a/mysql-test/suite/innodb_gis/t/create_spatial_index.test +++ b/mysql-test/suite/innodb_gis/t/create_spatial_index.test @@ -94,9 +94,11 @@ ANALYZE TABLE tab; # Check the spatial relationship between 2 GIS shapes -# Test the MBRContains +--echo # Test the MBRContains SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); +# Show plan if we cannot use index order +EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1+0; 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; @@ -117,7 +119,7 @@ WHERE MBRContains(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); -# Test the MBRWithin +--echo # 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; @@ -129,7 +131,7 @@ WHERE MBRWithin(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); -# Test the ST_Crosses +--echo # 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; @@ -152,7 +154,7 @@ WHERE ST_Crosses(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); -# Test the MBRDisjoint +--echo # 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; @@ -164,7 +166,7 @@ WHERE MBRDisjoint(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); -# Test the MBREquals +--echo # 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; @@ -176,7 +178,6 @@ 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; @@ -199,7 +200,7 @@ WHERE MBRintersects(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); -# Test the Overelaps +--echo # 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; @@ -222,7 +223,7 @@ WHERE MBROverlaps(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); -# Test the ST_Touches +--echo # 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; @@ -245,7 +246,7 @@ WHERE ST_Touches(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); -# Test the MBRContains +--echo # 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; @@ -268,7 +269,7 @@ WHERE MBRWithin(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); -# Test the MBRDisjoint +--echo # 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; @@ -280,7 +281,7 @@ WHERE MBRDisjoint(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); -# Test the MBREquals +--echo # 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; @@ -292,7 +293,7 @@ WHERE MBREquals(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); -# Test the MBRintersects +--echo # 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; @@ -315,7 +316,7 @@ WHERE MBRintersects(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); -# Test the MBROverelaps +--echo # 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; @@ -327,7 +328,7 @@ WHERE MBROverlaps(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); -# Test the MBRTouches +--echo # 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; @@ -339,7 +340,7 @@ WHERE MBRTouches(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1); -# Test with Procedure +--echo # Test with Procedure delimiter |; CREATE PROCEDURE proc_wl6968() @@ -357,7 +358,7 @@ delimiter ;| CALL proc_wl6968(); -# Test the Delete & Update +--echo # 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; @@ -478,7 +479,7 @@ ANALYZE TABLE tab; # Check the spatial relationship between 2 GIS shapes -# Test the MBRContains +--echo # 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; @@ -501,7 +502,7 @@ WHERE MBRContains(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); -# Test the MBRWithin +--echo # 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; @@ -513,7 +514,7 @@ WHERE MBRWithin(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); -# Test the ST_Crosses +--echo # 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; @@ -536,7 +537,7 @@ WHERE ST_Crosses(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); -# Test the MBRDisjoint +--echo # 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; @@ -548,7 +549,7 @@ WHERE MBRDisjoint(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); -# Test the MBREquals +--echo # 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; @@ -560,7 +561,7 @@ WHERE MBREquals(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); -# Test the MBRintersects +--echo # 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; @@ -583,7 +584,7 @@ WHERE MBRintersects(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); -# Test the Overelaps +--echo # 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; @@ -606,7 +607,7 @@ WHERE MBROverlaps(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); -# Test the ST_Touches +--echo # 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; @@ -629,7 +630,7 @@ WHERE ST_Touches(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); -# Test the MBRContains +--echo # 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; @@ -652,7 +653,7 @@ WHERE MBRWithin(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); -# Test the MBRDisjoint +--echo # 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; @@ -664,7 +665,7 @@ WHERE MBRDisjoint(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); -# Test the MBREquals +--echo # 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; @@ -676,7 +677,7 @@ WHERE MBREquals(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); -# Test the MBRintersects +--echo # 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; @@ -699,7 +700,7 @@ WHERE MBRintersects(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); -# Test the MBROverelaps +--echo # 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; @@ -711,7 +712,7 @@ WHERE MBROverlaps(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); -# Test the MBRTouches +--echo # 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; @@ -723,7 +724,7 @@ WHERE MBRTouches(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1); -# Test with Procedure +--echo # Test with Procedure delimiter |; CREATE PROCEDURE proc_wl6968() @@ -741,7 +742,7 @@ delimiter ;| CALL proc_wl6968(); -# Test the Delete & Update +--echo # 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; @@ -850,7 +851,7 @@ ANALYZE TABLE tab; # Check the spatial relationship between 2 GIS shapes -# Test the MBRContains +--echo # 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; @@ -873,7 +874,7 @@ WHERE MBRContains(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); -# Test the MBRWithin +--echo # 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; @@ -885,7 +886,7 @@ WHERE MBRWithin(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); -# Test the ST_Crosses +--echo # 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; @@ -908,7 +909,7 @@ WHERE ST_Crosses(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); -# Test the MBRDisjoint +--echo # 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; @@ -920,7 +921,7 @@ WHERE MBRDisjoint(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); -# Test the MBREquals +--echo # 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; @@ -932,7 +933,7 @@ WHERE MBREquals(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); -# Test the MBRintersects +--echo # 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; @@ -955,7 +956,7 @@ WHERE MBRintersects(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); -# Test the Overelaps +--echo # 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; @@ -978,7 +979,7 @@ WHERE MBROverlaps(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); -# Test the ST_Touches +--echo # 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; @@ -1001,7 +1002,7 @@ WHERE ST_Touches(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); -# Test the MBRContains +--echo # 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; @@ -1024,7 +1025,7 @@ WHERE MBRWithin(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); -# Test the MBRDisjoint +--echo # 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; @@ -1036,7 +1037,7 @@ WHERE MBRDisjoint(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); -# Test the MBREquals +--echo # 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; @@ -1048,7 +1049,7 @@ WHERE MBREquals(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); -# Test the MBRintersects +--echo # 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; @@ -1071,7 +1072,7 @@ WHERE MBRintersects(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); -# Test the MBROverelaps +--echo # 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; @@ -1083,7 +1084,7 @@ WHERE MBROverlaps(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); -# Test the MBRTouches +--echo # 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; @@ -1095,7 +1096,7 @@ WHERE MBRTouches(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1); -# Test the Delete & Update +--echo # 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; @@ -1124,7 +1125,7 @@ DROP TABLE tab; # End of Testcase compress table with Auto_increment -# Test check constraint on spatial column +--echo # Test check constraint on spatial column --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(c1 > 0) ) ENGINE=InnoDB; CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(CAST(c1 AS BINARY) > 0) ) ENGINE=InnoDB; diff --git a/mysql-test/suite/innodb_gis/t/geometry.test b/mysql-test/suite/innodb_gis/t/geometry.test index 4e8f0f72..fe19deb3 100644 --- a/mysql-test/suite/innodb_gis/t/geometry.test +++ b/mysql-test/suite/innodb_gis/t/geometry.test @@ -648,7 +648,7 @@ CREATE TABLE child (id GEOMETRY, parent_id GEOMETRY, ) ENGINE=INNODB; --echo #check partition table support ---error 1178 +--error ER_BLOB_FIELD_IN_PART_FUNC_ERROR CREATE TABLE emp2( id GEOMETRY NOT NULL, store_name VARCHAR(30), diff --git a/mysql-test/suite/innodb_gis/t/repeatable_spatial.test b/mysql-test/suite/innodb_gis/t/repeatable_spatial.test index 3152911b..16372dc1 100644 --- a/mysql-test/suite/innodb_gis/t/repeatable_spatial.test +++ b/mysql-test/suite/innodb_gis/t/repeatable_spatial.test @@ -40,7 +40,7 @@ connection con1; SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; # Record count should be 1 SELECT COUNT(*) FROM tab; @@ -54,7 +54,7 @@ connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; START TRANSACTION; @@ -155,7 +155,7 @@ connection con1; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; # Record count should be 1 SELECT COUNT(*) FROM tab; @@ -169,7 +169,7 @@ connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; START TRANSACTION; diff --git a/mysql-test/suite/innodb_gis/t/rollback.test b/mysql-test/suite/innodb_gis/t/rollback.test index a0998669..8483b356 100644 --- a/mysql-test/suite/innodb_gis/t/rollback.test +++ b/mysql-test/suite/innodb_gis/t/rollback.test @@ -463,8 +463,8 @@ rollback; # Test partial update rollback after recovered. # Crash the server in partial update. ---write_line restart $MYSQLTEST_VARDIR/tmp/mysqld.1.expect -set session debug="+d,row_mysql_crash_if_error"; +--write_line restart $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +set session debug_dbug="+d,row_mysql_crash_if_error"; --error 2013 update t1 set a=point(5,5), b=point(5,5), c=5 where i < 3; diff --git a/mysql-test/suite/innodb_gis/t/rtree_compress.test b/mysql-test/suite/innodb_gis/t/rtree_compress.test index 56690b88..cd6cf903 100644 --- a/mysql-test/suite/innodb_gis/t/rtree_compress.test +++ b/mysql-test/suite/innodb_gis/t/rtree_compress.test @@ -53,7 +53,7 @@ set @g1 = ST_GeomFromText('Polygon((10 10,10 800,800 800,800 10,10 10))'); select count(*) from t1 where MBRWithin(t1.c2, @g1); SET @saved_dbug = @@SESSION.debug_dbug; -SET DEBUG='+d,page_copy_rec_list_start_compress_fail'; +SET DEBUG_DBUG='+d,page_copy_rec_list_start_compress_fail'; delete from t1; select count(*) from t1 where MBRWithin(t1.c2, @g1); SET debug_dbug = @saved_dbug; diff --git a/mysql-test/suite/innodb_gis/t/rtree_concurrent_srch.test b/mysql-test/suite/innodb_gis/t/rtree_concurrent_srch.test index c4a6448f..017c650f 100644 --- a/mysql-test/suite/innodb_gis/t/rtree_concurrent_srch.test +++ b/mysql-test/suite/innodb_gis/t/rtree_concurrent_srch.test @@ -79,7 +79,7 @@ insert into t1 select * from t1; # Connection 'a' will place predicate lock on almost all pages connection a; set session transaction isolation level serializable; -select @@tx_isolation; +select @@transaction_isolation; start transaction; set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))'); select count(*) from t1 where MBRwithin(t1.c2, @g1); @@ -89,7 +89,7 @@ connect (b,localhost,root,,); set session transaction isolation level serializable; set session innodb_lock_wait_timeout = 1; -select @@tx_isolation; +select @@transaction_isolation; insert into t1 select * from t1; insert into t1 select * from t1; @@ -115,7 +115,7 @@ select count(*) from t1 where MBRwithin(t1.c2, @g1); connection b; select @@innodb_lock_wait_timeout; -select @@tx_isolation; +select @@transaction_isolation; --error ER_LOCK_WAIT_TIMEOUT insert into t1 select * from t1; @@ -146,7 +146,7 @@ insert into t1 select * from t1; # Connection 'a' will place predicate lock on almost all pages connection a; set session transaction isolation level serializable; -select @@tx_isolation; +select @@transaction_isolation; start transaction; set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))'); select count(*) from t1 where MBRwithin(t1.c2, @g1); @@ -156,7 +156,7 @@ connection b; set session transaction isolation level serializable; set session innodb_lock_wait_timeout = 1; -select @@tx_isolation; +select @@transaction_isolation; insert into t1 select * from t1; insert into t1 select * from t1; @@ -181,7 +181,7 @@ select count(*) from t1 where MBRIntersects(t1.c2, @g1); connection b; select @@innodb_lock_wait_timeout; -select @@tx_isolation; +select @@transaction_isolation; # this should conflict with the "MBRIntersects" predicate lock in session "a" --error ER_LOCK_WAIT_TIMEOUT @@ -264,7 +264,7 @@ CALL insert_t1(0, 1000); # Connection 'a' will place predicate lock on root and last leaf page connection a; set session transaction isolation level serializable; -select @@tx_isolation; +select @@transaction_isolation; start transaction; set @g1 = ST_GeomFromText('Polygon((800 800, 800 1000, 1000 1000, 1000 800, 800 800))'); select count(*) from t1 where MBRwithin(t1.c2, @g1); diff --git a/mysql-test/suite/innodb_gis/t/rtree_create_inplace.test b/mysql-test/suite/innodb_gis/t/rtree_create_inplace.test index 73469368..2626c576 100644 --- a/mysql-test/suite/innodb_gis/t/rtree_create_inplace.test +++ b/mysql-test/suite/innodb_gis/t/rtree_create_inplace.test @@ -37,7 +37,7 @@ SELECT COUNT(*) FROM t1 WHERE MBRWithin(t1.c2, @g1); ALTER TABLE t1 DROP INDEX idx, ADD SPATIAL INDEX idx3(c2); -SET debug_dbug='+d,row_merge_instrument_log_check_flush'; +SET SESSION debug_dbug="+d,row_merge_instrument_log_check_flush"; ALTER TABLE t1 DROP INDEX idx3, ADD SPATIAL INDEX idx4(c2), ADD SPATIAL INDEX idx5(c3); ALTER TABLE t1 FORCE; diff --git a/mysql-test/suite/innodb_gis/t/rtree_multi_pk.test b/mysql-test/suite/innodb_gis/t/rtree_multi_pk.test index f606e569..567e1694 100644 --- a/mysql-test/suite/innodb_gis/t/rtree_multi_pk.test +++ b/mysql-test/suite/innodb_gis/t/rtree_multi_pk.test @@ -66,6 +66,7 @@ INSERT INTO t1 VALUES("left3", ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 SET @p = ST_GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'); SELECT name, ST_AsText(square) from t1 where MBRContains(@p, square); +--sorted_result SELECT name, ST_AsText(square) from t1 where MBRDisjoint(@p, square); SELECT name, ST_AsText(square) from t1 where MBREquals(@p, square); SELECT name, ST_AsText(square) from t1 where MBRIntersects(@p, square); diff --git a/mysql-test/suite/innodb_gis/t/rtree_optimize.test b/mysql-test/suite/innodb_gis/t/rtree_optimize.test new file mode 100644 index 00000000..c3de282d --- /dev/null +++ b/mysql-test/suite/innodb_gis/t/rtree_optimize.test @@ -0,0 +1,44 @@ +--source include/have_innodb.inc + +CREATE TABLE t ( + id INT AUTO_INCREMENT, + c BINARY(226) DEFAULT '', + s POINT NOT NULL, + PRIMARY KEY(id,c) +) ENGINE=InnoDB; +INSERT INTO t (s) VALUES + (POINTFromText('POINT(0.78 0.72)')),(POINTFromText('POINT(0.44 0.21)')), + (POINTFromText('POINT(0.93 0.56)')),(POINTFromText('POINT(0.57 0.21)')), + (POINTFromText('POINT(0.12 0.65)')),(POINTFromText('POINT(0.20 0.96)')), + (POINTFromText('POINT(0.99 0.71)')),(POINTFromText('POINT(0.27 0.23)')), + (POINTFromText('POINT(0.68 0.14)')),(POINTFromText('POINT(0.20 0.05)')), + (POINTFromText('POINT(0.47 0.57)')),(POINTFromText('POINT(0.89 0.79)')), + (POINTFromText('POINT(0.09 0.57)')),(POINTFromText('POINT(0.58 0.52)')), + (POINTFromText('POINT(0.73 0.32)')),(POINTFromText('POINT(0.87 0.35)')), + (POINTFromText('POINT(0.60 0.12)')),(POINTFromText('POINT(0.14 0.17)')), + (POINTFromText('POINT(0.76 0.29)')),(POINTFromText('POINT(0.60 0.35)')), + (POINTFromText('POINT(0.48 0.69)')),(POINTFromText('POINT(0.79 0.45)')), + (POINTFromText('POINT(0.85 0.11)')),(POINTFromText('POINT(0.59 0.99)')), + (POINTFromText('POINT(0.95 0.18)')),(POINTFromText('POINT(0.78 0.49)')), + (POINTFromText('POINT(0.11 0.22)')),(POINTFromText('POINT(0.26 0.85)')), + (POINTFromText('POINT(0.28 0.10)')),(POINTFromText('POINT(0.45 0.25)')), + (POINTFromText('POINT(0.70 0.40)')),(POINTFromText('POINT(0.65 0.86)')), + (POINTFromText('POINT(0.69 0.98)')),(POINTFromText('POINT(0.56 0.11)')), + (POINTFromText('POINT(0.94 0.59)')),(POINTFromText('POINT(0.19 0.94)')), + (POINTFromText('POINT(0.82 0.85)')),(POINTFromText('POINT(0.74 0.07)')), + (POINTFromText('POINT(0.33 0.48)')),(POINTFromText('POINT(0.37 0.37)')), + (POINTFromText('POINT(0.40 0.08)')),(POINTFromText('POINT(0.45 0.74)')), + (POINTFromText('POINT(0.57 0.07)')),(POINTFromText('POINT(0.36 0.11)')), + (POINTFromText('POINT(0.94 0.60)')),(POINTFromText('POINT(0.75 0.76)')), + (POINTFromText('POINT(0.92 0.56)')),(POINTFromText('POINT(0.88 0.52)')), + (POINTFromText('POINT(0.49 0.24)')),(POINTFromText('POINT(0.96 0.08)')), + (POINTFromText('POINT(0.93 0.99)')),(POINTFromText('POINT(0.88 0.31)')), + (POINTFromText('POINT(0.93 0.78)')),(POINTFromText('POINT(0.62 0.50)')), + (POINTFromText('POINT(0.54 0.53)')),(POINTFromText('POINT(0.66 0.83)')), + (POINTFromText('POINT(0.21 0.87)')),(POINTFromText('POINT(0.42 0.28)')), + (POINTFromText('POINT(0.80 0.84)')),(POINTFromText('POINT(0.39 0.68)')), + (POINTFromText('POINT(0.05 0.24)')),(POINTFromText('POINT(0.05 0.58)')); +ALTER TABLE t ADD SPATIAL INDEX(s); +OPTIMIZE TABLE t; +# Cleanup +DROP TABLE t; |