#414 rounds BigDecimal values (loss of scale)

2.3rc2
closed-fixed
Rob Manning
Core (461)
5
2006-09-30
2006-08-09
Anonymous
No

with HSQLDB (version 1.8.0.5) as Database:

CREATE TABLE test (nr NUMERIC);
or
CREATE TABLE test (nr DECIMAL);

INSERT INTO test (nr) VALUES (2.4);

SELECT * FROM test;

will show a wrong result: 2
=> The scale seems to be lost.

But:
Selecting it with a little Java program (Connection,
Statement, ResultSet...) will end up in the correct
result: 2.4 as BigDecimal (HSQLDB data type NUMERIC and
DECIMAL map to BigDecimal).

The database script file shows the correct value: 2.4

So, I think, it's a bug in SQuirreL SQL Client!?!

I would appreciate a bugfix in 2.3 final.

Thank you very much for that great JDBC-Client!

Horst

Discussion

  • Rob Manning
    Rob Manning
    2006-08-12

    • labels: 336290 --> Core
     
  • Rob Manning
    Rob Manning
    2006-08-12

    Logged In: YES
    user_id=1287991

    Horst,

    Indeed, I can reproduce the behavior following the steps you
    listed. I'm not quite sure that it's a bug however, and
    here is why. You did not declare the precision and scale
    when you created a table with decimal/numeric type. The
    HSQLDB jdbc driver (rightly or wrongly - I can't decide)
    reports that the "nr" column has 0 decimal digits. I
    assume that is because the scale if not specified
    defaults to 0. If I modify your create statement only
    slightly:

    CREATE TABLE test (nr DECIMAL(10,2));

    Then SQuirreL displays the value as 2.4 when I perform
    the same select. In case you are not aware, "10" is the
    value for precision, which means the number can have
    up to 10 digits, and "2" is the value for scale which
    means two of those ten digits can be used to store
    fractional amounts.

    We rely on the driver and database metadata greatly
    to properly format the values we get back from the
    database. In this case, since the scale is 0,
    we are formatting the value with no decimal
    digits - that is what scale = 0 implies.

    I would recommend being explicit about the precision and
    scale you require for the column. I believe this practice
    lends itself to portability since you tend to find
    loss of precision problems quicker ( when you create the
    table, as opposed to at runtime when the app attempts
    to store the data).

    Does my explanation seem reasonable to you?

    Rob

     
  • giobo
    giobo
    2006-08-22

    Logged In: YES
    user_id=1270155

    I have the same problem and I can't change the table
    structure for the SQL client.

    I've checked that the version 2.1 final work fine and
    other SQL client work correctly with 0 in scale.

    This bug is present in versions 2.2 and next.

    Thank you

     
  • Rob Manning
    Rob Manning
    2006-09-30

    • milestone: 620232 --> 2.3rc2
    • status: open --> closed-fixed
     
  • Rob Manning
    Rob Manning
    2006-09-30

    Logged In: YES
    user_id=1287991

    Ok, for whatever reason, when I started looking at
    this issue again, I can't seem to reproduce it
    following the steps outlined in the original
    report. I consistently get 2.4 in the result,
    both Object Tree table contents and SQL editor
    result section. I don't believe any new code was
    added to address the issue that I was seeing before.
    Nonetheless, feel free to re-open this issue
    if it proves not to be fixed at a later point.

    Rob

     
  • Logged In: NO

    I think that it would be nice to be able to override the formatting of numeric columns.

    Hibernate for example seems to generate ddl for HSQL that defaults to DECIMAL_DIGITS=0 when looking at the column meta data in SQuirreL, but the mappings works ok in Hibernate. As it is quite tedious to override all scale parameters in the mappins it would be great if SQuirreL could be configured to force another scale.

     
  • Logged In: NO

    Hello Rob,

    specifying the precision/scale explicitly is a workaround, but not always what you need:
    If you don't want to have any precision and/or scale, you have to take NUMERIC or DECIMAL, because both end up in java.math.BigDecimal.

    Probably you are right, it could be a bug in the jdbc driver, but which precision/scale should the driver put into the metadata, if the data type doesn't have any precision/scale?!?

    Furthermore, I found bug 1608540
    (http://sourceforge.net/tracker/index.php?func=detail&aid=1608540&group_id=28383&atid=393414).
    If I change in "Global preferences/Data type control"s the "SQL types SQL types 6, 7, 8, 2, 3" to "default",
    the result of the original example is correctly displayed: 2.4.
    Personally I can live with 2.4 instead of the localized German 2,4 (colon, no dot!), but of course it would be nice, to have bug 1608540 fixed, so that in both configurations the result would be 2.4 / 2,4.

    So I think, fixing bug 1608540 will also fix this bug, won't it?

    Best regards,
    Horst

     
  • Logged In: NO

    Hello Rob,

    I tested it again with version 2.6.1 and it worked fine - regardless of the described SQL data type setting preference.

    Horst