0027919: Match Landed Cost P&E raises an error in Postgres 8.4

Description

Match Landed Cost P&E raises an error while opening and is not returning results.

This issue only happens in Postgres 8.4, in 9.1 it works properly.

The following error is triggered in the log:

Caused by: org.postgresql.util.PSQLException: ERROR: column "landedcost0_.processed" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 2500
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
    at org.hibernate.loader.Loader.doQuery(Loader.java:802)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.doList(Loader.java:2533)
    ... 48 more
c59635e2 560410 [http-8080-2] ERROR org.openbravo.service.datasource.DataSourceServlet - select coalesce(landedcost3_.M_Lc_Matched_ID, landedcost0_.M_LC_Cost_ID) as col_0_0_, case when landedcost3_.M_Lc_Matched_ID is not null then true else false end as col_1_0_, landedcost2_.Name as col_2_0_, landedcost0_.Amount as col_3_0_, (select array_to_string(array_agg(to_char((materialmg7_.DocumentNo||' - '||materialmg6_.Line))), ',') from M_LC_ReceiptLine_Amt landedcost5_ inner join M_InOutLine materialmg6_ on landedcost5_.M_Inoutline_ID=materialmg6_.M_InOutLine_ID inner join M_InOut materialmg7_ on materialmg6_.M_InOut_ID=materialmg7_.M_InOut_ID, M_LC_Cost landedcost8_ where landedcost5_.M_Lc_Cost_ID=landedcost8_.M_LC_Cost_ID and landedcost5_.M_Lc_Cost_ID=landedcost0_.M_LC_Cost_ID) as col_4_0_, (select array_to_string(array_agg(to_char(product9_.Name)), ',') from M_Product product9_ where product9_.M_Product_ID in (select distinct materialmg11_.M_Product_ID from M_LC_ReceiptLine_Amt landedcost10_ inner join M_InOutLine materialmg11_ on landedcost10_.M_Inoutline_ID=materialmg11_.M_InOutLine_ID, M_Product product12_, M_LC_Cost landedcost13_ where materialmg11_.M_Product_ID=product12_.M_Product_ID and landedcost10_.M_Lc_Cost_ID=landedcost13_.M_LC_Cost_ID and landedcost10_.M_Lc_Cost_ID=landedcost0_.M_LC_Cost_ID)) as col_5_0_, landedcost3_.AMT_Invoice_Currency as col_6_0_, (select sum(landedcost14_.Amount) from M_LC_Matched landedcost14_, M_LC_Cost landedcost15_, C_InvoiceLine invoicelin16_ where landedcost14_.M_Lc_Cost_ID=landedcost15_.M_LC_Cost_ID and landedcost14_.C_Invoiceline_ID=invoicelin16_.C_InvoiceLine_ID and landedcost14_.M_Lc_Cost_ID=landedcost0_.M_LC_Cost_ID and landedcost14_.C_Invoiceline_ID<>invoicelin4_.C_InvoiceLine_ID) as col_7_0_, (select array_to_string(array_agg(to_char((invoice19_.DocumentNo||' - '||invoicelin18_.Line))), ',') from M_LC_Matched landedcost17_ inner join C_InvoiceLine invoicelin18_ on landedcost17_.C_Invoiceline_ID=invoicelin18_.C_InvoiceLine_ID inner join C_Invoice invoice19_ on invoicelin18_.C_Invoice_ID=invoice19_.C_Invoice_ID, M_LC_Cost landedcost20_ where landedcost17_.M_Lc_Cost_ID=landedcost20_.M_LC_Cost_ID and landedcost17_.M_Lc_Cost_ID=landedcost0_.M_LC_Cost_ID and invoicelin18_.C_InvoiceLine_ID<>invoicelin4_.C_InvoiceLine_ID) as col_8_0_, landedcost1_.Referencedate as col_9_0_, landedcost0_.IsMatched as col_10_0_, landedcost0_.IsMatchingAdjusted as col_11_0_, landedcost3_.M_Lc_Matched_ID as col_12_0_, landedcost0_.M_LC_Cost_ID as col_13_0_, landedcost1_.Documentno as col_14_0_, landedcost0_.Line as col_15_0_, landedcost0_.Processed as col_16_0_, currency21_.ISO_Code as col_17_0_ from M_LC_Cost landedcost0_ inner join M_LandedCost landedcost1_ on landedcost0_.M_Landedcost_ID=landedcost1_.M_Landedcost_ID inner join M_LC_Type landedcost2_ on landedcost0_.M_Lc_Type_ID=landedcost2_.M_Lc_Type_ID left outer join M_LC_Matched landedcost3_ on landedcost0_.M_LC_Cost_ID=landedcost3_.M_Lc_Cost_ID and (landedcost3_.C_Invoiceline_ID=?), C_Currency currency21_ cross join C_InvoiceLine invoicelin4_ where landedcost0_.C_Currency_ID=currency21_.C_Currency_ID and (landedcost0_.IsMatched='N' or landedcost3_.M_Lc_Matched_ID is not null) and (landedcost0_.AD_Client_ID in ('0' , '23C59575B9CF467C9620760EB255B389')) and (landedcost0_.AD_Org_ID in ('0' , '7BABA5FF80494CAFA54DEBD22EC46F01' , 'BAE22373FEBE4CCCA24517E23F0C8A48' , '19404EAD144C49A0AF37D54377CF452D' , '2E60544D37534C0B89E765FE29BC0B43')) and (invoicelin4_.C_InvoiceLine_ID is null or invoicelin4_.C_InvoiceLine_ID=?) and landedcost2_.M_Product_ID=? group by coalesce(landedcost3_.M_Lc_Matched_ID, landedcost0_.M_LC_Cost_ID) , landedcost0_.M_LC_Cost_ID , landedcost2_.Name , landedcost0_.Amount , currency21_.ISO_Code , landedcost3_.Amount , case when landedcost3_.M_Lc_Matched_ID is not null then true else false end , landedcost1_.Referencedate , landedcost0_.IsMatched , landedcost0_.IsMatchingAdjusted , landedcost3_.M_Lc_Matched_ID , landedcost0_.M_LC_Cost_ID , invoicelin4_.C_InvoiceLine_ID , landedcost1_.Documentno , landedcost0_.Line , landedcost3_.AMT_Invoice_Currency
c59635e2 560410 [http-8080-2] ERROR org.openbravo.client.kernel.KernelUtils - could not execute query
org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2536)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452)
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
    at org.openbravo.service.datasource.HQLDataSourceService.getData(HQLDataSourceService.java:176)
    at org.openbravo.service.datasource.ReadOnlyDataSourceService.fetchJSONObject(ReadOnlyDataSourceService.java:130)
    at org.openbravo.service.datasource.ReadOnlyDataSourceService.fetch(ReadOnlyDataSourceService.java:68)
    at org.openbravo.service.datasource.DataSourceServlet.doFetch(DataSourceServlet.java:261)
    at org.openbravo.service.datasource.DataSourceServlet.doPost(DataSourceServlet.java:744)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
    at org.openbravo.base.HttpBaseServlet.serviceInitialized(HttpBaseServlet.java:225)
    at org.openbravo.base.secureApp.HttpSecureAppServlet.service(HttpSecureAppServlet.java:449)
    at org.openbravo.client.kernel.BaseKernelServlet.callServiceInSuper(BaseKernelServlet.java:92)
    at org.openbravo.client.kernel.BaseKernelServlet.service(BaseKernelServlet.java:63)
    at org.openbravo.service.datasource.DataSourceServlet.service(DataSourceServlet.java:140)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.utils.SessionExpirationFilter.doFilter(SessionExpirationFilter.java:66)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.utils.CharsetFilter.doFilter(CharsetFilter.java:35)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.client.kernel.KernelFilter$1.doAction(KernelFilter.java:62)
    at org.openbravo.dal.core.ThreadHandler.run(ThreadHandler.java:46)
    at org.openbravo.client.kernel.KernelFilter.doFilter(KernelFilter.java:71)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.dal.core.DalRequestFilter$1.doAction(DalRequestFilter.java:81)
    at org.openbravo.dal.core.ThreadHandler.run(ThreadHandler.java:46)
    at org.openbravo.dal.core.DalRequestFilter.doFilter(DalRequestFilter.java:103)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at flex.messaging.security.TomcatValve.invoke(TomcatValve.java:143)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:291)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:744)

