Double NATURAL JOIN in pre-join projection does not work

Grega_Kešpret

CREATE TABLE a ( 
common1 INT NOT NULL, 
common2 INT NOT NULL, 
common3 INT NOT NULL, 
adata 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)

Grega_Kešpret

Hi Amelia,
any news about that?

Dhanesh_Padmana

I have a similar problem. I have tried pre-join projections for joins of two tables, but do not know how we go about optimizing joins of 3 tables.

Dhanesh_Padmana_1

I was able to do optimization of joins of 3 tables following Grega's approach using inner joins. Thanks.