Hash value generated does not match

I would expect in the first instance that either the query itself is returning different results in the two sessions or that possibly some session specific config (e.g. NLS_DATE_FORMAT) is different.

Does the sqltext have ordering?  If not, it probably should.

Are your client session settings the same? Date format, number format etc?

user1356432 wrote:

 

Hi,

We are using DBMS_SQLHASH.GETHASH to get the hash value of a result set for one of our requirements. The hash value generated by using the mentioned function in SQL * PLUS is different from that is generated in SQL Developer. Any idea about this discrepancy.

 

Thanks.

Regards; Gopal

post COPY & PASTE results that show above is correct.

How do I ask a question on the forums?

How many rows are there in your resultset?

SQL Developer has a tendency to only select the first 50 rows (depending on configuration I think), whereas SQL Plus will select all of them.

I'd guess it's something like this:
Cut-n-past from SQL Developer screens:

variable b1 varchar2(64)

begin

    :b1 := dbms_sqlhash.gethash('select ctime from obj$',dbms_crypto.hash_md5);

end;

/

print b1

PL/SQL procedure successfully completed.

B1

--------------------------------------------------------------------------------

18B862AF8C005A042B2A66A988D536FF

Now from SQL*Plus:

SQL> variable b1 varchar2(64)

SQL> begin

  2  :b1 := dbms_sqlhash.gethash('select ctime from obj$',dbms_crypto.hash_md5);

  3  end;

  4  /

PL/SQL procedure successfully completed.

SQL> print b1

B1

--------------------------------------------------------------------------------------------------------------------------------

63828654857CFF6A71221850D33FCEDE

SQL>

Regards

Jonathan Lewis

Any idea about this discrepancy.

1. you get the hash for a result set using sql*plus

2. you get the hash for a result set using sql dev

3. the hashes are different.

Therefore the natural conclusion is that the result sets are different.

Therefore the OBVIOUS thing to do first is: GENERATE AND MANUALLY COMPARE THE TWO RESULT SETS.

Did you do that? If not then do it and post the results.

1. your friend John says he pulled two apples from the same basket at the grocery store.

2. he said they were two DIFFERENT types of apple but should have been the same since they were in the same basket.

Wouldn't your first response to John be: SHOW ME THE APPLES?

Stefan Jager wrote:

 

How many rows are there in your resultset?

 

SQL Developer has a tendency to only select the first 50 rows (depending on configuration I think), whereas SQL Plus will select all of them.

Stefan,

Not relevant in this case - the complete result set is selected internally by the function which then computes the hash value from the rows and returns only a single value.

Regards

Jonathan Lewis

Hi Jonathan,

I beg to differ: SQL Developer tends to modify the SQL statement before it is sent to Oracle - so the two statements as received by Oracle won't be the same. Hence the different hashes, as it should be. I do admit at the moment I have no proof of that, and if I'm honest I'm not taking your copy and paste as proof - SQL Dev could be clever enough to see through an anonymous block and still modify the select statement. I'd like to see the traces for both your statements, because only that will confirm anything either way.

Besides that: especially an old hash like MD5 should give the same result given the same input. Therefore even with your statements something funny is going on between the two clients. How would you explain that? Those two hashes SHOULD be the same, unless the ctime column changes by the minute.

Regards,

Stefan

Stefan Jager wrote:

 

Hi Jonathan,

 

I beg to differ: SQL Developer tends to modify the SQL statement before it is sent to Oracle - so the two statements as received by Oracle won't be the same. Hence the different hashes, as it should be. I do admit at the moment I have no proof of that, and if I'm honest I'm not taking your copy and paste as proof - SQL Dev could be clever enough to see through an anonymous block and still modify the select statement. I'd like to see the traces for both your statements, because only that will confirm anything either way.

 

Besides that: especially an old hash like MD5 should give the same result given the same input. Therefore even with your statements something funny is going on between the two clients. How would you explain that? Those two hashes SHOULD be the same, unless the ctime column changes by the minute.

 

