Mondrian 4: JDBC columns with Types.Real data type return null Schema won't load.

[chubbard]

Using the mysql driver and a table with a column of Mysql Float value type. Mondrian doesn't recognize the data type because the mysql driver is returning a REAL data type when used in a Query. Exception being thrown:

mondrian.rolap.RolapSchema$MondrianSchemaException: Unknown data type FLOAT (7) for column latitude of view; mondrian is probably not familiar with this database's type system (in Query) (at line 18, column 8)
at mondrian.rolap.RolapSchemaLoaderHandlerImpl.warning(RolapSchemaLoaderHandlerImpl.java:72)
at mondrian.rolap.RolapSchemaLoaderHandlerImpl.warning(RolapSchemaLoaderHandlerImpl.java:62)
at mondrian.rolap.RolapSchema$PhysSchema.describe(RolapSchema.java:1033)
at mondrian.rolap.RolapSchema$PhysView.populateColumns(RolapSchema.java:1623)
at mondrian.rolap.RolapSchema$PhysRelationImpl.ensurePopulated(RolapSchema.java:1519)
at mondrian.rolap.RolapSchema$PhysView.ensurePopulated(RolapSchema.java:1561)
at mondrian.rolap.RolapSchemaLoader.registerView(RolapSchemaLoader.java:1015)
at mondrian.rolap.RolapSchemaLoader.validatePhysicalSchema(RolapSchemaLoader.java:740)
at mondrian.rolap.RolapSchemaLoader.loadStage2(RolapSchemaLoader.java:376)
at mondrian.rolap.RolapSchemaLoader.loadStage1(RolapSchemaLoader.java:336)
at mondrian.rolap.RolapSchemaLoader.loadStage0(RolapSchemaLoader.java:272)
at mondrian.rolap.RolapSchemaLoader.createSchema(RolapSchemaLoader.java:4303)
at mondrian.rolap.RolapSchemaPool.get(RolapSchemaPool.java:210)
at mondrian.rolap.RolapSchemaPool.get(RolapSchemaPool.java:62)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:160)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:84)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:112)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:68)
at mondrian.olap4j.MondrianOlap4jConnection.<init>(MondrianOlap4jConnection.java:153)
at mondrian.olap4j.FactoryJdbc4Plus$AbstractConnection.<init>(FactoryJdbc4Plus.java:323)
at mondrian.olap4j.FactoryJdbc41Impl$MondrianOlap4jConnectionJdbc41.<init>(FactoryJdbc41Impl.java:118)
at mondrian.olap4j.FactoryJdbc41Impl.newConnection(FactoryJdbc41Impl.java:32)
at mondrian.olap4j.MondrianOlap4jDriver.connect(MondrianOlap4jDriver.java:139)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:233)
at com.fuseanalytics.archiver.model.AnalyticModel$AnalysisConnection.<init>(AnalyticModel.java:418)
at com.fuseanalytics.archiver.model.AnalyticModel.getConnection(AnalyticModel.java:62)
at com.fuseanalytics.archiver.model.AnalyticModel.getConnection(AnalyticModel.java:57)
at com.fuseanalytics.archiver.model.AnalyticModel.getCubes(AnalyticModel.java:74)
at com.fuseanalytics.archiver.model.AnalyticModel.getReports(AnalyticModel.java:87)
at com.fuseanalytics.archiver.controller.AnalyticsController.reportsSearch(AnalyticsController.java:111)
at com.fuseanalytics.archiver.controller.AnalyticsController$$FastClassBySpringCGLIB$$bdc547b8.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:700)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.security.access.intercept.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:64)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:633)
at com.fuseanalytics.archiver.controller.AnalyticsController$$EnhancerBySpringCGLIB$$a20797ab.reportsSearch(<generated>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:440)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:428)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:933)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:867)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:951)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:842)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:827)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:106)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.tuckey.web.filters.urlrewrite.RuleChain.handleRewrite(RuleChain.java:176)
at org.tuckey.web.filters.urlrewrite.RuleChain.doRules(RuleChain.java:145)
at org.tuckey.web.filters.urlrewrite.UrlRewriter.processRequest(UrlRewriter.java:92)
at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:389)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)

In the schema the following was done:

<PhysicalSchema>
    <Query alias='regions' keyColumn='id'>
        <ExpressionView>
            <SQL dialect='generic'><![CDATA[
select * from Regions where account_id=%account_id%
            ]]></SQL>
        </ExpressionView>
    </Query>
</PhysicalSchema>

On RolapSchema:1030 the following call returns null:

final Dialect.Datatype datatype = dialect.sqlTypeToDatatype(typeName, type);

But start on JdbcDialectImpl:1136 this switch doesn't recognize Types.REAL

    case Types.NUMERIC:
    case Types.DECIMAL:
    case Types.FLOAT:
    case Types.DOUBLE:
        return Datatype.Numeric;

The Types.REAL is missing causing the null to be returned. It should be changed to:

    case Types.NUMERIC:
    case Types.DECIMAL:
    case Types.FLOAT:
    case Types.DOUBLE:
    case Types.REAL:
        return Datatype.Numeric;

Other things I've tried to work around it:

1. Add a mysql dialect query to it. That didn't work.

2. Changing MySQL FLOAT -> MySQL DOUBLE. Nope.

3. Update the Driver. It could be a driver issue because if you use Table instead of Query in the PhysicalSchema it works. But I have to limit things by account_id so I have to have the Query option working. It's easy to push this off on the driver, BUT

   1. Types.REAL is a valid JDBC Type and Mondrian should work with standard data types.
   2. While the Driver MIGHT be wrong adding the Types.REAL makes the code more robust and easier to work with out of the box.

[chubbard]

I've looked into this further, and I don't think it's a driver problem. I've tried looking at the metadata returned from the driver from issuing both ResultSet.getMetaData(), and Connection.getMetaData().getColumns() method. Both agree the JDBC data type of a Float column in Mysql is a JDBC type of REAL. I checked the Mondrian code and it appears that in JdbcSchema it does associate Types.REAL with NUMERIC, but it's missing from JdbcDialectImpl which explains why it works for using the <Table> element, but NOT <Query>.

[julianhyde]

Did you know what in the JDBC spec, FLOAT is a 64 bit floating point (and likewise java.sql.Types.FLOAT)? A lot of people assume that it is a 32 bit floating point. (REAL is 32 bit floating point, and DOUBLE is, like FLOAT, a 64 bit floating point.)

MySQL's FLOAT column is a 32 bit floating point. Let's assume that their driver exposes FLOAT columns as JDBC type REAL, as it should.

In which case, I'm not sure where JDBC FLOAT columns are coming from, but we should treat them exactly the same as we handle JDBC DOUBLE columns.

[chubbard]

In trying to work around this problem with Mondrian I tried changing my columns to Mysql Doubles, but it still reported them as Types.REAL which is strange. I'm assuming FLOAT, DOUBLE, and REAL should all be treated the same way to Mondrian. In JdbcSchema.getDatatype() it does exactly that and that's why I felt the case ladder in JdbcDialectImpl was missing the case statement for REAL.

So under what conditions will Mysql return Types.FLOAT? I don't think it will return FLOAT ever since from the JDBC docs it says "The JDBC type FLOAT is basically equivalent to the JDBC type DOUBLE. We provided both FLOAT and DOUBLE in a possibly misguided attempt at consistency with previous database APIs..." So they basically admit it was bit of screw up on their part to provide both Float and Double.