Possible NULL in NOT NULL-Field [CORE6416]

[firebird-automations]

Submitted by: Maxim Kuzmin (cybermax)

Steps for reproduce:
1. Create tables.
CREATE TABLE TEST_TABLE (
ID INTEGER NOT NULL,
NEW_FIELD INTEGER
);

CREATE TABLE TEST_TABLE_2 (
ID INTEGER NOT NULL
);

SET TERM ^ ;

CREATE OR ALTER TRIGGER TEST_TABLE_2_AI0 FOR TEST_TABLE_2
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
INSERT INTO TEST_TABLE(id) values(http://new.id);
END
^

SET TERM ; ^

2. Execute insert record, but rollback the transaction.
INSERT INTO TEST_TABLE_2(ID) VALUES (1)

3. Set NEW_FIELD as NOT NULL:
ALTER TABLE TEST_TABLE ALTER NEW_FIELD SET NOT NULL

4. Execute insert record and commit the transaction.
INSERT INTO TEST_TABLE_2(ID) VALUES (1)

5. Check values:
SELECT * FROM TEST_TABLE:
ID NEW_FIELD
1 <NULL>

[firebird-automations]

Commented by: @pavel-zotov

I get value = 0 (zero) instead of null for this example.
And if change type of new_field to date / timestamp then get 17-nov-1858
For boolean get <false>.
For varchar get chr(0).
No such problems on 4.0.

C:\FBTESTING\qa\misc>C:\FB\30Cs\isql -q -z -i c6416.sql
ISQL Version: WI-V3.0.6.33328 Firebird 3.0

shell del C:\temp\tmp4test.fdb 2>nul;
create database 'localhost:C:\temp\tmp4test.fdb' user sysdba password 'masterkey';
Server version:
WI-V3.0.6.33328 Firebird 3.0
WI-V3.0.6.33328 Firebird 3.0/tcp (Image-PC1)/P15:C
WI-V3.0.6.33328 Firebird 3.0/tcp (Image-PC1)/P15:C

create table test_audit (
id integer not null
,new_field_n integer
,new_field_d date
,new_field_t timestamp
,new_field_b boolean
,new_field_c varchar(10)
);

create table test_work (
id integer not null
);

set term ^ ;
create or alter trigger trg_test_work_ai for test_work active after insert position 0
as
begin
insert into test_audit(id) values(http://new.id);
end
^
set term ; ^
commit;

-- 2. execute insert record, but rollback the transaction.
insert into test_work(id) values (1);
rollback;

-- 3. set new_field as not null:
alter table test_audit alter new_field_n set not null;
alter table test_audit alter new_field_d set not null;
alter table test_audit alter new_field_t set not null;
alter table test_audit alter new_field_b set not null;
alter table test_audit alter new_field_c set not null;

-- 4. execute insert record and commit the transaction.
insert into test_work(id) values (1);

-- 5. check values:
set list on;
select
id
,new_field_n
,new_field_d
,new_field_t
,new_field_b
,ascii_val(new_field_c) as new_field_c_ascii_val
from test_audit;

ID 1
NEW_FIELD_N 0
NEW_FIELD_D 1858-11-17
NEW_FIELD_T 1858-11-17 00:00:00.0000
NEW_FIELD_B <false>
NEW_FIELD_C_ASCII_VAL 0

[firebird-automations]

Commented by: Maxim Kuzmin (cybermax)

I'm testing your example on 4.0.2076 and have a same result - nulls in altered fields.
Maybe isql autoconvert NULL's to default values? Backup and stop restore at this table shows, what in record really NULL's.

[firebird-automations]

Commented by: @pavel-zotov

On 4.0.0.2225 result is expected:

C:\FBTESTING\qa\misc>C:\FB\40SS\isql -q -z -i c6416.sql
ISQL Version: WI-V4.0.0.2225 Firebird 4.0 Release Candidate 1

shell del C:\temp\tmp4test.fdb 2>nul;
create database 'localhost:C:\temp\tmp4test.fdb' user sysdba password 'masterkey';
Server version:
WI-V4.0.0.2225 Firebird 4.0 Release Candidate 1
WI-V4.0.0.2225 Firebird 4.0 Release Candidate 1/tcp (Image-PC1)/P16:C
WI-V4.0.0.2225 Firebird 4.0 Release Candidate 1/tcp (Image-PC1)/P16:C

create table test_audit (
. . .
)
-- 2. execute insert record, but rollback the transaction.
insert into test_work(id) values (1);
rollback;

-- 3. set new_field as not null:
alter table test_audit alter new_field_n set not null;
alter table test_audit alter new_field_d set not null;
alter table test_audit alter new_field_t set not null;
alter table test_audit alter new_field_b set not null;
alter table test_audit alter new_field_c set not null;

-- 4. execute insert record and commit the transaction.
insert into test_work(id) values (1);
Statement failed, SQLSTATE = 23000
validation error for column "TEST_AUDIT"."NEW_FIELD_N", value "*** null ***"
-At trigger 'TRG_TEST_WORK_AI' line: 4, col: 5

[firebird-automations]

Commented by: @pavel-zotov

... and on 4.0.0.2076 result is:

-- 5. check values:
set list on;
select
id
,new_field_n
,new_field_d
,new_field_t
,new_field_b
,ascii_val(new_field_c) as new_field_c_ascii_val
from test_audit;

ID 1
NEW_FIELD_N 0
NEW_FIELD_D 1858-11-17
NEW_FIELD_T 1858-11-17 00:00:00.0000
NEW_FIELD_B <false>
NEW_FIELD_C_ASCII_VAL 0

-- but NOT nulls.

[firebird-automations]

Commented by: Maxim Kuzmin (cybermax)

Pavel Zotov, you are right, test script in isql shows not nulled values. But I added drop not null and get next result:
4.5. alter table test_audit alter new_field_n drop not null;
alter table test_audit alter new_field_d drop not null;
alter table test_audit alter new_field_t drop not null;
alter table test_audit alter new_field_b drop not null;
alter table test_audit alter new_field_c drop not null;
commit;

Cmd:
c:\Program Files (x86)\Firebird\Firebird_3_0>isql -q -z -i c6416.sql
ISQL Version: WI-V3.0.6.33328 Firebird 3.0
Server version:
WI-V3.0.6.33328 Firebird 3.0
WI-V3.0.6.33328 Firebird 3.0/tcp (SVN)/P15:C
WI-V3.0.6.33328 Firebird 3.0/tcp (SVN)/P15:C

ID 1
NEW_FIELD_N <null>
NEW_FIELD_D <null>
NEW_FIELD_T <null>
NEW_FIELD_B <null>
NEW_FIELD_C_ASCII_VAL <null>

isql by yourself replace a NULL's to default value.
Also, after execute script can you connect to database via other tool (like a IBExpert), execute "select * from test_audit" and make sure, what isql deceive.

[firebird-automations]

Commented by: Maxim Kuzmin (cybermax)

Pavel Zotov,

I'm tested other version.
For FB 4, in version 4.0.0.2076 reproduced, in version 4.0.0.2225 not reproduced. According to your results.
For FB 3, reproduced in last snapshot FB 3.0.7.33374.

[firebird-automations]

Commented by: @pavel-zotov

> But I added drop not null and get next result:
> 4.5. alter table test_audit alter new_field_n drop not null;
. . .
> Cmd:
> c:\Program Files (x86)\Firebird\Firebird_3_0>isql -q -z -i c6416.sql
. . .
> ID 1
> NEW_FIELD_N <null>

You have initially pointed that problem relates to field that is declared as NOT null.
For what here to do 'DROP not null' ? We get expected result here, isn't ?

[firebird-automations]

Commented by: Maxim Kuzmin (cybermax)

> For what here to do 'DROP not null' ? We get expected result here, isn't ?
This command need for stop masking NULLs by isql.
You executing "select * from test_audit" from other tool or did backup/restore this database? This will remove all questions.

Log from restore:
gbak:opened file c:\temp\tmp4test.fbk
gbak:transportable backup -- data in XDR format
gbak: backup file is compressed
gbak:backup version is 10
gbak:created database c:\temp\tmp4test2.fdb, page_size 8192 bytes
gbak:started transaction
gbak:restoring domain RDB$1
gbak:restoring domain RDB$2
gbak:restoring domain RDB$3
gbak:restoring domain RDB$4
gbak:restoring domain RDB$5
gbak:restoring domain RDB$6
gbak:restoring domain RDB$7
gbak:committing metadata
gbak:restoring table TEST_AUDIT
gbak: restoring column NEW_FIELD_T
gbak: restoring column ID
gbak: restoring column NEW_FIELD_N
gbak: restoring column NEW_FIELD_D
gbak: restoring column NEW_FIELD_C
gbak: restoring column NEW_FIELD_B
gbak:restoring table TEST_WORK
gbak: restoring column ID
gbak:committing metadata
gbak:restoring data for table TEST_WORK
gbak: 3 records restored
gbak:restoring data for table TEST_AUDIT
gbak: ERROR:validation error for column "TEST_AUDIT"."NEW_FIELD_N", value "*** n
ull ***"
gbak: ERROR: warning -- record could not be restored
gbak: ERROR: Exiting before completion due to errors
gbak:Exiting before completion due to errors

[firebird-automations]

Commented by: @dyemanov

Re-connect between steps (3) and (4) should cure the issue. AFAIU, trigger TEST_TABLE_2_AI0 caches the original definition of the table TEST_TABLE and doesn't see the added constraint. As soon as this trigger is reloaded inside the matadata cache, the problem disappears.

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Commented by: @dyemanov

In FB4 it was fixed together with CORE6382.