QueryResolverException upon generated SQL query from Mondrian

[chesse]

Hello,

I've setup a "Star-schema" vdb to connect from an Mondrian client. Upon executing a MDX statement I get the following exception:

Mondrian Error:Internal error: while building member cache;  sql=[select "Batch"."ident" as "c0" from "staR"."Batch" as "Batch" group  by "Batch"."ident" order by "Batch"."ident" ASC]

Remote org.teiid.api.exception.query.QueryResolverException:  Unrelated order by column Batch.ident cannot be used in a SET query,  with SELECT DISTINCT, or GROUP BY

It seems that the query engine can't execute "order by table.column".

If the statement is changed from:

select "Batch"."ident" as "c0" from "staR"."Batch" as "Batch" group  by "Batch"."ident" order by "Batch"."ident" ASC

to

select "Batch"."ident" as "c0" from "staR"."Batch" as "Batch" group  by "Batch"."ident" order by "c0" ASC

it works.

Using the alias "c0" in the "group by" doesn't work either. I've tested this with the DTP SQL Scrapbook connected to the vdb.

Thanks,
Carmon

[shawkins]

Hi Carmon,

We are being strict about the notion of an unrelated order by key.  If the group by were not there, then the query would resolve just fine.  It wouldn't be a problem though to loosen that restriction for cases like this where the column reference also being projected.  I can log a JIRA.

There is no expectation though for the alias to be resolvable in the group by clause.  Is Mondrian also generating queries of that form?

Steve

[chesse]

Hello Steve,

thats good to know...  the alias in the order by isn't important. I just wondered why it worked with the alias but not with the table.column.

Unfortunately Mondrian only generates queries with group by "table"."column" order by "table"."column" so right now I can't use Mondrian for OLAP with the vdb.

I would be thankful if you can log a JIRA.

Thanks,

Carmon

[shawkins]

Hi Carmon,

This was worked under https://jira.jboss.org/browse/TEIID-1250 and will be part of 7.2 alpha2

Steve