0

I have a field wth the type- SMALLINT(5) in my table in mysql database.

When i try to do a aggregate operation (sum()) on the field using projections, I get the following exception

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLDataException: 
'8.0042892E7' in column '1' is outside valid range for the datatype SMALLINT.

I understood it as "aggregate operations results are returned in the same datatype in which it is stored". iS my assumption correct.

If so,Is it possible for me to cast the return value to a long value or a String

juan
  • 343
  • 3
  • 5
  • 16

1 Answers1

2

In fact, your assumption is right, that the aggregate operation result returns the same datatype in which the attribute is stored. Of course that's why you see the error message "outside valid range": a SMALLINT is between -32768 and 32767 signed, or 0 and 65535 unsigned. 8.0042892E7 is far to large for this data type.

However it is possible to cast the result to an INT:

CAST(SUM(whatever) as INT)

For an INT the range is [-2147483648, 2147483647] for signed integer, and [0, 4294967295] for unsigned.

Peter Keller
  • 7,526
  • 2
  • 26
  • 29
  • I have tried running your suggestion as Query and it works, is there an equivalent way of writing this using criteria.setprojection – juan Mar 08 '14 at 04:13
  • i never tried it. HQL supports CAST if the underlying database supports it. According to http://stackoverflow.com/questions/4269389/using-jpa-2-0-criteria-api-and-cast-causes-generated-jpql-to-fail-in-hibernate using it with the Criteria API fails at least for older Hibernate versions. – Peter Keller Mar 08 '14 at 06:24