mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-02-04 21:02:17 +01:00
Code Issues Projects Releases Packages Wiki Activity Actions
mariadb/mysql-test/suite/innodb_gis/t/point_basic.test
Marko Mäkelä 9608773f75 MDEV-4750 follow-up: Reduce disabling innodb_stats_persistent 
This essentially reverts commit 4e89ec6692
and only disables InnoDB persistent statistics for tests where it is
desirable. By design, InnoDB persistent statistics will not be updated
except by ANALYZE TABLE or by STATS_AUTO_RECALC.

The internal transactions that update persistent InnoDB statistics
in background tasks (with innodb_stats_auto_recalc=ON) may cause
nondeterministic query plans or interfere with some tests that deal
with other InnoDB internals, such as the purge of transaction history.
2021-08-31 13:55:02 +03:00

859 lines
36 KiB
Text
Raw Permalink Blame History

#********************************************************
# wl6942: Store certain GIS data type in the row rather
# than as BLOB.
#
#********************************************************
--source include/have_geometry.inc
--source include/have_innodb.inc
--echo #
--echo # Test for basic POINT operation
--echo #
CREATE TABLE gis_point(p1 POINT, p2 POINT) ENGINE=InnoDB;
INSERT INTO gis_point VALUES
(ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(0 0)')),
(ST_PointFromText('POINT(10 -20)'), ST_PointFromText('POINT(10 -20)')),
(ST_PointFromText('POINT(3.1415926 3.535897)'), ST_PointFromText('POINT(-3.932626 -3.488272)')),
(ST_PointFromText('POINT(-111.9876 234.1357)'), ST_PointFromText('POINT(-957.1914 958.1919)'));
SELECT ST_X(p1), ST_Y(p2) FROM gis_point;
DROP TABLE gis_point;
--echo #
--echo # Test when POINT is not on any indexes
--echo #
CREATE TABLE gis_point(p1 POINT, p2 POINT) ENGINE=InnoDB;
INSERT INTO gis_point VALUES
(ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(100.32374832 101.23741821)')),
(ST_PointFromText('POINT(105.34523342 103.18492302)'), ST_PointFromText('POINT(100.32374832 101.23741821)')),
(ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(200.32247328 101.86728201)')),
(ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(100.32374832 101.98527111)'));
--replace_column 9 #
EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
SELECT ST_AsText(p1) FROM gis_point WHERE p1 = p2;
SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE p1 = ST_PointFromText('POINT(100.32374832 101.23741821)');
SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE p2 = ST_PointFromText('POINT(200.32247328 101.86728201)');
DROP TABLE gis_point;
--echo #
--echo # Test some ALTER TABLE operations on POINT tables
--echo #
CREATE TABLE t1 (
p POINT NOT NULL,
g GEOMETRY NOT NULL
) ENGINE=InnoDB;
INSERT INTO t1 VALUES(ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 11)'));
INSERT INTO t1 VALUES(ST_PointFromText('POINT(1 1)'), ST_PointFromText('POINT(10 12)'));
INSERT INTO t1 VALUES(ST_PointFromText('POINT(1 0)'), ST_PointFromText('POINT(10 13)'));
INSERT INTO t1 VALUES(ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(10 14)'));
SELECT ST_AsText(p), ST_AsText(g) FROM t1;
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g');
ALTER TABLE t1 ADD COLUMN p1 POINT, ADD COLUMN p2 POINT, ADD KEY(p);
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g','p1','p2');
--echo # NOT NULL POINT will use ''
SELECT count(*) AS `Expect 4` FROM t1 WHERE p1 = '';
SELECT count(*) AS `Expect 4` FROM t1 WHERE p2 = '';
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p1;
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p2;
ALTER TABLE t1 DROP COLUMN p2;
--echo # NULLABLE POINT will use NULL
ALTER TABLE t1 ADD COLUMN p2 POINT, ADD KEY(p2);
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g','p1','p2');
SELECT count(*) AS `Expect 4` FROM t1 WHERE p2 IS NULL;
UPDATE t1 SET p2 = ST_PointFromText('POINT(10 20)');
UPDATE t1 SET p1 = ST_PointFromText('POINT(10 20)');
ALTER TABLE t1 DROP COLUMN p2;
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g','p1','p2');
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p1;
INSERT INTO t1 VALUES (ST_PointFromText('POINT(0.5 0.5)'), ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 19)'));
SELECT ST_AsText(p1) FROM t1;
DELETE FROM t1 WHERE p1 = ST_PointFromText('POINT(10 19)');
SELECT ST_AsText(p1) FROM t1;
--echo # Add spatial keys on the table
--error ER_SPATIAL_CANT_HAVE_NULL
ALTER TABLE t1 ADD SPATIAL(p), ADD SPATIAL(p1);
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))'));
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))'));
--echo # Drop spatial keys on the table
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE t1 DROP KEY p, DROP KEY p1;
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))'));
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))'));
TRUNCATE t1;
ALTER TABLE t1 DROP COLUMN p, DROP COLUMN p1;
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
ALTER TABLE t1 ADD COLUMN p POINT, ADD COLUMN p1 POINT;
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.5 0.5)'), ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 19)'));
INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.6 0.6)'), ST_PointFromText('POINT(1 1)'), ST_PointFromText('POINT(10 20)'));
INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.7 0.7)'), ST_PointFromText('POINT(1 0)'), ST_PointFromText('POINT(10 21)'));
INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.8 0.8)'), ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(10 22)'));
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))'));
SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))'));
ALTER TABLE t1 DROP COLUMN p1, ADD COLUMN p1 POINT, CHANGE COLUMN p pp POINT AFTER p1;
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('pp','g','p1');
UPDATE t1 SET p1 = ST_PointFromText('POINT(5 5)');
SELECT ST_AsText(pp), ST_AsText(p1) FROM t1;
--error ER_SPATIAL_CANT_HAVE_NULL
ALTER TABLE t1 ADD SPATIAL(p1), ADD SPATIAL(pp), ALGORITHM = COPY;
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/t1' AND c.name IN ('pp','g','p1');
SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))')) ORDER BY pp;
SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE MBRWithin(pp, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))')) ORDER BY pp;
DROP TABLE t1;
--echo #
--echo # Test when the POINT is on B-TREE
--echo #
CREATE TABLE gis_point(fid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, p POINT, KEY(p)) ENGINE=InnoDB STATS_PERSISTENT=0;
INSERT INTO gis_point VALUES
(101, ST_PointFromText('POINT(10 10)')),
(102, ST_PointFromText('POINT(20 10)')),
(103, ST_PointFromText('POINT(20 20)')),
(104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
SELECT ST_AsText(p) FROM gis_point;
SELECT ST_AsText(p) FROM gis_point WHERE p = ST_PointFromText('POINT(20 20)');
INSERT INTO gis_point VALUES
(201, ST_PointFromText('POINT(100.32374832 101.23741821)')),
(202, ST_PointFromText('POINT(102.43287328 100.23489233)')),
(203, ST_PointFromText('POINT(101.43284962 100.45892392)')),
(204, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)')))),
(205, ST_PointFromText('POINT(101.43284962 100.45892392)')),
(206, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)'))));
-- echo 'The ORDER BY will use filesort'
--replace_column 9 #
EXPLAIN SELECT ST_AsText(p) FROM gis_point ORDER BY p;
SELECT ST_AsText(p) FROM gis_point ORDER BY p;
SELECT ST_AsText(p), COUNT(*) FROM gis_point GROUP BY p;
TRUNCATE gis_point;
INSERT INTO gis_point VALUES
(101, ST_PointFromText('POINT(10 10)')),
(102, ST_PointFromText('POINT(20 10)')),
(103, ST_PointFromText('POINT(20 20)')),
(104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
--echo # Check if we can create prefix index on POINT
ALTER TABLE gis_point ADD COLUMN g POINT, ADD KEY(g(8));
INSERT INTO gis_point VALUES
(105, ST_PointFromText('POINT(25 15)'), ST_PointFromText('POINT(100 100)')),
(106, ST_PointFromText('POINT(25 25)'), ST_PointFromText('POINT(110 110)'));
SELECT ST_AsText(p) FROM gis_point;
SELECT ST_AsText(p), ST_AsText(g) FROM gis_point WHERE g IS NULL;
UPDATE gis_point SET g = ST_PointFromText('POINT(200 200)') WHERE g IS NULL OR g = ST_PointFromText('POINT(110 110)');
SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POINT(200 200)');
--echo # Check the information schema tables
SELECT table_name, column_name, data_type, column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='gis_point' AND (column_name = 'p' OR column_name = 'g');
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name LIKE 'test/g%' AND c.name IN ('p','g');
SELECT length(p) FROM gis_point;
ALTER TABLE gis_point DROP COLUMN g, ALGORITHM = COPY;
TRUNCATE gis_point;
INSERT INTO gis_point VALUES
(101, ST_PointFromText('POINT(10 10)')),
(102, ST_PointFromText('POINT(20 10)')),
(103, ST_PointFromText('POINT(20 20)')),
(104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
--echo # Check if we can create prefix index on POINT
ALTER TABLE gis_point ADD COLUMN g POINT, ADD KEY(g(8));
INSERT INTO gis_point VALUES
(105, ST_PointFromText('POINT(25 15)'), ST_PointFromText('POINT(100 100)')),
(106, ST_PointFromText('POINT(25 25)'), ST_PointFromText('POINT(110 110)'));
SELECT ST_AsText(p) FROM gis_point;
SELECT ST_AsText(p), ST_AsText(g) FROM gis_point WHERE g IS NULL;
UPDATE gis_point SET g = ST_PointFromText('POINT(200 200)') WHERE g IS NULL OR g = ST_PointFromText('POINT(110 110)');
SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POINT(200 200)');
CHECK TABLE gis_point;
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/gis_point' AND c.name IN ('p','g');
DROP TABLE gis_point;
--echo #
--echo # Check the mtype of other geometry data types should be 15
--echo #
CREATE TABLE g (
geom GEOMETRY NOT NULL,
l LINESTRING NOT NULL,
poly POLYGON NOT NULL,
mp MULTIPOINT NOT NULL,
ml MULTILINESTRING NOT NULL,
mpoly MULTIPOLYGON NOT NULL,
gc GEOMETRYCOLLECTION NOT NULL
) ENGINE=InnoDB;
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/g';
DROP TABLE g;
--echo #
--echo # check the mtype and len with CREATE TABLE AS
--echo #
CREATE TABLE t1 (
p POINT NOT NULL,
g GEOMETRY NOT NULL
) ENGINE=InnoDB;
INSERT INTO t1 VALUES(ST_PointFromText('POINT(10 10)'),ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
CREATE TABLE t2 ENGINE=InnoDB AS SELECT * FROM t1;
# Check the mtype and len of the table
SELECT t.name, c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name LIKE 'test/t%' AND c.name IN ('p','g');
# Cleanup
DROP TABLE t1,t2;
--echo #
--echo # Test when POINT is used in spatial index
--echo #
SET @ls1 = ST_GeomFromText('LINESTRING(0 20, 10 0)');
SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 10 20)');
SET @ls3 = ST_GeomFromText('LINESTRING(20 -40, 21 -42)');
SET @ls4 = ST_GeomFromText('LINESTRING(20 -42, 21 -40)');
SET @poly1 = ST_GeomFromText('POLYGON((2 2, 2 10, 10 10, 10 2, 2 2))');
SET @poly2 = ST_GeomFromText('POLYGON((0 0, -5 0, -4 -1, -6 -15, -3 -15, 0 0))');
SET @poly3 = ST_GeomFromText('POLYGON((10.0 10.0, 20.5 20, 20.5 50, 32.0 64.0, 32.3 64.6, 5 60, 10 10))');
SET @poly4 = ST_GeomFromText('POLYGON((0 10, -10 10, -10 -10, 0 -10, 0 10))');
SET @p1 = ST_PointFromText('POINT(0 0)');
SET @mpoly = ST_GeomFromText('MULTIPOLYGON(((3 3, 3 16, 16 16, 16 3, 3 3)), ((10 10, 10 50, 50 50, 50 10, 10 10)))');
CREATE TABLE gis_point (p1 POINT NOT NULL, p2 POINT NOT NULL, SPATIAL KEY k1 (p1), SPATIAL KEY k2 (p2)) ENGINE=InnoDB;
INSERT INTO gis_point VALUES
(ST_PointFromText('POINT(1 2)'), ST_PointFromText('POINT(-1 -3)')),
(ST_PointFromText('POINT(2 4)'), ST_PointFromText('POINT(-2 -6)')),
(ST_PointFromText('POINT(3 6)'), ST_PointFromText('POINT(-3 -9)')),
(ST_PointFromText('POINT(4 8)'), ST_PointFromText('POINT(-4 -12)')),
(ST_PointFromText('POINT(5 10)'), ST_PointFromText('POINT(-5 -15)')),
(ST_PointFromText('POINT(6 12)'), ST_PointFromText('POINT(-6 -18)')),
(ST_PointFromText('POINT(7 14)'), ST_PointFromText('POINT(-7 -21)')),
(ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)')),
(ST_PointFromText('POINT(9 18)'), ST_PointFromText('POINT(-4 2)')),
(ST_PointFromText('POINT(10 21)'), ST_PointFromText('POINT(-6 3)')),
(ST_PointFromText('POINT(20.5 41)'), ST_PointFromText('POINT(-8 4)')),
(ST_PointFromText('POINT(26.25 57)'), ST_PointFromText('POINT(1 2)')),
(ST_PointFromText('POINT(32.1234 64.2468)'), ST_PointFromText('POINT(-1 -1)'));
CREATE TABLE gis_point1 SELECT * FROM gis_point;
# Check the mtype and len of the table, should be 14,25
SELECT c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/gis_point' AND c.name IN ('p1','p2');
SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point1;
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Intersection(@ls1, @ls2) = p1;
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1);
SELECT ST_AsText(p2) FROM gis_point1 WHERE ST_Contains(@poly2, p2);
SELECT ST_AsText(p2) FROM gis_point1 WHERE ST_Equals(p2, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Contains(@mpoly, p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly);
--echo # Check functions that use MBR, with line type data
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @ls1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @ls3);
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @ls1);
--echo # Check functions that use MBR, with polygon type data
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @poly1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @poly1);
--echo # Check functions that use MBR, with point type data
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @p1);
--echo # Check with MBR functions point type data
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @p1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1);
--echo # Check with MBR functions point polygon data
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @p1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1);
--echo # Check with MBR functions polygon type data
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @poly1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @poly1);
--echo # Check with MBR functions line type data
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @ls1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @ls1);
DROP TABLE gis_point1;
SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point;
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Intersection(@ls1, @ls2) = p1;
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
SELECT ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2);
SELECT ST_AsText(p2) FROM gis_point WHERE ST_Equals(p2, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Contains(@mpoly, p1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly);
--echo # Check functions that use MBR, with line type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls3);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @ls1);
--echo # Check functions that use MBR, with polygon type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
--echo # Check functions that use MBR, with point type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @p1);
--echo # Check with MBR functions point type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
--echo # Check with MBR functions point polygon data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
--echo # Check with MBR functions polygon type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @poly1);
--echo # Check with MBR functions line type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @ls1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
--echo # Modify the storage engine to Myisam, Check the spatial functions
ALTER TABLE gis_point ENGINE Myisam;
--echo # Check functions that use MBR, with line type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls3);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @ls1);
--echo # Check functions that use MBR, with polygon type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
--echo # Check functions that use MBR, with point type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @p1);
--echo # Check with MBR functions point type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
--echo # Check with MBR functions point polygon data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
--echo # Check with MBR functions polygon type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @poly1);
--echo # Check with MBR functions line type data
SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @ls1);
--echo # No matching records
SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls1);
SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
--echo # Modify the storage engine to InnoDB again, do following testing
ALTER TABLE gis_point ENGINE InnoDB;
CHECK TABLE gis_point;
-- echo The ORDER BY for spatial index will use filesort
--replace_column 9 #
EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
-- echo Try to do IDU on the table and verify the result
DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 41)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 41)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
CHECK TABLE gis_point;
-- echo Use a trx to test the IDU on the table and verify the result
START TRANSACTION;
DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 49)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 49)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
ROLLBACK;
SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 41)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
CHECK TABLE gis_point;
DROP TABLE gis_point;
--echo #
--echo # Test inserting/updating different type data into POINT field
--echo #
CREATE TABLE gis_point (i INT, p POINT) ENGINE=InnoDB;
CREATE TABLE geom (i INT, g GEOMETRY NOT NULL, SPATIAL KEY(g)) ENGINE=InnoDB;
INSERT INTO gis_point VALUES(0, ST_PointFromText('POINT(1 1)'));
INSERT INTO gis_point VALUES(1, ST_PointFromText('POINT(2 2)'));
INSERT INTO gis_point VALUES(2, NULL);
ALTER TABLE gis_point ADD COLUMN j INT, ALGORITHM = COPY;
SELECT t.name, c.name, c.mtype, c.prtype, c.len
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
ON c.table_id = t.table_id
WHERE t.name='test/gis_point' AND c.name='p';
SELECT i, ST_AsText(p) FROM gis_point;
UPDATE gis_point SET p = NULL WHERE p = ST_PointFromText('POINT(1 1)');
UPDATE gis_point SET p = ST_PointFromText('POINT(1 2)') WHERE p = ST_PointFromText('POINT(2 2)');
UPDATE gis_point SET p = ST_PointFromText('POINT(1 1)') WHERE p IS NULL;
SELECT i, ST_AsText(p) FROM gis_point;
INSERT INTO geom VALUES(0, ST_PointFromText('POINT(0 0)'));
INSERT INTO geom VALUES(1, ST_PointFromText('POINT(10 10)'));
INSERT INTO geom VALUES(2, ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'));
SELECT ST_AsText(g) FROM geom;
SELECT ST_AsText(p) FROM gis_point;
DELETE FROM geom WHERE g = ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
--echo From GEOMETRY to POINT, now ALL the data are POINT
ALTER TABLE geom MODIFY g POINT NOT NULL;
SHOW CREATE TABLE geom;
SELECT ST_AsText(g) FROM geom;
--echo From POINT to GEOMETRY, all data are POINT
ALTER TABLE geom MODIFY g GEOMETRY NOT NULL;
SHOW CREATE TABLE geom;
TRUNCATE TABLE geom;
--echo From GEOMETRY to POINT, the table is empty
ALTER TABLE geom MODIFY g POINT NOT NULL;
SHOW CREATE TABLE geom;
SELECT ST_AsText(g) FROM geom;
CHECK TABLE gis_point;
CHECK TABLE geom;
DROP TABLE gis_point, geom;
--echo #
--echo # Test when a geom field, such as POINT, is also a primary key
--echo #
CREATE TABLE t1 (
a INT NOT NULL,
p POINT NOT NULL,
l LINESTRING NOT NULL,
g GEOMETRY NOT NULL,
PRIMARY KEY(p),
SPATIAL KEY `idx2` (p),
SPATIAL KEY `idx3` (l),
SPATIAL KEY `idx4` (g)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES(
1, ST_GeomFromText('POINT(10 10)'),
ST_GeomFromText('LINESTRING(1 1, 5 5, 10 10)'),
ST_GeomFromText('POLYGON((30 30, 40 40, 50 50, 30 50, 30 40, 30 30))'));
INSERT INTO t1 VALUES(
2, ST_GeomFromText('POINT(20 20)'),
ST_GeomFromText('LINESTRING(2 3, 7 8, 9 10, 15 16)'),
ST_GeomFromText('POLYGON((10 30, 30 40, 40 50, 40 30, 30 20, 10 30))'));
SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
--replace_column 9 #
EXPLAIN UPDATE t1 SET p = ST_GeomFromText('POINT(30 30)') WHERE p = ST_GeomFromText('POINT(20 20)');
UPDATE t1 SET p = ST_GeomFromText('POINT(30 30)') WHERE p = ST_GeomFromText('POINT(20 20)');
# Once Bug 18265942 - GIS: INNODB WOULD GET POSSIBLE WRONG FIND_FLAG FOR SELECT FROM SERVER is fixed, we can test all the following statements commented out.
#SELECT ST_AsText(p) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((25 25, 25 35, 35 35, 35 25, 25 25))'));
#UPDATE t1 SET p = ST_GeomFromText('POINT(15 15)') WHERE MBRWithin(p, ST_GeomFromText('POLYGON((25 25, 25 35, 35 35, 35 25, 25 25))'));
#DELETE FROM t1 WHERE MBRWithin(p, ST_GeomFromText('LINESTRING(15 15, 15 25, 25 25, 35 15, 15 15)'));
#SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
#INSERT INTO t1 VALUES(
#3, ST_GeomFromText('POINT(25 25)'),
#ST_GeomFromText('LINESTRING(2 3, 7 8, 9 10, 15 16)'),
#ST_GeomFromText('POLYGON((10 30, 30 40, 40 50, 40 30, 30 20, 10 30))'));
#UPDATE t1 SET p = ST_GeomFromText('POINT(50 50)'), l = ST_GeomFromText('LINESTRING(20 20, 21 21, 22 22, 23 23)') WHERE MBRWithin(g, ST_GeomFromText('LINESTRING(25 25, 25 50, 50 50, 50 25, 25 25)'));
SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
ALTER TABLE t1 DROP PRIMARY KEY;
ALTER TABLE t1 ADD PRIMARY KEY(a);
SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
ALTER TABLE t1 DROP PRIMARY KEY;
ALTER TABLE t1 ADD PRIMARY KEY(p);
SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
SHOW CREATE TABLE t1;
# Also waiting for Bug 18265942
#UPDATE t1 SET p = ST_PointFromText('POINT(20 20)') WHERE ST_Equals(p, ST_PointFromText('POINT(30 30)'));
#INSERT INTO t1 VALUES(
#3, ST_GeomFromText('POINT(25 25)'),
#ST_GeomFromText('LINESTRING(2 3, 7 8, 9 10, 15 16)'),
#ST_GeomFromText('POLYGON((10 30, 30 40, 40 50, 40 30, 30 20, 10 30))'));
SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
ALTER TABLE t1 DROP PRIMARY KEY;
ALTER TABLE t1 ADD PRIMARY KEY(p);
--replace_column 9 #
EXPLAIN SELECT a, ST_AsText(p) FROM t1 WHERE a = 2 AND p = ST_GeomFromText('POINT(30 30)');
SELECT a, ST_AsText(p) FROM t1 WHERE a = 2 AND p = ST_GeomFromText('POINT(30 30)');
SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
CHECK TABLE t1;
DROP TABLE t1;
--echo #
--echo # Test for foreign keys.
--echo #
CREATE TABLE parent(p POINT, PRIMARY KEY(p)) ENGINE=InnoDB;
CREATE TABLE child(p POINT NOT NULL) ENGINE=InnoDB;
ALTER TABLE parent ADD SPATIAL INDEX idx1(p ASC);
ALTER TABLE child ADD SPATIAL INDEX idx2(p ASC);
SHOW CREATE TABLE parent;
SHOW CREATE TABLE child;
--error ER_CANT_CREATE_TABLE
ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
show warnings;
ALTER TABLE parent DROP INDEX idx1;
--error ER_CANNOT_ADD_FOREIGN,ER_CANT_CREATE_TABLE
ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
show warnings;
ALTER TABLE child DROP INDEX idx2;
--error ER_CANNOT_ADD_FOREIGN,ER_CANT_CREATE_TABLE
ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
show warnings;
DROP TABLE child, parent;
--echo #
--echo # Bug#28763: Selecting geometry fields in UNION caused server crash.
--echo #
CREATE TABLE t1(f1 GEOMETRY, f2 POINT, f3 GEOMETRY) ENGINE=InnoDB;
SELECT f1 FROM t1 UNION SELECT f1 FROM t1;
INSERT INTO t1 (f2,f3) VALUES (ST_GeomFromText('POINT(1 1)'),
ST_GeomFromText('POINT(2 2)'));
SELECT ST_AsText(f2),ST_AsText(f3) FROM t1;
SELECT ST_AsText(a) FROM (SELECT f2 AS a FROM t1 UNION SELECT f3 FROM t1) t;
CREATE TABLE t2 AS SELECT f2 AS a FROM t1 UNION SELECT f3 FROM t1;
DESC t2;
SELECT ST_AsText(a) FROM t2;
DROP TABLE t1, t2;
Reference in a new issue View git blame Copy permalink