# file initial01.test
# This is the first phase stored procedure test for the updown test suite
# and will verify variable data types in SP's

# The strategy here as in all updown tests will be to create objects here,
# attempt to use, modify, and drop them in the upgrade phase, create more
# there, and attempt to use,modify, and drop both these and those in the
# downgrade phase.

# many of these tests are culled from existing test files such as sp.test.orig
# for matters of isolation all test cases within this file should use the database
# procdb

# for the most part tests are being executed by users who are granted only those
# grants required to accomplish the tests.  Only use cases are being included
# here, not exception cases
###############################################


--echo connecting as 'root';
connect (root, $MASTER_HOST, root,,);
--echo connected;
#####################################
--echo SETTING UP FOR PROCEDURE TESTS
#####################################

CREATE DATABASE procdb;
use procdb;
#CREATE TABLE procdb.proc_TABLE_init(log_msg VARCHAR(1024));

# Test tabless
# t1 and t2 are reused throughout the file, and dropped at the end.
# t3 and up are created and dropped when needed.
CREATE TABLE t1 (
        id   CHAR(16) NOT NULL DEFAULT '',
        data INT NOT NULL
);
CREATE TABLE t2 (
        s   CHAR(16),
        i   INT,
        d   DOUBLE
);


# create and connect to users
#eval GRANT CREATE ROUTINE, SELECT ON procdb.* TO 'sp_create'@'$CLIENT_HOST'
#                IDENTIFIED BY 'sp_create';
--replace_result $CLIENT_HOST CLIENT_HOST
eval GRANT ALTER ROUTINE, SELECT ON procdb.* TO  'sp_alter'@'$CLIENT_HOST'
                IDENTIFIED BY 'sp_alter';
--replace_result $CLIENT_HOST CLIENT_HOST
eval GRANT EXECUTE, INSERT, DELETE, UPDATE, SELECT ON procdb.*
             TO  'sp_execute'@'$CLIENT_HOST' IDENTIFIED BY 'sp_execute';
--replace_result $CLIENT_HOST CLIENT_HOST
eval GRANT CREATE, DELETE, DROP ON procdb.* TO 'sp_cre'@'$CLIENT_HOST'
                IDENTIFIED BY 'sp_cre';

connect (sp_cre,$MASTER_HOST,sp_cre,sp_cre,procdb);
connect (sp_execute, $MASTER_HOST, sp_execute, sp_execute,procdb);
connect (sp_alter, $MASTER_HOST, sp_alter, sp_alter,);
#connect (sp_create, $MASTER_HOST, sp_create, sp_create,procdb);
connection root;
# NOTE when bug 19857 is resolved use sp_create user to create
#      procedures instead of root;


######################################################################
########## TEST CASES BEGIN HERE #####################################
######################################################################
use procdb;
# procedure with no statements
DELIMITER |;
CREATE PROCEDURE initial_empty()
BEGIN
END|
DELIMITER ;|
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_empty();


# Single statement, no params.
--echo connecting as user 'sp_CREATE'
connection root;
--echo connected;
CREATE PROCEDURE initial_foo42() 
         DETERMINISTIC
         SQL SECURITY INVOKER
  INSERT INTO t1 VALUES ("foo", 42);
--echo connecting as user 'sp_execute'
connection sp_execute;
--echo connected;
CALL initial_foo42();
SELECT * FROM t1;

# Single statement, two IN params.
--echo connecting as user 'root'
connection root;
--echo connected;
CREATE PROCEDURE initial_bar(x CHAR(16), y INT)
              SQL SECURITY INVOKER
  INSERT INTO t1 VALUES (x, y);
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_bar("bar", 666);
SELECT * FROM t1;


