
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!