{"id":2344,"date":"2010-07-07T10:53:37","date_gmt":"2010-07-07T08:53:37","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=2344"},"modified":"2010-07-07T13:33:44","modified_gmt":"2010-07-07T11:33:44","slug":"implicit-casting-you-dont-want-to-see-around","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/implicit-casting-you-dont-want-to-see-around","title":{"rendered":"Implicit casting you don’t want to see around"},"content":{"rendered":"

In Beware of implicit casting<\/a>, I have outlined the dangers of implicit casting. Here’s a few more real-world examples I have tackled:<\/p>\n

Number-String comparisons<\/h4>\n

Much like in programming languages, implicit casting is made to numbers when at least one of the arguments is a number. Thus:<\/p>\n

\n
\r\nmysql> SELECT 3 = '3.0';\r\n+-----------+\r\n| 3 = '3.0' |\r\n+-----------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n+-----------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> SELECT '3' = '3.0';\r\n+-------------+\r\n| '3' = '3.0' |\r\n+-------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n+-------------+\r\n<\/pre>\n<\/blockquote>\n
The second query consists of pure strings comparison. It has no way to determine that number comparison should be made.<\/p>\n
Direct DATE arithmetics<\/h4>\n
The first query seems<\/em> to work, but is completely incorrect. The second explains why. The third is a total mess.<\/p>\n
\n
\r\nmysql> SELECT DATE('2010-01-01')+3;\r\n+----------------------+\r\n| DATE('2010-01-01')+3 |\r\n+----------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 20100104 |\r\n+----------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> SELECT DATE('2010-01-01')-3;\r\n+----------------------+\r\n| DATE('2010-01-01')-3 |\r\n+----------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 20100098 |\r\n+----------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> SELECT '2010-01-01' - 3;\r\n+------------------+\r\n| '2010-01-01' - 3 |\r\n+------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2007 |\r\n+------------------+\r\n1 row in set, 1 warning (0.00 sec)\r\n<\/pre>\n<\/blockquote>\n
Number-String comparisons, big integers<\/h4>\n
Look at the following crazy comparisons:<\/p>\n
\n
\r\nmysql> SELECT 1234 = '1234';\r\n+---------------+\r\n| 1234 = '1234' |\r\n+---------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n+---------------+\r\n\r\nmysql> SELECT 123456789012345678 = '123456789012345678';\r\n+-------------------------------------------+\r\n| 123456789012345678 = '123456789012345678' |\r\n+-------------------------------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n+-------------------------------------------+\r\n\r\nmysql> SELECT 123456789012345678 = '123456789012345677';\r\n+-------------------------------------------+\r\n| 123456789012345678 = '123456789012345677' |\r\n+-------------------------------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n+-------------------------------------------+\r\n<\/pre>\n<\/blockquote>\n
The amazing result of the last two comparisons may strike as odd. Actually, it may strike as a bug, and indeed when a customer approached me with this behavior I was at loss for words. But this is documented<\/a>. The manual describes the cases for casting, then states: “… In all other cases, the arguments are compared as             floating-point (real) numbers<\/em>. …”<\/p>\n
Lessons learned:<\/h4>\n
    \n
  • Be careful when comparing strings with floating point values. Matching depends on how both are represented.<\/li>\n
  • Avoid converting temporal types to strings when doing date manipulation.<\/li>\n
  • Avoid direct math on temporal types.<\/li>\n
  • Avoid casting BIGINT<\/strong>s represented by strings. Casting will turn out to use FLOAT<\/strong>s and may be incorrect.<\/li>\n<\/ul>\n
    Last but not least:<\/p>\n
      \n
    • Use the proper data types for your data’s representation. When dealing with numbers, use numbers. When dealing with temporal values, use temporal types.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
      In Beware of implicit casting, I have outlined the dangers of implicit casting. Here’s a few more real-world examples I have tackled: Number-String comparisons Much like in programming languages, implicit casting is made to numbers when at least one of the arguments is a number. Thus: mysql> SELECT 3 = ‘3.0’; +———–+ | 3 = […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[18,21],"class_list":["post-2344","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-data-types","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-BO","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2344","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=2344"}],"version-history":[{"count":24,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2344\/revisions"}],"predecessor-version":[{"id":2693,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2344\/revisions\/2693"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=2344"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=2344"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=2344"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}