# Scope test. This is legal (warnings might be possible in the future,
# but for the time being, we just accept it).
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_scope(a INT, b FLOAT)
BEGIN
  DECLARE b INT;
  DECLARE c FLOAT;
  SET a=3;
  SET b=4.4;
  SET c=3.1416;
  BEGIN
    DECLARE c INT;
    SET c=66;
    SELECT a AS 'inner scope-a';
    SELECT b AS 'inner scope-b';
    SELECT c AS 'inner scope-c';
  END;
  SELECT a AS 'outer scope a';
  SELECT b AS 'outer scope b';
  SELECT c AS 'outer scope-c';
END|
DELIMITER ;|
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_scope (1,2.2);


# Two statements
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_two(x1 CHAR(16), x2 CHAR(16), y INT)
                    SQL SECURITY INVOKER
BEGIN
  INSERT INTO t1 VALUES (x1, y);
  INSERT INTO t1 VALUES (x2, y);
end|
DELIMITER ;|
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_two("one", "two", 3);
SELECT * FROM t1;

# Simple test of local variables and SET.
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_locset(x CHAR(16), y INT)
            SQL SECURITY INVOKER
BEGIN
  DECLARE z1, z2 INT;
  SET z1 = y;
  SET z2 = z1+2;
  INSERT INTO t1 VALUES (x, z2);
END|
DELIMITER ;|
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_locset("locset", 19);
SELECT * FROM t1;


# In some contexts local variables are not recognized
# (and in some, you have to qualify the identifier).
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_setcontext() SQL SECURITY INVOKER
BEGIN
  DECLARE data INT DEFAULT 2;
  INSERT INTO t1 (id, data) VALUES ("foo", 1);
  REPLACE t1 SET data = data, id = "bar";
  UPDATE t1 SET id = "kaka", data = 3 WHERE t1.data = data;
END|
DELIMITER ;|
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_setcontext();
SELECT * FROM t1;



# Set things to null
--echo connecting as user 'sp_cre';
connection sp_cre;
--echo connected;
CREATE TABLE t3 ( d DATE, i INT, f DOUBLE, s VARCHAR(32) );

--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_nullset() SQL SECURITY INVOKER
BEGIN
  DECLARE ld DATE;
  DECLARE li INT;
  DECLARE lf DOUBLE;
  DECLARE ls VARCHAR(32);

  SET ld = null, li = null, lf = null, ls = null;
  INSERT INTO t3 VALUES (ld, li, lf, ls);

  INSERT INTO t3 (i, f, s) VALUES ((ld is null), 1,    "ld is null"),
                                  ((li is null), 1,    "li is null"),
				  ((li = 0),     null, "li = 0"),
				  ((lf is null), 1,    "lf is null"),
				  ((lf = 0),     null, "lf = 0"),
				  ((ls is null), 1,    "ls is null");
END|
DELIMITER ;|
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_nullset();
SELECT * FROM t3;
--echo connecting as user 'sp_cre';
connection sp_cre;
--echo connected;
DROP TABLE t3;


# The peculiar (non-standard) mixture of variables types in SET.
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_mixset(x CHAR(16), y INT)
               SQL SECURITY INVOKER
BEGIN
  DECLARE z INT;
  SET @z = y, z = 666, max_join_size = 100;
  INSERT INTO t1 VALUES (x, z);
END|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_mixset("mixset", 19);
SHOW VARIABLES LIKE 'max_join_size';
SELECT id,data,@z FROM t1;


# Multiple CALL statements, one with OUT parameter.
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_zip(x CHAR(16), y INT)
             SQL SECURITY INVOKER
BEGIN
  DECLARE z INT;
  CALL initial_zap(y, z);
  CALL initial_bar(x, z);
END|

# SET local variables and OUT parameter.
CREATE PROCEDURE initial_zap(x INT, OUT y INT)
BEGIN
  DECLARE z INT;
  SET z = x+1, y = z;
END|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
use procdb;
CALL initial_zip("zip", 99);
SELECT * FROM t1;

# Top-level OUT parameter
CALL initial_zap(7, @zap);
SELECT @zap;



