Declaration of package constants in form "CONSTANT <name> charactser set ... collate ... = <value>"

[Pavel Zotov]

i can't find proper form of package constant declaration when it must be explicitly casted to some character sort + collation (rather than its value is  specified via "introducer syntax"):

This is example:

set bail on;
set autoterm on;
set list on;
set names utf8;
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';

create or alter function foo returns int
as
    declare k_pi blob sub_type 1 = _utf8 q'#rapport de la circonférence au diamètre#' collate unicode_ci_ai collate unicode_ci_ai;
    declare k_c blob sub_type 1 character set utf8 collate unicode_ci_ai = q'#la vitesse de la lumière dans le vide#';
begin
    return 1;
end
;
   
create or alter package pg_const_blob as
begin
    constant k_pi blob sub_type 1 = _utf8 q'#rapport de la circonférence au diamètre#' collate unicode_ci_ai collate unicode_ci_ai;
    constant k_c blob sub_type 1 character set utf8 collate unicode_ci_ai = q'#la vitesse de la lumière dans le vide#';
end
;

Function will be compiled OK.

Package compilation will fail on SECOND constant declaration, i.e. output will be:

    constant k_c blob sub_type 1 character set utf8 collate unicode_ci_ai = q'#la vitesse de la lumière dans le vide#';
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 4, column 53
-collate

[Pavel Zotov]

PS.
no problem with varchar datatype (unlike blob):

create or alter package pg_const_vchr as
begin
    constant k_pi varchar(255) = _utf8 q'#rapport de la circonférence au diamètre#' collate unicode_ci_ai collate unicode_ci_ai;
    constant k_c varchar(255) character set utf8 collate unicode_ci_ai = q'#la vitesse de la lumière dans le vide#';
end
-- will be compiled OK.

воскресенье, 24 мая 2026 г. в 19:50:13 UTC+3, Pavel Zotov:

[Denis Simonov]

Why is the collate clause listed twice here?

create or alter function foo returns int
as
    declare k_pi blob sub_type 1 = _utf8 q'#rapport de la circonférence au diamètre#' collate unicode_ci_ai collate unicode_ci_ai;
    declare k_c blob sub_type 1 character set utf8 collate unicode_ci_ai = q'#la vitesse de la lumière dans le vide#';
begin
    return 1;
end
;

понедельник, 25 мая 2026 г. в 01:57:07 UTC+3, Pavel Zotov:

[Pavel Zotov]

0xFF. Funny copy-paste  shows minor problem in compiler :-)

This also passes:

create or alter function foo returns int
as

    declare k_pi blob sub_type 1 = _utf8 q'#rapport de la circonférence au diamètre#' collate unicode_ci_ai collate unicode_ci_ai collate unicode_ci_ai collate unicode_ci_ai collate unicode_ci_ai collate unicode_ci_ai;
begin
    return 1;
end
;

OK, this is after correction:

set bail on;
set autoterm on;
set list on;
set names utf8;
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';

create or alter function foo returns int
as

    declare k_pi blob sub_type 1 = _utf8 q'#rapport de la circonférence au diamètre#' collate unicode_ci_ai;

    declare k_c blob sub_type 1 character set utf8 collate unicode_ci_ai = q'#la vitesse de la lumière dans le vide#';
begin
    return 1;
end
;

create or alter package pg_const_vchr as
begin

    constant k_pi varchar(255) = _utf8 q'#rapport de la circonférence au diamètre#' collate unicode_ci_ai;

    constant k_c varchar(255) character set utf8 collate unicode_ci_ai = q'#la vitesse de la lumière dans le vide#';
end

;  

create or alter package pg_const_blob as
begin

    constant k_pi blob sub_type 1 = _utf8 q'#rapport de la circonférence au diamètre#' collate unicode_ci_ai;

    constant k_c blob sub_type 1 character set utf8 collate unicode_ci_ai = q'#la vitesse de la lumière dans le vide#';
end
;

понедельник, 25 мая 2026 г. в 09:39:00 UTC+3, Denis Simonov:

[Denis Simonov]

понедельник, 25 мая 2026 г. в 09:50:35 UTC+3, Pavel Zotov:

0xFF. Funny copy-paste  shows minor problem in compiler :-)

This also passes:

create or alter function foo returns int
as

    declare k_pi blob sub_type 1 = _utf8 q'#rapport de la circonférence au diamètre#' collate unicode_ci_ai collate unicode_ci_ai collate unicode_ci_ai collate unicode_ci_ai collate unicode_ci_ai collate unicode_ci_ai;
begin
    return 1;
end
;

Create an issue about this.

 

[Artyom Abakumov]

Thanks for the reproduction test. Fixed in the PR: https://github.com/FirebirdSQL/firebird/pull/9037

понедельник, 25 мая 2026 г. в 09:50:35 UTC+3, Pavel Zotov: