1

We have a set of calculations that do something like

SELECT ROUND(SUM(ROUND(3*IP))/3,1) as IP_sum from ....

Say for example that IP_sum is exactly 180.

When we call from the mysql client we get 180.0 every time, but if we use the perl dbi, the result depends on IP's column type.

When IP is a decimal(7,1) we get one digit and IP_sum => '180.0' When IP is a float(7,1) we don't get any digits => IP_sum => '180'

Is anyone aware of a new change or update to the Perl DBI that might be causing this? We just updated some modules and programs this week.

Engine: Mysql 5.6, mysql client, 5.5.

Improve this question
6
  • If you are talking about the mysql datatype FLOAT(7,1), well do not use (m,n) on FLOAT. Use either DECIMAL(7,1) or plain FLOAT in MySQL.
    – Rick James
    Apr 29, 2017 at 20:10
  • Rick James, it still seems like a strange behavior when calling ROUnD(num,1) on the column
    – Sean
    May 2, 2017 at 18:50
  • Please elaborate on "strange".
    – Rick James
    May 3, 2017 at 3:39
  • seems to me that "SELECT ROUND(example,3) from foo" should return ?.XXX regardless of the type of column example is. Maybe that's against the spec. I'm not sure.
    – Sean
    May 4, 2017 at 4:57
  • My comment was not a real answer -- it was talking about FLOAT. HVS has the "real answer".
    – Rick James
    May 4, 2017 at 5:03

1 Answer 1

Reset to default
3

This was unrelated to MySQL and was due to a change in the Perl DBD::mysql packages from version 4.013 to 4.042. Essentially, DBD::mysql now attempts to coerce the MySQL datatype into a Perl datatype. So, previously, a float of 4.0 would be returned as a string '4.0', but now the same float will be returned as a numeric value and be displayed as '4' when printed (without additional formatting on the part of Perl.

Improve this answer
1
  • excellent answer. I'm sure this took a great deal of work to track down.
    – Sean
    Apr 28, 2017 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.