# Note: 1. There are several deactivated statement sequences.
#          if (0)
#          {
#             <sequence>
#          }
#          Please switch to "if (1)" if you want that the statement sequence
#          will be executed.
#       2. The test starts with the extreme simplified testcase
#          (removal of columns, heavy use of constants ...) followed by
#          testcases with increased complexity till the original testcase
#          is reached.



--disable_warnings
DROP DATABASE IF EXISTS mysqltest_db1;
--enable_warnings
CREATE DATABASE mysqltest_db1;
USE mysqltest_db1;
CREATE TABLE t_duplicate_key (f1 SMALLINT NOT NULL, PRIMARY KEY(f1));
INSERT INTO t_duplicate_key SET f1 = 1;
CREATE TABLE t_select_template (f1 BIGINT, f2 VARCHAR(20), f3 BIGINT);
INSERT INTO t_select_template SET f1 = 1, f2 = 'Schoenenbourg', f3 = 1;
CREATE TABLE t_select4 LIKE t_select_template;
INSERT INTO t_select4(f1,f2,f3)
SELECT f1, f2, f3 FROM t_select_template;
CREATE TABLE t_select3 LIKE t_select_template;
INSERT INTO t_select3(f1,f2,f3)
SELECT f1, f2, f3 FROM t_select_template;
CREATE TABLE t_select2 LIKE t_select_template;
INSERT INTO t_select2(f1,f2,f3)
SELECT f1, f2, f3 FROM t_select_template;
CREATE TABLE t_select1 LIKE t_select_template;
INSERT INTO t_select1(f1,f2,f3)
SELECT f1, f2, f3 FROM t_select_template;


--disable_abort_on_error

# Replay testcase for similar Bug#15516: Selecting from the view causes server
# to crash on 2nd, 4th,6th, etc executions.
# It does not CRASH on my MySQL release (~ 5.0.17) even with --ps-protocol .
# That means "my" bug has a different reason.
if (0)
{
  CREATE TABLE a (id int unsigned not null);
  CREATE TABLE b (id int unsigned not null);
  CREATE OR REPLACE VIEW c AS (SELECT * FROM a) UNION (SELECT * FROM b);
  SELECT * FROM c;
  SELECT * FROM c;
  SELECT * FROM c;
  SELECT * FROM c;
}

# The extreme simplified testcase.
if (1)
{
# --> CRASH CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t_select3 UNION DISTINCT SELECT 1 ;
CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t_select3 UNION ALL SELECT 1 ;
# --> No CRASH CREATE OR REPLACE VIEW v1 AS SELECT f1, f2, f3 FROM t_select3;
# CREATE OR REPLACE VIEW v2 AS SELECT f1 FROM v1;
CREATE OR REPLACE VIEW v2 AS SELECT 1 FROM v1;
# --> No CRASH CREATE OR REPLACE VIEW v2 AS SELECT f1, f2, f3 FROM t_select3 UNION DISTINCT SELECT 1 , 'Schoenenbourg' , 1 ;
CREATE OR REPLACE VIEW v3 AS SELECT 1 AS f1 FROM v2;
# --> No CRASH CREATE OR REPLACE VIEW v3 AS SELECT f1, f2, f3 FROM v1;

# Prepared Statements on VIEW v3 are harmless
# PREPARE stmt1 FROM 'SELECT f1 AS my_cal FROM v3';
# EXECUTE stmt1;
# EXECUTE stmt1;
# EXECUTE stmt1;

delimiter //;
CREATE FUNCTION func1() RETURNS BIGINT
BEGIN
RETURN (SELECT COUNT(*) FROM v3);
END//
delimiter ;//

SELECT func1();
SELECT 1;
}

if (0)
{
CREATE OR REPLACE VIEW view8 AS SELECT 1 FROM t_select3 UNION DISTINCT SELECT 1 ;
# --> No CRASH CREATE OR REPLACE VIEW view8 AS SELECT f1, f2, f3 FROM t_select3;
CREATE OR REPLACE VIEW view25 AS SELECT 1 FROM view8;
# --> No CRASH CREATE OR REPLACE VIEW view25 AS SELECT f1, f2, f3 FROM t_select3 UNION DISTINCT SELECT 1 , 'Schoenenbourg' , 1 ;
CREATE OR REPLACE VIEW view308 AS SELECT 1 FROM view25;
# --> No CRASH CREATE OR REPLACE VIEW view308 AS SELECT f1, f2, f3 FROM view8;

delimiter //;
CREATE FUNCTION func2() RETURNS BIGINT
BEGIN
DECLARE c CURSOR FOR SELECT 1 FROM view308;
RETURN 17;
END//
delimiter ;//

SELECT func2();
SELECT 1;
}

