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

[Pavel Zotov]

> Are these differences expected ?

Some are.

I did some fixes, but read doc:
---
When a select list item 

... [ skipped ] ...

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.
---

Unfortunately, i still can't understand result of one more  example:

================

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
;

set term ^; execute block as begin rdb$set_context('USER_SESSION','SHOW_FOR_TABLE', 'CTAS_TEST'); end ^ set term ;^
commit;

/*
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref50/firebird-50-language-reference.html#fblangref50-datatypes
Precision   Data type            Dialect 3
 1 ...  4   NUMERIC              SMALLINT
 1 ...  4   DECIMAL              INTEGER
 5 ...  9   NUMERIC or DECIMAL   INTEGER
10 ... 18   NUMERIC or DECIMAL   BIGINT
19 ... 38   NUMERIC or DECIMAL   INT128
*/

-- ###########################
-- ###   l i t e r a l s   ###
-- ###########################
-- "... the new column's datatype is derived from the query result."
recreate table ctas_test as (
    select
         0 as lit_016                                             -- 496 long
        ,2147483647 as lit_032                                    -- 496 long
        ,9223372036854775807 as lit_064                           -- 580 int64
        ,170141183460469231731687303715884105727 as lit_128       -- 32752 int128
        ,1e1 as lit_dbl                                           -- 480 double
        ,1.0 as lit_num_01_01                                     -- 580 scale=-1
        ,.0001 as lit_num_04_04                                   -- 580 scale=-4
        ,.00001 as lit_num_05_05                                  -- 580 scale=-5
        ,.000000001 as lit_num_09_09                              -- 580 scale=-9
        ,.0000000001 as lit_num_10_10                             -- 580 scale=-10
        ,.000000000000000001 as lit_num_18_18                     -- 580 scale=-18
        ,.00000000000000000000000000000000000001 as lit_num_38_38 -- 580 scale=-38
        ,1e-309 as lit_df34                                       -- 32762 decfloat
     from rdb$database
);

commit;
select * from v_fields_info;

================

Output:

RF_FLD_NAME     F_FIELD_TYPE F_FLD_SUB_TYPE F_FLD_PREC F_FLD_SCALE
=============== ============ ============== ========== ===========
LIT_016                    8              0          0           0
LIT_032                    8              0          0           0
LIT_064                   16              0          0           0
LIT_128                   26              0          0           0
LIT_DBL                   27         <null>     <null>           0
LIT_NUM_01_01             16              0          0          -1
LIT_NUM_04_04             16              0          0          -4
LIT_NUM_05_05             16              0          0          -5
LIT_NUM_09_09             16              0          0          -9
LIT_NUM_10_10             16              0          0         -10
LIT_NUM_18_18             16              0          0         -18
LIT_NUM_38_38             16              0          0         -38
LIT_DF34                  25         <null>         34           0 

Why values in rdb$field_precision for numeric type are 0 (marked in yellow) ?

I would expect instead:  4, 4,  9, 18, 18, and 38.

 

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

Do you mean arithmetic expression or some functions like gen_id(g, 1), gen_uuid() or [crypt_]hash() ? 

 

[Pavel Zotov]

PS

And one more Q:

==========

If the source column is based on an auto-generated (implicit) domain, its exact type (precision/scale, character  set, collation, etc.) is copied

==========

Is it about RDB$nnnn domains that are generated when we create some domain / column ?

Can you please give some example for this case ?

[Adriano dos Santos Fernandes]

On 7/14/26 16:42, Pavel Zotov wrote:
>
> Why values in rdb$field_precision for numeric type are 0 (marked in
> yellow) ?
> I would expect instead:  4, 4,  9, 18, 18, and 38.
>
>  

By the same reason it is 0 in normal create table in that your other
example:

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

They are not NUMERIC nor DECIMAL field, just expressions. Expressions do
not carry exact precision.

>
> A non-field expression's type is calculated as client reports then.
>
>
> Do you mean arithmetic expression or some functions like gen_id(g, 1),
> gen_uuid() or [crypt_]hash() ? 

Non-field. Literal, sum of fields, etc.


Adriano

[Adriano dos Santos Fernandes]

On 7/14/26 18:22, Pavel Zotov wrote:
> Is it about *RDB$nnnn* domains that are generated when we create some
> domain / column ?

A (original table) column based on a builtin type that generated the
RDB$nnnn domain.

A new RDB$nnnn will be generated for the CTAS column copying the
original column type, not referencing the same RDB$nnnn domain.


Adriano