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

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

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.

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.

Vertica 6.1.1.

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

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 ;

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.

Tables t1 and t2 are the same table.

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.

Thank you.