1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Rep Power

    Crc32 gives a wrong value in pivot table


    I have a pivot table, and I need to turn the values into integers with crc32.

    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
    My pivot works fine, but my fields get a wrong CRC32 value.
    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.
    Any idea's?
    It's Mysql 5.0.96 and running on a 64bit system.

  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Toronto Canada
    Rep Power
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Rep Power
    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
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Rep Power

    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

IMN logo majestic logo threadwatch logo seochat tools logo