Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Mondrian generates the wrong SQL (SqlTupleReader) when aggregations is called.

  1. #1
    Join Date
    Apr 2014
    Posts
    18

    Default Mondrian generates the wrong SQL (SqlTupleReader) when aggregations is called.

    I have a problem when using aggregate tables and querying all members (No Filter) of a dimension level.
    I believe the problem have something to do with the definition of the level column and column name (One numeric, the other one a label).
    The error message I got is : "PSQLException: ERROR: missing FROM-clause entry for table (Aggregation Table).
    The problem disappear when a filter is added into the MDX. Below is the full log:

    ==> mondrian_sql.log <==
    2014-07-23 09:51:28,437 DEBUG [mondrian.sql] 2351: SqlTupleReader.readTuples [[d_charge_status.h_charge_status].[charge_status]]: executing sql [select "dim_charge_status"."charge_status_id" as "c0", "dim_charge_status"."charge_status_name" as "c1" from "dw"."dim_charge_status" as "dim_charge_status" where "dim_charge_status"."charge_status_id" = "agg_changestatus_v"."dim_charge_status_charge_statu" group by "dim_charge_status"."charge_status_id", "dim_charge_status"."charge_status_name" order by "dim_charge_status"."charge_status_id" ASC NULLS LAST]
    2014-07-23 09:51:28,439 DEBUG [mondrian.sql] 2351: , exec+fetch 1 ms, 0 rows
    2014-07-23 09:51:28,439 DEBUG [mondrian.sql] 2351: , failed (org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "agg_changestatus_v"
    Position: 205)


    ==> catalina.out <==
    09:51:28,441 ERROR [QueryResource] Cannot execute query (478658ED-C7CD-E2DE-A45B-330CF8C60EA3)
    org.saiku.service.util.exception.SaikuServiceException: runId:157 Can't execute query: 478658ED-C7CD-E2DE-A45B-330CF8C60EA3
    at org.saiku.service.olap.OlapQueryService.execute(OlapQueryService.java:305)
    at org.saiku.service.olap.OlapQueryService.execute(OlapQueryService.java:223)
    at org.saiku.web.rest.resources.QueryResource.execute(QueryResource.java:745)
    at sun.reflect.GeneratedMethodAccessor207.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
    at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185)
    at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
    at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
    at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
    at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
    at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
    at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
    at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511)
    at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1442)
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1391)
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1381)
    at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416)
    at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:538)
    at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:716)
    at org.pentaho.platform.web.servlet.JAXRSPluginServlet.service(JAXRSPluginServlet.java:62)
    at org.saiku.plugin.resources.ExtendedJAXRSPluginServlet.service(ExtendedJAXRSPluginServlet.java:65)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.pentaho.platform.web.servlet.JAXRSPluginServlet.service(JAXRSPluginServlet.java:67)
    at org.pentaho.platform.web.servlet.PluginDispatchServlet.service(PluginDispatchServlet.java:89)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.pentaho.platform.web.http.filters.PentahoWebContextFilter.doFilter(PentahoWebContextFilter.java:161)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.pentaho.platform.web.http.filters.PentahoRequestContextFilter.doFilter(PentahoRequestContextFilter.java:83)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:378)
    at org.springframework.security.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
    at org.springframework.security.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.springframework.security.ui.ExceptionTranslationFilter.doFilterHttp(ExceptionTranslationFilter.java:101)
    at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.springframework.security.providers.anonymous.AnonymousProcessingFilter.doFilterHttp(AnonymousProcessingFilter.java:105)
    at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.springframework.security.ui.basicauth.BasicProcessingFilter.doFilterHttp(BasicProcessingFilter.java:174)
    at org.pentaho.platform.web.http.security.PentahoBasicProcessingFilter.doFilterHttp(PentahoBasicProcessingFilter.java:88)
    at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.springframework.security.context.HttpSessionContextIntegrationFilter.doFilterHttp(HttpSessionContextIntegrationFilter.java:235)
    at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.pentaho.platform.web.http.filters.HttpSessionPentahoSessionIntegrationFilter.doFilter(HttpSessionPentahoSessionIntegrationFilter.java:265)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.springframework.security.wrapper.SecurityContextHolderAwareRequestFilter.doFilterHttp(SecurityContextHolderAwareRequestFilter.java:91)
    at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.springframework.security.util.FilterChainProxy.doFilter(FilterChainProxy.java:175)
    at org.springframework.security.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:99)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.pentaho.platform.web.http.filters.SystemStatusFilter.doFilter(SystemStatusFilter.java:59)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.pentaho.platform.web.http.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:112)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.pentaho.platform.web.http.filters.WebappRootForwardingFilter.doFilter(WebappRootForwardingFilter.java:66)
    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.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:470)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:744)
    Caused by: org.olap4j.OlapException: mondrian gave exception while executing query
    at mondrian.olap4j.MondrianOlap4jConnection$Helper.createException(MondrianOlap4jConnection.java:840)
    at mondrian.olap4j.MondrianOlap4jStatement.executeOlapQueryInternal(MondrianOlap4jStatement.java:423)
    at mondrian.olap4j.MondrianOlap4jStatement.executeOlapQuery(MondrianOlap4jStatement.java:347)
    at org.saiku.olap.query.OlapQuery.execute(OlapQuery.java:290)
    at org.saiku.service.olap.OlapQueryService.execute(OlapQueryService.java:249)
    ... 79 more
    Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select NON EMPTY {Hierarchize({[Measures].[bill_proc_count]})} ON COLUMNS,
    NON EMPTY {Hierarchize({[d_charge_status.h_charge_status].[charge_status].Members})} ON ROWS
    from [opsChargesReceivablesCube]
    ]
    at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:972)
    at mondrian.olap.Util.newInternal(Util.java:2403)
    at mondrian.olap.Util.newError(Util.java:2419)
    at mondrian.rolap.RolapConnection.executeInternal(RolapConnection.java:706)
    at mondrian.rolap.RolapConnection.access$000(RolapConnection.java:51)
    at mondrian.rolap.RolapConnection$1.call(RolapConnection.java:622)
    at mondrian.rolap.RolapConnection$1.call(RolapConnection.java:621)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    ... 1 more
    Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal error: Populating member cache with members for [[d_charge_status.h_charge_status].[charge_status]]; sql=[select "dim_charge_status"."charge_status_id" as "c0", "dim_charge_status"."charge_status_name" as "c1" from "dw"."dim_charge_status" as "dim_charge_status" where "dim_charge_status"."charge_status_id" = "agg_changestatus_v"."dim_charge_status_charge_statu" group by "dim_charge_status"."charge_status_id", "dim_charge_status"."charge_status_name" order by "dim_charge_status"."charge_status_id" ASC NULLS LAST]
    at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:972)
    at mondrian.olap.Util.newInternal(Util.java:2403)
    at mondrian.olap.Util.newError(Util.java:2419)
    at mondrian.rolap.SqlStatement.handle(SqlStatement.java:353)
    at mondrian.rolap.SqlStatement.execute(SqlStatement.java:253)
    at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.java:334)
    at mondrian.rolap.SqlTupleReader.prepareTuples(SqlTupleReader.java:395)
    at mondrian.rolap.SqlTupleReader.readMembers(SqlTupleReader.java:506)
    at mondrian.rolap.SqlMemberSource.getMembersInLevel(SqlMemberSource.java:527)
    at mondrian.rolap.SmartMemberReader.getMembersInLevel(SmartMemberReader.java:146)
    at mondrian.rolap.RolapCubeHierarchy$CacheRolapCubeHierarchyMemberReader.getMembersInLevel(RolapCubeHierarchy.java:727)
    at mondrian.rolap.RolapSchemaReader.getLevelMembers(RolapSchemaReader.java:566)
    at mondrian.olap.DelegatingSchemaReader.getLevelMembers(DelegatingSchemaReader.java:242)
    at mondrian.olap.fun.FunUtil.getNonEmptyLevelMembers(FunUtil.java:2195)
    at mondrian.olap.fun.FunUtil.levelMembers(FunUtil.java:2209)
    at mondrian.olap.fun.LevelMembersFunDef$1.evaluateList(LevelMembersFunDef.java:37)
    at mondrian.olap.fun.HierarchizeFunDef$1.evaluateList(HierarchizeFunDef.java:46)
    at mondrian.calc.impl.AbstractListCalc.evaluateIterable(AbstractListCalc.java:71)
    at mondrian.rolap.RolapResult.executeAxis(RolapResult.java:875)
    at mondrian.rolap.RolapResult.evalLoad(RolapResult.java:700)
    at mondrian.rolap.RolapResult.loadMembers(RolapResult.java:656)
    at mondrian.rolap.RolapResult.<init>(RolapResult.java:288)
    at mondrian.rolap.RolapConnection.executeInternal(RolapConnection.java:671)
    ... 7 more
    Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "agg_changestatus_v"
    Position: 205
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1834)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:372)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:252)
    at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at mondrian.rolap.SqlStatement.execute(SqlStatement.java:200)

  2. #2
    Join Date
    Apr 2014
    Posts
    18

    Default

    The solution to this problem is to remove the "nameColumn" from the dimension level if the level is associated as a level at the declared aggregation table. I consider this a big bug on Mondrian, considering that the use of "column" for ID and "nameColumn" for description. When creating the aggregation tables using the "Aggregation Designer" application, the app always use the "column" (ID) reference to build the column at the aggregation table, on the other hand the Mondrian engine seems to be utilizing the "nameColumn" to match the and use the aggregation table during a query. Apparently the SQLTupleReader class gets confuse and it deliver an incomplete query resulting in the "missing FROM-clause" error.

  3. #3
    Join Date
    Aug 2006
    Posts
    287

    Default

    On what version of Mondrian did you encounter this error?

  4. #4
    Join Date
    Apr 2014
    Posts
    18

    Default

    This is Mondrian Release 5.0.1.stable.

  5. #5
    Join Date
    Aug 2014
    Posts
    4

    Default

    Quote Originally Posted by reynunez View Post
    The solution to this problem is to remove the "nameColumn" from the dimension level if the level is associated as a level at the declared aggregation table. I consider this a big bug on Mondrian, considering that the use of "column" for ID and "nameColumn" for description. When creating the aggregation tables using the "Aggregation Designer" application, the app always use the "column" (ID) reference to build the column at the aggregation table, on the other hand the Mondrian engine seems to be utilizing the "nameColumn" to match the and use the aggregation table during a query. Apparently the SQLTupleReader class gets confuse and it deliver an incomplete query resulting in the "missing FROM-clause" error.
    And what is the solution if I need to show the nameColumn?

  6. #6
    Join Date
    Apr 2014
    Posts
    18

    Default

    Unfortunately the only apparent way to use aggregation tables is using the field description at the level column. The problem using the "nameColumn" is that Mondrian use the name column to match the aggregation table to be use in the query process. Obviously the purpose of using ID, description is useless when using aggregation tables.

    This is a bug !! The same bug was reported at the JIRA Pentaho site: http://jira.pentaho.com/browse/MONDRIAN-1034

    If anybody knows any other way around , please let us know. Thanks
    Last edited by reynunez; 08-28-2014 at 09:16 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.