"default" is ignored after “alter procedure" in one script without reconnect

[nLeonardt95]

Here is an example table and procedure for this.

--DROP PROCEDURE ADDMYTABLE;
--DROP TABLE MYTABLE;
--commit;


CREATE TABLE MYTABLE (
    FIELDOLD  INTEGER
);

SET TERM ^ ;

create or alter procedure ADDMYTABLE (
    FIELDOLD integer)
as
begin
  insert into MYTABLE ( FIELDOLD)
  values ( :FIELDOLD);
end^

SET TERM ; ^

When I run the following script, it results in corrupt records in my table. What am I doing?

• I am alter the procedure to create data records. (I noticed that it doesn't matter what I change, I just have to create an old procedure and couldn't change the content of the procedure anyway).
• In the same script, I create a new field “FIELDNEW” with a default and “not null”.
• Then, at the end of the same script, I execute the procedure.

The result is that the data record is created with “NULL” in “FIELDNEW,” even though that shouldn't be possible.

However, if I reconnect after the “alter procedure,”

I don't get this effect.

I noticed this in Firebird 3.0.4, but it also continues to occur in 5.0.3.

set term ^;

create or alter procedure ADDMYTABLE (
    FIELDOLD integer)
as
begin
  insert into MYTABLE ( FIELDOLD)
  values ( :FIELDOLD);
end^

set term;^
-- The problem here is probably the procedure. 
-- When I reconnect after the “alter procedure,” it works.
-- So FieldNew is then set to the default value.


alter table MYTABLE add FIELDNEW bigint default 0 not null;
commit;



execute procedure ADDMYTABLE( 1);
commit;

[EPluribusUnum]

You execute DDL and non-DDL command on the sam e transaction. This can lead to corruption (and a known limitation).

[nLeonardt95]

I perform these steps in my application in different stages.

A new transaction is opened for each stage. I have the same problem there. I have now summarized everything for the example.

The only thing that helps is that I recreate the entire CONNECTION during the stages.

...(and a known limitation).

• Where can I find information about this limitation?
  • Especially because it's a well-known limitation. Why don't I get an error when I mix DDL and DML in a script? You shouldn't allow that if it could potentially cause problems.

[hvlad]

Here is full isql session with FB3 and your sample:

SQL> create database 'inet://s:\temp\gh-8802.fdb';
SQL> set auto off;
SQL>
SQL> CREATE TABLE MYTABLE (
CON>     FIELDOLD  INTEGER
CON> );
SQL>
SQL> SET TERM ^;
SQL>
SQL> create or alter procedure ADDMYTABLE (
CON>     FIELDOLD integer)
CON> as
CON> begin
CON>   insert into MYTABLE ( FIELDOLD)
CON>   values ( :FIELDOLD);
CON> end^
SQL>
SQL> SET TERM ;^
SQL>
SQL> commit;
SQL>
SQL> set term ^;
SQL>
SQL> create or alter procedure ADDMYTABLE (
CON>     FIELDOLD integer)
CON> as
CON> begin
CON>   insert into MYTABLE ( FIELDOLD)
CON>   values ( :FIELDOLD);
CON> end^
SQL>
SQL> set term ;^
SQL>
SQL> -- The problem here is probably the procedure.
SQL> -- When I reconnect after the “alter procedure,” it works.
SQL> -- So FieldNew is then set to the default value.
SQL>
SQL> alter table MYTABLE add FIELDNEW bigint default 0 not null;
SQL> commit;
SQL>
SQL> execute procedure ADDMYTABLE( 1);
SQL> commit;
SQL>
SQL> select * from mytable;

    FIELDOLD              FIELDNEW
============ =====================
           1                     0

SQL> commit;
SQL> exit;

There is no issue as you see. Probably, you commited second ALTER PROCEDURE before run ALTER TABLE. Run both statements in the same transaction, or alter procedure after altering table.

[nLeonardt95]

Without “set auto off;” the problem occurs. But I also see another problem with the display of ISQL. Even without “set auto off;” ISQL shows me 0 for FieldNEW. However, IBExpert shows me “NULL.”

In this case, IBExpert definitely shows me the truth.

How can I check this? After running the script, make a backup of the database and restore it.

The following error then occurs:

" [10:47:29] gbak:validation error on column in table MYTABLE [10:47:29] gbak: ERROR:validation error for column “MYTABLE”.‘FIELDNEW’, value “*** null ***” "

SET SQL DIALECT 3;

create database 'localhost/3055:D:\Temp\Test4.fdb' user 'SYSDBA' password 'xxx';

CREATE TABLE MYTABLE (
    FIELDOLD  INTEGER
);

SET TERM ^;

create or alter procedure ADDMYTABLE (
    FIELDOLD integer)
as
begin
  insert into MYTABLE ( FIELDOLD)
  values ( :FIELDOLD);
end^

SET TERM ;^

commit;


set term ^;

create or alter procedure ADDMYTABLE (
    FIELDOLD integer)
as
begin
  insert into MYTABLE ( FIELDOLD)
  values ( :FIELDOLD);
end^

set term ;^

-- The problem here is probably the procedure.
-- When I reconnect after the “alter procedure,” it works.
-- So FieldNew is then set to the default value.

alter table MYTABLE add FIELDNEW bigint default 0 not null;
commit;

execute procedure ADDMYTABLE( 1);
commit;

select * from mytable;
commit;

[AlexPeshkoff]

ISQL really can show 0 for NOT NULL field with NULL value. For an experiment it's better to choose other default value.

[hvlad]

Without “set auto off;” the problem occurs.

Sure, read what I wrote about transactions above and learn about "set autoddl" isql command.

[hvlad]

ISQL really can show 0 for NOT NULL field with NULL value. For an experiment it's better to choose other default value.

Just repeated script with default 123 and all is how it must be - field have value 123.

[nLeonardt95]

It's okay, thanks for the suggested solutions. I have solutions to work around the problem. I just wanted to report it and point it out so that it can be fixed in the future. I consider it is a mistake that it is possible to create corrupt data records in any way.