Trailing zeros in calculated SQL fields treated as Strings

[godonnell_ip]

[SmartClient Version: v11.0p_2016-10-07/PowerEdition Deployment (built 2016-10-07)]

This one may be tricky for me to supply you a test case because it seems to be based on behaviors associated with DB2, but perhaps my simulated example below will be enough for you to work from. This is NOT a problem in earlier versions, including Client Version: v10.0p_2016-02-18/PowerEdition Deployment (built 2016-02-18).

The problem is that certain values obtained from calculations performed on fields declared as DECIMAL types are returned to the client as strings, which causes problems with display (all the trailing zeros are displayed) and sorting (client-side sorting sorts them as alphanumeric values).

These trailing zeros are handled without issue in SmartGWT 5.0 (as noted above):

But as you can see, SmartGWT 6.0 returns the values as strings:

For background, in DB2 certain calculations on DECIMAL data types return numerous trailing zeros:

Whereas others do not:

Please don't ask me to explain why. I don't know...

To simulate this, just make the the following change to employees.ds.xml (there are 16 zeros) in the BuiltInDS sample, run it. Select "Employees" . The salary column will display with all trailing zeros.

<field name="Salary"          title="Salary"          type="float"     customSelectExpression="1.0000000000000000"/>

Inspect the response with the console, and you will see the Salary was returned as a string:


Now remove one of the zeros (only 15 trailing zeros) and repeat: the Salary column values will be returned as numeric values and display without trailing zeros.

Now change the value to the left of the decimal point to 10 and leave 15 trailing zeros. Run again, and the values will display with trailing zeros again. Remove another one of the trailing zeros, and the values once again display as numerics. This might indicate that we are exceeding a limit of 16 significant digits, even though the trailing zeros should not be considered significant.

Regards,
Gary

[Isomorphic]

Try setting DataSourceField.stringInBrowser:false for the field in question. If this works, it seems to indicate that DB2 is producing BigDecimal values that have spurious precision, triggering the (intended) behavior of trying to preserve that precision by delivering the Number as a String, since JavaScript's Number type can't represent the full precision possible for BigDecimal.

[godonnell_ip]

Well, I guess that's what you get for skipping a point release... I'm sure I read about that property in the past, but since we only just moved from 10 to 11 we haven't had to actually use it until now. I ended up adding the datasource.defaultStringInBrowser: false property to server.properties, and now we're back to where we were.

I read the documentation and understand the limitations of this approach, but this behavior suits us right now (particularly since the trailing zeros are not significant).

Regards,
Gary