Filter predicate not pushed into CTE computing aggregate with group by expression

[sim1984]

Firebird 5.0.4, 6.0.

The following indices are available:

CREATE INDEX HORSE_IDX_BIRTHDAY ON HORSE (BIRTHDAY);
CREATE INDEX HORSE_IDX_BIRTHYEAR ON HORSE COMPUTED BY (EXTRACT(YEAR FROM BIRTHDAY));

Good query:

with
  t as (
     select
       birthday,
       count(*) as cnt
     from horse
     group by 1
  )
select *
from t
where birthday = date '12.05.2007';

The date of birth filter is pushed into the CTE and we have a quick plan:

Select Expression
    -> Filter
        -> Aggregate
            -> Filter
                -> Table "HORSE" as "T HORSE" Access By ID
                    -> Index "HORSE_IDX_BIRTHDAY" Range Scan (full match)

Now let's replace it with grouping by an expression for which an index exists.

with
  t as (
     select
       extract(year from birthday) as birthyear,
       count(*) as cnt
     from horse
     group by 1
  )
select *
from t
where birthyear = 2007;

We have a bad plan - full index scan, aggregation, and then a filter:

Select Expression
    -> Filter
        -> Aggregate
            -> Table "HORSE" as "T HORSE" Access By ID
                -> Index "HORSE_IDX_BIRTHYEAR" Full Scan

--------------------------------------------------------------------------------
PLAN (T HORSE ORDER HORSE_IDX_BIRTHYEAR)
------ Performance info ------
Prepare time = 0ms
Execute time = 422ms
Avg fetch time = 422,00 ms
Current memory = 1 721 377 360
Max memory = 1 721 581 296
Memory buffers = 102 400
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 1 341 552

For comparison:

with
  t as (
     select
       extract(year from birthday) as birthyear,
       count(*) as cnt
     from horse
     where extract(year from birthday) = 2007
     group by 1
  )
select *
from t;

Good plan and execution time:

Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "T HORSE" Access By ID
                -> Index "HORSE_IDX_BIRTHYEAR" Range Scan (full match)

PLAN (T HORSE ORDER HORSE_IDX_BIRTHYEAR)
------ Performance info ------
Prepare time = 0ms
Execute time = 15ms
Avg fetch time = 15,00 ms
Current memory = 1 720 459 536
Max memory = 1 720 476 576
Memory buffers = 102 400
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 19 726