Failed "ALTER TABLE ADD <C>...." statement leads to incorrect error message in subsequent DML (with referencing to missed column <C>). Reconnect is needed to get rid of this.

[pavel-zotov]

Consider script:

shell del c:\temp\tmp4test.fdb 2>nul;
create database 'localhost:c:\temp\tmp4test.fdb' user sysdba password 'masterkey';
 
recreate table test(id int, x int, constraint test_pk primary key(id) );
insert into test(id, x) values(1, 1);
commit; ------- [ A ]
 
set echo on;
 
alter table test add y int not null; ---- [ 0 ]
rollback;
 
-- ##########################################################
-- connect 'localhost:c:\temp\tmp4test.fdb' user sysdba password 'masterkey'; ------ [ B ]
-- ##########################################################
 
insert into test(id, x) values(1, 1); ---- [ 1 ]
commit;

Statement marked as "[ 0 ]" obviously will fail because table already has one record (i.e. this is expected):

Statement failed, SQLSTATE = 22006
unsuccessful metadata update
-Cannot make field Y of table TEST NOT NULL because there are NULLs present

After this we do ROLLBACK (or COMMIT - no matter) and try to insert record with specifying only existing columns. But value for ID will violate PK constraint.

So, after statement marked as "[ 1 ]" we can expect to see:

Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "TEST_PK" on table "TEST"
-Problematic key value is ("ID" = 1)

But result will differ:

Statement failed, SQLSTATE = 23000
validation error for column "TEST"."Y", value "*** null ***"

If we do reconnect after recreate table and inserting record with ID=1 (see COMMIT marked as "[ A ]"), outcome will be:

Statement failed, SQLSTATE = 22006
unsuccessful metadata update
-Cannot make field Y of table TEST NOT NULL because there are NULLs present

Obviously, in both cases there are no column "Y" (and it was not added to this table). One need to do reconnect in such case (see commented statement "[ B ]").