When migrating from Firebird 2.5 to Firebird 3 we have a number of stored
procedures that stop functioning correctly. They start working again once
the procedures have been rebuilt from script.
The procedures all perform something along the lines of inserting a record
with a default value and then copying this record to a different table.
The copy (insert) fails with:
validation error for column "TABLETO"."AMOUNT", value "*** null ***"
Simple script to reproduce:
CREATE TABLE TABLETO (
PK INTEGER NOT NULL,
AMOUNT FLOAT DEFAULT 0.00 NOT NULL,
PRIMARY KEY(PK));
CREATE TABLE TABLEFROM (
PK INTEGER NOT NULL,
AMOUNT FLOAT DEFAULT 0.00 NOT NULL,
PRIMARY KEY(PK));
SET TERM ^;
CREATE PROCEDURE TESTCOPY (PK INTEGER)
AS BEGIN
INSERT INTO TABLEFROM(PK) VALUES (:PK);
INSERT INTO TABLETO (PK, AMOUNT) VALUES (:PK, (SELECT
COALESCE(AMOUNT, 0) FROM TABLEFROM WHERE PK = :PK));
END ^
Steps to reproduce:
- Create new database under Firebird 2.5
- Run sample script
- Back up the database
- Restore database under Firebird 3
- Run command "execute procedure testcopy(1);" via isql
Versions:
Firebird 2.5.8
Firebird 3.0.4
Is this a known issue or bug?
Thanks and regards
David