if (0)
{
CREATE OR REPLACE VIEW view8 AS SELECT f1, f2, f3 FROM t_select3 UNION DISTINCT SELECT 1 , 'Schoenenbourg' , 1 ;
# --> No CRASH CREATE OR REPLACE VIEW view8 AS SELECT f1, f2, f3 FROM t_select3;
CREATE OR REPLACE VIEW view25 AS SELECT f1, f2, f3 FROM view8;
# --> No CRASH CREATE OR REPLACE VIEW view25 AS SELECT f1, f2, f3 FROM t_select3 UNION DISTINCT SELECT 1 , 'Schoenenbourg' , 1 ;
CREATE OR REPLACE VIEW view308 AS SELECT f1, f2, f3 FROM view25;
# --> No CRASH CREATE OR REPLACE VIEW view308 AS SELECT f1, f2, f3 FROM view8;

delimiter //;
CREATE FUNCTION func3() RETURNS BIGINT
BEGIN
DECLARE c CURSOR FOR SELECT f1, f2, f3 FROM view308;
OPEN c;
CLOSE c;
RETURN 17;
END//
delimiter ;//

SELECT func3();
SELECT 1;
}

if (0)
{
CREATE OR REPLACE VIEW view8 AS SELECT f1, f2, f3 FROM t_select3 UNION DISTINCT SELECT 1 , 'Schoenenbourg' , 1 ;
# --> No CRASH CREATE OR REPLACE VIEW view8 AS SELECT f1, f2, f3 FROM t_select3;
CREATE OR REPLACE VIEW view25 AS SELECT f1, f2, f3 FROM view8;
CREATE OR REPLACE VIEW view308 AS SELECT f1, f2, f3 FROM view25;

delimiter //;
CREATE FUNCTION func4() RETURNS BIGINT
BEGIN
DECLARE _f1 BIGINT;
DECLARE _f2 VARCHAR(20);
DECLARE _f3 BIGINT;
DECLARE c CURSOR FOR SELECT f1, f2, f3 FROM view308;
OPEN c;
FETCH c INTO _f1, _f2, _f3;
CLOSE c;
RETURN _f1;
END//
delimiter ;//

SELECT func4();
SELECT 1;
}

if (0)
{
CREATE OR REPLACE VIEW view8 AS SELECT f1, f2, f3 FROM t_select3 UNION DISTINCT SELECT 1 AS f1, 'Schoenenbourg' AS f2, 1 AS f3;
CREATE OR REPLACE VIEW view25 AS SELECT f1, f2, f3 FROM view8;
CREATE OR REPLACE VIEW view308 AS SELECT f1, f2, f3 FROM view25;

delimiter //;
CREATE FUNCTION func5() RETURNS BIGINT
BEGIN
DECLARE _f1 BIGINT;
DECLARE _f2 VARCHAR(20);
DECLARE _f3 BIGINT;
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR SELECT f1, f2, f3 FROM view308;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
REPEAT
FETCH c INTO _f1, _f2, _f3;
UNTIL done END REPEAT;
CLOSE c;
RETURN _f1;
END//
delimiter ;//

SELECT func5();
SELECT 1;
}

if (0)
{
CREATE OR REPLACE VIEW view8 AS SELECT f1, f2, f3 FROM t_select3 UNION DISTINCT SELECT 1 AS f1, 'Schoenenbourg' AS f2, 1 AS f3;
CREATE OR REPLACE VIEW view25 AS SELECT f1, f2, f3 FROM view8;
CREATE OR REPLACE VIEW view308 AS SELECT f1, f2, f3 FROM view25;

delimiter //;
CREATE FUNCTION func6() RETURNS BIGINT
BEGIN
DECLARE _f1 BIGINT;
DECLARE _f2 VARCHAR(20);
DECLARE _f3 BIGINT;
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR SELECT f1, f2, f3 FROM view308 WHERE f3 IN (SELECT f1 FROM t_select2);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
REPEAT
FETCH c INTO _f1, _f2, _f3;
UNTIL done END REPEAT;
CLOSE c;
RETURN _f1;
END//
delimiter ;//

SELECT func6();
SELECT 1;
}

