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

When level contains nulls, the values are not sorted correctly on the chart

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Severe Severe
    • Resolution: Fixed
    • Affects Version/s: 3.4.1 GA (4.5.0 GA Suite Release)
    • Component/s: None
    • Labels:
      None
    • 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:
      Validated by QA

      Description

      See screenshot. If you sort A->Z or Z->A on State, the behavior is unexpected. You can reproduce on steel-wheels with this report XML:

      <report xmlns="http://www.pentaho.com" freezeRows="true" freezeColumns="true" autoRefresh="true" version="7" showDrillLinks="false" emptyCellDisplay="-" showEmptyCells="false" useNonVisualTotals="false" showColumnGrandTotal="false" showRowGrandTotal="false" reportTypeEnum="JSON" cube="SteelWheelsSales" catalog="SteelWheels">
      <measures><measure showAggregate="true" showSum="false" showAverage="false" showCount="false" showMin="false" showMax="false" measureTypeEnum="VALUE" sortOrderEnum="NONE" formula="[Measures].[Sales]" id="[MEASURE:0]" gembarOrdinal="0" gembarId="measures"/></measures>
      <columnAttributes/>
      <rowAttributes><attribute showSubtotal="false" sortOrderEnum="DESC" formula="[Markets].[State Province]" gembarId="rows" gembarOrdinal="0"/></rowAttributes>
      <filters><filter viewFilterEnum="MULTIPLE" formula="[Time].[Quarters]"><predicates><predicate ordinal="1" operatorEnum="AFTER"><member pos="0" formula="[Time].[2003].[QTR1]" caption="QTR1"/></predicate></predicates></filter><filter viewFilterEnum="MULTIPLE" formula="[Markets].[State Province]"><predicates><predicate ordinal="1" operatorEnum="EQUAL"><member pos="0" formula="[Markets].[NA].[USA].[CA]" caption="CA"/><member pos="0" formula="[Markets].null.[South Africa].null" caption="Not Available"/><member pos="0" formula="[Markets].[APAC].[Australia].[Queensland]" caption="Queensland"/></predicate></predicates></filter></filters>
      <chartOptions legendFontFamily="Default" labelFontFamily="Default" legendStyle="PLAIN" legendColor="#000000" legendSize="12" legendBackgroundColor="#ffffff" labelStyle="PLAIN" backgroundColorEnd="#ffffff" maxChartsPerRow="3" backgroundFill="NONE" labelSize="12" labelColor="#000000" backgroundColor="#ffffff" maxValues="100" lineShape="CIRCLE" lineWidth="2" displayUnitsSecondary="UNITS_0" autoRangeSecondary="true" displayUnits="UNITS_0" autoRange="true" showLegend="true" legendPosition="RIGHT" customChartType="ccc_barstacked" chartType="CUSTOM"/>
      <selectionFilters/>

      <pageSetup excelScalingPageTall="1" excelScalingPageWide="1" excelScalingPercent="100" excelScalingType="PERCENT" pdfOrientation="LANDSCAPE" excelOrientation="LANDSCAPE" pdfPageSize="LETTER" excelPageSize="LETTER"/>
      </report>

      1. mondrian1197.xanalyzer
        2 kB
        Luc Boudreau
      1. mondrian1197.PNG
        243 kB
      2. screenshot-1.jpg
        210 kB
      3. screenshot-2.jpg
        122 kB

        Activity

        Hide
        Benny Chow added a comment - - edited

        I dug into this some more and I think this is a Mondrian issue. This simple MDX on steelwheels will reproduce it. There isn't a repo on foodmart because foodmart doesn't have too many nulls for level names.

        With
        Set [*NATIVE_CJ_SET] as '[*BASE_MEMBERS_Markets]'
        Set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS],[Markets].CurrentMember.OrderKey,BASC,Ancestor([Markets].CurrentMember,[Markets].[Country]).OrderKey,BASC)'
        Set [*BASE_MEMBERS_Markets] as '[Markets].[State Province].Members'
        Set [*BASE_MEMBERS_Measures] as '

        {[Measures].[*ZERO]}

        '
        Set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET],

        {([Markets].currentMember)}

        )'
        Set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
        Member [Measures].[*ZERO] as '0', SOLVE_ORDER=0
        Select
        [*BASE_MEMBERS_Measures] on columns,
        [*SORTED_ROW_AXIS] on rows
        From [SteelWheelsSales]

        Look at the resulting sort in the screenshot. It doesn't make sense. When I looked into the code, I think the issue is in RolapMemberBase.compareTo. Code snippet:

        // compare by unique name, if one ore both members are null
        if (this.key == RolapUtil.sqlNullValue

        other.key == RolapUtil.sqlNullValue) { return this.getUniqueName().compareTo(other.getUniqueName()); }

        I don't think comparing by uniqueName is correct here. Instead, I would suggest:

        sqlNullValue compared to sqlNullValue is 0
        sqlNullValue compared to non-sqlNullValue should be -1
        non-sqlNullValue compared to sqlNullValue should be 1

        Show
        Benny Chow added a comment - - edited I dug into this some more and I think this is a Mondrian issue. This simple MDX on steelwheels will reproduce it. There isn't a repo on foodmart because foodmart doesn't have too many nulls for level names. With Set [*NATIVE_CJ_SET] as ' [*BASE_MEMBERS_Markets] ' Set [*SORTED_ROW_AXIS] as 'Order( [*CJ_ROW_AXIS] , [Markets] .CurrentMember.OrderKey,BASC,Ancestor( [Markets] .CurrentMember, [Markets] . [Country] ).OrderKey,BASC)' Set [*BASE_MEMBERS_Markets] as ' [Markets] . [State Province] .Members' Set [*BASE_MEMBERS_Measures] as ' {[Measures].[*ZERO]} ' Set [*CJ_ROW_AXIS] as 'Generate( [*NATIVE_CJ_SET] , {([Markets].currentMember)} )' Set [*CJ_COL_AXIS] as ' [*NATIVE_CJ_SET] ' Member [Measures] . [*ZERO] as '0', SOLVE_ORDER=0 Select [*BASE_MEMBERS_Measures] on columns, [*SORTED_ROW_AXIS] on rows From [SteelWheelsSales] Look at the resulting sort in the screenshot. It doesn't make sense. When I looked into the code, I think the issue is in RolapMemberBase.compareTo. Code snippet: // compare by unique name, if one ore both members are null if (this.key == RolapUtil.sqlNullValue other.key == RolapUtil.sqlNullValue) { return this.getUniqueName().compareTo(other.getUniqueName()); } I don't think comparing by uniqueName is correct here. Instead, I would suggest: sqlNullValue compared to sqlNullValue is 0 sqlNullValue compared to non-sqlNullValue should be -1 non-sqlNullValue compared to sqlNullValue should be 1
        Hide
        Luc Boudreau added a comment -

        Fixed in https://github.com/pentaho/mondrian/commit/2eb75a2dc2227784fe62b87fde7bf8056b29c388

        RolapMemberBase was comparing members with null keys by unique name, which only made sense for VisualTotalsMember but would create problems with other normal members. This fix makes it so that null keys are always equal, but VisualTotalsMember overrides the compareTo(Object) method so the delegate member is compared. This is in line with other parts of the code, like RolapCubeMember.

        Show
        Luc Boudreau added a comment - Fixed in https://github.com/pentaho/mondrian/commit/2eb75a2dc2227784fe62b87fde7bf8056b29c388 RolapMemberBase was comparing members with null keys by unique name, which only made sense for VisualTotalsMember but would create problems with other normal members. This fix makes it so that null keys are always equal, but VisualTotalsMember overrides the compareTo(Object) method so the delegate member is compared. This is in line with other parts of the code, like RolapCubeMember.
        Hide
        Luc Boudreau added a comment -

        Attached to this case is a report to reproduce the issue. (See screenshot mondrian1197.png) I've also created a test in mondrian.test.SteelWheelsSchemaTest.testMondrian1197()

        Show
        Luc Boudreau added a comment - Attached to this case is a report to reproduce the issue. (See screenshot mondrian1197.png) I've also created a test in mondrian.test.SteelWheelsSchemaTest.testMondrian1197()
        Hide
        Brandon Bruce added a comment -

        Still not sorting correctly. I used the MDX provided by the previous comment

        Show
        Brandon Bruce added a comment - Still not sorting correctly. I used the MDX provided by the previous comment
        Hide
        Luc Boudreau added a comment -

        A proper fix was checked into
        https://github.com/pentaho/mondrian/commit/432806bf4036b2cd2ead18c5158519f8494584ea

        Same repro steps as above.

        Show
        Luc Boudreau added a comment - A proper fix was checked into https://github.com/pentaho/mondrian/commit/432806bf4036b2cd2ead18c5158519f8494584ea Same repro steps as above.
        Hide
        Ezequiel Cuellar added a comment -

        Validating

        Show
        Ezequiel Cuellar added a comment - Validating
        Hide
        Ezequiel Cuellar added a comment -

        Validated by QA

        Show
        Ezequiel Cuellar added a comment - Validated by QA

          People

          • Assignee:
            Unassigned User
            Reporter:
            Benny Chow
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: