Be careful when comparing hashed column values from different tables where the columns have different data types and sizes. They may not have the same hashed value.
Example:
dbadmin=> CREATE TABLE a (c INT);
CREATE TABLE
dbadmin=> INSERT INTO a SELECT 1;
OUTPUT
--------
1
(1 row)
dbadmin=> CREATE TABLE b ( c NUMERIC(10, 1));
CREATE TABLE
dbadmin=> INSERT INTO b SELECT 1;
OUTPUT
--------
1
(1 row)
dbadmin=> SELECT COUNT(*) FROM a JOIN b ON b.c = a.c;
COUNT
-------
1
(1 row)
dbadmin=> SELECT COUNT(*) FROM a JOIN b ON HASH(b.c) = HASH(a.c);
COUNT
-------
0
(1 row)
dbadmin=> \x
Expanded display is on.
dbadmin=> SELECT HASH(12345::INT) "12345::INT",
dbadmin-> HASH(12345::VARCHAR(10)) "12345::VARCHAR(10)",
dbadmin-> HASH(12345::VARCHAR(100)) "12345::VARCHAR(100)",
dbadmin-> HASH(12345::NUMERIC(100,0)) "12345::NUMERIC(100,0)",
dbadmin-> HASH(12345::NUMERIC(100,5)) "12345::NUMERIC(100,5)",
dbadmin-> HASH(12345::NUMERIC(100,10)) "12345::NUMERIC(100,10)",
dbadmin-> HASH(12345::FLOAT) "12345::FLOAT";
-[ RECORD 1 ]----------+--------------------
12345::INT | 4960063907862199528
12345::VARCHAR(10) | 7521734626139061666
12345::VARCHAR(100) | 7521734626139061666
12345::NUMERIC(100,0) | 3327062938535469805
12345::NUMERIC(100,5) | 6342074168025654472
12345::NUMERIC(100,10) | 560125968008200468
12345::FLOAT | 4397895841065323663
Helpful Link:
https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/HASH.htm
Have fun!
About the Author
James Knicely
Vertica Field Chief Technologist
I've had the privilege of working with many database technologies in my career. But after being introduced to Vertica in May of 2011 as a client, I was hooked on the new technology after witnessing a query run in milliseconds that had previously ran for hours on the legacy database we had in place. It was then that I knew I wanted to eventually join the Vertica team, and 4 years later I did! I am currently a Vertica evangelist and am ready to help you get on board! Please feel free to reach out to me with any questions you have about Vertica and make sure to follow my Vertica Quick Tips!
