Column valueNumeric issues with decimal numbers

When performing an insert using and tags with valueNumeric, we are experiencing various decimal number becoming “inaccurate” with what appears to be typical double issues when creating them from strings.

The database being run against is a Sybase ASE 15 database and the insert consists of;

     
     
     

where DECIMALFIELD is DECIMAL(38,22) and NUMERICFIELD is NUMERIC(38,22) in the table DECIMALTABLE.

Looking at the code in Liquibase, it appears that these valueNumeric values will end up being converted into a Double, which is not guaranteed to be accurate, and in our case is not. BigDecimal gets around this problem, but of course the parsing of these valueNumeric values does not use BigDecimal.

To work around this I have had to add a update DECIMALTABLE set NUMERICFIELD=100000000000.1234578, DECIMALFIELD=10000000000.12 where PRIKEY=1, which sets to values in the database correctly. But it would be nice if valueNumeric did correctly parse and set the values accurrately from the tag.

Another issue that has popped with the valueNumeric conversion is with Money columns in SQL Server 2000.

There is a column of type MONEY on the database and inserting the value “922337203685471.9975” using;

                       

results in the following error message;

[INFO] Error setting up or running Liquibase: Migration failed for change set liquibase/SQLServerBase.xml::14::peterm:
    Reason: liquibase.exception.JDBCException: Error executing SQL INSERT INTO [MONEYTABLE] ([MONEYFIELD], [PRIKEY], [SMALLMONEYFIELD]) VALUES (9.223372036854776E14, 3, 123456.1245):
          Caused By: Error executing SQL INSERT INTO [MONEYTABLE] ([MONEYFIELD], [PRIKEY], [SMALLMONEYFIELD]) VALUES (9.223372036854776E14, 3, 123456.1245):
              Caused By: Arithmetic overflow error for type money, value = 922337203685477.620000.

I am concerned by the massive loss of precision in the value that Liqubase is actually trying to inject via the SQL in the error message.

Once again, the way to work around it is to inject the value using an tag then it goes in without a problem (due to no nasty conversion).

Yes, we should be parsing with BigDecimal.  I created an issue for it:  http://liquibase.jira.com/browse/CORE-518 and will implement it with 2.1

Nathan