From 3f619478f796eddbba6e39502fe941b285dd97b1 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 20:00:34 +0200 Subject: Adding upstream version 1:10.11.6. Signed-off-by: Daniel Baumann --- mysql-test/main/gis-rtree.test | 1012 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 1012 insertions(+) create mode 100644 mysql-test/main/gis-rtree.test (limited to 'mysql-test/main/gis-rtree.test') diff --git a/mysql-test/main/gis-rtree.test b/mysql-test/main/gis-rtree.test new file mode 100644 index 00000000..c4ff7799 --- /dev/null +++ b/mysql-test/main/gis-rtree.test @@ -0,0 +1,1012 @@ +-- source include/have_geometry.inc + +# +# test of rtree (using with spatial data) +# + +CREATE TABLE t1 ( + fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + g GEOMETRY NOT NULL, + SPATIAL KEY(g) +); + +SHOW CREATE TABLE t1; + +let $1=150; +let $2=150; +while ($1) +{ + eval INSERT INTO t1 (g) VALUES (GeomFromText('LineString($1 $1, $2 $2)')); + dec $1; + inc $2; +} + +SELECT count(*) FROM t1; +EXPLAIN SELECT fid, AsText(g) FROM t1 WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))')); +SELECT fid, AsText(g) FROM t1 WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))')); + +DROP TABLE t1; + +CREATE TABLE t2 ( + fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + g GEOMETRY NOT NULL +); + +let $1=10; +while ($1) +{ + let $2=10; + while ($2) + { + eval INSERT INTO t2 (g) VALUES (LineString(Point($1 * 10 - 9, $2 * 10 - 9), Point($1 * 10, $2 * 10))); + dec $2; + } + dec $1; +} + +ALTER TABLE t2 ADD SPATIAL KEY(g); +SHOW CREATE TABLE t2; +SELECT count(*) FROM t2; +EXPLAIN SELECT fid, AsText(g) FROM t2 WHERE Within(g, + GeomFromText('Polygon((40 40,60 40,60 60,40 60,40 40))')); +SELECT fid, AsText(g) FROM t2 WHERE Within(g, + GeomFromText('Polygon((40 40,60 40,60 60,40 60,40 40))')); + +let $1=10; +while ($1) +{ + let $2=10; + while ($2) + { + eval DELETE FROM t2 WHERE Within(g, Envelope(GeometryFromWKB(Point($1 * 10 - 9, $2 * 10 - 9), 0))); + SELECT count(*) FROM t2; + dec $2; + } + dec $1; +} + +DROP TABLE t2; + +CREATE TABLE t1 (a geometry NOT NULL, SPATIAL (a)); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); +check table t1; +analyze table t1; +drop table t1; + +# +# The following crashed gis +# + +CREATE TABLE t1 ( + fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + g GEOMETRY NOT NULL, + SPATIAL KEY(g) +); + +INSERT INTO t1 (g) VALUES (GeomFromText('LineString(1 2, 2 3)')),(GeomFromText('LineString(1 2, 2 4)')); +#select * from t1 where g GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'); +SELECT 1 FROM t1 WHERE a > GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'); +EXPLAIN SELECT 1 FROM t1 WHERE a >= GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'); +SELECT 1 FROM t1 WHERE a >= GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'); +DROP TABLE t1; + + +--echo # +--echo # Bug #51357: crash when using handler commands on spatial indexes +--echo # + +CREATE TABLE t1(a GEOMETRY NOT NULL,SPATIAL INDEX a(a)); +HANDLER t1 OPEN; +HANDLER t1 READ a FIRST; +HANDLER t1 READ a NEXT; +HANDLER t1 READ a PREV; +HANDLER t1 READ a LAST; +HANDLER t1 CLOSE; + +# second crash fixed when the tree has changed since the last search. +HANDLER t1 OPEN; +HANDLER t1 READ a FIRST; +INSERT INTO t1 VALUES (GeomFromText('Polygon((40 40,60 40,60 60,40 60,40 40))')); +--echo # should not crash +--disable_result_log +HANDLER t1 READ a NEXT; +--enable_result_log +HANDLER t1 CLOSE; + +DROP TABLE t1; + + +--echo End of 5.0 tests. + + +--echo # +--echo # Bug #57323/11764487: myisam corruption with insert ignore +--echo # and invalid spatial data +--echo # + +CREATE TABLE t1(a POINT NOT NULL, b GEOMETRY NOT NULL, + SPATIAL KEY(a), SPATIAL KEY(b)); +INSERT INTO t1 VALUES(GEOMFROMTEXT("point (0 0)"), GEOMFROMTEXT("point (1 1)")); +--error ER_CANT_CREATE_GEOMETRY_OBJECT +INSERT IGNORE INTO t1 SET a=GEOMFROMTEXT("point (-6 0)"), b=GEOMFROMTEXT("error"); +--error ER_CANT_CREATE_GEOMETRY_OBJECT +INSERT IGNORE INTO t1 SET a=GEOMFROMTEXT("point (-6 0)"), b=NULL; +SELECT ASTEXT(a), ASTEXT(b) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(a INT NOT NULL, b GEOMETRY NOT NULL, + KEY(a), SPATIAL KEY(b)); +INSERT INTO t1 VALUES(0, GEOMFROMTEXT("point (1 1)")); +--error ER_CANT_CREATE_GEOMETRY_OBJECT +INSERT IGNORE INTO t1 SET a=0, b=GEOMFROMTEXT("error"); +--error ER_CANT_CREATE_GEOMETRY_OBJECT +INSERT IGNORE INTO t1 SET a=1, b=NULL; +SELECT a, ASTEXT(b) FROM t1; +DROP TABLE t1; + +--echo End of 5.1 tests + +# +# MDEV-4521 MBRContains, MBRWithin no longer work with geometries of different type. +# +CREATE TABLE t1 ( + l LINESTRING NOT NULL, + SPATIAL KEY(l) +); + +INSERT INTO t1 VALUES(GeomFromText('LINESTRING(0 0, 1 1)')); +INSERT INTO t1 VALUES(GeomFromText('LINESTRING(1 1, 2 2)')); +INSERT INTO t1 VALUES(GeomFromText('LINESTRING(2 2, 3 3)')); + +SELECT COUNT(*) FROM t1 IGNORE INDEX(l) WHERE MBRContains(l, GEOMFROMTEXT('POINT(0 0)')); +SELECT COUNT(*) FROM t1 IGNORE INDEX(l) WHERE MBRWithin(GEOMFROMTEXT('POINT(0.5 0.5)'), l); + +SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRContains(l, GEOMFROMTEXT('POINT(0 0)')); +SELECT COUNT(*) FROM t1 FORCE INDEX(l) WHERE MBRWithin(GEOMFROMTEXT('POINT(0.5 0.5)'), l); + +DROP TABLE t1; + + +--echo # +--echo # Start of 10.1 tests +--echo # + +--echo # +--echo # MDEV-8239 Reverse spatial operations OP(const, field) do not get optimized +--echo # +CREATE TABLE t1 (a GEOMETRY NOT NULL, SPATIAL KEY(a)); +INSERT INTO t1 VALUES (Point(1,2)),(Point(1,3)); +EXPLAIN SELECT * FROM t1 WHERE MBRINTERSECTS(a,Point(1,2)); +EXPLAIN SELECT * FROM t1 WHERE ST_INTERSECTS(a,Point(1,2)); +EXPLAIN SELECT * FROM t1 WHERE MBRINTERSECTS(Point(1,2),a); +EXPLAIN SELECT * FROM t1 WHERE ST_INTERSECTS(Point(1,2),a); +DROP TABLE t1; + +--echo # +--echo # MDEV-8610 "WHERE CONTAINS(indexed_geometry_column,1)" causes full table scan +--echo # +CREATE TABLE t1 (a GEOMETRY NOT NULL, SPATIAL KEY(a)); +INSERT INTO t1 VALUES (Point(1,1)),(Point(2,2)),(Point(3,3)); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +EXPLAIN SELECT * FROM t1 WHERE CONTAINS(a,1); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +EXPLAIN SELECT * FROM t1 WHERE CONTAINS(a,1.0); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +EXPLAIN SELECT * FROM t1 WHERE CONTAINS(a,1e0); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +EXPLAIN SELECT * FROM t1 WHERE CONTAINS(a,TIME'00:00:00'); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +EXPLAIN SELECT * FROM t1 WHERE CONTAINS(a,DATE'2001-01-01'); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +EXPLAIN SELECT * FROM t1 WHERE CONTAINS(a,TIMESTAMP'2001-01-01 00:00:00'); +DROP TABLE t1; + + +--echo # +--echo # End of 10.1 tests +--echo # -- cgit v1.2.3