Epoch column works in "join...on", but not in "join...using()". Why?

Jack_Kidwell

Using the hidden "epoch" column this works: select * from table1 t1 join table2 t2 on t1.epoch = t2.epoch and t1.device_id = t2.device_id This throws an error message about "epoch not in table1": select * from table1 t1 join table2 t2 using( epoch, device_id ) Why is it an error?

[Deleted User]

Hi Jack, thanks for your question! We'll look into this and help you find a way to execute this command without an error.

Bhawana

Hi Jack, The column name"epoch" is reserved. Could you please try using different column name instead of epoch. May I please know what version of vertica you are running.

Jack_Kidwell

Right. I need to use the hidden "epoch" column. As mentioned, the hidden epoch column can be used for joins in "on" clauses, but not in "using" clauses.

Jack_Kidwell

Vertica 6.1.1.

Bhawana

Jack, Can we get the DDL of those two tables used in the query. Once I have a DDL I can reproduce it here. Thanks, Bhawana

Jack_Kidwell

CREATE TABLE nt_netinf_cmts_data ( device_id INTEGER, interface_id INTEGER, metric_id INTEGER, timestamp INTEGER NOT NULL, value FLOAT ) PARTITION BY timestamp / 604800 ; CREATE PROJECTION nt_netinf_cmts_data_P00 ( device_id ENCODING RLE, interface_id ENCODING RLE, metric_id ENCODING RLE, timestamp ENCODING COMMONDELTA_COMP, value ) AS SELECT device_id , interface_id , metric_id, timestamp, value FROM nt_netinf_cmts_data ORDER BY device_id , interface_id , metric_id, timestamp SEGMENTED BY HASH(device_id , interface_id ) ALL NODES; CREATE PROJECTION nt_netinf_cmts_data_P01 ( device_id ENCODING RLE, interface_id ENCODING RLE, metric_id ENCODING RLE, timestamp ENCODING COMMONDELTA_COMP, value ) AS SELECT device_id , interface_id , metric_id, timestamp, value FROM nt_netinf_cmts_data ORDER BY device_id , interface_id , metric_id, timestamp SEGMENTED BY HASH(device_id , interface_id ) ALL NODES OFFSET 1 ;

Jack_Kidwell

I think i understand why it is syntactically correct in "join...on" and not "join...using": The "on" variant requires an explicit table name while the "using" variant does not allow table names. The hidden epoch column can only be accessed by fully qualified name. This odd usage comes about in an effort to de-duplicate rows. In the interest of speed, new rows are inserted without regard to existing rows, causing some duplicate rows. We use the hidden epoch column for de-duplication.

Jack_Kidwell

Tables t1 and t2 are the same table.

Bhawana

Jack, Hidden epoch column is an undocumented columns, so it is not supported that it works correctly in all cases.But there is no issues with USING clause as such. It works fine with any kind of user defined columns.

Jack_Kidwell

Thank you.