SPAGOBI-862 - bad formed sql query in Spagobi Studio 3.4 (postgresql DB)

[KnowageServiceAcc]

Details

Key	Value
Key	SPAGOBI-862
Type	Bug
Status	In Progress
Priority	Critical
Resolution
Affects	3.4.0
Fix	3.5.0
Components	META, SERVER/AdHoc/Qbe
Labels

People

Key	Value
Assignee	Andrea Gioia
Reporter	Carl Col'z

Dates

Key	Value
Created	2012-05-08T20:58:33.000+0200
Updated	2013-11-06T15:03:37.000+0100
Resolved	n.d.

Description

I've built a model for my database an published successfully in spagobi server. However when I try to filter or run a query using QBE engine (even the simpler) I have error. Two of them are detailed below. One using a single table and the other using two related tables. In Spagobi studio 3.3 the same error was reported and it was fixed with a plugin file that one can download, however the plugin does not work with version 3.4 because the plugin is versioned as 1.0.0 (it.eng.spagobi.meta.generator_X.X.X.jar)

(FROM WHERE THE compId prefix came?)

it.eng.spagobi.utilities.engines.SpagoBIEngineServiceException: An error occurred in GET_VALUES_FOR_QBE_FILTER_LOOKUP_ACTION service while executing query: [SELECT DISTINCT t_0.compId.anio FROM Ventas t_0 ORDER BY t_0.compId.anio ASC]
at it.eng.spagobi.engines.qbe.services.core.GetValuesForQbeFilterLookup.service(GetValuesForQbeFilterLookup.java:174)
at it.eng.spago.dispatching.action.ActionCoordinator.invokeServiceBusiness(ActionCoordinator.java:209)
at it.eng.spago.dispatching.action.ActionCoordinator.service(ActionCoordinator.java:158)

---

(LOOK rel_cod_unidneg_in_unid_neg SOME QUESTION HERE

it.eng.spagobi.utilities.engines.SpagoBIEngineServiceException: An error occurred in EXEC_QUERY_ACTION service while executing query: [SELECT t_0.rel_cod_unidneg_in_unid_neg.unid_neg_desc, SUM(t_0.compId.cantidad) FROM Ventas t_0 GROUP BY t_0.rel_cod_unidneg_in_unid_neg.unid_neg_desc]
at it.eng.spagobi.engines.qbe.services.core.ExecuteQueryAction.executeQuery(ExecuteQueryAction.java:217)
at it.eng.spagobi.engines.qbe.services.core.ExecuteQueryAction.service(ExecuteQueryAction.java:106)
at it.eng.spago.dispatching.action.ActionCoordinator.invokeServiceBusiness(ActionCoordinator.java:209)
at it.eng.spago.dispatching.action.ActionCoordinator.service(ActionCoordinator.java:158)
at it.eng.spago.dispatching.httpchannel.AdapterHTTP.service(AdapterHTTP.java:488)
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 it.eng.spagobi.utilities.filters.SpagoBIAccessFilter.doFilter(SpagoBIAccessFilter.java:200)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at it.eng.spagobi.utilities.filters.EngineCheckSessionFilter.doFilter(EngineCheckSessionFilter.java:86)
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 org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
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:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:662)

Comments

Andrea Gioia added a comment - 2012-05-08T21:48:48.958+0200

Two things...

1. you copy there the first part of each stacktrace but the part that is most informative is the last one where the root cause is contained. Can you post the full stack trace?

2. You mentioned a previous bug that has been fixed with a patch on version 3.3. Can you specify exactly what is this bug (the url to the bug report)?

Thank you :)

Carl Col'z added a comment - 2012-05-11T22:10:35.894+0200

I used again SpagoStudio 3.3 and solved the problem. I try to build another datamart with SpagoStudio 3.4 for giving you the full stack trace. I could no remember how I got to the patch for SpagoStudio3.3 (I can no find it), but in the mean time I am attaching the plugin that solved the problem which fortunately I saved.

Carl Col'z added a comment - 2012-05-11T22:18:04.913+0200

Ok. here's a full stack trace for the error (the same lines are repeated two more times so I just put it once). I says 23 more but are not shown in the log.

