CTAS. Nullability in case when column is declared via nullable domain but has explicit 'own' NOT-null flag in the declaration

[Pavel Zotov]

One more question about NULL-inheritance: should NOT_null flag be in some column of CTAS result if this column in the source table is based on NULLABLE domain but has EXPLICIT clause 'NOT NULL' in its declaration ?

This is example:

set bail on;
set list on;
shell del r:\temp\tmp4test.fdb 2>nul;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';

create domain dm_not_null int NOT null;
create domain dm_nullable int;

recreate table tbase(
    fn_implicit_nn dm_not_null
   ,fn_explicit_nn dm_nullable NOT NULL
);

recreate table ctas_test as (
    select * from tbase
);

set echo on;
show table tbase;

show table ctas_test;

Output:

show table tbase;
Table: PUBLIC.TBASE
FN_IMPLICIT_NN                  (PUBLIC.DM_NOT_NULL) INTEGER Not Null
FN_EXPLICIT_NN                  (PUBLIC.DM_NULLABLE) INTEGER Not Null

show table ctas_test;
Table: PUBLIC.CTAS_TEST
FN_IMPLICIT_NN                  (PUBLIC.DM_NOT_NULL) INTEGER Not Null
FN_EXPLICIT_NN                  (PUBLIC.DM_NULLABLE) INTEGER Nullable

[Adriano dos Santos Fernandes]

On 7/7/26 16:09, Pavel Zotov wrote:
> One more question about NULL-inheritance: should NOT_null flag be in
> some column of CTAS result if this column in the source table is based
> on NULLABLE domain but has EXPLICIT clause 'NOT NULL' in its declaration ?
> This is example:

Fixed. Thanks.


Adriano