# "Deep" CALLs...
--echo connecting as user 'root';
connection root;
--echo connected;
use procdb;
CREATE PROCEDURE initial_c1(x INT) SQL SECURITY INVOKER
     CALL initial_c2("c", x);

CREATE PROCEDURE initial_c2(s CHAR(16), x INT)
           SQL SECURITY INVOKER
     CALL initial_c3(x, s);

CREATE PROCEDURE initial_c3(x INT, s CHAR(16))
           SQL SECURITY INVOKER
     CALL initial_c4("level", x, s);

CREATE PROCEDURE initial_c4(l CHAR(8), x INT, s CHAR(16))
           SQL SECURITY INVOKER
     INSERT INTO t1 VALUES (concat(l,s), x);

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_c1(42);
SELECT * FROM t1;


# INOUT test
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_iotest(x1 CHAR(16), x2 CHAR(16), y INT)
            SQL SECURITY INVOKER
BEGIN
  CALL initial_inc2(x2, y);
  INSERT INTO t1 VALUES (x1, y);
END|


CREATE PROCEDURE initial_inc2(x CHAR(16), y INT)
            SQL SECURITY INVOKER
BEGIN
  CALL initial_inc(y);
  INSERT INTO t1 VALUES (x, y);
END|

CREATE PROCEDURE initial_inc(INOUT io INT)
     SET io = io + 1|

DELIMITER ;|
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_iotest("io1", "io2", 1);
SELECT * FROM t1;


# Propagating top-level @-vars
--echo connecting as user 'root';
connection root;
--echo connected;
CREATE PROCEDURE initial_incr(INOUT x INT)
     CALL initial_inc(x);


--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
# Before
SELECT @zap;
CALL initial_incr(@zap);
# After
SELECT @zap;


# Call-by-value test
#  The expected result is:
#    ("cbv2", 4)
#    ("cbv1", 4711)
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_cbv1()
        SQL SECURITY INVOKER
BEGIN
  DECLARE y INT DEFAULT 3;
  CALL initial_cbv2(y+1, y);
  INSERT INTO t1 VALUES ("cbv1", y);
end|


CREATE PROCEDURE initial_cbv2(y1 INT, INOUT y2 INT)
          SQL SECURITY INVOKER
BEGIN
  SET y2 = 4711;
  INSERT INTO t1 VALUES ("cbv2", y1);
end|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_cbv1();
SELECT * FROM t1;


# SubSELECT arguments

INSERT INTO t2 VALUES ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3);


--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_sub1(id CHAR(16), x INT)
           SQL SECURITY INVOKER
  INSERT INTO t1 VALUES (id, x)|


CREATE PROCEDURE initial_sub2(id CHAR(16))
           SQL SECURITY INVOKER
BEGIN
   DECLARE x INT;
   SET x = (SELECT sum(t.i) FROM t2 t);
   INSERT INTO t1 VALUES (id, x);
END|

CREATE FUNCTION initial_sub3(i int) RETURNS INT
    RETURN i+1|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_sub1("sub1a", (SELECT 7));
CALL initial_sub1("sub1b", (SELECT max(i) FROM t2));
--error ER_OPERAND_COLUMNS
CALL initial_sub1("sub1c", (SELECT i,d FROM t2 limit 1));
CALL initial_sub1("sub1d", (SELECT 1 FROM (SELECT 1) a));
CALL initial_sub2("sub2");
SELECT * FROM t1;
SELECT initial_sub3((SELECT max(i) FROM t2));


# basic tests of the flow control constructs

# Just test on 'x'...
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_a0(x INT) SQL SECURITY INVOKER
WHILE x DO
  SET x = x-1;
  INSERT INTO t1 VALUES ("a0", x);
END WHILE|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_a0(3);
SELECT * FROM t1;


# The same, but with a more traditional test.
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_a(x INT) SQL SECURITY INVOKER
WHILE x > 0 DO
  SET x = x-1;
  INSERT INTO t1 VALUES ("a", x);
