Debian Codegraph @

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

raw
1 # WL#6968 InnoDB R-tree cursor support
2
3 --source include/not_valgrind.inc
4
5
6 # Create table with R-tree index.
7 create table t1 (c1 int, c2 geometry not null SRID 0, spatial index (c2))engine=innodb;
8
9 # Insert enough values to let R-tree split.
10 delimiter |;
11 create procedure insert_t1(IN total int)
12 begin
13 declare i int default 1;
14 while (i <= total) DO
15 insert into t1 values (i, Point(i, i));
16 set i = i + 1;
17 end while;
18 end|
19 delimiter ;|
20
21 # Test level 1 rtree.
22 CALL insert_t1(1000);
23 select count(*) from t1;
24
25 set @g1 = ST_GeomFromText('Polygon((0 0,0 1000,1000 1000,1000 0,0 0))');
26
27 select count(*) from t1 where MBRWithin(t1.c2, @g1);
28
29 set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))');
30 select count(*) from t1 where MBRWithin(t1.c2, @g1);
31
32 set @g1 = ST_GeomFromText('Polygon((10 10,10 800,800 800,800 10,10 10))');
33 select count(*) from t1 where MBRWithin(t1.c2, @g1);
34
35 set @g1 = ST_GeomFromText('Polygon((100 100,100 800,800 800,800 100,100 100))');
36 select count(*) from t1 where MBRWithin(t1.c2, @g1);
37
38 #SET DEBUG='+d, rtr_pessimistic_position';
39 #select count(*) from t1 where MBRWithin(t1.c2, @g1);
40 #SET DEBUG='-d, rtr_pessimistic_position';
41
42 # Equality search
43 set @g1 = ST_GeomFromText('Point(1 1)');
44 select count(*) from t1 where MBRequals(t1.c2, @g1);
45
46 # MBRDisjoint search
47 set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))');
48 select count(*) from t1 where MBRdisjoint(t1.c2, @g1);
49
50 # Clean up.
51 DROP PROCEDURE insert_t1;
52
53 truncate t1;
54
55 let $1=150;
56 let $2=150;
57 while ($1)
58 {
59 eval INSERT INTO t1 VALUES ($1, ST_GeomFromText('LineString($1 $1, $2 $2)'));
60 dec $1;
61 inc $2;
62 }
63
64 select count(*) from t1;
65
66 set @g1 = ST_GeomFromText('Polygon((0 0,0 1000,1000 1000,1000 0,0 0))');
67 select count(*) from t1 where MBRwithin(t1.c2, @g1);
68 truncate t1;
69
70 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)'));
71 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)'));
72 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(4 4, 170 170)'));
73 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)'));
74 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)'));
75 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)'));
76 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)'));
77
78 insert into t1 select * from t1;
79 insert into t1 select * from t1;
80 insert into t1 select * from t1;
81 insert into t1 select * from t1;
82 insert into t1 select * from t1;
83 insert into t1 select * from t1;
84 insert into t1 select * from t1;
85 insert into t1 select * from t1;
86 insert into t1 select * from t1;
87 insert into t1 select * from t1;
88 insert into t1 select * from t1;
89
90 # Testing "MBRtouches"
91 # This is apparently now treated as "intersects"
92 set @g1 = ST_GeomFromText('Polygon((0 0,0 2, 2 2, 2 0, 0 0))');
93 select count(*) from t1 where MBRtouches(t1.c2, @g1);
94
95 set @g1 = ST_GeomFromText('Polygon((0 0,0 200,200 200,200 0,0 0))');
96 select count(*) from t1 where MBRWithin(t1.c2, @g1);
97
98 # Test MBRequals
99 set @g1 = ST_GeomFromText('LineString(2 2, 150 150)');
100 select count(*) from t1 where MBRequals(t1.c2, @g1);
101
102 # Test store procedure with open cursor
103 set @g1 = ST_GeomFromText('Polygon((0 0,0 200,200 200,200 0,0 0))');
104 create table t3 (a int) engine = innodb;
105
106 delimiter |;
107
108 CREATE PROCEDURE curdemo()
109 BEGIN
110 DECLARE done INT DEFAULT FALSE;
111 DECLARE a INT;
112
113 DECLARE cur1 CURSOR FOR SELECT c1 from t1 where MBRWithin(t1.c2, @g1);
114 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
115
116 OPEN cur1;
117 read_loop: LOOP
118 FETCH cur1 INTO a;
119 IF done THEN
120 LEAVE read_loop;
121 END IF;
122 INSERT INTO test.t3 VALUES (a);
123 END LOOP;
124
125 CLOSE cur1;
126 END|
127
128 delimiter ;|
129
130 call curdemo();
131
132 select count(*) from t3;
133
134 drop procedure curdemo;
135 drop table t3;
136 drop table t1;
137