Hibernate error from Projections.sum when sum is too large for field type

Question

I am trying to use Projection to summarize a size field when grouping by role. The criteria work fine, unless the amount no longer fits the size field type. The database in which I use MySQL returned a larger type for the sum when I ran the generated query directly to the database, but I get an exception from Hibernate. The size field is long in Java and BIGINT in MySQL. In any case, in order to force Hibernate to return a larger type, it seems to be trying to force the sum to enter a field type, even if the database returns it in a larger type.

The projection portion of the criteria looks like this: List<Object[]> roleSummaries = session.createCriteria(PhysicalDisk.class) .setProjection(Projections.projectionList() .add(Projections.groupProperty(PhysicalDisk_.role), "role") .add(Projections.rowCount(), "count") .add(Projections.sum(PhysicalDisk_.totalBytes), "space") ) .createCriteria(PhysicalDisk_.raidGroup, "raidGroup") .createCriteria("raidGroup." + RAIDGroup_.plex, "plex") .add(Restrictions.eq("plex." + Plex_.diskAggregate, diskAggregate)) .list(); 

and the exception that I get:

 Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLDataException: '12000010002048860160' in column '3' is outside valid range for the datatype BIGINT. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.6.0_21] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) ~[na:1.6.0_21] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) ~[na:1.6.0_21] at java.lang.reflect.Constructor.newInstance(Constructor.java:513) ~[na:1.6.0_21] at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) ~[mysql-connector-java.jar:na] at com.mysql.jdbc.Util.getInstance(Util.java:384) ~[mysql-connector-java.jar:na] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1027) ~[mysql-connector-java.jar:na] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) ~[mysql-connector-java.jar:na] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984) ~[mysql-connector-java.jar:na] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929) ~[mysql-connector-java.jar:na] at com.mysql.jdbc.ResultSetImpl.throwRangeException(ResultSetImpl.java:7970) ~[mysql-connector-java.jar:na] at com.mysql.jdbc.ResultSetImpl.parseLongAsDouble(ResultSetImpl.java:7254) ~[mysql-connector-java.jar:na] at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:2944) ~[mysql-connector-java.jar:na] at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:2909) ~[mysql-connector-java.jar:na] at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:3021) ~[mysql-connector-java.jar:na] at org.apache.commons.dbcp.DelegatingResultSet.getLong(DelegatingResultSet.java:278) ~[commons-dbcp.jar:1.4] at org.apache.commons.dbcp.DelegatingResultSet.getLong(DelegatingResultSet.java:278) ~[commons-dbcp.jar:1.4] at org.hibernate.type.descriptor.sql.BigIntTypeDescriptor$2.doExtract(BigIntTypeDescriptor.java:61) ~[hibernate-core.jar:3.6.0.Final] at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64) ~[hibernate-core.jar:3.6.0.Final] at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253) ~[hibernate-core.jar:3.6.0.Final] at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:249) ~[hibernate-core.jar:3.6.0.Final] at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:234) ~[hibernate-core.jar:3.6.0.Final] at org.hibernate.loader.criteria.CriteriaLoader.getResultColumnOrRow(CriteriaLoader.java:148) ~[hibernate-core.jar:3.6.0.Final] at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:639) ~[hibernate-core.jar:3.6.0.Final] at org.hibernate.loader.Loader.doQuery(Loader.java:829) ~[hibernate-core.jar:3.6.0.Final] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274) ~[hibernate-core.jar:3.6.0.Final] at org.hibernate.loader.Loader.doList(Loader.java:2533) ~[hibernate-core.jar:3.6.0.Final] at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276) ~[hibernate-core.jar:3.6.0.Final] at org.hibernate.loader.Loader.list(Loader.java:2271) ~[hibernate-core.jar:3.6.0.Final] at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119) ~[hibernate-core.jar:3.6.0.Final] at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716) ~[hibernate-core.jar:3.6.0.Final] at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347) ~[hibernate-core.jar:3.6.0.Final] at com.netapp.dfm.entity.storage.WAFLDiskEntityManager.findDiskPhysicalSummaryForCluster(WAFLDiskEntityManager.java:153) ~[dfm-data-access.jar/:na] at com.netapp.dfm.entity.storage.WAFLDiskEntityManager$$FastClassByCGLIB$$707d513f.invoke(<generated>) ~[cglib-nodep.jar:na] at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191) ~[cglib-nodep.jar:na] ... 

Answer 1

Beware: NOT TESTED

Projections.sum() implementation is as follows:

 return new AggregateProjection("sum", propertyName); 

And AggregateProjection uses the property type as the type returned by the function (sum)

Instead, you can use your own subclass of AggregateProjection , where the getTypes method getTypes overridden to return new Type[] {BigIntegerType.INSTANCE} instead of the type of the summed property.

Answer 2

The problem is that the database is trying to combine the sum of the BIGINT fields with the BIGINT, which is too small to contain a value (which also does not fit in java.lang.Long).

To get around this problem, you can replace the projection of the sum as follows:

 Projections.sqlProjection( "cast(sum({alias}.PHYSICAL_DISK) as number(30)) as SPACE", new String[] { "SPACE" }, new Type[] { BigIntegerType.INSTANCE }) 

(assuming the "physical disk" property appears in the "PHYSICAL_DISK" column in your table).

Please note: I tested this only in H2, not in MySQL, but considering that MySQL has a translation operator, the principle should be the same.

Answer 3

Hibernation changed the behavior in reverse types for Projections.sum between versions. If you use a version of Hibernate prior to 3.5, sum will return an integer. 3.5 and above, it will return Long. See this post for more information. If you are not at 3.5, upgrading to a newer version may ease your problem.