Incorrect cardinality estimation for retrievals with multiple compound indices having common set of fields

[dyemanov]

Simplified test case (with PRINT_OPT_INFO defined for clarity):

recreate table t (id int primary key, f1 int, f2 int, f3 int);

insert into t
select val, mod(val, 100), mod(val, 200), mod(val, 300)
from (
  select row_number() over() as val
  from rdb$types, rdb$types, rdb$types
  rows 1000000);

commit;

create index it1 on t(f1, f2);
create index it2 on t(f1, f3);

-- Test #1: two conditions mapped to a compound index IT2
-- correct cardinality estimation

select count(*)
from t
where f1 = 1 and f3 = 3;

Select Expression
    -> Aggregate [rows: 1]
        -> Filter [rows: 3337]
            -> Table "T" Access By ID [rows: 3337]
                -> Bitmap
                    -> Index "IT2" Range Scan (full match)

-- Test #2: three conditions, two of them are mapped to the compound index IT2, the third one is not mapped to an index
-- incorrect cardinality estimation, third condition is not accounted

select count(*)
from t
where f1 = 1 and f2 = 2 and f3 = 3;

Select Expression
    -> Aggregate [rows: 1]
        -> Filter [rows: 3337]
            -> Table "T" Access By ID [rows: 3337]
                -> Bitmap
                    -> Index "IT2" Range Scan (full match)

-- Test #3: three conditions, two of them are mapped to the compound index IT2, the third one has index usage forcibly disabled
-- correct cardinality estimation, third condition reduces cardinality

select count(*)
from t
where f1 = 1 and f2+0 = 2 and f3 = 3;

Select Expression
    -> Aggregate [rows: 1]
        -> Filter [rows: 334]
            -> Table "T" Access By ID [rows: 3337]
                -> Bitmap
                    -> Index "IT2" Range Scan (full match)

The issue is rather old but usually does not cause any harm. However, in v5 (due to more complex optimizer) this sometimes causes incorrect plans being chosen (e.g. HASH instead of JOIN).

[pavel-zotov]

Test for this ticket can't run on FB 5.x: there are no differences neither in explained plans nor in trace statistics
for snapshots before and after fix.
Only cardinality estimation can be checked but this feature avaliable only in FB-6 via rdb$sql() package which is avaliable since 19-sep-2023 [39b0195].
Only second example can be checked (1st and 3rd have proper cardinality estimation on FB 6.x snapshots before and after fix).