Firebird - QueryRecCount with GROUP BY

[sasa.pikija]

Hi.
I'm using Unidac ver 7.4.11 26-Nov-18, on Delphi 10.2 Update 3 and Firebird 2.5 database.

Problem is in simple query which returns error when QueryRecCount property is True.
SQL query

Code: Select all

Select Group, Item, SUM(Amount) AS Amount FROM TestTbl GROUP BY 1,2;

Error is: SQL error code = -104 Invalid column position used in the GROUP BY clause

I watched SQL execution in SQL monitor and SQL that you do for QueryRecCount is:

Code: Select all

SELECT COUNT(*) FROM ( SELECT 1 AS C FROM TestTbl GROUP BY 1,2 ) 

So you replaced Group, Item, SUM(Amount) AS Amount with 1 but you leave GROUP BY 1,2 statement. Maybe you should have to replace entire GROUP BY with 1 or remove GROUP BY entirely.

When I write SQL like:

Code: Select all

Select Group, Item, SUM(Amount) AS Amount FROM TestTbl GROUP BY Group, Item;

then everything is OK because you create QueryRecCount SQL like:

Code: Select all

SELECT COUNT(*) FROM ( SELECT 1 AS C FROM TestTbl GROUP BY Group,Item) 

That is error free but not happiest solution because you are doing GROUP BY on columns that are not in SELECT.



I tried same query in PostgreSQL and it works without any errors because in PostgreSQL you make SQL like:

Code: Select all

SELECT COUNT(*) FROM ( SELECT Group, Item, SUM(Amount) AS Amount FROM TestTbl GROUP BY 1,2 ) 

That SQL works in Firebird 2.5 also.

Example that I posted is simplified, in real life, i really need to use column position instead of column names.

Can you fix QueryRecCount in Firebird with GROUP BY clause or give me some other solution which leave column position in group by clause.

Thanks.

[ViktorV]

Thanks for the information. We reproduced the issue and it will be fixed in the next build.