I did some investigation, the reason is this method:
public static int getAddProofType(int type)  (org.h2.value.DataType)

That conversion make sense, however i think that the documentation should 
be fixed.
http://www.h2database.com/html/functions.html#sum

(
Personaly i would have preferred a sum returning the same type as the 
argument.
Potential overflows could be managed by the author of the query with 
something like: select count(cast(col as DECIMAL))
)

I read the doc of some other database, and they are mainly doing a similar 
conversion except Oracle that preserve the type.

*Oracle:*

> This function takes as an argument any numeric datatype or any nonnumeric 
> datatype that can be implicitly converted to a numeric datatype. The 
> function returns the same datatype as the numeric datatype of the argument.
> Column JDBC Type is lost when using SUM aggregate.
>

*Postgresql:*

> bigint for smallint or int arguments, numeric for bigint arguments, double 
> precision for floating-point arguments, otherwise the same as the argument 
> data type
>

*Mysql:*

> For numeric arguments, the variance and standard deviation functions 
> return a DOUBLE value. The SUM() and AVG() functions return a DECIMAL value 
> for exact-value arguments (integer or DECIMAL), and a DOUBLE value for 
> approximate-value arguments (FLOAT or DOUBLE). (Before MySQL 5.0.3, SUM() 
> and AVG() return DOUBLE for all numeric arguments.) 
>

Cédric.


Le jeudi 24 janvier 2013 19:30:42 UTC+1, cedric a écrit :
>
> Hi,
>
> According to the SUM() documentation.
>
>> the returned value is of the same data type as the parameter.
>>
>
> However it seems to not be the case (version 1.3.170).
>
> Here is the test case:
>     private static int getJdbcType(Connection conn, String sql) throws 
> SQLException {
>         PreparedStatement st = conn.prepareStatement(sql);
>         try {
>             ResultSet rs = st.executeQuery();
>             try {
>                 return rs.getMetaData().getColumnType(1);
>             } finally {
>                 rs.close();
>             }
>         } finally {
>             st.close();
>         }
>     }
>     private static void doUpdate(Connection conn, String sql) throws 
> SQLException {
>         PreparedStatement st = conn.prepareStatement(sql);
>         try {
>             st.executeUpdate();
>         } finally {
>             st.close();
>         }
>     }
>     public static void main(String[] args) {
>         
>         try {
>             Connection conn = 
> DriverManager.getConnection("jdbc:h2:mem:test");
>             try {
>                 doUpdate(conn,"create table test (col BIGINT)");
>                 System.out.println(getJdbcType(conn, "select col from 
> test")); 
>                 System.out.println(getJdbcType(conn, "select sum(col) from 
> test")); // wrong !
>                 System.out.println(getJdbcType(conn, "select cast(sum(col) 
> as BIGINT) from test")); // workarround
>             } finally {
>                 conn.close();
>             }
>         } catch (SQLException ex) {
>             throw new RuntimeException(ex);
>         }
>         
>     }
> *
> The result is:*
> -5 // java.sql.BIGINT
> 3  // java.sql.DECIMAL
> -5 // java.sql.BIGINT
>
> *Expected result:*
> -5 // java.sql.BIGINT
> -5 // java.sql.BIGINT
> -5 // java.sql.BIGINT
>
>
> I think it is a bug, the mapped JDBC type returned by ResultSetMetaData 
> should not change for all queries.
>
> Regards,
> Cédric.
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.


Reply via email to