Re: [Firebird-devel] Wrong rdb$field_sub_type for numeric computed column

[Mark R. <ma...@la...>]

On Wed, 18 Jan 2012 23:08:36 +0100, "Ivan Prenosil"
<iva...@se...> wrote:
>> I have a table:
>> recreate table test (
>>  base numeric(15,4),
>>  test1 computed by (base+base),
>>  test2 computed by (cast(base+base as numeric(15,4))),
>>  foo bigint,
>>  bar numeric(15,4)
>> );
>> 
>> If I run the query:
>> select rf.RDB$FIELD_NAME, f.RDB$FIELD_SCALE, f.RDB$FIELD_TYPE,
>> f.RDB$FIELD_SUB_TYPE
>> from RDB$RELATION_FIELDS rf inner join RDB$FIELDS f on
>> (rf.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME)
>> where rf.RDB$RELATION_NAME = 'TEST'
>> it reports:
>> BASE                           -4 16 1
>> TEST1                          -4 16 0
>> TEST2                          -4 16 1
>> FOO                            0 16 0
>> BAR                            -4 16 1
>> 
>> Why is rdb$field_sub_type for test1 column "0"? Then it looks like
>> bigint. Checking the rdb$field_scale could be a solution, but is it
>> then numeric or decimal?
> 
> I would say it is expected. Numeric expressions generally do not
preserve
> data types exactly. E.g.
>   SmallintField + 1 -> result is Bigint
>   NumericField(9, 2) + 1 -> result is .. ehm ... Num/Dec(18, 2)  (API
will
>   return zero as subtype)

I think it shouldn't, if something is defined as a DECIMAL or NUMERIC it
should specify subtype 2 or 1, never 0. And if it has subtype 0 it should
not be allowed to have a scale other than 0.

> Or consider this - what (sub)type do you think should return this
> expression ?
>   NumericField(15, 4) + DecimalField(15, 4)

I would expect DECIMAL(15,4) or maybe DECIMAL(16,4) to allow for overflow
as according to the SQL spec NUMERIC has a precision of exactly the
specified number, while DECIMAL has a precision of the specified number or
higher.

Mark