Thanks for your answer.

I made some tests and found some strange exceptions to the rules you
described.

Consider the following DDL sentences, which create four fields with the
for possible combinations for default value and not null constraint:

alter table table1 add test1 integer;
alter table table1 add test2 integer not null;
alter table table1 add test3 integer default 3;
alter table table1 add test4 integer default 4 not null;

Given the following select sentence:

select distinct test1, test2, test3, test4,
  iif(test1 is null,1,0) as t1,
  iif(test2 is null,1,0) as t2,
  iif(test3 is null,1,0) as t3,
  iif(test4 is null,1,0) as t4
  from table1;

you get the following row:

test1    test2  test3     test4  t1   t2  t3   t4
[null]    0        [null]     4        1    1    1    0

So it seems that, when there is no not null constraint (test1 and
test3), the engine returns null in select clauses as well as when
comparing values. The default value of t3 is not used.
On the other hand, when there is a not null constraint, you have two cases:
  there is a default (test4): The engine uses this default for selects
and when comparing values.
  there is no default (test2): It returns 0 for selects but uses null
value for comparing operations.

Of course, we are always speaking in the case that no field value exists
in the returned record version.

As you can see, default value are only assumed when there is a not null
constraint.
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.

Thanks for any answer.
Aldo Caruso


El 10/02/18 a las 13:48, Dimitry Sibiryakov sd@...
[firebird-support] escribió:

> 10.02.2018 16:32, Aldo Caruso aldo.caruso@... [firebird-support] wrote:
>> I discovered that when a field is added to a table with a NOT NULL
>> constraint and a default value, it is automatically filled with that
>> default value.
> No, it isn't. It would be too slow.
> Default value is returned by select if no field value exists in returned record
> version. Engine works about this way:
> 1) Prepare buffer for returned values and fill it with default values.
> 2) Replace values in the buffer with values extracted from storage.
> 3) Send the buffer to client side.
>
>