January 27th, 2014, 09:30 AM
Crc32 gives a wrong value in pivot table
I have a pivot table, and I need to turn the values into integers with crc32.
My pivot works fine, but my fields get a wrong CRC32 value.
SELECT e.guid as id, e.guid as guid, type, subtype, md.owner_guid as owner_guid,site_guid, container_guid, md.access_id as access_id, username, name,e.time_created, time_updated,name, e.enabled, banned, e.last_action, last_login,
MAX( IF( msn.string = 'portrait', CRC32(msv.string), NULL ) ) AS portrait,
MAX( IF( msn.string = 'fashion', CRC32(msv.string), NULL ) ) AS fashion FROM exp_metadata md
JOIN exp_metastrings msn ON md.name_id = msn.id
JOIN exp_metastrings msv ON md.value_id = msv.id
JOIN exp_users_entity u ON u.guid = md.entity_guid
JOIN exp_entities e ON e.guid = md.entity_guid
GROUP BY guid
ex. my field value gets: 1401672531 (crc32 integer)
while actually the right crc32 output should be: 2620844751
I don't get it, it's converting a string to an integer, but it's just not right.
It's Mysql 5.0.96 and running on a 64bit system.
January 28th, 2014, 08:06 AM
i had never heard of CRC32 until now
da manual says it "computes a cyclic redundancy check value" -- wtf is that?
would you, perhaps, be looking for CAST(msv.string AS UNSIGNED) instead?
January 28th, 2014, 08:24 AM
A CRC is 'just' a type of hash value, I have usually seen it to act as a file integrity checker.
WHat it will be doing is genertaing a value based on what you give it, inclusing any leading or trailing spaces - be sure you you are comparing the results from like inputs.
The moon on the one hand, the dawn on the other:
The moon is my sister, the dawn is my brother.
The moon on my left and the dawn on my right.
My brother, good morning: my sister, good night.
-- Hilaire Belloc
January 28th, 2014, 10:40 AM
I searched for this for a complete day and noticed that I had a uppercase character in the string and I was converting the same string without that uppercase...
I feel very, but very stupid and I appologise for this thread.
But anyway, one good thing this thread did was letting r937 now about crc32 :-)
I use it to convert strings to integers to let me query and filter faster using Sphinx.
Thank you and sorry