it.eng.spagobi.engines.qbe.services.core.ServiceExceptionAction.logError: An error occurred in GET_VALUES_FOR_QBE_FILTER_LOOKUP_ACTION service while executing query: [SELECT DISTINCT t_0.rel_cod_grupo_in_grupos.cod_grupo FROM Ventas t_0 ORDER BY t_0.rel_cod_grupo_in_grupos.cod_grupo ASC]
it.eng.spagobi.engines.qbe.services.core.ServiceExceptionAction.logError: The error root cause is: Impossible to read result number
it.eng.spagobi.engines.qbe.services.core.ServiceExceptionAction.logError: The error root cause stack trace is:
java.lang.RuntimeException: Impossible to load data
at it.eng.qbe.statement.jpa.JPQLDataSet.loadData(JPQLDataSet.java:65)
at it.eng.spagobi.engines.qbe.services.core.GetValuesForQbeFilterLookup.service(GetValuesForQbeFilterLookup.java:164)
at it.eng.spago.dispatching.action.ActionCoordinator.invokeServiceBusiness(ActionCoordinator.java:209)
at it.eng.spago.dispatching.action.ActionCoordinator.service(ActionCoordinator.java:158)
at it.eng.spago.dispatching.httpchannel.AdapterHTTP.service(AdapterHTTP.java:488)
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 it.eng.spagobi.utilities.filters.SpagoBIAccessFilter.doFilter(SpagoBIAccessFilter.java:200)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at it.eng.spagobi.utilities.filters.EngineCheckSessionFilter.doFilter(EngineCheckSessionFilter.java:86)
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 org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
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:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.lang.RuntimeException: Impossible to read result number
at it.eng.qbe.statement.jpa.JPQLDataSet.getResultNumber(JPQLDataSet.java:178)
at it.eng.qbe.statement.jpa.JPQLDataSet.loadDataPersistenceProvider(JPQLDataSet.java:89)
at it.eng.qbe.statement.jpa.JPQLDataSet.loadData(JPQLDataSet.java:63)
... 23 more
it.eng.spagobi.engines.qbe.services.core.ServiceExceptionAction.logError: The error full stack trace is:
it.eng.spagobi.utilities.engines.SpagoBIEngineServiceException: An error occurred in GET_VALUES_FOR_QBE_FILTER_LOOKUP_ACTION service while executing query: [SELECT DISTINCT t_0.rel_cod_grupo_in_grupos.cod_grupo FROM Ventas t_0 ORDER BY t_0.rel_cod_grupo_in_grupos.cod_grupo ASC]
at it.eng.spagobi.engines.qbe.services.core.GetValuesForQbeFilterLookup.service(GetValuesForQbeFilterLookup.java:174)
at it.eng.spago.dispatching.action.ActionCoordinator.invokeServiceBusiness(ActionCoordinator.java:209)
at it.eng.spago.dispatching.action.ActionCoordinator.service(ActionCoordinator.java:158)
at it.eng.spago.dispatching.httpchannel.AdapterHTTP.service(AdapterHTTP.java:488)
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 it.eng.spagobi.utilities.filters.SpagoBIAccessFilter.doFilter(SpagoBIAccessFilter.java:200)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at it.eng.spagobi.utilities.filters.EngineCheckSessionFilter.doFilter(EngineCheckSessionFilter.java:86)
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 org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
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:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.lang.RuntimeException: Impossible to load data
at it.eng.qbe.statement.jpa.JPQLDataSet.loadData(JPQLDataSet.java:65)
at it.eng.spagobi.engines.qbe.services.core.GetValuesForQbeFilterLookup.service(GetValuesForQbeFilterLookup.java:164)
... 22 more
Caused by: java.lang.RuntimeException: Impossible to read result number
at it.eng.qbe.statement.jpa.JPQLDataSet.getResultNumber(JPQLDataSet.java:178)
at it.eng.qbe.statement.jpa.JPQLDataSet.loadDataPersistenceProvider(JPQLDataSet.java:89)
at it.eng.qbe.statement.jpa.JPQLDataSet.loadData(JPQLDataSet.java:63)
... 23 more

Andrea Gioia added a comment - 2012-05-14T11:45:46.552+0200

It cannot execute the query. This usually happens when the dataource is not configured properly. Can you execute a query over your model in SpagoBI Studio 3.4?

Javier Valdes added a comment - 2012-06-04T11:40:12.326+0200

I am experiencing the same problem. In my case Windows 7, Oracle Java sdk 1.6.0_25, SpagoBI 3.4, Postgresql 91.

Javier Valdes added a comment - 2012-06-04T13:33:56.857+0200

Hi again. I have test it with Postgres 8.3 and I have the same problem

Anthony H added a comment - 2012-06-14T00:09:24.200+0200

I have the same problem. I am using Mysql. I created a new query in SpagoBI Studio then drag and drop columns to Select Fields and found that every field being added "compid" prefix. I am not sure if it is normal, but I watch the tour "inquiry your business model" and don't see the "compid" prefix.