HASHBYTES giving different resaults than yesterday

Question

HASHBYTES giving different resaults than yesterday

astrid 69000

Hall of Fame

Points: 3208
More actions
June 8, 2021 at 12:54 pm

#3893370
Good morning,

Did anything change with the HASHBYTES on sql 2017?
```
CONVERT(CHAR(40), HASHBYTES('SHA1', LTRIM(RTRIM(COALESCE(CURRENCY_ID,''))) 
+ ';' + LTRIM(RTRIM(COALESCE(ID,'')))  
), 2)
```
we run this daily and save it on a table, and today it is giving a different result.
any ideas why?
for example, yesterday we got 5BABA83ED92E292DEE74DFD7CC6A88F9F882601F and today, same query, same numbers, we got 74A3F106336CD7902029AE252EF2D55EB5D8DD18

thanks!!!

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

I don't know of any such change. I'm thinking that there would be a million person march on Redmond if there were. Have you checked the raw data to see if it changed?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 2

it is weird. I am not sure.

for example the process run last night. the hash that I got from SSIS using the same process we always use that was matching HASHBYTES, for the word "USD" it usually give C0FB8B944A767E1AACE82F930D95E69ED912ADAD

but today it is giving 57814CFBAE5C6D00F5CAB49E34B9AA2CE8269EB9 on sql and on SSIS it gave the same result as usual.

CONVERT(CHAR(40), HASHBYTES('SHA1', LTRIM(RTRIM(COALESCE('USD','')))), 2)AS usd

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 3

Are you sure that you are getting the same record as before? Is there a chance that you have something like select top 1 that today didn't get to the record that shows the USD?

Adi

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 4

I am running the following query. of a table that loaded last night using SSIS, which we use every night.

select *, CURRENCY_ID,
CONVERT(CHAR(40), HASHBYTES('SHA1', LTRIM(RTRIM(COALESCE(CURRENCY_ID,'')))), 2)AS CurrencyHashKey  ,  
CONVERT(CHAR(40), HASHBYTES('SHA1', LTRIM(RTRIM(COALESCE('USD','')))), 2)AS USD,
CONVERT(CHAR(40), HASHBYTES('SHA1', LTRIM(RTRIM(COALESCE('usd','')))), 2)AS usd,
ID,
   CONVERT(CHAR(40), HASHBYTES('SHA1', LTRIM(RTRIM(COALESCE(ID,'')))), 2)AS CustomerOrderHashKey   
from CUSTOMER_ORDER 
where CurrencyHashKey = 'C0FB8B944A767E1AACE82F930D95E69ED912ADAD'and CustomerOrderHashKey = 'E328E54D905C70339459F987CB8F1F18795A8129'

but the results for CurrencyHashKey is now = 57814CFBAE5C6D00F5CAB49E34B9AA2CE8269EB9

and I also check what I get in CURRENCY_ID = USD => so here the value did not change

and for CustomerOrderHashKey i also get a different result = CFCCBABB80FFC5839BE249B861E222A5A12EF59A

we truncate the table every night and loaded it again. so it is not something from another date.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

If I run this

SELECT CurrencyHashKey = CONVERT(CHAR(40), HASHBYTES('SHA1', LTRIM(RTRIM(COALESCE('USD', '')))), 2);

I get

57814CFBAE5C6D00F5CAB49E34B9AA2CE8269EB9

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 6

Yes, but yesterday I was getting C0FB8B944A767E1AACE82F930D95E69ED912ADAD using the same, and today using the same SSIS process we also got C0FB8B944A767E1AACE82F930D95E69ED912ADAD.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

SwePeso

SSC-Dedicated

Points: 39757

June 8, 2021 at 1:27 pm

#3893418

N 56°04'39.16"
E 12°55'05.25"

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

astrid 69000 wrote:

Yes, but yesterday I was getting C0FB8B944A767E1AACE82F930D95E69ED912ADAD using the same, and today using the same SSIS process we also got C0FB8B944A767E1AACE82F930D95E69ED912ADAD.

Are you suggesting that if you could go back in time by one day and run the query in my previous post, that the result would be different?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 9

yesterday loaded without any issue. today i am getting a different result.

Thom A SSC Guru Points: 99130 More actions · Answer 10

astrid 69000 wrote:

it is weird. I am not sure.
for example the process run last night. the hash that I got from SSIS using the same process we always use that was matching HASHBYTES, for the word "USD" it usually give C0FB8B944A767E1AACE82F930D95E69ED912ADAD
but today it is giving 57814CFBAE5C6D00F5CAB49E34B9AA2CE8269EB9 on sql and on SSIS it gave the same result as usual.
CONVERT(CHAR(40), HASHBYTES('SHA1', LTRIM(RTRIM(COALESCE('USD','')))), 2)AS usd

At some point you switched from using a nvarchar to an varchar or vice versa. This is why making your data types consistent is important. I can get both your values, but the values need to be different data types:

SELECT CONVERT(char(40), HASHBYTES('SHA1', LTRIM(RTRIM(COALESCE('USD', '')))), 2) AS usd_varchar, --57814CFBAE5C6D00F5CAB49E34B9AA2CE8269EB9
       CONVERT(char(40), HASHBYTES('SHA1', LTRIM(RTRIM(COALESCE(N'USD', '')))), 2) AS usd_nvarchar; --C0FB8B944A767E1AACE82F930D95E69ED912ADAD

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 11

that is the weird thing, nothing changed from last night, i will try to change it now.

Thom A SSC Guru Points: 99130 More actions · Answer 12

astrid 69000 wrote:

that is the weird thing, nothing changed from last night, i will try to change it now.

With the greatest of respect, this won't be true. Someone changed something. Like Jeff said at the very start of the topic, if HASHBYTES suddenly started returning inconsistent results, there would be a riot at Redmond.

I would suggest, perhaps, consulting your source control software for commits which might expose who deployed (and reverted) the change.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 13

I tried, but we are still having records that are unmatched.

Thom A SSC Guru Points: 99130 More actions · Answer 14

astrid 69000 wrote:

I tried, but we are still having records that are unmatched.

Presumably because the prior DML statement was using the opposite data type to what you are now.You're going to probably need to compare both varchar and nvarchar values now due to the inconsistent typing.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk