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

Incorrect data column is used in the WHERE clause of the SQL when using Oracle DB

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 3.2.2 GA (3.8.0 GA Suite Release), 3.3.0 GA (4.1.0 GA Suite Release)
    • Component/s: SQL Dialects
    • Labels:
      None
    • Environment:
      Oracel 11g
    • Customer Case:
    • QA Validation Status:
      Validated by QA
    • SpiraTest:

      Description

      The problem behavior is that the user does not get any data when drill down to the lowest level of the cube.
      The cause of the problem is that incorrect date fields are used in the WHERE clause of the resultant SQL.
      The field defined for the 'nameColumn', instead of that defined for 'column', of the date dimension for the level is used in the SQL.
      However, the values used in the comparison is from the field defined in the 'column' field of the level.
      Please see the readme.txt file for a full description of the problem.

        Issue Links

          Activity

          Hide
          Man Shing Yau added a comment -
          Tested BI release 3.8 and 4.1 with same result
          Show
          Man Shing Yau added a comment - Tested BI release 3.8 and 4.1 with same result
          Hide
          Luc Boudreau added a comment -
          Man, I was able to work around the problem by adding a "missing column in the date table. The month level didn't have a MONTH_ID column. Adding it to the DB and the Mondrian schema solves the problem. I'm not exactly sure why Mondrian confuses the columns (probably because it uses the same column for both the key and the name) but the customer can work around the issue by simply adding a MONTH_ID column. I'll attach sample SQLs and a schema to this case.
          Show
          Luc Boudreau added a comment - Man, I was able to work around the problem by adding a "missing column in the date table. The month level didn't have a MONTH_ID column. Adding it to the DB and the Mondrian schema solves the problem. I'm not exactly sure why Mondrian confuses the columns (probably because it uses the same column for both the key and the name) but the customer can work around the issue by simply adding a MONTH_ID column. I'll attach sample SQLs and a schema to this case.
          Hide
          Luc Boudreau added a comment -
          Man. Nevermind what I said earlier. It was a bug. I've found it, fixed it, and am working on reproducing it on FoodMart.
          Show
          Luc Boudreau added a comment - Man. Nevermind what I said earlier. It was a bug. I've found it, fixed it, and am working on reproducing it on FoodMart.
          Hide
          Luc Boudreau added a comment -
          Fixed in revision 14926 in //open/mondrian. I've added a unit test to reproduce the issue. This bug can also be reproduced/verified with the repro steps above.
          Show
          Luc Boudreau added a comment - Fixed in revision 14926 in //open/mondrian. I've added a unit test to reproduce the issue. This bug can also be reproduced/verified with the repro steps above.
          Hide
          Luc Boudreau added a comment - - edited
          Repro steps:

           - Take the FoodMart schema and add the following dimension to the Sales cube:

          <Dimension name="PandaSteak" foreignKey="promotion_id">
          <Hierarchy hasAll="false" primaryKey="lvl_3_id">
          <InlineTable alias="meatShack">
          <ColumnDefs>
          <ColumnDef name="lvl_1_id" type="Integer"/>
          <ColumnDef name="lvl_1_name" type="String"/>
          <ColumnDef name="lvl_2_id" type="Integer"/>
          <ColumnDef name="lvl_2_name" type="String"/>
          <ColumnDef name="lvl_3_id" type="Integer"/>
          <ColumnDef name="lvl_3_name" type="String"/>
          </ColumnDefs>
          <Rows>
          <Row>
          <Value column="lvl_1_id">1</Value>
          <Value column="lvl_1_name">level 1</Value>
          <Value column="lvl_2_id">1</Value>
          <Value column="lvl_2_name">level 2 - 1</Value>
          <Value column="lvl_3_id">112</Value>
          <Value column="lvl_3_name">level 3 - 1</Value>
          </Row>
          <Row>
          <Value column="lvl_1_id">1</Value>
          <Value column="lvl_1_name">level 1</Value>
          <Value column="lvl_2_id">1</Value>
          <Value column="lvl_2_name">level 2 - 1</Value>
          <Value column="lvl_3_id">114</Value>
          <Value column="lvl_3_name">level 3 - 2</Value>
          </Row>
          </Rows>
          </InlineTable>
          <Level name="Level1" column="lvl_1_id" nameColumn="lvl_1_name" />
          <Level name="Level2" column="lvl_2_id" nameColumn="lvl_2_name" />
          <Level name="Level3" column="lvl_3_id" nameColumn="lvl_3_name" />
          </Hierarchy>
          </Dimension>

           - Issue the following query :

          select non empty crossjoin({[PandaSteak].[Level3].[level 3 - 1], [PandaSteak].[Level3].[level 3 - 2]}, {[Measures].[Unit Sales], [Measures].[Store Cost]}) on columns, {[Product].[Product Family].Members} on rows from [Sales]

           - If the bug is fixed, you will get results back and no errors.
          Show
          Luc Boudreau added a comment - - edited Repro steps:  - Take the FoodMart schema and add the following dimension to the Sales cube: <Dimension name="PandaSteak" foreignKey="promotion_id"> <Hierarchy hasAll="false" primaryKey="lvl_3_id"> <InlineTable alias="meatShack"> <ColumnDefs> <ColumnDef name="lvl_1_id" type="Integer"/> <ColumnDef name="lvl_1_name" type="String"/> <ColumnDef name="lvl_2_id" type="Integer"/> <ColumnDef name="lvl_2_name" type="String"/> <ColumnDef name="lvl_3_id" type="Integer"/> <ColumnDef name="lvl_3_name" type="String"/> </ColumnDefs> <Rows> <Row> <Value column="lvl_1_id">1</Value> <Value column="lvl_1_name">level 1</Value> <Value column="lvl_2_id">1</Value> <Value column="lvl_2_name">level 2 - 1</Value> <Value column="lvl_3_id">112</Value> <Value column="lvl_3_name">level 3 - 1</Value> </Row> <Row> <Value column="lvl_1_id">1</Value> <Value column="lvl_1_name">level 1</Value> <Value column="lvl_2_id">1</Value> <Value column="lvl_2_name">level 2 - 1</Value> <Value column="lvl_3_id">114</Value> <Value column="lvl_3_name">level 3 - 2</Value> </Row> </Rows> </InlineTable> <Level name="Level1" column="lvl_1_id" nameColumn="lvl_1_name" /> <Level name="Level2" column="lvl_2_id" nameColumn="lvl_2_name" /> <Level name="Level3" column="lvl_3_id" nameColumn="lvl_3_name" /> </Hierarchy> </Dimension>  - Issue the following query : select non empty crossjoin({[PandaSteak].[Level3].[level 3 - 1], [PandaSteak].[Level3].[level 3 - 2]}, {[Measures].[Unit Sales], [Measures].[Store Cost]}) on columns, {[Product].[Product Family].Members} on rows from [Sales]  - If the bug is fixed, you will get results back and no errors.
          Hide
          Brandon Bruce added a comment -
          Please test and create a Spiratest for this case. Leave the Spiratest number in the Spiratest field of this JIRA issue.
          Show
          Brandon Bruce added a comment - Please test and create a Spiratest for this case. Leave the Spiratest number in the Spiratest field of this JIRA issue.
          Hide
          Suhas Gururaja added a comment -
          Tested using the 22 Feb build.

          Used the repro path provided by Luc. The query runs successfully.
          This issue is fixed. Please find the attached screen shot.
          Show
          Suhas Gururaja added a comment - Tested using the 22 Feb build. Used the repro path provided by Luc. The query runs successfully. This issue is fixed. Please find the attached screen shot.

            People

            • Assignee:
              Suhas Gururaja
              Reporter:
              Man Shing Yau
            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: