Issue Details (XML | Word | Printable)

Key: CORE-6256
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Attila Molnár
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

"Nested aggregate functions are not allowed" error on FB30

Created: 27/Feb/20 02:19 PM   Updated: 28/Feb/20 09:03 AM
Component/s: Engine
Affects Version/s: 3.0.5
Fix Version/s: None

QA Status: No test


 Description  « Hide
Hi!

This works for FB25 (Dialect1), but not for FB30 (Dialect3).

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



Our workaround : extract the subselect into CTE and then FB30 accepts it.

WITH
  sub AS
    (SELECT
        (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) db
    FROM ktrt rt)
SELECT
    SUM(
        kf.keszlet_db +
        (SELECT
            SUM(db)
        FROM sub)) szabad_db
FROM ktkf kf

(I can send the database on request)

 All   Comments   Change History   Subversion Commits      Sort Order: Descending order - Click to sort in ascending order
Attila Molnár added a comment - 28/Feb/20 09:03 AM
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.

Attila Molnár added a comment - 28/Feb/20 08:31 AM
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.

Attila Molnár added a comment - 28/Feb/20 07:30 AM
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.

Sean Leyne added a comment - 27/Feb/20 06:52 PM
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...