Rounding weirdness with scientific notation formatString

LabKey Support Forum (Inactive)
Rounding weirdness with scientific notation formatString Will Holtz  2016-03-02 15:17
Status: Closed
 
I have the query:
SELECT
    0.00055555 AS a,
    0.55555 AS b,
    555.55 AS c;

and associated metadata:
<?xml version="1.0" encoding="UTF-8"?>

<query xmlns="http://labkey.org/data/xml/query">
    <metadata>
        <tables xmlns="http://labkey.org/data/xml">
            <table tableName="TestFormat" tableDbType="NOT_IN_DB">
                <columns>
                    <column columnName="a">
                        <formatString>##0.#E0</formatString>
                    </column>
                    <column columnName="b">
                        <formatString>##0.#E0</formatString>
                    </column>
                    <column columnName="c">
                        <formatString>##0.#E0</formatString>
                    </column>
                </columns>
            </table>
        </tables>
    </metadata>
</query>

By viewing the query output in the schema browser I get the following:
    
A B C
 555.6E-6 555.5E-3 555.5E0

I would have expected the final digit of each of these to get rounded up to 6, but it only happened for A. This is with Postgres v9.5 and Labkey revision 41716.

-Will
 
 
Jon (LabKey DevOps) responded:  2016-03-02 18:31
Hi Will,

It looks like the number would have to stretch past 5 digits for that number 6 to appear. It's almost like rather than rounding up if you're number to the right is 5 and above, it appears to require two numbers to the right since the format 555.6E-3 appears if you make the original number at 0.555551 through 0.555559

Let me verify whether this rounding is intentional by LabKey or by the DB.

Regards,

Jon
 
Jon (LabKey DevOps) responded:  2016-03-02 22:22
Hi Will,

This definitely isn't a LabKey thing that is causing the unusual rounding, but something that is tied to Java.

https://docs.oracle.com/javase/7/docs/api/java/text/DecimalFormat.html

I tried to use CAST to have those numbers use DECIMAL, but nothing changed. However, floating point numbers are not exact representations of decimal numbers, so you'll always get this kind of odd behavior, regardless of programming languages.

Regards,

Jon
 
Will Holtz responded:  2016-03-03 09:28
Thanks for the reply Jon. I thought 5 digits of decimal precision was well within the capabilities of a 32-bit float. But I agree that this is a java issue and not Labkey.

-Will