CTAS. Values in RDB$FIELDS.RDB$FIELD_PRECISION for numerical datatypes (integers; floating- and fixed-point ; decfloat)

[Pavel Zotov]

Please consider script:
==========
set bail on;
set blob all;
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';

set width RF_FLD_NAME 15;

create view v_fields_info as
select
    rf.rdb$field_name as rf_fld_name
    ,f.rdb$field_type as f_field_type
    ,f.rdb$field_sub_type as f_fld_sub_type
    ,f.rdb$field_precision as f_fld_prec
    ,f.rdb$field_scale as f_fld_scale
from rdb$relation_fields rf
join rdb$fields f on rf.rdb$field_source = f.rdb$field_name
where rf.rdb$relation_name = coalesce( rdb$get_context('USER_SESSION','SHOW_FOR_TABLE'), upper('CTAS_TEST'))
order by rf.rdb$field_position
;

create table tbase (
    i016 smallint
   ,i032 int
   ,i064 bigint
   ,i128 int128

   ,dec_02 decimal(2)
   ,dec_04 decimal(4)
   ,dec_05 decimal(5)
   ,dec_09 decimal(9)
   ,dec_10 decimal(10)
   ,dec_38 decimal(38)

   ,num_02 numeric(2)
   ,num_04 numeric(4)
   ,num_05 numeric(5)
   ,num_09 numeric(9)
   ,num_10 numeric(10)
   ,num_38 numeric(38)

   ,flt float
   ,dbl double precision
   ,df16 decfloat(16)
   ,df34 decfloat(34)
);

show table tbase;
set term ^; execute block as begin rdb$set_context('USER_SESSION','SHOW_FOR_TABLE', 'TBASE'); end ^ set term ;^
commit;
select * from v_fields_info;
commit;

create table ctas_test as (
    select
         cast(null as smallint) as i016
        ,cast(null as int) as i032
        ,cast(null as bigint) as i064
        ,cast(null as int128) as i128

        ,cast(null as decimal(2)) as dec_02
        ,cast(null as decimal(4)) as dec_04
        ,cast(null as decimal(5)) as dec_05
        ,cast(null as decimal(9)) as dec_09
        ,cast(null as decimal(10)) as dec_10
        ,cast(null as decimal(38)) as dec_38

        ,cast(null as numeric(2)) as num_02
        ,cast(null as numeric(4)) as num_04
        ,cast(null as numeric(5)) as num_05
        ,cast(null as numeric(9)) as num_09
        ,cast(null as numeric(10)) as num_10
        ,cast(null as numeric(38)) as num_38

        ,cast(null as float) as flt
        ,cast(null as double precision) as dbl

        ,cast(null as decfloat(16)) as df16
        ,cast(null as decfloat(34)) as df34

    from rdb$database
);

show table ctas_test;
set term ^; execute block as begin rdb$set_context('USER_SESSION','SHOW_FOR_TABLE', 'CTAS_TEST'); end ^ set term ;^
commit;
select * from v_fields_info;
quit;
==========

Its output will contain two result sets (shown in table form) - one for table 'TBASE' and another for table 'CTAS_TEST' which has exactly same columns, types and precision values as 'TBASE'.

If we compare these result sets then one may see difference in the column RDB$FIELD_PRECISION ('F_FLD_PREC' column):

(left part is resultset for 'TBASE', right - for 'CTAS_TEST').

Are these differences expected ?

[Adriano dos Santos Fernandes]

> rdb-field-precision-diff.png
>
> (left part is resultset for 'TBASE', right - for 'CTAS_TEST').
>
> Are these differences expected ?
>

Some are.

I did some fixes, but read doc:
---
When a select list item is a direct reference to a source table or view
column, the new column copies the source
column's exact datatype instead of one derived only from the query's
runtime result (which would lose information
such as declared `NUMERIC`/`DECIMAL` precision).

* If the source column is based on a named domain, the new column
references that domain directly, as if it had
been declared `<column name> <domain name>`.
* If the source column is based on an auto-generated (implicit) domain,
its exact type (precision/scale, character
set, collation, etc.) is copied.

For any other select list item (an expression, a literal, or an
aggregate, for example), the new column's datatype
is derived from the query result.
---

A non-field expression's type is calculated as client reports then.


Adriano