Regards,

Stefan

Stefan,

I was demonstrating that it was possible to get different hash values from the same statement in two different environments to see if that helped John Thorton make an intelligent comment. Naturally I know why the two results were different in my example - I had a different setting for the nls_date_format.

As far as your "modify the statement" comment is concerned, that's not how SQL Developer limits the return set to the first 50 rows; it's basically doing something you could do in SQL*Plus through "set pagesize 53 set pause on".  It's possible, of course, that some tools (and maybe even SQL*Developer) would decide to set the optimizer mode to use first_rows_N optimization, and you might even find some tools (though I would be surprised if you did) that injected a /*+ first_rows(N) */ hint;  and maybe that can be adjusted in the configuration screen. But that's about the size of the rowsource returned - and a call to dbms_sqlhash.gethash() returns a single value to represent the entire result set of query.

Regards

Jonathan Lewis

How many rows are there in your resultset?

 

SQL Developer has a tendency to only select the first 50 rows (depending on configuration I think), whereas SQL Plus will select all of them.

How is ANY of that relevant?

The function OP is calling does NOT return a result set - it returns a scalar.

Neither client will EVER see a result set - so the number of rows isn't an issue.

I beg to differ: SQL Developer tends to modify the SQL statement before it is sent to Oracle - so the two statements as received by Oracle won't be the same.

That seems to be quite a LEAP from 'tends to modify' to "won't be the same".

The example Jonathan gave was a block - not a sql statement. Sql Dev does NOT modify strings within blocks.

As far as your "modify the statement" comment is concerned, that's not how SQL Developer limits the return set to the first 50 rows; it's basically doing something you could do in SQL*Plus through "set pagesize 53 set pause on".

Sql Dev is written in Java and uses a Java-based JDBC driver. It defines a buffer based on the JDBC batch size that has been set and then FETCHES the results to try to fill the buffer.

But your example does NOT return a result set - it returns a scalar. So the batch/fetch size doesn't enter into it. Doing a hash on  a result set is about the same as doing a count(*). In neither case is the entire result set sent to the client when the aggregation is done on the server.

Until/unless OP decides to post the actual queries and results they are using it is anyone's guess what really happened. But if I had to guess my guess would be that OP got confused about what they really did and posted the thread before realizing their mistake.

We'll have to wait and see - wouldn't surprise me at all if we never hear from them again.

rp0428 wrote:

 

As far as your "modify the statement" comment is concerned, that's not how SQL Developer limits the return set to the first 50 rows; it's basically doing something you could do in SQL*Plus through "set pagesize 53 set pause on".

Sql Dev is written in Java and uses a Java-based JDBC driver. It defines a buffer based on the JDBC batch size that has been set and then FETCHES the results to try to fill the buffer.

 

I have heard that theory before  - but in my version of SQL Developer (17.4.1.054) under Tools -> Preferences -> Database -> Advanced there's a preference called the SQL Array Fetch Size which can be set to a value between 50 and 200, and when I executed a query that select a row of size roughly 512KB SQL Developer still started with (and v$sql reported) a single fetch of 50 rows.

But your example does NOT return a result set - it returns a scalar.

No need to tell me that really, given that it's paraphrasing something I told Stefan earlier on:  "Not relevant in this case - the complete result set is selected internally by the function which then computes the hash value from the rows and returns only a single value."

But if I had to guess my guess would be that OP got confused about what they really did.

I prefer to assume that people are reasonably competent and sensible until they prove otherwise, and the OP's opening statement doesn't sound particularly confused or confusing to me:

"We are using DBMS_SQLHASH.GETHASH to get the hash value of a result set for one of our requirements." 

"The hash value generated by using the mentioned function in SQL * PLUS is different from that is generated in SQL Developer."

Regards

Jonathan Lewis