# file upgrade01.test
# This is the seconds phase stored procedure test for the updown test suite

# The strategy here as in all updown tests will be to attempt to use/modify/drop
# objects created in the previous phase, and then create more objects here for 
# use in the final 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


connect (root, $MASTER_HOST, root,,);
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;
# DEBUG when bug 19857 is resolved use sp_create user to create
#      procedures instead of root;

######################################################################
########## TEST CASES BEGIN HERE #####################################
######################################################################
use procdb;

#begin by testing the existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_empty();
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_empty
          COMMENT 'upgrade/alter test';
DROP PROCEDURE procdb.initial_empty;

# make new procedure with no statements
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_empty()
BEGIN
END|
DELIMITER ;|
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL upgrade_empty();



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_foo42();
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_foo42
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA;
DROP PROCEDURE procdb.initial_foo42;

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



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_bar("bar", 667);
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_bar
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA; 
# don't drop this one yet as it's used later

# Make new - Single statement, two IN params.
--echo connecting as user 'root'
connection root;
--echo connected;
CREATE PROCEDURE upgrade_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 upgrade_bar("bar", 666);
SELECT * FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_scope(2, 3.33);
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_scope
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY INVOKER;
DROP PROCEDURE procdb.initial_scope;

# Make new-Scope test. 
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_scope (1,2.2);



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_two("three", "four", 5);
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_two
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_two;

# Make new - Two statements
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_two("one", "two", 3);
SELECT * FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_locset("locset", 20);
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_locset
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA;
DROP PROCEDURE procdb.initial_locset;

# Make new-Simple test of local variables and SET.
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_locset("locset", 19);
SELECT * FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_setcontext();
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_setcontext
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY INVOKER;
DROP PROCEDURE procdb.initial_setcontext;

# Make new 
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_setcontext();
SELECT * FROM t1;

# needed for next procedure
--echo connecting as user 'sp_cre';
connection sp_cre;
--echo connected;
CREATE TABLE t3 ( d DATE, i INT, f DOUBLE, s VARCHAR(32) );

# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_nullset();
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_nullset
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_nullset;

# Set things to null
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_nullset();
SELECT * FROM t3;
--echo connecting as user 'sp_cre';
connection sp_cre;
--echo connected;
DROP TABLE t3;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_mixset("mixset",20);
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_mixset
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_mixset;

# Make new
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_mixset("mixset", 19);
SHOW VARIABLES LIKE 'max_join_size';
SELECT id,data,@z FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_zip("zip", 88);
SELECT * FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_zip
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_zip;
DROP PROCEDURE procdb.initial_zap;
DROP PROCEDURE procdb.initial_bar;

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

# SET local variables and OUT parameter.
CREATE PROCEDURE upgrade_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 upgrade_zip("zip", 99);
SELECT * FROM t1;

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


# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_c1(43);
SELECT * FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_c1
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
use procdb;
DROP PROCEDURE initial_c1;
DROP PROCEDURE initial_c2;
DROP PROCEDURE initial_c3;
DROP PROCEDURE initial_c4;

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

CREATE PROCEDURE upgrade_c2(s CHAR(16), x INT)
           SQL SECURITY INVOKER
     CALL upgrade_c3(x, s);

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

CREATE PROCEDURE upgrade_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 upgrade_c1(42);
SELECT * FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_iotest("io1", "io2", 1);
SELECT * FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_iotest
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_iotest;

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

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

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

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



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
# Before
SELECT @zap;
CALL initial_incr(@zap);
# After
SELECT @zap;
SELECT * FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_incr
          COMMENT 'upgrade/alter test'
          SQL SECURITY INVOKER;
DROP PROCEDURE procdb.initial_inc;
DROP PROCEDURE procdb.initial_inc2;
DROP PROCEDURE procdb.initial_incr;

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

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



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_cbv1();
SELECT * FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_cbv1
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_cbv1;
DROP PROCEDURE procdb.initial_cbv2;

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

CREATE PROCEDURE upgrade_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 upgrade_cbv1();
SELECT * FROM t1;
DELETE FROM t1;
DELETE FROM t2;


INSERT INTO t2 VALUES ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3);
# test existing procedure
--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));
DELETE FROM t1;
DELETE FROM t2;
INSERT INTO t2 VALUES ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3);
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_sub1
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
use procdb;
DROP PROCEDURE initial_sub1;
DROP PROCEDURE initial_sub2;
DROP FUNCTION initial_sub3;

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

CREATE PROCEDURE upgrade_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 upgrade_sub3(i int) RETURNS INT
    RETURN i+1|
DELIMITER ;|

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


# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_a0(5);
SELECT * FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_a0
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_a0;

# Make new- basic tests of the flow control constructs
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_a0(3);
SELECT * FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_a(3);
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_a
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_a;

# Make new- The same, but with a more traditional test.
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_a(3);
SELECT * FROM t1;
DELETE FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_b(3);
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_b
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_b;

# Make new - REPEAT
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_b(3);
SELECT * FROM t1;
DELETE FROM t1;



# test existing procedure
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_b2
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_b2;

