All Forums Analytics
teranerd 7 posts Joined 05/10
30 Jun 2010
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 ('')

Now i am trying to calculate
select hashrow(rowhash_col) from rowhash_my
union all
select hashrow(cast('' 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?

CarlosAL 512 posts Joined 04/08
01 Jul 2010

Please put the REAL bteq output of the execution.

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



dnoeth 4628 posts Joined 11/04
02 Jul 2010

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

select hashrow(translate('' using unicode_to_latin))



teranerd 7 posts Joined 05/10
06 Jul 2010

Thanks Dieter. Got it

rupert160 131 posts Joined 09/10
07 Aug 2012

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?

'Hello' a_unicode
,'aaaaaaaaaaaaaaaa' a_16bytes
,'aaaaaaaaaaaaaaaaa' a_17bytes
FROM dbc.tables

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
rupert160 131 posts Joined 09/10
07 Aug 2012

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
ToddAWalter 316 posts Joined 10/11
07 Aug 2012

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.

rupert160 131 posts Joined 09/10
07 Aug 2012

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 2 posts Joined 11/12
30 Nov 2012

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.

Fred 1096 posts Joined 08/04
02 Dec 2012

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.

You must sign in to leave a comment.