mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-12-30 16:15:43 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/innodb_gis/t/point_basic.test
Aleksey Midenkov a518e1dd42 MDEV-20865 Store foreign key info in TABLE_SHARE 
1. Access foreign keys via TABLE_SHARE::foreign_keys and
   TABLE_SHARE::referenced_keys;

   foreign_keys and referenced_keys are lists in TABLE_SHARE.

2. Remove handler FK interface:

   - get_foreign_key_list()
   - get_parent_foreign_key_list()
   - referenced_by_foreign_key()

3. Invalidate referenced shares on:

   - RENAME TABLE
   - DROP TABLE
   - RENAME COLUMN
   - ADD FOREIGN KEY

   When foreign table is created or altered by the above operations
   all referenced shares are closed. This blocks the operation while
   any referenced shares are used (when at least one its TABLE
   instance is locked).

4. Update referenced shares on:

   - CREATE TABLE

   On CREATE TABLE add items to referenced_keys of referenced
   shares. States of referenced shares are restored in case of errors.

5. Invalidate foreign shares on:

   - RENAME TABLE
   - RENAME COLUMN

   The above-mentioned blocking takes effect.

6. Check foreign/referenced shares consistency on:

   - CHECK TABLE

7. Temporary change until MDEV-21051:

   InnoDB fill foreign key info at handler open().

FOREIGN_KEY_INFO is refactored to FK_info holding Lex_cstring.

On first TABLE open FK_info is loaded from storage engine into
TABLE_SHARE. All referenced shares (if any exists) are closed. This
leads to blocking of first time foreign table open while referenced
tables are used.

MDEV-21311 Converge Foreign_key and supplemental generated Key together

mysql_prepare_create_table() does data validation and such utilities
as automatic name generation. But it does that only for indexes and
ignores Foreign_key objects. Now as Foreign_key data needs to be
stored in FRM files as well this processing must be done for it like
for any other Key objects.

Replace Key::FOREIGN_KEY type with Key::foreign flag of type
Key::MULTIPLE and Key::generated set to true. Construct one object
with Key::foreign == true instead of two objects of type
Key::FOREIGN_KEY and Key::MULTIPLE.

MDEV-21051 datadict refactorings

- Move read_extra2() to datadict.cc
- Refactored extra2_fields to Extra2_info
- build_frm_image() readability

MDEV-21051 build_table_shadow_filename() refactoring

mysql_prepare_alter_table() leaks fixes

MDEV-21051 amend system tables locking restriction

Table mysql.help_relation has foreign key to mysql.help_keyword. On
bootstrap when help_relation is opened, it preopens help_keyword for
READ and fails in lock_tables_check().

If system table is opened for write then fk references are opened for
write.

Related to: Bug#25422, WL#3984
Tests: main.lock

MDEV-21051 Store and read foreign key info into/from FRM files

1. Introduce Foreign_key_io class which creates/parses binary stream
containing foreign key structures. Referenced tables store there only
hints about foreign tables (their db and name), they restore full info
from the corresponding tables.

Foreign_key_io is stored under new EXTRA2_FOREIGN_KEY_INFO field in
extra2 section of FRM file.

2. Modify mysql_prepare_create_table() to generate names for foreign
keys. Until InnoDB storage of foreign keys is removed, FK names must
be unique across the database: the FK name must be based on table
name.

3. Keep stored data in sync on DDL changes. Referenced tables update
their foreign hints after following operations on foreign tables:

  - RENAME TABLE
  - DROP TABLE
  - CREATE TABLE
  - ADD FOREIGN KEY
  - DROP FOREIGN KEY

Foreign tables update their foreign info after following operations on
referenced tables:

  - RENAME TABLE
  - RENAME COLUMN

4. To achieve 3. there must be ability to rewrite extra2 section of
FRM file without full reparse. FRM binary is built from primary
structures like HA_CREATE_INFO and cannot be built from TABLE_SHARE.

Use shadow write and rename like fast_alter_partition_table()
does. Shadow FRM is new FRM file that replaces the old one.

CREATE TABLE workflow:

  1. Foreign_key is constructed in parser, placed into
     alter_info->key_list;

  2. mysql_prepare_create_table() translates them to FK_info, assigns
     foreign_id if needed;

  3. build_frm_image() writes two FK_info lists into FRM's extra2
     section, for referenced keys it stores only table names (hints);

  4. init_from_binary_frm_image() parses extra2 section and fills
     foreign_keys and referenced_keys of TABLE_SHARE.

     It restores referenced_keys by reading hint list of table names,
     opening corresponding shares and restoring FK_info from their
     foreign_keys. Hints resolution is done only when initializing
     non-temporary shares. Usually temporary share has different
     (temporary) name and it is impossible to resolve foreign keys by
     that name (as we identify them by both foreign and referenced
     table names). Another not unimportant reason is performance: this
     saves spare share acquisitions.

ALTER TABLE workflow:

  1. Foreign_key is constructed in parser, placed into
     alter_info->key_list;

  2. mysql_prepare_alter_table() prepares action lists and share list
     of foreigns/references;

  3. mysql_prepare_alter_table() locks list of foreigns/references by
     MDL_INTENTION_EXCLUSIVE, acquires shares;

  4. prepare_create_table() converts key_list into FK_list, assigns
     foreign_id;

  5. shadow FRM of altered table is created;

  6. data is copied;

  7. altered table is locked by MDL_EXCLUSIVE;

  8. fk_handle_alter() processes action lists, creates FK backups,
     modifies shares, writes shadow FRMs;

  9. altered table is closed;

  10. shadow FRMs are installed;

  11. altered table is renamed, FRM backup deleted;

  12. (TBD in MDEV-21053) shadow FRMs installation log closed, backups
      deleted;

On FK backup system:

In case of failed DDL operation all shares that was modified must be
restored into original state. This is done by FK_ddl_backup (CREATE,
DROP), FK_rename_backup (RENAME), FK_alter_backup (ALTER).

On STL usage:

STL is used for utility not performance-critical algorithms, core
structures hold native List. A wrapper was made to convert STL
exception into bool error status or NULL value.

MDEV-20865 fk_check_consistency() in CHECK TABLE

Self-refs fix

Test table_flags fix: "debug" deviation is now gone.

FIXMEs: +16 -1
2025-09-02 13:24:36 +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_INDEX_CANNOT_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_INDEX_CANNOT_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);
ALTER TABLE child ADD SPATIAL INDEX idx2(p);
SHOW CREATE TABLE parent;
SHOW CREATE TABLE child;
--error ER_WRONG_FK_DEF
ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
show warnings;
ALTER TABLE parent DROP INDEX idx1;
--error ER_WRONG_FK_DEF
ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
show warnings;
ALTER TABLE child DROP INDEX idx2;
--error ER_WRONG_FK_DEF
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