search

wkim / h2database

Automatically exported from code.google.com/p/h2database
0 stars 0 forks source link

Function AVG() for a numeric value accessed with getInt() returns rounded value #377

Closed GoogleCodeExporter closed 8 years ago

GoogleCodeExporter commented 8 years ago 
If you use AVG() with results for example in a value of 6.8 and try to get the 
result with getInt() you will get 7 (rounded) instead of 6. I'm not sure, if 
there is a specification if rounding or truncating is right, but other DBs 
return a truncated value (tested with Oracle, Sybase and MySQL).

I attached a example test case.

Original issue reported on code.google.com by andreas....@gmail.com on 16 Feb 2012 at 4:52

GoogleCodeExporter commented 8 years ago 
Please tell me if you find the specification.

For the SQL statement SELECT CAST(1.5 AS INT), some databases truncate (HSQLDB, 
Derby)
and others round to 2 (PostgreSQL).

Original comment by thomas.t...@gmail.com on 17 Feb 2012 at 1:15

GoogleCodeExporter commented 8 years ago 
Hello Thomas.

Hm. As far as I can see the ANSI-SQL-92 specification let the implementor 
decide if truncation or rounding is used:

    2) Subclause 4.4.1, "Characteristics of numbers": Whether trunca-
            tion or rounding is performed when trailing digits are removed
            from a numeric value is implementation-defined.

In the definition for "cast" the specification always says "rounding or 
truncating" without any further hints what if there is a prefered way to do it.

I'm sorry, I compared the results of H2 with Oracle and Sybase ASA and that DBs 
truncated the values. Maybe the behaviour of H2 should depend on the Oracle 
compatibility mode?

If you don't think so, reject the issue.

Original comment by andreas....@gmail.com on 17 Feb 2012 at 1:39

GoogleCodeExporter commented 8 years ago 
I think most databases round (1.5 becomes 2) when casting from double to int 
within the database engine itself, but truncate (1.5 becomes 1) if done in the 
JDBC API (when using getInt() on a double). H2 always rounds. It's not fully 
compatible with other databases, but then the other databases don't work 
consistently... so I can pick compatibility or consistency. For now I will pick 
consistency, so keep the current behavior.

Original comment by thomas.t...@gmail.com on 23 Feb 2012 at 7:23