Packaged temporary tables. Several failed tests since #9247c82b

46 views
Skip to first unread message

Pavel Zotov

unread,
Jun 4, 2026, 1:46:27 PMJun 4
to firebird-devel
Several problems did appear with QA since commit 9247c82b
// date: 12-may-2026 07:36:44 2026 -0300
// message: "Feature #8974 - Temporary Tables in Packages (#8983)"

Please consider following scripts:

case-1 (based on bugs/core_1152_test.py)
=======
set bail on;
set autoterm on;
set echo on;
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb' user sysdba password 'masterkey';
show version;
create table test(
    int1 int,
    int2 int,
    constraint chk_nn check (int1 is null or int2 is null)
);

commit; connect 'localhost:r:\temp\tmp4test.fdb' user sysdba password 'masterkey';

drop table test;
=======

Last statement will fail with:
=======
Statement failed, SQLSTATE = 23000
unsuccessful metadata update
-DROP TABLE "PUBLIC"."TEST" failed
-Cannot delete column being used in an Integrity Constraint.
=======

####################################################

case-2 (based on functional/gtcs/test_misplaced_collation_in_extracted_metadata.py)
=======
set bail on;
set keep_tran on;
set list on;

--#############
SET NAMES UTF8; -- <<< ACHTUNG >>>
--#############

set echo on;
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
commit;
set transaction read committed no record_version wait;

create domain domain_with_collate_clause as char(1)
    character set iso8859_1
    default 'v'
    check(value >='a' and value <='z')
    collate es_es;
commit;

create table table_with_collated_field (
    field_01 domain_with_collate_clause
        default 'w'
        collate pt_pt
);
alter table table_with_collated_field add constraint f01_check check( field_01 >= 'c' );
commit;

set bail off;

drop table table_with_collated_field;
drop domain domain_with_collate_clause;
commit;
=======
::: NOTE ::: it is crucial to make connection with some charset to reproduce problem (utf8 / iso8859_1 / win1251 etc) . If no charset is specified then bug not appears.

Output:
=======
drop table table_with_collated_field;
Statement failed, SQLSTATE = 22000
unsuccessful metadata update
-DROP TABLE "PUBLIC"."TABLE_WITH_COLLATED_FIELD" failed
-no current record for fetch operation

drop domain domain_with_collate_clause;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-DROP DOMAIN "PUBLIC"."DOMAIN_WITH_COLLATE_CLAUSE" failed
-Domain "PUBLIC"."DOMAIN_WITH_COLLATE_CLAUSE" is used in table "PUBLIC"."TABLE_WITH_COLLATED_FIELD" (local name "FIELD_01") and cannot be dropped
=======

In both cases no erros must occur.

PS.
This is link to snapshots:
    * 6.0.0.1942-c1ee670-x64.7z - has no aforesaid problem;
    * 6.0.0.1942-9247c82-x64.7z - first snapshot with this oddity.

Adriano dos Santos Fernandes

unread,
Jun 21, 2026, 9:49:05 AMJun 21
to firebir...@googlegroups.com
On 6/4/26 14:46, Pavel Zotov wrote:
> Several problems did appear with QA since commit 9247c82b <https://
> github.com/FirebirdSQL/firebird/
> commit/9247c82bfa9460def31a801cb4e72bf331b952b9>
> // date: 12-may-2026 07:36:44 2026 -0300
> // message: "Feature #8974 - Temporary Tables in Packages (#8983)"
>
> Please consider following scripts:
>

Fixed, thanks.


Adriano

Pavel Zotov

