--disable_abort_on_error

--disable_warnings
DROP TABLE IF EXISTS t1, t2, t4;
DROP VIEW  IF EXISTS v1, v2;
--enable_warnings

CREATE TABLE t1 ( COSTCODE INT, COSTTEXT VARCHAR (50) );
INSERT INTO t1 VALUES(1       ,'my_costtext');

CREATE TABLE t2 ( CONDCODE INT, CONDTEXT VARCHAR (50) );
INSERT INTO t2 VALUES(1       ,'my_condtext');

CREATE TABLE t4 ( COSTCODE INT , CONDCODE INT );
INSERT INTO t4 VALUES(1        ,1);

# select to show, that the join basically works
SELECT CONDTEXT, COSTTEXT
FROM t4 NATURAL JOIN t1
        NATURAL JOIN t2;

# Create the problematic VIEW
CREATE VIEW v1 AS
SELECT CONDTEXT, COSTTEXT
FROM t4 NATURAL JOIN t1
        NATURAL JOIN t2;

# Statements showing the problem
SELECT * FROM v1;
SHOW CREATE VIEW v1;


# Simplified VIEW definition, where all is OK
CREATE VIEW v2 AS
SELECT COSTTEXT
FROM t4 NATURAL JOIN t1;
SELECT * FROM v2;
SHOW CREATE VIEW v2;

##### The slightly modified original NIST test case
#     dml134

--disable_warnings
DROP TABLE IF EXISTS t1, t2, t3, t4;
DROP VIEW  IF EXISTS v1, v2, v3;
--enable_warnings

   CREATE TABLE t1 (
  COSTCODE INT UNIQUE,
  COSTTEXT VARCHAR (50) NOT NULL);

   CREATE TABLE t2 (
  CONDCODE INT UNIQUE,
  CONDTEXT VARCHAR (50) NOT NULL);

   CREATE TABLE t3 (
  ITEMCODE INT PRIMARY KEY,
  ITEMTEXT VARCHAR (50) NOT NULL);

   CREATE TABLE t4 (
  COSTCODE INT REFERENCES t1 (COSTCODE),
  CONDCODE INT REFERENCES t2 (CONDCODE),
  ITEMCODE INT REFERENCES t3);

   CREATE VIEW v1 AS
  SELECT ITEMTEXT, CONDTEXT, COSTTEXT
    FROM t4 NATURAL JOIN t1
                   NATURAL JOIN t2
                   NATURAL JOIN t3;

   CREATE VIEW v2 AS
  SELECT ITEMTEXT, CONDTEXT, COSTTEXT
    FROM t4, t1, t2, t3
      WHERE t4.ITEMCODE = t3.ITEMCODE
        AND ((t4.CONDCODE = t2.CONDCODE
              AND t4.COSTCODE IS NULL
              AND t1.COSTCODE IS NULL)
          OR (t4.COSTCODE = t1.COSTCODE
              AND t4.CONDCODE IS NULL
              AND t2.CONDCODE IS NULL));

  CREATE VIEW v3 AS
  SELECT * FROM v1 UNION SELECT * FROM v2;