END WHILE|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_a(3);
SELECT * FROM t1;


# REPEAT
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_b(x INT) SQL SECURITY INVOKER
REPEAT
  INSERT INTO t1 VALUES (repeat("b",3), x);
  SET x = x-1;
UNTIL x = 0 END REPEAT|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_b(3);
SELECT * FROM t1;


# Check that repeat isn't parsed the wrong way
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_b2(x INT)
REPEAT(SELECT 1 INTO OUTFILE 'b2');
  INSERT INTO t1 VALUES (repeat("b2",3), x);
  SET x = x-1;
UNTIL x = 0 END REPEAT|
DELIMITER ;|
# We don't actually want to CALL it.


# Labelled WHILE with ITERATE (pointless really)
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_c(x int) SQL SECURITY INVOKER
hmm: WHILE x > 0 DO
  INSERT INTO t1 VALUES ("done", x);
  SET x = x-1;
  ITERATE hmm;
  INSERT INTO t1 VALUES ("not-done", x);
END WHILE hmm|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_c(3);
SELECT * FROM t1;


# Labelled WHILE with LEAVE
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_d(x int) SQL SECURITY INVOKER
hmm: WHILE x > 0 DO
  INSERT INTO t1 VALUES ("while", x);
  SET x = x-1;
  LEAVE hmm;
  INSERT INTO t1 VALUES ("x", x);
END WHILE|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_d(3);
SELECT * FROM t1;


# LOOP, with simple IF statement
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_e(x INT) SQL SECURITY INVOKER
foo: LOOP
  IF x = 0 THEN
    LEAVE foo;
  END IF;
  INSERT INTO t1 VALUES ("loop", x);
  SET x = x-1;
END LOOP foo|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_e(3);
SELECT * FROM t1;


# A full IF statement
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_f(x INT) SQL SECURITY INVOKER
IF x < 0 THEN
  INSERT INTO t1 VALUES ("f", 0);
ELSEIF x = 0 THEN
  INSERT INTO t1 VALUES ("f", 1);
ELSE
  INSERT INTO t1 VALUES ("f", 2);
END IF|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_f(-2);
CALL initial_f(0);
CALL initial_f(4);
SELECT * FROM t1;


# This form of CASE is really just syntactic sugar for IF-ELSEIF-...
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_g(x INT) SQL SECURITY INVOKER
CASE
WHEN x < 0 THEN
  INSERT INTO t1 VALUES ("g", 0);
WHEN x = 0 THEN
  INSERT INTO t1 VALUES ("g", 1);
ELSE
  INSERT INTO t1 VALUES ("g", 2);
END CASE|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_g(-42);
CALL initial_g(0);
CALL initial_g(1);
SELECT * FROM t1;


# The "simple CASE"
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_h(x int) SQL SECURITY INVOKER
CASE x
WHEN 0 THEN
  INSERT INTO t1 VALUES ("h0", x);
WHEN 1 THEN
  INSERT INTO t1 VALUES ("h1", x);
ELSE
  INSERT INTO t1 VALUES ("h?", x);
END CASE|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_h(0);
CALL initial_h(1);
CALL initial_h(17);
SELECT * FROM t1;

# It's actually possible to LEAVE a BEGIN-END block
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_i(x INT) SQL SECURITY INVOKER
foo:
BEGIN
  IF x = 0 THEN
    LEAVE foo;
  END IF;
  INSERT INTO t1 VALUES ("i", x);
END foo|
DELIMITER ;|
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_i(0);
CALL initial_i(3);
SELECT * FROM t1;


# SELECT with one of more result set sent back to the clinet
--echo connecting as user 'root';
connection root;
--echo connected;
CREATE PROCEDURE initial_sel1()
    SELECT * FROM t1;

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_sel1();


# SELECT INTO local variables
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_into_test(x CHAR(16), y INT)
                 SQL SECURITY INVOKER