if (0)
{
CREATE OR REPLACE VIEW view8 AS SELECT f1, f2, f3 FROM t_select3 UNION DISTINCT SELECT 1 AS f1, 'Schoenenbourg' AS f2, 1 AS f3;
CREATE OR REPLACE VIEW view25 AS SELECT f1, f2, f3 FROM view8;
CREATE OR REPLACE VIEW view308 AS SELECT f1, f2, f3 FROM view25;

delimiter //;
CREATE FUNCTION func7() RETURNS BIGINT
BEGIN
DECLARE _f1 BIGINT;
DECLARE _f2 VARCHAR(20);
DECLARE _f3 BIGINT;
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR SELECT f1, f2, f3 FROM t_select2 WHERE f3 IN (SELECT f1 FROM view308);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
REPEAT
FETCH c INTO _f1, _f2, _f3;
UNTIL done END REPEAT;
CLOSE c;
RETURN _f1;
END//
delimiter ;//

SELECT func7();
SELECT 1;
}

if (0)
{
CREATE OR REPLACE VIEW view8 AS SELECT f1, f2, f3 FROM t_select3 UNION DISTINCT SELECT 1 AS f1, 'Schoenenbourg' AS f2, 1 AS f3;
CREATE OR REPLACE VIEW view25 AS SELECT f1, f2, f3 FROM view8;
CREATE OR REPLACE VIEW view308 AS SELECT f1, f2, f3 FROM view25 NATURAL JOIN t_select4;

delimiter //;
CREATE FUNCTION func8() RETURNS BIGINT
BEGIN
DECLARE _f1 BIGINT;
DECLARE _f2 VARCHAR(20);
DECLARE _f3 BIGINT;
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR SELECT f1, f2, f3 FROM t_select2 WHERE f3 IN (SELECT f1 FROM view308);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
REPEAT
FETCH c INTO _f1, _f2, _f3;
UNTIL done END REPEAT;
CLOSE c;
RETURN _f1;
END//
delimiter ;//

SELECT func8();
SELECT 1;
}


if (0)
{
CREATE OR REPLACE VIEW view8 AS SELECT f1, f2, f3 FROM t_select3 UNION DISTINCT SELECT 1 AS f1, 'Schoenenbourg' AS f2, 1 AS f3;
# CREATE OR REPLACE VIEW view25 AS SELECT f1, f2, f3 FROM t_select2 WHERE f3 IN (SELECT f1 FROM view8);
CREATE OR REPLACE VIEW view25 AS SELECT f1, f2, f3 FROM view8;
CREATE OR REPLACE VIEW view308 AS SELECT f1, f2, f3 FROM t_select4 NATURAL JOIN view25;

delimiter //;
CREATE FUNCTION func9() RETURNS BIGINT
BEGIN
DECLARE _f1 BIGINT;
DECLARE _f2 VARCHAR(20);
DECLARE _f3 BIGINT;
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR SELECT f1, f2, f3 FROM t_select2 WHERE f3 IN (SELECT f1 FROM view308);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
REPEAT
FETCH c INTO _f1, _f2, _f3;
UNTIL done END REPEAT;
CLOSE c;
RETURN _f1;
END//
delimiter ;//

SELECT func9();
SELECT 1;
}

if (1)
{
##### NO CRASH
# CREATE OR REPLACE VIEW view8 AS SELECT f1, f2, f3 FROM t_select3 UNION DISTINCT SELECT 1 AS f1, 'Schoenenbourg' AS f2, 1 AS f3;
CREATE OR REPLACE VIEW view25 AS SELECT f1, f2, f3 FROM t_select2 WHERE f3 IN (SELECT f1 FROM t_select3 UNION DISTINCT SELECT 1 AS f1);
CREATE OR REPLACE VIEW view308 AS SELECT f1, f2, f3 FROM t_select4 NATURAL JOIN view25;

delimiter //;
CREATE FUNCTION func10() RETURNS BIGINT
BEGIN
DECLARE _f1 BIGINT;
DECLARE _f2 VARCHAR(20);
DECLARE _f3 BIGINT;
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR SELECT f1, f2, f3 FROM t_select2 WHERE f3 IN (SELECT f1 FROM view308);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
REPEAT
FETCH c INTO _f1, _f2, _f3;
UNTIL done END REPEAT;
CLOSE c;
RETURN _f1;
END//
delimiter ;//

SELECT func10();
SELECT 1;
}


