0

I am trying to implement a special hashing algorithm in SQL to de-hash some of our data. I am almost there but I am stuck at last step.

Trying this bit of code in SQL:

SELECT CAST(2912047312 AS BIGINT) & CAST(2912047312 AS BIGINT)

gives me 2912047312

However in JavaScript using following code:

console.log(2912047312 & 2912047312);

the result will be -1382919984

Am I doing something wrong in SQL, read about the necessity of conversion to bigint which has been done, but the operation always returns the same value.

SQL Database: SQL Server 2008 10.50.6000

Moseleyi
  • 2,585
  • 1
  • 24
  • 46
  • What datatype is it in Java? – Matt Oct 20 '15 at 11:53
  • Post executable JS code. – usr Oct 20 '15 at 11:57
  • Please tag dbms product used? (This is not ANSI SQL.) – jarlh Oct 20 '15 at 12:05
  • Your SQL looks good. It would appear that 32 bit integer conversion in Javascript for bitwise operations may be relevant- related issues such as http://stackoverflow.com/questions/2983206/bitwise-and-in-javascript-with-a-64-bit-integer come to mind, for example. In your scenario, since you're simply looking for bitwise operations on larger integers, when in Javascript I would be tempted to mask the upper and lower parts of the integer and operate on these. Best. – Tom Oct 20 '15 at 12:24
  • Knowing nothing about JavaScript... is `console.log` interpreting the result as a signed integer? What happens in JS if you just `console.log(2912047312)`? – nhgrif Oct 20 '15 at 12:29
  • @nhgrif I don't think it's console.log, this is standard for Javascript. Quote: "Bitwise operators treat their operands as a sequence of 32 bits". https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Bitwise_Operators . But maybe I have misunderstood? – Tom Oct 20 '15 at 12:29
  • @Tom the only problem is that I can't change the values from JavaScript, hence I am trying to implement the same behaviour in SQL – Moseleyi Oct 20 '15 at 12:51
  • JavaScript may not behave as you'd expect with large integers, as all Javascript numbers are actually floating point, even those that look like integers. (e.g. try `console.log(10205150669939609)` and you might be surprised...) – Matt Gibson Oct 20 '15 at 13:00

1 Answers1

2

Javascript

Javascript is using 32 bit signed integers while performing bitwise operations (as Tom linked it already). Since your value is bigger than maximum value of a 32bit integer, only the lower part of the integer will be in use (this is because of overflow).

You can test it by executing a simple test: console.log(2912047312 | 0) which will print you -1382919984.

The two operands in the 2912047312 & 2912047312 expression will be transformed to 32 bit integers, so the actual bitwise operation will be performed on the -1382919984 & -1382919984 expression.

The range of a signed integer is −2147483648 to +2147483647.

SQL Server

SQL Server can perform bitwise operation on any integer type operands, including bigint, so the result will be calculated on a bigger datatype, no overflow will occur.

So the difference is because the two systems are using different data types in bitwise operations, and one of them can't handle the value you provided without overflow and therefore without changing the actual value.

Edit

You can 'emulate' the overflow with a small trick:

SELECT (-2147483648 + (CAST(2912047312 AS BIGINT) & CAST(2147483647 AS BIGINT)));

The CAST(2912047312 AS BIGINT) & CAST(2147483647 AS BIGINT) part will drop the upper part of the 64 bit integer value (the upper 32 bits are 0s, the lower 32 bits are 1s for the 2147483647 value which is the maximum value using a 32bit integer).

Then it will add the result to the minimum value of a signed 32 bit integer (-2147483648).

Pred
  • 8,789
  • 3
  • 26
  • 46
  • Correct me if I understand it incorrectly (hopefully I get it right). So the SQL can handle 64bits using BIGINT, but JavaScript won't, but it doesn't "tell" us by throwing an error etc. just processes the lower part of the integer. Also I'm assuming I can't do anything in SQL since it will cause arithmetic overflow, but can I convert them so that SQL treats them same way as JavaScript? I may be very thick here, apologies, how `2912047312 ` become `-1382919984`? – Moseleyi Oct 20 '15 at 12:57
  • Yes you can, see the edit, otherwise you understood it correctly – Pred Oct 20 '15 at 13:02
  • I see.. it does make sense and I think it's almost perfect `SELECT (-2147483647 + (CAST(2912047312 AS BIGINT) & CAST(2147483647 AS BIGINT)))` `-1382919983` `console.log(2912047312 & 2912047312);` `-1382919984` there is 1 difference.. should I add 1 for sql result after the aforementioned operation? – Moseleyi Oct 20 '15 at 13:55
  • Another "funny" thing... Based on your example I have been trying to work out an example on the other side of the Int32 spectrum: `console.log(-2332017855 & -2332017855);` which is 1962949441 now what is weird is when I compare following: `SELECT (2147483647 - (CAST(-2332017855 AS BIGINT) ^ CAST(-2147483647 AS BIGINT) ));` with `SELECT (-2147483647 - (CAST(-2332017855 AS BIGINT) ^ CAST(2147483647 AS BIGINT) ));` ideally only +/- should change, but the value changed as well, could that be also the reason for the difference? – Moseleyi Oct 20 '15 at 14:16
  • My fault, the min value of a 32bit (signed) integer is -2147483648, I've used -2147483647. fixed in the answer. With your examples: `SELECT (2147483647 - (CAST(-2332017855 AS BIGINT) ^ CAST(-2147483648 AS BIGINT) ));` and `SELECT (-2147483648 - (CAST(-2332017855 AS BIGINT) ^ CAST(2147483647 AS BIGINT) ));` – Pred Oct 20 '15 at 14:22
  • I played around with the < -2147483648 applied different calculation.. and it works perfectly.. Thank you a million times! – Moseleyi Oct 20 '15 at 14:41