Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

"Nested aggregate functions are not allowed" error on FB30 [CORE6256] #6498

Closed
firebird-automations opened this issue Feb 27, 2020 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Attila Molnár (e_pluribus_unum)

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)

@firebird-automations
Copy link
Collaborator Author

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...

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@EPluribusUnum
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants