Pentaho Analysis - Mondrian
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-626

DATE type Levels can cause errors with certain JDBC drivers (e.g. Oracle 5/6).

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Severe Severe
    • Resolution: Fixed
    • Affects Version/s: None
    • Component/s: None
    • Labels:
      None
    • Customer Case:
    • QA Validation Status:
      Validated by QA

      Description

      The 1.4 Oracle drivers treat DATE fields as a java.sql.Date (makes sense). WIth the 5 and 6 versions they have started treating both date and timestamp as java.sql.Timestamp. This can cause an error with Mondrian if any levels are defined as having type='Date', since the JdbcDialect will assume that date literals match the yyyy-mm-dd format and will throw an exception if the string includes the time (e.g. '2001-01-19 00:00:00.0').

      I'm not sure if any other jdbc drivers besides Oracle have a similar quirk about treating Date and Timestamp both as java.sql.Timestamp.

      One possible solution would be to update the dialect to drop the time information if the datatype is DATE. Another option would be to strip the timestamp when the member is loaded. The second option has the advantage of dropping the unwanted time information from the member name.
      1. pentaho.log
        4.76 MB
        Suhas Gururaja

        Activity

        Hide
        Julian Hyde added a comment -
        The Oracle DATE datatype is misnamed; it predates the ANSI standard DATE, TIME and TIMESTAMP types, and maps more closely to ANSI's TIMESTAMP. It contains hours, minutes, and seconds. Conventionally people who just want to store date values truncate to midnight. (Which works as long as everyone agrees which timezone midnight occurs in!)

        So, it makes sense for Oracle's JDBC driver to return Timestamp values. We just need to deal with them. If we can deal with them sensibly, we should be able to handle schemas where people have chosen to store date values in a timestamp field.

        Does Oracle's driver allow you to call ResultSet.getDate on a column whose type is ANSI TIMESTAMP (i.e. Oracle DATE)? If so, that would be the simplest solution. If the column is defined in Mondrian as a date, that would tell Mondrian to treat the value as a ANSI DATE field and use the corresponding JDBC method to get it.

        At the root of this problem is the fact that we have never adequately specified or tested datetime behavior. The FoodMart schema doesn't even contain a date field. It would be useful to add a table to the FoodMart schema with say 100 rows and a column of each possible data type (including decimals, date, time, timestamp, blob and clob). Then we could start writing tests.
        Show
        Julian Hyde added a comment - The Oracle DATE datatype is misnamed; it predates the ANSI standard DATE, TIME and TIMESTAMP types, and maps more closely to ANSI's TIMESTAMP. It contains hours, minutes, and seconds. Conventionally people who just want to store date values truncate to midnight. (Which works as long as everyone agrees which timezone midnight occurs in!) So, it makes sense for Oracle's JDBC driver to return Timestamp values. We just need to deal with them. If we can deal with them sensibly, we should be able to handle schemas where people have chosen to store date values in a timestamp field. Does Oracle's driver allow you to call ResultSet.getDate on a column whose type is ANSI TIMESTAMP (i.e. Oracle DATE)? If so, that would be the simplest solution. If the column is defined in Mondrian as a date, that would tell Mondrian to treat the value as a ANSI DATE field and use the corresponding JDBC method to get it. At the root of this problem is the fact that we have never adequately specified or tested datetime behavior. The FoodMart schema doesn't even contain a date field. It would be useful to add a table to the FoodMart schema with say 100 rows and a column of each possible data type (including decimals, date, time, timestamp, blob and clob). Then we could start writing tests.
        Hide
        Julian Hyde added a comment -
        > Jon Rand writes:
        >
        > Oracle has provided a connection property that will cause getObject() to return a java.sql.Date object for Date columns. The property name is "oracle.jdbc.mapDateToTimestamp". Setting the property to "false" restores the behavior that Mondrian expects.
        Show
        Julian Hyde added a comment - > Jon Rand writes: > > Oracle has provided a connection property that will cause getObject() to return a java.sql.Date object for Date columns. The property name is "oracle.jdbc.mapDateToTimestamp". Setting the property to "false" restores the behavior that Mondrian expects.
        Hide
        Gretchen Moran added a comment -
        Some details on how to set the property in order to use the work around Julian mentions:

        Add a new Oracle user such as “OLAP”. Configure Mondrian to use this datasource. The JNDI definition should expose the property as follows:

        <local-tx-datasource>
           <jndi-name>OLAP</jndi-name>
           <connection-url>jdbc:oracle:thin:@XXXXXXXXXXXXXXXXXXX</connection-url>
           <driver-class>oracle.jdbc.OracleDriver</driver-class>
           <min-pool-size>5</min-pool-size>
           <max-pool-size>40</max-pool-size>
          <connection-property name=”oracle.jdbc.mapDateToTimestamp”>false</connection-property>
        </local-tx-datasource>

        Show
        Gretchen Moran added a comment - Some details on how to set the property in order to use the work around Julian mentions: Add a new Oracle user such as “OLAP”. Configure Mondrian to use this datasource. The JNDI definition should expose the property as follows: <local-tx-datasource>    <jndi-name>OLAP</jndi-name>    <connection-url>jdbc:oracle:thin:@XXXXXXXXXXXXXXXXXXX</connection-url>    <driver-class>oracle.jdbc.OracleDriver</driver-class>    <min-pool-size>5</min-pool-size>    <max-pool-size>40</max-pool-size>   <connection-property name=”oracle.jdbc.mapDateToTimestamp”>false</connection-property> </local-tx-datasource>
        Hide
        Luc Boudreau added a comment -
        Change 14576 by lucboudreau@luc-mondrian-3.2 on 2011/08/30 07:53:52

               MONDRIAN: [MONDRIAN-626] Fixes exceptions with parameters od timestamp and date type. Those types were doccumented in the mondrian.xml schema but were not implemented. I've also added a test on the SteelWheels schema based on a timestamp column to make sure it keeps working. Also adds an override in OracleDialect to compensate for some of Oracle's drivers which return DATE objects as TIMESTAMPS.

        Affected files ...

        ... //open/mondrian/src/main/mondrian/olap/Property.java#38 edit
        ... //open/mondrian/src/main/mondrian/olap/fun/PropertiesFunDef.java#23 edit
        ... //open/mondrian/src/main/mondrian/rolap/RolapLevel.java#82 edit
        ... //open/mondrian/src/main/mondrian/spi/impl/OracleDialect.java#13 edit
        ... //open/mondrian/testsrc/main/mondrian/test/SteelWheelsSchemaTest.java#6 edit
        Show
        Luc Boudreau added a comment - Change 14576 by lucboudreau@luc-mondrian-3.2 on 2011/08/30 07:53:52        MONDRIAN: [ MONDRIAN-626 ] Fixes exceptions with parameters od timestamp and date type. Those types were doccumented in the mondrian.xml schema but were not implemented. I've also added a test on the SteelWheels schema based on a timestamp column to make sure it keeps working. Also adds an override in OracleDialect to compensate for some of Oracle's drivers which return DATE objects as TIMESTAMPS. Affected files ... ... //open/mondrian/src/main/mondrian/olap/Property.java#38 edit ... //open/mondrian/src/main/mondrian/olap/fun/PropertiesFunDef.java#23 edit ... //open/mondrian/src/main/mondrian/rolap/RolapLevel.java#82 edit ... //open/mondrian/src/main/mondrian/spi/impl/OracleDialect.java#13 edit ... //open/mondrian/testsrc/main/mondrian/test/SteelWheelsSchemaTest.java#6 edit
        Hide
        Suhas Gururaja added a comment -
        Tested the issue on the CI build dated Oct 6, 2011 12:24:05 AM bi-server-ee-MON33(#50)

        Tried to validate this issue but still am finding some errors. Am i missing some thing during validations. I have used Oracle6.jar and working on foodmart schema.

        Am getting this error "java.lang.ClassCastException: oracle.sql.TIMESTAMP cannot be cast to java.lang.Comparable". Please find the attached log.
        Golda can you please help me on this.
        Show
        Suhas Gururaja added a comment - Tested the issue on the CI build dated Oct 6, 2011 12:24:05 AM bi-server-ee-MON33(#50) Tried to validate this issue but still am finding some errors. Am i missing some thing during validations. I have used Oracle6.jar and working on foodmart schema. Am getting this error "java.lang.ClassCastException: oracle.sql.TIMESTAMP cannot be cast to java.lang.Comparable". Please find the attached log. Golda can you please help me on this.
        Hide
        Curtis Boyden added a comment -
        The test is running and passing on CI
        Show
        Curtis Boyden added a comment - The test is running and passing on CI

          People

          • Assignee:
            Golda David
            Reporter:
            mkambol
          • Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: