On 26-3-2015 09:26, liviuslivius liviuslivius@...
[firebird-support] wrote:

>
>
> Hi,
> i know this kind of cast
> SELECT CAST('1.23' AS NUMERIC(10, 2)) FROM RDB$DATABASE
> but what are the rules for this (numeric without parenthesis)
> [1]
> SELECT CAST('1.23' AS NUMERIC) FROM RDB$DATABASE
> result is "1"
> [2]
> SELECT CAST('12345678.23' as numeric) FROM RDB$DATABASE
> result is "12345678"
> [3]
> SELECT CAST('123456789.23' as numeric) FROM RDB$DATABASE
> SQL Message : -802
> Arithmetic overflow or division by zero has occurred.
> Engine Code : 335544321
> Engine Message :
> arithmetic exception, numeric overflow, or string truncation
> numeric value is out of range
> [4]
> SELECT CAST('123456789' as numeric) FROM RDB$DATABASE
> result is "123456789"
> [5]
> SELECT CAST('12345678.23' as numeric) FROM RDB$DATABASE
> result is "12345678"
> [6]
> SELECT CAST('1234567890' as numeric) FROM RDB$DATABASE
> result is "1234567890"
> [7]
> SELECT CAST('12345678901' as numeric) FROM RDB$DATABASE
> SQL Message : -802
> Arithmetic overflow or division by zero has occurred.
> Engine Code : 335544321
> Engine Message :
> arithmetic exception, numeric overflow, or string truncation
> numeric value is out of range
> what are the rules and why
> as you can see [3] raise error [4][6] not

NUMERIC without scale and precision is INTEGER. See Interbase 6.0 Data
Definition Guide page 65. This allows 1234567890 to fit, even though it
has precision 10.

According to that same page, NUMERIC(9) is stored as INTEGER.
CAST('1234567890' AS NUMERIC(9)) also works even though it is precision
10, but this is caused by Firebird treating it as a normal INTEGER, and
1234567890 < 2147483647. Technically this is a bug.

This is also the reason why [7] fails: 12345678901 doesn't fit in a 32
bit integer.

Now as to why [4] fails: 123456789.23 is a NUMERIC(12,2) which is stored
as a BIGINT 12345678923. When casting to NUMERIC (or: INTEGER), the
value is first converted to a NUMERIC(9,2): as 12345678923 and scale -2,
and only then is the scale removed to convert to NUMERIC(9) (or
INTEGER): as 123456789. But 12345678923 does not fit in an INTEGER, so
the conversion is rejected at the first step.

The reason that [5] succeeds is that 1234567823 fits in an INTEGER, if
you would have used 12345678.235 it would have failed as well.
On the other hand forcing Firebird to first truncate will allow it to
pass, the following casts both work:
CAST(CAST('12345678.235' AS NUMERIC(10)) as NUMERIC)
CAST(CAST('123456789.23' AS NUMERIC(10)) as NUMERIC)

Now if this is correct behavior from the perspective of the SQL standard
I am not sure (and I currently don't have the energy to study them to
find out), but I'd guess it isn't.

Mark
--
Mark Rotteveel