unread,
Jun 21, 2026, 3:29:57 PMJun 21
to firebir...@googlegroups.com
hi Adriano!
Currently i see that only TWO problems (specified in fb-devel https://groups.google.com/g/firebird-devel/c/oWnT6JkRNLU/m/Qj-lpQBfBAAJ ) are solved.
But not third which i've described yesterday:
==============
set bail on;
set list on;
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
recreate package pg_03 as begin end;
recreate package pg_02 as begin end;
recreate package pg_01 as begin end;
commit;
 
recreate table test01 (id1 int primary key, f01 int);
recreate table test02 (id2 int primary key, f02 int);
recreate table test03 (id3 int primary key, f03 int);
commit;
 
insert into test01(id1, f01) values(1, 111);
insert into test02(id2, f02) values(1, 222);
insert into test03(id3, f03) values(1, 333);
commit;
 
set term ^;
create package body pg_01 as
begin
    procedure p01(i_x int) returns (o_y int) as
    begin
        --  dependencies is created for test01
        select id1 from test01 into :o_y;
    end
end
^
 
create package body pg_02 as
begin
    procedure p01(i_x int) returns (o_y int)  as
    begin
        --  dependencies is created for test01 and test02 and test03
        select id1 from test01 into :o_y;
        select id2 from test02 into :o_y;
        select id3 from test03 into :o_y;
    end
end
^
 
recreate package pg_03 as
begin
  procedure p01(i_x int) returns (o_y int);
  procedure p02(i_x int) returns (o_y int);
  procedure p03(i_x int) returns (o_y int);
end
^
 
create package body pg_03
as
begin
    procedure p01(i_x int) returns (o_y int) as
    begin
        -- no dependencies is created !!!
        select f01 from test01 where id1 = :i_x into :o_y;
    end
 
    procedure p02(i_x int) returns (o_y int) as
    begin
        -- no dependencies is created !!!
        select f02 from test02 where id2 = :i_x into :o_y;
    end
 
    procedure p03(i_x int) returns (o_y int) as
    begin
        --  dependencies is only created for test03
        select f03 from test03 where id3 = :i_x into :o_y;
    end
end
^
set term ;^
commit;
 
drop package pg_01;
drop package pg_02;
 
-- Expected STDERR: SQLSTATE = 42000 / cannot delete / COLUMN TESTnn.IDn / there are 1 dependencies
set bail OFF;
set echo on;
drop table test01;
 
drop table test02;
quit;
==============
 
Output does not contaion info about COLUMN. Only name of table is shown:
==============
drop table test01;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-cannot delete
-TABLE "PUBLIC"."TEST01"
-there are 1 dependencies
==============
 
Is it expected ?
 
 
 

Adriano dos Santos Fernandes

unread,
Jun 21, 2026, 8:50:20 PMJun 21
to firebir...@googlegroups.com
On 6/21/26 16:29, Pavel Zotov wrote:
> hi Adriano!
>  
> On 6/4/26 14:46, Pavel Zotov wrote:
>
>  Several problems did appear with QA since commit 9247c82b <https://
>  github.com/FirebirdSQL/firebird/
>  commit/9247c82bfa9460def31a801cb4e72bf331b952b9>
>  // date: 12-may-2026 07:36:44 2026 -0300
>  // message: "Feature #8974 - Temporary Tables in Packages (#8983)"
>  
>  Please consider following scripts:
>  
> Fixed, thanks.
>
>  
> Currently i see that only TWO problems (specified in fb-devel https://
> groups.google.com/g/firebird-devel/c/oWnT6JkRNLU/m/Qj-lpQBfBAAJ
> <https://groups.google.com/g/firebird-devel/c/oWnT6JkRNLU/m/Qj-
> lpQBfBAAJ> ) are solved.
I cannot confirm about no dependency creation:
----
RDB$DEPENDENT_NAME PG_01



RDB$DEPENDED_ON_NAME TEST01



RDB$FIELD_NAME ID1



RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_01



RDB$DEPENDED_ON_NAME TEST01



RDB$FIELD_NAME <null>
RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_02



RDB$DEPENDED_ON_NAME TEST03



RDB$FIELD_NAME ID3



RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_02



RDB$DEPENDED_ON_NAME TEST03



RDB$FIELD_NAME <null>
RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_02



RDB$DEPENDED_ON_NAME TEST02



RDB$FIELD_NAME ID2



RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_02



RDB$DEPENDED_ON_NAME TEST02



RDB$FIELD_NAME <null>
RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_02



RDB$DEPENDED_ON_NAME TEST01



RDB$FIELD_NAME ID1



RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_02



RDB$DEPENDED_ON_NAME TEST01



RDB$FIELD_NAME <null>
RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_03



RDB$DEPENDED_ON_NAME TEST01



RDB$FIELD_NAME F01



RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_03



RDB$DEPENDED_ON_NAME TEST01



RDB$FIELD_NAME ID1



RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_03



RDB$DEPENDED_ON_NAME TEST01



RDB$FIELD_NAME <null>
RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_03



RDB$DEPENDED_ON_NAME TEST02



RDB$FIELD_NAME F02



RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_03



RDB$DEPENDED_ON_NAME TEST02



RDB$FIELD_NAME ID2



RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_03



RDB$DEPENDED_ON_NAME TEST02



RDB$FIELD_NAME <null>
RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_03



RDB$DEPENDED_ON_NAME TEST03



RDB$FIELD_NAME F03



RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_03



RDB$DEPENDED_ON_NAME TEST03



RDB$FIELD_NAME ID3



RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC




RDB$DEPENDENT_NAME PG_03



RDB$DEPENDED_ON_NAME TEST03



RDB$FIELD_NAME <null>
RDB$DEPENDENT_TYPE 19
RDB$DEPENDED_ON_TYPE 0
RDB$PACKAGE_NAME <null>
RDB$DEPENDENT_SCHEMA_NAME PUBLIC



RDB$DEPENDED_ON_SCHEMA_NAME PUBLIC



----

> drop package pg_01;
> drop package pg_02;
>  
> -- Expected STDERR: SQLSTATE = 42000 / cannot delete / COLUMN

Why that would be expected if it's dropping a table?


Adriano

Pavel Zotov

unread,
Jun 22, 2026, 5:12:58 AMJun 22
to firebird-devel
 
> drop package pg_01;
> drop package pg_02;
>  
> -- Expected STDERR: SQLSTATE = 42000 / cannot delete / COLUMN

Why that would be expected if it's dropping a table?

The previous message (about drop COLUMN while we intend to drop the whole TABLE) was confusing, but it's been like that since 3.x.
I've asked just to be on the safe side— wasn't something broken...

Reply all
Reply to author
Forward
0 new messages