Debian Codegraph @

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

raw
1 CREATE TABLE t_wl6455 ( i INT, g GEOMETRY NOT NULL) ENGINE=InnoDB;
2 SHOW CREATE TABLE t_wl6455;
3 Table Create Table
4 t_wl6455 CREATE TABLE `t_wl6455` (
5 `i` int(11) DEFAULT NULL,
6 `g` geometry NOT NULL
7 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
8 INSERT INTO t_wl6455 VALUES(1, POINT(1,1));
9 INSERT INTO t_wl6455 VALUES(2, POINT(2,2));
10 SET @mp = 'MULTIPOINT(0 0, 20 20, 60 60)';
11 INSERT INTO t_wl6455 VALUES(3, ST_GeomFromText(@mp));
12 INSERT INTO t_wl6455 VALUES(4, LINESTRING(POINT(1,1), POINT(4, 4)));
13 INSERT INTO t_wl6455 VALUES(5, LINESTRING(POINT(2,2), POINT(5, 5)));
14 SET @mls = 'MultiLineString((1 1,2 2,3 3),(4 4,5 5))';
15 INSERT INTO t_wl6455 VALUES(6, ST_GeomFromText(@mls));
16 SET @poly = 'Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))';
17 INSERT INTO t_wl6455 VALUES(7, ST_GeomFromText(@poly));
18 SET @mpoly = 'MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))';
19 INSERT INTO t_wl6455 VALUES(8, ST_GeomFromText(@mpoly));
20 SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';
21 INSERT INTO t_wl6455 VALUES(9, ST_GeomFromText(@gc));
22 SELECT ST_AsText(g) FROM t_wl6455;
23 ST_AsText(g)
24 POINT(1 1)
25 POINT(2 2)
26 MULTIPOINT(0 0,20 20,60 60)
27 LINESTRING(1 1,4 4)
28 LINESTRING(2 2,5 5)
29 MULTILINESTRING((1 1,2 2,3 3),(4 4,5 5))
30 POLYGON((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))
31 MULTIPOLYGON(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))
32 GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
33 CREATE INDEX i_p ON t_wl6455 (g(10));
34 EXPLAIN SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(2,2);
35 id select_type table type possible_keys key key_len ref rows Extra
36 1 SIMPLE t_wl6455 ref i_p i_p 12 const # Using where
37 SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(2,2);
38 ST_AsText(g)
39 POINT(2 2)
40 SET AUTOCOMMIT = 0;
41 INSERT INTO t_wl6455 VALUES(10, POINT(10,10));
42 SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(10,10);
43 ST_AsText(g)
44 POINT(10 10)
45 ROLLBACK;
46 SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(10,10);
47 ST_AsText(g)
48 INSERT INTO t_wl6455 VALUES(10, POINT(10,10));
49 COMMIT;
50 FLUSH TABLES;
51 INSERT INTO t_wl6455 VALUES(11, POINT(11,11));
52 BEGIN;
53 INSERT INTO t_wl6455 VALUES(1, POINT(1,1));
54 # restart
55 CHECK TABLE t_wl6455;
56 Table Op Msg_type Msg_text
57 test.t_wl6455 check status OK
58 SELECT ST_AsText(g) FROM t_wl6455;
59 ST_AsText(g)
60 POINT(1 1)
61 POINT(2 2)
62 MULTIPOINT(0 0,20 20,60 60)
63 LINESTRING(1 1,4 4)
64 LINESTRING(2 2,5 5)
65 MULTILINESTRING((1 1,2 2,3 3),(4 4,5 5))
66 POLYGON((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))
67 MULTIPOLYGON(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))
68 GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
69 POINT(10 10)
70 POINT(11 11)
71 SELECT sc.name, sc.pos, sc.mtype
72 FROM information_schema.innodb_sys_columns sc
73 INNER JOIN information_schema.innodb_sys_tables st
74 ON sc.TABLE_ID=st.TABLE_ID
75 WHERE st.NAME='test/t_wl6455' AND sc.NAME='g';
76 name pos mtype
77 g 1 14
78 DROP TABLE t_wl6455;