diff options
Diffstat (limited to 'mysql-test/suite/innodb_gis/r/repeatable_spatial.result')
| -rw-r--r-- | mysql-test/suite/innodb_gis/r/repeatable_spatial.result | 174 |
1 files changed, 174 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb_gis/r/repeatable_spatial.result b/mysql-test/suite/innodb_gis/r/repeatable_spatial.result new file mode 100644 index 00000000..065312f6 --- /dev/null +++ b/mysql-test/suite/innodb_gis/r/repeatable_spatial.result @@ -0,0 +1,174 @@ +CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL, +c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL) +ENGINE=InnoDB; +ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC); +ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC); +ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on Polygon'; +ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry'; +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'), +ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), +ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); +SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_AsText(c4),ST_AsText(c5) FROM tab; +c1 ST_AsText(c2) ST_AsText(c3) ST_AsText(c4) ST_AsText(c5) +1 POINT(10 10) LINESTRING(5 5,20 20,30 30) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) +SELECT COUNT(*) FROM tab; +COUNT(*) +1 +"In connection 1" +connect con1,localhost,root,,; +connection con1; +SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SELECT @@tx_isolation; +@@tx_isolation +REPEATABLE-READ +SELECT COUNT(*) FROM tab; +COUNT(*) +1 +SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_AsText(c4),ST_AsText(c5) FROM tab; +c1 ST_AsText(c2) ST_AsText(c3) ST_AsText(c4) ST_AsText(c5) +1 POINT(10 10) LINESTRING(5 5,20 20,30 30) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) +"In connection 2" +connect con2,localhost,root,,; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +SELECT @@tx_isolation; +@@tx_isolation +READ-COMMITTED +START TRANSACTION; +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'), +ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'), +ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))')); +SET @g1 = ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'); +SET @g2 = ST_GeomFromText('POINT(10 10)'); +UPDATE tab SET C5 = ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))') +WHERE MBREquals(tab.c4, @g1) AND MBREquals(tab.c2, @g2); +"In connection 1" +connection con1; +SET @g3 = ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'); +SELECT c1,ST_AsText(c2),ST_AsText(c4),ST_AsText(c5) FROM tab WHERE MBREquals(tab.c5, @g3); +c1 ST_AsText(c2) ST_AsText(c4) ST_AsText(c5) +SELECT COUNT(*) FROM tab; +COUNT(*) +1 +START TRANSACTION; +SELECT COUNT(*) FROM tab; +COUNT(*) +1 +"In connection 2" +connection con2; +SELECT COUNT(*) FROM tab; +COUNT(*) +2 +COMMIT; +disconnect con2; +"In connection 1" +connection con1; +SELECT COUNT(*) FROM tab; +COUNT(*) +1 +SET @g4 = ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'); +SELECT ST_AsText(c5) FROM tab WHERE MBREquals(tab.c5, @g4); +ST_AsText(c5) +COMMIT; +SELECT ST_AsText(c5) FROM tab WHERE MBREquals(tab.c5, @g4); +ST_AsText(c5) +POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) +SELECT COUNT(*) FROM tab; +COUNT(*) +2 +SELECT c1,ST_AsText(c2),ST_AsText(c4),ST_AsText(c5) FROM tab; +c1 ST_AsText(c2) ST_AsText(c4) ST_AsText(c5) +1 POINT(10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) +2 POINT(20 20) POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) +disconnect con1; +connection default; +DROP TABLE tab; +CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL, +c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL) +ENGINE=InnoDB; +ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC); +ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC); +ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on Polygon'; +ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry'; +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'), +ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), +ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); +SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_AsText(c4),ST_AsText(c5) FROM tab; +c1 ST_AsText(c2) ST_AsText(c3) ST_AsText(c4) ST_AsText(c5) +1 POINT(10 10) LINESTRING(5 5,20 20,30 30) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) +SELECT COUNT(*) FROM tab; +COUNT(*) +1 +"In connection 1" +connect con1,localhost,root,,; +connection con1; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SELECT @@tx_isolation; +@@tx_isolation +REPEATABLE-READ +SELECT COUNT(*) FROM tab; +COUNT(*) +1 +SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_AsText(c4),ST_AsText(c5) FROM tab; +c1 ST_AsText(c2) ST_AsText(c3) ST_AsText(c4) ST_AsText(c5) +1 POINT(10 10) LINESTRING(5 5,20 20,30 30) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) +"In connection 2" +connect con2,localhost,root,,; +connection con2; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +SELECT @@tx_isolation; +@@tx_isolation +READ-COMMITTED +START TRANSACTION; +INSERT INTO tab(c1,c2,c3,c4,c5) +VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'), +ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'), +ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))')); +SET @g1 = ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'); +SET @g2 = ST_GeomFromText('POINT(10 10)'); +UPDATE tab SET C5 = ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))') +WHERE MBREquals(tab.c4, @g1) AND MBREquals(tab.c2, @g2); +"In connection 1" +connection con1; +SET @g3 = ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'); +SELECT c1,ST_AsText(c2),ST_AsText(c4),ST_AsText(c5) FROM tab WHERE MBREquals(tab.c5, @g3); +c1 ST_AsText(c2) ST_AsText(c4) ST_AsText(c5) +SELECT COUNT(*) FROM tab; +COUNT(*) +1 +START TRANSACTION; +SELECT COUNT(*) FROM tab; +COUNT(*) +1 +"In connection 2" +connection con2; +SELECT COUNT(*) FROM tab; +COUNT(*) +2 +COMMIT; +disconnect con2; +"In connection 1" +connection con1; +SELECT COUNT(*) FROM tab; +COUNT(*) +1 +SET @g4 = ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'); +SELECT ST_AsText(c5) FROM tab WHERE MBREquals(tab.c5, @g4); +ST_AsText(c5) +COMMIT; +SELECT ST_AsText(c5) FROM tab WHERE MBREquals(tab.c5, @g4); +ST_AsText(c5) +POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) +SELECT COUNT(*) FROM tab; +COUNT(*) +2 +SELECT c1,ST_AsText(c2),ST_AsText(c4),ST_AsText(c5) FROM tab; +c1 ST_AsText(c2) ST_AsText(c4) ST_AsText(c5) +1 POINT(10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) +2 POINT(20 20) POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) +disconnect con1; +connection default; +DROP TABLE tab; |