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: Ascending order - Click to sort in descending order
No changes have yet been made on this issue.