Uploaded image for project: 'Pentaho Analysis - Mondrian'
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-983

Regression: Unable to execute MDX statement with native MATCHES

    Details

    • Type: Bug
    • Status: Closed
    • Severity: 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.

      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]

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: