Firebird 6.0.0 can't restore local SP referencing system table

[tonne1]

FB 6.0.0.2068, trying to restore a FB5 DB which contains SPs referencing system tables, something like

for 
  select rdb$field_name from rdb$relation_fields where ... 
  into :fldname 
do ...

error message:

gbak: ERROR:table "PUBLIC"."RDB$RELATION_FIELDS" is not defined

The topic is user object referencing system object. Do you need a test case?

[asfernandes]

The topic is user object referencing system object. Do you need a test case?

Yes.

[tonne1]

Man, that was quick. The error only occurs if the reference to a system table is inside a local SP of a regular SP.

To reproduce the error:

create or alter procedure read_systable_from_local_sp
returns (
  result integer
)
AS
declare procedure local_sp returns (val integer) as
begin
  select 1 from rdb$relation_fields rows 1
  into :val;
  suspend;
end

begin
  select val from local_sp
  into :result;
  suspend;
end;

[tonne1]

updated and simplified my test case

[asfernandes]

Cannot reproduce.

isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database 't.fdb';
SQL> set term !;
SQL> create or alter procedure read_systable_from_local_sp
CON> returns (
CON>   result integer
CON> )
CON> AS
CON> declare procedure local_sp returns (val integer) as
CON> begin
CON>   select 1 from rdb$relation_fields rows 1
CON>   into :val;
CON>   suspend;
CON> end
CON> 
CON> begin
CON>   select val from local_sp
CON>   into :result;
CON>   suspend;
CON> end!


gbak -B -V t.fdb t.fbk
gbak:readied database t.fdb for backup
gbak:creating file t.fbk
gbak:starting transaction
gbak:database t.fdb has a page size of 8192 bytes.
gbak:writing schemas
gbak:writing schema "PUBLIC"
gbak:writing domains
gbak:    writing domain "PUBLIC"."RDB$1"
gbak:writing shadow files
gbak:writing character sets
gbak:writing collations
gbak:writing tables
gbak:writing types
gbak:writing filters
gbak:writing id generators
gbak:writing exceptions
gbak:writing functions
gbak:writing stored procedures
gbak:writing stored procedure "PUBLIC"."READ_SYSTABLE_FROM_LOCAL_SP"
gbak:writing parameter "RESULT" for stored procedure
gbak:writing packages
gbak:writing triggers
gbak:writing trigger messages
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "PUBLIC"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:    writing privilege for user "SYSDBA"
gbak:writing security classes
gbak:writing table constraints
gbak:writing referential constraints
gbak:writing check constraints
gbak:writing SQL roles
gbak:writing names mapping
gbak:writing publications
gbak:writing constants
gbak:closing file, committing, and finishing. 3072 bytes written


gbak -C -REP -V t.fbk tt.fdb
gbak:opened file t.fbk
gbak:transportable backup -- data in XDR format
gbak:		backup file is compressed
gbak:backup version is 12
gbak:created database tt.fdb, page_size 8192 bytes
gbak:started transaction
gbak:restoring schema "PUBLIC"
gbak:restoring domain "PUBLIC"."RDB$1"
gbak:restoring stored procedure "PUBLIC"."READ_SYSTABLE_FROM_LOCAL_SP"
gbak:    restoring parameter "RESULT" for stored procedure
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "PUBLIC"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:    restoring privilege for user "SYSDBA"
gbak:creating indexes
gbak:committing metadata
gbak:adjusting views dbkey length
gbak:updating ownership of packages, procedures and tables
gbak:adding missing privileges
gbak:adjusting system generators
gbak:finishing, closing, and going home
gbak:adjusting the ONLINE and FORCED WRITES flags


isql tt.fdb
Database: tt.fdb, User: SYSDBA
SQL> select * from read_systable_from_local_sp;

      RESULT 
============ 
           1 

[asfernandes]

Now reproduced.
I was creating original database in FB 6.

[asfernandes]

Please test next snapshot.

[tonne1]

Fixed, using build 2073. Thanks!