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