[development] Converting floats stored in scientific notation back to decimals

[Karen Stevenson]

I'm working on the CCK D6 port and I need help coming up with the SQL to convert floats stored in scientific notation back to decimals.

Up to now, CCK has used a float field to store the 'decimal' field type, which works fine if the numbers are not too large, but ends up stored in scientific notation if they are. This has been a known CCK problem for a long time but fixing it before now would have involved re-writing some of the most complex code in CCK, so it hasn't gotten done.

In D6 we now have a real decimal field to use, which is great, but I need to write an D6 update that will take the data sometimes stored in scientific notation and sometimes as decimal values in those float fields, and migrate it to decimal fields without losing any data, and I badly need help finding the right SQL to do this update.

You can easily create a test bed by creating a table with a float field and a decimal field. Then store some small and some very large integers and decimals, in the float field and try to copy those stored values from the float field back to the decimal field without losing or corrupting any data.

And of course we have to do it in a way that works in both MYSQL and Postgres :)

Thanks!

Karen