Hi all. I've checked the documentation, the FAQs, searched the forums, and googled it, but I can't seem to find a solution to the problem I'm facing, so I would really appreciate any help some of you with a bit more experience with Hibernate could give me.

I am performing a very simple query where I sum() a byte (tinyint) field. However, obviously the result could easily be out of range, which is exactly the problem I'm having. It appears that the result is returned as the type of the field queried. Is this the intended functionality? Am I missing something?

Thanks!

Hibernate version:
2.1

Mapping documents:
<property name="level" column="level" type="byte" not-null="true"/>

Full stack trace of any exception that occurs:
java.sql.SQLException: Value '229' is out of range [-127,127]
at com.mysql.jdbc.ResultSet.getByte(ResultSet.java:681)
at com.mysql.jdbc.ResultSet.getByte(ResultSet.java:713)
at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getByte(DelegatingResultSet.java:230)
at net.sf.hibernate.type.ByteType.get(ByteType.java:21)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.hql.QueryTranslator.getResultColumnOrRow(QueryTranslator.java:1000)
at net.sf.hibernate.loader.Loader.getRowFromResultSet(Loader.java:238)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:281)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1553)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1530)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1526)
...

Name and version of the database you are using:
MySQL 3.23.54

The generated SQL (show_sql=true):
select sum(userhero0_.level) as x0_0_ from user_her
oes userhero0_ where (userhero0_.user_id=? )

Debug level Hibernate log excerpt:

2005-02-17 23:35:45,910 [http-8080-Processor24] DEBUG - HQL: select sum(uh.level) from com.octopi.dal.UserHero a
s uh where uh.user.id = ?
2005-02-17 23:35:45,910 [http-8080-Processor24] DEBUG - SQL: select sum(userhero0_.level) as x0_0_ from user_her
oes userhero0_ where (userhero0_.user_id=? )
2005-02-17 23:35:45,910 [http-8080-Processor24] DEBUG - about to open: 0 open PreparedStatements, 0 open ResultS
ets
2005-02-17 23:35:45,910 [http-8080-Processor24] DEBUG - select sum(userhero0_.level) as x0_0_ from user_heroes u
serhero0_ where (userhero0_.user_id=? )
2005-02-17 23:35:45,911 [http-8080-Processor24] DEBUG - preparing statement
2005-02-17 23:35:45,911 [http-8080-Processor24] DEBUG - binding '1' to parameter: 1
2005-02-17 23:35:45,912 [http-8080-Processor24] DEBUG - processing result set
2005-02-17 23:35:45,912 [http-8080-Processor24] DEBUG - result row:
2005-02-17 23:35:45,912 [http-8080-Processor24] DEBUG - SQL Exception

The sum() function passes the same type as a result of its operation to the result processor. Yes you can go over the size limits of the type.

Wouldn't you say then, that it makes sum() a fairly unsafe function to use? It seems as if I'll need to change my underlying type to an integer just to avoid the exception, when I know it will never hold a value above 12. I mean, the way it works now, I can't imagine ever feeling reasonable assured it would work...

It would not matter what type you use the potential is there to overflow the type's storage size. There are alternatives but to me increasing the database' storage size is the best option. In many cases, its more efficient if you use main storage type for the underlying hardware architecture, eg, Integer could be more effcient though its hard to measure.