# HU within dml060

set sql_mode= 'traditional';

SET AUTOCOMMIT= 0;

--disable_abort_on_error

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

CREATE TABLE t1
    (EMPNUM   CHAR(3) NOT NULL,
    PNUM     CHAR(3) NOT NULL,
    UNIQUE(PNUM))
    ENGINE= InnoDB;

INSERT INTO t1 VALUES  ('E3','P2');

COMMIT WORK;

INSERT INTO t1 VALUES('E3','P4');

SELECT * FROM t1 WHERE EMPNUM = 'E3';
-- PASS:0266 If 2 rows selected and PNUM values are 'P2' and 'P4'?

-- TEST:0266 Update:searched - UNIQUE violation!
UPDATE t1
SET PNUM = 'P6'
WHERE EMPNUM = 'E3';
-- PASS:0266 If ERROR, unique constraint, 0 rows updated?

# Here comes a wrong result !!
SELECT * FROM t1 WHERE EMPNUM = 'E3';
-- PASS:0266 If 2 rows selected and PNUM values are 'P2' and 'P4'?

ROLLBACK WORK;

############################################################

# The slightly modified NIST test case
--disable_warnings
DROP VIEW IF EXISTS v1;
DROP TABLE IF EXISTS t1;
--enable_warnings

  CREATE TABLE t1
   (EMPNUM   CHAR(3) NOT NULL,
    PNUM     CHAR(3) NOT NULL,
    HOURS    DECIMAL(5),
    UNIQUE(EMPNUM,PNUM))
    ENGINE= InnoDB;


  CREATE VIEW v1 (EMPNUM,PNUM,HOURS)
     AS SELECT EMPNUM,PNUM,HOURS
        FROM   t1
        WHERE  EMPNUM='E3'
     WITH CHECK OPTION;


      DELETE FROM t1;

      INSERT INTO t1 VALUES  ('E1','P1',40);
      INSERT INTO t1 VALUES  ('E1','P2',20);
      INSERT INTO t1 VALUES  ('E1','P3',80);
      INSERT INTO t1 VALUES  ('E1','P4',20);
      INSERT INTO t1 VALUES  ('E1','P5',12);
      INSERT INTO t1 VALUES  ('E1','P6',12);
      INSERT INTO t1 VALUES  ('E2','P1',40);
      INSERT INTO t1 VALUES  ('E2','P2',80);
      INSERT INTO t1 VALUES  ('E3','P2',20);
      INSERT INTO t1 VALUES  ('E4','P2',20);
      INSERT INTO t1 VALUES  ('E4','P4',40);
      INSERT INTO t1 VALUES  ('E4','P5',80);

      COMMIT WORK;

-- TEST:0266 Update:searched - UNIQUE violation under view!

-- setup
--enable_info
     INSERT INTO t1
            VALUES('E3','P4',50);
-- PASS:0266 If 1 row is inserted?
--disable_info

     SELECT EMPNUM, PNUM, HOURS
          FROM v1;
-- PASS:0266 If 2 rows are selected?

     SELECT * FROM t1 WHERE EMPNUM = 'E3';
-- PASS:0266 If 2 rows selected and PNUM values are 'P2' and 'P4'?

--enable_info
     UPDATE v1
          SET PNUM = 'P6'
          WHERE EMPNUM = 'E3';
--disable_info
-- PASS:0266 If ERROR, unique constraint, 0 rows updated?

     SELECT EMPNUM, PNUM, HOURS
          FROM v1;
-- PASS:0266 If 2 rows are selected?

     SELECT * FROM t1 WHERE EMPNUM = 'E3';
-- PASS:0266 If 2 rows selected and PNUM values are 'P2' and 'P4'?

-- restore
     ROLLBACK WORK;