Regression in 6.x: query with "... where <field> in ( <scalar_value> | <scalar_func>, null )" returns empty rowset instead of existing record

[pavel-zotov]

set bail on;
set list on;
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb 2>nul;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';

recreate table test(id int);

set term ^;
execute block as
begin
    rdb$set_context('USER_SESSION', 'VALUE_TO_BE_CHECKED', 1);
end
^
set term ;^

insert into test values( rdb$get_context('USER_SESSION', 'VALUE_TO_BE_CHECKED') );
commit;

set count on;
set echo on;

select 'a' from test where id = rdb$get_context('USER_SESSION', 'VALUE_TO_BE_CHECKED') or id is null;

select 'b' from test where id = 1 or id is null;

select 'c' from test where id in ( rdb$get_context('USER_SESSION', 'VALUE_TO_BE_CHECKED'), null );

select 'd' from test where id in ( 1, null );

select 'e' from test where id in ( (select id from test rows 1), null );

Outcome:

• on 6.0.0.1342-d2bf66f (Thu Nov 6 14:11:49 2025 +0300, Postfix for #8430 (#8752)) -- one record for every statement, e.g.:

CONSTANT                        a
CONSTANT                        b
CONSTANT                        c
CONSTANT                        d
CONSTANT                        e

(expected)

• on 6.0.0.1346-53b218f (Fri Nov 7 07:40:37 2025 -0300, Cleanup: get rid of req_null (#8798)) -- only three statements work:

* select 'a' from test where id = rdb$get_context('USER_SESSION', 'VALUE_TO_BE_CHECKED') or id is null;
CONSTANT                        a
Records affected: 1

select 'b' from test where id = 1 or id is null;
CONSTANT                        b
Records affected: 1

select 'c' from test where id in ( rdb$get_context('USER_SESSION', 'VALUE_TO_BE_CHECKED'), null );
Records affected: 0 -------- <<< ??? <<<

select 'd' from test where id in ( 1, null );
Records affected: 0 -------- <<< ??? <<<

select 'e' from test where id in ( (select id from test rows 1), null );
CONSTANT                        e
Records affected: 1

Same result if we use scalar func (e.g. PI()) instead of numeric literal value:

recreate table test(id double precision);
set term ^;
execute block as
begin
    rdb$set_context('USER_SESSION', 'VALUE_TO_BE_CHECKED', pi());
end
^
set term ;^
insert into test values( rdb$get_context('USER_SESSION', 'VALUE_TO_BE_CHECKED') );
commit;
set count on;
set echo on;
select 'a' from test where id = rdb$get_context('USER_SESSION', 'VALUE_TO_BE_CHECKED') or id is null;
select 'b' from test where id = pi() or id is null;
select 'c' from test where id in ( rdb$get_context('USER_SESSION', 'VALUE_TO_BE_CHECKED'), null );
select 'd' from test where id in ( pi(), null );
select 'e' from test where id in ( (select id from test rows 1), null );

PS.
Bug was discovered due to persistent OLTP-EMUL test failures for FB 6.x that started since 6.0.0.1351 (08-nov-2025), see:
http://49.12.10.46/oltp-emul-fb/