hashrow function

Analytics
Enthusiast

hashrow function

I did this

create volatile table rowhash_my,no log
(
rowhash_col varchar(20)
)
on commit preserve rows

insert into rowhash_my values ('1234_#abcd@gmail.com')

Now i am trying to calculate
select hashrow(rowhash_col) from rowhash_my
union all
select hashrow(cast('1234_#abcd@gmail.com' as varchar(20)) from rowhash_my;

my assumption was that both the above select's should return same value but the above query returns two different values. can some please explain why?
Tags (1)
9 REPLIES
Junior Supporter

Re: hashrow function

Please put the REAL bteq output of the execution.

There is a missing parenthesis ')' in the second select. Your statement won't run.

Cheers.

Carlos.
Senior Apprentice

Re: hashrow function

Do a SHOW TABLE and check the character set.
It's LATIN whereas any literal is always UNICODE.

select hashrow(translate('1234_#abcd@gmail.com' using unicode_to_latin))

Dieter
Enthusiast

Re: hashrow function

Thanks Dieter. Got it
Enthusiast

Re: hashrow function

I have a feeling the HASHROW function has changed within the last few versions I'm using 13.10 and now:

  • UNICODE and LATIN values hasH to the same value
  • it will provide varying values for seed values greater than 16 bytes.

This used not to be the case, can somebody tell me when this HASHROW funciton was updated?

CREATE VOLATILE TABLE t1 AS(
SELECT DISTINCT
'Hello' a_unicode
,TRANSLATE('Hello' USING UNICODE_TO_LATIN) a_latin
,'aaaaaaaaaaaaaaaa' a_16bytes
,'aaaaaaaaaaaaaaaaa' a_17bytes
FROM dbc.tables
)WITH DATA
ON COMMIT PRESERVE ROWS;

SELECT HASHROW(a_unicode),HASHROW(a_latin),HASHROW(a_16bytes),HASHROW(a_17bytes) FROM t1;
/*
HASHROW(a_unicode) HASHROW(a_latin) HASHROW(a_16bytes) HASHROW(a_17bytes)
C6-9F-1A-33 C6-9F-1A-33 00-1E-43-58 1F-A2-E2-35
*/

Enthusiast

Re: hashrow function

Addendum: I ran the above statements by teranerd and got the same row hash values actually even the following gave the same:

SELECT HASHROW(rowhash_col) FROM rowhash_my
UNION ALL
SELECT HASHROW(TRANSLATE('1234_#abcd@gmail.com' USING UNICODE_TO_LATIN)) FROM rowhash_my;

Teradata Employee

Re: hashrow function

On 13.10 that has been newly installed via Sysinit, there is a new hashing algorithm that changes the result from HashRow. The changes affect Unicode data the most but also affect the result from Latin characters and other types.
Enthusiast

Re: hashrow function

Thanks Todd,

I'll assume that because your "HASHROW" function is proprietary, the properties of it are not able to be discussed right? I mean specifically the relationship of exactly what types respond differently vs similarly? I'll just trial and error some analysis....

Kind Regards,

JPH
Fan

Re: hashrow function

How often has the hashing function been updated?  With the new one implemented in 13.10 should we expect it to remain consistent for the long term? 

I've selected variables as my PI based on their hash distribution and am wondering if I need to check this at every update or just once in a long while.

-Justin

Teradata Employee

Re: hashrow function

The hash function seldom changes, and will be clearly documented in the Release Summary. To rephrase Todd's answer, an in-place upgrade to a new release will not change the hash function. In general a new hash function can be implemented only by completely reinitializing the filesystem.

Think of it like reformatting your hard drive and doing a "clean install" of a new OS on your PC. You then have to do a complete restore of all the data from backup and reinstall all applications (SPs, UDFs). So in many cases, people will opt to wait until they have a brand new system and are copying everything over anyway.