Gecko [ www.jdcpatala.org ]

Name	Size	Permission
0.result	28.99 KB	-rw-r--r--
1.result	63.29 KB	-rw-r--r--
alter_spatial_index.result	35.42 KB	-rw-r--r--
bug16236208.result	2.78 KB	-rw-r--r--
bug16266012.result	614 B	-rw-r--r--
bug17057168.result	1.13 KB	-rw-r--r--
check_rtree.result	599 B	-rw-r--r--
create_spatial_index.result	91.58 KB	-rw-r--r--
geometry.result	47.01 KB	-rw-r--r--
gis.result	63.06 KB	-rw-r--r--
gis_split_inf.result	9.9 KB	-rw-r--r--
gis_split_nan.result	23.68 KB	-rw-r--r--
kill_server.result	997 B	-rw-r--r--
multi_pk.result	1.77 KB	-rw-r--r--
point_basic.result	49.9 KB	-rw-r--r--
point_big.result	20.09 KB	-rw-r--r--
precise.result	26.5 KB	-rw-r--r--
repeatable_spatial.result	6.96 KB	-rw-r--r--
rollback.result	224.48 KB	-rw-r--r--
row_format.result	78.11 KB	-rw-r--r--
rt_precise.result	2.08 KB	-rw-r--r--
rtree.result	103.72 KB	-rw-r--r--
rtree_compress.result	1.46 KB	-rw-r--r--
rtree_compress2.result	666 B	-rw-r--r--
rtree_concurrent_srch.result	11.67 KB	-rw-r--r--
rtree_concurrent_srch_2.result	1.02 KB	-rw-r--r--
rtree_crash.result	997 B	-rw-r--r--
rtree_create_inplace.result	1.78 KB	-rw-r--r--
rtree_debug.result	1.57 KB	-rw-r--r--
rtree_drop_index.result	140.8 KB	-rw-r--r--
rtree_estimate.result	8.19 KB	-rw-r--r--
rtree_multi_pk.result	6.98 KB	-rw-r--r--
rtree_old.result	102.68 KB	-rw-r--r--
rtree_purge.result	563 B	-rw-r--r--
rtree_recovery.result	1.48 KB	-rw-r--r--
rtree_rollback1.result	1.29 KB	-rw-r--r--
rtree_rollback2.result	406 B	-rw-r--r--
rtree_search.result	14.36 KB	-rw-r--r--
rtree_split.result	3.83 KB	-rw-r--r--
rtree_undo.result	1.72 MB	-rw-r--r--
types.result	2.96 KB	-rw-r--r--
update_root.result	481 B	-rw-r--r--

Code Editor : alter_spatial_index.result

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);
ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC);
ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on Polygon';
ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry';
ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE;
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 42000: A SPATIAL index may only contain a geometrical type column
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 MySQL 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 MySQL server version for the right syntax to use near 'USING HASH' at line 1
ALTER TABLE tab CHANGE c2 c2 MULTIPOINT NOT NULL FIRST, ALGORITHM=COPY;
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING NOT NULL,ALGORITHM=COPY;
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON NOT NULL;
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
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)
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)
DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
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;
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;
ALTER TABLE tab CHANGE COLUMN c33 c3 LINESTRING NOT NULL;
ALTER TABLE tab CHANGE COLUMN c44 c4 POLYGON NOT NULL;
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	Table storage engine for 'tab' doesn't have this option
SHOW WARNINGS;
Level	Code	Message
Note	1031	Table storage engine for '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;
ALTER TABLE tab ADD PRIMARY KEY(c2) ;
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)
2	POINT(20 20)	POLYGON((40 50,70 50,80 80,70 100,50 100,40 70,40 50))
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)
2	POINT(3000 3000)	POLYGON((40 50,70 50,80 80,70 100,50 100,40 70,40 50))
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)
1	POINT(10 10)	POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
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'
UPDATE tab SET c4 = ST_GeomFromText('POINT(4000 4000)')
WHERE MBRIntersects(tab.c4, @g1);
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
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;
ALTER TABLE tab DROP INDEX idx2;
CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab where c1 = c2;
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=InnoDB 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);
CREATE SPATIAL INDEX idx2 ON tab(c2 ASC);
ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c2);
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 ;
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));
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	OK
ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC);
ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC);
ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on Polygon';
ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) 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;
ALTER TABLE tab MODIFY COLUMN c3 POLYGON NOT NULL;
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
ALTER TABLE tab add COLUMN c7 POINT NOT NULL;
ERROR 22004: Invalid use of NULL value
ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INPLACE, LOCK=NONE;
ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED.
SHOW CREATE TABLE tab;
Table	Create Table
tab	CREATE TABLE `tab` (
  `c1` int(11) NOT NULL,
  `c2` geometry 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((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 c2 POLYGON NOT NULL;
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
ALTER TABLE tab MODIFY COLUMN c4 GEOMETRY NOT NULL;
SHOW CREATE TABLE tab;
Table	Create Table
tab	CREATE TABLE `tab` (
  `c1` int(11) NOT NULL,
  `c2` geometry NOT NULL,
  `c3` linestring NOT NULL,
  `c4` geometry 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		
ANALYZE TABLE tab;
Table	Op	Msg_type	Msg_text
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	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;
ALTER TABLE tab MODIFY COLUMN c3 LINESTRING NOT NULL;
ALTER TABLE tab MODIFY COLUMN c4 POLYGON NOT NULL;
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		
ANALYZE TABLE tab;
Table	Op	Msg_type	Msg_text
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,70 50,80 80,70 100,50 100,40 70,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,70 50,80 80,70 100,50 100,40 70,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 42000: A SPATIAL index may only contain a geometrical type column
ALTER TABLE tab MODIFY COLUMN c4 BLOB NOT NULL;
ERROR 42000: A SPATIAL index may only contain a geometrical type column
ALTER TABLE tab  ENGINE Myisam;
ALTER TABLE tab  ENGINE InnoDB;
ANALYZE TABLE tab;
Table	Op	Msg_type	Msg_text
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))
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);
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
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);
ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC);
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	0	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) ;
ERROR HY000: Cannot add foreign key constraint
ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ON DELETE CASCADE ;
ERROR HY000: Cannot add foreign key constraint
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) ;
ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC);
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	0	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	0	32	NULL		SPATIAL		
ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ;
ERROR 42000: BLOB/TEXT column 'parent_id' used in key specification without a key length
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, algorithm=inplace;
update t1 set b = st_geomfromtext('point(0 0)');
alter table t1 add spatial index(b), algorithm=inplace;
ERROR 42000: All parts of a SPATIAL index must be NOT NULL
delete from t1;
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 22004: Invalid use of NULL value
alter table t1 add b geometry not null, algorithm=copy;
ERROR 22004: Invalid use of NULL value
update t1 set b = st_geomfromtext('point(0 0)');
ERROR 42S22: Unknown column 'b' in 'field list'
alter table t1 add spatial index(b), algorithm=copy;
ERROR 42000: Key column 'b' doesn't exist in table
delete from t1;
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;

${this.title}

Code Editor : alter_spatial_index.result