Steps To Reproduce

Go to Landed cost window and create a new one.
Go to Cost tab and select a "Product" type landed cost type. Enter an amount.
Go to Receipt tab and select a receipt.
Process Landed Cost.

Go to purchase invoice and create a new one including same "Product", enter same amount. Complete and post the invoice.

Go to lines and press "Match LC Cost". Realize that the P&E doesn't display any results and the error described above is triggered in the log.

Proposed Solution

Add lcc.processed to group by clause of "Match LC Costs" Hql Query

Notes
(0071064) hgbot (developer) 2014-10-20 18:56	Repository: erp/devel/pi Changeset: 8e150ca07c2f9523ae6ae227bb40a64671d2aa16 Author: Unai Martirena <unai.martirena <at> openbravo.com> Date: Mon Oct 20 18:54:45 2014 +0200 URL: http://code.openbravo.com/erp/devel/pi/rev/8e150ca07c2f9523ae6ae227bb40a64671d2aa16 [^] Fixes issue 27919: Match Landed Cost P&E raises an error in Postgres 8.4 --- M src-db/database/sourcedata/AD_TABLE.xml ---

(0071243) Sandrahuguet (developer) 2014-10-29 12:29	Verified

(0072956) hudsonbot (developer) 2014-12-30 23:23	A changeset related to this issue has been promoted main and to the Central Repository, after passing a series of tests. Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/6525fe229e06 [^] Maturity status: Test

Issue History
Date Modified	Username	Field	Change
2014-10-20 18:54	umartirena	New Issue
2014-10-20 18:54	umartirena	Assigned To	=> dmiguelez
2014-10-20 18:54	umartirena	Modules	=> Core
2014-10-20 18:54	umartirena	Triggers an Emergency Pack	=> No
2014-10-20 18:56	hgbot	Checkin
2014-10-20 18:56	hgbot	Note Added: 0071064
2014-10-20 18:56	hgbot	Status	new => resolved
2014-10-20 18:56	hgbot	Resolution	open => fixed
2014-10-20 18:56	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/devel/pi/rev/8e150ca07c2f9523ae6ae227bb40a64671d2aa16 [^]
2014-10-29 12:29	Sandrahuguet	Review Assigned To	=> Sandrahuguet
2014-10-29 12:29	Sandrahuguet	Note Added: 0071243
2014-10-29 12:29	Sandrahuguet	Status	resolved => closed
2014-10-29 12:30	Sandrahuguet	Assigned To	dmiguelez => umartirena
2014-12-30 23:23	hudsonbot	Checkin
2014-12-30 23:23	hudsonbot	Note Added: 0072956

Relationships [ Relation Graph ] [ Dependency Graph ]