From a2a2e32c02643a0cec111511220227703fda1cd5 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Mon, 1 Jul 2024 20:15:00 +0200 Subject: Merging upstream version 1:11.4.2. Signed-off-by: Daniel Baumann --- .../suite/innodb_gis/t/alter_spatial_index.test | 12 --- mysql-test/suite/innodb_gis/t/check_rtree.test | 2 +- .../suite/innodb_gis/t/create_spatial_index.test | 97 +++++++++++----------- mysql-test/suite/innodb_gis/t/geometry.test | 2 +- .../suite/innodb_gis/t/repeatable_spatial.test | 8 +- mysql-test/suite/innodb_gis/t/rollback.test | 4 +- mysql-test/suite/innodb_gis/t/rtree_compress.test | 2 +- .../suite/innodb_gis/t/rtree_concurrent_srch.test | 14 ++-- .../suite/innodb_gis/t/rtree_create_inplace.test | 2 +- mysql-test/suite/innodb_gis/t/rtree_multi_pk.test | 1 + mysql-test/suite/innodb_gis/t/rtree_optimize.test | 44 ++++++++++ 11 files changed, 111 insertions(+), 77 deletions(-) create mode 100644 mysql-test/suite/innodb_gis/t/rtree_optimize.test (limited to 'mysql-test/suite/innodb_gis/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; -- cgit v1.2.3