# Make new -Check that repeat isn't parsed the wrong way
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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.



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_c(3);
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_c
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_c;

# Make new - Labelled WHILE with ITERATE (pointless really)
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_c(3);
SELECT * FROM t1;
DELETE FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_d(3);
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_d
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_d;

# Make new - Labelled WHILE with LEAVE
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_d(3);
SELECT * FROM t1;
DELETE FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_e(3);
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_e
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_e;

# Make new - LOOP, with simple IF statement
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_e(3);
SELECT * FROM t1;
DELETE FROM t1;




# test existing procedure
--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;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_f
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_f;

# A full IF statement
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_f(-2);
CALL upgrade_f(0);
CALL upgrade_f(4);
SELECT * FROM t1;
DELETE FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_g(-3);
CALL initial_g(0);
CALL initial_g(99);
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_g
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_g;

# Make new - using CASE
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_g(-42);
CALL upgrade_g(0);
CALL upgrade_g(1);
SELECT * FROM t1;
DELETE FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_h(0);
CALL initial_h(1);
CALL initial_h(99);
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_h
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_h;

# Make new - "simple CASE"
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_h(0);
CALL upgrade_h(1);
CALL upgrade_h(17);
SELECT * FROM t1;
DELETE FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_i(0);
CALL initial_i(3);
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_i
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_i;

# Make new - IF with LEAVE
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_i(0);
CALL upgrade_i(3);
SELECT * FROM t1;
DELETE FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_sel1();
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_sel1
          COMMENT 'upgrade/alter test'
          READS SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_sel1;

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

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



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_into_test("into", 200);
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_into_test
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_into_test;

# Make new- SELECT INTO local variables
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_into_test("into", 100);
SELECT * FROM t1;
DELETE FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_into_test2("into", 201);
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_into_test2
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_into_test2;

# Make new- SELECT INTO with a mix of local and global variables
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_into_test2("into", 101);
SELECT id,data,@z FROM t1;
DELETE FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_into_test3();
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_into_test3
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_into_test3;

# Make new - SELECT * INTO ... (bug test)
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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", 103);
# Two CALL needed for bug test
CALL upgrade_into_test3();
CALL upgrade_into_test3();
SELECT * FROM t2;


#
# CONDITIONs and HANDLERs
#
# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_hndlr1(42);
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_hndlr1
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_hndlr1;

# Make new
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_hndlr1(42);
SELECT * FROM t1;
DELETE FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_hndlr2(42);
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_hndlr2
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_hndlr2;

# Make new
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_hndlr2(42);
SELECT * FROM t1;
DELETE FROM t1;
DELETE FROM t2;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_hndlr3(3);
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_hndlr3
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_hndlr3;

# Make new
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_hndlr3(3);
SELECT * FROM t1;


#
# Cursors
#
# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_cur1();
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_cur1
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_cur1;

# Make new
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 upgrade_cur1();
SELECT * FROM t1;


# needed for next test
connection root;
CREATE TABLE procdb.t3 ( s char(16), i int );
# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_cur2();
SELECT * FROM t1;
DELETE FROM t1;
DELETE FROM t3;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_cur2
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_cur2;

# Make new
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_cur2() SQL SECURITY INVOKER
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE c1 CURSOR FOR SELECT id,data FROM t1;
  DECLARE c2 CURSOR FOR SELECT i FROM t2;
  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 upgrade_cur2();
SELECT * FROM t3;
DELETE FROM t1;
DELETE FROM t2;

connection root;
DROP TABLE t3;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_chistics();
SELECT * FROM t1;
DELETE FROM t1;
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_chistics
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_chistics;

# Make new 
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER |;
CREATE PROCEDURE upgrade_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 is fixed in both versions
--replace_column 3 '*'
SHOW CREATE PROCEDURE upgrade_chistics;
# Call it, just to make sure.
CALL upgrade_chistics();
SELECT * FROM t1;
DELETE FROM t1;



# test existing procedure
--echo connecting as user 'sp_execute';
connection sp_execute;
--echo connected;
CALL initial_modes(@c1, @c2);
SET sql_SELECT_limit = DEFAULT;
SELECT @c1, @c2;
DELETE FROM t1;
INSERT INTO t1 VALUES ("foo", 1), ("bar", 2), ("zip", 3);
--echo connecting as user 'sp_alter';
connection sp_alter;
--echo connected;
ALTER PROCEDURE procdb.initial_modes
          COMMENT 'upgrade/alter test'
          MODIFIES SQL DATA
          SQL SECURITY DEFINER;
DROP PROCEDURE procdb.initial_modes;

# Make new Check mode settings
SET @@sql_mode = 'ANSI';
--echo connecting as user 'root';
connection root;
--echo connected;
DELIMITER $;
CREATE PROCEDURE upgrade_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 ;$
SET @@sql_mode = '';

--echo connecting to user 'sp_execute';
connection sp_execute;
--echo connected;
SET sql_SELECT_limit = 1;
CALL upgrade_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 LIKE 'sp_db3';

select name, type from mysql.proc WHERE db LIKE 'proc%';