Should NUMERIC(5,2) return 123 or 123.00 for the inserted 123?

Assume we have a table with the NUMERIC(5,2) and we're inserting 123 as BigDecimal.

When we select the value back - should it be 123 (as we inserted it) or 123.00?

I've encountered this issue when migrating from HSQLDB 1.8.0.10 to 2.3.3 in one of the projects.

With 1.8.0.10 I was getting the scale of the inserted value (123 for the inserted 123).

With 2.3.3 I'm getting the scale of the column no matter what (123.00 for the inserted 123).

I've reported this as a bug but got the following response:

But NUMERIC(5,2) means there is always 2 fractional digits no matter what you insert. Version 1.8 wasn't following the SQL Standard definition.

I don't know if this is the correct or not, but it does not seem logical to me.

Should we really getting these zeroes in the fractional part, "no matter what"?

Here's my test case:

    Connection conn = DriverManager.getConnection(...);
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("create table test (value NUMERIC(5,2));");
    String sql = "INSERT INTO test (value) VALUES(?)";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setBigDecimal(1, BigDecimal.ONE);
    pstmt.executeUpdate();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test");
    while (rs.next()) {
        Assert.assertEquals(BigDecimal.ONE, rs.getBigDecimal(1));
    }
    rs.close();
    stmt.close();
    conn.close();

Solution

The correct value would be 123.00 when you insert 123 into a NUMERIC(5,2) column. That is true, because for numerics the value is always converted to have the precision of the target datatype. In your case the specified precision is 5 and your scale is 2. Only numerics with a scale of 0 are integers.

From SQL-1992 standard: 4.4.1 Characteristics of numbers

The scale is a non-negative integer. A scale of 0 indicates that the number is an integer.

[...]

Whenever an exact or approximate numeric value is assigned to a data item or parameter representing an exact numeric value, an approximation of its value that preserves leading significant dig- its after rounding or truncating is represented in the data type of the target. The value is converted to have the precision and scale of the target. The choice of whether to truncate or round is implementation-defined.

[...]

Whenever an exact or approximate numeric value is assigned to a data item or parameter representing an approximate numeric value, an approximation of its value is represented in the data type of the target. The value is converted to have the precision of the target.

Imagine a VARCHAR(50) column that would return VARCHAR(3) type for a string that is stored in it which contains only 3 characters. Wouldn't that be odd?

Above example is valid, since if we trim the value 123.00 to 123 it would no longer be a valid numeric(5,2) datatype. The conversion is in place there to let you insert that value without throwing an error.

Also, your older version of HSQL 1.8.0.1 from 2005.07.11 was apparently not following SQL Standard in this particular case. From what you can read on their site:

HyperSQL 2 supports the dialect of SQL defined by SQL standards 92, 1999, 2003, 2008 and 2011. This means where a feature of the standard is supported, e.g. left outer join, the syntax is that specified by the standard text. Almost all syntactic features of SQL-92 up to Advanced Level are supported, as well as SQL:2011 core and many optional features of this standard.