W dniu 2014-09-03 o 08:51, Svein Erling Tysvær
svein.erling.tysvaer@... [firebird-support] pisze:

>
>
> >Hi,
> >
> >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
> >
> >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
> > ) 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?
>
> Hi Macma!
>
> I would have tried this query (I've assumed that ID is the primary key
> of IDY_DO_ZAPYTANIA) and checked whether the result was as desired
> (admittedly, I'm not used to using MIN in a subselect):
>
> select
> ke.I_ID_GRUPY,
> ke.C_SYMBOL,
> (select min(cs.C_OPIS)
> from T_CENY_SKLADNIKI as cs
> join T_CENY_W_OKRESIE as cwo
> on cwo.I_ID_CENY_OKRS = cs.I_ID_CENY_OKRS
> join R_KOSZTY kk on cwo.I_ID_CENNIKA = kk.I_ID_CENNIKA
> where cs.C_SYMBOL = ke.C_SYMBOL
> and kk.I_ID_KOSZTY = ko.I_ID_KOSZTY) 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,
> max(ke.I_ORDER_INDEX) I_ORDER
> from R_KOSZTY k
> join R_KOSZT_OKRESY as ko on ko.I_ID_KOSZTY = k.I_ID_KOSZTY
> join R_KOSZT_ELEMENTY as ke on ke.I_ID_KOSZT_OKRESY = ko.I_ID_KOSZT_OKRESY
> join IDY_DO_ZAPYTANIA z on k.I_ID_KOSZTY = z.ID
> where ke.SI_USEINSUMMARY = 1
> group by ke.I_ID_GRUPY, ke.C_SYMBOL, 3, SI_WYKLADNIK, SI_IDJEDNOSTKA,
> SI_STPOTYPE, SI_CURRENCYPERVALUE
> order by 12
>

I get the same error with Your query

>
> I don't know whether your error is a result of grouping by C_OPIS_X (I
> didn't think it was allowed), ordering by a field you do not select (I
> added it to the output, change back if this isn't the problem) or
> something else. Using LEFT JOIN as opposed to [INNER] JOIN reduces the
> options for the optimizer - it is sometimes useful as part of
> optimization, but only after experiencing the original query being slow.
>
> If this doesn't solve your problem, please tell us more about your
> table definitions - I've no clue which fields are unique or having
> lots of duplicates in your tables, nor which indexes/keys you've
> defined and that limits the possible modifications I (and others on
> this list) can do to your query.
>

Here You can download sample database
https://sydel.technicon.com.pl/~grzegorz/tmp/EKONOMICZNA.zip
I've check that this problem also occur when upgrading from 2.5.2 to 2.5.3

Best Regards,
macma

>
> HTH,
> Set
>
> PS! I've never used Fb 2.1 myself, only 0.9.4, 1.5 and 2.5.
>
>