Thread: int4, int8, real ....division...

pgsql-7.4.2

I think it's not a bug, just interesting results:

SELECT   9223372036854775807/(365*10000000)  ;
result: -14300526699
wrong

SELECT   ( 9223372036854775807/365 )/10000000  ;
result: 2526951242
good

SELECT   ( 9223372036854775807::real /(365*10000000) )  ;
result: -14300526699,6589
wrong

SELECT   ( 9223372036854775807 /(365*10000000)::real )  ;
result: -14300526699,6589
wrong

SELECT   ( 9223372036854775807 /(365*10000000)::real )  ;
result: -14300526699,6589
wrong

SELECT   ( 9223372036854775807/(365*10000000::real) )  ;
result:  2526951242,97391
good

SELECT   ( 9223372036854775807/(365::real*10000000) )  ;
result:  2526951242,97391
good



REASON IS:

SELECT 365*10000000 ;
result: -644967296
wrong

but

SELECT 365*10000000::int8 ;
result: 3650000000
good



If this operation embended in complex expression,
then very difficult to find the reason of computation error.
This misstake comes only after the border of int4 and int8.


-------------------------------------------------
Gyenese Pál Attila
számítástechnikai vezető
MEDIAGNOST KFT.
1106 Fehér út 10
Tel.: 431.87.74
Fax.: 265.20.73

"Gyenese Pál Attila" <gyenese@mediagnost.hu> writes:
> REASON IS:

> SELECT 365*10000000 ;
> result: -644967296
> wrong

This isn't a division problem --- the difficulty is there's no check for
overflow in int4 multiplication.  (Nor in any of the other integer
arithmetic operations, for that matter.)

It'd be nice if C made it easier to detect integer overflow :-(
... AFAIK, testing this would make those subroutines many times slower,
which is pretty annoying when the hardware already knows whether the
result overflowed or not.
        regards, tom lane

Tom Lane wrote:
> This isn't a division problem --- the difficulty is there's no check for
> overflow in int4 multiplication.  (Nor in any of the other integer
> arithmetic operations, for that matter.)

It seems to me that SQL2003, Section 6.26 (<numeric value expression>,
"General Rules", item 5) requires that we check for overflow after
arithmetic operations on exact numeric types:

%%%%
5) If the most specific type of the result of an arithmetic operation is
exact numeric, then

Case:

a) If the operator is not division and the mathematical result of the
operation is not exactly representable with the precision and scale of
the result data type, then an exception condition is raised: data
exception — numeric value out of range.

b) If the operator is division and the approximate mathematical result
of the operation represented with the precision and scale of the result
data type loses one or more leading significant digits after rounding or
truncating if necessary, then an exception condition is raised: data
exception — numeric value out of range. The choice of whether to round
or truncate is implementation-defined.
%%%%

Or am I misreading the spec?

-Neil

Neil Conway <neilc@samurai.com> writes:
> Tom Lane wrote:
>> This isn't a division problem --- the difficulty is there's no check for
>> overflow in int4 multiplication.  (Nor in any of the other integer
>> arithmetic operations, for that matter.)

> Or am I misreading the spec?

Spec compliance is not the issue here, the issue is devising a
reasonable implementation ...

            regards, tom lane