CTAS. Nullability in case when column content is gathered from right part of OUTER join (which always may return null)

[Pavel Zotov]

Please consider script:

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

set bail on;
set list on;
set blob all;
 
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb 2>nul;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
 
create domain dm_int_nullable int;
create domain dm_int_NOT_null int not null;
commit;

create table tbase (
    fld_not_null_explicit int not null
   ,fld_not_null_owr_domain dm_int_nullable not null
   ,fld_not_null_via_domain dm_int_NOT_null
);
 
create table ctas_test as(
    select
        b.fld_not_null_explicit as lj_fld_not_null_explicit
       ,b.fld_not_null_owr_domain as lj_fld_not_null_owr_domain
       ,b.fld_not_null_via_domain as lj_fld_not_null_via_domain
    from rdb$database d
    left join tbase b on 1=1
) with no data;

set echo on;
show table ctas_test;
quit;

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

Its output on 6.0.0.2074-7df850d will be:

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

show table ctas_test;
Table: PUBLIC.CTAS_TEST
LJ_FLD_NOT_NULL_EXPLICIT        INTEGER Nullable
LJ_FLD_NOT_NULL_OWR_DOMAIN      (PUBLIC.DM_INT_NULLABLE) INTEGER Not Null
LJ_FLD_NOT_NULL_VIA_DOMAIN      (PUBLIC.DM_INT_NOT_NULL) INTEGER Not Null

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

So,CTAS is created using query with OUTER join that may return NULLs in every its column. But two of fields are NOT null.

Is it expected ?

[Adriano dos Santos Fernandes]

On 7/16/26 10:44, Pavel Zotov wrote:
>
> Please consider script:
> ============
> set bail on;
> set list on;
> set blob all;
>  
> shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb 2>nul;
> create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password
> 'masterkey';
>  
> create domain dm_int_nullable int;
> create domain dm_int_NOT_null int not null;
> commit;
>
> create table tbase (
>     fld_not_null_explicit int not null
>    ,fld_not_null_owr_domain dm_int_nullable not null
>    ,fld_not_null_via_domain dm_int_NOT_null
> );
>  
> create table ctas_test as(
>     select
>         b.fld_not_null_explicit as lj_fld_not_null_explicit
>        ,b.fld_not_null_owr_domain as lj_fld_not_null_owr_domain
>        ,b.fld_not_null_via_domain as lj_fld_not_null_via_domain
>     from rdb$database d

>     *left* join tbase b on 1=1

> ) with no data;
>
> set echo on;
> show table ctas_test;
> quit;
> ============
>
> Its output on 6.0.0.2074-7df850d will be:
> ============
> show table ctas_test;
> Table: PUBLIC.CTAS_TEST
> LJ_FLD_NOT_NULL_EXPLICIT        INTEGER Nullable

> LJ_FLD_NOT_NULL_OWR_DOMAIN      (PUBLIC.DM_INT_NULLABLE) INTEGER *Not* Null
> LJ_FLD_NOT_NULL_VIA_DOMAIN      (PUBLIC.DM_INT_NOT_NULL) INTEGER *Not* Null

>  ============
>
> So,CTAS is created using query with OUTER join that may return NULLs in
> every its column. But two of fields are NOT null.
>

The case of fld_not_null_owr_domain is easily fixable.

But fld_not_null_via_domain is different. A NOT NULL domain cannot be
nullified when used. And views have this same exact problem with NOT
NULL field and outer join, as views also inherits field's domain.

If there are no better option, I believe domain name copy should be
switched to its underlying type copy in this case.


Adriano

[Pavel Zotov]

But fld_not_null_via_domain is different. A NOT NULL domain cannot be
nullified when used. And views have this same exact problem with NOT
NULL field and outer join, as views also inherits field's domain.

If there are no better option, I believe domain name copy should be
switched to its underlying type copy in this case.

Am i right in guess that you speak about  replacing " dm_int_NOT_null" with just "int" (nullable) for this field ?

 

[Adriano dos Santos Fernandes]

On 7/16/26 20:53, Pavel Zotov wrote:
> Am i right in guess that you speak about  replacing " dm_int_NOT_null"
> with just "int" (nullable) for this field ?

Yes.


Adriano

[Pavel Zotov]

Fix confirmed, test has been committed.