--echo connecting as 'root';
connect (root, localhost, 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
#GRANT CREATE ROUTINE, SELECT ON procdb.* TO 'sp_create'@'localhost'
#                IDENTIFIED BY 'sp_create';
GRANT ALTER ROUTINE, SELECT ON procdb.* TO  'sp_alter'@'localhost'
                IDENTIFIED BY 'sp_alter';
GRANT EXECUTE, INSERT, DELETE, UPDATE, SELECT ON procdb.*
             TO  'sp_execute'@'localhost' IDENTIFIED BY 'sp_execute';
GRANT CREATE, DELETE, DROP ON procdb.* TO 'sp_cre'@'localhost'
                IDENTIFIED BY 'sp_cre';

connect (sp_cre,localhost,sp_cre,sp_cre,procdb);
connect (sp_execute, localhost, sp_execute, sp_execute,procdb);
connect (sp_alter, localhost, sp_alter, sp_alter,);
#connect (sp_create, localhost, 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 = 100000;
  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;