[SOLVED] Redshift CRC32 producing different output than MySQL

Redshift CRC32 producing different output than MySQL

Redshift's CRC32 function returns a string:

SELECT CRC32('abc');
> 364b3fb7

while MySQL returns an integer:

SELECT CRC32('abc');
> 891568578

From AWS docs:

The CRC32 function returns an 8-character string that is a text representation of the hexadecimal value of a 32-bit binary sequence.

And from W3 docs:

MySQL CRC32() returns the cyclic redundancy check value of a given string as a 32-bit unsigned value

I'm guessing the hex value is equivalent to that of the result in MySQL, but I haven't been able to convert it. I need an integer output for numeric comparison.

I tried STRTOL with base 32 but no luck:

SELECT STRTOL(CRC32('abc'), 32);
> 109667532135

I am definitely missing something I can't identify, should they not produce the same value?

Solution

There are many CRC-32's. You have two different ones there. So, no, they should not produce the same value. The MySQL CRC in hex is 352441C2.

The Redshift one is the CRC-32/SCSI. The MySQL one is the CRC-32/ISO-HDLC.