Debian Codegraph @

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

raw
1 SET SESSION debug="+d,row_print_geometry_data";
2 CREATE TABLE t_wl6455 ( i INT, g GEOMETRY NOT NULL) ENGINE=InnoDB;
3 SHOW CREATE TABLE t_wl6455;
4 Table Create Table
5 t_wl6455 CREATE TABLE `t_wl6455` (
6 `i` int DEFAULT NULL,
7 `g` geometry NOT NULL
8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
9 INSERT INTO t_wl6455 VALUES(1, POINT(1,1));
10 INSERT INTO t_wl6455 VALUES(2, POINT(2,2));
11 SET @mp = 'MULTIPOINT(0 0, 20 20, 60 60)';
12 INSERT INTO t_wl6455 VALUES(3, ST_GeomFromText(@mp));
13 INSERT INTO t_wl6455 VALUES(4, LINESTRING(POINT(1,1), POINT(4, 4)));
14 INSERT INTO t_wl6455 VALUES(5, LINESTRING(POINT(2,2), POINT(5, 5)));
15 SET @mls = 'MultiLineString((1 1,2 2,3 3),(4 4,5 5))';
16 INSERT INTO t_wl6455 VALUES(6, ST_GeomFromText(@mls));
17 SET @poly = 'Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))';
18 INSERT INTO t_wl6455 VALUES(7, ST_GeomFromText(@poly));
19 SET @mpoly = 'MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))';
20 INSERT INTO t_wl6455 VALUES(8, ST_GeomFromText(@mpoly));
21 SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';
22 INSERT INTO t_wl6455 VALUES(9, ST_GeomFromText(@gc));
23 SELECT ST_AsText(g) FROM t_wl6455;
24 ST_AsText(g)
25 POINT(1 1)
26 POINT(2 2)
27 MULTIPOINT((0 0),(20 20),(60 60))
28 LINESTRING(1 1,4 4)
29 LINESTRING(2 2,5 5)
30 MULTILINESTRING((1 1,2 2,3 3),(4 4,5 5))
31 POLYGON((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))
32 MULTIPOLYGON(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))
33 GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
34 CREATE INDEX i_p ON t_wl6455 (g(10));
35 ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
36 EXPLAIN SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(2,2);
37 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
38 1 SIMPLE t_wl6455 NULL ALL NULL NULL NULL NULL # 11.11 Using where
39 Warnings:
40 Note 1003 /* select#1 */ select st_astext(`test`.`t_wl6455`.`g`) AS `ST_AsText(g)` from `test`.`t_wl6455` where (`test`.`t_wl6455`.`g` = <cache>(point(2,2)))
41 SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(2,2);
42 ST_AsText(g)
43 POINT(2 2)
44 SET AUTOCOMMIT = 0;
45 INSERT INTO t_wl6455 VALUES(10, POINT(10,10));
46 SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(10,10);
47 ST_AsText(g)
48 POINT(10 10)
49 ROLLBACK;
50 SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(10,10);
51 ST_AsText(g)
52 INSERT INTO t_wl6455 VALUES(10, POINT(10,10));
53 COMMIT;
54 INSERT INTO t_wl6455 VALUES(11, POINT(11,11));
55 BEGIN;
56 INSERT INTO t_wl6455 VALUES(1, POINT(1,1));
57 # Kill and restart
58 CHECK TABLE t_wl6455;
59 Table Op Msg_type Msg_text
60 test.t_wl6455 check status OK
61 SELECT ST_AsText(g) FROM t_wl6455;
62 ST_AsText(g)
63 POINT(1 1)
64 POINT(2 2)
65 MULTIPOINT((0 0),(20 20),(60 60))
66 LINESTRING(1 1,4 4)
67 LINESTRING(2 2,5 5)
68 MULTILINESTRING((1 1,2 2,3 3),(4 4,5 5))
69 POLYGON((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))
70 MULTIPOLYGON(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))
71 GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
72 POINT(10 10)
73 POINT(11 11)
74 SELECT sc.name, sc.pos, sc.mtype
75 FROM information_schema.innodb_columns sc
76 INNER JOIN information_schema.innodb_tables st
77 ON sc.TABLE_ID=st.TABLE_ID
78 WHERE st.NAME='test/t_wl6455' AND sc.NAME='g';
79 name pos mtype
80 g 1 14
81 DROP TABLE t_wl6455;