Integer out of range problem

[Javier]

Hi all,

I'm facing a 90110-103 error with H2 when performing an statement like
select avg(int_field) from some_table.

I've tried run the statement by replacing max(int_field), min
(int_field) and it works. The max value of int_field is 997920 and its
lowest value is 1. The table has 6 million records (1.5 gb database
and 950 M of index file).

Of course, int_field is defined as INTEGER NOT NULL.

Thoughts ?

Regards

[Javier]

Solved.

The solution is change the statement to select avg(cast(int_field as
bigint)) from some_table.

Is this an expected condition ? If int_field is defined as integer....
while cast to bigint ?

Thoughts ?

[Thomas Mueller]

Hi,

> The solution is change the statement to select avg(cast(int_field as
> bigint)) from some_table.
>
> Is this an expected condition ? If int_field is defined as integer....
> while cast to bigint ?

It's not nice... Some database solve this problem (HSQLDB as far as I
know) while others don't (MS SQL Server, not sure what version). I
will try to solve this for the next release, but I'm not sure yet if I
can for all data types.

Regards,
Thomas

[Thomas Mueller]

Hi,

The problem for AVG will be fixed in the next release. There is a
problem for SUM as well: it can overflow when using large values. In
the next release, SUM will return a data type that is safe. I hope
this will not break applications that call ResultSet.getObject and
expect the old data type.

Regards,
Thomas

[Javier]

Ok. Sounds ok for me.

My solution was change int_field from integer type to bigint type. The
cast is not needed anymore.

Thanks.

On Nov 20, 12:17 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote: