| 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; |