--disable_abort_on_error

--disable_warnings
DROP TABLE IF EXISTS t1,t2,t3,t4;
--enable_warnings


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

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

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

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

INSERT INTO t2 VALUES ( NULL, 'No cost code assigned');
INSERT INTO t2 VALUES ( 0,    'Expensive');
INSERT INTO t2 VALUES ( 1,    'Absurdly expensive');
INSERT INTO t2 VALUES ( 2,    'Outrageously expensive');
INSERT INTO t2 VALUES ( 3,    'Robbery; a complete and total rip-off');
INSERT INTO t3 VALUES ( NULL, 'Unknown');
INSERT INTO t3 VALUES ( 1,    'Slightly used');
INSERT INTO t3 VALUES ( 2,    'Returned as defective');
INSERT INTO t3 VALUES ( 3,    'Visibly damaged (no returns)');
INSERT INTO t4 VALUES ( 1,    'Lousy excuse for a tape deck');
INSERT INTO t4 VALUES ( 3,    'World''s worst VCR');
INSERT INTO t4 VALUES ( 4,    'Irreparable intermittent CD player');
INSERT INTO t4 VALUES ( 7,    'Self-destruct VGA monitor w/ critical need detect');
INSERT INTO t1 VALUES (3, NULL, 4);
INSERT INTO t1 VALUES (1, 2, 3);
INSERT INTO t1 VALUES (2, 3, 7);
INSERT INTO t1 VALUES (0, 3, 1);
INSERT INTO t1 VALUES (3, 1, 7);

#### appearances of the bug
# !!!! wrong error message
# ERROR 42S22: Unknown column 'test.t2.CONDCODE' in 'on clause'
SELECT count(*)
FROM t1 
  INNER JOIN t2 USING(COSTCODE)
  INNER JOIN t3 USING(CONDCODE);

# !!!! wrong result  20
SELECT count(*)
FROM t1 
  NATURAL JOIN t2
  NATURAL JOIN t3;

# The expected result for comparison  4
SELECT count(*)
FROM t1
  INNER JOIN t2
  INNER JOIN t3
WHERE t1.COSTCODE = t2.COSTCODE
  AND t1.CONDCODE = t3.CONDCODE;


#### Some variations
#-----------------------------
# JOIN of two tables

SELECT count(*)
FROM t1 
  NATURAL JOIN t2;
SELECT count(*)
FROM t1 
  INNER JOIN t2 USING(COSTCODE);
SELECT count(*)
FROM t1
  INNER JOIN t2
WHERE t1.COSTCODE = t2.COSTCODE;

SELECT count(*)
FROM t1 
  NATURAL JOIN t3;
SELECT count(*)
FROM t1 
  INNER JOIN t3 USING(CONDCODE);
SELECT count(*)
FROM t1
  INNER JOIN t3
WHERE t1.CONDCODE = t3.CONDCODE;

SELECT count(*)
FROM t1 
  NATURAL JOIN t4;
SELECT count(*)
FROM t1 
  INNER JOIN t4 USING(ITEMCODE);
SELECT count(*)
FROM t1
  INNER JOIN t4
WHERE t1.ITEMCODE = t4.ITEMCODE;

#-----------------------------
# JOIN of three tables

# !!!! wrong result !!!!
SELECT count(*)
FROM t1 
  NATURAL JOIN t2
  NATURAL JOIN t3;
# !!!! wrong error message !!!!
SELECT count(*)
FROM t1 
  INNER JOIN t2 USING(COSTCODE)
  INNER JOIN t3 USING(CONDCODE);
SELECT count(*)
FROM t1
  INNER JOIN t2
  INNER JOIN t3
WHERE t1.COSTCODE = t2.COSTCODE
  AND t1.CONDCODE = t3.CONDCODE;


# !!!! wrong result !!!!
SELECT count(*)
FROM t1 
  NATURAL JOIN t2
  NATURAL JOIN t4;
SELECT count(*)
FROM t1 
  INNER JOIN t2 USING(COSTCODE)
  INNER JOIN t4 USING(ITEMCODE);
SELECT count(*)
FROM t1
  INNER JOIN t2
  INNER JOIN t4
WHERE t1.COSTCODE = t2.COSTCODE
  AND t1.ITEMCODE = t4.ITEMCODE;


# !!!! wrong result !!!!
SELECT count(*)
FROM t1 
  NATURAL JOIN t3
  NATURAL JOIN t4;
# !!!! wrong error message !!!!
SELECT count(*)
FROM t1 
  INNER JOIN t3 USING(CONDCODE)
  INNER JOIN t4 USING(ITEMCODE);
SELECT count(*)
FROM t1
  INNER JOIN t3
  INNER JOIN t4
WHERE 
      t1.CONDCODE = t3.CONDCODE
  AND t1.ITEMCODE = t4.ITEMCODE;


#-----------------------------

# nearly the original NIST test case

# !!!! wrong result !!!!
# NIST expects 4
SELECT count(*)
FROM t1 
  NATURAL JOIN t2
  NATURAL JOIN t3
  NATURAL JOIN t4;

# !!!! wrong error message !!!!
SELECT count(*)
FROM t1 
  INNER JOIN t2 USING(COSTCODE)
  INNER JOIN t3 USING(CONDCODE)
  INNER JOIN t4 USING(ITEMCODE);

SELECT count(*)
FROM t1
  INNER JOIN t2
  INNER JOIN t3
  INNER JOIN t4
WHERE t1.COSTCODE = t2.COSTCODE
  AND t1.CONDCODE = t3.CONDCODE
  AND t1.ITEMCODE = t4.ITEMCODE;