mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2026-03-03 23:19:02 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/innodb_gis/t/alter_spatial_index.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

791 lines
26 KiB
Text
Raw Permalink Blame History

# ******************************************************************
# Test Alter table add spatial index comments
# Test error Alter table modify column with No not null option
# Test error Alter table modify column with null option
# Test table column having both indexes spatial and Btree
# Test error Alter table spatial index using hash/Btree
# Test modify column from point to multipoint,line to multiline
# Test modify column from multipoint to point,multiline to line
# Test discard & import tablespace
# spatial index on temp tables
# Unique constraint on spatial index column Geometry
# Unique constraint on spatial index column POINT
# Modify Engine Innodb to Myisam to InnoDB
# Check Foreign Key constraint on Point column
# Check Foreign Key constraint on Geometry column
# ******************************************************************
CALL mtr.add_suppression("but MariaDB is asking statistics for 2 columns. Have you mixed");
--source include/have_innodb.inc
--source include/have_geometry.inc
let MYSQLD_DATADIR= `select @@datadir`;
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;
CREATE TABLE tab1(c1 int NOT NULL PRIMARY KEY,c2 MULTIPOINT NOT NULL,
c3 MULTILINESTRING NOT NULL,c4 MULTIPOLYGON NOT NULL,c5 GEOMETRY NOT NULL)
ENGINE=InnoDB;
INSERT INTO tab1 SELECT * FROM tab;
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))'));
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))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'),
ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'),
ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
--enable_info
ALTER TABLE tab ADD SPATIAL INDEX idx2(c2);
ALTER TABLE tab ADD SPATIAL KEY idx3(c3);
ALTER TABLE tab ADD SPATIAL INDEX idx4(c4) COMMENT 'testing spatial index on Polygon';
ALTER TABLE tab ADD SPATIAL KEY idx5(c5) COMMENT 'testing spatial index on Geometry';
ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE;
--disable_info
# Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)');
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
UPDATE tab SET C2 = ST_GeomFromText('POINT(100 100)')
WHERE MBRContains(tab.c4, @g1);
DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
# Test the MBRWithin
SET @g1 = ST_GeomFromText( 'POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))');
SELECT c1,ST_AsText(c2),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1);
UPDATE tab SET C2 = ST_GeomFromText('POINT(200 200)')
WHERE MBRWithin(tab.c4, @g1);
SELECT c1,ST_AsText(c2),ST_AsText(c4) FROM tab WHERE MBRWithin(tab.c4, @g1);
DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
--error ER_INDEX_CANNOT_HAVE_NULL
ALTER TABLE tab MODIFY COLUMN c2 MULTIPOINT;
--error ER_INDEX_CANNOT_HAVE_NULL
ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING;
--error ER_INDEX_CANNOT_HAVE_NULL
ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON;
--error ER_INDEX_CANNOT_HAVE_NULL
ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING NULL;
--error ER_INDEX_CANNOT_HAVE_NULL
ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON NULL;
--error ER_INDEX_CANNOT_HAVE_NULL
ALTER TABLE tab MODIFY COLUMN c4 Geometry NULL;
--error ER_INDEX_CANNOT_HAVE_NULL
ALTER TABLE tab CHANGE COLUMN c2 c22 POINT;
--error ER_INDEX_CANNOT_HAVE_NULL
ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING;
--error ER_INDEX_CANNOT_HAVE_NULL
ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON;
# --error ER_SPATIAL_MUST_HAVE_GEOM_COL
--error ER_WRONG_ARGUMENTS
ALTER TABLE tab add SPATIAL INDEX idx1(c1);
--error ER_PARSE_ERROR
ALTER TABLE tab ADD SPATIAL INDEX idx6(c2) USING BTREE;
--error ER_PARSE_ERROR
ALTER TABLE tab ADD SPATIAL INDEX idx6(c2) USING HASH;
# --error ER_INVALID_USE_OF_NULL
# ALTER TABLE tab CHANGE c2 c2 MULTIPOINT NOT NULL FIRST, ALGORITHM=COPY;
# --error ER_CANT_CREATE_GEOMETRY_OBJECT
# ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING NOT NULL,ALGORITHM=COPY;
# --error ER_CANT_CREATE_GEOMETRY_OBJECT
# ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON NOT NULL;
SHOW CREATE TABLE tab;
--replace_column 7 #
SHOW INDEX FROM tab;
SET @g1 = ST_GeomFromText('POLYGON((20 20,30 30,40 40,50 50,40 50,30 40,30 30,20 20))');
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1);
UPDATE tab SET C2 = ST_GeomFromText('POINT(1000 1000)')
WHERE ST_Crosses(tab.c4, @g1);
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1);
DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
--enable_info
ALTER TABLE tab CHANGE COLUMN c2 c22 POINT NOT NULL;
ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING NOT NULL;
ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON NOT NULL;
--disable_info
SHOW CREATE TABLE tab;
--replace_column 7 #
SHOW INDEX FROM tab;
--enable_info
ALTER TABLE tab CHANGE COLUMN c22 c2 POINT NOT NULL;
ALTER TABLE tab CHANGE COLUMN c33 c3 LINESTRING NOT NULL;
ALTER TABLE tab CHANGE COLUMN c44 c4 POLYGON NOT NULL;
--disable_info
SHOW CREATE TABLE tab;
--replace_column 7 #
SHOW INDEX FROM tab;
ALTER TABLE tab DISABLE KEYS;
SHOW WARNINGS;
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1);
UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
WHERE MBREquals(tab.c4, @g1);
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1);
DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1);
--enable_info
ALTER TABLE tab DROP PRIMARY KEY;
ALTER TABLE tab ADD PRIMARY KEY(c2) ;
--disable_info
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1);
UPDATE tab SET C2 = ST_GeomFromText('POINT(3000 3000)')
WHERE ST_Touches(tab.c4, @g1);
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1);
DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1);
FLUSH TABLE tab FOR EXPORT;
--copy_file $MYSQLD_DATADIR/test/tab.ibd $MYSQLD_DATADIR/test/tab.ibd.bk
UNLOCK TABLES;
ALTER TABLE tab DISCARD TABLESPACE;
--disable_warnings
--error ER_TABLESPACE_DISCARDED
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab;
--copy_file $MYSQLD_DATADIR/test/tab.ibd.bk $MYSQLD_DATADIR/test/tab.ibd
--remove_file $MYSQLD_DATADIR/test/tab.ibd.bk
--disable_query_log
--error ER_INTERNAL_ERROR
ALTER TABLE tab IMPORT TABLESPACE;
ALTER TABLE tab DROP INDEX idx2;
ALTER TABLE tab DROP INDEX idx3;
ALTER TABLE tab DROP INDEX idx4;
ALTER TABLE tab DROP INDEX idx5;
ALTER TABLE tab DROP INDEX idx6;
SHOW CREATE TABLE tab;
ALTER TABLE tab IMPORT TABLESPACE;
--enable_query_log
CHECK TABLE tab;
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab ORDER BY c1;
SET @g1 = ST_GeomFromText('LINESTRING( 3010 3010,4010 4010,5010 5010)');
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) order by c1;
--error ER_DUP_ENTRY
UPDATE tab SET c2 = ST_GeomFromText('POINT(4000 4000)')
WHERE MBRIntersects(tab.c4, @g1);
# --error ER_CANT_CREATE_GEOMETRY_OBJECT
# UPDATE tab SET c4 = ST_GeomFromText('POINT(4000 4000)')
# WHERE MBRIntersects(tab.c4, @g1);
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
DELETE FROM tab WHERE MBRIntersects(tab.c4, @g1);
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
INSERT INTO tab SELECT * FROM tab1;
--enable_info
ALTER TABLE tab DROP PRIMARY KEY;
--disable_info
# Check spatial index on temp tables
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab where c1 = c2;
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab;
INSERT INTO temp_tab SELECT * FROM tab;
CREATE SPATIAL INDEX idx2 ON temp_tab(c2);
CREATE SPATIAL INDEX idx3 ON temp_tab(c3);
CREATE SPATIAL INDEX idx4 ON temp_tab(c4);
CREATE SPATIAL INDEX idx5 ON temp_tab(c5);
SHOW CREATE TABLE temp_tab;
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1) ORDER BY c1;
# The following comments will be removed once the patch is available
UPDATE temp_tab SET C2 = ST_GeomFromText('POINT(1000 1000)')
WHERE MBRContains(temp_tab.c4, @g1);
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1);
# Sever crashes Here so commented, will be removed later
DELETE FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1);
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1) ORDER BY c1;
# Check Unique constraint on spatial index column POINT
SHOW CREATE TABLE tab;
--replace_column 7 #
SHOW INDEX FROM tab;
DELETE FROM tab;
--enable_info
ALTER TABLE tab ADD PRIMARY KEY(c2);
CREATE SPATIAL INDEX idx2 ON tab(c2);
ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c2);
--disable_info
SHOW CREATE TABLE tab;
--replace_column 7 #
SHOW INDEX FROM tab;
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))'));
# Check Unique constraint on spatial index column Geometry
DELETE FROM tab;
--enable_info
ALTER TABLE tab DROP PRIMARY KEY ;
ALTER TABLE tab DROP KEY const_1;
ALTER TABLE tab ADD PRIMARY KEY(c5(10));
ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c5(10));
--disable_info
SHOW CREATE TABLE tab;
--replace_column 7 #
SHOW INDEX FROM tab;
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))'));
#cleanup
DROP TABLE tab,tab1,temp_tab;
--enable_warnings
# Check Modify POINT to GEOMETRY and GEOMETRY to POINT
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;
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))'));
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))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'),
ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'),
ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
ANALYZE TABLE tab;
--enable_info
ALTER TABLE tab ADD SPATIAL INDEX idx2(c2);
ALTER TABLE tab ADD SPATIAL KEY idx3(c3);
ALTER TABLE tab ADD SPATIAL INDEX idx4(c4) COMMENT 'testing spatial index on Polygon';
ALTER TABLE tab ADD SPATIAL KEY idx5(c5) COMMENT 'testing spatial index on Geometry';
ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE;
ALTER TABLE tab MODIFY COLUMN c2 GEOMETRY NOT NULL;
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
ALTER TABLE tab MODIFY COLUMN c3 POLYGON NOT NULL;
ALTER TABLE tab add COLUMN c7 POINT NOT NULL;
--disable_info
# instant add, supported
ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INPLACE, LOCK=NONE;
SELECT HEX(c8) FROM tab;
BEGIN;
INSERT INTO tab SELECT 0,c2,c3,c4,c5,
ST_GeomFromText('POINT(67 89)'),ST_GeomFromText('POINT(67 89)')
FROM tab LIMIT 1;
SELECT HEX(c8) FROM tab;
ROLLBACK;
ALTER TABLE tab add COLUMN c9 POINT NOT NULL AFTER c5, ALGORITHM = INPLACE, LOCK=NONE;
ALTER TABLE tab DROP COLUMN c9, ALGORITHM=INSTANT;
SHOW CREATE TABLE tab;
--replace_column 7 #
SHOW INDEX FROM tab;
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
UPDATE tab SET C2 = ST_GeomFromText('POINT(1000 1000)')
WHERE MBRContains(tab.c4, @g1);
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
--enable_info
# --error ER_CANT_CREATE_GEOMETRY_OBJECT
# ALTER TABLE tab MODIFY COLUMN c2 POLYGON NOT NULL;
ALTER TABLE tab MODIFY COLUMN c4 GEOMETRY NOT NULL;
--disable_info
SHOW CREATE TABLE tab;
--replace_column 7 #
SHOW INDEX FROM tab;
ANALYZE TABLE tab;
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
SET @g2 = ST_GeomFromText('LINESTRING(140 140,150 150,160 160)');
SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1)
AND MBREquals(tab.c3,@g2) ORDER BY c1;
UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
WHERE MBREquals(tab.c4, @g1) AND MBREquals(tab.c3,@g2);
SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1)
AND MBREquals(tab.c3,@g2) ORDER BY c1;
DELETE FROM tab WHERE MBREquals(tab.c4, @g1) AND MBREquals(tab.c3,@g2);
SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1)
AND MBREquals(tab.c3,@g2) ORDER BY c1;
ANALYZE TABLE tab;
SET @g1 = ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))');
SET @g2 = ST_GeomFromText('LINESTRING(1 1,2 2,3 3)');
# When Point type data exist in the column allow DDL operation
--enable_info
ALTER TABLE tab MODIFY COLUMN c2 POINT NOT NULL;
ALTER TABLE tab MODIFY COLUMN c4 POLYGON NOT NULL;
--disable_info
SHOW CREATE TABLE tab;
--replace_column 7 #
SHOW INDEX FROM tab;
ANALYZE TABLE tab;
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
SET @g2 = ST_GeomFromText('LINESTRING(1 1,2 2,3 3)');
# Should be 0 rows affected
SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
AND ST_Touches(tab.c3,@g2);
# Should be 0 rows affected
UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
WHERE ST_Touches(tab.c4, @g1) AND ST_Touches(tab.c3,@g2);
# Should be 0 rows affected
DELETE FROM tab WHERE ST_Touches(tab.c4, @g1) AND ST_Touches(tab.c3,@g2);
# Should be 0 rows affected
SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
AND ST_Touches(tab.c3,@g2);
# should be 1 row affected
SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
OR ST_Touches(tab.c3,@g2);
# should be 1 row affected
UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2);
# should be 1 row affected
SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
OR ST_Touches(tab.c3,@g2);
# should be 1 row affected
DELETE FROM tab WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2);
# Should be Empty set
SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
OR ST_Touches(tab.c3,@g2);
--enable_info
# --error ER_SPATIAL_MUST_HAVE_GEOM_COL
--error ER_WRONG_ARGUMENTS
ALTER TABLE tab MODIFY COLUMN c4 INT NOT NULL;
# --error ER_SPATIAL_MUST_HAVE_GEOM_COL
--error ER_WRONG_ARGUMENTS
ALTER TABLE tab MODIFY COLUMN c4 BLOB NOT NULL;
--disable_info
# Test InnoDB to Myisam to InnoDB
ALTER TABLE tab ENGINE Myisam;
ALTER TABLE tab ENGINE InnoDB;
ANALYZE TABLE tab;
SET @g1 = ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))');
SET @g2 = ST_GeomFromText('LINESTRING(400 400,500 500,600 700)');
SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) AND MBRWithin(tab.c3, @g2);
# --error ER_CANT_CREATE_GEOMETRY_OBJECT
# UPDATE tab SET c2 = ST_GeomFromText('POINT(2000 2000)'),
# c3=ST_GeomFromText('POINT(2000 2000)')
# WHERE MBRWithin(tab.c4, @g1) AND MBRWithin(tab.c3, @g2);
SET @g1 = ST_GeomFromText('POINT(2000 2000)');
SET @g2 = ST_GeomFromText('POINT(2000 2000)');
SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c2, @g1) AND MBRWithin(tab.c3, @g2);
DELETE FROM tab WHERE MBRWithin(tab.c2, @g1) AND MBRWithin(tab.c3, @g2);
SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c2, @g1) AND MBRWithin(tab.c3, @g2);
#cleanup
DROP TABLE tab;
# Check Foreign Key constraint on Point column
CREATE TABLE parent (id POINT, PRIMARY KEY(id)) ENGINE=InnoDB;
CREATE TABLE child (id GEOMETRY NOT NULL, parent_id POINT NOT NULL) ENGINE=InnoDB;
--enable_info
ALTER TABLE parent ADD SPATIAL INDEX idx1(id);
ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id);
--disable_info
SHOW CREATE TABLE parent;
SHOW CREATE TABLE child;
SHOW INDEX FROM parent;
--replace_column 7 #
SHOW INDEX FROM child;
--disable_result_log
--error ER_WRONG_FK_DEF
ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ;
--error ER_WRONG_FK_DEF
ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ON DELETE CASCADE ;
--enable_result_log
#cleanup
DROP table child,parent;
# Check Foreign Key constraint on Geometry column
CREATE TABLE parent (id GEOMETRY, PRIMARY KEY(id(10))) ENGINE=InnoDB;
CREATE TABLE child (id GEOMETRY NOT NULL, parent_id GEOMETRY NOT NULL) ENGINE=InnoDB;
--enable_info
ALTER TABLE parent ADD SPATIAL INDEX idx1(id) ;
ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id);
--disable_info
SHOW CREATE TABLE parent;
SHOW CREATE TABLE child;
SHOW INDEX FROM parent;
SHOW INDEX FROM child;
--disable_result_log
--error ER_WRONG_FK_DEF
ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ;
--enable_result_log
#cleanup
DROP table child,parent;
# Check add spatial index when table already has rows (inplace).
create table t1 (c1 int) engine=innodb;
insert into t1 values(NULL);
# Add spatial index fail, since geometry column can't be null.
--error ER_INDEX_CANNOT_HAVE_NULL
alter table t1 add b geometry, add spatial index(b), algorithm=inplace;
# Add spatial index fail, since there's invalid geo data.
# The case has to be commented because it no longer fails and following cases
# don't expect the effect of such a statement.
--error ER_CANT_CREATE_GEOMETRY_OBJECT
alter table t1 add b geometry not null, add spatial index(b), algorithm=inplace;
--enable_info
alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'),
add spatial index(b), algorithm=inplace;
--disable_info
DROP table t1;
# Check add spatial index when table already has rows (copy).
create table t1 (c1 int) engine=innodb;
insert into t1 values(NULL);
# Add spatial index fail, since geometry column can't be null.
--error ER_INDEX_CANNOT_HAVE_NULL
alter table t1 add b geometry, add spatial index(b), algorithm=copy;
# Add spatial index fail, since there's a NULL or invalid geo data.
--error ER_CANT_CREATE_GEOMETRY_OBJECT
alter table t1 add b geometry not null, add spatial index(b), algorithm=copy;
alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'),
add spatial index(b), algorithm=copy;
DROP table t1;
create table t1 (p point not null default if(unix_timestamp()>10,POINT(1,1),LineString(Point(0,0),Point(1,1)))) ENGINE=innodb;
set timestamp=10;
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
insert into t1 values(default);
drop table t1;
SET timestamp=default;
create table t1 (p point not null default if(unix_timestamp()>10,POINT(1,1),LineString(Point(0,0),Point(1,1)))) ENGINE=innodb;
set timestamp=10;
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
alter table t1 add column i int;
drop table t1;
SET timestamp=default;
CREATE OR REPLACE TABLE t1 (a INT) ENGINE=InnoDB;
--error ER_CANT_CREATE_GEOMETRY_OBJECT
ALTER TABLE t1 ADD COLUMN b POINT DEFAULT '0';
DROP TABLE t1;
--echo #
--echo # MDEV-29520 heap-use-after-poison in row_merge_spatial_rows()
--echo #
CREATE TABLE t
(a VARCHAR(8192), b POINT NOT NULL, PRIMARY KEY(a(8)), SPATIAL(b))
ENGINE=InnoDB;
INSERT INTO t VALUES (REPEAT('MariaDB Corporation Ab ',351),POINT(0,0));
ALTER TABLE t FORCE;
# Cleanup
DROP TABLE t;
--echo #
--echo # MDEV-29856 heap-use-after-poison in row_merge_spatial_rows()
--echo # with PRIMARY KEY on column prefix
--echo #
CREATE TABLE t (id INT, f TEXT, s POINT NOT NULL,
PRIMARY KEY(id,f(1)), SPATIAL(s)) ENGINE=InnoDB;
INSERT INTO t VALUES
(1,REPEAT('x',8192),@p:=ST_GeomFromText('POINT(0 0)')),(2,'',@p);
ALTER TABLE t FORCE;
DROP TABLE t;
--echo # End of 10.3 tests
Reference in a new issue View git blame Copy permalink