{"id":6028,"date":"2013-01-24T10:08:40","date_gmt":"2013-01-24T08:08:40","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=6028"},"modified":"2013-01-24T10:08:40","modified_gmt":"2013-01-24T08:08:40","slug":"re-mysql-5-1-vs-mysql-5-5-floats-doubles-and-scientific-notation","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/re-mysql-5-1-vs-mysql-5-5-floats-doubles-and-scientific-notation","title":{"rendered":"Re: MySQL 5.1 vs. MySQL 5.5: Floats, Doubles, and Scientific Notation"},"content":{"rendered":"

Reading Sheeri’s MySQL 5.1 vs. MySQL 5.5: Floats, Doubles, and Scientific Notation<\/a>, I was baffled at this change of floating point number notation.<\/p>\n

However, I was also concerned about the final action taken: using “–ignore-columns”<\/strong> to avoid comparing the FLOAT<\/strong>\/DOUBLE<\/strong> types.<\/p>\n

The –float-precision<\/a> option for pt-table-checksum<\/em> currently only uses ROUND()<\/strong> so as to disregard minor rounding issues. But it can very easily extend to handle the difference in floating point notation. Consider again the problem:<\/p>\n

\n
mysql> create table tf(f float);\r\nQuery OK, 0 rows affected (0.11 sec)\r\n\r\nmysql> insert into tf values(0.0000958084);\r\nQuery OK, 1 row affected (0.04 sec)\r\n\r\nmysql-5.1<\/strong>> select * from tf;\r\n+-------------+\r\n| f\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-------------+\r\n| 9.58084e-05 |\r\n+-------------+\r\n\r\nmysql-5.5<\/strong>> select * from tf;\r\n+--------------+\r\n| f\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+--------------+\r\n| 0.0000958084 |\r\n+--------------+<\/pre>\n<\/blockquote>\n
How can we normalize the notation?<\/p>\n
Easily: CAST<\/strong> it as DECIMAL<\/strong>. Consider:<\/p>\n
\n
mysql-5.1<\/strong>> SELECT f,ROUND(IF(f BETWEEN -1 AND 1, CAST(f AS DECIMAL(65,30))<\/strong>, f), 10) as fn from tf;\r\n+-------------+--------------+\r\n| f\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | fn\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-------------+--------------+\r\n| 9.58084e-05 | 0.0000958084 |\r\n+-------------+--------------+\r\n\r\nmysql-5.5<\/strong>> SELECT f,ROUND(IF(f BETWEEN -1 AND 1, CAST(f AS DECIMAL(65,30))<\/strong>, f), 10) as fn from tf;\r\n+--------------+--------------+\r\n| f\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | fn\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+--------------+--------------+\r\n| 0.0000958084 | 0.0000958084 |\r\n+--------------+--------------+<\/pre>\n<\/blockquote>\n
The normalization works well in both cases; also, taking care to only normalize values in the range [-1, 1]<\/strong>.<\/p>\n
The change in pt-table-checksum<\/em>? One line of code:<\/p>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 elsif ( $float_precision && $type =~ m\/float|double\/ ) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $result = \"ROUND($result, $float_precision)\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }<\/pre>\n<\/blockquote>\n
Turns to<\/p>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 elsif ( $float_precision && $type =~ m\/float|double\/ ) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $result = \"ROUND(IF($result BETWEEN -1 AND 1, CAST($result AS DECIMAL(65,30)), $result), $float_precision)\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }<\/pre>\n<\/blockquote>\n
I’ve just submitted a blueprint, but I would think changing one line of code shouldn’t wait till next release of Percona Toolkit: one can edit in-place their \/usr\/bin\/pt-table-checksum<\/strong> and proceed to validate their database integrity.<\/p>\n","protected":false},"excerpt":{"rendered":"
Reading Sheeri’s MySQL 5.1 vs. MySQL 5.5: Floats, Doubles, and Scientific Notation, I was baffled at this change of floating point number notation. However, I was also concerned about the final action taken: using “–ignore-columns” to avoid comparing the FLOAT\/DOUBLE types. The –float-precision option for pt-table-checksum currently only uses ROUND() so as to disregard minor […]<\/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":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[18,57,96],"class_list":["post-6028","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-data-types","tag-open-source","tag-percona-toolkit"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1ze","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6028","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=6028"}],"version-history":[{"count":3,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6028\/revisions"}],"predecessor-version":[{"id":6031,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6028\/revisions\/6031"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=6028"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=6028"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=6028"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}