BEGIN
  INSERT INTO t1 VALUES (x, y);
  SELECT id,data INTO x,y FROM t1 LIMIT 1;
  INSERT INTO t1 VALUES (concat(x, "2"), y+2);
END|
DELIMITER ;|
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_into_test("into", 100);
SELECT * FROM t1;


# SELECT INTO with a mix of local and global variables
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_into_test2(x CHAR(16), y INT)
              SQL SECURITY INVOKER
BEGIN
  INSERT INTO t1 VALUES (x, y);
  SELECT id,data INTO x,@z FROM t1 LIMIT 1;
  INSERT INTO t1 VALUES (concat(x, "2"), y+2);
END|
DELIMITER ;|
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_into_test2("into", 100);
SELECT id,data,@z FROM t1;


# SELECT * INTO ... (bug test)
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_into_test3()
       SQL SECURITY INVOKER
BEGIN
  DECLARE x CHAR(16);
  DECLARE y INT;
  SELECT * INTO x,y FROM t1 LIMIT 1;
  INSERT INTO t2 VALUES (x, y, 0.0);
END|
DELIMITER ;|
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
DELETE FROM t1;
INSERT INTO t1 VALUES ("into3", 19);
# Two CALL needed for bug test
CALL initial_into_test3();
CALL initial_into_test3();
SELECT * FROM t2;


#
# CONDITIONs and HANDLERs
#

--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_hndlr1(val INT)
           SQL SECURITY INVOKER
BEGIN
  DECLARE x INT DEFAULT 0;
  DECLARE foo CONDITION FOR 1136;
  DECLARE bar CONDITION FOR SQLSTATE '42S98';        # Just for testing syntax
  DECLARE zip CONDITION FOR SQLSTATE VALUE '42S99';  # Just for testing syntax
  DECLARE CONTINUE HANDLER FOR foo SET x = 1;

  INSERT INTO t1 VALUES ("hndlr1", val, 2);  # Too many vals throws 1136
  IF (x) THEN
    INSERT INTO t1 VALUES ("hndlr1", val);   # This instead then
  END IF;
END|
DELIMITER ;|
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_hndlr1(42);
SELECT * FROM t1 ORDER BY id;



--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_hndlr2(val INT)
           SQL SECURITY INVOKER
BEGIN
  DECLARE x INT DEFAULT 0;

  BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '21S01' SET x = 1;

    INSERT INTO t1 VALUES ("hndlr2", val, 2); # Too many values
  END;

  INSERT INTO t1 VALUES ("hndlr2", x);
END|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_hndlr2(42);
SELECT * FROM t1 ORDER BY id;


--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_hndlr3(val INT)
BEGIN
  DECLARE x INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION        # Any error
  BEGIN
    DECLARE z INT;

    SET z = 2 * val;
    SET x = 1;
  END;

  IF val < 10 THEN
    BEGIN
      DECLARE y INT;

      SET y = val + 10;
      INSERT INTO t1 VALUES ("hndlr3", y, 2);  # Too many values
      IF x THEN
        INSERT INTO t1 VALUES ("hndlr3", y);
      END IF;
    END;
  END IF;
END|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_hndlr3(3);
SELECT * FROM t1 ORDER BY id;


#
# Cursors
#
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_cur1() SQL SECURITY INVOKER
BEGIN
  DECLARE a CHAR(16);
  DECLARE b INT;
  DECLARE c DOUBLE;
  DECLARE done INT DEFAULT 0;
  DECLARE c CURSOR FOR SELECT * FROM t2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN c;
  REPEAT
    FETCH c INTO a, b, c;
    IF NOT done THEN
        INSERT INTO t1 VALUES (a, b+c);
    END IF;
  UNTIL done END REPEAT;
  CLOSE c;
