7 Replies Latest reply on Dec 15, 2014 3:06 PM by Steven Hawkins

    Unrelated order by column CASE WHEN upon generated SQL query from Mondrian

    Arunachala Shetty Newbie

      Hello,

       

      Please help me in resolving Teiid error. Details are below:

       

      Versions:

      Teiid 8.8.0.

       

      Analysis from my end:

      I cannot use group by along with case when function in my teiid database development environment. Equivalent query is executed in my mysql command prompt. I found similar issue resolved in QueryResolverException upon generated SQL query from Mondrian and patch is provided as per [TEIID-1250] Allow usage of select expressions in order by, even with group by/distinct. - JBoss Issue Tracker". Whether this patch is updated in Teiid 8.8 or do I need to need to do something else?

       

      Stacktrace:

      I have setup a simple table in mysql, created a VDB using JBOSS Teiid and created a Mondrian schema. When I tried to analyze data using using mondrian for Teiid VDB I am getting following error:

      10:13:06,570 WARN  [org.teiid.PROCESSOR] (Worker8_QueryProcessorQueue67) hPO3iPY4Tw1Q TEIID30020 Processing exception for request hPO3iPY4Tw1Q.0 'TEIID30088 Unrelated order by column CASE WHEN Person.FirstName IS NULL THEN 1 ELSE 0 END cannot be used in a SET query, with SELECT DISTINCT, or GROUP BY'. Originally QueryResolverException ResolverUtil.java:422. Enable more detailed logging to see the entire stacktrace.

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)  mondrian.olap.MondrianException: Mondrian Error:Internal error: Populating member cache with members for [[Dim1.Hier1].[Lvl1]]; sql=[select "Person"."FirstName" as "c0" from "Person" as "Person" group by "Person"."FirstName" order by CASE WHEN "Person"."FirstName" IS NULL THEN 1 ELSE 0 END, "Person"."FirstName" ASC]

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:977)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.olap.Util.newInternal(Util.java:2404)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.olap.Util.newError(Util.java:2420)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.SqlStatement.handle(SqlStatement.java:352)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.SqlStatement.execute(SqlStatement.java:252)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.java:350)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.SqlTupleReader.prepareTuples(SqlTupleReader.java:401)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.SqlTupleReader.readMembers(SqlTupleReader.java:512)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.SqlMemberSource.getMembersInLevel(SqlMemberSource.java:527)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.NoCacheMemberReader.getMembersInLevel(NoCacheMemberReader.java:133)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.RolapCubeHierarchy$CacheRolapCubeHierarchyMemberReader.getMembersInLevel(RolapCubeHierarchy.java:724)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.RolapSchemaReader.getLevelMembers(RolapSchemaReader.java:566)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.RolapSchemaReader.getLevelMembers(RolapSchemaReader.java:552)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.RolapCube$RolapCubeSchemaReader.getLevelMembers(RolapCube.java:2815)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.RolapSchemaReader.getHierarchyRootMembers(RolapSchemaReader.java:79)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.olap.Util.lookupHierarchyRootMember(Util.java:1109)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.olap.HierarchyBase.lookupChild(HierarchyBase.java:169)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.olap.DimensionBase.lookupChild(DimensionBase.java:112)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.olap.CubeBase.lookupChild(CubeBase.java:142)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.RolapCube.lookupChild(RolapCube.java:2638)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.RolapSchemaReader.getElementChild(RolapSchemaReader.java:381)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.olap.Util.lookupCompound(Util.java:750)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.RolapSchemaReader.lookupCompoundInternal(RolapSchemaReader.java:443)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.RolapSchemaReader.lookupCompound(RolapSchemaReader.java:428)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.RolapCube$RolapCubeSchemaReader.getMemberByUniqueName(RolapCube.java:2908)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.RolapSchemaReader.getMemberByUniqueName(RolapSchemaReader.java:388)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at java.lang.reflect.Method.invoke(Unknown Source)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.rolap.RolapUtil$2.invoke(RolapUtil.java:111)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at $Proxy231.getMemberByUniqueName(Unknown Source)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.olap4j.MondrianOlap4jCube.lookupMember(MondrianOlap4jCube.java:208)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.olap4j.MondrianOlap4jCube.lookupMember(MondrianOlap4jCube.java:196)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at mondrian.olap4j.MondrianOlap4jCube.lookupMember(MondrianOlap4jCube.java:34)

      10:13:06,570 ERROR [stderr] (http-/0.0.0.0:8080-5)      at org.olap4j.query.QueryDimension.include(QueryDimension.java:112)

      ...

      ...

       

      Thank you,

      Arunachal