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

Regression: Unable to execute MDX statement with native MATCHES

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Unknown Unknown
    • Resolution: Fixed
    • Affects Version/s: 3.3.0 GA (4.1.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
    • CI Build:
      CI build dated Oct 6, 2011 9:27:15 PM bi-server-ee-MON33.zip(#60).

      Description

      This used to work on Mondrian 3.2 but after upgrade to 3.3 this Analyzer test started failing. It may have something to do with native evaluation of the match function on mysql.

      Stack trace:

      java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ');.*' order by ISNULL(`product_class`.`product_family`) ASC, `product_class`.`pr' at line 1
      at com.pentaho.analyzer.service.impl.AnalysisAreaManager.executeQuery(AnalysisAreaManager.java:278)
      at com.pentaho.analyzer.report.ReportManagerImpl.parseAndExecuteMDX(ReportManagerImpl.java:301)
      at com.pentaho.analyzer.report.ReportManagerImpl.getCVResultSet(ReportManagerImpl.java:362)
      at com.pentaho.analyzer.report.ReportManagerImpl.runReport(ReportManagerImpl.java:117)
      at com.pentaho.analyzer.report.ReportManagerImpl.runReport(ReportManagerImpl.java:88)
      at com.pentaho.analyzer.report.ReportRequestService.runReportSync(ReportRequestService.java:228)
      at com.pentaho.analyzer.service.impl.PredicateFilterTest.testFilterContainsSpecialChars(PredicateFilterTest.java:420)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
      at java.lang.reflect.Method.invoke(Unknown Source)
      at org.junit.internal.runners.TestMethod.invoke(TestMethod.java:59)
      at org.junit.internal.runners.MethodRoadie.runTestMethod(MethodRoadie.java:98)
      at org.junit.internal.runners.MethodRoadie$2.run(MethodRoadie.java:79)
      at org.junit.internal.runners.MethodRoadie.runBeforesThenTestThenAfters(MethodRoadie.java:87)
      at org.junit.internal.runners.MethodRoadie.runTest(MethodRoadie.java:77)
      at org.junit.internal.runners.MethodRoadie.run(MethodRoadie.java:42)
      at org.junit.internal.runners.JUnit4ClassRunner.invokeTestMethod(JUnit4ClassRunner.java:88)
      at org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)
      at org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)
      at org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)
      at org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)
      at org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)
      at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:49)
      at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
      Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ');.*' order by ISNULL(`product_class`.`product_family`) ASC, `product_class`.`pr' at line 1
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
      at java.lang.reflect.Constructor.newInstance(Unknown Source)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
      at com.mysql.jdbc.Util.getInstance(Util.java:381)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2642)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2571)
      at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1464)
      at mondrian.rolap.SqlStatement.execute(SqlStatement.java:161)
      at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.java:279)
      at mondrian.rolap.HighCardSqlTupleReader.prepareTuples(HighCardSqlTupleReader.java:80)
      at mondrian.rolap.HighCardSqlTupleReader.readMembers(HighCardSqlTupleReader.java:136)
      at mondrian.rolap.RolapNativeSet$SetEvaluator.executeList(RolapNativeSet.java:234)
      at mondrian.rolap.RolapNativeSet$SetEvaluator.execute(RolapNativeSet.java:177)
      at mondrian.olap.fun.FilterFunDef$BaseIterCalc.evaluateIterable(FilterFunDef.java:118)
      at mondrian.calc.impl.AbstractIterCalc.evaluate(AbstractIterCalc.java:50)
      at mondrian.rolap.RolapResult.evaluateExp(RolapResult.java:864)
      at mondrian.rolap.RolapNamedSetEvaluator.ensureList(RolapNamedSetEvaluator.java:87)
      at mondrian.rolap.RolapNamedSetEvaluator.evaluateTupleIterable(RolapNamedSetEvaluator.java:62)
      at mondrian.mdx.NamedSetExpr$1.evaluateIterable(NamedSetExpr.java:99)
      at mondrian.olap.fun.FilterFunDef$IterIterCalc.makeIterable(FilterFunDef.java:217)
      at mondrian.olap.fun.FilterFunDef$BaseIterCalc.evaluateIterable(FilterFunDef.java:121)
      at mondrian.calc.impl.AbstractIterCalc.evaluate(AbstractIterCalc.java:50)
      at mondrian.rolap.RolapResult.evaluateExp(RolapResult.java:864)
      at mondrian.rolap.RolapNamedSetEvaluator.ensureList(RolapNamedSetEvaluator.java:87)
      at mondrian.rolap.RolapNamedSetEvaluator.evaluateTupleIterable(RolapNamedSetEvaluator.java:62)
      at mondrian.mdx.NamedSetExpr$1.evaluateIterable(NamedSetExpr.java:99)
      at mondrian.olap.fun.GenerateFunDef$GenerateListCalcImpl.evaluateList(GenerateFunDef.java:111)
      at mondrian.calc.impl.AbstractListCalc.evaluate(AbstractListCalc.java:65)
      at mondrian.rolap.RolapResult.evaluateExp(RolapResult.java:864)
      at mondrian.rolap.RolapNamedSetEvaluator.ensureList(RolapNamedSetEvaluator.java:87)
      at mondrian.rolap.RolapNamedSetEvaluator.evaluateTupleIterable(RolapNamedSetEvaluator.java:62)
      at mondrian.mdx.NamedSetExpr$1.evaluateIterable(NamedSetExpr.java:99)
      at mondrian.olap.fun.OrderFunDef$CalcImpl.evaluateList(OrderFunDef.java:199)
      at mondrian.calc.impl.AbstractListCalc.evaluate(AbstractListCalc.java:65)
      at mondrian.rolap.RolapResult.evaluateExp(RolapResult.java:864)
      at mondrian.rolap.RolapNamedSetEvaluator.ensureList(RolapNamedSetEvaluator.java:87)
      at mondrian.rolap.RolapNamedSetEvaluator.evaluateTupleIterable(RolapNamedSetEvaluator.java:62)
      at mondrian.mdx.NamedSetExpr$1.evaluateIterable(NamedSetExpr.java:99)
      at mondrian.calc.impl.IterableListCalc.evaluateList(IterableListCalc.java:38)
      at mondrian.olap.fun.UnionFunDef$1.evaluateList(UnionFunDef.java:56)
      at mondrian.calc.impl.AbstractListCalc.evaluateIterable(AbstractListCalc.java:71)
      at mondrian.rolap.RolapResult.executeAxis(RolapResult.java:772)
      at mondrian.rolap.RolapResult.evalLoad(RolapResult.java:615)
      at mondrian.rolap.RolapResult.loadMembers(RolapResult.java:580)
      at mondrian.rolap.RolapResult.<init>(RolapResult.java:288)
      at mondrian.rolap.RolapConnection.executeInternal(RolapConnection.java:625)
      at mondrian.rolap.RolapConnection.access$000(RolapConnection.java:52)
      at mondrian.rolap.RolapConnection$1.call(RolapConnection.java:586)
      at mondrian.rolap.RolapConnection$1.call(RolapConnection.java:585)
      at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
      at java.util.concurrent.FutureTask.run(Unknown Source)
      at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
      at java.lang.Thread.run(Unknown Source)


      Here's the SQL:
      2187 [mondrian.rolap.RolapResultShepherd$executor] DEBUG mondrian.rolap.RolapUtil - HighCardSqlTupleReader.readTuples [[Product].[Product Category]]: done executing sql [select `product_class`.`product_family` as `c0`, `product_class`.`product_department` as `c1`, `product_class`.`product_category` as `c2` from `FOODMART`.`product` as `product`, `FOODMART`.`product_class` as `product_class`, `FOODMART`.`sales_fact_1997` as `sales_fact_1997` where `product`.`product_class_id` = `product_class`.`product_class_id` and `sales_fact_1997`.`product_id` = `product`.`product_id` group by `product_class`.`product_family`, `product_class`.`product_department`, `product_class`.`product_category` having UPPER(c2) REGEXP '.*A"); WINDOW.ALERT("WOOT');.*' order by ISNULL(`product_class`.`product_family`) ASC, `product_class`.`product_family` ASC, ISNULL(`product_class`.`product_department`) ASC, `product_class`.`product_department` ASC, ISNULL(`product_class`.`product_category`) ASC, `product_class`.`product_category` ASC], exec+fetch 8 ms, 0 rows


      MDX:

       With
       Set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Product], Not IsEmpty ([Measures].[Unit Sales]))'
       Set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS],[Product].CurrentMember.OrderKey,BASC,Ancestor([Product].CurrentMember,[Product].[Product Department]).OrderKey,BASC)'
       Set [*NATIVE_MEMBERS_Product] as 'Generate([*NATIVE_CJ_SET], {[Product].CurrentMember})'
       Set [*BASE_MEMBERS_Product] as 'Filter([Product].[Product Category].Members,[Product].CurrentMember.Caption Matches ("(?i).*\Qa""); window.alert(""woot'');\E.*"))'
       Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'
       Set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Product].currentMember)})'
       Set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
       Member [Product].[*TOTAL_MEMBER_SEL~SUM] as 'Sum([*NATIVE_MEMBERS_Product])', SOLVE_ORDER=-100
       Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Unit Sales]', FORMAT_STRING = 'Standard', SOLVE_ORDER=400
       Select
       [*BASE_MEMBERS_Measures] on columns,
       Union({[Product].[*TOTAL_MEMBER_SEL~SUM]},[*SORTED_ROW_AXIS]) on rows
       From [Sales]

        Activity

        Hide
        Julian Hyde added a comment -
        Fixed in change 14590.
        Show
        Julian Hyde added a comment - Fixed in change 14590.
        Hide
        Suhas Gururaja added a comment -
        Tested this issue on CI build dated Oct 6, 2011 9:27:15 PM bi-server-ee-MON33.zip(#60).

        Used the above mentioned MDX query and it is working fine. We are not getting any error in the logs. Please find the attached screen shots and the logs.
        Show
        Suhas Gururaja added a comment - Tested this issue on CI build dated Oct 6, 2011 9:27:15 PM bi-server-ee-MON33.zip(#60). Used the above mentioned MDX query and it is working fine. We are not getting any error in the logs. Please find the attached screen shots and the logs.

          People

          • Assignee:
            Suhas Gururaja
            Reporter:
            Benny Chow
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: