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.
10create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb;
11
12# Insert enough values to let R-tree split.
13delimiter |;
14create procedure insert_t1(IN total int)
15begin
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;
21end|
22delimiter ;|
23
24# Test level 1 rtree.
25CALL insert_t1(1000);
26select count(*) from t1;
27
28set @g1 = ST_GeomFromText('Polygon((0 0,0 1000,1000 1000,1000 0,0 0))');
29
30select count(*) from t1 where MBRWithin(t1.c2, @g1);
31
32set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))');
33select count(*) from t1 where MBRWithin(t1.c2, @g1);
34
35set @g1 = ST_GeomFromText('Polygon((10 10,10 800,800 800,800 10,10 10))');
36select count(*) from t1 where MBRWithin(t1.c2, @g1);
37
38set @g1 = ST_GeomFromText('Polygon((100 100,100 800,800 800,800 100,100 100))');
39select 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
47set @g1 =  ST_GeomFromText('Point(1 1)');
48select count(*) from t1 where MBRequals(t1.c2, @g1);
49
50# MBRDisjoint search
51set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))');
52select count(*) from t1 where MBRdisjoint(t1.c2, @g1);
53
54# Clean up.
55DROP PROCEDURE insert_t1;
56
57truncate t1;
58
59let $1=150;
60let $2=150;
61while ($1)
62{
63  eval INSERT INTO t1  VALUES ($1, ST_GeomFromText('LineString($1 $1, $2 $2)'));
64  dec $1;
65  inc $2;
66}
67
68select count(*) from t1;
69
70set @g1 = ST_GeomFromText('Polygon((0 0,0 1000,1000 1000,1000 0,0 0))');
71select count(*) from t1 where MBRwithin(t1.c2, @g1);
72truncate t1;
73
74INSERT INTO t1  VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)'));
75INSERT INTO t1  VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)'));
76INSERT INTO t1  VALUES (1, ST_GeomFromText('LineString(4 4, 170 170)'));
77INSERT INTO t1  VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)'));
78INSERT INTO t1  VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)'));
79INSERT INTO t1  VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)'));
80INSERT INTO t1  VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)'));
81
82insert into t1 select * from t1;
83insert into t1 select * from t1;
84insert into t1 select * from t1;
85insert into t1 select * from t1;
86insert into t1 select * from t1;
87insert into t1 select * from t1;
88insert into t1 select * from t1;
89insert into t1 select * from t1;
90insert into t1 select * from t1;
91insert into t1 select * from t1;
92insert into t1 select * from t1;
93
94# Testing "MBRtouches"
95# This is apparently now treated as "intersects"
96set @g1 = ST_GeomFromText('Polygon((0 0,0 2, 2 2, 2 0, 0 0))');
97select count(*) from t1 where MBRtouches(t1.c2, @g1);
98
99set @g1 = ST_GeomFromText('Polygon((0 0,0 200,200 200,200 0,0 0))');
100select count(*) from t1 where MBRWithin(t1.c2, @g1);
101
102# Test MBRequals
103set @g1 = ST_GeomFromText('LineString(2 2, 150 150)');
104select count(*) from t1 where MBRequals(t1.c2, @g1);
105
106# Test store procedure with open cursor
107set @g1 = ST_GeomFromText('Polygon((0 0,0 200,200 200,200 0,0 0))');
108create table t3 (a int) engine = innodb;
109
110delimiter |;
111
112CREATE PROCEDURE curdemo()
113BEGIN
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);
127END LOOP;
128
129CLOSE cur1;
130END|
131
132delimiter ;|
133
134call curdemo();
135
136select count(*) from t3;
137
138drop procedure curdemo;
139drop table t3;
140drop table t1;
141
142