diff options
Diffstat (limited to 'mysql-test/suite/innodb_gis')
| -rw-r--r-- | mysql-test/suite/innodb_gis/r/1.result | 4 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_gis/r/alter_spatial_index.result | 6 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_gis/r/create_spatial_index.result | 2 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_gis/r/gis.result | 4 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_gis/r/point_basic.result | 123 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_gis/r/rtree_estimate.result | 6 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_gis/t/multi_pk.test | 2 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_gis/t/point_basic.test | 113 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_gis/t/rtree_purge.test | 2 |
9 files changed, 191 insertions, 71 deletions
diff --git a/mysql-test/suite/innodb_gis/r/1.result b/mysql-test/suite/innodb_gis/r/1.result index bce3f994696..6db6407b5db 100644 --- a/mysql-test/suite/innodb_gis/r/1.result +++ b/mysql-test/suite/innodb_gis/r/1.result @@ -458,7 +458,7 @@ explain extended select ST_issimple(MultiPoint(Point(3, 6), Point(4, 10))), ST_i id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select st_issimple(geometrycollection(point(3,6),point(4,10))) AS `ST_issimple(MultiPoint(Point(3, 6), Point(4, 10)))`,st_issimple(point(3,6)) AS `ST_issimple(Point(3, 6))` +Note 1003 select st_issimple(multipoint(point(3,6),point(4,10))) AS `ST_issimple(MultiPoint(Point(3, 6), Point(4, 10)))`,st_issimple(point(3,6)) AS `ST_issimple(Point(3, 6))` create table t1 (a geometry not null); insert into t1 values (ST_GeomFromText('Point(1 2)')); insert into t1 values ('Garbage'); @@ -1028,7 +1028,7 @@ f5 datetime YES NULL drop view v1; drop table t1; SELECT MultiPoint(12345,''); -ERROR HY000: Illegal parameter data type int for operation 'geometrycollection' +ERROR HY000: Illegal parameter data type int for operation 'multipoint' SELECT 1 FROM (SELECT GREATEST(1,GEOMETRYCOLLECTION('00000','00000')) b FROM DUAL) AS d WHERE (LINESTRING(d.b)); ERROR HY000: Illegal parameter data type varchar for operation 'geometrycollection' # diff --git a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result index ab530328acd..6e7cf74373d 100644 --- a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result +++ b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result @@ -294,7 +294,7 @@ affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab where c1 = c2; -ERROR HY000: Illegal parameter data types int and geometry for operation '=' +ERROR HY000: Illegal parameter data types int and point for operation '=' SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab; INSERT INTO temp_tab SELECT * FROM tab; @@ -773,13 +773,13 @@ 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; insert into t1 values(default); -ERROR 22007: Incorrect POINT value: 'GEOMETRYCOLLECTION(POINT(0 0),POINT(1 1))' for column `test`.`t1`.`p` at row 1 +ERROR 22007: Incorrect POINT value: 'LINESTRING(0 0,1 1)' for column `test`.`t1`.`p` at row 1 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; alter table t1 add column i int; -ERROR 22007: Incorrect POINT value: 'GEOMETRYCOLLECTION(POINT(0 0),POINT(1 1))' for column `test`.`t1`.`p` at row 1 +ERROR 22007: Incorrect POINT value: 'LINESTRING(0 0,1 1)' for column `test`.`t1`.`p` at row 1 drop table t1; SET timestamp=default; CREATE OR REPLACE TABLE t1 (a INT) ENGINE=InnoDB; diff --git a/mysql-test/suite/innodb_gis/r/create_spatial_index.result b/mysql-test/suite/innodb_gis/r/create_spatial_index.result index 6fe619df453..a01dbc188e5 100644 --- a/mysql-test/suite/innodb_gis/r/create_spatial_index.result +++ b/mysql-test/suite/innodb_gis/r/create_spatial_index.result @@ -1244,7 +1244,7 @@ Table Op Msg_type Msg_text test.tab check status OK DROP TABLE tab; CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(c1 > 0) ) ENGINE=InnoDB; -ERROR HY000: Illegal parameter data types geometry and int for operation '>' +ERROR HY000: Illegal parameter data types point and int for operation '>' CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(CAST(c1 AS BINARY) > 0) ) ENGINE=InnoDB; CREATE SPATIAL INDEX idx1 ON tab(c1) ; SHOW CREATE TABLE tab; diff --git a/mysql-test/suite/innodb_gis/r/gis.result b/mysql-test/suite/innodb_gis/r/gis.result index 078ce90b119..2a31a6c5317 100644 --- a/mysql-test/suite/innodb_gis/r/gis.result +++ b/mysql-test/suite/innodb_gis/r/gis.result @@ -458,7 +458,7 @@ explain extended select ST_issimple(MultiPoint(Point(3, 6), Point(4, 10))), ST_i id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select st_issimple(geometrycollection(point(3,6),point(4,10))) AS `ST_issimple(MultiPoint(Point(3, 6), Point(4, 10)))`,st_issimple(point(3,6)) AS `ST_issimple(Point(3, 6))` +Note 1003 select st_issimple(multipoint(point(3,6),point(4,10))) AS `ST_issimple(MultiPoint(Point(3, 6), Point(4, 10)))`,st_issimple(point(3,6)) AS `ST_issimple(Point(3, 6))` create table t1 (a geometry not null); insert into t1 values (ST_GeomFromText('Point(1 2)')); insert into t1 values ('Garbage'); @@ -1024,7 +1024,7 @@ f5 datetime YES NULL drop view v1; drop table t1; SELECT MultiPoint(12345,''); -ERROR HY000: Illegal parameter data type int for operation 'geometrycollection' +ERROR HY000: Illegal parameter data type int for operation 'multipoint' SELECT 1 FROM (SELECT GREATEST(1,GEOMETRYCOLLECTION('00000','00000')) b FROM DUAL) AS d WHERE (LINESTRING(d.b)); ERROR HY000: Illegal parameter data type varchar for operation 'geometrycollection' # diff --git a/mysql-test/suite/innodb_gis/r/point_basic.result b/mysql-test/suite/innodb_gis/r/point_basic.result index 4184ec75226..bda5ae59238 100644 --- a/mysql-test/suite/innodb_gis/r/point_basic.result +++ b/mysql-test/suite/innodb_gis/r/point_basic.result @@ -61,12 +61,20 @@ POINT(0 1) POINT(10 11) POINT(1 1) POINT(10 12) POINT(1 0) POINT(10 13) POINT(0 0) POINT(10 14) -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR 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='test/t1' AND c.name IN ('p','g'); name mtype prtype len p 14 1535 12 g 14 1535 12 ALTER TABLE t1 ADD COLUMN p1 POINT, ADD COLUMN p2 POINT, ADD KEY(p); -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1' OR name = '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'); name mtype prtype len p 14 1535 12 g 14 1535 12 @@ -86,7 +94,11 @@ ST_AsText(p) ST_AsText(p1) ALTER TABLE t1 DROP COLUMN p2; # NULLABLE POINT will use NULL ALTER TABLE t1 ADD COLUMN p2 POINT, ADD KEY(p2); -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1' OR name = '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'); name mtype prtype len p 14 1535 12 g 14 1535 12 @@ -98,7 +110,11 @@ Expect 4 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 name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = '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','p2'); name mtype prtype len p 14 1535 12 g 14 1535 12 @@ -123,7 +139,11 @@ POINT(10 20) # Add spatial keys on the table ALTER TABLE t1 ADD SPATIAL(p), ADD SPATIAL(p1); ERROR 42000: All parts of a SPATIAL index must be NOT NULL -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = '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'); name mtype prtype len p 14 1535 12 g 14 1535 12 @@ -148,7 +168,11 @@ POINT(0 0) POINT(10 20) # Drop spatial keys on the table ALTER TABLE t1 DROP KEY p, DROP KEY p1; ERROR 42000: Can't DROP INDEX `p1`; check that it exists -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = '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'); name mtype prtype len p 14 1535 12 g 14 1535 12 @@ -172,11 +196,19 @@ ST_AsText(p) ST_AsText(p1) POINT(0 0) POINT(10 20) TRUNCATE t1; ALTER TABLE t1 DROP COLUMN p, DROP COLUMN p1; -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = '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'); name mtype prtype len g 14 1535 12 ALTER TABLE t1 ADD COLUMN p POINT, ADD COLUMN p1 POINT; -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = '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'); name mtype prtype len g 14 1535 12 p 14 1279 12 @@ -205,7 +237,11 @@ SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('P ST_AsText(p) ST_AsText(p1) POINT(0 0) POINT(10 22) ALTER TABLE t1 DROP COLUMN p1, ADD COLUMN p1 POINT, CHANGE COLUMN p pp POINT AFTER p1; -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'pp' OR name = 'g' OR name = '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'); name mtype prtype len g 14 1535 12 p1 14 1279 12 @@ -219,7 +255,11 @@ POINT(1 0) POINT(5 5) POINT(0 0) POINT(5 5) ALTER TABLE t1 ADD SPATIAL(p1), ADD SPATIAL(pp), ALGORITHM = COPY; ERROR 42000: All parts of a SPATIAL index must be NOT NULL -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'pp' OR name = 'g' OR name = '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'); name mtype prtype len g 14 1535 12 p1 14 1279 12 @@ -323,7 +363,11 @@ SELECT table_name, column_name, data_type, column_type FROM INFORMATION_SCHEMA.C table_name column_name data_type column_type gis_point p point point gis_point g point point -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR 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'); name mtype prtype len p 14 1279 12 g 14 1279 12 @@ -372,7 +416,11 @@ POINT(200 200) CHECK TABLE gis_point; Table Op Msg_type Msg_text test.gis_point check status OK -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR 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='test/gis_point' AND c.name IN ('p','g'); name mtype prtype len p 14 1279 12 g 14 1279 12 @@ -389,7 +437,11 @@ ml MULTILINESTRING NOT NULL, mpoly MULTIPOLYGON NOT NULL, gc GEOMETRYCOLLECTION NOT NULL ) ENGINE=InnoDB; -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'geom' OR name = 'l' OR name = 'poly' OR name = 'mp' OR name = 'ml' OR name = 'mpoly' OR name = 'gc'; +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'; name mtype prtype len geom 14 1535 12 l 14 1535 12 @@ -407,14 +459,18 @@ 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))')); -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' AND name='t1'; -name mtype prtype len -p 14 1535 12 -CREATE TABLE t2 AS SELECT * FROM t1; -DROP TABLE t1; -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' AND name='t2'; -name mtype prtype len -DROP table t2; +CREATE TABLE t2 ENGINE=InnoDB AS SELECT * FROM t1; +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'); +name name mtype prtype len +test/t1 p 14 1535 12 +test/t1 g 14 1535 12 +test/t2 p 14 1535 12 +test/t2 g 14 1535 12 +DROP TABLE t1,t2; # # Test when POINT is used in spatial index # @@ -444,7 +500,11 @@ INSERT INTO gis_point VALUES (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; -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p1' OR name = '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/gis_point' AND c.name IN ('p1','p2'); name mtype prtype len p1 14 1535 12 p2 14 1535 12 @@ -1326,12 +1386,13 @@ 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 name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p'; -name mtype prtype len -p 14 1279 12 -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p'; -name mtype prtype len -p 14 1279 12 +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'; +name name mtype prtype len +test/gis_point p 14 1279 12 SELECT i, ST_AsText(p) FROM gis_point; i ST_AsText(p) 0 POINT(1 1) @@ -1501,7 +1562,7 @@ ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p); ERROR HY000: Can't create table `test`.`child` (errno: 150 "Foreign key constraint is incorrectly formed") show warnings; Level Code Message -Warning 150 Alter table `test`.`child` with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near 'FOREIGN KEY(p) REFERENCES parent(p)'. +Warning 150 Alter table `test`.`child` with foreign key (p) constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. Error 1005 Can't create table `test`.`child` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint for `child` ALTER TABLE parent DROP INDEX idx1; @@ -1509,7 +1570,7 @@ ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p); Got one of the listed errors show warnings; Level Code Message -Warning 150 Alter table `test`.`child` with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near 'FOREIGN KEY(p) REFERENCES parent(p)'. +Warning 150 Alter table `test`.`child` with foreign key (p) constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. Error 1005 Can't create table `test`.`child` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint for `child` ALTER TABLE child DROP INDEX idx2; @@ -1517,7 +1578,7 @@ ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p); Got one of the listed errors show warnings; Level Code Message -Warning 150 Alter table `test`.`child` with foreign key constraint failed. There is only prefix index in the referenced table where the referenced columns appear as the first columns near 'FOREIGN KEY(p) REFERENCES parent(p)'. +Warning 150 Alter table `test`.`child` with foreign key (p) constraint failed. There is only prefix index in the referenced table where the referenced columns appear as the first columns. Error 1005 Can't create table `test`.`child` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint for `child` DROP TABLE child, parent; diff --git a/mysql-test/suite/innodb_gis/r/rtree_estimate.result b/mysql-test/suite/innodb_gis/r/rtree_estimate.result index edb37778f54..dafcc40aba8 100644 --- a/mysql-test/suite/innodb_gis/r/rtree_estimate.result +++ b/mysql-test/suite/innodb_gis/r/rtree_estimate.result @@ -30,7 +30,7 @@ ST_AsText(g) POINT(10 10) EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRDisjoint(g, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL g NULL NULL NULL 3 Using where +1 SIMPLE t1 range g g 34 NULL 2 Using where SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g1); ST_AsText(g) POINT(10 10) @@ -75,14 +75,14 @@ POINT(10 10) POLYGON((5 5,20 5,20 21,5 21,5 5)) EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRIntersects(g, @g2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL g NULL NULL NULL 3 Using where +1 SIMPLE t1 range g g 34 NULL 2 Using where SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g2); ST_AsText(g) POINT(10 10) POLYGON((5 5,20 5,20 21,5 21,5 5)) EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL g NULL NULL NULL 3 Using where +1 SIMPLE t1 range g g 34 NULL 2 Using where SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g2); ST_AsText(g) POINT(10 10) diff --git a/mysql-test/suite/innodb_gis/t/multi_pk.test b/mysql-test/suite/innodb_gis/t/multi_pk.test index c90f794fe15..1be919d165a 100644 --- a/mysql-test/suite/innodb_gis/t/multi_pk.test +++ b/mysql-test/suite/innodb_gis/t/multi_pk.test @@ -8,6 +8,8 @@ --source include/have_debug.inc --source include/big_test.inc --source include/not_valgrind.inc +# This test often times out with MSAN +--source include/not_msan.inc # Create table with R-tree index. create table t1 (c1 int, c2 varchar(255), c3 geometry not null, primary key(c1, c2), spatial index (c3))engine=innodb; diff --git a/mysql-test/suite/innodb_gis/t/point_basic.test b/mysql-test/suite/innodb_gis/t/point_basic.test index 4ac90b93d4c..3ab1ab5c902 100644 --- a/mysql-test/suite/innodb_gis/t/point_basic.test +++ b/mysql-test/suite/innodb_gis/t/point_basic.test @@ -58,10 +58,18 @@ INSERT INTO t1 VALUES(ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(10 SELECT ST_AsText(p), ST_AsText(g) FROM t1; -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR 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='test/t1' AND c.name IN ('p','g'); ALTER TABLE t1 ADD COLUMN p1 POINT, ADD COLUMN p2 POINT, ADD KEY(p); -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1' OR name = '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'); --echo # NOT NULL POINT will use '' SELECT count(*) AS `Expect 4` FROM t1 WHERE p1 = ''; @@ -74,14 +82,22 @@ ALTER TABLE t1 DROP COLUMN p2; --echo # NULLABLE POINT will use NULL ALTER TABLE t1 ADD COLUMN p2 POINT, ADD KEY(p2); -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1' OR name = '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 name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = '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','p2'); SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p1; @@ -93,7 +109,11 @@ 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 name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = '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)'); @@ -104,7 +124,11 @@ SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('P --echo # Drop spatial keys on the table --error ER_CANT_DROP_FIELD_OR_KEY ALTER TABLE t1 DROP KEY p, DROP KEY p1; -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = '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)'); @@ -115,10 +139,18 @@ SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('P TRUNCATE t1; ALTER TABLE t1 DROP COLUMN p, DROP COLUMN p1; -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = '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 name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = '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'); SHOW CREATE TABLE t1; @@ -134,7 +166,11 @@ SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText(' 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 name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'pp' OR name = 'g' OR name = '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)'); @@ -142,7 +178,11 @@ 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 name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'pp' OR name = 'g' OR name = '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'); 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)'); @@ -206,7 +246,11 @@ SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POIN --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 name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR 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; @@ -235,11 +279,14 @@ SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POIN CHECK TABLE gis_point; -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR 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='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 # @@ -252,7 +299,11 @@ CREATE TABLE g ( mpoly MULTIPOLYGON NOT NULL, gc GEOMETRYCOLLECTION NOT NULL ) ENGINE=InnoDB; -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'geom' OR name = 'l' OR name = 'poly' OR name = 'mp' OR name = 'ml' OR name = 'mpoly' OR name = 'gc'; +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; @@ -268,20 +319,17 @@ CREATE TABLE t1 ( INSERT INTO t1 VALUES(ST_PointFromText('POINT(10 10)'),ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); -# Check the mtype and len of the table -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' AND name='t1'; - -CREATE TABLE t2 AS SELECT * FROM t1; - -# Cleanup -DROP TABLE t1; +CREATE TABLE t2 ENGINE=InnoDB AS SELECT * FROM t1; -# Check the mtype and len of the table, should be 14,25 -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' AND name='t2'; +# 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 t2; - +DROP TABLE t1,t2; --echo # --echo # Test when POINT is used in spatial index @@ -316,7 +364,12 @@ INSERT INTO gis_point VALUES CREATE TABLE gis_point1 SELECT * FROM gis_point; -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p1' OR name = 'p2'; +# 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; @@ -640,9 +693,11 @@ INSERT INTO gis_point VALUES(2, NULL); ALTER TABLE gis_point ADD COLUMN j INT, ALGORITHM = COPY; -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p'; - -SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p'; +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; diff --git a/mysql-test/suite/innodb_gis/t/rtree_purge.test b/mysql-test/suite/innodb_gis/t/rtree_purge.test index 60ecbe2e53a..fc5ce2e14bc 100644 --- a/mysql-test/suite/innodb_gis/t/rtree_purge.test +++ b/mysql-test/suite/innodb_gis/t/rtree_purge.test @@ -3,6 +3,8 @@ --source include/innodb_page_size.inc --source include/have_sequence.inc --source include/not_valgrind.inc +# This test often times out with MSAN +--source include/not_msan.inc SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency; SET GLOBAL innodb_purge_rseg_truncate_frequency = 1; |