Debian Codegraph @

mysql-test/suite/innodb_gis/t/point_basic.test

raw
1 #********************************************************
2 # wl6942: Store certain GIS data type in the row rather
3 # than as BLOB.
4 #
5 #********************************************************
6
7 --source include/have_geometry.inc
8 --source include/have_innodb.inc
9
10
11 --echo #
12 --echo # Test for basic POINT operation
13 --echo #
14 CREATE TABLE gis_point(p1 POINT, p2 POINT) ENGINE=InnoDB;
15 INSERT INTO gis_point VALUES
16 (ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(0 0)')),
17 (ST_PointFromText('POINT(10 -20)'), ST_PointFromText('POINT(10 -20)')),
18 (ST_PointFromText('POINT(3.1415926 3.535897)'), ST_PointFromText('POINT(-3.932626 -3.488272)')),
19 (ST_PointFromText('POINT(-111.9876 234.1357)'), ST_PointFromText('POINT(-957.1914 958.1919)'));
20 SELECT ST_X(p1), ST_Y(p2) FROM gis_point;
21 DROP TABLE gis_point;
22
23
24 --echo #
25 --echo # Test when POINT is not on any indexes
26 --echo #
27 CREATE TABLE gis_point(p1 POINT, p2 POINT) ENGINE=InnoDB;
28 INSERT INTO gis_point VALUES
29 (ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(100.32374832 101.23741821)')),
30 (ST_PointFromText('POINT(105.34523342 103.18492302)'), ST_PointFromText('POINT(100.32374832 101.23741821)')),
31 (ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(200.32247328 101.86728201)')),
32 (ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(100.32374832 101.98527111)'));
33
34 --replace_column 9 #
35 EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
36 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
37
38 SELECT ST_AsText(p1) FROM gis_point WHERE p1 = p2;
39 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE p1 = ST_PointFromText('POINT(100.32374832 101.23741821)');
40 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE p2 = ST_PointFromText('POINT(200.32247328 101.86728201)');
41
42 DROP TABLE gis_point;
43
44
45 --echo #
46 --echo # Test some ALTER TABLE operations on POINT tables
47 --echo #
48
49 CREATE TABLE t1 (
50 p POINT NOT NULL,
51 g GEOMETRY NOT NULL
52 ) ENGINE=InnoDB;
53
54 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 11)'));
55 INSERT INTO t1 VALUES(ST_PointFromText('POINT(1 1)'), ST_PointFromText('POINT(10 12)'));
56 INSERT INTO t1 VALUES(ST_PointFromText('POINT(1 0)'), ST_PointFromText('POINT(10 13)'));
57 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(10 14)'));
58
59 SELECT ST_AsText(p), ST_AsText(g) FROM t1;
60
61 SELECT c.name, c.mtype, c.prtype, c.len
62 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
63 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
64 ON c.table_id = t.table_id
65 WHERE t.name='test/t1' AND c.name IN ('p','g');
66
67 ALTER TABLE t1 ADD COLUMN p1 POINT, ADD COLUMN p2 POINT, ADD KEY(p);
68 SELECT c.name, c.mtype, c.prtype, c.len
69 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
70 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
71 ON c.table_id = t.table_id
72 WHERE t.name='test/t1' AND c.name IN ('p','g','p1','p2');
73
74 --echo # NOT NULL POINT will use ''
75 SELECT count(*) AS `Expect 4` FROM t1 WHERE p1 = '';
76 SELECT count(*) AS `Expect 4` FROM t1 WHERE p2 = '';
77
78 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p1;
79 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p2;
80
81 ALTER TABLE t1 DROP COLUMN p2;
82
83 --echo # NULLABLE POINT will use NULL
84 ALTER TABLE t1 ADD COLUMN p2 POINT, ADD KEY(p2);
85 SELECT c.name, c.mtype, c.prtype, c.len
86 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
87 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
88 ON c.table_id = t.table_id
89 WHERE t.name='test/t1' AND c.name IN ('p','g','p1','p2');
90
91 SELECT count(*) AS `Expect 4` FROM t1 WHERE p2 IS NULL;
92 UPDATE t1 SET p2 = ST_PointFromText('POINT(10 20)');
93 UPDATE t1 SET p1 = ST_PointFromText('POINT(10 20)');
94
95 ALTER TABLE t1 DROP COLUMN p2;
96 SELECT c.name, c.mtype, c.prtype, c.len
97 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
98 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
99 ON c.table_id = t.table_id
100 WHERE t.name='test/t1' AND c.name IN ('p','g','p1','p2');
101
102 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p1;
103
104 INSERT INTO t1 VALUES (ST_PointFromText('POINT(0.5 0.5)'), ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 19)'));
105 SELECT ST_AsText(p1) FROM t1;
106 DELETE FROM t1 WHERE p1 = ST_PointFromText('POINT(10 19)');
107 SELECT ST_AsText(p1) FROM t1;
108
109 --echo # Add spatial keys on the table
110 --error ER_INDEX_CANNOT_HAVE_NULL
111 ALTER TABLE t1 ADD SPATIAL(p), ADD SPATIAL(p1);
112 SELECT c.name, c.mtype, c.prtype, c.len
113 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
114 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
115 ON c.table_id = t.table_id
116 WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
117
118 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
119 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
120
121 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))'));
122 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))'));
123
124 --echo # Drop spatial keys on the table
125 --error ER_CANT_DROP_FIELD_OR_KEY
126 ALTER TABLE t1 DROP KEY p, DROP KEY p1;
127 SELECT c.name, c.mtype, c.prtype, c.len
128 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
129 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
130 ON c.table_id = t.table_id
131 WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
132
133 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
134 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
135
136 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))'));
137 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))'));
138
139 TRUNCATE t1;
140
141 ALTER TABLE t1 DROP COLUMN p, DROP COLUMN p1;
142 SELECT c.name, c.mtype, c.prtype, c.len
143 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
144 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
145 ON c.table_id = t.table_id
146 WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
147
148 ALTER TABLE t1 ADD COLUMN p POINT, ADD COLUMN p1 POINT;
149 SELECT c.name, c.mtype, c.prtype, c.len
150 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
151 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
152 ON c.table_id = t.table_id
153 WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
154
155 SHOW CREATE TABLE t1;
156
157 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.5 0.5)'), ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 19)'));
158 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.6 0.6)'), ST_PointFromText('POINT(1 1)'), ST_PointFromText('POINT(10 20)'));
159 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.7 0.7)'), ST_PointFromText('POINT(1 0)'), ST_PointFromText('POINT(10 21)'));
160 INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.8 0.8)'), ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(10 22)'));
161
162 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
163 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
164
165 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))'));
166 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))'));
167
168 ALTER TABLE t1 DROP COLUMN p1, ADD COLUMN p1 POINT, CHANGE COLUMN p pp POINT AFTER p1;
169 SELECT c.name, c.mtype, c.prtype, c.len
170 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
171 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
172 ON c.table_id = t.table_id
173 WHERE t.name='test/t1' AND c.name IN ('pp','g','p1');
174
175 UPDATE t1 SET p1 = ST_PointFromText('POINT(5 5)');
176
177 SELECT ST_AsText(pp), ST_AsText(p1) FROM t1;
178
179 --error ER_INDEX_CANNOT_HAVE_NULL
180 ALTER TABLE t1 ADD SPATIAL(p1), ADD SPATIAL(pp), ALGORITHM = COPY;
181 SELECT c.name, c.mtype, c.prtype, c.len
182 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
183 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
184 ON c.table_id = t.table_id
185 WHERE t.name='test/t1' AND c.name IN ('pp','g','p1');
186
187 SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
188 SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
189
190 SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))')) ORDER BY pp;
191 SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE MBRWithin(pp, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))')) ORDER BY pp;
192
193 DROP TABLE t1;
194
195
196 --echo #
197 --echo # Test when the POINT is on B-TREE
198 --echo #
199 CREATE TABLE gis_point(fid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, p POINT, KEY(p)) ENGINE=InnoDB STATS_PERSISTENT=0;
200
201 INSERT INTO gis_point VALUES
202 (101, ST_PointFromText('POINT(10 10)')),
203 (102, ST_PointFromText('POINT(20 10)')),
204 (103, ST_PointFromText('POINT(20 20)')),
205 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
206
207 SELECT ST_AsText(p) FROM gis_point;
208 SELECT ST_AsText(p) FROM gis_point WHERE p = ST_PointFromText('POINT(20 20)');
209
210 INSERT INTO gis_point VALUES
211 (201, ST_PointFromText('POINT(100.32374832 101.23741821)')),
212 (202, ST_PointFromText('POINT(102.43287328 100.23489233)')),
213 (203, ST_PointFromText('POINT(101.43284962 100.45892392)')),
214 (204, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)')))),
215 (205, ST_PointFromText('POINT(101.43284962 100.45892392)')),
216 (206, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)'))));
217
218 -- echo 'The ORDER BY will use filesort'
219 --replace_column 9 #
220 EXPLAIN SELECT ST_AsText(p) FROM gis_point ORDER BY p;
221 SELECT ST_AsText(p) FROM gis_point ORDER BY p;
222
223 SELECT ST_AsText(p), COUNT(*) FROM gis_point GROUP BY p;
224
225 TRUNCATE gis_point;
226
227 INSERT INTO gis_point VALUES
228 (101, ST_PointFromText('POINT(10 10)')),
229 (102, ST_PointFromText('POINT(20 10)')),
230 (103, ST_PointFromText('POINT(20 20)')),
231 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
232
233 --echo # Check if we can create prefix index on POINT
234 ALTER TABLE gis_point ADD COLUMN g POINT, ADD KEY(g(8));
235
236 INSERT INTO gis_point VALUES
237 (105, ST_PointFromText('POINT(25 15)'), ST_PointFromText('POINT(100 100)')),
238 (106, ST_PointFromText('POINT(25 25)'), ST_PointFromText('POINT(110 110)'));
239
240 SELECT ST_AsText(p) FROM gis_point;
241 SELECT ST_AsText(p), ST_AsText(g) FROM gis_point WHERE g IS NULL;
242
243 UPDATE gis_point SET g = ST_PointFromText('POINT(200 200)') WHERE g IS NULL OR g = ST_PointFromText('POINT(110 110)');
244 SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POINT(200 200)');
245
246 --echo # Check the information schema tables
247 SELECT table_name, column_name, data_type, column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='gis_point' AND (column_name = 'p' OR column_name = 'g');
248
249 SELECT c.name, c.mtype, c.prtype, c.len
250 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
251 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
252 ON c.table_id = t.table_id
253 WHERE t.name LIKE 'test/g%' AND c.name IN ('p','g');
254
255 SELECT length(p) FROM gis_point;
256
257 ALTER TABLE gis_point DROP COLUMN g, ALGORITHM = COPY;
258
259 TRUNCATE gis_point;
260
261 INSERT INTO gis_point VALUES
262 (101, ST_PointFromText('POINT(10 10)')),
263 (102, ST_PointFromText('POINT(20 10)')),
264 (103, ST_PointFromText('POINT(20 20)')),
265 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
266
267 --echo # Check if we can create prefix index on POINT
268 ALTER TABLE gis_point ADD COLUMN g POINT, ADD KEY(g(8));
269
270 INSERT INTO gis_point VALUES
271 (105, ST_PointFromText('POINT(25 15)'), ST_PointFromText('POINT(100 100)')),
272 (106, ST_PointFromText('POINT(25 25)'), ST_PointFromText('POINT(110 110)'));
273
274 SELECT ST_AsText(p) FROM gis_point;
275 SELECT ST_AsText(p), ST_AsText(g) FROM gis_point WHERE g IS NULL;
276
277 UPDATE gis_point SET g = ST_PointFromText('POINT(200 200)') WHERE g IS NULL OR g = ST_PointFromText('POINT(110 110)');
278 SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POINT(200 200)');
279
280 CHECK TABLE gis_point;
281
282 SELECT c.name, c.mtype, c.prtype, c.len
283 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
284 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
285 ON c.table_id = t.table_id
286 WHERE t.name='test/gis_point' AND c.name IN ('p','g');
287
288 DROP TABLE gis_point;
289
290 --echo #
291 --echo # Check the mtype of other geometry data types should be 15
292 --echo #
293 CREATE TABLE g (
294 geom GEOMETRY NOT NULL,
295 l LINESTRING NOT NULL,
296 poly POLYGON NOT NULL,
297 mp MULTIPOINT NOT NULL,
298 ml MULTILINESTRING NOT NULL,
299 mpoly MULTIPOLYGON NOT NULL,
300 gc GEOMETRYCOLLECTION NOT NULL
301 ) ENGINE=InnoDB;
302 SELECT c.name, c.mtype, c.prtype, c.len
303 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
304 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
305 ON c.table_id = t.table_id
306 WHERE t.name='test/g';
307
308 DROP TABLE g;
309
310 --echo #
311 --echo # check the mtype and len with CREATE TABLE AS
312 --echo #
313
314 CREATE TABLE t1 (
315 p POINT NOT NULL,
316 g GEOMETRY NOT NULL
317 ) ENGINE=InnoDB;
318
319
320 INSERT INTO t1 VALUES(ST_PointFromText('POINT(10 10)'),ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
321
322 CREATE TABLE t2 ENGINE=InnoDB AS SELECT * FROM t1;
323
324 # Check the mtype and len of the table
325 SELECT t.name, c.name, c.mtype, c.prtype, c.len
326 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
327 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
328 ON c.table_id = t.table_id
329 WHERE t.name LIKE 'test/t%' AND c.name IN ('p','g');
330
331 # Cleanup
332 DROP TABLE t1,t2;
333
334 --echo #
335 --echo # Test when POINT is used in spatial index
336 --echo #
337 SET @ls1 = ST_GeomFromText('LINESTRING(0 20, 10 0)');
338 SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 10 20)');
339 SET @ls3 = ST_GeomFromText('LINESTRING(20 -40, 21 -42)');
340 SET @ls4 = ST_GeomFromText('LINESTRING(20 -42, 21 -40)');
341 SET @poly1 = ST_GeomFromText('POLYGON((2 2, 2 10, 10 10, 10 2, 2 2))');
342 SET @poly2 = ST_GeomFromText('POLYGON((0 0, -5 0, -4 -1, -6 -15, -3 -15, 0 0))');
343 SET @poly3 = ST_GeomFromText('POLYGON((10.0 10.0, 20.5 20, 20.5 50, 32.0 64.0, 32.3 64.6, 5 60, 10 10))');
344 SET @poly4 = ST_GeomFromText('POLYGON((0 10, -10 10, -10 -10, 0 -10, 0 10))');
345 SET @p1 = ST_PointFromText('POINT(0 0)');
346 SET @mpoly = ST_GeomFromText('MULTIPOLYGON(((3 3, 3 16, 16 16, 16 3, 3 3)), ((10 10, 10 50, 50 50, 50 10, 10 10)))');
347
348 CREATE TABLE gis_point (p1 POINT NOT NULL, p2 POINT NOT NULL, SPATIAL KEY k1 (p1), SPATIAL KEY k2 (p2)) ENGINE=InnoDB;
349
350 INSERT INTO gis_point VALUES
351 (ST_PointFromText('POINT(1 2)'), ST_PointFromText('POINT(-1 -3)')),
352 (ST_PointFromText('POINT(2 4)'), ST_PointFromText('POINT(-2 -6)')),
353 (ST_PointFromText('POINT(3 6)'), ST_PointFromText('POINT(-3 -9)')),
354 (ST_PointFromText('POINT(4 8)'), ST_PointFromText('POINT(-4 -12)')),
355 (ST_PointFromText('POINT(5 10)'), ST_PointFromText('POINT(-5 -15)')),
356 (ST_PointFromText('POINT(6 12)'), ST_PointFromText('POINT(-6 -18)')),
357 (ST_PointFromText('POINT(7 14)'), ST_PointFromText('POINT(-7 -21)')),
358 (ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)')),
359 (ST_PointFromText('POINT(9 18)'), ST_PointFromText('POINT(-4 2)')),
360 (ST_PointFromText('POINT(10 21)'), ST_PointFromText('POINT(-6 3)')),
361 (ST_PointFromText('POINT(20.5 41)'), ST_PointFromText('POINT(-8 4)')),
362 (ST_PointFromText('POINT(26.25 57)'), ST_PointFromText('POINT(1 2)')),
363 (ST_PointFromText('POINT(32.1234 64.2468)'), ST_PointFromText('POINT(-1 -1)'));
364
365 CREATE TABLE gis_point1 SELECT * FROM gis_point;
366
367 # Check the mtype and len of the table, should be 14,25
368 SELECT c.name, c.mtype, c.prtype, c.len
369 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
370 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
371 ON c.table_id = t.table_id
372 WHERE t.name='test/gis_point' AND c.name IN ('p1','p2');
373
374 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point1;
375 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Intersection(@ls1, @ls2) = p1;
376 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1);
377 SELECT ST_AsText(p2) FROM gis_point1 WHERE ST_Contains(@poly2, p2);
378 SELECT ST_AsText(p2) FROM gis_point1 WHERE ST_Equals(p2, @p1);
379 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
380 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Contains(@mpoly, p1);
381 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly);
382
383 --echo # Check functions that use MBR, with line type data
384 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @ls1);
385 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @ls1);
386 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @ls1);
387 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @ls1);
388
389 --echo # No matching records
390 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @ls1);
391 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @ls1);
392 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @ls3);
393 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @ls1);
394
395 --echo # Check functions that use MBR, with polygon type data
396 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @poly1);
397 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1);
398 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @poly1);
399 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @poly1);
400 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @poly1);
401
402 --echo # No matching records
403 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @poly1);
404 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @poly1);
405 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @poly1);
406
407 --echo # Check functions that use MBR, with point type data
408 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1);
409 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1);
410 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1);
411 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @p1);
412 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1);
413 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1);
414
415 --echo # No matching records
416 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1);
417 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @p1);
418
419 --echo # Check with MBR functions point type data
420 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1);
421 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1);
422 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1);
423 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1);
424 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1);
425 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @p1);
426
427 --echo # No matching records
428 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1);
429
430 --echo # Check with MBR functions point polygon data
431 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1);
432 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1);
433 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1);
434 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1);
435 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1);
436 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @p1);
437
438 --echo # No matching records
439 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1);
440
441 --echo # Check with MBR functions polygon type data
442 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @poly1);
443 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1);
444 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @poly1);
445 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @poly1);
446
447 --echo # No matching records
448 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @poly1);
449 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @poly1);
450 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @poly1);
451
452 --echo # Check with MBR functions line type data
453 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @ls1);
454 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @ls1);
455 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @ls1);
456 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @ls1);
457 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @ls1);
458
459 --echo # No matching records
460 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @ls1);
461 SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @ls1);
462
463 DROP TABLE gis_point1;
464
465 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point;
466 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Intersection(@ls1, @ls2) = p1;
467 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
468 SELECT ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2);
469 SELECT ST_AsText(p2) FROM gis_point WHERE ST_Equals(p2, @p1);
470 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
471 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Contains(@mpoly, p1);
472 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly);
473
474 --echo # Check functions that use MBR, with line type data
475 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
476 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
477 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
478 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
479
480 --echo # No matching records
481 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @ls1);
482 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
483 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls3);
484 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @ls1);
485
486 --echo # Check functions that use MBR, with polygon type data
487 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
488 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
489 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
490 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @poly1);
491 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
492
493 --echo # No matching records
494 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @poly1);
495 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
496 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
497
498 --echo # Check functions that use MBR, with point type data
499 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
500 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
501 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
502 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @p1);
503 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
504 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
505
506 --echo # No matching records
507 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
508 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @p1);
509
510 --echo # Check with MBR functions point type data
511 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
512 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
513 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
514 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
515 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
516 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
517
518 --echo # No matching records
519 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
520
521 --echo # Check with MBR functions point polygon data
522 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
523 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
524 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
525 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
526 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
527 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
528
529 --echo # No matching records
530 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
531
532 --echo # Check with MBR functions polygon type data
533 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
534 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
535 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
536 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
537
538 --echo # No matching records
539 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
540 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
541 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @poly1);
542
543 --echo # Check with MBR functions line type data
544 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
545 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
546 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
547 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
548 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @ls1);
549
550 --echo # No matching records
551 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls1);
552 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
553
554 --echo # Modify the storage engine to Myisam, Check the spatial functions
555 ALTER TABLE gis_point ENGINE Myisam;
556
557 --echo # Check functions that use MBR, with line type data
558 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
559 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
560 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
561 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
562
563 --echo # No matching records
564 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @ls1);
565 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
566 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls3);
567 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @ls1);
568
569 --echo # Check functions that use MBR, with polygon type data
570 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
571 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
572 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
573 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @poly1);
574 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
575
576 --echo # No matching records
577 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @poly1);
578 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
579 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
580
581 --echo # Check functions that use MBR, with point type data
582 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
583 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
584 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
585 SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @p1);
586 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
587 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
588
589 --echo # No matching records
590 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
591 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @p1);
592
593 --echo # Check with MBR functions point type data
594 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
595 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
596 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
597 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
598 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
599 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
600
601 --echo # No matching records
602 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
603
604 --echo # Check with MBR functions point polygon data
605 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1);
606 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1);
607 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1);
608 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1);
609 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1);
610 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1);
611
612 --echo # No matching records
613 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1);
614
615 --echo # Check with MBR functions polygon type data
616 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1);
617 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1);
618 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1);
619 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1);
620
621 --echo # No matching records
622 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1);
623 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1);
624 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @poly1);
625
626 --echo # Check with MBR functions line type data
627 SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1);
628 SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1);
629 SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1);
630 SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1);
631 SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @ls1);
632
633 --echo # No matching records
634 SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls1);
635 SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1);
636
637 --echo # Modify the storage engine to InnoDB again, do following testing
638 ALTER TABLE gis_point ENGINE InnoDB;
639
640 CHECK TABLE gis_point;
641
642 -- echo The ORDER BY for spatial index will use filesort
643 --replace_column 9 #
644 EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
645 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
646
647 -- echo Try to do IDU on the table and verify the result
648 DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
649
650 INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
651
652 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
653
654 UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 41)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
655
656 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 41)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
657
658 CHECK TABLE gis_point;
659
660 -- echo Use a trx to test the IDU on the table and verify the result
661 START TRANSACTION;
662
663 DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
664
665 INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
666
667 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
668
669 UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 49)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
670
671 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 49)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
672
673 ROLLBACK;
674
675 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 41)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4);
676
677 CHECK TABLE gis_point;
678
679 DROP TABLE gis_point;
680
681
682
683 --echo #
684 --echo # Test inserting/updating different type data into POINT field
685 --echo #
686
687 CREATE TABLE gis_point (i INT, p POINT) ENGINE=InnoDB;
688 CREATE TABLE geom (i INT, g GEOMETRY NOT NULL, SPATIAL KEY(g)) ENGINE=InnoDB;
689
690 INSERT INTO gis_point VALUES(0, ST_PointFromText('POINT(1 1)'));
691 INSERT INTO gis_point VALUES(1, ST_PointFromText('POINT(2 2)'));
692 INSERT INTO gis_point VALUES(2, NULL);
693
694 ALTER TABLE gis_point ADD COLUMN j INT, ALGORITHM = COPY;
695
696 SELECT t.name, c.name, c.mtype, c.prtype, c.len
697 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
698 JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
699 ON c.table_id = t.table_id
700 WHERE t.name='test/gis_point' AND c.name='p';
701
702 SELECT i, ST_AsText(p) FROM gis_point;
703
704 UPDATE gis_point SET p = NULL WHERE p = ST_PointFromText('POINT(1 1)');
705 UPDATE gis_point SET p = ST_PointFromText('POINT(1 2)') WHERE p = ST_PointFromText('POINT(2 2)');
706 UPDATE gis_point SET p = ST_PointFromText('POINT(1 1)') WHERE p IS NULL;
707
708 SELECT i, ST_AsText(p) FROM gis_point;
709
710 INSERT INTO geom VALUES(0, ST_PointFromText('POINT(0 0)'));
711 INSERT INTO geom VALUES(1, ST_PointFromText('POINT(10 10)'));
712 INSERT INTO geom VALUES(2, ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'));
713
714 SELECT ST_AsText(g) FROM geom;
715
716 SELECT ST_AsText(p) FROM gis_point;
717
718 DELETE FROM geom WHERE g = ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
719 --echo From GEOMETRY to POINT, now ALL the data are POINT
720 ALTER TABLE geom MODIFY g POINT NOT NULL;
721 SHOW CREATE TABLE geom;
722 SELECT ST_AsText(g) FROM geom;
723
724 --echo From POINT to GEOMETRY, all data are POINT
725 ALTER TABLE geom MODIFY g GEOMETRY NOT NULL;
726 SHOW CREATE TABLE geom;
727
728 TRUNCATE TABLE geom;
729
730 --echo From GEOMETRY to POINT, the table is empty
731 ALTER TABLE geom MODIFY g POINT NOT NULL;
732 SHOW CREATE TABLE geom;
733 SELECT ST_AsText(g) FROM geom;
734
735 CHECK TABLE gis_point;
736 CHECK TABLE geom;
737
738 DROP TABLE gis_point, geom;
739
740
741
742 --echo #
743 --echo # Test when a geom field, such as POINT, is also a primary key
744 --echo #
745
746 CREATE TABLE t1 (
747 a INT NOT NULL,
748 p POINT NOT NULL,
749 l LINESTRING NOT NULL,
750 g GEOMETRY NOT NULL,
751 PRIMARY KEY(p),
752 SPATIAL KEY `idx2` (p),
753 SPATIAL KEY `idx3` (l),
754 SPATIAL KEY `idx4` (g)
755 ) ENGINE=InnoDB;
756
757 INSERT INTO t1 VALUES(
758 1, ST_GeomFromText('POINT(10 10)'),
759 ST_GeomFromText('LINESTRING(1 1, 5 5, 10 10)'),
760 ST_GeomFromText('POLYGON((30 30, 40 40, 50 50, 30 50, 30 40, 30 30))'));
761
762 INSERT INTO t1 VALUES(
763 2, ST_GeomFromText('POINT(20 20)'),
764 ST_GeomFromText('LINESTRING(2 3, 7 8, 9 10, 15 16)'),
765 ST_GeomFromText('POLYGON((10 30, 30 40, 40 50, 40 30, 30 20, 10 30))'));
766
767 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
768
769 --replace_column 9 #
770 EXPLAIN UPDATE t1 SET p = ST_GeomFromText('POINT(30 30)') WHERE p = ST_GeomFromText('POINT(20 20)');
771 UPDATE t1 SET p = ST_GeomFromText('POINT(30 30)') WHERE p = ST_GeomFromText('POINT(20 20)');
772
773 # Once Bug 18265942 - GIS: INNODB WOULD GET POSSIBLE WRONG FIND_FLAG FOR SELECT FROM SERVER is fixed, we can test all the following statements commented out.
774 #SELECT ST_AsText(p) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((25 25, 25 35, 35 35, 35 25, 25 25))'));
775 #UPDATE t1 SET p = ST_GeomFromText('POINT(15 15)') WHERE MBRWithin(p, ST_GeomFromText('POLYGON((25 25, 25 35, 35 35, 35 25, 25 25))'));
776 #DELETE FROM t1 WHERE MBRWithin(p, ST_GeomFromText('LINESTRING(15 15, 15 25, 25 25, 35 15, 15 15)'));
777 #SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
778 #INSERT INTO t1 VALUES(
779 #3, ST_GeomFromText('POINT(25 25)'),
780 #ST_GeomFromText('LINESTRING(2 3, 7 8, 9 10, 15 16)'),
781 #ST_GeomFromText('POLYGON((10 30, 30 40, 40 50, 40 30, 30 20, 10 30))'));
782 #UPDATE t1 SET p = ST_GeomFromText('POINT(50 50)'), l = ST_GeomFromText('LINESTRING(20 20, 21 21, 22 22, 23 23)') WHERE MBRWithin(g, ST_GeomFromText('LINESTRING(25 25, 25 50, 50 50, 50 25, 25 25)'));
783
784 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
785
786 ALTER TABLE t1 DROP PRIMARY KEY;
787 ALTER TABLE t1 ADD PRIMARY KEY(a);
788
789 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
790
791 ALTER TABLE t1 DROP PRIMARY KEY;
792 ALTER TABLE t1 ADD PRIMARY KEY(p);
793
794 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
795
796 SHOW CREATE TABLE t1;
797
798 # Also waiting for Bug 18265942
799 #UPDATE t1 SET p = ST_PointFromText('POINT(20 20)') WHERE ST_Equals(p, ST_PointFromText('POINT(30 30)'));
800 #INSERT INTO t1 VALUES(
801 #3, ST_GeomFromText('POINT(25 25)'),
802 #ST_GeomFromText('LINESTRING(2 3, 7 8, 9 10, 15 16)'),
803 #ST_GeomFromText('POLYGON((10 30, 30 40, 40 50, 40 30, 30 20, 10 30))'));
804
805 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
806
807 ALTER TABLE t1 DROP PRIMARY KEY;
808 ALTER TABLE t1 ADD PRIMARY KEY(p);
809
810 --replace_column 9 #
811 EXPLAIN SELECT a, ST_AsText(p) FROM t1 WHERE a = 2 AND p = ST_GeomFromText('POINT(30 30)');
812 SELECT a, ST_AsText(p) FROM t1 WHERE a = 2 AND p = ST_GeomFromText('POINT(30 30)');
813
814 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
815
816 CHECK TABLE t1;
817
818 DROP TABLE t1;
819
820 --echo #
821 --echo # Test for foreign keys.
822 --echo #
823 CREATE TABLE parent(p POINT, PRIMARY KEY(p)) ENGINE=InnoDB;
824 CREATE TABLE child(p POINT NOT NULL) ENGINE=InnoDB;
825 ALTER TABLE parent ADD SPATIAL INDEX idx1(p);
826 ALTER TABLE child ADD SPATIAL INDEX idx2(p);
827 SHOW CREATE TABLE parent;
828 SHOW CREATE TABLE child;
829
830 --error ER_CANT_CREATE_TABLE
831 ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
832 show warnings;
833
834 ALTER TABLE parent DROP INDEX idx1;
835 --error ER_CANNOT_ADD_FOREIGN,ER_CANT_CREATE_TABLE
836 ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
837 show warnings;
838
839 ALTER TABLE child DROP INDEX idx2;
840 --error ER_CANNOT_ADD_FOREIGN,ER_CANT_CREATE_TABLE
841 ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
842 show warnings;
843
844 DROP TABLE child, parent;
845
846 --echo #
847 --echo # Bug#28763: Selecting geometry fields in UNION caused server crash.
848 --echo #
849 CREATE TABLE t1(f1 GEOMETRY, f2 POINT, f3 GEOMETRY) ENGINE=InnoDB;
850 SELECT f1 FROM t1 UNION SELECT f1 FROM t1;
851 INSERT INTO t1 (f2,f3) VALUES (ST_GeomFromText('POINT(1 1)'),
852 ST_GeomFromText('POINT(2 2)'));
853 SELECT ST_AsText(f2),ST_AsText(f3) FROM t1;
854 SELECT ST_AsText(a) FROM (SELECT f2 AS a FROM t1 UNION SELECT f3 FROM t1) t;
855 CREATE TABLE t2 AS SELECT f2 AS a FROM t1 UNION SELECT f3 FROM t1;
856 DESC t2;
857 SELECT ST_AsText(a) FROM t2;
858 DROP TABLE t1, t2;
859