03.09.2014 09:44, Macma wrote:

>
> I found that after upgrade from FireBird 2.1.5 Update 1 to 2.1.6 my
> query stop working with error "Dynamic SQL Error SQL error code = -104
> Invalid expression in the select list (not contained in either an
> aggregate function or the GROUP BY clause)". The same error occur on 2.5.3

Interesting. The stricter checks were not really intended, it looks like
an indirect result of some bugfix. I hope this issue doesn't reject
formally correct queries.

> select
> ke.I_ID_GRUPY,
> ke.C_SYMBOL,
> (
> select first 1 cs.C_OPIS from T_CENY_SKLADNIKI as cs
> left join T_CENY_W_OKRESIE as cwo on (cwo.I_ID_CENY_OKRS =
> cs.I_ID_CENY_OKRS)
> where cs.C_SYMBOL = ke.C_SYMBOL and cwo.I_ID_CENNIKA = any (select
> I_ID_CENNIKA from R_KOSZTY as kk where kk.I_ID_KOSZTY = ko.I_ID_KOSZTY)
> group by cs.C_OPIS

Out of curiosity, why both "FIRST 1 cs.C_OPIS" and "GROUP BY cs.C_OPIS"?
The grouping looks redundant here.

> ) as C_OPIS_X,
> sum(ke.N_VALUE) as N_VALUE_SUMA,
> sum(ke.N_VATIN) as N_VATIN_SUMA,
> sum(ke.N_COSTNETTO) as N_COSTNETTO_SUMA,
> sum(ke.N_COSTBRUTTO) as N_COSTBRUTTO_SUMA,
> ke.SI_WYKLADNIK, ke.SI_IDJEDNOSTKA, ke.SI_STPOTYPE,
> ke.SI_CURRENCYPERVALUE
> from
> R_KOSZTY k
> left join R_KOSZT_OKRESY as ko on (ko.I_ID_KOSZTY = k.I_ID_KOSZTY)
> left join R_KOSZT_ELEMENTY as ke on (ke.I_ID_KOSZT_OKRESY =
> ko.I_ID_KOSZT_OKRESY)
> where ke.SI_USEINSUMMARY = 1 and k.I_ID_KOSZTY = any (select ID from
> IDY_DO_ZAPYTANIA)
> group by ke.I_ID_GRUPY, ke.C_SYMBOL, C_OPIS_X, SI_WYKLADNIK,
> SI_IDJEDNOSTKA, SI_STPOTYPE, SI_CURRENCYPERVALUE
> order by max(ke.I_ORDER_INDEX)
>
> Any idea how to overcome this issue?

Instead of grouping on the subquery, you need to group on its
dependencies. At the first glance, there are two of them: ke.C_SYMBOL
and ko.I_ID_KOSZTY. The latter one is missing in the GROUP BY list.


Dmitry