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 s...@ibphoenix.com [firebird-support] escribió: > 10.02.2018 16:32, Aldo Caruso aldo.car...@argencasas.com [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. > >
