Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: None
    • Fix Version/s: 1.0.0-incubating
    • Component/s: None
    • Labels:

      Description

      Hi Julian,

      If I put 'or' in a join condition, then optiq will throw CanNotPlanException. If I change "or" to "and", everything is fine. (The query itself does not have any real meaning).

      "select d.\"deptno\" \n"
      + "from \"hr\".\"emps\" as e\n"
      + "join \"hr\".\"depts\" as d\n"
      + " on e.\"deptno\" = d.\"deptno\" or e.\"empid\" = d.\"deptno\"\n"

      java.sql.SQLException: while executing SQL: select d."deptno"
      from "hr"."emps" as e
      join "hr"."depts" as d
      on e."deptno" = d."deptno" or e."empid" = d."deptno"

      at net.hydromatic.avatica.Helper.createException(Helper.java:40)
      at net.hydromatic.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:80)
      at net.hydromatic.optiq.test.JdbcTest.testReadme(JdbcTest.java:318)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
      at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
      at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
      at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
      at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
      at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
      at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
      at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
      at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
      at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
      at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
      at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
      at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
      at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
      at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:74)
      at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:202)
      at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:65)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
      Caused by: org.eigenbase.relopt.RelOptPlanner$CannotPlanException: Node [rel(#22 | FLINK-22):Subset([#5|https://github.com/JulianHyde/optiq/issues/5] | FLINK-5).ENUMERABLE.[]] could not be implemented; planner state:

      ---------------- Imported from GitHub ----------------
      Url: https://github.com/julianhyde/optiq/issues/212
      Created by: jinfengni
      Labels:
      Created at: Thu Mar 27 02:23:22 CET 2014
      State: open

        Issue Links

          Activity

          Hide
          github-import GitHub Import added a comment -

          [Date: Fri Mar 28 05:28:34 CET 2014, Author: julianhyde]

          I think there's a simple explanation. In out-of-the-box optiq, there is no implementation of join that can handle conditions that are not equals. In technical parlance, there is no theta-join.

          Even without a theta-join operator, we ought to be able to implement inner joins, such as this, as a filter on top of a cartesian product. I guess there's a rule missing.

          Show
          github-import GitHub Import added a comment - [Date: Fri Mar 28 05:28:34 CET 2014, Author: julianhyde ] I think there's a simple explanation. In out-of-the-box optiq, there is no implementation of join that can handle conditions that are not equals. In technical parlance, there is no theta-join. Even without a theta-join operator, we ought to be able to implement inner joins, such as this, as a filter on top of a cartesian product. I guess there's a rule missing.
          Hide
          github-import GitHub Import added a comment -

          [Date: Fri Mar 28 05:55:03 CET 2014, Author: jinfengni]

          I actually used JdbcTest.testReadme(), and replaced the query in that method with the one I tried. I thought the method testReadme() will go through a planner with all available rules.

          By checking the set/relsubsets, seems there is no EnumerableJoinRel converted from JoinRel. I saw EnumerableJoinRel will check if it's equal join, and throw InvalidRelException if not. That probably explains why there is no EnumerableJoinRel converted from JoinRel.

          Show
          github-import GitHub Import added a comment - [Date: Fri Mar 28 05:55:03 CET 2014, Author: jinfengni ] I actually used JdbcTest.testReadme(), and replaced the query in that method with the one I tried. I thought the method testReadme() will go through a planner with all available rules. By checking the set/relsubsets, seems there is no EnumerableJoinRel converted from JoinRel. I saw EnumerableJoinRel will check if it's equal join, and throw InvalidRelException if not. That probably explains why there is no EnumerableJoinRel converted from JoinRel.
          Hide
          github-import GitHub Import added a comment -

          [Date: Fri Mar 28 06:00:52 CET 2014, Author: jinfengni]

          Just debug in EnumerableJoinRule.convert(), and verified that it did caught an InvalidRelException. However, the code just log the exception, and return null. Do you think it might be better to stop the execution, in stead of return null (which then will hit CanNotPlanException eventually) ?

          } catch (InvalidRelException e)

          { LOGGER.fine(e.toString()); return null; }
          Show
          github-import GitHub Import added a comment - [Date: Fri Mar 28 06:00:52 CET 2014, Author: jinfengni ] Just debug in EnumerableJoinRule.convert(), and verified that it did caught an InvalidRelException. However, the code just log the exception, and return null. Do you think it might be better to stop the execution, in stead of return null (which then will hit CanNotPlanException eventually) ? } catch (InvalidRelException e) { LOGGER.fine(e.toString()); return null; }
          Hide
          github-import GitHub Import added a comment -

          [Date: Fri Mar 28 06:05:57 CET 2014, Author: julianhyde]

          InvalidRelException just means that a particular rule cannot fire. It is not a big deal – there are usually lots more rules – which is why the tracing level is FINE.

          CannotPlanException means that the whole query cannot be planned. The cause is often complex. A rule or set of rules may not exist, or may not be enabled, or some fine setup like removing trivial casts may prevent the rules that need to fire from firing.

          In this case it may be simple – there is no theta-join operator, hence no rule exists to handle theta-join.

          Julian

          Show
          github-import GitHub Import added a comment - [Date: Fri Mar 28 06:05:57 CET 2014, Author: julianhyde ] InvalidRelException just means that a particular rule cannot fire. It is not a big deal – there are usually lots more rules – which is why the tracing level is FINE. CannotPlanException means that the whole query cannot be planned. The cause is often complex. A rule or set of rules may not exist, or may not be enabled, or some fine setup like removing trivial casts may prevent the rules that need to fire from firing. In this case it may be simple – there is no theta-join operator, hence no rule exists to handle theta-join. Julian
          Hide
          julianhyde Julian Hyde added a comment -

          CALCITE-451 should solve this.

          Show
          julianhyde Julian Hyde added a comment - CALCITE-451 should solve this.
          Hide
          julianhyde Julian Hyde added a comment -

          Once we have theta join, check whether this case is now solved.

          Show
          julianhyde Julian Hyde added a comment - Once we have theta join, check whether this case is now solved.
          Hide
          julianhyde Julian Hyde added a comment -

          I have confirmed that, now CALCITE-451 is fixed, the test case works.

          Show
          julianhyde Julian Hyde added a comment - I have confirmed that, now CALCITE-451 is fixed, the test case works.
          Hide
          julianhyde Julian Hyde added a comment -

          Closing now that 1.0.0-incubating has been released.

          Show
          julianhyde Julian Hyde added a comment - Closing now that 1.0.0-incubating has been released.

            People

            • Assignee:
              Unassigned
              Reporter:
              github-import GitHub Import
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development