1# 2# MDEV-12457 Cursors with parameters 3# 4CREATE TABLE t1 (a INT, b VARCHAR(10)) ENGINE=MyISAM; 5INSERT INTO t1 VALUES (1,'old'),(2,'old'),(3,'old'),(4,'old'),(5,'old'); 6CREATE PROCEDURE p1(min INT,max INT) 7BEGIN 8DECLARE done INT DEFAULT FALSE; 9DECLARE va INT; 10DECLARE cur CURSOR(pmin INT, pmax INT) FOR SELECT a FROM t1 WHERE a BETWEEN pmin AND pmax; 11DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; 12OPEN cur(min,max); 13read_loop: LOOP 14FETCH cur INTO va; 15IF done THEN 16LEAVE read_loop; 17END IF; 18INSERT INTO t1 VALUES (va,'new'); 19END LOOP; 20CLOSE cur; 21END; 22$$ 23CALL p1(2,4); 24SELECT * FROM t1 ORDER BY b DESC,a; 25a b 261 old 272 old 283 old 294 old 305 old 312 new 323 new 334 new 34DROP PROCEDURE p1; 35DROP TABLE t1; 36# 37# OPEN with a wrong number of parameters 38# 39CREATE TABLE t1 (a INT, b VARCHAR(10)); 40CREATE PROCEDURE p1(a_a INT,a_b VARCHAR(32)) 41BEGIN 42DECLARE v_a INT; 43DECLARE v_b VARCHAR(10); 44DECLARE c CURSOR (p_a INT, p_b VARCHAR(32)) FOR SELECT * FROM t1 WHERE a=p_a; 45OPEN c(a_a); 46CLOSE c; 47END; 48$$ 49ERROR 42000: Incorrect parameter count to cursor 'c' 50DROP TABLE t1; 51# 52# Cursor parameters are not visible outside of the cursor 53# 54CREATE PROCEDURE p1(a_a INT) 55BEGIN 56DECLARE v_a INT; 57DECLARE c CURSOR (p_a INT) FOR SELECT a FROM t1 WHERE a=p_a; 58OPEN c(a_a); 59SET p_a=10; 60END; 61$$ 62ERROR HY000: Unknown system variable 'p_a' 63CREATE PROCEDURE p1(a_a INT) 64BEGIN 65DECLARE v_a INT; 66DECLARE c CURSOR (p_a INT) FOR SELECT a FROM t1 WHERE a=p_a; 67SET p_a= 10; 68OPEN c(a_a); 69END; 70$$ 71ERROR HY000: Unknown system variable 'p_a' 72# 73# Cursor parameter shadowing a local variable 74# 75CREATE TABLE t1 (a INT); 76INSERT INTO t1 VALUES (1); 77CREATE PROCEDURE p1(a INT) 78BEGIN 79DECLARE done INT DEFAULT 0; 80DECLARE v_a INT DEFAULT NULL; 81DECLARE p_a INT DEFAULT NULL; 82DECLARE c CURSOR (p_a VARCHAR(32)) FOR SELECT p_a FROM t1; 83DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; 84OPEN c(a); 85read_loop: LOOP 86FETCH c INTO v_a; 87IF done THEN 88LEAVE read_loop; 89END IF; 90SELECT v_a; 91END LOOP; 92CLOSE c; 93END; 94$$ 95CALL p1(1); 96v_a 971 98CALL p1(NULL); 99v_a 100NULL 101DROP PROCEDURE p1; 102DROP TABLE t1; 103# 104# Parameters in SELECT list 105# 106CREATE PROCEDURE p1(a_a INT, a_b VARCHAR(32)) 107BEGIN 108DECLARE v_a INT; 109DECLARE v_b VARCHAR(10); 110DECLARE c CURSOR (p_a INT, p_b VARCHAR(32)) FOR SELECT p_a,p_b FROM DUAL; 111OPEN c(a_a + 0,a_b); 112FETCH c INTO v_a, v_b; 113SELECT v_a, v_b; 114CLOSE c; 115OPEN c(a_a + 1,a_b); 116FETCH c INTO v_a, v_b; 117SELECT v_a, v_b; 118CLOSE c; 119END; 120$$ 121CALL p1(1,'b1'); 122v_a v_b 1231 b1 124v_a v_b 1252 b1 126DROP PROCEDURE p1; 127# 128# Parameters in SELECT list + UNION 129# 130CREATE PROCEDURE p1(a_a INT, a_b VARCHAR(32)) 131BEGIN 132DECLARE v_a INT; 133DECLARE v_b VARCHAR(10); 134DECLARE c CURSOR(p_a INT, p_b VARCHAR(32)) FOR 135SELECT p_a,p_b FROM DUAL 136UNION ALL 137SELECT p_a+1,CONCAT(p_b,'b') FROM DUAL; 138OPEN c(a_a,a_b); 139FETCH c INTO v_a, v_b; 140SELECT v_a, v_b; 141FETCH c INTO v_a, v_b; 142SELECT v_a, v_b; 143CLOSE c; 144END; 145$$ 146CALL p1(1,'b1'); 147v_a v_b 1481 b1 149v_a v_b 1502 b1b 151DROP PROCEDURE p1; 152# 153# Parameters in SELECT list + type conversion + warnings 154# 155SET sql_mode=''; 156CREATE PROCEDURE p1(a_a VARCHAR(32)) 157BEGIN 158DECLARE v_a INT; 159DECLARE c CURSOR (p_a INT) FOR SELECT p_a FROM DUAL; 160OPEN c(a_a); 161FETCH c INTO v_a; 162SELECT v_a; 163CLOSE c; 164END; 165$$ 166CALL p1('1b'); 167v_a 1681 169Warnings: 170Warning 1265 Data truncated for column 'p_a' at row 1 171CALL p1('b1'); 172v_a 1730 174Warnings: 175Warning 1366 Incorrect integer value: 'b1' for column ``.``.`p_a` at row 1 176DROP PROCEDURE p1; 177SET sql_mode=DEFAULT; 178# 179# One parameter in SELECT list + subselect 180# 181CREATE PROCEDURE p1(a_a VARCHAR(32)) 182BEGIN 183DECLARE v_a VARCHAR(10); 184DECLARE c CURSOR (p_a VARCHAR(32)) FOR 185SELECT p_a FROM DUAL UNION SELECT REVERSE(p_a) FROM DUAL; 186OPEN c((SELECT a_a)); 187FETCH c INTO v_a; 188SELECT v_a; 189FETCH c INTO v_a; 190SELECT v_a; 191CLOSE c; 192END; 193$$ 194CALL p1('ab'); 195v_a 196ab 197v_a 198ba 199DROP PROCEDURE p1; 200# 201# Two parameters in SELECT list + subselect 202# 203CREATE PROCEDURE p1() 204BEGIN 205DECLARE v_a VARCHAR(32); 206DECLARE v_b VARCHAR(32); 207DECLARE c CURSOR (p_a VARCHAR(32), p_b VARCHAR(32)) FOR 208SELECT p_a, p_b FROM DUAL 209UNION 210SELECT p_b, p_a FROM DUAL; 211OPEN c((SELECT 'aaa'),(SELECT 'bbb')); 212FETCH c INTO v_a, v_b; 213SELECT v_a, v_b; 214FETCH c INTO v_a, v_b; 215SELECT v_a, v_b; 216CLOSE c; 217END; 218$$ 219CALL p1(); 220v_a v_b 221aaa bbb 222v_a v_b 223bbb aaa 224DROP PROCEDURE p1; 225# 226# Two parameters in SELECT list + two parameters in WHERE + subselects 227# 228CREATE PROCEDURE p1(a_a VARCHAR(32), a_b VARCHAR(32)) 229BEGIN 230DECLARE done INT DEFAULT 0; 231DECLARE v_a VARCHAR(32); 232DECLARE v_b VARCHAR(32); 233DECLARE c CURSOR (value_a VARCHAR(32), value_b VARCHAR(32), 234pattern_a VARCHAR(32), pattern_b VARCHAR(32)) FOR 235SELECT value_a, value_b FROM DUAL WHERE value_a LIKE pattern_a 236UNION 237SELECT value_b, value_a FROM DUAL WHERE value_b LIKE pattern_b; 238DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; 239OPEN c((SELECT 'aaa'),(SELECT 'bbb'),(SELECT a_a),(SELECT a_b)); 240read_loop: LOOP 241FETCH c INTO v_a, v_b; 242IF done THEN 243LEAVE read_loop; 244END IF; 245SELECT v_a, v_b; 246END LOOP; 247CLOSE c; 248END; 249$$ 250CALL p1('%','%'); 251v_a v_b 252aaa bbb 253v_a v_b 254bbb aaa 255CALL p1('aaa','xxx'); 256v_a v_b 257aaa bbb 258CALL p1('xxx','bbb'); 259v_a v_b 260bbb aaa 261CALL p1('xxx','xxx'); 262DROP PROCEDURE p1; 263# 264# Parameters in SELECT list + stored function 265# 266CREATE FUNCTION f1 (a VARCHAR(32)) RETURNS VARCHAR(32) 267BEGIN 268RETURN CONCAT(a,'y'); 269END; 270$$ 271CREATE PROCEDURE p1(a_a VARCHAR(32)) 272BEGIN 273DECLARE done INT DEFAULT 0; 274DECLARE v_a VARCHAR(10); 275DECLARE v_b VARCHAR(10); 276DECLARE c CURSOR (p_sel_a VARCHAR(32), p_cmp_a VARCHAR(32)) FOR 277SELECT p_sel_a, p_cmp_a FROM DUAL; 278DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; 279OPEN c(f1(a_a), f1(a_a)); 280read_loop: LOOP 281FETCH c INTO v_a, v_b; 282IF done THEN 283LEAVE read_loop; 284END IF; 285SELECT v_a, v_b; 286END LOOP; 287CLOSE c; 288END; 289$$ 290CALL p1('x'); 291v_a v_b 292xy xy 293CALL p1(f1(COALESCE(NULL, f1('x')))); 294v_a v_b 295xyyy xyyy 296DROP PROCEDURE p1; 297DROP FUNCTION f1; 298# 299# One parameter in WHERE clause 300# 301CREATE TABLE t1 (a INT, b VARCHAR(10)); 302CREATE TABLE t2 (a INT, b VARCHAR(10)); 303INSERT INTO t1 VALUES (1,'11'); 304INSERT INTO t1 VALUES (1,'12'); 305INSERT INTO t1 VALUES (2,'21'); 306INSERT INTO t1 VALUES (2,'22'); 307INSERT INTO t1 VALUES (3,'31'); 308INSERT INTO t1 VALUES (3,'32'); 309CREATE PROCEDURE p1(a_a INT) 310BEGIN 311DECLARE done INT DEFAULT 0; 312DECLARE v_a INT; 313DECLARE v_b VARCHAR(10); 314DECLARE c CURSOR (p_a INT) FOR SELECT a,b FROM t1 WHERE a=p_a; 315DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; 316OPEN c(a_a); 317read_loop: LOOP 318FETCH c INTO v_a, v_b; 319IF done THEN 320LEAVE read_loop; 321END IF; 322INSERT INTO t2 VALUES (v_a,v_b); 323END LOOP; 324CLOSE c; 325END; 326$$ 327CALL p1(1); 328SELECT * FROM t2; 329a b 3301 11 3311 12 332DROP TABLE t1; 333DROP TABLE t2; 334DROP PROCEDURE p1; 335# 336# Two parameters in WHERE clause 337# 338CREATE TABLE t1 (a INT, b VARCHAR(10)); 339CREATE TABLE t2 (a INT, b VARCHAR(10)); 340INSERT INTO t1 VALUES (1,'11'); 341INSERT INTO t1 VALUES (1,'12'); 342INSERT INTO t1 VALUES (2,'21'); 343INSERT INTO t1 VALUES (2,'22'); 344INSERT INTO t1 VALUES (3,'31'); 345INSERT INTO t1 VALUES (3,'32'); 346CREATE PROCEDURE p1(a_a INT, a_b VARCHAR(32)) 347BEGIN 348DECLARE done INT DEFAULT 0; 349DECLARE v_a INT; 350DECLARE v_b VARCHAR(10); 351DECLARE c CURSOR (p_a INT, p_b VARCHAR(32)) FOR SELECT a,b FROM t1 WHERE a=p_a AND b=p_b; 352DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; 353OPEN c(a_a, a_b); 354read_loop: LOOP 355FETCH c INTO v_a, v_b; 356IF done THEN 357LEAVE read_loop; 358END IF; 359INSERT INTO t2 VALUES (v_a,v_b); 360END LOOP; 361CLOSE c; 362END; 363$$ 364CALL p1(1,'11'); 365SELECT * FROM t2; 366a b 3671 11 368DROP TABLE t1; 369DROP TABLE t2; 370DROP PROCEDURE p1; 371# 372# Parameters in WHERE and HAVING clauses 373# 374CREATE TABLE t1 (name VARCHAR(10), value INT); 375INSERT INTO t1 VALUES ('but',1); 376INSERT INTO t1 VALUES ('but',1); 377INSERT INTO t1 VALUES ('but',1); 378INSERT INTO t1 VALUES ('bin',1); 379INSERT INTO t1 VALUES ('bin',1); 380INSERT INTO t1 VALUES ('bot',1); 381CREATE PROCEDURE p1 (arg_name_limit VARCHAR(32), arg_total_limit INT) 382BEGIN 383DECLARE i INT DEFAULT 0; 384DECLARE v_name VARCHAR(10); 385DECLARE v_total INT; 386-- +0 is needed to work around the bug MDEV-11081 387DECLARE c CURSOR(p_v INT) FOR 388SELECT name, SUM(value + p_v) + 0 AS total FROM t1 389WHERE name LIKE arg_name_limit 390GROUP BY name HAVING total>=arg_total_limit; 391WHILE i < 2 DO 392BEGIN 393DECLARE done INT DEFAULT 0; 394DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; 395OPEN c(i); 396read_loop: LOOP 397FETCH c INTO v_name, v_total; 398IF done THEN 399LEAVE read_loop; 400END IF; 401SELECT v_name, v_total; 402END LOOP; 403CLOSE c; 404SET i= i + 1; 405END; 406END WHILE; 407END; 408$$ 409CALL p1('%', 2); 410v_name v_total 411bin 2 412v_name v_total 413but 3 414v_name v_total 415bin 4 416v_name v_total 417bot 2 418v_name v_total 419but 6 420CALL p1('b_t', 0); 421v_name v_total 422bot 1 423v_name v_total 424but 3 425v_name v_total 426bot 2 427v_name v_total 428but 6 429DROP PROCEDURE p1; 430DROP TABLE t1; 431# 432# One parameter in LIMIT clause 433# 434CREATE TABLE t1 (a INT, b VARCHAR(10)); 435INSERT INTO t1 VALUES (1,'b1'); 436INSERT INTO t1 VALUES (2,'b2'); 437INSERT INTO t1 VALUES (3,'b3'); 438INSERT INTO t1 VALUES (4,'b4'); 439INSERT INTO t1 VALUES (5,'b5'); 440INSERT INTO t1 VALUES (6,'b6'); 441CREATE PROCEDURE p1(a_a INT) 442BEGIN 443DECLARE done INT DEFAULT 0; 444DECLARE v_a INT; 445DECLARE v_b VARCHAR(10); 446DECLARE c CURSOR (p_a INT) FOR SELECT a,b FROM t1 ORDER BY a LIMIT p_a; 447DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; 448CREATE TABLE t2 (a INT, b VARCHAR(10)); 449OPEN c(a_a); 450read_loop: LOOP 451FETCH c INTO v_a, v_b; 452IF done THEN 453LEAVE read_loop; 454END IF; 455INSERT INTO t2 VALUES (v_a,v_b); 456END LOOP; 457CLOSE c; 458SELECT * FROM t2; 459DROP TABLE t2; 460END; 461$$ 462CALL p1(1); 463a b 4641 b1 465CALL p1(3); 466a b 4671 b1 4682 b2 4693 b3 470CALL p1(6); 471a b 4721 b1 4732 b2 4743 b3 4754 b4 4765 b5 4776 b6 478DROP TABLE t1; 479DROP PROCEDURE p1; 480# 481# End of MDEV-12457 Cursors with parameters 482# 483# 484# MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT 485# 486# Explicit cursor 487CREATE TABLE t1 (a INT, b VARCHAR(10)); 488INSERT INTO t1 VALUES (1,'b1'), (2,'b2'), (3,'b3'); 489BEGIN NOT ATOMIC 490DECLARE cur CURSOR FOR SELECT * FROM t1; 491FOR rec IN cur 492DO 493SELECT rec.a AS a, rec.b AS b; 494END FOR; 495END; 496$$ 497a b 4981 b1 499a b 5002 b2 501a b 5023 b3 503DROP TABLE t1; 504# Explicit cursor with parameters 505CREATE TABLE t1 (a INT, b VARCHAR(10)); 506INSERT INTO t1 VALUES (1,'b1'), (2,'b2'), (3,'b3'); 507BEGIN NOT ATOMIC 508DECLARE cur CURSOR(pa INT) FOR SELECT * FROM t1 WHERE a>=pa; 509FOR rec IN cur(2) 510DO 511SELECT rec.a AS a, rec.b AS b; 512END FOR; 513END; 514$$ 515a b 5162 b2 517a b 5183 b3 519DROP TABLE t1; 520# Explicit cursor + label 521CREATE TABLE t1 (a INT, b VARCHAR(10)); 522INSERT INTO t1 VALUES ('1','b1'), ('2','b2'); 523BEGIN NOT ATOMIC 524DECLARE cur CURSOR FOR SELECT * FROM t1; 525forrec: 526FOR rec IN cur 527DO 528SELECT rec.a AS a, rec.b AS b; 529IF rec.a = 2 THEN 530LEAVE forrec; 531END IF; 532END FOR forrec; 533END; 534$$ 535a b 5361 b1 537a b 5382 b2 539DROP TABLE t1; 540# Explicit cursor + FETCH inside the loop body produce an error on "NOT FOUND" 541BEGIN NOT ATOMIC 542DECLARE x INT; 543DECLARE cur CURSOR FOR SELECT 1 AS x; 544FOR rec IN cur 545DO 546FETCH cur INTO x; 547END FOR; 548END; 549$$ 550ERROR 02000: No data - zero rows fetched, selected, or processed 551# Explicit cursor + FETCH inside the loop body are normally handled by "HANDLER FOR NOT FOUND" 552BEGIN NOT ATOMIC 553DECLARE done INT DEFAULT 0; 554DECLARE cur CURSOR FOR SELECT 1 AS x, 'y1' AS y UNION 555SELECT 2,'y2' UNION 556SELECT 3,'y3'; 557DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 558forrec: 559FOR rec IN cur 560DO 561SELECT CONCAT(rec.x, ' ', rec.y) AS 'Implicit FETCH'; 562FETCH cur INTO rec; 563IF done THEN 564SELECT 'NO DATA' AS `Explicit FETCH`; 565LEAVE forrec; 566ELSE 567SELECT CONCAT(rec.x, ' ', rec.y) AS 'Explicit FETCH'; 568END IF; 569END FOR; 570END; 571$$ 572Implicit FETCH 5731 y1 574Explicit FETCH 5752 y2 576Implicit FETCH 5773 y3 578Explicit FETCH 579NO DATA 580# Implicit cursor 581CREATE TABLE t1 (a INT, b VARCHAR(10)); 582INSERT INTO t1 VALUES ('1','b1'), ('2','b2'); 583BEGIN NOT ATOMIC 584FOR rec IN (SELECT * FROM t1) 585DO 586SELECT rec.a AS a, rec.b AS b; 587END FOR; 588END; 589$$ 590a b 5911 b1 592a b 5932 b2 594DROP TABLE t1; 595# Implicit cursor + label 596CREATE TABLE t1 (a INT, b VARCHAR(10)); 597INSERT INTO t1 VALUES ('1','b1'), ('2','b2'); 598BEGIN NOT ATOMIC 599forrec: 600FOR rec IN (SELECT * FROM t1) 601DO 602SELECT rec.a AS a, rec.b AS b; 603IF rec.a = 2 THEN 604LEAVE forrec; 605END IF; 606END FOR; 607END; 608$$ 609a b 6101 b1 611a b 6122 b2 613DROP TABLE t1; 614# 615# MDEV-15941 Explicit cursor FOR loop does not close the cursor 616# 617BEGIN NOT ATOMIC 618DECLARE v INT; 619DECLARE cur CURSOR FOR SELECT 1 AS a FROM DUAL; 620FOR rec IN cur 621DO 622SELECT rec.a; 623END FOR; 624FETCH cur INTO v; 625END; 626$$ 627rec.a 6281 629ERROR 24000: Cursor is not open 630BEGIN NOT ATOMIC 631DECLARE v INT; 632DECLARE cur CURSOR FOR SELECT 1 AS a FROM DUAL; 633label: 634FOR rec IN cur 635DO 636SELECT rec.a; 637END FOR; 638FETCH cur INTO v; 639END; 640$$ 641rec.a 6421 643ERROR 24000: Cursor is not open 644BEGIN NOT ATOMIC 645DECLARE cur CURSOR FOR SELECT 1 AS a FROM DUAL; 646OPEN cur; 647FOR rec IN cur DO 648SELECT rec.a; 649END FOR; 650END; 651$$ 652ERROR 24000: Cursor is already open 653BEGIN NOT ATOMIC 654DECLARE cur CURSOR FOR SELECT 1 AS a FROM DUAL; 655FOR rec IN cur 656DO 657SELECT rec.a; 658END FOR; 659FOR rec IN cur 660DO 661SELECT rec.a; 662END FOR; 663END; 664$$ 665rec.a 6661 667rec.a 6681 669BEGIN NOT ATOMIC 670DECLARE cur CURSOR FOR SELECT 1 AS a FROM DUAL; 671label1: 672FOR rec IN cur 673DO 674SELECT rec.a; 675END FOR; 676label2: 677FOR rec IN cur 678DO 679SELECT rec.a; 680END FOR; 681END; 682$$ 683rec.a 6841 685rec.a 6861 687# 688# MDEV-16584 SP with a cursor inside a loop wastes THD memory aggressively 689# 690CREATE PROCEDURE p1() 691BEGIN 692DECLARE mem_used_old BIGINT UNSIGNED DEFAULT 693(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS 694WHERE VARIABLE_NAME='MEMORY_USED'); 695DECLARE i INT DEFAULT 1; 696WHILE i <= 5000 697DO 698BEGIN 699DECLARE msg TEXT; 700DECLARE mem_used_cur BIGINT UNSIGNED DEFAULT 701(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS 702WHERE VARIABLE_NAME='MEMORY_USED'); 703DECLARE cur CURSOR FOR SELECT 1 FROM DUAL; 704IF (mem_used_cur >= mem_used_old * 2) THEN 705SHOW STATUS LIKE 'Memory_used'; 706SET msg=CONCAT('Memory leak detected: i=', i, ' mem_used_old=',mem_used_old,' mem_used_cur=', mem_used_cur); 707SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT=msg; 708END IF; 709END; 710SET i=i+1; 711END WHILE; 712END; 713$$ 714CALL p1; 715DROP PROCEDURE p1; 716# 717# MDEV-17278 CURSOR FOR LOOP - ERROR: unexpected end of stream, read 0 bytes (SERVER CRASH) 718# 719CREATE TABLE t1 (id2 int, id int, en1 enum('aaa','a','b','c')); 720INSERT INTO t1 VALUES(1,1,'aaa'),(2,2,'a'),(3,3,'b'),(4,4,'c'); 721CREATE PROCEDURE p1() 722BEGIN 723FOR rec IN (SELECT en1 FROM t1) 724DO 725SELECT rec.en1; 726END FOR; 727END; 728$$ 729CALL p1(); 730rec.en1 731aaa 732rec.en1 733a 734rec.en1 735b 736rec.en1 737c 738DROP PROCEDURE p1; 739DROP TABLE t1; 740