Aggregate max failed for Date

[yeahyangliu]

I am using pivot4j to create a measure, in order to find the max date for a time dimension and location dimension, it works on Saiku, but it throw an exception for me: 'Java.lang.ClassCastException: java.sql.Date cannot be cast to java.lang.Number'. I have try max date in mysql, it works fine, could you tell me why it doesn't works for pivot4j?

[mysticfall]

Sorry for the late response. Could you copy & paste the relevant stacktrace please? And if the problem also occurs with the bundled FoodMart database, it'd also help if you can give me instructions on how to reproduce it.

Thanks!

[yeahyangliu]

Here is the sql the pivo4j generated: [select dim_location.province_id as c0, max(samples.effectiveSampleDate) as m0 from dim_location as dim_location, samples as samples where samples.facility_id = dim_location.id group by dim_location.province_id]

Here is the error: 'Unhandled exception has occurred, Please see the log for more details : org.olap4j.OlapException: not a number'

[mysticfall]

Thanks for the information. But I need the stacktrace, the lines following the error message. If you don't see any other lines than the one you quoted, than probably they might have been logged elsewhere, like catalina.log or pentaho.log file under logs directory, if you are running it on Tomcat.

Please check the log file again and see if you can find the whole stacktrace when it happens.

[yeahyangliu]

Caused by: java.lang.ClassCastException: java.sql.Date cannot be cast to java.lang.Number
    at mondrian.olap.fun.FunUtil.max(FunUtil.java:1330)
    at mondrian.rolap.RolapAggregator$4.aggregate(RolapAggregator.java:160)
    at mondrian.olap.fun.AggregateFunDef$AggregateCalc.aggregate(AggregateFunDef.java:148)
    at mondrian.rolap.RolapResult$1.evaluate(RolapResult.java:345)
    at mondrian.olap.ExpCacheDescriptor.evaluate(ExpCacheDescriptor.java:100)
    at mondrian.rolap.RolapEvaluator.getCachedResult(RolapEvaluator.java:1073)
    at mondrian.calc.impl.CacheCalc.evaluate(CacheCalc.java:31)
    at mondrian.rolap.RolapEvaluator.evaluateCurrent(RolapEvaluator.java:806)
    at mondrian.rolap.RolapResult.executeStripe(RolapResult.java:1150)
    at mondrian.rolap.RolapResult.executeStripe(RolapResult.java:1300)
    at mondrian.rolap.RolapResult.executeStripe(RolapResult.java:1300)
    at mondrian.rolap.RolapResult.executeBody(RolapResult.java:1013)
    at mondrian.rolap.RolapResult.<init>(RolapResult.java:504)
    at mondrian.rolap.RolapConnection.executeInternal(RolapConnection.java:672)

[mysticfall]

Ok, it seems that I have at least some clue as to what might have went wrong. I probably need to wait until later this week to properly investigate this problem due to my real work, but I think we have somewhere to start now.

By the way, do you see any other stack trace other than that? Usually there are other parts where some packages of Pivot4J are referred as well. If you find some, it'd be even more helpful to track down the problem if you could share that as well.

Thanks for your patience!

P.S.: Relevant MDX statement (aside from the generated SQL output) would be helpful if it's available too.

[yeahyangliu]

I think this issue is easy to reproduce, create a date type column and using a max aggregator, then it will pop up this error.

[mysticfall]

Ok, I'll try it as soon as I'll be available to start working on this issue.

[yeahyangliu]

Thank you very much!

[mysticfall]

I finally had some free time to investigate into this issue, and it turns out it's not Pivot4J's fault, but a known limitation of Mondrian:

• http://jira.pentaho.com/browse/MONDRIAN-696

As there's nothing can be done from Pivot4J's side, I'll close this issue now.

Thanks for the patience.