CALL mtr.add_suppression("but MySQL is asking statistics for 2 columns. Have you mixed"); 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))')); ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on Polygon'; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry'; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 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); c1 ST_Astext(c2) ST_Astext(c4) 3 POINT(4 4) POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) 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); c1 ST_Astext(c2) ST_Astext(c4) 3 POINT(0 0) POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) DELETE FROM tab WHERE MBRContains(tab.c4, @g1); SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1); c1 ST_Astext(c2) ST_Astext(c4) SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)'); SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1); c1 ST_Astext(c2) ST_Astext(c4) 4 POINT(50 50) POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) UPDATE tab SET C2 = ST_GeomFromText('POINT(100 100)') WHERE MBRContains(tab.c4, @g1); DELETE FROM tab WHERE MBRContains(tab.c4, @g1); 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); c1 ST_AsText(c2) ST_Astext(c4) 5 POINT(3 3) POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010)) 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); c1 ST_AsText(c2) ST_AsText(c4) 5 POINT(200 200) POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010)) DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); ALTER TABLE tab MODIFY COLUMN c2 MULTIPOINT; ERROR 42000: All parts of a SPATIAL index must be NOT NULL ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING; ERROR 42000: All parts of a SPATIAL index must be NOT NULL ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON; ERROR 42000: All parts of a SPATIAL index must be NOT NULL ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING NULL; ERROR 42000: All parts of a SPATIAL index must be NOT NULL ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON NULL; ERROR 42000: All parts of a SPATIAL index must be NOT NULL ALTER TABLE tab MODIFY COLUMN c4 Geometry NULL; ERROR 42000: All parts of a SPATIAL index must be NOT NULL ALTER TABLE tab CHANGE COLUMN c2 c22 POINT; ERROR 42000: All parts of a SPATIAL index must be NOT NULL ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING; ERROR 42000: All parts of a SPATIAL index must be NOT NULL ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON; ERROR 42000: All parts of a SPATIAL index must be NOT NULL ALTER TABLE tab add SPATIAL INDEX idx1(c1); ERROR HY000: Incorrect arguments to SPATIAL INDEX ALTER TABLE tab ADD SPATIAL INDEX idx6(c2 ASC) USING BTREE; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USING BTREE' at line 1 ALTER TABLE tab ADD SPATIAL INDEX idx6(c2 ASC) USING HASH; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USING HASH' at line 1 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( `c1` int(11) NOT NULL, `c2` point NOT NULL, `c3` linestring NOT NULL, `c4` polygon NOT NULL, `c5` geometry NOT NULL, PRIMARY KEY (`c1`), SPATIAL KEY `idx2` (`c2`), SPATIAL KEY `idx3` (`c3`), SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon', SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry', KEY `idx6` (`c4`(10)) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW INDEX FROM tab; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment tab 0 PRIMARY 1 c1 A # NULL NULL BTREE tab 1 idx2 1 c2 A # 32 NULL SPATIAL tab 1 idx3 1 c3 A # 32 NULL SPATIAL tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry tab 1 idx6 1 c4 A # 10 NULL BTREE 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); c1 ST_Astext(c2) ST_Astext(c4) 2 POINT(20 20) POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) 1 POINT(10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 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); c1 ST_Astext(c2) ST_Astext(c4) 2 POINT(1000 1000) POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) 1 POINT(1000 1000) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); ALTER TABLE tab CHANGE COLUMN c2 c22 POINT NOT NULL; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING NOT NULL; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON NOT NULL; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( `c1` int(11) NOT NULL, `c22` point NOT NULL, `c33` linestring NOT NULL, `c44` polygon NOT NULL, `c5` geometry NOT NULL, PRIMARY KEY (`c1`), SPATIAL KEY `idx2` (`c22`), SPATIAL KEY `idx3` (`c33`), SPATIAL KEY `idx4` (`c44`) COMMENT 'testing spatial index on Polygon', SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry', KEY `idx6` (`c44`(10)) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW INDEX FROM tab; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment tab 0 PRIMARY 1 c1 A # NULL NULL BTREE tab 1 idx2 1 c22 A # 32 NULL SPATIAL tab 1 idx3 1 c33 A # 32 NULL SPATIAL tab 1 idx4 1 c44 A # 32 NULL SPATIAL testing spatial index on Polygon tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry tab 1 idx6 1 c44 A # 10 NULL BTREE ALTER TABLE tab CHANGE COLUMN c22 c2 POINT NOT NULL; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab CHANGE COLUMN c33 c3 LINESTRING NOT NULL; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab CHANGE COLUMN c44 c4 POLYGON NOT NULL; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( `c1` int(11) NOT NULL, `c2` point NOT NULL, `c3` linestring NOT NULL, `c4` polygon NOT NULL, `c5` geometry NOT NULL, PRIMARY KEY (`c1`), SPATIAL KEY `idx2` (`c2`), SPATIAL KEY `idx3` (`c3`), SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon', SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry', KEY `idx6` (`c4`(10)) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW INDEX FROM tab; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment tab 0 PRIMARY 1 c1 A # NULL NULL BTREE tab 1 idx2 1 c2 A # 32 NULL SPATIAL tab 1 idx3 1 c3 A # 32 NULL SPATIAL tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry tab 1 idx6 1 c4 A # 10 NULL BTREE ALTER TABLE tab DISABLE KEYS; Warnings: Note 1031 Storage engine InnoDB of the table `test`.`tab` doesn't have this option SHOW WARNINGS; Level Code Message Note 1031 Storage engine InnoDB of the table `test`.`tab` doesn't have this option 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); c1 ST_Astext(c2) ST_Astext(c4) 10 POINT(160 160) POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) 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); c1 ST_Astext(c2) ST_Astext(c4) 10 POINT(2000 2000) POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) DELETE FROM tab WHERE MBREquals(tab.c4, @g1); SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1); c1 ST_Astext(c2) ST_Astext(c4) ALTER TABLE tab DROP PRIMARY KEY; affected rows: 4 info: Records: 4 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD PRIMARY KEY(c2) ; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 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); c1 ST_Astext(c2) ST_Astext(c4) 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); c1 ST_Astext(c2) ST_Astext(c4) 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); c1 ST_Astext(c2) ST_Astext(c4) FLUSH TABLE tab FOR EXPORT; UNLOCK TABLES; ALTER TABLE tab DISCARD TABLESPACE; SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab; ERROR HY000: Tablespace has been discarded for table `tab` CHECK TABLE tab; Table Op Msg_type Msg_text test.tab check status OK SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab ORDER BY c1; c1 ST_Astext(c2) ST_Astext(c4) 6 POINT(3 3) POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010)) 7 POINT(60 70) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 8 POINT(0 0) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 9 POINT(120 120) POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010)) 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; c1 ST_Astext(c2) ST_Astext(c4) 7 POINT(60 70) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 8 POINT(0 0) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 9 POINT(120 120) POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010)) UPDATE tab SET c2 = ST_GeomFromText('POINT(4000 4000)') WHERE MBRIntersects(tab.c4, @g1); ERROR 23000: Duplicate entry '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00@\xAF@\x' for key 'PRIMARY' SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c2) ST_Astext(c4) 7 POINT(60 70) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 8 POINT(0 0) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 9 POINT(120 120) POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010)) 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; c1 ST_Astext(c2) ST_Astext(c4) INSERT INTO tab SELECT * FROM tab1; ALTER TABLE tab DROP PRIMARY KEY; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE tab DROP INDEX idx2; affected rows: 0 info: Records: 0 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 '=' 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; Table Create Table temp_tab CREATE TEMPORARY TABLE `temp_tab` ( `c1` int(11) NOT NULL, `c2` point NOT NULL, `c3` linestring NOT NULL, `c4` polygon NOT NULL, `c5` geometry NOT NULL, SPATIAL KEY `idx2` (`c2`), SPATIAL KEY `idx3` (`c3`), SPATIAL KEY `idx4` (`c4`), SPATIAL KEY `idx5` (`c5`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 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; c1 ST_Astext(c2) ST_Astext(c4) 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); c1 ST_Astext(c2) ST_Astext(c4) 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; c1 ST_Astext(c2) ST_Astext(c4) SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( `c1` int(11) NOT NULL, `c2` point NOT NULL, `c3` linestring NOT NULL, `c4` polygon NOT NULL, `c5` geometry NOT NULL, SPATIAL KEY `idx3` (`c3`), SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon', SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry', KEY `idx6` (`c4`(10)) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW INDEX FROM tab; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment tab 1 idx3 1 c3 A # 32 NULL SPATIAL tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry tab 1 idx6 1 c4 A # 10 NULL BTREE DELETE FROM tab; ALTER TABLE tab ADD PRIMARY KEY(c2); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 CREATE SPATIAL INDEX idx2 ON tab(c2 ASC); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c2); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( `c1` int(11) NOT NULL, `c2` point NOT NULL, `c3` linestring NOT NULL, `c4` polygon NOT NULL, `c5` geometry NOT NULL, PRIMARY KEY (`c2`(25)), UNIQUE KEY `const_1` (`c2`(25)), SPATIAL KEY `idx3` (`c3`), SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon', SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry', KEY `idx6` (`c4`(10)) USING BTREE, SPATIAL KEY `idx2` (`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW INDEX FROM tab; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment tab 0 PRIMARY 1 c2 A # 25 NULL BTREE tab 0 const_1 1 c2 A # 25 NULL BTREE tab 1 idx3 1 c3 A # 32 NULL SPATIAL tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry tab 1 idx6 1 c4 A # 10 NULL BTREE tab 1 idx2 1 c2 A # 32 NULL SPATIAL 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))')); DELETE FROM tab; ALTER TABLE tab DROP PRIMARY KEY ; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab DROP KEY const_1; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD PRIMARY KEY(c5(10)); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c5(10)); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( `c1` int(11) NOT NULL, `c2` point NOT NULL, `c3` linestring NOT NULL, `c4` polygon NOT NULL, `c5` geometry NOT NULL, PRIMARY KEY (`c5`(10)), UNIQUE KEY `const_1` (`c5`(10)), SPATIAL KEY `idx3` (`c3`), SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon', SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry', KEY `idx6` (`c4`(10)) USING BTREE, SPATIAL KEY `idx2` (`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW INDEX FROM tab; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment tab 0 PRIMARY 1 c5 A # 10 NULL BTREE tab 0 const_1 1 c5 A # 10 NULL BTREE tab 1 idx3 1 c3 A # 32 NULL SPATIAL tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry tab 1 idx6 1 c4 A # 10 NULL BTREE tab 1 idx2 1 c2 A # 32 NULL SPATIAL 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))')); DROP TABLE tab,tab1,temp_tab; 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; Table Op Msg_type Msg_text test.tab analyze status Engine-independent statistics collected test.tab analyze status OK ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on Polygon'; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry'; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab MODIFY COLUMN c2 GEOMETRY NOT NULL; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab MODIFY COLUMN c3 POLYGON NOT NULL; affected rows: 10 info: Records: 10 Duplicates: 0 Warnings: 0 ALTER TABLE tab add COLUMN c7 POINT NOT NULL; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INPLACE, LOCK=NONE; SELECT HEX(c8) FROM tab; HEX(c8) 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; HEX(c8) 0000000001010000000000000000C050400000000000405640 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; Table Create Table tab CREATE TABLE `tab` ( `c1` int(11) NOT NULL, `c2` geometry NOT NULL, `c3` polygon NOT NULL, `c4` polygon NOT NULL, `c5` geometry NOT NULL, `c7` point NOT NULL, `c8` point NOT NULL, PRIMARY KEY (`c1`), SPATIAL KEY `idx2` (`c2`), SPATIAL KEY `idx3` (`c3`), SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon', SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry', KEY `idx6` (`c4`(10)) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW INDEX FROM tab; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment tab 0 PRIMARY 1 c1 A # NULL NULL BTREE tab 1 idx2 1 c2 A # 32 NULL SPATIAL tab 1 idx3 1 c3 A # 32 NULL SPATIAL tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry tab 1 idx6 1 c4 A # 10 NULL BTREE 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; c1 ST_Astext(c2) ST_Astext(c4) 3 POINT(1000 1000) POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) 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; c1 ST_Astext(c2) ST_Astext(c4) ALTER TABLE tab MODIFY COLUMN c4 GEOMETRY NOT NULL; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( `c1` int(11) NOT NULL, `c2` geometry NOT NULL, `c3` polygon NOT NULL, `c4` geometry NOT NULL, `c5` geometry NOT NULL, `c7` point NOT NULL, `c8` point NOT NULL, PRIMARY KEY (`c1`), SPATIAL KEY `idx2` (`c2`), SPATIAL KEY `idx3` (`c3`), SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon', SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry', KEY `idx6` (`c4`(10)) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW INDEX FROM tab; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment tab 0 PRIMARY 1 c1 A # NULL NULL BTREE tab 1 idx2 1 c2 A # 32 NULL SPATIAL tab 1 idx3 1 c3 A # 32 NULL SPATIAL tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry tab 1 idx6 1 c4 A # 10 NULL BTREE ANALYZE TABLE tab; Table Op Msg_type Msg_text test.tab analyze status Engine-independent statistics collected test.tab analyze status OK 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; c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4) 10 POINT(160 160) LINESTRING(140 140,150 150,160 160) POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) 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; c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4) 10 POINT(2000 2000) LINESTRING(140 140,150 150,160 160) POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) 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; c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4) ANALYZE TABLE tab; Table Op Msg_type Msg_text test.tab analyze status Engine-independent statistics collected test.tab analyze status OK 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)'); ALTER TABLE tab MODIFY COLUMN c2 POINT NOT NULL; affected rows: 8 info: Records: 8 Duplicates: 0 Warnings: 0 ALTER TABLE tab MODIFY COLUMN c3 LINESTRING NOT NULL; affected rows: 8 info: Records: 8 Duplicates: 0 Warnings: 0 ALTER TABLE tab MODIFY COLUMN c4 POLYGON NOT NULL; affected rows: 8 info: Records: 8 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( `c1` int(11) NOT NULL, `c2` point NOT NULL, `c3` linestring NOT NULL, `c4` polygon NOT NULL, `c5` geometry NOT NULL, `c7` point NOT NULL, `c8` point NOT NULL, PRIMARY KEY (`c1`), SPATIAL KEY `idx2` (`c2`), SPATIAL KEY `idx3` (`c3`), SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon', SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry', KEY `idx6` (`c4`(10)) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW INDEX FROM tab; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment tab 0 PRIMARY 1 c1 A # NULL NULL BTREE tab 1 idx2 1 c2 A # 32 NULL SPATIAL tab 1 idx3 1 c3 A # 32 NULL SPATIAL tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry tab 1 idx6 1 c4 A # 10 NULL BTREE ANALYZE TABLE tab; Table Op Msg_type Msg_text test.tab analyze status Engine-independent statistics collected test.tab analyze status OK 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)'); 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); c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4) UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)') WHERE ST_Touches(tab.c4, @g1) AND ST_Touches(tab.c3,@g2); DELETE FROM tab WHERE ST_Touches(tab.c4, @g1) AND ST_Touches(tab.c3,@g2); 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); c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4) 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); c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4) 2 POINT(20 20) LINESTRING(20 20,30 30,40 40) POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)') WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2); 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); c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4) 2 POINT(2000 2000) LINESTRING(20 20,30 30,40 40) POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) DELETE FROM tab WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2); 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); c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4) ALTER TABLE tab MODIFY COLUMN c4 INT NOT NULL; ERROR HY000: Incorrect arguments to SPATIAL INDEX ALTER TABLE tab MODIFY COLUMN c4 BLOB NOT NULL; ERROR HY000: Incorrect arguments to SPATIAL INDEX ALTER TABLE tab ENGINE Myisam; ALTER TABLE tab ENGINE InnoDB; ANALYZE TABLE tab; Table Op Msg_type Msg_text test.tab analyze status Engine-independent statistics collected test.tab analyze status OK 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); c1 ST_AsText(c2) ST_AsText(c3) ST_Astext(c4) 5 POINT(3 3) LINESTRING(400 400,500 500,600 700) POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010)) 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); c1 ST_AsText(c2) ST_AsText(c3) ST_Astext(c4) 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); c1 ST_AsText(c2) ST_AsText(c3) ST_Astext(c4) DROP TABLE tab; CREATE TABLE parent (id POINT, PRIMARY KEY(id)) ENGINE=InnoDB; CREATE TABLE child (id GEOMETRY NOT NULL, parent_id POINT NOT NULL) ENGINE=InnoDB; ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE parent; Table Create Table parent CREATE TABLE `parent` ( `id` point NOT NULL, PRIMARY KEY (`id`(25)), SPATIAL KEY `idx1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE child; Table Create Table child CREATE TABLE `child` ( `id` geometry NOT NULL, `parent_id` point NOT NULL, SPATIAL KEY `idx2` (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW INDEX FROM parent; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment parent 0 PRIMARY 1 id A 0 25 NULL BTREE parent 1 idx1 1 id A NULL 32 NULL SPATIAL SHOW INDEX FROM child; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment child 1 idx2 1 parent_id A # 32 NULL SPATIAL ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ; ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ON DELETE CASCADE ; DROP table child,parent; 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; ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC) ; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE parent; Table Create Table parent CREATE TABLE `parent` ( `id` geometry NOT NULL, PRIMARY KEY (`id`(10)), SPATIAL KEY `idx1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE child; Table Create Table child CREATE TABLE `child` ( `id` geometry NOT NULL, `parent_id` geometry NOT NULL, SPATIAL KEY `idx2` (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW INDEX FROM parent; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment parent 0 PRIMARY 1 id A 0 10 NULL BTREE parent 1 idx1 1 id A NULL 32 NULL SPATIAL SHOW INDEX FROM child; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment child 1 idx2 1 parent_id A NULL 32 NULL SPATIAL ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ; DROP table child,parent; create table t1 (c1 int) engine=innodb; insert into t1 values(NULL); alter table t1 add b geometry, add spatial index(b), algorithm=inplace; ERROR 42000: All parts of a SPATIAL index must be NOT NULL alter table t1 add b geometry not null, add spatial index(b), algorithm=inplace; ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), add spatial index(b), algorithm=inplace; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 DROP table t1; create table t1 (c1 int) engine=innodb; insert into t1 values(NULL); alter table t1 add b geometry, add spatial index(b), algorithm=copy; ERROR 42000: All parts of a SPATIAL index must be NOT NULL alter table t1 add b geometry not null, add spatial index(b), algorithm=copy; ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), add spatial index(b), algorithm=copy; DROP table t1; # # BUG#20111575 ALTER TABLE...ADD SPATIAL INDEX...LOCK NONE IS REFUSED # WITHOUT STATING A REASON # CREATE TABLE t1(p point NOT NULL) ENGINE=innodb; ALTER TABLE t1 ADD SPATIAL INDEX(p), LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED ALTER TABLE t1 ADD SPATIAL INDEX(p); ALTER TABLE t1 FORCE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED 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' 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' for column `test`.`(temporary)`.`p` at row 1 drop table t1; SET timestamp=default; CREATE OR REPLACE TABLE t1 (a INT) ENGINE=InnoDB; ALTER TABLE t1 ADD COLUMN b POINT DEFAULT '0'; ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field DROP TABLE t1;