diff options
author | Michael Widenius <monty@mariadb.org> | 2018-03-09 14:05:35 +0200 |
---|---|---|
committer | Monty <monty@mariadb.org> | 2018-03-29 13:59:44 +0300 |
commit | a7abddeffa6a760ce948c2dfb007cdf3f1a369d5 (patch) | |
tree | 70eb743fa965a17380bbc0ac88ae79ca1075b896 /mysql-test/main/sp-cursor.test | |
parent | ab1941266c59a19703a74b5593cf3f508a5752d7 (diff) | |
download | mariadb-git-a7abddeffa6a760ce948c2dfb007cdf3f1a369d5.tar.gz |
Create 'main' test directory and move 't' and 'r' there
Diffstat (limited to 'mysql-test/main/sp-cursor.test')
-rw-r--r-- | mysql-test/main/sp-cursor.test | 609 |
1 files changed, 609 insertions, 0 deletions
diff --git a/mysql-test/main/sp-cursor.test b/mysql-test/main/sp-cursor.test new file mode 100644 index 00000000000..2e7a72cf8d0 --- /dev/null +++ b/mysql-test/main/sp-cursor.test @@ -0,0 +1,609 @@ + +--echo # +--echo # MDEV-12457 Cursors with parameters +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'old'),(2,'old'),(3,'old'),(4,'old'),(5,'old'); +DELIMITER $$; +CREATE PROCEDURE p1(min INT,max INT) +BEGIN + DECLARE done INT DEFAULT FALSE; + DECLARE va INT; + DECLARE cur CURSOR(pmin INT, pmax INT) FOR SELECT a FROM t1 WHERE a BETWEEN pmin AND pmax; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; + OPEN cur(min,max); + read_loop: LOOP + FETCH cur INTO va; + IF done THEN + LEAVE read_loop; + END IF; + INSERT INTO t1 VALUES (va,'new'); + END LOOP; + CLOSE cur; +END; +$$ +DELIMITER ;$$ +CALL p1(2,4); +SELECT * FROM t1 ORDER BY b DESC,a; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # OPEN with a wrong number of parameters +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +--error ER_WRONG_PARAMCOUNT_TO_CURSOR +CREATE PROCEDURE p1(a_a INT,a_b VARCHAR(32)) +BEGIN + DECLARE v_a INT; + DECLARE v_b VARCHAR(10); + DECLARE c CURSOR (p_a INT, p_b VARCHAR(32)) FOR SELECT * FROM t1 WHERE a=p_a; + OPEN c(a_a); + CLOSE c; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + + +--echo # +--echo # Cursor parameters are not visible outside of the cursor +--echo # + +DELIMITER $$; +--error ER_UNKNOWN_SYSTEM_VARIABLE +CREATE PROCEDURE p1(a_a INT) +BEGIN + DECLARE v_a INT; + DECLARE c CURSOR (p_a INT) FOR SELECT a FROM t1 WHERE a=p_a; + OPEN c(a_a); + SET p_a=10; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_UNKNOWN_SYSTEM_VARIABLE +CREATE PROCEDURE p1(a_a INT) +BEGIN + DECLARE v_a INT; + DECLARE c CURSOR (p_a INT) FOR SELECT a FROM t1 WHERE a=p_a; + SET p_a= 10; + OPEN c(a_a); +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # Cursor parameter shadowing a local variable +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +DELIMITER $$; +CREATE PROCEDURE p1(a INT) +BEGIN + DECLARE done INT DEFAULT 0; + DECLARE v_a INT DEFAULT NULL; + DECLARE p_a INT DEFAULT NULL; + DECLARE c CURSOR (p_a VARCHAR(32)) FOR SELECT p_a FROM t1; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; + OPEN c(a); + read_loop: LOOP + FETCH c INTO v_a; + IF done THEN + LEAVE read_loop; + END IF; + SELECT v_a; + END LOOP; + CLOSE c; +END; +$$ +DELIMITER ;$$ +CALL p1(1); +CALL p1(NULL); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Parameters in SELECT list +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(a_a INT, a_b VARCHAR(32)) +BEGIN + DECLARE v_a INT; + DECLARE v_b VARCHAR(10); + DECLARE c CURSOR (p_a INT, p_b VARCHAR(32)) FOR SELECT p_a,p_b FROM DUAL; + OPEN c(a_a + 0,a_b); + FETCH c INTO v_a, v_b; + SELECT v_a, v_b; + CLOSE c; + OPEN c(a_a + 1,a_b); + FETCH c INTO v_a, v_b; + SELECT v_a, v_b; + CLOSE c; +END; +$$ +DELIMITER ;$$ +CALL p1(1,'b1'); +DROP PROCEDURE p1; + + +--echo # +--echo # Parameters in SELECT list + UNION +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(a_a INT, a_b VARCHAR(32)) +BEGIN + DECLARE v_a INT; + DECLARE v_b VARCHAR(10); + DECLARE c CURSOR(p_a INT, p_b VARCHAR(32)) FOR + SELECT p_a,p_b FROM DUAL + UNION ALL + SELECT p_a+1,CONCAT(p_b,'b') FROM DUAL; + OPEN c(a_a,a_b); + FETCH c INTO v_a, v_b; + SELECT v_a, v_b; + FETCH c INTO v_a, v_b; + SELECT v_a, v_b; + CLOSE c; +END; +$$ +DELIMITER ;$$ +CALL p1(1,'b1'); +DROP PROCEDURE p1; + + +--echo # +--echo # Parameters in SELECT list + type conversion + warnings +--echo # + +SET sql_mode=''; +DELIMITER $$; +CREATE PROCEDURE p1(a_a VARCHAR(32)) +BEGIN + DECLARE v_a INT; + DECLARE c CURSOR (p_a INT) FOR SELECT p_a FROM DUAL; + OPEN c(a_a); + FETCH c INTO v_a; + SELECT v_a; + CLOSE c; +END; +$$ +DELIMITER ;$$ +CALL p1('1b'); +CALL p1('b1'); +DROP PROCEDURE p1; +SET sql_mode=DEFAULT; + + +--echo # +--echo # One parameter in SELECT list + subselect +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(a_a VARCHAR(32)) +BEGIN + DECLARE v_a VARCHAR(10); + DECLARE c CURSOR (p_a VARCHAR(32)) FOR + SELECT p_a FROM DUAL UNION SELECT REVERSE(p_a) FROM DUAL; + OPEN c((SELECT a_a)); + FETCH c INTO v_a; + SELECT v_a; + FETCH c INTO v_a; + SELECT v_a; + CLOSE c; +END; +$$ +DELIMITER ;$$ +CALL p1('ab'); +DROP PROCEDURE p1; + + +--echo # +--echo # Two parameters in SELECT list + subselect +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE v_a VARCHAR(32); + DECLARE v_b VARCHAR(32); + DECLARE c CURSOR (p_a VARCHAR(32), p_b VARCHAR(32)) FOR + SELECT p_a, p_b FROM DUAL + UNION + SELECT p_b, p_a FROM DUAL; + OPEN c((SELECT 'aaa'),(SELECT 'bbb')); + FETCH c INTO v_a, v_b; + SELECT v_a, v_b; + FETCH c INTO v_a, v_b; + SELECT v_a, v_b; + CLOSE c; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; + + +--echo # +--echo # Two parameters in SELECT list + two parameters in WHERE + subselects +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(a_a VARCHAR(32), a_b VARCHAR(32)) +BEGIN + DECLARE done INT DEFAULT 0; + DECLARE v_a VARCHAR(32); + DECLARE v_b VARCHAR(32); + DECLARE c CURSOR (value_a VARCHAR(32), value_b VARCHAR(32), + pattern_a VARCHAR(32), pattern_b VARCHAR(32)) FOR + SELECT value_a, value_b FROM DUAL WHERE value_a LIKE pattern_a + UNION + SELECT value_b, value_a FROM DUAL WHERE value_b LIKE pattern_b; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; + OPEN c((SELECT 'aaa'),(SELECT 'bbb'),(SELECT a_a),(SELECT a_b)); + read_loop: LOOP + FETCH c INTO v_a, v_b; + IF done THEN + LEAVE read_loop; + END IF; + SELECT v_a, v_b; + END LOOP; + CLOSE c; +END; +$$ +DELIMITER ;$$ +CALL p1('%','%'); +CALL p1('aaa','xxx'); +CALL p1('xxx','bbb'); +CALL p1('xxx','xxx'); +DROP PROCEDURE p1; + + +--echo # +--echo # Parameters in SELECT list + stored function +--echo # + +DELIMITER $$; +CREATE FUNCTION f1 (a VARCHAR(32)) RETURNS VARCHAR(32) +BEGIN + RETURN CONCAT(a,'y'); +END; +$$ +CREATE PROCEDURE p1(a_a VARCHAR(32)) +BEGIN + DECLARE done INT DEFAULT 0; + DECLARE v_a VARCHAR(10); + DECLARE v_b VARCHAR(10); + DECLARE c CURSOR (p_sel_a VARCHAR(32), p_cmp_a VARCHAR(32)) FOR + SELECT p_sel_a, p_cmp_a FROM DUAL; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; + OPEN c(f1(a_a), f1(a_a)); + read_loop: LOOP + FETCH c INTO v_a, v_b; + IF done THEN + LEAVE read_loop; + END IF; + SELECT v_a, v_b; + END LOOP; + CLOSE c; +END; +$$ +DELIMITER ;$$ +CALL p1('x'); +# A complex expression +CALL p1(f1(COALESCE(NULL, f1('x')))); +DROP PROCEDURE p1; +DROP FUNCTION f1; + + +--echo # +--echo # One parameter in WHERE clause +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE TABLE t2 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES (1,'11'); +INSERT INTO t1 VALUES (1,'12'); +INSERT INTO t1 VALUES (2,'21'); +INSERT INTO t1 VALUES (2,'22'); +INSERT INTO t1 VALUES (3,'31'); +INSERT INTO t1 VALUES (3,'32'); +DELIMITER $$; +CREATE PROCEDURE p1(a_a INT) +BEGIN + DECLARE done INT DEFAULT 0; + DECLARE v_a INT; + DECLARE v_b VARCHAR(10); + DECLARE c CURSOR (p_a INT) FOR SELECT a,b FROM t1 WHERE a=p_a; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; + OPEN c(a_a); + read_loop: LOOP + FETCH c INTO v_a, v_b; + IF done THEN + LEAVE read_loop; + END IF; + INSERT INTO t2 VALUES (v_a,v_b); + END LOOP; + CLOSE c; +END; +$$ +DELIMITER ;$$ +CALL p1(1); +SELECT * FROM t2; +DROP TABLE t1; +DROP TABLE t2; +DROP PROCEDURE p1; + + +--echo # +--echo # Two parameters in WHERE clause +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE TABLE t2 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES (1,'11'); +INSERT INTO t1 VALUES (1,'12'); +INSERT INTO t1 VALUES (2,'21'); +INSERT INTO t1 VALUES (2,'22'); +INSERT INTO t1 VALUES (3,'31'); +INSERT INTO t1 VALUES (3,'32'); +DELIMITER $$; +CREATE PROCEDURE p1(a_a INT, a_b VARCHAR(32)) +BEGIN + DECLARE done INT DEFAULT 0; + DECLARE v_a INT; + DECLARE v_b VARCHAR(10); + DECLARE c CURSOR (p_a INT, p_b VARCHAR(32)) FOR SELECT a,b FROM t1 WHERE a=p_a AND b=p_b; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; + OPEN c(a_a, a_b); + read_loop: LOOP + FETCH c INTO v_a, v_b; + IF done THEN + LEAVE read_loop; + END IF; + INSERT INTO t2 VALUES (v_a,v_b); + END LOOP; + CLOSE c; +END; +$$ +DELIMITER ;$$ +CALL p1(1,'11'); +SELECT * FROM t2; +DROP TABLE t1; +DROP TABLE t2; +DROP PROCEDURE p1; + +--echo # +--echo # Parameters in WHERE and HAVING clauses +--echo # +CREATE TABLE t1 (name VARCHAR(10), value INT); +INSERT INTO t1 VALUES ('but',1); +INSERT INTO t1 VALUES ('but',1); +INSERT INTO t1 VALUES ('but',1); +INSERT INTO t1 VALUES ('bin',1); +INSERT INTO t1 VALUES ('bin',1); +INSERT INTO t1 VALUES ('bot',1); +DELIMITER $$; +CREATE PROCEDURE p1 (arg_name_limit VARCHAR(32), arg_total_limit INT) +BEGIN + DECLARE i INT DEFAULT 0; + DECLARE v_name VARCHAR(10); + DECLARE v_total INT; +-- +0 is needed to work around the bug MDEV-11081 + DECLARE c CURSOR(p_v INT) FOR + SELECT name, SUM(value + p_v) + 0 AS total FROM t1 + WHERE name LIKE arg_name_limit + GROUP BY name HAVING total>=arg_total_limit; + WHILE i < 2 DO + BEGIN + DECLARE done INT DEFAULT 0; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; + OPEN c(i); + read_loop: LOOP + FETCH c INTO v_name, v_total; + IF done THEN + LEAVE read_loop; + END IF; + SELECT v_name, v_total; + END LOOP; + CLOSE c; + SET i= i + 1; + END; + END WHILE; +END; +$$ +DELIMITER ;$$ +CALL p1('%', 2); +CALL p1('b_t', 0); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # One parameter in LIMIT clause +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES (1,'b1'); +INSERT INTO t1 VALUES (2,'b2'); +INSERT INTO t1 VALUES (3,'b3'); +INSERT INTO t1 VALUES (4,'b4'); +INSERT INTO t1 VALUES (5,'b5'); +INSERT INTO t1 VALUES (6,'b6'); +DELIMITER $$; +CREATE PROCEDURE p1(a_a INT) +BEGIN + DECLARE done INT DEFAULT 0; + DECLARE v_a INT; + DECLARE v_b VARCHAR(10); + DECLARE c CURSOR (p_a INT) FOR SELECT a,b FROM t1 ORDER BY a LIMIT p_a; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; + CREATE TABLE t2 (a INT, b VARCHAR(10)); + OPEN c(a_a); + read_loop: LOOP + FETCH c INTO v_a, v_b; + IF done THEN + LEAVE read_loop; + END IF; + INSERT INTO t2 VALUES (v_a,v_b); + END LOOP; + CLOSE c; + SELECT * FROM t2; + DROP TABLE t2; +END; +$$ +DELIMITER ;$$ +CALL p1(1); +CALL p1(3); +CALL p1(6); +DROP TABLE t1; +DROP PROCEDURE p1; + + + +--echo # +--echo # End of MDEV-12457 Cursors with parameters +--echo # + + +--echo # +--echo # MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT +--echo # + +--echo # Explicit cursor + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES (1,'b1'), (2,'b2'), (3,'b3'); +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE cur CURSOR FOR SELECT * FROM t1; + FOR rec IN cur + DO + SELECT rec.a AS a, rec.b AS b; + END FOR; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + +--echo # Explicit cursor with parameters + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES (1,'b1'), (2,'b2'), (3,'b3'); +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE cur CURSOR(pa INT) FOR SELECT * FROM t1 WHERE a>=pa; + FOR rec IN cur(2) + DO + SELECT rec.a AS a, rec.b AS b; + END FOR; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + +--echo # Explicit cursor + label + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES ('1','b1'), ('2','b2'); +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE cur CURSOR FOR SELECT * FROM t1; + forrec: + FOR rec IN cur + DO + SELECT rec.a AS a, rec.b AS b; + IF rec.a = 2 THEN + LEAVE forrec; + END IF; + END FOR forrec; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + +--echo # Explicit cursor + FETCH inside the loop body produce an error on "NOT FOUND" + +DELIMITER $$; +--error ER_SP_FETCH_NO_DATA +BEGIN NOT ATOMIC + DECLARE x INT; + DECLARE cur CURSOR FOR SELECT 1 AS x; + FOR rec IN cur + DO + FETCH cur INTO x; + END FOR; +END; +$$ +DELIMITER ;$$ + + +--echo # Explicit cursor + FETCH inside the loop body are normally handled by "HANDLER FOR NOT FOUND" + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE done INT DEFAULT 0; + DECLARE cur CURSOR FOR SELECT 1 AS x, 'y1' AS y UNION + SELECT 2,'y2' UNION + SELECT 3,'y3'; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; + forrec: + FOR rec IN cur + DO + SELECT CONCAT(rec.x, ' ', rec.y) AS 'Implicit FETCH'; + FETCH cur INTO rec; + IF done THEN + SELECT 'NO DATA' AS `Explicit FETCH`; + LEAVE forrec; + ELSE + SELECT CONCAT(rec.x, ' ', rec.y) AS 'Explicit FETCH'; + END IF; + END FOR; +END; +$$ +DELIMITER ;$$ + + +--echo # Implicit cursor + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES ('1','b1'), ('2','b2'); +DELIMITER $$; +BEGIN NOT ATOMIC + FOR rec IN (SELECT * FROM t1) + DO + SELECT rec.a AS a, rec.b AS b; + END FOR; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + +--echo # Implicit cursor + label + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES ('1','b1'), ('2','b2'); +DELIMITER $$; +BEGIN NOT ATOMIC + forrec: + FOR rec IN (SELECT * FROM t1) + DO + SELECT rec.a AS a, rec.b AS b; + IF rec.a = 2 THEN + LEAVE forrec; + END IF; + END FOR; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; |