HASHBYTES giving different resaults than yesterday

Question

Post reply

HASHBYTES giving different resaults than yesterday

astrid 69000

Hall of Fame

Points: 3203
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: 1000539 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)
Intro to Tally Tables and Functions

astrid 69000 Hall of Fame Points: 3203 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: 418 More actions · Answer 3

Adi Cohn

SSC-Addicted

Points: 418

June 8, 2021 at 1:15 pm

#3893409

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: 3203 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: 245813 More actions · Answer 5

If I run this

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

I get

57814CFBAE5C6D00F5CAB49E34B9AA2CE8269EB9

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

astrid 69000

Hall of Fame

Points: 3203

June 8, 2021 at 1:26 pm

#3893416

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: 39725 More actions · Answer 7

SwePeso

SSC-Dedicated

Points: 39725

June 8, 2021 at 1:27 pm

#3893418

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

Phil Parkin SSC Guru Points: 245813 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?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

astrid 69000

Hall of Fame

Points: 3203

June 8, 2021 at 1:37 pm

#3893422

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

Thom A SSC Guru Points: 99009 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: 3203 More actions · Answer 11

astrid 69000

Hall of Fame

Points: 3203

June 8, 2021 at 1:49 pm

#3893427

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

Thom A SSC Guru Points: 99009 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: 3203 More actions · Answer 13

astrid 69000

Hall of Fame

Points: 3203

June 8, 2021 at 2:08 pm

#3893438

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

Thom A SSC Guru Points: 99009 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