Hi all,

>>> if I do this (there is 1 row already in the table)
>>>
>>> ALTER TABLE mytable add test Integer DEFAULT 0 NOT NULL; commit;

>> The default value you specified is only used if you are inserting a
>> new record and if your insert statement does not contain the
>> test-field.

Except when you add a NOT NULL field. However, you should do another
test to make sure (see below).

> SQL> ALTER TABLE tblsystemstatus add test Integer DEFAULT 0 NOT NULL;
> SQL> commit;
> SQL> select * from tblsystemstatus;
>
> LOGLIST TEST
> ==================================== ============
> ABCD 0

In your case this 0 is probably real. However, suppose you would have
added a NOT NULL field without specifying a default value. In that
case, select * from tblsystemstatus would return this set:

LOGLIST TEST
==================================== ============
ABCD 0

Looks pretty much the same, doesn't it? But there's a difference: in
the first case (NOT NULL with default), the 0 is really there. In the
second case (no default), the field is <null> and Firebird lies to you
about its contents.

To make 100% sure, execute one or more of the following queries:

select * from tblsystemstatus where abcd is null
select * from tblsystemstatus where abcd is not null
select abcd+3 from tblsystemstatus

If abcd is null, the first set will show the same record as before
(with phoney 0 value!), the second will be empty and the third will
show 0 for abcd+3.

If abcd is not null, the first set will be empty, the second will show
the same record as before (with genuine 0 value), and the third will
show 3 for abcd+3.


Personally I consider this a huge bug, but I suspect it's really a
"feature" based on the SQL standard, which states (or at least
suggests) that a NOT NULL field may never return a NULL.

I did extensive tests on this in all Firebird versions plus IB6
because I'm preparing a major update of the Firebird Null Guide. I
haven't reported anything as a bug (yet).


HTH,

Paul Vinkenoog