I can reproduce it (Firebird 2.5.8 to Firebird 3.0.4).
I suggest that you create a ticket in the tracker. The problem doesn't
sound familiar to me, and if it is a known problem, the core developers
will close appropriately to an existing duplicate.
Looking at the BLR (using set blob all in isql) and then selecting the
BLR from RDB$PROCEDURES does show some differences (after recreating the
procedure in Firebird 3):
--- v25blr.txt 2019-02-13 16:33:58.530790700 +0100
+++ v30blr.txt 2019-02-13 16:33:48.811658200 +0100
@@ -18,6 +18,8 @@
blr_parameter2, 0, 0,0, 1,0,
blr_field, 0, 2, 'P','K',
blr_end,
+ blr_begin,
+ blr_start_savepoint,
blr_store,
blr_relation, 7, 'T','A','B','L','E','T','O', 2,
blr_begin,
@@ -35,14 +37,14 @@
blr_parameter2, 0, 0,0, 1,0,
blr_end,
blr_cast, blr_float,
- blr_value_if,
- blr_missing,
+ blr_coalesce, 2,
blr_field, 1, 6,
'A','M','O','U','N','T',
blr_literal, blr_long, 0, 0,0,0,0,
- blr_field, 1, 6,
'A','M','O','U','N','T',
blr_null,
blr_field, 2, 6, 'A','M','O','U','N','T',
blr_end,
+ blr_end_savepoint,
+ blr_end,
blr_end,
blr_end,
blr_end,
My bet is on the addition of the savepoint.
I'm not sure if this is fixable during a restore. Recompiling stored
procedures, triggers, etc is always a good thing to do when upgrading.
Mark
On 13-2-2019 14:57, david.holli...@parcelperfect.com [firebird-support]
wrote:
> 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?
--
Mark Rotteveel
