Debian Codegraph @

mysql-test/suite/innodb_gis/t/rtree_multi_pk.test

raw
1 # WL#6745 InnoDB R-tree support
2 # This test case will test basic R-tree support features.
3
4
5 # Create table with R-tree index.
6 create table t1 (i int, i2 char(10), g geometry not null SRID 0, primary key (i, i2), spatial index (g))engine=innodb;
7
8 # Insert values.
9 insert into t1 values (1, "111", POINT(1,1));
10 insert into t1 values (2, "222", POINT(1.5,1.5));
11 insert into t1 values (3, "333", POINT(3,3));
12 insert into t1 values (4, "444", POINT(3.1,3.1));
13 insert into t1 values (5, "555", POINT(5,5));
14
15 analyze table t1;
16
17 # Select by R-tree index.
18 set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
19 explain select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1);
20 --sorted_result
21 select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1);
22
23 # Delete values.
24 set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
25 delete from t1 where MBRWithin(t1.g, @g1);
26 check table t1;
27
28 select ST_astext(t1.g) from t1;
29
30 # Update values.
31 set @g1 = ST_GeomFromText('Polygon((5 5,5 5,5 5,5 5,5 5))');
32 update t1 set g = POINT(2,2) where MBRWithin(t1.g, @g1);
33 check table t1;
34
35 select ST_astext(t1.g) from t1;
36
37 # Show index.
38 analyze table t1;
39 --replace_column 7 #
40 show indexes from t1;
41
42 # Cleanup.
43 drop table t1;
44
45 # Test functions.
46 create table t1 (name VARCHAR(100), square GEOMETRY not null SRID 0, spatial index (square))engine=innodb;
47
48
49 INSERT INTO t1 VALUES("small", ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
50 INSERT INTO t1 VALUES("big", ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
51
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
56 INSERT INTO t1 VALUES("down", ST_GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
57 INSERT INTO t1 VALUES("down2", ST_GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
58 INSERT INTO t1 VALUES("down3", ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
59
60 INSERT INTO t1 VALUES("right", ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
61 INSERT INTO t1 VALUES("right2", ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
62 INSERT INTO t1 VALUES("right3", ST_GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
63
64 INSERT INTO t1 VALUES("left", ST_GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
65 INSERT INTO t1 VALUES("left2", ST_GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
66 INSERT INTO t1 VALUES("left3", ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))'));
67
68 SET @p = ST_GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))');
69 --sorted_result
70 SELECT name, ST_AsText(square) from t1 where MBRContains(@p, square);
71 --sorted_result
72 SELECT name, ST_AsText(square) from t1 where MBRDisjoint(@p, square);
73 --sorted_result
74 SELECT name, ST_AsText(square) from t1 where MBREquals(@p, square);
75 --sorted_result
76 SELECT name, ST_AsText(square) from t1 where MBRIntersects(@p, square);
77 --sorted_result
78 SELECT name, ST_AsText(square) from t1 where MBROverlaps(@p, square);
79 --sorted_result
80 SELECT name, ST_AsText(square) from t1 where MBRTouches(@p, square);
81 --sorted_result
82 SELECT name, ST_AsText(square) from t1 where MBRWithin(@p, square);
83
84 # MBROverlaps needs a few more tests, with point and line dimensions
85
86 --error ER_GIS_INVALID_DATA
87 SET @vert1 = ST_GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
88 --error ER_GIS_INVALID_DATA
89 SET @horiz1 = ST_GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
90 --error ER_GIS_INVALID_DATA
91 SET @horiz2 = ST_GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
92 --error ER_GIS_INVALID_DATA
93 SET @horiz3 = ST_GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
94 --error ER_GIS_INVALID_DATA
95 SET @point1 = ST_GeomFromText('POLYGON ((0 0))');
96 --error ER_GIS_INVALID_DATA
97 SET @point2 = ST_GeomFromText('POLYGON ((-2 0))');
98
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 SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @horiz1) GROUP BY a1.name;
101 SELECT MBROverlaps(@horiz1, @vert1) FROM DUAL;
102 SELECT MBROverlaps(@horiz1, @horiz2) FROM DUAL;
103 SELECT MBROverlaps(@horiz1, @horiz3) FROM DUAL;
104 SELECT MBROverlaps(@horiz1, @point1) FROM DUAL;
105 SELECT MBROverlaps(@horiz1, @point2) FROM DUAL;
106
107 DROP TABLE t1;
108
109 # Inplace create spatial index is not supported
110 create table t1 (i int not null, g geometry not null SRID 0)engine=innodb;
111
112 # Insert values.
113 insert into t1 values (1, POINT(1,1));
114 insert into t1 values (2, POINT(1.5,1.5));
115 insert into t1 values (3, POINT(3,3));
116 insert into t1 values (4, POINT(3.1,3.1));
117 insert into t1 values (5, POINT(5,5));
118
119 alter table t1 add primary key(i), algorithm=inplace;
120 alter table t1 drop primary key;
121
122 create spatial index idx on t1(g) algorithm=inplace;
123
124 create spatial index idx2 on t1(g);
125
126 alter table t1 add primary key(i), algorithm=inplace;
127
128 show create table t1;
129
130 drop index idx on t1;
131
132 drop table t1;