Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0027919Openbravo ERP04. Warehouse managementpublic2014-10-20 18:542014-12-30 23:23
Reporterumartirena 
Assigned Toumartirena 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned ToSandrahuguet
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0027919: Match Landed Cost P&E raises an error in Postgres 8.4
DescriptionMatch 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 ReproduceGo 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 SolutionAdd lcc.processed to group by clause of "Match LC Costs" Hql Query
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2014-10-20 18:54umartirenaNew Issue
2014-10-20 18:54umartirenaAssigned To => dmiguelez
2014-10-20 18:54umartirenaOBNetwork customer => No
2014-10-20 18:54umartirenaModules => Core
2014-10-20 18:54umartirenaTriggers an Emergency Pack => No
2014-10-20 18:56hgbotCheckin
2014-10-20 18:56hgbotNote Added: 0071064
2014-10-20 18:56hgbotStatusnew => resolved
2014-10-20 18:56hgbotResolutionopen => fixed
2014-10-20 18:56hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/8e150ca07c2f9523ae6ae227bb40a64671d2aa16 [^]
2014-10-29 12:29SandrahuguetReview Assigned To => Sandrahuguet
2014-10-29 12:29SandrahuguetNote Added: 0071243
2014-10-29 12:29SandrahuguetStatusresolved => closed
2014-10-29 12:30SandrahuguetAssigned Todmiguelez => umartirena
2014-12-30 23:23hudsonbotCheckin
2014-12-30 23:23hudsonbotNote Added: 0072956

Notes
(0071064)
hgbot   
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   
2014-10-29 12:29   
Verified
(0072956)
hudsonbot   
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