# Test derived from the NIST tests
# SCHEMA+USER names are changed HU --> mysqltest1, FLATER --> mysqltest2
--disable_abort_on_error

#### Heavy simplified testcase
let $new_dba= mysqltest1;
--error 0,ER_CANNOT_USER
eval DROP USER $new_dba@localhost;
eval CREATE USER $new_dba@localhost identified by 'PWD';
--disable_warnings
eval DROP SCHEMA IF EXISTS $new_dba;
--enable_warnings
eval CREATE SCHEMA $new_dba;
eval grant ALL ON $new_dba.* to $new_dba@localhost with grant option;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (mysqltest1,localhost,mysqltest1,PWD,test);
connection default;

let $new_dba= mysqltest2;
--error 0,ER_CANNOT_USER
eval DROP USER $new_dba@localhost;
eval CREATE USER $new_dba@localhost identified by 'PWD';
--disable_warnings
eval DROP SCHEMA IF EXISTS $new_dba;
--enable_warnings
eval CREATE SCHEMA $new_dba;
eval grant ALL ON $new_dba.* to $new_dba@localhost with grant option;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (mysqltest2,localhost,mysqltest2,PWD,test);
connection default;

connection mysqltest1 ;
USE mysqltest1 ;

CREATE TABLE STAFF3 (EMPNUM    CHAR(3) NOT NULL,
              EMPNAME  CHAR(20),
              GRADE DECIMAL(4),
              CITY   CHAR(15));
CREATE VIEW VSTAFF3 AS SELECT * FROM STAFF3;

GRANT SELECT,UPDATE(EMPNUM,EMPNAME) ON STAFF3
           TO mysqltest2@localhost
           WITH GRANT OPTION;
GRANT SELECT,UPDATE(EMPNUM,EMPNAME) ON VSTAFF3
    TO mysqltest2@localhost WITH GRANT OPTION;

connection mysqltest2 ;
USE mysqltest2 ;
CREATE TABLE BASE_WCOV (C1 INT);

let $my_stmt= SELECT COUNT(*) FROM
mysqltest1.VSTAFF3 T09, BASE_WCOV T10;
eval $my_stmt ;
eval prepare stmt1 from "$my_stmt" ;
execute stmt1;
--echo # Attention: We use STAFF3 instead of CREATE VIEW VSTAFF3 AS SELECT * FROM STAFF3;
let $my_stmt= SELECT COUNT(*) FROM
mysqltest1.STAFF3 T09, BASE_WCOV T10;
eval $my_stmt ;
eval prepare stmt1 from "$my_stmt" ;
execute stmt1;

# Cleanup
connection default;
disconnect mysqltest1;
DROP USER mysqltest1@localhost;
DROP SCHEMA mysqltest1;
disconnect mysqltest2;
DROP USER mysqltest2@localhost;
DROP SCHEMA mysqltest2;

################################## The original testcase ###########################################


let $new_dba= mysqltest1;
--error 0,ER_CANNOT_USER
eval DROP USER $new_dba@localhost;
eval CREATE USER $new_dba@localhost identified by 'PWD';
--disable_warnings
eval DROP SCHEMA IF EXISTS $new_dba;
--enable_warnings
eval CREATE SCHEMA $new_dba;
eval grant ALL ON $new_dba.* to $new_dba@localhost with grant option;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (mysqltest1,localhost,mysqltest1,PWD,test);
connection default;

let $new_dba= mysqltest2;
--error 0,ER_CANNOT_USER
eval DROP USER $new_dba@localhost;
eval CREATE USER $new_dba@localhost identified by 'PWD';
--disable_warnings
eval DROP SCHEMA IF EXISTS $new_dba;
--enable_warnings
eval CREATE SCHEMA $new_dba;
eval grant ALL ON $new_dba.* to $new_dba@localhost with grant option;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (mysqltest2,localhost,mysqltest2,PWD,test);
connection default;

--disable_abort_on_error

#       USE mysqltest1 ;
#--source suite/nist/t/schema/schema1.mysql
connection mysqltest1 ;


  USE mysqltest1 ;

  CREATE TABLE STAFF
   (EMPNUM   CHAR(3) NOT NULL UNIQUE,
    EMPNAME  CHAR(20),
    GRADE    DECIMAL(4),
    CITY     CHAR(15));


  CREATE TABLE PROJ
   (PNUM     CHAR(3) NOT NULL UNIQUE,
    PNAME    CHAR(20),
    PTYPE    CHAR(6),
    BUDGET   DECIMAL(9),
    CITY     CHAR(15));


  CREATE TABLE WORKS
   (EMPNUM   CHAR(3) NOT NULL,
    PNUM     CHAR(3) NOT NULL,
    HOURS    DECIMAL(5),
    UNIQUE(EMPNUM,PNUM));

  CREATE TABLE STAFF3 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15));

   CREATE VIEW VSTAFF3 AS SELECT * FROM STAFF3;

-- ************* grant statements follow *************

   GRANT ALL PRIVILEGES ON STAFF
           TO mysqltest2@localhost;
   GRANT SELECT ON WORKS
           TO mysqltest2@localhost;
   GRANT SELECT ON PROJ
           TO mysqltest2@localhost;

   GRANT SELECT,UPDATE(EMPNUM,EMPNAME) ON STAFF3
           TO mysqltest2@localhost
           WITH GRANT OPTION;

   GRANT SELECT,UPDATE(EMPNUM,EMPNAME) ON VSTAFF3
    TO mysqltest2@localhost WITH GRANT OPTION;

connection default;
#       USE mysqltest2 ;
#--source suite/nist/t/schema/schema5.mysql
connection mysqltest2 ;

      USE mysqltest2 ;

   CREATE TABLE USIG (C1 INT, C_1 INT);
   CREATE TABLE U_SIG (C1 INT, C_1 INT);

   -- For generation of "with check option violation" SQLSTATE.
   CREATE TABLE BASE_WCOV (C1 INT);
   CREATE VIEW WCOV AS SELECT * FROM BASE_WCOV WHERE
     C1 > 0 WITH CHECK OPTION;

   CREATE TABLE BASE_VS1 (C1 INT, C2 INT);
   CREATE VIEW VS1 AS SELECT * FROM BASE_VS1 WHERE C1 = 0;
   CREATE VIEW VS2 AS
     SELECT A.C1 FROM BASE_VS1 A WHERE EXISTS
       (SELECT B.C2 FROM BASE_VS1 B WHERE B.C2 = A.C1);


# use mysqltest2 ;
# --source suite/nist/t/sql/dml132.mysql

-- TEST:0525 FIPS sizing:  15 Table references in SQL statement!

  # Bug#7423 VIEWs defined as SELECT with corelated SUBQUERY are sometimes buggy
  let $my_stmt= SELECT COUNT(*) FROM
  mysqltest1.WORKS T01, mysqltest1.PROJ T02, mysqltest1.STAFF T03,
  USIG T04, U_SIG T05, BASE_VS1 T06, VS1 T07,
  VS2 T08, mysqltest1.VSTAFF3 T09, BASE_WCOV T10
  WHERE T03.EMPNUM > 'E1';
  eval $my_stmt ;
  eval prepare stmt1 from "$my_stmt" ;
  execute stmt1;

# Cleanup
connection default;
disconnect mysqltest1;
DROP USER mysqltest1@localhost;
DROP SCHEMA mysqltest1;
disconnect mysqltest2;
DROP USER mysqltest2@localhost;
DROP SCHEMA mysqltest2;