###### nist1-mysql.test ######

--disable_abort_on_error

# 1. extreme simplified NIST test case

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP VIEW  IF EXISTS v1;
--enable_warnings

  CREATE TABLE t1 ( col1 CHAR );

  # 1. The check option is needed
  # 2. The <cond1> AND/OR <cond2> is needed
  #      ! AND is better than OR, because both conditions
  #      ! must be evaluated (The optimizer may change in future)
  # 3. It does not play any role if the complete check gives
  #    true or false
  # 4. The non prepared version does not crash the server
  CREATE VIEW v1 AS
  SELECT col1
  FROM t1
  WHERE 1 AND 1
  WITH CHECK OPTION;

  # nonprepared version INSERT INTO v1 VALUES('A');

  PREPARE stmt1 FROM "INSERT INTO v1 VALUES('A')" ;

  # This execute crashes the server !!
   EXECUTE stmt1;

# 2. full NIST test case (the names are modified)
# Please uncomment the next line
# let $full_test= 1;
while ($full_test)
{
  DROP TABLE t1;
  DROP VIEW  v1;

  # CREATION of tables within 'schema/schema8'
        CREATE TABLE t1 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4) CHECK (GRADE > 0 AND GRADE < 20),
                CITY   CHAR(15));

        CREATE VIEW v1 AS
                          SELECT EMPNUM,EMPNAME,GRADE,CITY
                          FROM t1
                          WHERE GRADE > 0 AND GRADE < 20
                          WITH CHECK OPTION;

  # TEST within 'sql/cdr027', sub test case 0447

  # The original NIST statement
   INSERT INTO v1
       VALUES('X1','Vicki',NULL,'Houston');
  # NIST expects
-- PASS:0447 If ERROR, view check constraint, 0 rows inserted?

  # my PS version
  prepare stmt1 from "INSERT INTO v1
       VALUES('X1','Vicki',NULL,'Houston')" ;
  # The execute crashes the server
  # execute stmt1;

  let $full_test= 0;
}