END|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
DELETE FROM t1;
DELETE FROM t2;
INSERT INTO t2 VALUES ("foo", 42, -1.9), ("bar", 3, 12.1), ("zap", 666, -3.14);
CALL initial_cur1();
SELECT * FROM t1;



connection root;
CREATE TABLE procdb.t3 ( s char(16), i int );

--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_cur2() SQL SECURITY INVOKER
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE c1 CURSOR FOR SELECT id,data FROM t1 ORDER BY id;
  DECLARE c2 CURSOR FOR SELECT i FROM t2 ORDER BY i;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN c1;
  OPEN c2;
  REPEAT
  BEGIN
    DECLARE a CHAR(16);
    DECLARE b,c INT;

    FETCH FROM c1 INTO a, b;
    FETCH NEXT FROM c2 INTO c;
    IF NOT done THEN
      IF b < c THEN
        INSERT INTO t3 VALUES (a, b);
      ELSE
        INSERT INTO t3 VALUES (a, c);
      END IF;
    END IF;
  END;
  UNTIL done END REPEAT;
  CLOSE c1;
  CLOSE c2;
END|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_cur2();
SELECT * FROM t3;
DELETE FROM t1;
DELETE FROM t2;

connection root;
DROP TABLE t3;



# The few characteristics we parse
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE initial_chistics()
    SQL SECURITY INVOKER
    LANGUAGE SQL
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    COMMENT 'Characteristics PROCEDURE test'
  INSERT INTO t1 VALUES ("chistics", 1)|
DELIMITER ;|

--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;

# DEBUG -modify this when #21244 fixed in both versions
--replace_column 3 '*'
SHOW CREATE PROCEDURE initial_chistics;
# Call it, just to make sure.
CALL initial_chistics();
SELECT * FROM t1;
DELETE FROM t1;


# Check mode settings
INSERT INTO t1 VALUES ("foo", 1), ("bar", 2), ("zip", 3);
SET @@sql_mode = 'ANSI';
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER $;
CREATE PROCEDURE initial_modes(OUT c1 INT, OUT c2 INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE x INT;
  DECLARE c CURSOR FOR SELECT data FROM t1;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  SELECT 0 || 23 INTO c1;
  SET c2 = 0;
  OPEN c;
  REPEAT
    FETCH c INTO x;
    IF NOT done THEN
      SET c2 = c2 + 1;
    END IF;
  UNTIL done END REPEAT;
  CLOSE c;
END$
DELIMITER ;$

--echo connecting to user 'sp_execute';
connection sp_execute;
--echo connected;
SET @@sql_mode = '';
SET sql_SELECT_limit = 1;
CALL initial_modes(@c1, @c2);
SET sql_SELECT_limit = DEFAULT;

SELECT @c1, @c2;
DELETE FROM t1;

# And yet again, with just a PROCEDURE.

--echo connecting to user 'root';
connection root;
--echo connected;
CREATE DATABASE sp_db3;
# Commenting below lines as 'root' already has 'ALL' privs
#--replace_result $CLIENT_HOST CLIENT_HOST
#eval GRANT CREATE ROUTINE ON sp_db3.* TO 'root'@'$CLIENT_HOST';
--replace_result $CLIENT_HOST CLIENT_HOST
eval GRANT EXECUTE ON PROCEDURE sp_db3.dummy TO 'sp_execute'@'$CLIENT_HOST';

--echo connecting to user 'root';
connection root;
--echo connected;
USE sp_db3;

CREATE PROCEDURE dummy(OUT x INT)
  SET x = 42;

--echo connecting to user 'sp_execute';
connection sp_execute;
--echo connected;
CALL sp_db3.dummy(@dummy);
SELECT @dummy;

--echo connecting to user 'root';
connection root;
--echo connected;
DROP DATABASE sp_db3;
# Check that it's gone
SELECT type,db,name FROM mysql.proc WHERE db = 'sp_db3';

Select name, type from mysql.proc WHERE db = 'procdb';