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