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

Wrong table alias in SQL generated to populate member cache

    Details

    • 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:
      Not Yet Validated

      Description

      In case of multiple dimension usage of the same shared dimension in a cube,
      only for the second usage,
      if a level in the dimension hierarchy have a property or a ordinal column,
      and the MDX query contain "non empty",
      mondrian creates wrong alias table building member cache.
      This doasn't happen with a filter in the first dimension usage.

      To reproduce with FoodMart:
      1) clone the column store_id in the table "sales_fact_1997"; name it "buyer_id".
      2) in the FoodMart.xml schema add a dimension usage "Buyer" for the shared dimension "Store" with column "buyer_id" AFTER "Store" dimension usage

      <DimensionUsage source="Store" name="Buyer" visible="true" foreignKey="buyer_id" highCardinality="false">
      </DimensionUsage>

      3) query the schema:

      select [Measures].[Unit Sales] on columns,
      non empty [Buyer].[USA].[OR].[Portland].children on rows
      from [Sales]

      Error rise:
      **************************************
      Mondrian Error:java.util.concurrent.ExecutionException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select {[Measures].[Unit Sales]} ON COLUMNS,
        NON EMPTY [Buyer].[USA].[OR].[Portland].Children ON ROWS
      from [Sales]
      ]

      mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select {[Measures].[Unit Sales]} ON COLUMNS,
        NON EMPTY [Buyer].[USA].[OR].[Portland].Children ON ROWS
      from [Sales]
      ]

      Mondrian Error:Internal error: Error while executing query [select {[Measures].[Unit Sales]} ON COLUMNS,
        NON EMPTY [Buyer].[USA].[OR].[Portland].Children ON ROWS
      from [Sales]
      ]

      Mondrian Error:Internal error: while building member cache; sql=[select `store_1`.`store_name` as `c0`, `store`.`store_type` as `c1`, `store`.`store_manager` as `c2`, `store`.`store_sqft` as `c3`, `store`.`grocery_sqft` as `c4`, `store`.`frozen_sqft` as `c5`, `store`.`meat_sqft` as `c6`, `store`.`coffee_bar` as `c7`, `store`.`store_street_address` as `c8` from `store` as `store_1`, `sales_fact_1997` as `sales_fact_1997` where `sales_fact_1997`.`buyer_id` = `store_1`.`store_id` and `store_1`.`store_state` = 'OR' and `store_1`.`store_city` = 'Portland' group by `store_1`.`store_name`, `store`.`store_type`, `store`.`store_manager`, `store`.`store_sqft`, `store`.`grocery_sqft`, `store`.`frozen_sqft`, `store`.`meat_sqft`, `store`.`coffee_bar`, `store`.`store_street_address` order by ISNULL(`store_1`.`store_name`) ASC, `store_1`.`store_name` ASC]

      Unknown column 'store.store_type' in 'field list'
      *********************************
      *Note the alias "from `store` as `store_1`" not used for the field defined in the properties.

      *Note:

      **without "non empty" it runs:

      select [Measures].[Unit Sales] on columns,
      [Buyer].[USA].[OR].[Portland].children on rows
      from [Sales]
      *****************
      Axis #0:
      {}
      Axis #1:
      {[Measures].[Unit Sales]}
      Axis #2:
      {[Buyer].[USA].[OR].[Portland].[Store 11]}
      Row #0: 26.079
      ******************
      **with a filter in the first dimension usage it runs:

      select [Measures].[Unit Sales] on columns,
      non empty [Buyer].[USA].[OR].[Portland].children on rows
      from [Sales]
      where [Store].[USA]

      **querying the first dimension usage runs ([Store] vs. [Buyer]):

      select [Measures].[Unit Sales] on columns,
      non empty [Store].[USA].[OR].[Portland].children on rows
      from [Sales]

      Axis #0:
      {}
      Axis #1:
      {[Measures].[Unit Sales]}
      Axis #2:
      {[Store].[USA].[OR].[Portland].[Store 11]}
      Row #0: 26.079
      1. FoodMartMysql.xml
        46 kB
        Li Deng
      2. workbench.log
        448 kB
        Enrico Maria Carmona
      1. screenshot-1.jpg
        14 kB

        Activity

        Hide
        Enrico Maria Carmona added a comment -
        debug level workbench log
        Show
        Enrico Maria Carmona added a comment - debug level workbench log
        Hide
        Pedro Vale added a comment -
        Issued a pull request with the fix for this issue.
        Assigning to Julian for review.
        Show
        Pedro Vale added a comment - Issued a pull request with the fix for this issue. Assigning to Julian for review.
        Hide
        Julian Hyde added a comment -
        Fixed in https://github.com/pentaho/mondrian/commit/36c1f9b5dbcadf0eee219ac5a25033e31f61d9a5.

        I have also merged into lagunitas branch (mainly the test case; the fixed code is commented out in lagunitas, so I may have to fix the bug again).
        Show
        Julian Hyde added a comment - Fixed in https://github.com/pentaho/mondrian/commit/36c1f9b5dbcadf0eee219ac5a25033e31f61d9a5 . I have also merged into lagunitas branch (mainly the test case; the fixed code is commented out in lagunitas, so I may have to fix the bug again).
        Hide
        Enrico Maria Carmona added a comment -
        Thx, but still get the wrong result: 25 duplicated lines because two alias for 'store' table and only one join

        log:
        2012-10-04 11:08:00,345 DEBUG [mondrian.sql] 25: SqlMemberSource.getMemberChildren: executing sql [select `store_1`.`store_name` as `c0`, `store`.`store_type` as `c1`, `store`.`store_manager` as `c2`, `store`.`store_sqft` as `c3`, `store`.`grocery_sqft` as `c4`, `store`.`frozen_sqft` as `c5`, `store`.`meat_sqft` as `c6`, `store`.`coffee_bar` as `c7`, `store`.`store_street_address` as `c8` from `store` as `store_1`, `sales_fact_1997` as `sales_fact_1997`, `store` as `store` where `sales_fact_1997`.`buyer_id` = `store_1`.`store_id` and `store_1`.`store_state` = 'OR' and `store_1`.`store_city` = 'Portland' group by `store_1`.`store_name`, `store`.`store_type`, `store`.`store_manager`, `store`.`store_sqft`, `store`.`grocery_sqft`, `store`.`frozen_sqft`, `store`.`meat_sqft`, `store`.`coffee_bar`, `store`.`store_street_address` order by ISNULL(`store_1`.`store_name`) ASC, `store_1`.`store_name` ASC]
        2012-10-04 11:08:00,345 DEBUG [mondrian.server.monitor] SqlStatementStartEvent(25)
        2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column store_name is of internal type OBJECT. JDBC type was 12
        2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column store_type is of internal type OBJECT. JDBC type was 12
        2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column store_manager is of internal type OBJECT. JDBC type was 12
        2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column store_sqft is of internal type DOUBLE. JDBC type was 8
        2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column grocery_sqft is of internal type DOUBLE. JDBC type was 8
        2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column frozen_sqft is of internal type DOUBLE. JDBC type was 8
        2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column meat_sqft is of internal type DOUBLE. JDBC type was 8
        2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column coffee_bar is of internal type OBJECT. JDBC type was 1
        2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column store_street_address is of internal type OBJECT. JDBC type was 12
        2012-10-04 11:08:00,345 DEBUG [mondrian.sql] 25: , exec 1 ms
        2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: executing sql [select `store_1`.`store_name` as `c0`, `store`.`store_type` as `c1`, `store`.`store_manager` as `c2`, `store`.`store_sqft` as `c3`, `store`.`grocery_sqft` as `c4`, `store`.`frozen_sqft` as `c5`, `store`.`meat_sqft` as `c6`, `store`.`coffee_bar` as `c7`, `store`.`store_street_address` as `c8` from `store` as `store_1`, `sales_fact_1997` as `sales_fact_1997`, `store` as `store` where `sales_fact_1997`.`buyer_id` = `store_1`.`store_id` and `store_1`.`store_state` = 'OR' and `store_1`.`store_city` = 'Portland' group by `store_1`.`store_name`, `store`.`store_type`, `store`.`store_manager`, `store`.`store_sqft`, `store`.`grocery_sqft`, `store`.`frozen_sqft`, `store`.`meat_sqft`, `store`.`coffee_bar`, `store`.`store_street_address` order by ISNULL(`store_1`.`store_name`) ASC, `store_1`.`store_name` ASC], exec 1 ms
        2012-10-04 11:08:00,345 DEBUG [mondrian.server.monitor] SqlStatementExecuteEvent(25)
        2012-10-04 11:08:00,345 DEBUG [mondrian.sql] 25: , exec+fetch 0 ms, 25 rows
        2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: done executing sql [select `store_1`.`store_name` as `c0`, `store`.`store_type` as `c1`, `store`.`store_manager` as `c2`, `store`.`store_sqft` as `c3`, `store`.`grocery_sqft` as `c4`, `store`.`frozen_sqft` as `c5`, `store`.`meat_sqft` as `c6`, `store`.`coffee_bar` as `c7`, `store`.`store_street_address` as `c8` from `store` as `store_1`, `sales_fact_1997` as `sales_fact_1997`, `store` as `store` where `sales_fact_1997`.`buyer_id` = `store_1`.`store_id` and `store_1`.`store_state` = 'OR' and `store_1`.`store_city` = 'Portland' group by `store_1`.`store_name`, `store`.`store_type`, `store`.`store_manager`, `store`.`store_sqft`, `store`.`grocery_sqft`, `store`.`frozen_sqft`, `store`.`meat_sqft`, `store`.`coffee_bar`, `store`.`store_street_address` order by ISNULL(`store_1`.`store_name`) ASC, `store_1`.`store_name` ASC], exec+fetch 0 ms, 25 rows, ex=26, close=26, open=[]
        2012-10-04 11:08:00,360 DEBUG [mondrian.server.monitor] SqlStatementInfo{sqlStatementId=25}
        2012-10-04 11:08:00,360 DEBUG [mondrian.server.monitor] SqlStatementEndEvent(25)
        Show
        Enrico Maria Carmona added a comment - Thx, but still get the wrong result: 25 duplicated lines because two alias for 'store' table and only one join log: 2012-10-04 11:08:00,345 DEBUG [mondrian.sql] 25: SqlMemberSource.getMemberChildren: executing sql [select `store_1`.`store_name` as `c0`, `store`.`store_type` as `c1`, `store`.`store_manager` as `c2`, `store`.`store_sqft` as `c3`, `store`.`grocery_sqft` as `c4`, `store`.`frozen_sqft` as `c5`, `store`.`meat_sqft` as `c6`, `store`.`coffee_bar` as `c7`, `store`.`store_street_address` as `c8` from `store` as `store_1`, `sales_fact_1997` as `sales_fact_1997`, `store` as `store` where `sales_fact_1997`.`buyer_id` = `store_1`.`store_id` and `store_1`.`store_state` = 'OR' and `store_1`.`store_city` = 'Portland' group by `store_1`.`store_name`, `store`.`store_type`, `store`.`store_manager`, `store`.`store_sqft`, `store`.`grocery_sqft`, `store`.`frozen_sqft`, `store`.`meat_sqft`, `store`.`coffee_bar`, `store`.`store_street_address` order by ISNULL(`store_1`.`store_name`) ASC, `store_1`.`store_name` ASC] 2012-10-04 11:08:00,345 DEBUG [mondrian.server.monitor] SqlStatementStartEvent(25) 2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column store_name is of internal type OBJECT. JDBC type was 12 2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column store_type is of internal type OBJECT. JDBC type was 12 2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column store_manager is of internal type OBJECT. JDBC type was 12 2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column store_sqft is of internal type DOUBLE. JDBC type was 8 2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column grocery_sqft is of internal type DOUBLE. JDBC type was 8 2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column frozen_sqft is of internal type DOUBLE. JDBC type was 8 2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column meat_sqft is of internal type DOUBLE. JDBC type was 8 2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column coffee_bar is of internal type OBJECT. JDBC type was 1 2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.SqlStatement] SqlStatement.guessType - Column store_street_address is of internal type OBJECT. JDBC type was 12 2012-10-04 11:08:00,345 DEBUG [mondrian.sql] 25: , exec 1 ms 2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: executing sql [select `store_1`.`store_name` as `c0`, `store`.`store_type` as `c1`, `store`.`store_manager` as `c2`, `store`.`store_sqft` as `c3`, `store`.`grocery_sqft` as `c4`, `store`.`frozen_sqft` as `c5`, `store`.`meat_sqft` as `c6`, `store`.`coffee_bar` as `c7`, `store`.`store_street_address` as `c8` from `store` as `store_1`, `sales_fact_1997` as `sales_fact_1997`, `store` as `store` where `sales_fact_1997`.`buyer_id` = `store_1`.`store_id` and `store_1`.`store_state` = 'OR' and `store_1`.`store_city` = 'Portland' group by `store_1`.`store_name`, `store`.`store_type`, `store`.`store_manager`, `store`.`store_sqft`, `store`.`grocery_sqft`, `store`.`frozen_sqft`, `store`.`meat_sqft`, `store`.`coffee_bar`, `store`.`store_street_address` order by ISNULL(`store_1`.`store_name`) ASC, `store_1`.`store_name` ASC], exec 1 ms 2012-10-04 11:08:00,345 DEBUG [mondrian.server.monitor] SqlStatementExecuteEvent(25) 2012-10-04 11:08:00,345 DEBUG [mondrian.sql] 25: , exec+fetch 0 ms, 25 rows 2012-10-04 11:08:00,345 DEBUG [mondrian.rolap.RolapUtil] SqlMemberSource.getMemberChildren: done executing sql [select `store_1`.`store_name` as `c0`, `store`.`store_type` as `c1`, `store`.`store_manager` as `c2`, `store`.`store_sqft` as `c3`, `store`.`grocery_sqft` as `c4`, `store`.`frozen_sqft` as `c5`, `store`.`meat_sqft` as `c6`, `store`.`coffee_bar` as `c7`, `store`.`store_street_address` as `c8` from `store` as `store_1`, `sales_fact_1997` as `sales_fact_1997`, `store` as `store` where `sales_fact_1997`.`buyer_id` = `store_1`.`store_id` and `store_1`.`store_state` = 'OR' and `store_1`.`store_city` = 'Portland' group by `store_1`.`store_name`, `store`.`store_type`, `store`.`store_manager`, `store`.`store_sqft`, `store`.`grocery_sqft`, `store`.`frozen_sqft`, `store`.`meat_sqft`, `store`.`coffee_bar`, `store`.`store_street_address` order by ISNULL(`store_1`.`store_name`) ASC, `store_1`.`store_name` ASC], exec+fetch 0 ms, 25 rows, ex=26, close=26, open=[] 2012-10-04 11:08:00,360 DEBUG [mondrian.server.monitor] SqlStatementInfo{sqlStatementId=25} 2012-10-04 11:08:00,360 DEBUG [mondrian.server.monitor] SqlStatementEndEvent(25)
        Hide
        Tiago Gomes Ferreira added a comment -
        pull request:
        https://github.com/pentaho/mondrian/pull/23
        same alias used for properties as well now, only one store in from
        Show
        Tiago Gomes Ferreira added a comment - pull request: https://github.com/pentaho/mondrian/pull/23 same alias used for properties as well now, only one store in from
        Hide
        Pedro Vale added a comment -
        New pull request to solve the issue found by Enrico.

        Assigning to Julian for revision and integration.

        thanks
        Show
        Pedro Vale added a comment - New pull request to solve the issue found by Enrico. Assigning to Julian for revision and integration. thanks
        Hide
        Luc Boudreau added a comment -
        I've merged the request.
        Show
        Luc Boudreau added a comment - I've merged the request.
        Hide
        Li Deng added a comment -
        Verified by following repro steps in Description. verified the query worked and no error received. see attached screenshot and schema FoodMartMysql.xml
        Show
        Li Deng added a comment - Verified by following repro steps in Description. verified the query worked and no error received. see attached screenshot and schema FoodMartMysql.xml

          People

          • Assignee:
            Li Deng
            Reporter:
            Enrico Maria Carmona
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: