Debian Codegraph @

mysql-test/suite/innodb_gis/r/alter_spatial_index.result

raw
1 CALL mtr.add_suppression("but MySQL is asking statistics for 2 columns. Have you mixed");
2 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Tablespace for table .* is set as discarded");
3 CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL SRID 0,
4 c3 LINESTRING NOT NULL SRID 0,c4 POLYGON NOT NULL SRID 0,c5 GEOMETRY NOT NULL SRID 0)
5 ENGINE=InnoDB;
6 CREATE TABLE tab1(c1 int NOT NULL PRIMARY KEY,c2 MULTIPOINT NOT NULL SRID 0,
7 c3 MULTILINESTRING NOT NULL SRID 0,c4 MULTIPOLYGON NOT NULL SRID 0,c5 GEOMETRY NOT NULL SRID 0)
8 ENGINE=InnoDB;
9 INSERT INTO tab1 SELECT * FROM tab;
10 INSERT INTO tab(c1,c2,c3,c4,c5)
11 VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
12 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
13 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
14 INSERT INTO tab(c1,c2,c3,c4,c5)
15 VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),
16 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),
17 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
18 INSERT INTO tab(c1,c2,c3,c4,c5)
19 VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'),
20 ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'),
21 ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'));
22 INSERT INTO tab(c1,c2,c3,c4,c5)
23 VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'),
24 ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'),
25 ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'));
26 INSERT INTO tab(c1,c2,c3,c4,c5)
27 VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'),
28 ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'),
29 ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'));
30 INSERT INTO tab(c1,c2,c3,c4,c5)
31 VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
32 ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'),
33 ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'));
34 INSERT INTO tab(c1,c2,c3,c4,c5)
35 VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
36 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
37 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
38 INSERT INTO tab(c1,c2,c3,c4,c5)
39 VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
40 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
41 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
42 INSERT INTO tab(c1,c2,c3,c4,c5)
43 VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'),
44 ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'),
45 ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'));
46 INSERT INTO tab(c1,c2,c3,c4,c5)
47 VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'),
48 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'),
49 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
50 ALTER TABLE tab ADD SPATIAL INDEX idx2(c2);
51 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC);
52 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
53 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 ASC);
54 ERROR HY000: Incorrect usage of spatial/fulltext/hash index and explicit index order
55 ALTER TABLE tab ADD SPATIAL KEY idx3(c3);
56 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4) COMMENT 'testing spatial index on Polygon';
57 ALTER TABLE tab ADD SPATIAL KEY idx5(c5) COMMENT 'testing spatial index on Geometry';
58 ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE;
59 ERROR HY000: The index type BTREE is not supported for spatial indexes.
60 SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
61 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
62 c1 ST_Astext(c2) ST_Astext(c4)
63 3 POINT(4 4) POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
64 UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
65 WHERE MBRContains(tab.c4, @g1);
66 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
67 c1 ST_Astext(c2) ST_Astext(c4)
68 3 POINT(0 0) POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
69 DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
70 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
71 c1 ST_Astext(c2) ST_Astext(c4)
72 SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)');
73 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
74 c1 ST_Astext(c2) ST_Astext(c4)
75 4 POINT(50 50) POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
76 UPDATE tab SET C2 = ST_GeomFromText('POINT(100 100)')
77 WHERE MBRContains(tab.c4, @g1);
78 DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
79 SET @g1 = ST_GeomFromText( 'POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))');
80 SELECT c1,ST_AsText(c2),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1);
81 c1 ST_AsText(c2) ST_Astext(c4)
82 5 POINT(3 3) POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))
83 UPDATE tab SET C2 = ST_GeomFromText('POINT(200 200)')
84 WHERE MBRWithin(tab.c4, @g1);
85 SELECT c1,ST_AsText(c2),ST_AsText(c4) FROM tab WHERE MBRWithin(tab.c4, @g1);
86 c1 ST_AsText(c2) ST_AsText(c4)
87 5 POINT(200 200) POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))
88 DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
89 ALTER TABLE tab MODIFY COLUMN c2 MULTIPOINT SRID 0;
90 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
91 ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING SRID 0;
92 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
93 ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON SRID 0;
94 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
95 ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING NULL SRID 0;
96 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
97 ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON NULL SRID 0;
98 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
99 ALTER TABLE tab MODIFY COLUMN c4 Geometry NULL SRID 0;
100 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
101 ALTER TABLE tab CHANGE COLUMN c2 c22 POINT SRID 0;
102 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
103 ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING SRID 0;
104 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
105 ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON SRID 0;
106 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
107 ALTER TABLE tab add SPATIAL INDEX idx1(c1);
108 ERROR 42000: A SPATIAL index may only contain a geometrical type column
109 ALTER TABLE tab ADD SPATIAL INDEX idx6(c2) USING BTREE;
110 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
111 ALTER TABLE tab ADD SPATIAL INDEX idx6(c2) USING HASH;
112 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
113 ALTER TABLE tab CHANGE c2 c2 MULTIPOINT NOT NULL SRID 0 FIRST, ALGORITHM=COPY;
114 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
115 ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING NOT NULL SRID 0,ALGORITHM=COPY;
116 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
117 ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON NOT NULL SRID 0;
118 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
119 SHOW CREATE TABLE tab;
120 Table Create Table
121 tab CREATE TABLE `tab` (
122 `c1` int NOT NULL,
123 `c2` point NOT NULL /*!80003 SRID 0 */,
124 `c3` linestring NOT NULL /*!80003 SRID 0 */,
125 `c4` polygon NOT NULL /*!80003 SRID 0 */,
126 `c5` geometry NOT NULL /*!80003 SRID 0 */,
127 PRIMARY KEY (`c1`),
128 SPATIAL KEY `idx2` (`c2`),
129 SPATIAL KEY `idx3` (`c3`),
130 SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon',
131 SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry'
132 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
133 SHOW INDEX FROM tab;
134 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
135 tab 0 PRIMARY 1 c1 A # NULL NULL BTREE YES NULL
136 tab 1 idx2 1 c2 A # 32 NULL SPATIAL YES NULL
137 tab 1 idx3 1 c3 A # 32 NULL SPATIAL YES NULL
138 tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon YES NULL
139 tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry YES NULL
140 SET @g1 = ST_GeomFromText('POLYGON((20 20,30 30,40 40,50 50,40 50,30 40,30 30,20 20))');
141 # Invalid polygon.
142 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1);
143 c1 ST_Astext(c2) ST_Astext(c4)
144 # Invalid polygon.
145 UPDATE tab SET C2 = ST_GeomFromText('POINT(1000 1000)')
146 WHERE ST_Crosses(tab.c4, @g1);
147 # Invalid polygon.
148 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1);
149 c1 ST_Astext(c2) ST_Astext(c4)
150 # Invalid polygon.
151 DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
152 ALTER TABLE tab CHANGE COLUMN c2 c22 POINT NOT NULL SRID 0;
153 ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING NOT NULL SRID 0;
154 ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON NOT NULL SRID 0;
155 SHOW CREATE TABLE tab;
156 Table Create Table
157 tab CREATE TABLE `tab` (
158 `c1` int NOT NULL,
159 `c22` point NOT NULL /*!80003 SRID 0 */,
160 `c33` linestring NOT NULL /*!80003 SRID 0 */,
161 `c44` polygon NOT NULL /*!80003 SRID 0 */,
162 `c5` geometry NOT NULL /*!80003 SRID 0 */,
163 PRIMARY KEY (`c1`),
164 SPATIAL KEY `idx2` (`c22`),
165 SPATIAL KEY `idx3` (`c33`),
166 SPATIAL KEY `idx4` (`c44`) COMMENT 'testing spatial index on Polygon',
167 SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry'
168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
169 SHOW INDEX FROM tab;
170 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
171 tab 0 PRIMARY 1 c1 A # NULL NULL BTREE YES NULL
172 tab 1 idx2 1 c22 A # 32 NULL SPATIAL YES NULL
173 tab 1 idx3 1 c33 A # 32 NULL SPATIAL YES NULL
174 tab 1 idx4 1 c44 A # 32 NULL SPATIAL testing spatial index on Polygon YES NULL
175 tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry YES NULL
176 ALTER TABLE tab CHANGE COLUMN c22 c2 POINT NOT NULL SRID 0;
177 ALTER TABLE tab CHANGE COLUMN c33 c3 LINESTRING NOT NULL SRID 0;
178 ALTER TABLE tab CHANGE COLUMN c44 c4 POLYGON NOT NULL SRID 0;
179 SHOW CREATE TABLE tab;
180 Table Create Table
181 tab CREATE TABLE `tab` (
182 `c1` int NOT NULL,
183 `c2` point NOT NULL /*!80003 SRID 0 */,
184 `c3` linestring NOT NULL /*!80003 SRID 0 */,
185 `c4` polygon NOT NULL /*!80003 SRID 0 */,
186 `c5` geometry NOT NULL /*!80003 SRID 0 */,
187 PRIMARY KEY (`c1`),
188 SPATIAL KEY `idx2` (`c2`),
189 SPATIAL KEY `idx3` (`c3`),
190 SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon',
191 SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry'
192 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
193 SHOW INDEX FROM tab;
194 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
195 tab 0 PRIMARY 1 c1 A # NULL NULL BTREE YES NULL
196 tab 1 idx2 1 c2 A # 32 NULL SPATIAL YES NULL
197 tab 1 idx3 1 c3 A # 32 NULL SPATIAL YES NULL
198 tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon YES NULL
199 tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry YES NULL
200 ALTER TABLE tab DISABLE KEYS;
201 Warnings:
202 Note 1031 Table storage engine for 'tab' doesn't have this option
203 SHOW WARNINGS;
204 Level Code Message
205 Note 1031 Table storage engine for 'tab' doesn't have this option
206 SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
207 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1);
208 c1 ST_Astext(c2) ST_Astext(c4)
209 10 POINT(160 160) POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
210 UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
211 WHERE MBREquals(tab.c4, @g1);
212 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1);
213 c1 ST_Astext(c2) ST_Astext(c4)
214 10 POINT(2000 2000) POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
215 DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
216 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1);
217 c1 ST_Astext(c2) ST_Astext(c4)
218 ALTER TABLE tab DROP PRIMARY KEY;
219 ALTER TABLE tab ADD PRIMARY KEY(c2) ;
220 ERROR HY000: Spatial indexes can't be primary or unique indexes.
221 SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
222 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1);
223 c1 ST_Astext(c2) ST_Astext(c4)
224 2 POINT(20 20) POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
225 UPDATE tab SET C2 = ST_GeomFromText('POINT(3000 3000)')
226 WHERE ST_Touches(tab.c4, @g1);
227 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1);
228 c1 ST_Astext(c2) ST_Astext(c4)
229 2 POINT(3000 3000) POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
230 DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
231 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1);
232 c1 ST_Astext(c2) ST_Astext(c4)
233 FLUSH TABLE tab FOR EXPORT;
234 UNLOCK TABLES;
235 ALTER TABLE tab DISCARD TABLESPACE;
236 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab;
237 ERROR HY000: Tablespace has been discarded for table 'tab'
238 CHECK TABLE tab;
239 Table Op Msg_type Msg_text
240 test.tab check status OK
241 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab ORDER BY c1;
242 c1 ST_Astext(c2) ST_Astext(c4)
243 1 POINT(10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
244 6 POINT(3 3) POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))
245 7 POINT(60 70) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
246 8 POINT(0 0) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
247 9 POINT(120 120) POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))
248 SET @g1 = ST_GeomFromText('LINESTRING( 3010 3010,4010 4010,5010 5010)');
249 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) order by c1;
250 c1 ST_Astext(c2) ST_Astext(c4)
251 7 POINT(60 70) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
252 8 POINT(0 0) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
253 9 POINT(120 120) POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))
254 UPDATE tab SET c2 = ST_GeomFromText('POINT(4000 4000)')
255 WHERE MBRIntersects(tab.c4, @g1);
256 UPDATE tab SET c4 = ST_GeomFromText('POINT(4000 4000)')
257 WHERE MBRIntersects(tab.c4, @g1);
258 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
259 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
260 c1 ST_Astext(c2) ST_Astext(c4)
261 7 POINT(4000 4000) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
262 8 POINT(4000 4000) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
263 9 POINT(4000 4000) POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))
264 DELETE FROM tab WHERE MBRIntersects(tab.c4, @g1);
265 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
266 c1 ST_Astext(c2) ST_Astext(c4)
267 INSERT INTO tab SELECT * FROM tab1;
268 ALTER TABLE tab DROP INDEX idx2;
269 CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab where c1 = c2;
270 INSERT INTO temp_tab SELECT * FROM tab;
271 CREATE SPATIAL INDEX idx2 ON temp_tab(c2);
272 CREATE SPATIAL INDEX idx3 ON temp_tab(c3);
273 CREATE SPATIAL INDEX idx4 ON temp_tab(c4);
274 CREATE SPATIAL INDEX idx5 ON temp_tab(c5);
275 SHOW CREATE TABLE temp_tab;
276 Table Create Table
277 temp_tab CREATE TEMPORARY TABLE `temp_tab` (
278 `c1` int NOT NULL,
279 `c2` point NOT NULL /*!80003 SRID 0 */,
280 `c3` linestring NOT NULL /*!80003 SRID 0 */,
281 `c4` polygon NOT NULL /*!80003 SRID 0 */,
282 `c5` geometry NOT NULL /*!80003 SRID 0 */,
283 SPATIAL KEY `idx2` (`c2`),
284 SPATIAL KEY `idx3` (`c3`),
285 SPATIAL KEY `idx4` (`c4`),
286 SPATIAL KEY `idx5` (`c5`)
287 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
288 SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
289 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1) ORDER BY c1;
290 c1 ST_Astext(c2) ST_Astext(c4)
291 UPDATE temp_tab SET C2 = ST_GeomFromText('POINT(1000 1000)')
292 WHERE MBRContains(temp_tab.c4, @g1);
293 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1);
294 c1 ST_Astext(c2) ST_Astext(c4)
295 DELETE FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1);
296 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1) ORDER BY c1;
297 c1 ST_Astext(c2) ST_Astext(c4)
298 SHOW CREATE TABLE tab;
299 Table Create Table
300 tab CREATE TABLE `tab` (
301 `c1` int NOT NULL,
302 `c2` point NOT NULL /*!80003 SRID 0 */,
303 `c3` linestring NOT NULL /*!80003 SRID 0 */,
304 `c4` polygon NOT NULL /*!80003 SRID 0 */,
305 `c5` geometry NOT NULL /*!80003 SRID 0 */,
306 SPATIAL KEY `idx3` (`c3`),
307 SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon',
308 SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry'
309 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
310 SHOW INDEX FROM tab;
311 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
312 tab 1 idx3 1 c3 A # 32 NULL SPATIAL YES NULL
313 tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon YES NULL
314 tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry YES NULL
315 DELETE FROM tab;
316 ALTER TABLE tab ADD PRIMARY KEY(c2);
317 ERROR HY000: Spatial indexes can't be primary or unique indexes.
318 CREATE SPATIAL INDEX idx2 ON tab(c2);
319 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c2);
320 ERROR HY000: Spatial indexes can't be primary or unique indexes.
321 SHOW CREATE TABLE tab;
322 Table Create Table
323 tab CREATE TABLE `tab` (
324 `c1` int NOT NULL,
325 `c2` point NOT NULL /*!80003 SRID 0 */,
326 `c3` linestring NOT NULL /*!80003 SRID 0 */,
327 `c4` polygon NOT NULL /*!80003 SRID 0 */,
328 `c5` geometry NOT NULL /*!80003 SRID 0 */,
329 SPATIAL KEY `idx3` (`c3`),
330 SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon',
331 SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry',
332 SPATIAL KEY `idx2` (`c2`)
333 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
334 SHOW INDEX FROM tab;
335 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
336 tab 1 idx3 1 c3 A # 32 NULL SPATIAL YES NULL
337 tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon YES NULL
338 tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry YES NULL
339 tab 1 idx2 1 c2 A # 32 NULL SPATIAL YES NULL
340 INSERT INTO tab(c1,c2,c3,c4,c5)
341 VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
342 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
343 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
344 DELETE FROM tab;
345 ALTER TABLE tab ADD PRIMARY KEY(c5(10));
346 ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
347 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c5(10));
348 ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
349 SHOW CREATE TABLE tab;
350 Table Create Table
351 tab CREATE TABLE `tab` (
352 `c1` int NOT NULL,
353 `c2` point NOT NULL /*!80003 SRID 0 */,
354 `c3` linestring NOT NULL /*!80003 SRID 0 */,
355 `c4` polygon NOT NULL /*!80003 SRID 0 */,
356 `c5` geometry NOT NULL /*!80003 SRID 0 */,
357 SPATIAL KEY `idx3` (`c3`),
358 SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon',
359 SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry',
360 SPATIAL KEY `idx2` (`c2`)
361 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
362 SHOW INDEX FROM tab;
363 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
364 tab 1 idx3 1 c3 A # 32 NULL SPATIAL YES NULL
365 tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon YES NULL
366 tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry YES NULL
367 tab 1 idx2 1 c2 A # 32 NULL SPATIAL YES NULL
368 INSERT INTO tab(c1,c2,c3,c4,c5)
369 VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
370 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
371 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
372 DROP TABLE tab,tab1,temp_tab;
373 CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL SRID 0,
374 c3 LINESTRING NOT NULL SRID 0,c4 POLYGON NOT NULL SRID 0,c5 GEOMETRY NOT NULL SRID 0)
375 ENGINE=InnoDB;
376 INSERT INTO tab(c1,c2,c3,c4,c5)
377 VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
378 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
379 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
380 INSERT INTO tab(c1,c2,c3,c4,c5)
381 VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),
382 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),
383 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
384 INSERT INTO tab(c1,c2,c3,c4,c5)
385 VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'),
386 ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'),
387 ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'));
388 INSERT INTO tab(c1,c2,c3,c4,c5)
389 VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'),
390 ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'),
391 ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'));
392 INSERT INTO tab(c1,c2,c3,c4,c5)
393 VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'),
394 ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'),
395 ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'));
396 INSERT INTO tab(c1,c2,c3,c4,c5)
397 VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
398 ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'),
399 ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'));
400 INSERT INTO tab(c1,c2,c3,c4,c5)
401 VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
402 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
403 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
404 INSERT INTO tab(c1,c2,c3,c4,c5)
405 VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
406 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
407 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
408 INSERT INTO tab(c1,c2,c3,c4,c5)
409 VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'),
410 ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'),
411 ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'));
412 INSERT INTO tab(c1,c2,c3,c4,c5)
413 VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'),
414 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'),
415 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
416 ANALYZE TABLE tab;
417 Table Op Msg_type Msg_text
418 test.tab analyze status OK
419 ALTER TABLE tab ADD SPATIAL INDEX idx2(c2);
420 ALTER TABLE tab ADD SPATIAL KEY idx3(c3);
421 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4) COMMENT 'testing spatial index on Polygon';
422 ALTER TABLE tab ADD SPATIAL KEY idx5(c5) COMMENT 'testing spatial index on Geometry';
423 ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE;
424 ERROR HY000: The index type BTREE is not supported for spatial indexes.
425 ALTER TABLE tab MODIFY COLUMN c2 GEOMETRY NOT NULL SRID 0;
426 ALTER TABLE tab MODIFY COLUMN c3 POLYGON NOT NULL SRID 0;
427 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
428 ALTER TABLE tab add COLUMN c7 POINT NOT NULL;
429 ERROR 22004: Invalid use of NULL value
430 ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INPLACE, LOCK=NONE;
431 ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED.
432 ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INPLACE, LOCK=SHARED;
433 ERROR 22004: Invalid use of NULL value
434 ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INSTANT;
435 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Do not support online operation on table with GIS index. Try ALGORITHM=COPY/INPLACE.
436 SHOW CREATE TABLE tab;
437 Table Create Table
438 tab CREATE TABLE `tab` (
439 `c1` int NOT NULL,
440 `c2` geometry NOT NULL /*!80003 SRID 0 */,
441 `c3` linestring NOT NULL /*!80003 SRID 0 */,
442 `c4` polygon NOT NULL /*!80003 SRID 0 */,
443 `c5` geometry NOT NULL /*!80003 SRID 0 */,
444 PRIMARY KEY (`c1`),
445 SPATIAL KEY `idx2` (`c2`),
446 SPATIAL KEY `idx3` (`c3`),
447 SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon',
448 SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry'
449 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
450 SHOW INDEX FROM tab;
451 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
452 tab 0 PRIMARY 1 c1 A # NULL NULL BTREE YES NULL
453 tab 1 idx2 1 c2 A # 32 NULL SPATIAL YES NULL
454 tab 1 idx3 1 c3 A # 32 NULL SPATIAL YES NULL
455 tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon YES NULL
456 tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry YES NULL
457 SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
458 UPDATE tab SET C2 = ST_GeomFromText('POINT(1000 1000)')
459 WHERE MBRContains(tab.c4, @g1);
460 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
461 c1 ST_Astext(c2) ST_Astext(c4)
462 3 POINT(1000 1000) POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
463 DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
464 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
465 c1 ST_Astext(c2) ST_Astext(c4)
466 ALTER TABLE tab MODIFY COLUMN c2 POLYGON NOT NULL SRID 0;
467 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
468 ALTER TABLE tab MODIFY COLUMN c4 GEOMETRY NOT NULL SRID 0;
469 SHOW CREATE TABLE tab;
470 Table Create Table
471 tab CREATE TABLE `tab` (
472 `c1` int NOT NULL,
473 `c2` geometry NOT NULL /*!80003 SRID 0 */,
474 `c3` linestring NOT NULL /*!80003 SRID 0 */,
475 `c4` geometry NOT NULL /*!80003 SRID 0 */,
476 `c5` geometry NOT NULL /*!80003 SRID 0 */,
477 PRIMARY KEY (`c1`),
478 SPATIAL KEY `idx2` (`c2`),
479 SPATIAL KEY `idx3` (`c3`),
480 SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon',
481 SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry'
482 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
483 SHOW INDEX FROM tab;
484 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
485 tab 0 PRIMARY 1 c1 A # NULL NULL BTREE YES NULL
486 tab 1 idx2 1 c2 A # 32 NULL SPATIAL YES NULL
487 tab 1 idx3 1 c3 A # 32 NULL SPATIAL YES NULL
488 tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon YES NULL
489 tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry YES NULL
490 ANALYZE TABLE tab;
491 Table Op Msg_type Msg_text
492 test.tab analyze status OK
493 SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
494 SET @g2 = ST_GeomFromText('LINESTRING(140 140,150 150,160 160)');
495 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1)
496 AND MBREquals(tab.c3,@g2) ORDER BY c1;
497 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
498 10 POINT(160 160) LINESTRING(140 140,150 150,160 160) POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
499 UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
500 WHERE MBREquals(tab.c4, @g1) AND MBREquals(tab.c3,@g2);
501 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1)
502 AND MBREquals(tab.c3,@g2) ORDER BY c1;
503 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
504 10 POINT(2000 2000) LINESTRING(140 140,150 150,160 160) POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
505 DELETE FROM tab WHERE MBREquals(tab.c4, @g1) AND MBREquals(tab.c3,@g2);
506 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1)
507 AND MBREquals(tab.c3,@g2) ORDER BY c1;
508 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
509 ANALYZE TABLE tab;
510 Table Op Msg_type Msg_text
511 test.tab analyze status OK
512 SET @g1 = ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))');
513 SET @g2 = ST_GeomFromText('LINESTRING(1 1,2 2,3 3)');
514 ALTER TABLE tab MODIFY COLUMN c2 POINT NOT NULL SRID 0;
515 ALTER TABLE tab MODIFY COLUMN c3 LINESTRING NOT NULL SRID 0;
516 ALTER TABLE tab MODIFY COLUMN c4 POLYGON NOT NULL SRID 0;
517 SHOW CREATE TABLE tab;
518 Table Create Table
519 tab CREATE TABLE `tab` (
520 `c1` int NOT NULL,
521 `c2` point NOT NULL /*!80003 SRID 0 */,
522 `c3` linestring NOT NULL /*!80003 SRID 0 */,
523 `c4` polygon NOT NULL /*!80003 SRID 0 */,
524 `c5` geometry NOT NULL /*!80003 SRID 0 */,
525 PRIMARY KEY (`c1`),
526 SPATIAL KEY `idx2` (`c2`),
527 SPATIAL KEY `idx3` (`c3`),
528 SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon',
529 SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry'
530 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
531 SHOW INDEX FROM tab;
532 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
533 tab 0 PRIMARY 1 c1 A # NULL NULL BTREE YES NULL
534 tab 1 idx2 1 c2 A # 32 NULL SPATIAL YES NULL
535 tab 1 idx3 1 c3 A # 32 NULL SPATIAL YES NULL
536 tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon YES NULL
537 tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry YES NULL
538 ANALYZE TABLE tab;
539 Table Op Msg_type Msg_text
540 test.tab analyze status OK
541 SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
542 SET @g2 = ST_GeomFromText('LINESTRING(1 1,2 2,3 3)');
543 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
544 AND ST_Touches(tab.c3,@g2);
545 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
546 UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
547 WHERE ST_Touches(tab.c4, @g1) AND ST_Touches(tab.c3,@g2);
548 DELETE FROM tab WHERE ST_Touches(tab.c4, @g1) AND ST_Touches(tab.c3,@g2);
549 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
550 AND ST_Touches(tab.c3,@g2);
551 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
552 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
553 OR ST_Touches(tab.c3,@g2);
554 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
555 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))
556 UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
557 WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2);
558 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
559 OR ST_Touches(tab.c3,@g2);
560 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
561 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))
562 DELETE FROM tab WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2);
563 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
564 OR ST_Touches(tab.c3,@g2);
565 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
566 ALTER TABLE tab MODIFY COLUMN c4 INT NOT NULL;
567 ERROR 42000: A SPATIAL index may only contain a geometrical type column
568 ALTER TABLE tab MODIFY COLUMN c4 BLOB NOT NULL;
569 ERROR 42000: A SPATIAL index may only contain a geometrical type column
570 ALTER TABLE tab ENGINE Myisam;
571 ALTER TABLE tab ENGINE InnoDB;
572 ANALYZE TABLE tab;
573 Table Op Msg_type Msg_text
574 test.tab analyze status OK
575 SET @g1 = ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))');
576 SET @g2 = ST_GeomFromText('LINESTRING(400 400,500 500,600 700)');
577 SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) AND MBRWithin(tab.c3, @g2);
578 c1 ST_AsText(c2) ST_AsText(c3) ST_Astext(c4)
579 5 POINT(3 3) LINESTRING(400 400,500 500,600 700) POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))
580 UPDATE tab SET c2 = ST_GeomFromText('POINT(2000 2000)'),
581 c3=ST_GeomFromText('POINT(2000 2000)')
582 WHERE MBRWithin(tab.c4, @g1) AND MBRWithin(tab.c3, @g2);
583 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
584 SET @g1 = ST_GeomFromText('POINT(2000 2000)');
585 SET @g2 = ST_GeomFromText('POINT(2000 2000)');
586 SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c2, @g1) AND MBRWithin(tab.c3, @g2);
587 c1 ST_AsText(c2) ST_AsText(c3) ST_Astext(c4)
588 DELETE FROM tab WHERE MBRWithin(tab.c2, @g1) AND MBRWithin(tab.c3, @g2);
589 SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c2, @g1) AND MBRWithin(tab.c3, @g2);
590 c1 ST_AsText(c2) ST_AsText(c3) ST_Astext(c4)
591 DROP TABLE tab;
592 CREATE TABLE parent (id POINT SRID 0 NOT NULL) ENGINE=InnoDB;
593 CREATE TABLE child (id GEOMETRY NOT NULL, parent_id POINT NOT NULL SRID 0) ENGINE=InnoDB;
594 ALTER TABLE parent ADD SPATIAL INDEX idx1(id);
595 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id);
596 ANALYZE TABLE parent;
597 Table Op Msg_type Msg_text
598 test.parent analyze status OK
599 ANALYZE TABLE child;
600 Table Op Msg_type Msg_text
601 test.child analyze status OK
602 SHOW CREATE TABLE parent;
603 Table Create Table
604 parent CREATE TABLE `parent` (
605 `id` point NOT NULL /*!80003 SRID 0 */,
606 SPATIAL KEY `idx1` (`id`)
607 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
608 SHOW CREATE TABLE child;
609 Table Create Table
610 child CREATE TABLE `child` (
611 `id` geometry NOT NULL,
612 `parent_id` point NOT NULL /*!80003 SRID 0 */,
613 SPATIAL KEY `idx2` (`parent_id`)
614 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
615 SHOW INDEX FROM parent;
616 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
617 parent 1 idx1 1 id A 0 32 NULL SPATIAL YES NULL
618 SHOW INDEX FROM child;
619 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
620 child 1 idx2 1 parent_id A # 32 NULL SPATIAL YES NULL
621 ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ;
622 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the foreign table 'child'
623 ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ON DELETE CASCADE ;
624 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the foreign table 'child'
625 DROP table child,parent;
626 CREATE TABLE parent (id GEOMETRY SRID 0 NOT NULL) ENGINE=InnoDB;
627 CREATE TABLE child (id GEOMETRY NOT NULL, parent_id GEOMETRY NOT NULL SRID 0) ENGINE=InnoDB;
628 ALTER TABLE parent ADD SPATIAL INDEX idx1(id) ;
629 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id);
630 ANALYZE TABLE parent;
631 Table Op Msg_type Msg_text
632 test.parent analyze status OK
633 ANALYZE TABLE child;
634 Table Op Msg_type Msg_text
635 test.child analyze status OK
636 SHOW CREATE TABLE parent;
637 Table Create Table
638 parent CREATE TABLE `parent` (
639 `id` geometry NOT NULL /*!80003 SRID 0 */,
640 SPATIAL KEY `idx1` (`id`)
641 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
642 SHOW CREATE TABLE child;
643 Table Create Table
644 child CREATE TABLE `child` (
645 `id` geometry NOT NULL,
646 `parent_id` geometry NOT NULL /*!80003 SRID 0 */,
647 SPATIAL KEY `idx2` (`parent_id`)
648 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
649 SHOW INDEX FROM parent;
650 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
651 parent 1 idx1 1 id A 0 32 NULL SPATIAL YES NULL
652 SHOW INDEX FROM child;
653 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
654 child 1 idx2 1 parent_id A 0 32 NULL SPATIAL YES NULL
655 ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ;
656 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the foreign table 'child'
657 DROP table child,parent;
658 create table t1 (c1 int) engine=innodb;
659 insert into t1 values(NULL);
660 alter table t1 add b geometry, add spatial index(b), algorithm=inplace;
661 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
662 alter table t1 add b geometry, algorithm=inplace;
663 update t1 set b = st_geomfromtext('point(0 0)');
664 alter table t1 add spatial index(b), algorithm=inplace;
665 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
666 delete from t1;
667 DROP table t1;
668 create table t1 (c1 int) engine=innodb;
669 insert into t1 values(NULL);
670 alter table t1 add b geometry, add spatial index(b), algorithm=copy;
671 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
672 alter table t1 add b geometry not null, add spatial index(b), algorithm=copy;
673 ERROR 22004: Invalid use of NULL value
674 alter table t1 add b geometry not null, algorithm=copy;
675 ERROR 22004: Invalid use of NULL value
676 update t1 set b = st_geomfromtext('point(0 0)');
677 ERROR 42S22: Unknown column 'b' in 'field list'
678 alter table t1 add spatial index(b), algorithm=copy;
679 ERROR 42000: Key column 'b' doesn't exist in table
680 delete from t1;
681 DROP table t1;
682 #
683 # BUG#20111575 ALTER TABLE...ADD SPATIAL INDEX...LOCK NONE IS REFUSED
684 # WITHOUT STATING A REASON
685 #
686 CREATE TABLE t1(p point NOT NULL SRID 0) ENGINE=innodb;
687 ALTER TABLE t1 ADD SPATIAL INDEX(p), LOCK=NONE;
688 ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED.
689 ALTER TABLE t1 ADD SPATIAL INDEX(p);
690 ALTER TABLE t1 FORCE, LOCK=NONE;
691 ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED.
692 DROP TABLE t1;