if (1)
{
##### NO CRASH
CREATE OR REPLACE VIEW view8 AS SELECT f1, f2, f3 FROM t_select3 UNION DISTINCT SELECT 1 AS f1, 'Schoenenbourg' AS f2, 1 AS f3;
CREATE OR REPLACE VIEW view25 AS SELECT f1, f2, f3 FROM t_select2 WHERE f3 IN (SELECT f1 FROM view8);
# CREATE OR REPLACE VIEW view308 AS SELECT f1, f2, f3 FROM t_select4 NATURAL JOIN view25;

delimiter //;
CREATE FUNCTION func11() RETURNS BIGINT
BEGIN
DECLARE _f1 BIGINT;
DECLARE _f2 VARCHAR(20);
DECLARE _f3 BIGINT;
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR SELECT f1, f2, f3 FROM t_select2 WHERE f3 IN (SELECT f1 FROM t_select4 NATURAL JOIN view25);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
REPEAT
FETCH c INTO _f1, _f2, _f3;
UNTIL done END REPEAT;
CLOSE c;
RETURN _f1;
END//
delimiter ;//

SELECT func11();
SELECT 1;
}

if (0)
{
CREATE OR REPLACE VIEW view8 AS SELECT f1, f2, f3 FROM t_select3 UNION DISTINCT SELECT 1 AS f1, 'Schoenenbourg' AS f2, 1 AS f3;
CREATE OR REPLACE VIEW view25 AS SELECT f1, f2, f3 FROM t_select2 WHERE f3 IN (SELECT f1 FROM view8);
CREATE OR REPLACE VIEW view308 AS SELECT f1, f2, f3 FROM t_select4 NATURAL JOIN view25;

delimiter //;
CREATE FUNCTION func12() RETURNS BIGINT
BEGIN
DECLARE _f1 BIGINT;
DECLARE _f2 VARCHAR(20);
DECLARE _f3 BIGINT;
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR SELECT f1, f2, f3 FROM t_select2 WHERE f3 IN (SELECT f1 FROM view308);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
REPEAT
FETCH c INTO _f1, _f2, _f3;
UNTIL done END REPEAT;
CLOSE c;
RETURN _f1;
END//
delimiter ;//

SELECT func12();
SELECT 1;
}

if (0)
{
CREATE OR REPLACE VIEW view2 AS SELECT 1 AS f1, 'Schoenenbourg' AS f2, 1 AS f3;
CREATE OR REPLACE VIEW view8 AS SELECT f1, f2, f3 FROM t_select3 UNION DISTINCT SELECT f3, f2, f1 FROM view2;
CREATE OR REPLACE VIEW view25 AS SELECT f1, f2, f3 FROM t_select2 WHERE f3 IN (SELECT f1 FROM view8);
CREATE OR REPLACE VIEW view308 AS SELECT f1, f2, f3 FROM t_select4 NATURAL JOIN view25;

delimiter //;
CREATE FUNCTION func13() RETURNS BIGINT
BEGIN
DECLARE _f1 BIGINT;
DECLARE _f2 VARCHAR(20);
DECLARE _f3 BIGINT;
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR SELECT f1, f2, f3 FROM t_select2 WHERE f3 IN (SELECT f1 FROM view308);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
REPEAT
FETCH c INTO _f1, _f2, _f3;
UNTIL done END REPEAT;
CLOSE c;
RETURN _f1;
END//
delimiter ;//

SELECT func13();
SELECT 1;
}

if (0)
{
# The original statement sequence
CREATE OR REPLACE VIEW view2 AS SELECT 1 AS f1, 'Schoenenbourg' AS f2, 1 AS f3;
CREATE OR REPLACE VIEW view8 AS SELECT f1, f2, f3 FROM t_select3 UNION DISTINCT SELECT f3, f2, f1 FROM view2;
CREATE OR REPLACE VIEW view25 AS SELECT f1, f2, f3 FROM t_select2 WHERE f3 IN (SELECT f1 FROM view8);
CREATE OR REPLACE VIEW view308 AS SELECT f1, f2, f3 FROM t_select4 NATURAL JOIN view25;

delimiter //;
CREATE FUNCTION func14() RETURNS BIGINT
BEGIN
DECLARE _f1 BIGINT;
DECLARE _f2 VARCHAR(20);
DECLARE _f3 BIGINT;
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR SELECT f1, f2, f3 FROM t_select2 WHERE f3 IN (SELECT f1 FROM view308);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
REPEAT
FETCH c INTO _f1, _f2, _f3;
UNTIL done END REPEAT;
CLOSE c;
RETURN _f1;
END//
delimiter ;//

SELECT func14() INTO @my_val FROM t_select2;
SELECT 1;
}


# Cleanup
DROP DATABASE mysqltest_db1;