MySQL JDBC driver & FLOAT(N, M) column type

Question

Hi to all.

I've recently got a bug that's somewhat weird for me. Let's consider that we have a table with a column of FLOAT(8, 3). Let's also consider we have a validated table editor accessible via web browser. The table editor supports the client-side validation for every column, and does not allow to put floats that are not in the range specified by the column type using a simple JavaScript regular expression, e.g.:

var rx = new RegExp("^\\d{1," + (total - precision) + "}(\\.\\d{1," + precision + "})?$");

representing simply:

^\d{1,5}(\.\d{1,3})?$

for FLOAT(8, 3). A user can easily input 99999.999 into that cell, and when he saves the changes, he gets 100000 in the cell - a precision-lost value that's invalid for the table column constraint. I tried to extend the validator regular expression to pass 100000, but this value cannot be saved into the DB. Wow... It was expected because of "Out of range value for column 'COLUMN' at row N", but I did not expect that the MySQL console (does not show a warning) and SQLyog (shows a warning) will allow the following UPDATE query:

UPDATE TABLE SET COLUMN = 100000; /* No matter it's out of range for FLOAT(8, 3), it's trimmed but not via JDBC MySQL driver*/

Obviously it's the MySQL connector specific issue. Is it a bug of the MySQL connector (currently I use mysql-connector-java-5.1.12-bin.jar)? Or are there any quick workarounds allowing to put 100000 (ideally a maximum value) trimmed or adjusted by the JDBC driver?

Thanks in advance.

Answer 1

Sounds like you're getting burned by rounding.

What happens if you make the table column 9,3 or 10,3 instead of 8,3? Don't change the validation code, just the column def.

Can you use an integer column with an assumed decimal point? That is, 1 would be stored as 1000. 3.221 would be stored as 3221. You'd have to know to divide by 1000 before displaying the number.

Here a discussion about problems with MySQL Float.