Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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, 
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)

Comments

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.