Directly from my thread "Wrong rdb$field_sub_type for numeric computed column" in firebird-devel:
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'm happy with conclusion that if field_scale is < 0 and no particular
sub_type, imply i.e. numeric. Probably somebody from core devs should
tell us.
Submitted by: @cincuranet
Directly from my thread "Wrong rdb$field_sub_type for numeric computed column" in firebird-devel:
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'm happy with conclusion that if field_scale is < 0 and no particular sub_type, imply i.e. numeric. Probably somebody from core devs should tell us.
That is what is done in Jaybird right now.
See getDataType(...) and getDataTypeName(...) in http://firebird.cvs.sourceforge.net/viewvc/firebird/client-java/src/main/org/firebirdsql/jdbc/AbstractDatabaseMetaData.java?view=markup
Commits: FirebirdSQL/NETProvider@35c3ae1f602cb17ea16f60d9337a34f7b742ce27