Debian Codegraph @

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

raw
1 #********************************************************
2 # wl6455: GIS Datatypes covers the following tests.
3 # DDL: ALTER PRIMARY, DROP KEY,INDEX,COLUMN TYPE etc
4 # INSERT/SELECT operations with WKT & WKB functions
5 # SELECT/DELETE/UPDATE with =,>,< predicate conditions
6 # Datatypes with Procedure (IN/INOUT/OUT,Cursors,Trigger)
7 # Datatypes with Aggregate functions
8 # File Formts Compressed & Dynamic
9 # All Geometric functions.
10 #********************************************************
11 --source include/have_debug.inc
12 --source include/have_innodb_max_16k.inc
13
14 SET default_storage_engine=InnoDB;
15 SET innodb_strict_mode=OFF;
16
17 # Turn on the geometry data print.
18 SET SESSION debug="+d,row_print_geometry_data";
19
20 SHOW VARIABLES LIKE '%storage_engine%';
21
22 #
23 # Spatial objects
24 #
25
26 USE test;
27
28 --disable_warnings
29 DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon;
30 DROP TABLE IF EXISTS gis_multi_point, gis_multi_line, gis_multi_polygon;
31 DROP TABLE IF EXISTS gis_geometrycollection, gis_geometry;
32 DROP TABLE IF EXISTS tab,tab2,tab3,parent,emp2;
33 DROP PROCEDURE IF EXISTS geominout;
34 DROP PROCEDURE IF EXISTS geom_insert;
35 DROP TRIGGER IF EXISTS geom_trigger;
36 DROP PROCEDURE IF EXISTS geom_cursor;
37
38
39 --enable_warnings
40
41 CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT) ENGINE=InnoDB;
42 CREATE TABLE gis_line (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING) ENGINE=InnoDB;
43 CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON) ENGINE=InnoDB;
44 CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT) ENGINE=InnoDB;
45 CREATE TABLE gis_multi_line (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING) ENGINE=InnoDB;
46 CREATE TABLE gis_multi_polygon (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON) ENGINE=InnoDB;
47 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION) ENGINE=InnoDB;
48 CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY) ENGINE=InnoDB;
49
50 SHOW FIELDS FROM gis_point;
51 SHOW FIELDS FROM gis_line;
52 SHOW FIELDS FROM gis_polygon;
53 SHOW FIELDS FROM gis_multi_point;
54 SHOW FIELDS FROM gis_multi_line;
55 SHOW FIELDS FROM gis_multi_polygon;
56 SHOW FIELDS FROM gis_geometrycollection;
57 SHOW FIELDS FROM gis_geometry;
58
59 --echo #INSERT using all WKT & WKB functions
60 INSERT INTO gis_point VALUES
61 (101, ST_PointFromText('POINT(10 10)')),
62 (102, ST_PointFromText('POINT(20 10)')),
63 (103, ST_PointFromText('POINT(20 20)')),
64 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
65
66
67 INSERT INTO gis_line VALUES
68 (105, ST_LineFromText('LINESTRING(0 0,0 10,10 0)')),
69 (106, ST_LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
70 (107, ST_LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(40, 10)))));
71
72
73 INSERT INTO gis_polygon VALUES
74 (108, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
75 (109, ST_PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
76 (110, ST_PolyFromWKB(ST_AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));
77
78
79 INSERT INTO gis_multi_point VALUES
80 (111, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
81 (112, ST_MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
82 (113, ST_MPointFromWKB(ST_AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));
83
84
85 INSERT INTO gis_multi_line VALUES
86 (114, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
87 (115, ST_MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
88 (116, ST_MLineFromWKB(ST_AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))));
89
90
91 INSERT INTO gis_multi_polygon VALUES
92 (117, ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),
93 ((59 18,67 18,67 13,59 13,59 18)))')),
94 (118, ST_MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),
95 (52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')),
96 (119, ST_MPolyFromWKB(ST_AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));
97
98
99 INSERT INTO gis_geometrycollection VALUES
100 (120, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
101 (121, ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(10 10), LINESTRING(10 10,20 20))')),
102 (122, ST_GeometryFromWKB(ST_AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))));
103
104 --echo #check INSERT using SELECT query
105 INSERT into gis_geometry SELECT * FROM gis_point;
106 INSERT into gis_geometry SELECT * FROM gis_line;
107 INSERT into gis_geometry SELECT * FROM gis_polygon;
108 INSERT into gis_geometry SELECT * FROM gis_multi_point;
109 INSERT into gis_geometry SELECT * FROM gis_multi_line;
110 INSERT into gis_geometry SELECT * FROM gis_multi_polygon;
111 INSERT into gis_geometry SELECT * FROM gis_geometrycollection;
112
113 --echo #check format coversion functions & spatial values
114 SELECT fid, ST_AsText(g) FROM gis_point;
115 SELECT fid, ST_AsText(g) FROM gis_line;
116 SELECT fid, ST_AsText(g) FROM gis_polygon;
117 SELECT fid, ST_AsText(g) FROM gis_multi_point;
118 SELECT fid, ST_AsText(g) FROM gis_multi_line;
119 SELECT fid, ST_AsText(g) FROM gis_multi_polygon;
120 SELECT fid, ST_AsText(g) FROM gis_geometrycollection;
121 SELECT fid, ST_AsText(g) FROM gis_geometry;
122
123 --echo #2.1 check DDL functionality on GIS datatypes
124 CREATE TABLE tab(c1 POINT,c2 LINESTRING,c3 POLYGON,C4 MULTIPOINT,c5 MULTILINESTRING ,
125 c6 MULTIPOLYGON,c7 GEOMETRYCOLLECTION ,c8 GEOMETRY) ENGINE=InnoDB;
126
127 --echo #check information schema for all the columns refer to 14, except POINT which is 15
128 SELECT sc.name, sc.pos, sc.mtype
129 FROM information_schema.innodb_columns sc
130 INNER JOIN information_schema.innodb_tables st
131 ON sc.TABLE_ID=st.TABLE_ID
132 WHERE st.NAME='test/tab'
133 ORDER BY sc.name;
134
135 --echo #check Perform convesrion before INSERT using WKT functions
136 SET @c1=ST_PointFromText('POINT(10 10)');
137 SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
138 SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
139 SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
140 SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
141 SET @c6=ST_MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))');
142 SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
143 SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
144
145 --echo #Insert the spatial values
146 INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
147
148 --echo #check index on GIS datatypes
149 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
150 CREATE INDEX idx1 on tab(c2(5) DESC) USING BTREE;
151 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
152 CREATE INDEX idx3 on tab(c3(5) ASC) USING BTREE;
153 --error ER_WRONG_SUB_KEY
154 CREATE UNIQUE INDEX idx2 on tab(c8(5) ASC) ;
155
156 --echo #check equality predicate on the index columns
157 --replace_column 10 #
158 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
159 WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
160
161 --echo #check index with WKB function
162 --replace_column 10 #
163 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
164 FROM tab WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
165
166 --echo #check index with WKT function
167 --replace_column 10 #
168 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
169 WHERE c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
170
171 --echo #check index with WKB function
172 --replace_column 10 #
173 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
174 FROM tab
175 WHERE c3=ST_PolyFromWKB(ST_AsWkB(ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))')));
176
177 --echo #check index with WKT function
178 --replace_column 10 #
179 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
180 WHERE c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
181
182 --echo #check index with WKB function
183 --replace_column 10 #
184 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
185 FROM tab
186 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
187
188 --echo #check range predicate on the index columns
189 --replace_column 10 #
190 --error ER_WRONG_ARGUMENTS
191 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
192 WHERE c2>=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
193
194 --replace_column 10 #
195 --error ER_WRONG_ARGUMENTS
196 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
197 WHERE c3>=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
198
199 --replace_column 10 #
200 --error ER_WRONG_ARGUMENTS
201 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
202 WHERE c8>=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
203
204 --echo #check index with DELETE operation
205 --replace_column 10 #
206 EXPLAIN DELETE FROM tab
207 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
208
209
210 --echo #check the spatial values
211 SELECT ST_AsText(c1) FROM tab;
212 SELECT ST_AsText(c2) FROM tab;
213 SELECT ST_AsText(c3) FROM tab;
214 SELECT ST_AsText(c4) FROM tab;
215 SELECT ST_AsText(c5) FROM tab;
216 SELECT ST_AsText(c6) FROM tab;
217 SELECT ST_AsText(c7) FROM tab;
218 SELECT ST_AsText(c8) From tab;
219
220 --echo #check by modify the column type
221 ALTER TABLE tab MODIFY COLUMN c1 Geometry;
222 ALTER TABLE tab MODIFY COLUMN c2 Geometry;
223 ALTER TABLE tab MODIFY COLUMN c3 Geometry;
224 ALTER TABLE tab MODIFY COLUMN c4 Geometry;
225 ALTER TABLE tab MODIFY COLUMN c5 Geometry;
226 ALTER TABLE tab MODIFY COLUMN c6 Geometry;
227 ALTER TABLE tab MODIFY COLUMN c7 Geometry;
228
229 --echo #check column datatypes
230 SHOW FIELDS FROM tab;
231
232 --echo #check the data after modify
233 SELECT ST_AsText(c1) FROM tab;
234 SELECT ST_AsText(c2) FROM tab;
235 SELECT ST_AsText(c3) FROM tab;
236 SELECT ST_AsText(c4) FROM tab;
237 SELECT ST_AsText(c5) FROM tab;
238 SELECT ST_AsText(c6) FROM tab;
239 SELECT ST_AsText(c7) FROM tab;
240 SELECT ST_AsText(c8) From tab;
241
242 --echo #check by modify the column type
243 ALTER TABLE tab MODIFY COLUMN c1 POINT;
244 ALTER TABLE tab MODIFY COLUMN c2 LINESTRING;
245 ALTER TABLE tab MODIFY COLUMN c3 POLYGON;
246 ALTER TABLE tab MODIFY COLUMN c4 MULTIPOINT;
247 ALTER TABLE tab MODIFY COLUMN c5 MULTILINESTRING;
248 ALTER TABLE tab MODIFY COLUMN c6 MULTIPOLYGON;
249 ALTER TABLE tab MODIFY COLUMN c7 GEOMETRYCOLLECTION;
250
251 --echo #check column datatypes
252 SHOW FIELDS FROM tab;
253
254 --echo #check the data after modify
255 SELECT ST_AsText(c1) FROM tab;
256 SELECT ST_AsText(c2) FROM tab;
257 SELECT ST_AsText(c3) FROM tab;
258 SELECT ST_AsText(c4) FROM tab;
259 SELECT ST_AsText(c5) FROM tab;
260 SELECT ST_AsText(c6) FROM tab;
261 SELECT ST_AsText(c7) FROM tab;
262 SELECT ST_AsText(c8) From tab;
263
264 --echo #check change the column type
265 ALTER TABLE tab MODIFY COLUMN c1 BLOB;
266
267 --echo #check column datatypes
268 SHOW CREATE TABLE tab;
269
270 --echo #check the data after modify
271 SELECT ST_AsText(c1) FROM tab;
272
273 --echo #change the column name
274 ALTER TABLE tab CHANGE COLUMN c1 c0 GEOMETRY ;
275
276
277 --echo #check column datatypes
278 SHOW FIELDS FROM tab;
279
280 --echo #add primary key
281 --error ER_WRONG_SUB_KEY
282 ALTER TABLE tab ADD PRIMARY KEY pk2(c8(5));
283
284 --echo #check columns
285 SHOW FIELDS FROM tab;
286
287 --echo #check columns
288 SHOW FIELDS FROM tab;
289
290 --echo #cleanup the table
291 TRUNCATE TABLE tab;
292
293 --echo #check with procedures
294
295 delimiter |;
296
297 --echo #crate proc with INOUT params
298 CREATE PROCEDURE geom_insert(IN c1 POINT,IN c2 LINESTRING,IN c3 POLYGON,
299 IN c4 MULTIPOINT,IN c5 MULTILINESTRING, IN c6 MULTIPOLYGON,IN c7 GEOMETRYCOLLECTION,
300 IN c8 GEOMETRY)
301 BEGIN
302 INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
303 END |
304
305 delimiter ;|
306
307 --echo #set the spatial values
308 SET @c1=ST_PointFromText('POINT(10 10)');
309 SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
310 SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
311 SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
312 SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
313 SET @c6=ST_MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))');
314 SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
315 SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
316
317 --echo #call the proc
318 CALL geom_insert(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
319
320 --echo #check the values ofter proc call
321 SELECT ST_AsText(c0) FROM tab;
322 SELECT ST_AsText(c2) FROM tab;
323 SELECT ST_AsText(c3) FROM tab;
324 SELECT ST_AsText(c4) FROM tab;
325 SELECT ST_AsText(c5) FROM tab;
326 SELECT ST_AsText(c6) FROM tab;
327 SELECT ST_AsText(c7) FROM tab;
328 SELECT ST_AsText(c8) From tab;
329
330
331 --echo #set the input spatial value
332 SET @c9=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
333
334 delimiter |;
335
336 --echo #crate a proc with INOUT params and pass a value.
337 CREATE PROCEDURE geominout(INOUT c9 GEOMETRY)
338 BEGIN
339
340 SELECT ST_AsText(@c9);
341 SET c9=ST_GeomCollFromText('GeometryCollection(Point(10 10),LineString(20 20, 30 30))');
342
343 END|
344
345 delimiter ;|
346
347 --echo #call the proc and then change the spatial value of c8
348 CALL geominout(@c9);
349
350 --echo #now check the out spatial values should be changed to new values
351 SELECT ST_AsText(@c9);
352
353 #check GIS datatype with Triggers
354
355 --echo #delete the records
356 TRUNCATE TABLE tab;
357
358 --echo #create another table same as tab
359 CREATE TABLE tab2 AS SELECT * FROM tab;
360
361 --echo #check the table definition
362 SHOW CREATE TABLE tab2;
363
364
365 delimiter |;
366
367 --echo #create a tigger and populate the values into tab2
368 CREATE TRIGGER geom_trigger AFTER INSERT ON tab
369 FOR EACH ROW
370 BEGIN
371 INSERT INTO tab2 VALUES (@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
372 END|
373
374 delimiter ;|
375
376 --echo #set the spatial values
377 SET @c1=ST_PointFromText('POINT(10 10)');
378 SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
379 SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
380 SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
381 SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
382 SET @c6=ST_MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))');
383 SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
384 SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
385
386 --echo #Insert the spatial values
387 INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
388
389 --echo #check the values whether populated
390 SELECT ST_AsText(c0) FROM tab2;
391 SELECT ST_AsText(c2) FROM tab2;
392 SELECT ST_AsText(c3) FROM tab2;
393 SELECT ST_AsText(c4) FROM tab2;
394 SELECT ST_AsText(c5) FROM tab2;
395 SELECT ST_AsText(c6) FROM tab2;
396 SELECT ST_AsText(c7) FROM tab2;
397 SELECT ST_AsText(c8) From tab2;
398
399 --echo #check Cursor with Geometry
400
401 delimiter |;
402
403 CREATE PROCEDURE geom_cursor()
404 BEGIN
405 DECLARE v GEOMETRY;
406 DECLARE c CURSOR FOR SELECT c8 FROM tab2;
407
408 OPEN c;
409 FETCH c INTO v;
410 CLOSE c;
411
412 SELECT ST_AsText(v);
413 END|
414
415 delimiter ;|
416
417 --echo # the following line was commented due the Bug#16282246
418 --echo # Once it is fixed, the comment will be removed.
419 --echo # right now bug fixing date is unknown.
420 #CALL geom_cursor();
421
422 --echo check self join
423 SELECT ST_AsText(a.c0),ST_AsText(a.c2),ST_AsText(a.c3)
424 FROM tab a,tab2 b WHERE a.c0=b.c0 AND a.c2=b.c2
425 AND a.c3=b.c3 AND a.c4=b.c4 AND a.c5=b.c5 AND a.c6=b.c6
426 AND a.c7=b.c7 AND a.c8=b.c8;
427
428 --echo #check equi join
429 SELECT ST_AsText(a.c2),ST_AsText(b.c2) FROM tab a,tab2 b WHERE a.c2=b.c2;
430
431 --echo #check DELETE stmt with Where clause and a constant predicate
432 --error ER_TRUNCATED_WRONG_VALUE
433 DELETE FROM tab
434 Where c8=c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
435
436 --echo #check the data, should 0
437 SELECT COUNT(*) FROM tab;
438
439 --echo #check UPDATE stmt with Where clause and a constant predicate
440 SET @c8=ST_GeomCollFromText('GeometryCollection(Point(10 10),LineString(20 20, 30 30))');
441 UPDATE tab2 SET c8=@c8
442 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
443
444 --echo #check the column should be modified to LINESTRING(10 10,20 20,30 30)
445 SELECT ST_AsText(c8) From tab2;
446
447 --echo #check GIS datatypes with aggregate functions
448 SELECT COUNT(ST_AsText(g)) FROM gis_point;
449 SELECT COUNT(ST_AsText(g)) FROM gis_line;
450 SELECT COUNT(ST_AsText(g)) FROM gis_polygon;
451 SELECT COUNT(ST_AsText(g)) FROM gis_multi_point;
452 SELECT COUNT(ST_AsText(g)) FROM gis_multi_line;
453 SELECT COUNT(ST_AsText(g)) FROM gis_multi_polygon;
454 SELECT COUNT(ST_AsText(g)) FROM gis_geometrycollection;
455 SELECT COUNT(ST_AsText(g)) FROM gis_geometry;
456
457 SELECT SUM(ST_AsText(g)) FROM gis_point;
458 SELECT SUM(ST_AsText(g)) FROM gis_line;
459 SELECT AVG(ST_AsText(g)) FROM gis_polygon;
460 --echo #here it show some string value no meaning
461 SELECT MAX(ST_AsText(g)) FROM gis_multi_point;
462 --echo #here it show some string value no meaning
463 SELECT MIN(ST_AsText(g)) FROM gis_multi_line;
464 SELECT STD(ST_AsText(g)) FROM gis_multi_polygon;
465 SELECT COUNT(ST_AsText(g)) FROM gis_geometrycollection;
466 SELECT AVG(ST_AsText(g)) FROM gis_geometry;
467
468 --disable_warnings
469 --echo #check Datatypes with compression tables
470
471 CREATE TABLE tab3(c1 POINT,c2 LINESTRING,c3 POLYGON,C4 MULTIPOINT,c5 MULTILINESTRING ,
472 c6 MULTIPOLYGON,c7 GEOMETRYCOLLECTION ,c8 GEOMETRY) ENGINE=InnoDB
473 KEY_BLOCK_SIZE=8 ROW_FORMAT=COMPRESSED;
474
475 --echo #check file format
476 --replace_regex /KEY_BLOCK_SIZE=[2,8]/KEY_BLOCK_SIZE=key_block_size/
477 SHOW CREATE TABLE tab3;
478
479 ALTER TABLE tab3 KEY_BLOCK_SIZE=16 ROW_FORMAT=Dynamic;
480
481 --echo #check file format
482 SHOW CREATE TABLE tab3;
483
484 --echo #check information schema for all the columns refer to 14, except POINT which is 15
485 SELECT sc.name, sc.pos, sc.mtype
486 FROM information_schema.innodb_columns sc
487 INNER JOIN information_schema.innodb_tables st
488 ON sc.TABLE_ID=st.TABLE_ID
489 WHERE st.NAME='test/tab3'
490 ORDER BY sc.name;
491
492 --echo #check Perform convesrion before INSERT using WKT functions
493 SET @c1=ST_PointFromText('POINT(10 10)');
494 SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
495 SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
496 SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
497 SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
498 SET @c6=ST_MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))');
499 SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
500 SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
501
502 --echo #Insert the spatial values
503 INSERT INTO tab3 VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
504
505 --echo #check index on GIS datatypes
506 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
507 CREATE INDEX idx1 on tab3(c2(5) DESC) USING BTREE;
508 --error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
509 CREATE INDEX idx3 on tab3(c3(5) ASC) USING BTREE;
510 --error ER_WRONG_SUB_KEY
511 CREATE UNIQUE INDEX idx2 on tab3(c8(5) ASC) ;
512
513 --enable_warnings
514
515 --echo #check equality predicate on the index columns
516 --replace_column 10 #
517 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
518 WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
519
520 --echo #check index with WKB function
521 --replace_column 10 #
522 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
523 FROM tab3 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
524
525 --echo #check index with WKT function
526 --replace_column 10 #
527 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
528 WHERE c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
529
530 --echo #check index with WKB function
531 --replace_column 10 #
532 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
533 FROM tab3
534 WHERE c3=ST_PolyFromWKB(ST_AsWkB(ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))')));
535
536 --echo #check index with WKT function
537 --replace_column 10 #
538 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
539 WHERE c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
540
541 --echo #check index with WKB function
542 --replace_column 10 #
543 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
544 FROM tab3
545 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
546
547 --echo #check range predicate on the index columns
548 --replace_column 10 #
549 --error ER_WRONG_ARGUMENTS
550 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
551 WHERE c2>=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
552
553 --replace_column 10 #
554 --error ER_WRONG_ARGUMENTS
555 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
556 WHERE c3>=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
557
558 --replace_column 10 #
559 --error ER_WRONG_ARGUMENTS
560 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
561 WHERE c8>=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
562
563 --echo #check index with DELETE operation
564 --replace_column 10 #
565 EXPLAIN DELETE FROM tab3
566 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
567
568
569 --echo #check the spatial values
570 SELECT ST_AsText(c1) FROM tab3;
571 SELECT ST_AsText(c2) FROM tab3;
572 SELECT ST_AsText(c3) FROM tab3;
573 SELECT ST_AsText(c4) FROM tab3;
574 SELECT ST_AsText(c5) FROM tab3;
575 SELECT ST_AsText(c6) FROM tab3;
576 SELECT ST_AsText(c7) FROM tab3;
577 SELECT ST_AsText(c8) From tab3;
578
579 --echo #check with Row_format = Dynamic
580 --disable_warnings
581 ALTER TABLE tab3 ROW_FORMAT=Dynamic;
582 --enable_warnings
583
584 --echo #check file format
585 SHOW CREATE TABLE tab3;
586
587 --echo #check index with WKB function
588 --replace_column 10 #
589 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
590 FROM tab3 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
591
592 --echo #check index with WKT function
593 --replace_column 10 #
594 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
595 WHERE c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
596
597
598
599 --echo #check the Geometry property functions
600 SELECT fid, ST_Dimension(g) FROM gis_geometry;
601 SELECT fid, ST_GeometryType(g) FROM gis_geometry;
602 SELECT fid, ST_IsEmpty(g) FROM gis_geometry;
603 SELECT fid, ST_AsText(ST_Envelope(g)) FROM gis_geometry;
604 --replace_column 10 #
605 explain select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry;
606
607 --echo #check Geometry point functions
608 SELECT fid, ST_X(g) FROM gis_point;
609 SELECT fid, ST_Y(g) FROM gis_point;
610 --replace_column 10 #
611 explain select ST_X(g),ST_Y(g) FROM gis_point;
612
613 SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line;
614 SELECT fid, ST_AsText(ST_EndPoint(g)) FROM gis_line;
615 SELECT fid, ST_Length(g) FROM gis_line;
616 SELECT fid, ST_NumPoints(g) FROM gis_line;
617 SELECT fid, ST_AsText(ST_PointN(g, 2)) FROM gis_line;
618 SELECT fid, ST_IsClosed(g) FROM gis_line;
619 --replace_column 10 #
620 explain select ST_AsText(ST_StartPoint(g)),ST_AsText(ST_EndPoint(g)),
621 ST_Length(g),ST_NumPoints(g),ST_AsText(ST_PointN(g, 2)),ST_IsClosed(g) FROM gis_line;
622
623 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon;
624 SELECT fid, ST_Area(g) FROM gis_polygon;
625 SELECT fid, ST_AsText(ST_ExteriorRing(g)) FROM gis_polygon;
626 SELECT fid, ST_NumInteriorRings(g) FROM gis_polygon;
627 SELECT fid, ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
628 --replace_column 10 #
629 explain select ST_AsText(ST_Centroid(g)),ST_Area(g),ST_AsText(ST_ExteriorRing(g)),
630 ST_NumInteriorRings(g),ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
631
632 SELECT fid, ST_IsClosed(g) FROM gis_multi_line;
633
634 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_multi_polygon;
635 SELECT fid, ST_Area(g) FROM gis_multi_polygon;
636
637 SELECT fid, ST_NumGeometries(g) from gis_multi_point;
638 SELECT fid, ST_NumGeometries(g) from gis_multi_line;
639 SELECT fid, ST_NumGeometries(g) from gis_multi_polygon;
640 SELECT fid, ST_NumGeometries(g) from gis_geometrycollection;
641 --replace_column 10 #
642 explain SELECT fid, ST_NumGeometries(g) from gis_multi_point;
643
644 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
645 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_line;
646 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_polygon;
647 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_geometrycollection;
648 SELECT fid, ST_AsText(ST_GeometryN(g, 1)) from gis_geometrycollection;
649 --replace_column 10 #
650 explain SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
651
652 SELECT g1.fid as first, g2.fid as second,
653 MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
654 MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
655 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
656 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
657 --replace_column 10 #
658 explain SELECT g1.fid as first, g2.fid as second,
659 MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
660 MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
661 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
662 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
663
664 --echo # check support of Foreign Key constraint
665 --error ER_WRONG_SUB_KEY
666 CREATE TABLE parent (id GEOMETRY NOT NULL,PRIMARY KEY (id(10))) ENGINE=INNODB;
667
668 --error ER_WRONG_SUB_KEY
669 CREATE TABLE child (id GEOMETRY, parent_id GEOMETRY,
670 INDEX par_ind (parent_id(10)),
671 FOREIGN KEY (parent_id(10)) REFERENCES parent(id)
672 ON DELETE CASCADE
673 ) ENGINE=INNODB;
674
675 --echo #check partition table support
676 --error 1178
677 CREATE TABLE emp2(
678 id GEOMETRY NOT NULL,
679 store_name VARCHAR(30),
680 parts VARCHAR(30),
681 store_id GEOMETRY
682 )
683 PARTITION BY LIST(store_id) (
684 PARTITION pNorth VALUES IN (10,20,30) ,
685 PARTITION pEast VALUES IN (40,50,60) ,
686 PARTITION pWest VALUES IN (70,80,100)
687 );
688
689 --echo #check start transaction commit & Rollback
690 START TRANSACTION;
691 DELETE FROM tab3;
692 ROLLBACK;
693
694 SELECT COUNT(*) FROM tab3;
695
696 START TRANSACTION;
697 DELETE FROM tab3;
698 COMMIT;
699
700 SELECT COUNT(*) FROM tab3;
701
702 DROP TABLE IF EXISTS gis_point, gis_line, gis_polygon, gis_multi_point;
703 DROP TABLE IF EXISTS gis_multi_line, gis_multi_polygon;
704 DROP TABLE IF EXISTS gis_geometrycollection, gis_geometry;
705 DROP TABLE IF EXISTS tab,tab2,tab3,parent,emp2;
706 DROP PROCEDURE IF EXISTS geominout;
707 DROP PROCEDURE IF EXISTS geom_insert;
708 DROP TRIGGER IF EXISTS geom_trigger;
709 DROP PROCEDURE IF EXISTS geom_cursor;