Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Severe Severe
    • Resolution: Fixed
    • Affects Version/s: 3.2.1 GA (3.7.0 GA Suite Release)
    • Component/s: None
    • Labels:
      None
    • Customer Case:
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.
    • QA Validation Status:
      Unvalidatable

      Description

      Oracle integer columns overflow if the value is greater than 2^31.

      This is because Mondrian assumes that a NUMBER(38, 0) column in Oracle is intended to hold an integer. 99% of the time, this is valid; and users would be glad that Mondrian is using a Java int value rather than a BigDecimal, which is what comes back from Oracle's JDBC driver. 1% of the time, the number really is larger than an int can hold.

      The solution is to allow the schema designer to specify the type that Mondrian is to use internally. Add an attribute, 'internalType', of element type Level. Valid values int, long, BigDecimal, double, String, Object. If specified, Mondrian would not try to guess the type [see the method mondrian.rolap.SqlStatement.guessType].

      They also imply that Mondrian would call java.sql.ResultSet.getXxx to get the column value. (Some drivers return different values for getString versus getObject; that's why it's useful to have separate String and Object type.)

      The Level@internalType attribute would be immediately deprecated. We don't guarantee that it will be available in mondrian-4. But we will replace it with equivalent functionality.

        Issue Links

          Activity

          Hide
          Julian Hyde added a comment -

          Note that this is related to MONDRIAN-825, but is a better solution.

          Show
          Julian Hyde added a comment - Note that this is related to MONDRIAN-825 , but is a better solution.
          Hide
          Jared Cornelius added a comment -

          Fixed in checkin 14213.

          Show
          Jared Cornelius added a comment - Fixed in checkin 14213.
          Hide
          Bryan Senseman added a comment -

          This also happens in PostgreSQL and Netezza (and I assume any database "derived" from Postgre). This is the work around we found to work consistently. Create a base measure that divides by 1000, then create a calculated measure that re-multiplies by 1000.

          <Measure name="Total Sends in Thousands" datatype="Integer" formatString="#,##0" aggregator="sum" visible="false">
          <MeasureExpression>
          <SQL dialect="generic">
          <![CDATA[TOT_SENDS / 1000.0]]>
          </SQL>
          </MeasureExpression>
          </Measure>
          <CalculatedMember name="Total Sends" formatString="#,##0" formula="cache([Measures].[Total Sends in Thousands] * 1000.0)" dimension="Measures" visible="true">
          </CalculatedMember>

          Notice that the division is by 1000.0, you'll be very surprised by how much integer truncation changes the end result when you are in the billions...

          Show
          Bryan Senseman added a comment - This also happens in PostgreSQL and Netezza (and I assume any database "derived" from Postgre). This is the work around we found to work consistently. Create a base measure that divides by 1000, then create a calculated measure that re-multiplies by 1000. <Measure name="Total Sends in Thousands" datatype="Integer" formatString="#,##0" aggregator="sum" visible="false"> <MeasureExpression> <SQL dialect="generic"> <![CDATA [TOT_SENDS / 1000.0] ]> </SQL> </MeasureExpression> </Measure> <CalculatedMember name="Total Sends" formatString="#,##0" formula="cache( [Measures] . [Total Sends in Thousands] * 1000.0)" dimension="Measures" visible="true"> </CalculatedMember> Notice that the division is by 1000.0, you'll be very surprised by how much integer truncation changes the end result when you are in the billions...
          Hide
          Golda David added a comment -

          Due to the Large volume of data required in a schema this is beyond our scope for testing.

          Show
          Golda David added a comment - Due to the Large volume of data required in a schema this is beyond our scope for testing.
          Hide
          Bryan Senseman added a comment -

          This issue still appears to be unresolved for Netezza & PostgreSQL.
          It should be relatively easy to test unless I'm missing something; insert 2 rows into a fact table that has an integer column, set the values to be 2Billion, then run a query in Mondrian that would some those 2 rows, which will result in 4Billion which will cause the overflow.

          Show
          Bryan Senseman added a comment - This issue still appears to be unresolved for Netezza & PostgreSQL. It should be relatively easy to test unless I'm missing something; insert 2 rows into a fact table that has an integer column, set the values to be 2Billion, then run a query in Mondrian that would some those 2 rows, which will result in 4Billion which will cause the overflow.
          Hide
          Jake Cornelius added a comment -

          This case results in similar issues, but is not specific to Oracle, rather it relates to Netezza

          Show
          Jake Cornelius added a comment - This case results in similar issues, but is not specific to Oracle, rather it relates to Netezza
          Hide
          Jake Cornelius added a comment -

          closing as this problem was resolved specific to Oracle, please see MONDRIAN-1134 for similar issues that are being resolved relative to Netezza and PostgreSQL

          Show
          Jake Cornelius added a comment - closing as this problem was resolved specific to Oracle, please see MONDRIAN-1134 for similar issues that are being resolved relative to Netezza and PostgreSQL

            People

            • Assignee:
              Jake Cornelius
              Reporter:
              Julian Hyde
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: