Debian Codegraph @

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

raw
1 CALL mtr.add_suppression("but MariaDB is asking statistics for 2 columns. Have you mixed");
2 CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL,
3 c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL)
4 ENGINE=InnoDB;
5 CREATE TABLE tab1(c1 int NOT NULL PRIMARY KEY,c2 MULTIPOINT NOT NULL,
6 c3 MULTILINESTRING NOT NULL,c4 MULTIPOLYGON NOT NULL,c5 GEOMETRY NOT NULL)
7 ENGINE=InnoDB;
8 INSERT INTO tab1 SELECT * FROM tab;
9 INSERT INTO tab(c1,c2,c3,c4,c5)
10 VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
11 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
12 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
13 INSERT INTO tab(c1,c2,c3,c4,c5)
14 VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),
15 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),
16 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
17 INSERT INTO tab(c1,c2,c3,c4,c5)
18 VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'),
19 ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'),
20 ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'));
21 INSERT INTO tab(c1,c2,c3,c4,c5)
22 VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'),
23 ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'),
24 ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'));
25 INSERT INTO tab(c1,c2,c3,c4,c5)
26 VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'),
27 ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'),
28 ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'));
29 INSERT INTO tab(c1,c2,c3,c4,c5)
30 VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
31 ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'),
32 ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'));
33 INSERT INTO tab(c1,c2,c3,c4,c5)
34 VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
35 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
36 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
37 INSERT INTO tab(c1,c2,c3,c4,c5)
38 VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
39 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
40 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
41 INSERT INTO tab(c1,c2,c3,c4,c5)
42 VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'),
43 ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'),
44 ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'));
45 INSERT INTO tab(c1,c2,c3,c4,c5)
46 VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'),
47 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'),
48 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
49 ALTER TABLE tab ADD SPATIAL INDEX idx2(c2);
50 affected rows: 0
51 info: Records: 0 Duplicates: 0 Warnings: 0
52 ALTER TABLE tab ADD SPATIAL KEY idx3(c3);
53 affected rows: 0
54 info: Records: 0 Duplicates: 0 Warnings: 0
55 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4) COMMENT 'testing spatial index on Polygon';
56 affected rows: 0
57 info: Records: 0 Duplicates: 0 Warnings: 0
58 ALTER TABLE tab ADD SPATIAL KEY idx5(c5) COMMENT 'testing spatial index on Geometry';
59 affected rows: 0
60 info: Records: 0 Duplicates: 0 Warnings: 0
61 ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE;
62 affected rows: 0
63 info: Records: 0 Duplicates: 0 Warnings: 0
64 SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
65 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
66 c1 ST_Astext(c2) ST_Astext(c4)
67 3 POINT(4 4) POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
68 UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
69 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 3 POINT(0 0) POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
73 DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
74 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
75 c1 ST_Astext(c2) ST_Astext(c4)
76 SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)');
77 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
78 c1 ST_Astext(c2) ST_Astext(c4)
79 4 POINT(50 50) POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
80 UPDATE tab SET C2 = ST_GeomFromText('POINT(100 100)')
81 WHERE MBRContains(tab.c4, @g1);
82 DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
83 SET @g1 = ST_GeomFromText( 'POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))');
84 SELECT c1,ST_AsText(c2),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1);
85 c1 ST_AsText(c2) ST_Astext(c4)
86 5 POINT(3 3) POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))
87 UPDATE tab SET C2 = ST_GeomFromText('POINT(200 200)')
88 WHERE MBRWithin(tab.c4, @g1);
89 SELECT c1,ST_AsText(c2),ST_AsText(c4) FROM tab WHERE MBRWithin(tab.c4, @g1);
90 c1 ST_AsText(c2) ST_AsText(c4)
91 5 POINT(200 200) POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))
92 DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
93 ALTER TABLE tab MODIFY COLUMN c2 MULTIPOINT;
94 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
95 ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING;
96 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
97 ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON;
98 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
99 ALTER TABLE tab MODIFY COLUMN c3 MULTILINESTRING NULL;
100 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
101 ALTER TABLE tab MODIFY COLUMN c4 MULTIPOLYGON NULL;
102 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
103 ALTER TABLE tab MODIFY COLUMN c4 Geometry NULL;
104 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
105 ALTER TABLE tab CHANGE COLUMN c2 c22 POINT;
106 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
107 ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING;
108 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
109 ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON;
110 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
111 ALTER TABLE tab add SPATIAL INDEX idx1(c1);
112 ERROR HY000: Incorrect arguments to SPATIAL INDEX
113 ALTER TABLE tab ADD SPATIAL INDEX idx6(c2) USING BTREE;
114 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
115 ALTER TABLE tab ADD SPATIAL INDEX idx6(c2) USING HASH;
116 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
117 SHOW CREATE TABLE tab;
118 Table Create Table
119 tab CREATE TABLE `tab` (
120 `c1` int(11) NOT NULL,
121 `c2` point NOT NULL,
122 `c3` linestring NOT NULL,
123 `c4` polygon NOT NULL,
124 `c5` geometry NOT NULL,
125 PRIMARY KEY (`c1`),
126 SPATIAL KEY `idx2` (`c2`),
127 SPATIAL KEY `idx3` (`c3`),
128 SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon',
129 SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry',
130 KEY `idx6` (`c4`(10)) USING BTREE
131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
132 SHOW INDEX FROM tab;
133 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
134 tab 0 PRIMARY 1 c1 A # NULL NULL BTREE NO
135 tab 1 idx2 1 c2 A # 32 NULL SPATIAL NO
136 tab 1 idx3 1 c3 A # 32 NULL SPATIAL NO
137 tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon NO
138 tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry NO
139 tab 1 idx6 1 c4 A # 10 NULL BTREE NO
140 SET @g1 = ST_GeomFromText('POLYGON((20 20,30 30,40 40,50 50,40 50,30 40,30 30,20 20))');
141 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1);
142 c1 ST_Astext(c2) ST_Astext(c4)
143 UPDATE tab SET C2 = ST_GeomFromText('POINT(1000 1000)')
144 WHERE ST_Crosses(tab.c4, @g1);
145 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1);
146 c1 ST_Astext(c2) ST_Astext(c4)
147 DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
148 ALTER TABLE tab CHANGE COLUMN c2 c22 POINT NOT NULL;
149 affected rows: 0
150 info: Records: 0 Duplicates: 0 Warnings: 0
151 ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING NOT NULL;
152 affected rows: 0
153 info: Records: 0 Duplicates: 0 Warnings: 0
154 ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON NOT NULL;
155 affected rows: 0
156 info: Records: 0 Duplicates: 0 Warnings: 0
157 SHOW CREATE TABLE tab;
158 Table Create Table
159 tab CREATE TABLE `tab` (
160 `c1` int(11) NOT NULL,
161 `c22` point NOT NULL,
162 `c33` linestring NOT NULL,
163 `c44` polygon NOT NULL,
164 `c5` geometry NOT NULL,
165 PRIMARY KEY (`c1`),
166 SPATIAL KEY `idx2` (`c22`),
167 SPATIAL KEY `idx3` (`c33`),
168 SPATIAL KEY `idx4` (`c44`) COMMENT 'testing spatial index on Polygon',
169 SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry',
170 KEY `idx6` (`c44`(10)) USING BTREE
171 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
172 SHOW INDEX FROM tab;
173 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
174 tab 0 PRIMARY 1 c1 A # NULL NULL BTREE NO
175 tab 1 idx2 1 c22 A # 32 NULL SPATIAL NO
176 tab 1 idx3 1 c33 A # 32 NULL SPATIAL NO
177 tab 1 idx4 1 c44 A # 32 NULL SPATIAL testing spatial index on Polygon NO
178 tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry NO
179 tab 1 idx6 1 c44 A # 10 NULL BTREE NO
180 ALTER TABLE tab CHANGE COLUMN c22 c2 POINT NOT NULL;
181 affected rows: 0
182 info: Records: 0 Duplicates: 0 Warnings: 0
183 ALTER TABLE tab CHANGE COLUMN c33 c3 LINESTRING NOT NULL;
184 affected rows: 0
185 info: Records: 0 Duplicates: 0 Warnings: 0
186 ALTER TABLE tab CHANGE COLUMN c44 c4 POLYGON NOT NULL;
187 affected rows: 0
188 info: Records: 0 Duplicates: 0 Warnings: 0
189 SHOW CREATE TABLE tab;
190 Table Create Table
191 tab CREATE TABLE `tab` (
192 `c1` int(11) NOT NULL,
193 `c2` point NOT NULL,
194 `c3` linestring NOT NULL,
195 `c4` polygon NOT NULL,
196 `c5` geometry NOT NULL,
197 PRIMARY KEY (`c1`),
198 SPATIAL KEY `idx2` (`c2`),
199 SPATIAL KEY `idx3` (`c3`),
200 SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon',
201 SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry',
202 KEY `idx6` (`c4`(10)) USING BTREE
203 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
204 SHOW INDEX FROM tab;
205 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
206 tab 0 PRIMARY 1 c1 A # NULL NULL BTREE NO
207 tab 1 idx2 1 c2 A # 32 NULL SPATIAL NO
208 tab 1 idx3 1 c3 A # 32 NULL SPATIAL NO
209 tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon NO
210 tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry NO
211 tab 1 idx6 1 c4 A # 10 NULL BTREE NO
212 ALTER TABLE tab DISABLE KEYS;
213 Warnings:
214 Note 1031 Storage engine InnoDB of the table `test`.`tab` doesn't have this option
215 SHOW WARNINGS;
216 Level Code Message
217 Note 1031 Storage engine InnoDB of the table `test`.`tab` doesn't have this option
218 SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
219 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1);
220 c1 ST_Astext(c2) ST_Astext(c4)
221 10 POINT(160 160) POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
222 UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
223 WHERE MBREquals(tab.c4, @g1);
224 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1);
225 c1 ST_Astext(c2) ST_Astext(c4)
226 10 POINT(2000 2000) POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
227 DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
228 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1);
229 c1 ST_Astext(c2) ST_Astext(c4)
230 ALTER TABLE tab DROP PRIMARY KEY;
231 affected rows: 6
232 info: Records: 6 Duplicates: 0 Warnings: 0
233 ALTER TABLE tab ADD PRIMARY KEY(c2) ;
234 affected rows: 0
235 info: Records: 0 Duplicates: 0 Warnings: 0
236 SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
237 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1);
238 c1 ST_Astext(c2) ST_Astext(c4)
239 2 POINT(20 20) POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
240 UPDATE tab SET C2 = ST_GeomFromText('POINT(3000 3000)')
241 WHERE ST_Touches(tab.c4, @g1);
242 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1);
243 c1 ST_Astext(c2) ST_Astext(c4)
244 2 POINT(3000 3000) POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
245 DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
246 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1);
247 c1 ST_Astext(c2) ST_Astext(c4)
248 FLUSH TABLE tab FOR EXPORT;
249 UNLOCK TABLES;
250 ALTER TABLE tab DISCARD TABLESPACE;
251 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab;
252 ERROR HY000: Tablespace has been discarded for table `tab`
253 ERROR HY000: Internal error: Drop all secondary indexes before importing table test/tab when .cfg file is missing.
254 Table Create Table
255 tab CREATE TABLE `tab` (
256 `c1` int(11) NOT NULL,
257 `c2` point NOT NULL,
258 `c3` linestring NOT NULL,
259 `c4` polygon NOT NULL,
260 `c5` geometry NOT NULL,
261 PRIMARY KEY (`c2`(25))
262 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
263 CHECK TABLE tab;
264 Table Op Msg_type Msg_text
265 test.tab check status OK
266 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab ORDER BY c1;
267 c1 ST_Astext(c2) ST_Astext(c4)
268 1 POINT(10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
269 6 POINT(3 3) POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))
270 7 POINT(60 70) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
271 8 POINT(0 0) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
272 9 POINT(120 120) POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))
273 SET @g1 = ST_GeomFromText('LINESTRING( 3010 3010,4010 4010,5010 5010)');
274 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) order by c1;
275 c1 ST_Astext(c2) ST_Astext(c4)
276 7 POINT(60 70) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
277 8 POINT(0 0) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
278 9 POINT(120 120) POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))
279 UPDATE tab SET c2 = ST_GeomFromText('POINT(4000 4000)')
280 WHERE MBRIntersects(tab.c4, @g1);
281 ERROR 23000: Duplicate entry '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00@\xAF...' for key 'PRIMARY'
282 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
283 c1 ST_Astext(c2) ST_Astext(c4)
284 7 POINT(60 70) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
285 8 POINT(0 0) POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
286 9 POINT(120 120) POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))
287 DELETE FROM tab WHERE MBRIntersects(tab.c4, @g1);
288 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
289 c1 ST_Astext(c2) ST_Astext(c4)
290 INSERT INTO tab SELECT * FROM tab1;
291 ALTER TABLE tab DROP PRIMARY KEY;
292 affected rows: 2
293 info: Records: 2 Duplicates: 0 Warnings: 0
294 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
295 CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab where c1 = c2;
296 ERROR HY000: Illegal parameter data types int and point for operation '='
297 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
298 CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab;
299 INSERT INTO temp_tab SELECT * FROM tab;
300 CREATE SPATIAL INDEX idx2 ON temp_tab(c2);
301 CREATE SPATIAL INDEX idx3 ON temp_tab(c3);
302 CREATE SPATIAL INDEX idx4 ON temp_tab(c4);
303 CREATE SPATIAL INDEX idx5 ON temp_tab(c5);
304 SHOW CREATE TABLE temp_tab;
305 Table Create Table
306 temp_tab CREATE TEMPORARY TABLE `temp_tab` (
307 `c1` int(11) NOT NULL,
308 `c2` point NOT NULL,
309 `c3` linestring NOT NULL,
310 `c4` polygon NOT NULL,
311 `c5` geometry NOT NULL,
312 SPATIAL KEY `idx2` (`c2`),
313 SPATIAL KEY `idx3` (`c3`),
314 SPATIAL KEY `idx4` (`c4`),
315 SPATIAL KEY `idx5` (`c5`)
316 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
317 SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
318 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1) ORDER BY c1;
319 c1 ST_Astext(c2) ST_Astext(c4)
320 UPDATE temp_tab SET C2 = ST_GeomFromText('POINT(1000 1000)')
321 WHERE MBRContains(temp_tab.c4, @g1);
322 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1);
323 c1 ST_Astext(c2) ST_Astext(c4)
324 DELETE FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1);
325 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM temp_tab WHERE MBRContains(temp_tab.c4, @g1) ORDER BY c1;
326 c1 ST_Astext(c2) ST_Astext(c4)
327 SHOW CREATE TABLE tab;
328 Table Create Table
329 tab CREATE TABLE `tab` (
330 `c1` int(11) NOT NULL,
331 `c2` point NOT NULL,
332 `c3` linestring NOT NULL,
333 `c4` polygon NOT NULL,
334 `c5` geometry NOT NULL
335 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
336 SHOW INDEX FROM tab;
337 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
338 DELETE FROM tab;
339 ALTER TABLE tab ADD PRIMARY KEY(c2);
340 affected rows: 0
341 info: Records: 0 Duplicates: 0 Warnings: 0
342 CREATE SPATIAL INDEX idx2 ON tab(c2);
343 affected rows: 0
344 info: Records: 0 Duplicates: 0 Warnings: 0
345 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c2);
346 affected rows: 0
347 info: Records: 0 Duplicates: 0 Warnings: 0
348 SHOW CREATE TABLE tab;
349 Table Create Table
350 tab CREATE TABLE `tab` (
351 `c1` int(11) NOT NULL,
352 `c2` point NOT NULL,
353 `c3` linestring NOT NULL,
354 `c4` polygon NOT NULL,
355 `c5` geometry NOT NULL,
356 PRIMARY KEY (`c2`(25)),
357 UNIQUE KEY `const_1` (`c2`(25)),
358 SPATIAL KEY `idx2` (`c2`)
359 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
360 SHOW INDEX FROM tab;
361 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
362 tab 0 PRIMARY 1 c2 A # 25 NULL BTREE NO
363 tab 0 const_1 1 c2 A # 25 NULL BTREE NO
364 tab 1 idx2 1 c2 A # 32 NULL SPATIAL NO
365 INSERT INTO tab(c1,c2,c3,c4,c5)
366 VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
367 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
368 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
369 DELETE FROM tab;
370 ALTER TABLE tab DROP PRIMARY KEY ;
371 affected rows: 0
372 info: Records: 0 Duplicates: 0 Warnings: 0
373 ALTER TABLE tab DROP KEY const_1;
374 affected rows: 0
375 info: Records: 0 Duplicates: 0 Warnings: 0
376 ALTER TABLE tab ADD PRIMARY KEY(c5(10));
377 affected rows: 0
378 info: Records: 0 Duplicates: 0 Warnings: 0
379 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c5(10));
380 affected rows: 0
381 info: Records: 0 Duplicates: 0 Warnings: 0
382 SHOW CREATE TABLE tab;
383 Table Create Table
384 tab CREATE TABLE `tab` (
385 `c1` int(11) NOT NULL,
386 `c2` point NOT NULL,
387 `c3` linestring NOT NULL,
388 `c4` polygon NOT NULL,
389 `c5` geometry NOT NULL,
390 PRIMARY KEY (`c5`(10)),
391 UNIQUE KEY `const_1` (`c5`(10)),
392 SPATIAL KEY `idx2` (`c2`)
393 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
394 SHOW INDEX FROM tab;
395 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
396 tab 0 PRIMARY 1 c5 A # 10 NULL BTREE NO
397 tab 0 const_1 1 c5 A # 10 NULL BTREE NO
398 tab 1 idx2 1 c2 A # 32 NULL SPATIAL NO
399 INSERT INTO tab(c1,c2,c3,c4,c5)
400 VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
401 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
402 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
403 DROP TABLE tab,tab1,temp_tab;
404 CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL,
405 c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL)
406 ENGINE=InnoDB;
407 INSERT INTO tab(c1,c2,c3,c4,c5)
408 VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
409 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
410 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
411 INSERT INTO tab(c1,c2,c3,c4,c5)
412 VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),
413 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),
414 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
415 INSERT INTO tab(c1,c2,c3,c4,c5)
416 VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'),
417 ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'),
418 ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'));
419 INSERT INTO tab(c1,c2,c3,c4,c5)
420 VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'),
421 ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'),
422 ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'));
423 INSERT INTO tab(c1,c2,c3,c4,c5)
424 VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'),
425 ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'),
426 ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'));
427 INSERT INTO tab(c1,c2,c3,c4,c5)
428 VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
429 ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'),
430 ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'));
431 INSERT INTO tab(c1,c2,c3,c4,c5)
432 VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
433 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
434 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
435 INSERT INTO tab(c1,c2,c3,c4,c5)
436 VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
437 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
438 ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
439 INSERT INTO tab(c1,c2,c3,c4,c5)
440 VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'),
441 ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'),
442 ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'));
443 INSERT INTO tab(c1,c2,c3,c4,c5)
444 VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'),
445 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'),
446 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
447 ANALYZE TABLE tab;
448 Table Op Msg_type Msg_text
449 test.tab analyze status Engine-independent statistics collected
450 test.tab analyze status OK
451 ALTER TABLE tab ADD SPATIAL INDEX idx2(c2);
452 affected rows: 0
453 info: Records: 0 Duplicates: 0 Warnings: 0
454 ALTER TABLE tab ADD SPATIAL KEY idx3(c3);
455 affected rows: 0
456 info: Records: 0 Duplicates: 0 Warnings: 0
457 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4) COMMENT 'testing spatial index on Polygon';
458 affected rows: 0
459 info: Records: 0 Duplicates: 0 Warnings: 0
460 ALTER TABLE tab ADD SPATIAL KEY idx5(c5) COMMENT 'testing spatial index on Geometry';
461 affected rows: 0
462 info: Records: 0 Duplicates: 0 Warnings: 0
463 ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE;
464 affected rows: 0
465 info: Records: 0 Duplicates: 0 Warnings: 0
466 ALTER TABLE tab MODIFY COLUMN c2 GEOMETRY NOT NULL;
467 affected rows: 0
468 info: Records: 0 Duplicates: 0 Warnings: 0
469 ALTER TABLE tab MODIFY COLUMN c3 POLYGON NOT NULL;
470 ERROR 22007: Incorrect POLYGON value: 'LINESTRING(5 5,20 20,30 30)' for column `test`.`tab`.`c3` at row 1
471 ALTER TABLE tab add COLUMN c7 POINT NOT NULL;
472 affected rows: 0
473 info: Records: 0 Duplicates: 0 Warnings: 0
474 ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INPLACE, LOCK=NONE;
475 SELECT HEX(c8) FROM tab;
476 HEX(c8)
477
478
479
480
481
482
483
484
485
486
487 BEGIN;
488 INSERT INTO tab SELECT 0,c2,c3,c4,c5,
489 ST_GeomFromText('POINT(67 89)'),ST_GeomFromText('POINT(67 89)')
490 FROM tab LIMIT 1;
491 SELECT HEX(c8) FROM tab;
492 HEX(c8)
493 0000000001010000000000000000C050400000000000405640
494
495
496
497
498
499
500
501
502
503
504 ROLLBACK;
505 ALTER TABLE tab add COLUMN c9 POINT NOT NULL AFTER c5, ALGORITHM = INPLACE, LOCK=NONE;
506 ALTER TABLE tab DROP COLUMN c9, ALGORITHM=INSTANT;
507 SHOW CREATE TABLE tab;
508 Table Create Table
509 tab CREATE TABLE `tab` (
510 `c1` int(11) NOT NULL,
511 `c2` geometry NOT NULL,
512 `c3` linestring NOT NULL,
513 `c4` polygon NOT NULL,
514 `c5` geometry NOT NULL,
515 `c7` point NOT NULL,
516 `c8` point NOT NULL,
517 PRIMARY KEY (`c1`),
518 SPATIAL KEY `idx2` (`c2`),
519 SPATIAL KEY `idx3` (`c3`),
520 SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon',
521 SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry',
522 KEY `idx6` (`c4`(10)) USING BTREE
523 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
524 SHOW INDEX FROM tab;
525 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
526 tab 0 PRIMARY 1 c1 A # NULL NULL BTREE NO
527 tab 1 idx2 1 c2 A # 32 NULL SPATIAL NO
528 tab 1 idx3 1 c3 A # 32 NULL SPATIAL NO
529 tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon NO
530 tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry NO
531 tab 1 idx6 1 c4 A # 10 NULL BTREE NO
532 SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
533 UPDATE tab SET C2 = ST_GeomFromText('POINT(1000 1000)')
534 WHERE MBRContains(tab.c4, @g1);
535 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
536 c1 ST_Astext(c2) ST_Astext(c4)
537 3 POINT(1000 1000) POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
538 DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
539 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
540 c1 ST_Astext(c2) ST_Astext(c4)
541 ALTER TABLE tab MODIFY COLUMN c4 GEOMETRY NOT NULL;
542 affected rows: 0
543 info: Records: 0 Duplicates: 0 Warnings: 0
544 SHOW CREATE TABLE tab;
545 Table Create Table
546 tab CREATE TABLE `tab` (
547 `c1` int(11) NOT NULL,
548 `c2` geometry NOT NULL,
549 `c3` linestring NOT NULL,
550 `c4` geometry NOT NULL,
551 `c5` geometry NOT NULL,
552 `c7` point NOT NULL,
553 `c8` point NOT NULL,
554 PRIMARY KEY (`c1`),
555 SPATIAL KEY `idx2` (`c2`),
556 SPATIAL KEY `idx3` (`c3`),
557 SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon',
558 SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry',
559 KEY `idx6` (`c4`(10)) USING BTREE
560 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
561 SHOW INDEX FROM tab;
562 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
563 tab 0 PRIMARY 1 c1 A # NULL NULL BTREE NO
564 tab 1 idx2 1 c2 A # 32 NULL SPATIAL NO
565 tab 1 idx3 1 c3 A # 32 NULL SPATIAL NO
566 tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon NO
567 tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry NO
568 tab 1 idx6 1 c4 A # 10 NULL BTREE NO
569 ANALYZE TABLE tab;
570 Table Op Msg_type Msg_text
571 test.tab analyze status Engine-independent statistics collected
572 test.tab analyze status OK
573 SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
574 SET @g2 = ST_GeomFromText('LINESTRING(140 140,150 150,160 160)');
575 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1)
576 AND MBREquals(tab.c3,@g2) ORDER BY c1;
577 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
578 10 POINT(160 160) LINESTRING(140 140,150 150,160 160) POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
579 UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
580 WHERE MBREquals(tab.c4, @g1) AND MBREquals(tab.c3,@g2);
581 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1)
582 AND MBREquals(tab.c3,@g2) ORDER BY c1;
583 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
584 10 POINT(2000 2000) LINESTRING(140 140,150 150,160 160) POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
585 DELETE FROM tab WHERE MBREquals(tab.c4, @g1) AND MBREquals(tab.c3,@g2);
586 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1)
587 AND MBREquals(tab.c3,@g2) ORDER BY c1;
588 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
589 ANALYZE TABLE tab;
590 Table Op Msg_type Msg_text
591 test.tab analyze status Engine-independent statistics collected
592 test.tab analyze status OK
593 SET @g1 = ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))');
594 SET @g2 = ST_GeomFromText('LINESTRING(1 1,2 2,3 3)');
595 ALTER TABLE tab MODIFY COLUMN c2 POINT NOT NULL;
596 affected rows: 8
597 info: Records: 8 Duplicates: 0 Warnings: 0
598 ALTER TABLE tab MODIFY COLUMN c4 POLYGON NOT NULL;
599 affected rows: 8
600 info: Records: 8 Duplicates: 0 Warnings: 0
601 SHOW CREATE TABLE tab;
602 Table Create Table
603 tab CREATE TABLE `tab` (
604 `c1` int(11) NOT NULL,
605 `c2` point NOT NULL,
606 `c3` linestring NOT NULL,
607 `c4` polygon NOT NULL,
608 `c5` geometry NOT NULL,
609 `c7` point NOT NULL,
610 `c8` point NOT NULL,
611 PRIMARY KEY (`c1`),
612 SPATIAL KEY `idx2` (`c2`),
613 SPATIAL KEY `idx3` (`c3`),
614 SPATIAL KEY `idx4` (`c4`) COMMENT 'testing spatial index on Polygon',
615 SPATIAL KEY `idx5` (`c5`) COMMENT 'testing spatial index on Geometry',
616 KEY `idx6` (`c4`(10)) USING BTREE
617 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
618 SHOW INDEX FROM tab;
619 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
620 tab 0 PRIMARY 1 c1 A # NULL NULL BTREE NO
621 tab 1 idx2 1 c2 A # 32 NULL SPATIAL NO
622 tab 1 idx3 1 c3 A # 32 NULL SPATIAL NO
623 tab 1 idx4 1 c4 A # 32 NULL SPATIAL testing spatial index on Polygon NO
624 tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry NO
625 tab 1 idx6 1 c4 A # 10 NULL BTREE NO
626 ANALYZE TABLE tab;
627 Table Op Msg_type Msg_text
628 test.tab analyze status Engine-independent statistics collected
629 test.tab analyze status OK
630 SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
631 SET @g2 = ST_GeomFromText('LINESTRING(1 1,2 2,3 3)');
632 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
633 AND ST_Touches(tab.c3,@g2);
634 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
635 UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
636 WHERE ST_Touches(tab.c4, @g1) AND ST_Touches(tab.c3,@g2);
637 DELETE FROM tab WHERE ST_Touches(tab.c4, @g1) AND ST_Touches(tab.c3,@g2);
638 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
639 AND ST_Touches(tab.c3,@g2);
640 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
641 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
642 OR ST_Touches(tab.c3,@g2);
643 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
644 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))
645 UPDATE tab SET C2 = ST_GeomFromText('POINT(2000 2000)')
646 WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2);
647 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
648 OR ST_Touches(tab.c3,@g2);
649 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
650 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))
651 DELETE FROM tab WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2);
652 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
653 OR ST_Touches(tab.c3,@g2);
654 c1 ST_Astext(c2) ST_AsText(c3) ST_Astext(c4)
655 ALTER TABLE tab MODIFY COLUMN c4 INT NOT NULL;
656 ERROR HY000: Incorrect arguments to SPATIAL INDEX
657 ALTER TABLE tab MODIFY COLUMN c4 BLOB NOT NULL;
658 ERROR HY000: Incorrect arguments to SPATIAL INDEX
659 ALTER TABLE tab ENGINE Myisam;
660 ALTER TABLE tab ENGINE InnoDB;
661 ANALYZE TABLE tab;
662 Table Op Msg_type Msg_text
663 test.tab analyze status Engine-independent statistics collected
664 test.tab analyze status OK
665 SET @g1 = ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))');
666 SET @g2 = ST_GeomFromText('LINESTRING(400 400,500 500,600 700)');
667 SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) AND MBRWithin(tab.c3, @g2);
668 c1 ST_AsText(c2) ST_AsText(c3) ST_Astext(c4)
669 5 POINT(3 3) LINESTRING(400 400,500 500,600 700) POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))
670 SET @g1 = ST_GeomFromText('POINT(2000 2000)');
671 SET @g2 = ST_GeomFromText('POINT(2000 2000)');
672 SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c2, @g1) AND MBRWithin(tab.c3, @g2);
673 c1 ST_AsText(c2) ST_AsText(c3) ST_Astext(c4)
674 DELETE FROM tab WHERE MBRWithin(tab.c2, @g1) AND MBRWithin(tab.c3, @g2);
675 SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c2, @g1) AND MBRWithin(tab.c3, @g2);
676 c1 ST_AsText(c2) ST_AsText(c3) ST_Astext(c4)
677 DROP TABLE tab;
678 CREATE TABLE parent (id POINT, PRIMARY KEY(id)) ENGINE=InnoDB;
679 CREATE TABLE child (id GEOMETRY NOT NULL, parent_id POINT NOT NULL) ENGINE=InnoDB;
680 ALTER TABLE parent ADD SPATIAL INDEX idx1(id);
681 affected rows: 0
682 info: Records: 0 Duplicates: 0 Warnings: 0
683 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id);
684 affected rows: 0
685 info: Records: 0 Duplicates: 0 Warnings: 0
686 SHOW CREATE TABLE parent;
687 Table Create Table
688 parent CREATE TABLE `parent` (
689 `id` point NOT NULL,
690 PRIMARY KEY (`id`(25)),
691 SPATIAL KEY `idx1` (`id`)
692 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
693 SHOW CREATE TABLE child;
694 Table Create Table
695 child CREATE TABLE `child` (
696 `id` geometry NOT NULL,
697 `parent_id` point NOT NULL,
698 SPATIAL KEY `idx2` (`parent_id`)
699 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
700 SHOW INDEX FROM parent;
701 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
702 parent 0 PRIMARY 1 id A 0 25 NULL BTREE NO
703 parent 1 idx1 1 id A NULL 32 NULL SPATIAL NO
704 SHOW INDEX FROM child;
705 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
706 child 1 idx2 1 parent_id A # 32 NULL SPATIAL NO
707 ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ;
708 ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ON DELETE CASCADE ;
709 DROP table child,parent;
710 CREATE TABLE parent (id GEOMETRY, PRIMARY KEY(id(10))) ENGINE=InnoDB;
711 CREATE TABLE child (id GEOMETRY NOT NULL, parent_id GEOMETRY NOT NULL) ENGINE=InnoDB;
712 ALTER TABLE parent ADD SPATIAL INDEX idx1(id) ;
713 affected rows: 0
714 info: Records: 0 Duplicates: 0 Warnings: 0
715 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id);
716 affected rows: 0
717 info: Records: 0 Duplicates: 0 Warnings: 0
718 SHOW CREATE TABLE parent;
719 Table Create Table
720 parent CREATE TABLE `parent` (
721 `id` geometry NOT NULL,
722 PRIMARY KEY (`id`(10)),
723 SPATIAL KEY `idx1` (`id`)
724 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
725 SHOW CREATE TABLE child;
726 Table Create Table
727 child CREATE TABLE `child` (
728 `id` geometry NOT NULL,
729 `parent_id` geometry NOT NULL,
730 SPATIAL KEY `idx2` (`parent_id`)
731 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
732 SHOW INDEX FROM parent;
733 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
734 parent 0 PRIMARY 1 id A 0 10 NULL BTREE NO
735 parent 1 idx1 1 id A NULL 32 NULL SPATIAL NO
736 SHOW INDEX FROM child;
737 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
738 child 1 idx2 1 parent_id A NULL 32 NULL SPATIAL NO
739 ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ;
740 DROP table child,parent;
741 create table t1 (c1 int) engine=innodb;
742 insert into t1 values(NULL);
743 alter table t1 add b geometry, add spatial index(b), algorithm=inplace;
744 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
745 alter table t1 add b geometry not null, add spatial index(b), algorithm=inplace;
746 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
747 alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'),
748 add spatial index(b), algorithm=inplace;
749 affected rows: 0
750 info: Records: 0 Duplicates: 0 Warnings: 0
751 DROP table t1;
752 create table t1 (c1 int) engine=innodb;
753 insert into t1 values(NULL);
754 alter table t1 add b geometry, add spatial index(b), algorithm=copy;
755 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
756 alter table t1 add b geometry not null, add spatial index(b), algorithm=copy;
757 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
758 alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'),
759 add spatial index(b), algorithm=copy;
760 DROP table t1;
761 create table t1 (p point not null default if(unix_timestamp()>10,POINT(1,1),LineString(Point(0,0),Point(1,1)))) ENGINE=innodb;
762 set timestamp=10;
763 insert into t1 values(default);
764 ERROR 22007: Incorrect POINT value: 'LINESTRING(0 0,1 1)' for column `test`.`t1`.`p` at row 1
765 drop table t1;
766 SET timestamp=default;
767 create table t1 (p point not null default if(unix_timestamp()>10,POINT(1,1),LineString(Point(0,0),Point(1,1)))) ENGINE=innodb;
768 set timestamp=10;
769 alter table t1 add column i int;
770 ERROR 22007: Incorrect POINT value: 'LINESTRING(0 0,1 1)' for column `test`.`t1`.`p` at row 0
771 drop table t1;
772 SET timestamp=default;
773 CREATE OR REPLACE TABLE t1 (a INT) ENGINE=InnoDB;
774 ALTER TABLE t1 ADD COLUMN b POINT DEFAULT '0';
775 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
776 DROP TABLE t1;
777 #
778 # MDEV-29520 heap-use-after-poison in row_merge_spatial_rows()
779 #
780 CREATE TABLE t
781 (a VARCHAR(8192), b POINT NOT NULL, PRIMARY KEY(a(8)), SPATIAL(b))
782 ENGINE=InnoDB;
783 INSERT INTO t VALUES (REPEAT('MariaDB Corporation Ab ',351),POINT(0,0));
784 ALTER TABLE t FORCE;
785 DROP TABLE t;
786 #
787 # MDEV-29856 heap-use-after-poison in row_merge_spatial_rows()
788 # with PRIMARY KEY on column prefix
789 #
790 CREATE TABLE t (id INT, f TEXT, s POINT NOT NULL,
791 PRIMARY KEY(id,f(1)), SPATIAL(s)) ENGINE=InnoDB;
792 INSERT INTO t VALUES
793 (1,REPEAT('x',8192),@p:=ST_GeomFromText('POINT(0 0)')),(2,'',@p);
794 ALTER TABLE t FORCE;
795 DROP TABLE t;
796 # End of 10.3 tests