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

Does Not Contain Filter from Analyzer (NOT MATCHES) is not natively evaluated resulting in CrossJoin result exceeded limit

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Severity: Urgent
    • Resolution: Not a Bug
    • Affects Version/s: 7.0.0 GA, 7.1.0 GA, 8.0.0 GA
    • Fix Version/s: None
    • Component/s: Native SQL
    • Story Points:
      0
    • 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.
    • Sprint Team:
      Tatooine (Maint)
    • Steps to Reproduce:
      Hide

      MySQL Server Setup

      1. Setup a Foodmart DB in MySQL

      Pentaho Server Setup

      1. Setup a Pentaho Server
      2. Log into PUC as Admin
      3. Set up a new JDBC connection to the Foodmart database in Manage DataSources and call it Foodmart
      4. Upload the attached foodmart.mondrian.xml and point it to the JDBC connection in step 3
      5. Upload the attached report Filter_DoesNotContain.xanalyzer.zip
      6. Run the report and notice that it runs normally but you get the following warning
        Unable to use native SQL evaluation for 'NonEmptyCrossJoin'; reason: arguments not supported
      7. Right click on Product Name and from the Filter dialog select Doesn't Contain and type ET in the text box
      8. Notice right away you get an Alert in Analyzer and checking the logs you can see the crossjoin Limit error

      Actual Results:
      A Crossjoin limit error is thrown in the logs and the reports are not able to complete

      Expected Results
      The filters should be natively evaluated as per the work in MONDRIAN-944
      Note that reverting the filters to CONTAINS the report runs as expected

      Show
      MySQL Server Setup Setup a Foodmart DB in MySQL Pentaho Server Setup Setup a Pentaho Server Log into PUC as Admin Set up a new JDBC connection to the Foodmart database in Manage DataSources and call it Foodmart Upload the attached foodmart.mondrian.xml and point it to the JDBC connection in step 3 Upload the attached report Filter_DoesNotContain.xanalyzer.zip Run the report and notice that it runs normally but you get the following warning Unable to use native SQL evaluation for 'NonEmptyCrossJoin'; reason: arguments not supported Right click on Product Name and from the Filter dialog select Doesn't Contain and type ET in the text box Notice right away you get an Alert in Analyzer and checking the logs you can see the crossjoin Limit error Actual Results: A Crossjoin limit error is thrown in the logs and the reports are not able to complete Expected Results The filters should be natively evaluated as per the work in MONDRIAN-944 Note that reverting the filters to CONTAINS the report runs as expected

      Description

      Based on the work done in MONDRIAN-944 does not contain filters which Analyzer translates to NOT MATCHES should be natively evaluated and run in the same manner if the report were using CONTAINS
      As it is right now any report using 2 or more does not contain filter will never render as it will run into a crossjoin limit reached error similar to below:

      com.pentaho.analyzer.report.ad: Request ID: c24ae7f3-170e-11e8-a1a7-54ee7510ce8b
      	at com.pentaho.analyzer.b.h.a(SourceFile:495)
      	at com.pentaho.analyzer.report.ReportManagerImpl.runReport(SourceFile:330)
      	at com.pentaho.analyzer.report.ReportManagerImpl.runReport(SourceFile:146)
      	at com.pentaho.analyzer.report.ReportRequestService$2.a(SourceFile:269)
      	at com.pentaho.analyzer.report.ReportRequestService$2.call(SourceFile:264)
      	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
      	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
      	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
      	at java.lang.Thread.run(Thread.java:745)
      Caused by: org.olap4j.OlapException: mondrian gave exception while executing query
      	at mondrian.olap4j.MondrianOlap4jConnection$Helper.createException(MondrianOlap4jConnection.java:858)
      	at mondrian.olap4j.MondrianOlap4jStatement.executeOlapQueryInternal(MondrianOlap4jStatement.java:423)
      	at mondrian.olap4j.MondrianOlap4jPreparedStatement.executeQuery(MondrianOlap4jPreparedStatement.java:72)
      	at com.pentaho.analyzer.report.ReportManagerImpl.runReport(SourceFile:324)
      	... 7 more
      Caused by: mondrian.olap.ResourceLimitExceededException: Mondrian Error:Size of CrossJoin result (13,090,680) exceeded limit (5,000,000)
      	at mondrian.resource.MondrianResource$_Def10.ex(MondrianResource.java:1168)
      	at mondrian.olap.Util.checkCJResultLimit(Util.java:3763)
      	at mondrian.olap.fun.CrossJoinFunDef.mutableCrossJoin(CrossJoinFunDef.java:550)
      	at mondrian.olap.fun.CrossJoinFunDef.mutableCrossJoin(CrossJoinFunDef.java:528)
      	at mondrian.olap.fun.NonEmptyCrossJoinFunDef$1.evaluateList(NonEmptyCrossJoinFunDef.java:92)
      	at mondrian.calc.impl.AbstractListCalc.evaluate(AbstractListCalc.java:65)
      	at mondrian.rolap.RolapProfilingEvaluator$ProfilingIterCalc.evaluate(RolapProfilingEvaluator.java:152)
      	at mondrian.rolap.RolapResult.evaluateExp(RolapResult.java:1092)
      	at mondrian.rolap.RolapNamedSetEvaluator.ensureList(RolapNamedSetEvaluator.java:100)
      	at mondrian.rolap.RolapNamedSetEvaluator.evaluateTupleIterable(RolapNamedSetEvaluator.java:66)
      	at mondrian.mdx.NamedSetExpr$1.evaluateIterable(NamedSetExpr.java:98)
      	at mondrian.calc.impl.AbstractIterCalc.evaluate(AbstractIterCalc.java:50)
      	at mondrian.rolap.RolapProfilingEvaluator$ProfilingIterCalc.evaluate(RolapProfilingEvaluator.java:152)
      	at mondrian.calc.impl.GenericIterCalc.evaluateIterable(GenericIterCalc.java:72)
      	at mondrian.olap.fun.GenerateFunDef$GenerateListCalcImpl.evaluateList(GenerateFunDef.java:114)
      	at mondrian.calc.impl.AbstractListCalc.evaluate(AbstractListCalc.java:65)
      	at mondrian.rolap.RolapProfilingEvaluator$ProfilingIterCalc.evaluate(RolapProfilingEvaluator.java:152)
      	at mondrian.rolap.RolapResult.evaluateExp(RolapResult.java:1092)
      	at mondrian.rolap.RolapNamedSetEvaluator.ensureList(RolapNamedSetEvaluator.java:100)
      	at mondrian.rolap.RolapNamedSetEvaluator.evaluateTupleIterable(RolapNamedSetEvaluator.java:66)
      	at mondrian.mdx.NamedSetExpr$1.evaluateIterable(NamedSetExpr.java:98)
      	at mondrian.rolap.RolapResult.executeAxis(RolapResult.java:980)
      	at mondrian.rolap.RolapResult.evalLoad(RolapResult.java:804)
      	at mondrian.rolap.RolapResult.loadMembers(RolapResult.java:760)
      	at mondrian.rolap.RolapResult.(RolapResult.java:246)
      	at mondrian.rolap.RolapConnection.executeInternal(RolapConnection.java:662)
      	at mondrian.rolap.RolapConnection.access$000(RolapConnection.java:52)
      	at mondrian.rolap.RolapConnection$1.call(RolapConnection.java:613)
      	at mondrian.rolap.RolapConnection$1.call(RolapConnection.java:611)
      	... 4 more
      

        PractiTest Integration




          Attachments

            Issue Links

              Activity

                People

                Assignee:
                Unassigned Unassigned
                Reporter:
                clopez Carlos Lopez
                Votes:
                3 Vote for this issue
                Watchers:
                12 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved: