# Bug#12093: SP not found on second PS execution if another thread drops other
#            SP in between 
#
# Architecture of the test:
#
#    Session 0 (default): Create some objects needed for session 1 and 2
#              working tables t0, t1, t2
#              table with actions for session 2  t99_actions
#
#    1. Session 0 (default):i
#               - Create a Stored Procedure and/or a function needed
#                 for the activies of session 1
#               - adjust t99_actions, by taking care that testcases for
#                 all tables used within the Stored procedures and
#                 Functions are included
#
#    2. Define a statement($sess1_command) calling a procedure or function
#
#    3. For all testcases (action blocks of session 2) in table t99_actions
#       do
#          1. Session 1 (con1):
#               - Prepare a statement from $sess1_command
#               - Execute the prepared statement
#          2. Session 2 (con2): 
#                 Execute the action block for this testcase
#          3. Session 1 (con1):
#                 Execute the prepared statement
#          4. Session 2 (con2): 
#                 Execute the action block for this testcase
#          5. Session 1 (con1):
#                 Execute the prepared statement
#       done
#
#    4. like 1. ........ 
#
#
# Thinkable future extensions of this test:
#   Command executed by session 1
#   - Put columns, constants, @variables and ... into
#     Stored Procedure/Function Parameters
#   - Cursors ?
#   Command block executed by session 2
#   - LOCK TABLE                         
#   - Modify table + COMMIT/ROLLBACK
#   - Modify table + ROLLBACK
#   - TRUNCATE table
#   - CREATE/DROP INDEX
#   - ANALYZE/OPTIMIZE.... table
#
#   Transactional and non transactional tables should be tested.
#   Maybe that some combinations cause a blocking of a session.
#
# Please try to put as many informations needed for understanding/replaying
# a problem into the protocol. That means
# - show if the active connection is switched
# - prevent the protocolling of auxiliary commands
#   Example: Search for the next command to be executed by session 2 ...
#
# Use of --disable_abort_on_error
#   - It should be switched on, when executing a testcase.
#   - It should be switched off (--enable_abort_on_error), when
#     inserting testcases into t99_actions, ...
#

--echo #--- Basic preparations needed for all tests   START
--disable_warnings
DROP TABLE IF EXISTS t0, t1, t2, t10, t99_actions;
DROP VIEW IF EXISTS v0;
DROP PROCEDURE IF EXISTS test_proc1;
DROP PROCEDURE IF EXISTS test_proc10;
DROP FUNCTION  IF EXISTS test_func1;
DROP FUNCTION  IF EXISTS test_func10;
DROP DATABASE IF EXISTS mysqltest_db1;
--enable_warnings
################################################################################
# Create some stuff for the actions of the first and second session            #
################################################################################
CREATE TABLE t0 (f1 BIGINT, f2 BIGINT);
INSERT INTO t0 VALUES(1, 1);
INSERT INTO t0 SELECT f1 +  1, f1 +  1 FROM t0;
INSERT INTO t0 SELECT f1 +  2, f1 +  2 FROM t0;
INSERT INTO t0 SELECT f1 +  4, f1 +  4 FROM t0;
INSERT INTO t0 SELECT f1 +  8, f1 +  8 FROM t0;
INSERT INTO t0 SELECT f1 + 16, f1 + 16 FROM t0;
CREATE TABLE t1 LIKE t0;
INSERT INTO t1 SELECT * FROM t0;
CREATE TABLE t2 LIKE t0;
INSERT INTO t2 SELECT * FROM t0;

CREATE TABLE t99_actions (
test_number BIGINT UNSIGNED,
command_number MEDIUMINT UNSIGNED,
command VARCHAR(200),
PRIMARY KEY (test_number,command_number)
);

################################################################################
# Establish the working connections                                            #
################################################################################
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (con1,localhost,root,,test);
--echo connection default;
connection default;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (con2,localhost,root,,test);
connection default;

################################################################################
# Define actions of session 2                                                  #
# Attention: Do not use objects (table t1, t2) needed by the actions of        #
#            session 1                                                         #
################################################################################
# t0 should not be used within the Stored Procedure or functions
INSERT INTO t99_actions VALUES(  1,  1, 'SELECT 1 AS my_value FROM t0 WHERE f1 = 1');

INSERT INTO t99_actions VALUES(  2,  1, 'SELECT MAX(f1) INTO @t0_max FROM t0');
INSERT INTO t99_actions VALUES(  2,  2, 'UPDATE t0 SET f1 = @t0_max + 1 WHERE f1 = @t0_max');

