Options

# Double NATURAL JOIN in pre-join projection does not work

CREATE TABLE a (
common1 INT NOT NULL,
common2 INT NOT NULL,
common3 INT NOT NULL,

PRIMARY KEY (common1, common2, common3)
);

CREATE TABLE b (
common1 INT NOT NULL,
common2 INT NOT NULL,
common3 INT NOT NULL,
common4 INT NOT NULL,
bdata INT NOT NULL,

PRIMARY KEY (common1, common2, common3, common4),
FOREIGN KEY (common1, common2, common3) REFERENCES a (common1, common2, common3)
);

INSERT INTO a VALUES (1,1,1,121);
INSERT INTO a VALUES (1,1,2,122);
INSERT INTO a VALUES (1,1,3,123);
INSERT INTO b VALUES (1,1,1,1,221);
INSERT INTO b VALUES (1,1,2,1,222);
INSERT INTO b VALUES (1,1,3,1,223);

CREATE TABLE c (
common1 INT NOT NULL,
common2 INT NOT NULL,
common3 INT NOT NULL,
common4 INT NOT NULL,
common5 INT NOT NULL,
cdata INT NOT NULL,

PRIMARY KEY (common1, common2, common3, common4, common5),
FOREIGN KEY (common1, common2, common3, common4) REFERENCES b (common1, common2, common3, common4),
FOREIGN KEY (common1, common2, common3) REFERENCES a (common1, common2, common3)
);

// This succeeds, however we have duplicated columns in the projection
CREATE PROJECTION c_super1 AS
SELECT * FROM c
JOIN b ON (c.common1, c.common2, c.common3, c.common4) = (b.common1, b.common2,b.common3, b.common4)
JOIN a ON (c.common1, c.common2, c.common3) = (a.common1, a.common2, a.common3)
ORDER BY c.common1, c.common2, c.common3;

// Check columns in projection
verticatest=> select * from c_super1;
common1 | common2 | common3 | common4 | common5 | cdata | common1_1 | common2_1 | common3_1 | common4_1 | bdata | common1_2 | common2_2 | common3_2 | adata
---------+---------+---------+---------+---------+-------+-----------+-----------+-----------+-----------+-------+-----------+-----------+-----------+-------
(0 rows)

// This also succeeds, and because of NATURAL JOIN we don't have duplicated common columns from table b
// However, we still have duplicated columns from table a
CREATE PROJECTION c_super2 AS
SELECT * FROM c
NATURAL JOIN b
JOIN a ON (c.common1, c.common2, c.common3) = (a.common1, a.common2, a.common3)
ORDER BY c.common1, c.common2, c.common3;

// Check columns in projection
verticatest=> select * from c_super2;
common1 | common2 | common3 | common4 | common5 | cdata | bdata | common1_1 | common2_1 | common3_1 | adata
---------+---------+---------+---------+---------+-------+-------+-----------+-----------+-----------+-------
(0 rows)

// But this does not succeed !
// ERROR 3818: JOIN qualifications to not refer to the correct relation(s)
CREATE PROJECTION c_super3 AS
SELECT * FROM c
NATURAL JOIN b
NATURAL JOIN a
ORDER BY c.common1, c.common2, c.common3;

// However, doing just a "SELECT * FROM c NATURAL JOIN b NATURAL JOIN a;" succeeds
// As we can see, there are no duplicate columns here

verticatest=> SELECT * FROM c NATURAL JOIN b NATURAL JOIN a;
common1 | common2 | common3 | common4 | common5 | cdata | bdata | adata
---------+---------+---------+---------+---------+-------+-------+-------
(0 rows)