Regression in 6.x: COALESCE(<non_empty_arg_1>, <non_empty_arg_2>) can return empty string

[pavel-zotov]

Problem seems related to #8145
The following issue cannot be solved until this error is fixed:
FirebirdSQL/oltp-emul#3

Consider script:

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 exception ex_empty_input_str 'Empty string encountered in input argument: @1. Further operations not allowed.';
create domain dm_dbobj varchar(64);

set term ^;
create or alter procedure sp_test(
     a_main_view dm_dbobj NOT null
    ,a_aux_view dm_dbobj default null
) returns (
    id_selected int
) as
begin

    rdb$set_context('USER_TRANSACTION', 'DEBUG2_a_main_view', coalesce(a_main_view, '[NULL]'));
    rdb$set_context('USER_TRANSACTION', 'DEBUG2_a_aux_view', coalesce(a_aux_view, '[NULL]'));

    a_aux_view = coalesce( a_aux_view, a_main_view ); ------------ [ 1 ]
    
    rdb$set_context('USER_TRANSACTION', 'DEBUG3_a_aux_view', coalesce(a_aux_view, '[NULL]'));

    if ( trim(a_aux_view) = '' ) then
    begin
        exception ex_empty_input_str
        using(
               'a_aux_view =>' || coalesce( trim(a_aux_view), '[null]' ) || '<'
            || '; input arg value =>' || coalesce( rdb$get_context('USER_TRANSACTION', 'DEBUG2_a_aux_view'), '[null]' ) || '<'
        );
    end

    id_selected = 1;
    suspend;

end
^
commit
^
execute block returns(id_selected int) as
    declare v_sttm varchar(8190);
begin
    v_sttm = q'{
        execute block(
            a_main_view dm_dbobj = ?
            ,a_aux_view dm_dbobj = ?
        ) returns(doc_list_id bigint) as
        begin
            rdb$set_context('USER_TRANSACTION', 'DEBUG1_a_main_view', coalesce(a_main_view, '[NULL]'));
            rdb$set_context('USER_TRANSACTION', 'DEBUG1_a_aux_view', coalesce(a_aux_view, '[NULL]'));

            select id_selected
            from sp_test(
                 :a_main_view
                ,:a_aux_view
            )
            into doc_list_id;

            suspend;
        end
    }';
  
    execute statement (v_sttm)
    (
         'v_main_view'
        ,'v_aux_view'
    )
    into id_selected;
    suspend;
    --     cast('v_main' as dm_dbobj)
    --    ,cast('v_aux_view' as dm_dbobj)
end
^
set term ;^
quit;

On 3.x, 4.x, 5.x and up to 6.0.0.894-1444312 (Fri Jul 4 10:49:57 2025 +0300) all works fine and this script issues ID_SELECTED 1

Staring from 6.0.0.894-35aae92 ( Fri Jul 4 13:30:24 2025 +0300) this script fails with:

Statement failed, SQLSTATE = HY000
exception 1
-"PUBLIC"."EX_EMPTY_INPUT_STR"
-Empty string encountered in input argument: a_aux_view =><; input arg value =>v_aux_view<. Further operations not allowed.
-At procedure "PUBLIC"."SP_TEST" line: 18, col: 9
At block line: 10, col: 13
-At block line: 6, col: 5

This means that
a_aux_view = coalesce( a_aux_view, a_main_view ); ------------ [ 1 ]
-- causes resulting variable a_aux_view to become EMPTY string, although both input args (a_aux_view and a_main_view) are not empty.

Passing arguments with casting to approriate type:

    --     cast('v_main' as dm_dbobj)
    --    ,cast('v_aux_view' as dm_dbobj)

-- not helps.

URL to above mentioned FB 6.x snapshots and used firebird.conf:
https://drive.google.com/drive/folders/1oYURnxKjaB1yXbDhy3boWd7dU-KCsJg5?usp=sharing

PS.
Problematic commit is: Merge pull request #8571 from aafemt/postfix8145

[pavel-zotov]

Simplified test case:

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 exception ex_empty_input_str 'Empty string encountered in input argument: @1. Further operations not allowed.';

set term ^;
create or alter procedure sp_test(
     a_main_view varchar(63) NOT null
    ,a_aux_view varchar(63) default null
) returns (
    id_selected int
) as
begin
    a_aux_view = coalesce( a_aux_view, a_main_view );
    if ( trim(a_aux_view) = '' ) then
    begin
        exception ex_empty_input_str using('a_aux_view');
    end

    id_selected = 1;
    suspend;

end
^
commit
^
execute block returns(id_selected int) as
    declare v_sttm varchar(8190);
begin
    execute statement ('select id_selected from sp_test(?, ?)') ('v_main_view', 'v_aux_view') into id_selected;
    suspend;
end
^
set term ;^
quit;

[aafemt]

Judging by error message, values of parameters are transferred ok, so problem perhaps isn't directly caused by commit. I'll look at it.

[aafemt]

Check artifacts from #8685.

[pavel-zotov]

Check artifacts from #8685.

It works fine:

C:\FBTESTING\OLTP-EMUL.TMP\src>C:\FB\60SS\isql -q -i gh_8681.sql -z -e
ISQL Version: WI-T6.0.0.1131 Firebird 6.0 Initial
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';
Server version:
WI-T6.0.0.1131 Firebird 6.0 Initial
WI-T6.0.0.1131 Firebird 6.0 Initial/tcp (PZ)/P20:C
WI-T6.0.0.1131 Firebird 6.0 Initial/tcp (PZ)/P20:C

recreate exception ex_empty_input_str 'Empty string encountered in input argument: @1. Further operations not allowed.';

set term ^;
create or alter procedure sp_test(
     a_main_view varchar(63) NOT null
    ,a_aux_view varchar(63) default null
) returns (
    id_selected int
) as
begin
    a_aux_view = coalesce( a_aux_view, a_main_view );
    if ( trim(a_aux_view) = '' ) then
    begin
        exception ex_empty_input_str using('a_aux_view');
    end

    id_selected = 1;
    suspend;

end
^
commit
^
execute block returns(id_selected int) as
    declare v_sttm varchar(8190);
begin
    execute statement ('select id_selected from sp_test(?, ?)') ('v_main_view', 'v_aux_view') into id_selected;
    suspend;
end
^

ID_SELECTED                     1

set term ;^
quit;