Debian Codegraph @

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

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