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
El 11/02/18 a las 02:40, Dmitry Yemanov dim...@users.sourceforge.net
[firebird-support] escribió:
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
