#
# repro for problem seen in upgrade testing of minor 5.1 releases (5.2.24 to 5.2.25-rc)
#

--echo connecting as 'root';
connect (root, localhost, root,,);
--echo connected;
#####################################
--echo SETTING UP FOR VIEW TESTS
#####################################

CREATE DATABASE viewdb;
use viewdb;

# create and connect to users
GRANT CREATE VIEW, SELECT ON viewdb.* TO
        'view_create'@'localhost' IDENTIFIED BY 'view_create';
GRANT SELECT ON viewdb.* TO 'view_select'@'localhost'
                IDENTIFIED BY 'view_select';
GRANT SELECT, INSERT ON viewdb.* TO 'view_insert'@'localhost'
                IDENTIFIED BY 'view_insert';

connect (view_create, localhost, view_create, view_create,viewdb);
connect (view_select, localhost, view_select, view_select,viewdb);
connect (view_insert, localhost, view_insert, view_insert,viewdb);

######## now work on new stuff ##########
connection root;
CREATE TABLE upgrade_t8 (a INT, b INT, c INT, PRIMARY KEY(a,b));
INSERT INTO upgrade_t8 VALUES (10,2,-1), (20,3,-2),
                              (30,4,-3), (40,5,-4),
                              (50,10,-5);


SELECT * FROM upgrade_t8;

DELETE FROM upgrade_t8;
INSERT INTO upgrade_t8 VALUES (10,2,-1), (20,3,-2);

--echo connecting to user 'view_create'
connection view_create;
--echo connected
CREATE SQL SECURITY INVOKER VIEW upgrade_v31 (x,y,z)
                 AS SELECT c, b, a FROM upgrade_t8;
CREATE SQL SECURITY INVOKER VIEW upgrade_v32 (x,y)
                 AS SELECT b, a FROM upgrade_t8;
CREATE SQL SECURITY INVOKER VIEW upgrade_v33 (x,y,z)
                 AS SELECT b, a, b FROM upgrade_t8;
CREATE SQL SECURITY INVOKER VIEW upgrade_v34 (x,y,z)
                 AS SELECT c+1, b, a FROM upgrade_t8;
CREATE ALGORITHM=TEMPTABLE SQL SECURITY INVOKER VIEW upgrade_v35 (x,y,z)
                 AS SELECT c, b, a FROM upgrade_t8;

--echo connecting to user 'view_insert'
connection view_insert;
--echo connected
# try insert to VIEW with fields duplicate
-- error 1471
INSERT INTO upgrade_v33 VALUES (-60,4,30);
# try insert to VIEW with expression in SELECT list
-- error 1471
INSERT INTO upgrade_v34 VALUES (-60,4,30);
# try insert to VIEW using temporary table algorithm
-- error 1471
INSERT INTO upgrade_v35 VALUES (-60,4,30);
INSERT INTO upgrade_v31 VALUES (-60,4,30);
INSERT INTO upgrade_v31 (z,y,x) VALUES (50,6,-100);
INSERT INTO upgrade_v32 VALUES (5,40);

--echo connecting to user 'view_select'
connection view_select;
--echo connected
SELECT * FROM upgrade_t8;