INSERT INTO t99_actions VALUES(  3,  1, 'SELECT MAX(f1) INTO @t0_max FROM t0');
INSERT INTO t99_actions VALUES(  3,  2, 'DELETE FROM t0 WHERE f1 = @t0_max');

INSERT INTO t99_actions VALUES(  4,  1, 'SELECT MAX(f1) INTO @t0_max FROM t0');
INSERT INTO t99_actions VALUES(  4,  2, 'INSERT INTO t0 SET f1 = @t0_max + 1');

INSERT INTO t99_actions VALUES(  5,  1, 'CREATE TABLE t10 (f1 BIGINT)');
INSERT INTO t99_actions VALUES(  5,  2, 'DROP   TABLE t10');

INSERT INTO t99_actions VALUES(  6,  1, 'CREATE PROCEDURE test_proc10() SELECT 238');
INSERT INTO t99_actions VALUES(  6,  2, 'DROP   PROCEDURE test_proc10');

INSERT INTO t99_actions VALUES(  7,  1, 'CREATE FUNCTION test_func10() RETURNS INT RETURN 5');
INSERT INTO t99_actions VALUES(  7,  2, 'DROP   FUNCTION test_func10');

INSERT INTO t99_actions VALUES(  8,  1, 'CREATE VIEW v0 AS SELECT * FROM t0');
INSERT INTO t99_actions VALUES(  8,  2, 'DROP VIEW v0');

INSERT INTO t99_actions VALUES(  9,  1, 'CREATE DATABASE mysqltest_db1');
INSERT INTO t99_actions VALUES(  9,  2, 'DROP DATABASE mysqltest_db1');

INSERT INTO t99_actions VALUES( 10,  1, 'ALTER TABLE t0 ADD f3 BIGINT');
INSERT INTO t99_actions VALUES( 10,  2, 'ALTER TABLE t0 DROP f3');

--echo #--- Basic preparations needed for all tests   STOP

################################################################################
# Define commands, which will generate actions on the tables t1 and t2         # 
################################################################################
let $insert_t1_actions= INSERT INTO t99_actions SELECT test_number + 100, command_number,
                     REPLACE(command,' t0',' t1')
                     FROM t99_actions
                     WHERE test_number IN ( SELECT test_number FROM t99_actions
                                            WHERE command LIKE '% t0%');
let $insert_t2_actions= INSERT INTO t99_actions SELECT test_number + 200, command_number,
                     REPLACE(command,' t0',' t2')
                     FROM t99_actions
                     WHERE test_number IN ( SELECT test_number FROM t99_actions
                                            WHERE command LIKE '% t0%');
let $delete_t1_actions= DELETE FROM t99_actions WHERE test_number BETWEEN 100 AND 199;
let $delete_t2_actions= DELETE FROM t99_actions WHERE test_number BETWEEN 200 AND 299;


################################################################################
# Slightly modified testcase of Bug#12093                                      #
################################################################################
--echo ##### Variations on nearly original testcase for Bug#12093
--echo connection default;
connection default;
--disable_warnings
DROP PROCEDURE IF EXISTS test_proc1;
DROP FUNCTION IF EXISTS test_func1;
--enable_warnings
delimiter //;
CREATE PROCEDURE test_proc1 (f1 INT )
BEGIN
  SELECT * FROM t2 LIMIT 1;
END //
CREATE FUNCTION test_func1() RETURNS INT
BEGIN
  RETURN (SELECT MAX(f1) FROM t1);
END //
delimiter ;//
--disable_query_log
eval $delete_t1_actions;
eval $delete_t2_actions;
eval $insert_t1_actions;
eval $insert_t2_actions;
--enable_query_log

let $sess1_command= PREPARE s1 FROM  'CALL test_proc1 (test_func1())';
--source include/ml050a.inc


################################################################################
# First simplification: The stored procedure used by session 1 does not use
#                       a table.
################################################################################
--echo ##### Variations on simplified testcase 1
--echo connection default;
connection default;
--disable_warnings
DROP PROCEDURE IF EXISTS test_proc1;
DROP FUNCTION IF EXISTS test_func1;
--enable_warnings
delimiter //;
CREATE PROCEDURE test_proc1 ( f1 INT )
BEGIN
  SELECT 'here is test_proc1' AS my_content;
END //

CREATE FUNCTION test_func1() RETURNS INT
BEGIN
  RETURN (SELECT MAX(f1) FROM t1);
END //
delimiter ;//
--disable_query_log
eval $delete_t1_actions;
eval $delete_t2_actions;
eval $insert_t1_actions;
--enable_query_log

let $sess1_command= PREPARE s1 FROM  'CALL test_proc1 (test_func1())';
--source include/ml050a.inc


################################################################################
# Second simplification: The function used by session 1 does not use
#                        a table.
#    The problems disappear !
################################################################################
--echo ##### Variations on simplified testcase 2
--echo connection default;
connection default;
--disable_warnings
DROP PROCEDURE IF EXISTS test_proc1;
DROP FUNCTION IF EXISTS test_func1;
--enable_warnings
delimiter //;
CREATE PROCEDURE test_proc1 ( f1 INT )
BEGIN
  SELECT * FROM t2 LIMIT 1;
END //

CREATE FUNCTION test_func1() RETURNS INT
BEGIN
  RETURN (SELECT 'here is test_func1'  AS my_content);
END //
delimiter ;//
--disable_query_log
eval $delete_t1_actions;
eval $delete_t2_actions;
eval $insert_t2_actions;
--enable_query_log

let $sess1_command= PREPARE s1 FROM  'CALL test_proc1 (test_func1())';
--source include/ml050a.inc


################################################################################
# Third simplification: The stored procedure und the function used by session 1
#                       do not use any table. 
#    The problems disappear !
################################################################################
--echo ##### Variations on simplified testcase 3
--echo connection default;
connection default;
--disable_warnings
DROP PROCEDURE IF EXISTS test_proc1;
DROP FUNCTION IF EXISTS test_func1;
--enable_warnings
delimiter //;
CREATE PROCEDURE test_proc1 ( f1 INT )
BEGIN
  SELECT 'here is test_proc1' AS my_content;
END //
CREATE FUNCTION test_func1() RETURNS INT
BEGIN
  RETURN (SELECT 'here is test_func1'  AS my_content);
END //
delimiter ;//
--disable_query_log
eval $delete_t1_actions;
eval $delete_t2_actions;
--enable_query_log

let $sess1_command= PREPARE s1 FROM  'CALL test_proc1 (test_func1())';
--source include/ml050a.inc


################################################################################
# Forth simplification: The stored procedure used by session 1
#                       with a constant as parameter
#                       The function is not used.
#    The problems disappear !
################################################################################
--echo ##### Variations on simplified testcase 4
--echo connection default;
connection default;
--disable_warnings
DROP PROCEDURE IF EXISTS test_proc1;
DROP FUNCTION IF EXISTS test_func1;
--enable_warnings
delimiter //;
CREATE PROCEDURE test_proc1 (f1 INT )
BEGIN
  SELECT * FROM t2 LIMIT 1;
END //
delimiter ;//
--disable_query_log
eval $delete_t1_actions;
eval $delete_t2_actions;
eval $insert_t1_actions;
eval $insert_t2_actions;
--enable_query_log

let $sess1_command= PREPARE s1 FROM  'CALL test_proc1 (888)';
--source include/ml050a.inc

################################################################################
# Fifth simplification: There function using table t1 is used within a SELECT
#                       with a constant as parameter
#                       The procedure is not used.
#    The problems disappear !
################################################################################
--echo ##### Variations on simplified testcase 5
--echo connection default;
connection default;
--disable_warnings
DROP PROCEDURE IF EXISTS test_proc1;
DROP FUNCTION IF EXISTS test_func1;
--enable_warnings
delimiter //;
CREATE FUNCTION test_func1() RETURNS INT
BEGIN
  RETURN (SELECT MAX(f1) FROM t1);
END //
delimiter ;//
--disable_query_log
eval $delete_t1_actions;
eval $delete_t2_actions;
eval $insert_t1_actions;
--enable_query_log

let $sess1_command= PREPARE s1 FROM  'SELECT test_func1() FROM t1 LIMIT 1';
--source include/ml050a.inc


################################################################################
# Cleanup                                                                      #
################################################################################
--echo #--- Final Cleanup   START
--disable_warnings
DROP TABLE IF EXISTS t0, t1, t2, t10, t99_actions;
DROP VIEW IF EXISTS v0;
DROP PROCEDURE IF EXISTS test_proc1;
DROP PROCEDURE IF EXISTS test_proc10;
DROP FUNCTION  IF EXISTS test_func1;
DROP FUNCTION  IF EXISTS test_func10;
DROP DATABASE IF EXISTS mysqltest_db1;
--enable_warnings
--echo #--- Final Cleanup   STOP