Submitted by: @pavel-zotov

Is related to CORE3981
Is related to CORE4083

Query-1:

select *
from (
select r.rdb$relation_name, r.rdb$relation_id as id
from rdb$relations r
left
join rdb$relation_fields rf on r.rdb$relation_name = rf.rdb$relation_name
where rdb$relation_id < 128
)
where id = 0;

Query-2:

select *
from (
select r.rdb$relation_name, abs(r.rdb$relation_id) as id
from rdb$relations r
left
join rdb$relation_fields rf on r.rdb$relation_name = rf.rdb$relation_name
where rdb$relation_id < 128
)
where id = 0;

The only difference in these queries is presence of ABS() function inside Q-2, i.e. column `ID` which will be filtered in outside of DT is result of trivial EVALUATION.

Trace in WI-V2.5.2.26540 (both queries have identical PLAN JOIN (R INDEX (RDB$INDEX_1), RF INDEX (RDB$INDEX_4)):

1) for Q-1:

4 records fetched
0 ms, 14 fetch(es)

Table Natural Index Update
***************************************************************
RDB$RELATION_FIELDS 4
RDB$RELATIONS 1

2) for Q2:

4 records fetched
0 ms, 96 fetch(es)

Table Natural Index Update
**************************************************************
RDB$RELATION_FIELDS 4
RDB$RELATIONS 42

Trace in WI-V2.5.3.26780:

1) for Q-1: the same as in 2.5.2, see above.

2) for Q-2:

4 records fetched
2 ms, 7 read(s), 856 fetch(es)

Table Natural Index Update
*************************************************************
RDB$RELATION_FIELDS 343 <<<<<<<<<<<<<<<<<<<<<< ??
RDB$RELATIONS 42

Trace in WI-V3.0.0.31931:

1) for Q-1 - the same as in 2.5.x;

2) for Q-2:

4 records fetched
186 ms, 14 read(s), 1116 fetch(es)

Table Natural Index
*****************************************************
RDB$RELATION_FIELDS 457 <<<<<<<<<<<<<<<<<<<<<<<< ??
RDB$RELATIONS 50