Pentaho MySQL Datatypes Decimals and Float problem

When you use Pentaho Tools like PDI / Kettle to generate tables in MySQL it is attempting to generate currency (precision) columns as Float. This is not an issue in the first place, as you would think. However it caused serious problems and multiple days of work for us.

The problem in simple terms is, that a FLOAT field in MySQL is stored as an approximate value http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html, which is considering you are building a financial table something you don't wanna see.

The problem we found was in an example with a table containing only 190 values a simple statement like:

SELECT SUM(amtacctdr-amtacctcr) AS TOTAL, SUM(amtacctdr) AS DEBIT, SUM(amtacctcr) AS CREDIT) FROM accountingdata WHERE account_id=1234

could cause results with up to 10 digits precision, even though the data you used to populate contained only 2 digits precision.

The fun stuff was that looking at the values in both source and target table showed no problem, so we were confused.

Further research directed us to: http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html explaining the problem.

We needed to reformat about 50 tables, repopulating 3 years of data warehouse to correct this and fix the issue, so please ensure that you use field types like NUMERIC or DECIMAL instead of FLOAT.