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.
