Dmitry,

    Thanks for your answer.

    Also note that when a not null field is created with a default value ( test4 ), not only any select returns its default value but also the engine considers it in compare statements as if it contained the default value.

    This is also true if you decide to change the default value afterwards: compare statements will vary accordingly.

    Example

alter table table1 add test4 integer default 4 not null;

select distinct test4, iif(test4 = 4,1,0) as t4 from table1;

Result:
 test4  t4
4         1

If you change the default value afterwards:

alter table t1 alter column test4 set default 5;

select distinct test4, iif(test4 = 5,1,0) as t4 from table1;

Result:
 test4  t4
5         1

In other words, this has the same effect as if the field value changed when you changed its default value.

Extending Helen advice, whenever you add or change constraints related to the definition of a field using a DDL statement, you must update the value of that field in all records by means of a DML statement in order to ensure that no inconsistent data is saved or shown, whichever client library you use.

Thanks,
Aldo Caruso

 

10.02.2018 22:33, Aldo Caruso wrote:
>
> A strange behavior is seen in the combination not null and no default
> value. It is returned as a 0 for selects but treated as a NULL when
> comparing.

In fact, the engine returns NULL. But query prepare describes the output
descriptor as NOT NULL. Some connectivity layers (including ISQL, IIRC)
get fooled, as NULL is not expected from a NOT NULL descriptor, and zero
/ empty string is returned. I recall that IBExpert is able to return
NULL in this case.

Dmitry