Debian Codegraph @

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

raw
1 # WL#6455 GEOMETRY datatypes support
2 # In order to implement GIS indexing in InnoDB,
3 # InnoDB needs to support GEOMETRY datatypes,
4 # so that InnoDB stores them properly and understands them.
5
6 --source include/have_debug.inc
7
8 # Avoid CrashReporter popup on Mac
9 --source include/not_crashrep.inc
10
11 # Turn on the geometry data print.
12 SET SESSION debug="+d,row_print_geometry_data";
13
14 # Test GEOMETRY datatype.
15 CREATE TABLE t_wl6455 ( i INT, g GEOMETRY NOT NULL) ENGINE=InnoDB;
16 SHOW CREATE TABLE t_wl6455;
17
18 # Insert Point.
19 INSERT INTO t_wl6455 VALUES(1, POINT(1,1));
20
21 INSERT INTO t_wl6455 VALUES(2, POINT(2,2));
22
23 # Insert MultiPoint.
24 SET @mp = 'MULTIPOINT(0 0, 20 20, 60 60)';
25 INSERT INTO t_wl6455 VALUES(3, ST_GeomFromText(@mp));
26
27 # Insert LineString.
28 INSERT INTO t_wl6455 VALUES(4, LINESTRING(POINT(1,1), POINT(4, 4)));
29
30 INSERT INTO t_wl6455 VALUES(5, LINESTRING(POINT(2,2), POINT(5, 5)));
31
32 # Insert MultiLineString.
33 SET @mls = 'MultiLineString((1 1,2 2,3 3),(4 4,5 5))';
34 INSERT INTO t_wl6455 VALUES(6, ST_GeomFromText(@mls));
35
36 # Insert Polygon.
37 SET @poly = 'Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))';
38 INSERT INTO t_wl6455 VALUES(7, ST_GeomFromText(@poly));
39
40 # Insert MultiPolygon.
41 SET @mpoly = 'MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))';
42 INSERT INTO t_wl6455 VALUES(8, ST_GeomFromText(@mpoly));
43
44 # Insert GeometryCollection.
45 SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';
46 INSERT INTO t_wl6455 VALUES(9, ST_GeomFromText(@gc));
47
48 # Show result.
49 SELECT ST_AsText(g) FROM t_wl6455;
50
51 # Test create prefix index.
52 --error ER_WRONG_SUB_KEY
53 CREATE INDEX i_p ON t_wl6455 (g(10));
54
55 # Select on prefix index.
56 --replace_column 10 #
57 EXPLAIN SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(2,2);
58 SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(2,2);
59
60 # Test rollback/commit
61 SET AUTOCOMMIT = 0;
62 INSERT INTO t_wl6455 VALUES(10, POINT(10,10));
63 SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(10,10);
64 ROLLBACK;
65 SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(10,10);
66
67 INSERT INTO t_wl6455 VALUES(10, POINT(10,10));
68 COMMIT;
69
70 INSERT INTO t_wl6455 VALUES(11, POINT(11,11));
71 BEGIN;
72 INSERT INTO t_wl6455 VALUES(1, POINT(1,1));
73
74 --source include/kill_and_restart_mysqld.inc
75
76 CHECK TABLE t_wl6455;
77 SELECT ST_AsText(g) FROM t_wl6455;
78
79 # Check information_schema, the mtype of GEOMETRY datatype is 14.
80 SELECT sc.name, sc.pos, sc.mtype
81 FROM information_schema.innodb_columns sc
82 INNER JOIN information_schema.innodb_tables st
83 ON sc.TABLE_ID=st.TABLE_ID
84 WHERE st.NAME='test/t_wl6455' AND sc.NAME='g';
85
86 # Clean up
87 DROP TABLE t_wl6455;