Commented by: Sean Leyne (seanleyne)

While it might compile, the CTE solution doesn't look to be equivalent to the original SQL...

You are not providing the CTE with the appropriate kf.ktfj_id and kf.mert_id values for the ktfj_valtoszam() SP in the CTE...

Commented by: Attila Molnár (e_pluribus_unum)

I was suprised that the workaround compiled, I tought that it must be independent from the main SELECT.

I'll check the params and looking for other workaround.

Still, not accepting the first select is a bug.

Commented by: Attila Molnár (e_pluribus_unum)

The workaround works as expected in FB25, it is a vaild rewrite. The ktfj_valtoszam receives the expected values from "ktkf" table, not null values.

I'll check in FB30.

Commented by: Attila Molnár (e_pluribus_unum)

The workaround also works as expected in FB30, it is a vaild rewrite. The ktfj_valtoszam receives the expected values from "ktkf" table, not null values.

This is still an issue with FB30 (works as expected in FB40)

Why this issue has been marked as 'fixed' for 4.0.0 ? It does not work on all FB 3.x+.
This is re-formatted SQL:

set bail on;
set list on;
shell if exist c:\temp\tmp4test.fdb del /q /f c:\temp\tmp4test.fdb;
create database 'localhost:c:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';

set term ^;
create or alter procedure ktfj_valtoszam(
    a_ktfj_id int
   ,a_rt_mert_id int
   ,a_kf_mert_id int
) returns (
    szorzo int
)
as begin
    szorzo = a_ktfj_id + a_rt_mert_id + a_kf_mert_id;
    suspend;
end
^
recreate table ktrt(
    vdb int
   ,db int
   ,ktdb int
   ,mert_id int
)
^

recreate table ktkf(
    keszlet_db int
   ,ktfj_id int
   ,mert_id int
)
^
set term ;^
commit;

set echo on;
select
sum (
    kf.keszlet_db +
    (
        select
        sum (
        (
            case when rt.vdb is null then rt.db else rt.vdb end - coalesce(rt.ktdb, 0)
        )
        *
        (
            select kv.szorzo
            from ktfj_valtoszam(kf.ktfj_id, rt.mert_id, kf.mert_id) kv)
        )
        from ktrt rt
    )
) szabad_db
from ktkf kf;

On FB 2.5.9.27156 in completes OK and displays SZABAD_DB <null>
On FB 4.0.5.3086, 5.0.1.1383, 6.0.0.336 it fails with:

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Nested aggregate and window functions are not allowed

I've marked it as fixed because of the comment

This is still an issue with FB30 (works as expected in FB40)

But i can't understand: what means phrase "works as expected in FB40" ? this query is not compiled in all FB >= 3.x

4.0.0 was not yet release when I was testing on FB40, it was a snapshot/alpha/beta/rc (can't remember exactly).
I meant "works as expected" that FB25 and FB40 was behaving the same.

So, how we have to consider current state (i.e. error with SQLSTATE = 42000) in every (fresh) FB >= 4.x: is it expected or no ?

BTW, this works OK:

select
    sum(
            m.i
            *
            (
                select sum(i) s from (select 1 i from rdb$types rows 2)
            )
       )
from (
    select 4 i from rdb$types rows 3
) m;