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
This is also true if you decide to change the default value
afterwards: compare statements will vary accordingly.
alter table table1 add test4 integer default 4 not null;
select distinct test4, iif(test4 = 4,1,0) as t4 from table1;
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;
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.
El 11/02/18 a las 02:40, Dmitry Yemanov dim...@users.sourceforge.net
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
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.