Debian Codegraph @

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

raw
1 create table t1 (i int, i2 char(10), g geometry not null, primary key (i, i2), spatial index (g))engine=innodb;
2 insert into t1 values (1, "111", POINT(1,1));
3 insert into t1 values (2, "222", POINT(1.5,1.5));
4 insert into t1 values (3, "333", POINT(3,3));
5 insert into t1 values (4, "444", POINT(3.1,3.1));
6 insert into t1 values (5, "555", POINT(5,5));
7 analyze table t1;
8 Table Op Msg_type Msg_text
9 test.t1 analyze status Engine-independent statistics collected
10 test.t1 analyze status OK
11 set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
12 explain select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1);
13 id select_type table type possible_keys key key_len ref rows Extra
14 1 SIMPLE t1 range g g 34 NULL 5 Using where
15 select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1);
16 ST_astext(t1.g)
17 POINT(1.5 1.5)
18 POINT(1 1)
19 set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
20 delete from t1 where MBRWithin(t1.g, @g1);
21 check table t1;
22 Table Op Msg_type Msg_text
23 test.t1 check status OK
24 select ST_astext(t1.g) from t1;
25 ST_astext(t1.g)
26 POINT(3 3)
27 POINT(3.1 3.1)
28 POINT(5 5)
29 set @g1 = ST_GeomFromText('Polygon((5 5,5 5,5 5,5 5,5 5))');
30 update t1 set g = POINT(2,2) where MBRWithin(t1.g, @g1);
31 check table t1;
32 Table Op Msg_type Msg_text
33 test.t1 check status OK
34 select ST_astext(t1.g) from t1;
35 ST_astext(t1.g)
36 POINT(3 3)
37 POINT(3.1 3.1)
38 POINT(2 2)
39 show indexes from t1;
40 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
41 t1 0 PRIMARY 1 i A # NULL NULL BTREE NO
42 t1 0 PRIMARY 2 i2 A # NULL NULL BTREE NO
43 t1 1 g 1 g A # 32 NULL SPATIAL NO
44 drop table t1;
45 create table t1 (name VARCHAR(100), square GEOMETRY not null, spatial index (square))engine=innodb;
46 INSERT INTO t1 VALUES("small", ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
47 INSERT INTO t1 VALUES("big", ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
48 INSERT INTO t1 VALUES("up", ST_GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
49 INSERT INTO t1 VALUES("up2", ST_GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
50 INSERT INTO t1 VALUES("up3", ST_GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
51 INSERT INTO t1 VALUES("down", ST_GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
52 INSERT INTO t1 VALUES("down2", ST_GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
53 INSERT INTO t1 VALUES("down3", ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
54 INSERT INTO t1 VALUES("right", ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
55 INSERT INTO t1 VALUES("right2", ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
56 INSERT INTO t1 VALUES("right3", ST_GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
57 INSERT INTO t1 VALUES("left", ST_GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
58 INSERT INTO t1 VALUES("left2", ST_GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
59 INSERT INTO t1 VALUES("left3", ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))'));
60 SET @p = ST_GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))');
61 SELECT name, ST_AsText(square) from t1 where MBRContains(@p, square);
62 name ST_AsText(square)
63 small POLYGON((0 0,0 1,1 1,1 0,0 0))
64 SELECT name, ST_AsText(square) from t1 where MBRDisjoint(@p, square);
65 name ST_AsText(square)
66 down3 POLYGON((0 -3,0 -1,2 -1,2 -3,0 -3))
67 left3 POLYGON((-3 0,-3 2,-1 2,-1 0,-3 0))
68 right3 POLYGON((3 0,3 2,5 2,5 0,3 0))
69 up3 POLYGON((0 3,0 5,2 5,2 3,0 3))
70 SELECT name, ST_AsText(square) from t1 where MBREquals(@p, square);
71 name ST_AsText(square)
72 SELECT name, ST_AsText(square) from t1 where MBRIntersects(@p, square);
73 name ST_AsText(square)
74 right2 POLYGON((2 0,2 2,4 2,4 0,2 0))
75 right POLYGON((1 0,1 2,3 2,3 0,1 0))
76 up2 POLYGON((0 2,0 4,2 4,2 2,0 2))
77 up POLYGON((0 1,0 3,2 3,2 1,0 1))
78 big POLYGON((0 0,0 3,3 3,3 0,0 0))
79 small POLYGON((0 0,0 1,1 1,1 0,0 0))
80 down POLYGON((0 -1,0 1,2 1,2 -1,0 -1))
81 down2 POLYGON((0 -2,0 0,2 0,2 -2,0 -2))
82 left POLYGON((-1 0,-1 2,1 2,1 0,-1 0))
83 left2 POLYGON((-2 0,-2 2,0 2,0 0,-2 0))
84 SELECT name, ST_AsText(square) from t1 where MBROverlaps(@p, square);
85 name ST_AsText(square)
86 right POLYGON((1 0,1 2,3 2,3 0,1 0))
87 up POLYGON((0 1,0 3,2 3,2 1,0 1))
88 down POLYGON((0 -1,0 1,2 1,2 -1,0 -1))
89 left POLYGON((-1 0,-1 2,1 2,1 0,-1 0))
90 SELECT name, ST_AsText(square) from t1 where MBRTouches(@p, square);
91 name ST_AsText(square)
92 right2 POLYGON((2 0,2 2,4 2,4 0,2 0))
93 up2 POLYGON((0 2,0 4,2 4,2 2,0 2))
94 down2 POLYGON((0 -2,0 0,2 0,2 -2,0 -2))
95 left2 POLYGON((-2 0,-2 2,0 2,0 0,-2 0))
96 SELECT name, ST_AsText(square) from t1 where MBRWithin(@p, square);
97 name ST_AsText(square)
98 big POLYGON((0 0,0 3,3 3,3 0,0 0))
99 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @vert1) GROUP BY a1.name;
100 MBRoverlaps
101 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @horiz1) GROUP BY a1.name;
102 MBRoverlaps
103 SELECT MBROverlaps(@horiz1, @vert1) FROM DUAL;
104 MBROverlaps(@horiz1, @vert1)
105 NULL
106 SELECT MBROverlaps(@horiz1, @horiz2) FROM DUAL;
107 MBROverlaps(@horiz1, @horiz2)
108 NULL
109 SELECT MBROverlaps(@horiz1, @horiz3) FROM DUAL;
110 MBROverlaps(@horiz1, @horiz3)
111 NULL
112 SELECT MBROverlaps(@horiz1, @point1) FROM DUAL;
113 MBROverlaps(@horiz1, @point1)
114 NULL
115 SELECT MBROverlaps(@horiz1, @point2) FROM DUAL;
116 MBROverlaps(@horiz1, @point2)
117 NULL
118 DROP TABLE t1;
119 create table t1 (i int not null, g geometry not null)engine=innodb;
120 insert into t1 values (1, POINT(1,1));
121 insert into t1 values (2, POINT(1.5,1.5));
122 insert into t1 values (3, POINT(3,3));
123 insert into t1 values (4, POINT(3.1,3.1));
124 insert into t1 values (5, POINT(5,5));
125 alter table t1 add primary key(i), algorithm=inplace;
126 alter table t1 drop primary key;
127 create spatial index idx on t1(g) algorithm=inplace;
128 create spatial index idx2 on t1(g);
129 Warnings:
130 Note 1831 Duplicate index `idx2`. This is deprecated and will be disallowed in a future release
131 alter table t1 add primary key(i), algorithm=inplace;
132 show create table t1;
133 Table Create Table
134 t1 CREATE TABLE `t1` (
135 `i` int(11) NOT NULL,
136 `g` geometry NOT NULL,
137 PRIMARY KEY (`i`),
138 SPATIAL KEY `idx` (`g`),
139 SPATIAL KEY `idx2` (`g`)
140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
141 drop index idx on t1;
142 drop table t1;