QA-test for "Search existing package constants in all SCHEMAs" ( log message from #81241342 )

[Pavel Zotov]

There was push #81241342 (13-may-2026) related to packages.

Following items from it remain unclear for me.
I want to ask hint about proper test implementation for them:
    * Search existing package constants in all SCHEMAs
    * Add constant missing RDB$CONSTANT_SOURCE setup
(in #9022 these items are: "Fix incorrect search in SCHEMAs list" and "Fix missing RDB$CONSTANT_SOURCE setup")

Consider script:

set bail on;
set heading off;
set autoddl off;
set autoterm 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;

create package pg_test as
begin
    constant phone varchar(12) = '+33610011111';
end
;

create schema stock;
create schema shops;
create schema salary;

create package stock.pg_test as
begin
    constant phone varchar(12) = '+33620022222';
end
;

create package shops.pg_test as
begin
    constant phone varchar(12) = '+33630033333';
end
;

create package salary.pg_test as
begin
    constant boss_income int = 4000000;
end
;

set search_path to public;
select pg_test.phone from rdb$database;

set search_path to stock;
select pg_test.phone from rdb$database;

set search_path to shops;
select pg_test.phone from rdb$database;

grant usage on package salary.pg_test to PUBLIC;
commit;

-- set search_path to salary; -------------- [ 1 ]
select pg_test.boss_income from rdb$database;


Its output will be:
    +33610011111
    +33620022222
    +33630033333
    Statement failed, SQLSTATE = 42S22
    Dynamic SQL Error
    -SQL error code = -206
    -Column unknown
    -"PG_TEST"."BOSS_INCOME"

If i UNcomment "[ 1 ]" then all works fine.
So, schema 'salary' must be added into the search path if we want to get constant from its 'pg_test' package (i.e. similarly to other schemas from this script).

But what does 'Search existing package constants in all SCHEMAs' means in that case ?
(i thought that it was somewhat like search in all existing schemas, regardless of whether some of them are present in the SEARCH path or no)

[Pavel Zotov]

This push contains one more item effect of which i can't obtain:

    Store EXECUTE privilege for all SYSTEM packages

Following script *works* without error on snapshot 6.0.0.1948-20260513_212500-f8eee95 which is closest prior to push   #81241342:

create table test(id int);
create or alter user john password 'junior';
commit;
revoke all on all from john;
commit;
grant select on test to john;
commit;

connect 'localhost:r:\temp\tmp4test.fdb' user john password 'junior';

select
     plan_line as rn
    ,cardinality
    ,cast(access_path as varchar(8190)) as access_txt
from system.rdb$sql.explain(q'#select count(*) from test#')
;

select system.rdb$time_zone_util.database_version() from rdb$database;

(note: user 'john' was NOT granted here with execute privilege to any package from SYSTEM schema)

Output:

RN                              1
CARDINALITY                     <null>
ACCESS_TXT                      Select Expression

RN                              2
CARDINALITY                     1.000000000000000
ACCESS_TXT                      -> Aggregate

RN                              3
CARDINALITY                     1.000000000000000
ACCESS_TXT                      -> Table "PUBLIC"."TEST" Full Scan

DATABASE_VERSION                2026b

(same on fresh 6.x snapshot).

So, how can i see the difference between snapshots before and after this push ?

[Artyom Abakumov]

If i UNcomment "[ 1 ]" then all works fine.
So, schema 'salary' must be added into the search path if we want to get constant from its 'pg_test' package (i.e. similarly to other schemas from this script).

But what does 'Search existing package constants in all SCHEMAs' means in that case ?
(i thought that it was somewhat like search in all existing schemas, regardless of whether some of them are present in the SEARCH path or no)

The error occurred in the following case:

create schema stock;

create package stock.pg_test as
begin
    constant phone varchar(12) = '+33620022222';
end
;

set search_path to public, stock;
select pg_test.phone from rdb$database; -- there was a bug: constant "public.pg_test.phone" not found 

The first existing schema from the schema search list was taken, not the first schema containing constant in question.

*  Add constant missing RDB$CONSTANT_SOURCE setup *

The RDB$CONSTANT_SOURCE value was missing for user made package constants 

* Store EXECUTE privilege for all SYSTEM packages *

Execute permissions are checked only when quering a constant from a package

[Pavel Zotov]

The first existing schema from the schema search list was taken, not the first schema containing constant in question.

...

Test committed ( functional/package/search_constants_in_all_schemas_test.py )
@Artyom Abakumov - thanks for explanations.

 

[Mark Rotteveel]

Does your PUBLIC schema also contain a package PG_TEST? If so, the
behaviour *is* correct IMHO.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 05-07-2026 00:21, Pavel Zotov wrote:
> But what does 'Search existing package constants in all SCHEMAs' means
> in that case ?
> (i thought that it was somewhat like search in all existing schemas,
> regardless of whether some of them are present in the SEARCH path or no)

That sounds like a documentation error to me to be honest. I wouldn't
expect the search to use all schemas, just all schemas on the search path.

Mark

--
Mark Rotteveel

[Artyom Abakumov]

No. The PUBLIC schema does not contain the PG_TEST package.

> But what does 'Search existing package constants in all SCHEMAs' means
> in that case ?
> (i thought that it was somewhat like search in all existing schemas,
> regardless of whether some of them are present in the SEARCH path or no)

That sounds like a documentation error to me to be honest. I wouldn't
expect the search to use all schemas, just all schemas on the search path.

Yes, sorry for the missliding message