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

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

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?

Comments

  • 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.

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.