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

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

arunachal Nov 23, 2014 1:18 AM

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

1. Re: Unrelated order by column CASE WHEN upon generated SQL query from Mondrian

shawkins Nov 24, 2014 8:47 AM (in response to arunachal)

Teiid is being strict about what is an allowed ordering. When there is a grouping present we expect the order by to be over select clause. This is an intermediate case where the order by expression is derived from the select clause. That would take an enhancement to generally allow.

Otherwise it would be better to use an inline view, or for this particular sql just to specify the null ordering - order by "Person"."FirstName" ASC NULLS LAST

Can you direct mondrian more as to how it generates a query?
Actions
2. Re: Unrelated order by column CASE WHEN upon generated SQL query from Mondrian

arunachal Nov 25, 2014 1:06 AM (in response to shawkins)

Thanks Hawkins,

I am not sure whether I am giving you the right information. Mondrian uses O-O approach to generate the MDX Query(OLAP4j). It reads through it's schema file and adds dimension and cubes from the schema file. This problem occurs when I am trying to add the dimensions or cubes. For example at org.olap4j.query.QueryDimension.include(QueryDimension.java:112).
Actions
3. Re: Unrelated order by column CASE WHEN upon generated SQL query from Mondrian

shawkins Nov 25, 2014 10:59 AM (in response to arunachal)

What dialect are you using with Mondrian? It looks like it will default to the null ordering with a case expression, but also supports the ansi syntax - JdbcDialectImpl.generateOrderByNullsAnsi. So this could be resolved with possibly a custom dialect for Mondrian or an enhancement to broaden allowed order by expressions in non-simple queries.
Actions
4. Re: Unrelated order by column CASE WHEN upon generated SQL query from Mondrian

arunachal Nov 27, 2014 12:09 AM (in response to shawkins)

This makes sense. I will come back to you after a try.
Actions
5. Re: Unrelated order by column CASE WHEN upon generated SQL query from Mondrian

arunachala.shetty Dec 6, 2014 6:09 AM (in response to arunachal)

It is working I modified JdbcDialectImpl of mondrian.olap.MondrianProperties package.
Here is what I did:
In mondrian.spi.impl.JdbcDialectImpl I returned generateOrderByNullsAnsi inside generateOrderByNulls method. COmmeted out other logic of generateOrderByNulls.

Thanks Hawkins...
Actions
6. Re: Unrelated order by column CASE WHEN upon generated SQL query from Mondrian

arunachal Dec 6, 2014 6:17 AM (in response to arunachal)

It is working I modified JdbcDialectImpl of mondrian.olap.MondrianProperties package.
Here is what I did:
In mondrian.spi.impl.JdbcDialectImpl I returned generateOrderByNullsAnsi inside generateOrderByNulls method. COmmeted out other logic of generateOrderByNulls.

Thanks Hawkins...
Actions
7. Re: Unrelated order by column CASE WHEN upon generated SQL query from Mondrian

shawkins Dec 15, 2014 3:06 PM (in response to arunachal)

Glad to hear. I also logged an issue to capture this: [TEIID-3253] Expand allowed order by expressions - JBoss Issue Tracker
Actions

Go to original post