[Mondrian] Re: Vertica: Errors in sql: Order by ... DESC NULLS LAST

Pedro Alves pmgalves at gmail.com
Fri Sep 11 12:08:38 EDT 2009



http://jira.pentaho.com/browse/MONDRIAN-617


Had to implement a new vertica dialog, but maybe it would make sense for 
this behavior to be handled by a config option. For instance, only psql 
versions >= 8.3 support it.


-pedro


On 09/11/2009 03:08 PM, Pedro Alves wrote:
>
> Using lastest mondrian, I have an error in vertica for a simple query:
>
>
> select NON EMPTY {
> Order(TopCount([Products].[All].Children, 10.0, [Measures].[Total
> Requests]), [Measures].[Total Requests], DESC
> )} ON COLUMNS,
> {[Date].[All]} ON ROWS
> from [BlockList Analysis]
>
>
> I think the problem is the ORDER BY ... DESC NULLS LAST, which is
> invalid in vertica (apparently it doesn't support this sql-2003 syntax).
>
>
> Can I turn off this sql generation option? What can I do to solve this?
>
>
> Thanks
>
> -pedro
>
> Full stack trace:
>
> at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:816)
> at mondrian.olap.Util.newInternal(Util.java:1466)
> at mondrian.olap.Util.newError(Util.java:1482)
> at mondrian.rolap.RolapConnection.execute(RolapConnection.java:592)
> at
> org.pentaho.platform.plugin.services.connections.mondrian.MDXConnection.executeQuery(MDXConnection.j
>
> ava:239)
> (...)
> Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal
> error: Populating member cache with members for [[Products].[Name]];
> sql=[select
> "a_blocklist_version"."products_Name" as "c0"
> from
> "a_blocklist_version" as "a_blocklist_version"
> group by
> "a_blocklist_version"."products_Name"
> order by
>
> sum("a_blocklist_version"."blocklist_requests_by_day_Total_Requests")
> DESC NULLS LAST,
> "a_blocklist_version"."products_Name" ASC
> ]
> at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:816)
> at mondrian.olap.Util.newInternal(Util.java:1466)
> at mondrian.olap.Util.newError(Util.java:1482)
> at mondrian.rolap.SqlStatement.handle(SqlStatement.java:226)
> at mondrian.rolap.SqlStatement.execute(SqlStatement.java:156)
> at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.java:241)
> at mondrian.rolap.SqlTupleReader.prepareTuples(SqlTupleReader.java:382)
> at mondrian.rolap.SqlTupleReader.readMembers(SqlTupleReader.java:489)
> at
> mondrian.rolap.RolapNativeSet$SetEvaluator.executeList(RolapNativeSet.java:213)
>
> at
> mondrian.rolap.RolapNativeSet$SetEvaluator.execute(RolapNativeSet.java:168)
> at
> mondrian.olap.fun.TopBottomCountFunDef$3.evaluateList(TopBottomCountFunDef.java:86)
>
> at
> mondrian.calc.impl.AbstractListCalc.evaluateMemberList(AbstractListCalc.java:84)
>
> at
> mondrian.calc.impl.AbstractExpCompiler$MemberListIterCalc.evaluateMemberIterable(AbstractExpCompiler.java:591)
>
> at
> mondrian.olap.fun.OrderFunDef$MemberCalcImpl.evaluateDual(OrderFunDef.java:184)
>
> at mondrian.olap.fun.OrderFunDef$ContextCalc.evaluate(OrderFunDef.java:452)
> at mondrian.rolap.RolapResult.executeAxis(RolapResult.java:728)
> at mondrian.rolap.RolapResult.evalLoad(RolapResult.java:571)
> at mondrian.rolap.RolapResult.loadMembers(RolapResult.java:537)
> at mondrian.rolap.RolapResult.<init>(RolapResult.java:269)
> at mondrian.rolap.RolapConnection.execute(RolapConnection.java:563)
> ... 75 more
> Caused by: com.vertica.util.PSQLException: ERROR: syntax error at or
> near "NULLS"
> at com.vertica.core.v3.QueryExecutorImpl.receiveErrorResponse(Unknown
> Source)
> at com.vertica.core.v3.QueryExecutorImpl.processResults(Unknown Source)
> at com.vertica.core.v3.QueryExecutorImpl.execute(Unknown Source)
> at com.vertica.jdbc2.AbstractJdbc2Statement.execute(Unknown Source)
> at com.vertica.jdbc2.AbstractJdbc2Statement.executeWithFlags(Unknown
> Source)
> at com.vertica.jdbc2.AbstractJdbc2Statement.executeQuery(Unknown Source)
> at
> org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:205)
>
> at mondrian.rolap.SqlStatement.execute(SqlStatement.java:142)
> ... 90 more
>



More information about the Mondrian mailing list