Re: [firebird-support] Re: Query stop working after upgrade from 2.1.5 to 2.1.6
On Wed, 03 Sep 2014 10:31:46 +0400, "Dmitry Yemanov dim...@users.sourceforge.net [firebird-support]" wrote: > 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. At first glance I would have expected this query to have failed in all previous Firebird versions as well (I believe most SQL dialects, and afaik also in Firebird) using the alias defined in the select in any other clause on the same level doesn't work. The fact that it worked in 2.1.5 might be the anomaly, not that it doesn't work in 2.1.6. Mark
Re: [firebird-support] Re: Query stop working after upgrade from 2.1.5 to 2.1.6
W dniu 2014-09-03 o 08:31, Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] pisze: > 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. Because I have to sum all element with the same "ke.C_SYMBOL" and description of that symbol are changing over time i need to display first found. But You right that "group by cs.C_OPIS" are not necessary. >>) 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. When I add "ko.I_ID_KOSZTY" to GROUP BY list the results are not what I expected because I get partial sum for every "ke.C_SYMBOL" and "ko.I_ID_KOSZTY" insted of partial sum of elements with different "ke.C_SYMBOL" only. I share sample database here https://sydel.technicon.com.pl/~grzegorz/tmp/EKONOMICZNA.zip Best Regards, macma > > > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++ > > > Yahoo Groups Links > > > ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: Query stop working after upgrade from 2.1.5 to 2.1.6
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 ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
