Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-254

Propagate RelCollation on aliased columns in JoinRule

    Details

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

      Description

      Consider this query:

      SELECT *
      FROM emp JOIN dept ON emp.deptno = dept.deptno
      ORDER BY dept.deptno;

      If table emp is sorted on emp.deptno (for example, table emp has index on emp.deptno, or table emp comes from a subquery which sorts on emp.deptno), the join predicate emp.deptno = dept.deptno indicates the two columns are aliased columns in terms of ordering. If JoinRule keep track of the RelCollation trait for the aliased columns, the query essentially become as following

      SELECT *
      FROM emp JOIN dept ON emp.deptno = dept.deptno
      ORDER BY emp.deptno;

      The ORDER BY should not require a SortRel, since the input table emp is sorted on emp.depno.

      Currently, optiq framework does not recognize the aliased columns from join = predicates, and may introduce redundant sort to the plan.

      This issue is relevant to issue (#41 | CALCITE-41)
      https://github.com/julianhyde/optiq/issues/41

      ---------------- Imported from GitHub ----------------
      Url: https://github.com/julianhyde/optiq/issues/254
      Created by: jinfengni
      Labels:
      Created at: Thu Apr 17 22:53:46 CEST 2014
      State: open

        Issue Links

          Activity

          Hide
          julianhyde Julian Hyde added a comment -

          Closing now that 1.1.0-incubating has been released.

          Show
          julianhyde Julian Hyde added a comment - Closing now that 1.1.0-incubating has been released.
          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/2709896e .
          Hide
          github-import GitHub Import added a comment -

          [Date: Fri Apr 18 23:11:55 CEST 2014, Author: jinfengni]

          That's right.

          Actually, the new code we put in Drill "physical" join rule propagates
          trait from left side (not from both sides), for both RelCollation trait and
          Distribution trait.

          The reason I asked the question in optiq group days ago is because I want
          to see if optiq already has a solution to keep track of equivalence class
          (or aliased columns) for RelTraits, before we come up with something else.
          Such solution will be useful for either RelCollation, or Distribution
          trait, or predicate push-down (predicate from transitive closure) . Doing
          that will help performance by removing redundant SortRel or ExchangeRel in
          the generated plan.

          On Fri, Apr 18, 2014 at 12:28 PM, Julian Hyde <notifications@github.com>wrote:

          > So, this isn't really an issue with the Optiq engine. I'm sure you'd like
          > me to fix EnumerableJoinRule so that you can copy the fix into
          > DrillJoinRule. But to be clear: nothing's stopping you from fixing
          > DrillJoinRule. Ball's in your court.
          >
          > —
          > Reply to this email directly or view it on GitHub<https://github.com/julianhyde/optiq/issues/254#issuecomment-40837220>
          > .
          >

          Show
          github-import GitHub Import added a comment - [Date: Fri Apr 18 23:11:55 CEST 2014, Author: jinfengni ] That's right. Actually, the new code we put in Drill "physical" join rule propagates trait from left side (not from both sides), for both RelCollation trait and Distribution trait. The reason I asked the question in optiq group days ago is because I want to see if optiq already has a solution to keep track of equivalence class (or aliased columns) for RelTraits, before we come up with something else. Such solution will be useful for either RelCollation, or Distribution trait, or predicate push-down (predicate from transitive closure) . Doing that will help performance by removing redundant SortRel or ExchangeRel in the generated plan. On Fri, Apr 18, 2014 at 12:28 PM, Julian Hyde <notifications@github.com>wrote: > So, this isn't really an issue with the Optiq engine. I'm sure you'd like > me to fix EnumerableJoinRule so that you can copy the fix into > DrillJoinRule. But to be clear: nothing's stopping you from fixing > DrillJoinRule. Ball's in your court. > > — > Reply to this email directly or view it on GitHub< https://github.com/julianhyde/optiq/issues/254#issuecomment-40837220 > > . >
          Hide
          github-import GitHub Import added a comment -

          [Date: Fri Apr 18 21:28:30 CEST 2014, Author: julianhyde]

          So, this isn't really an issue with the Optiq engine. I'm sure you'd like me to fix `EnumerableJoinRule` so that you can copy the fix into `DrillJoinRule`. But to be clear: nothing's stopping you from fixing `DrillJoinRule`. Ball's in your court.

          Show
          github-import GitHub Import added a comment - [Date: Fri Apr 18 21:28:30 CEST 2014, Author: julianhyde ] So, this isn't really an issue with the Optiq engine. I'm sure you'd like me to fix `EnumerableJoinRule` so that you can copy the fix into `DrillJoinRule`. But to be clear: nothing's stopping you from fixing `DrillJoinRule`. Ball's in your court.
          Hide
          github-import GitHub Import added a comment -

          [Date: Fri Apr 18 01:27:18 CEST 2014, Author: jinfengni]

          That's right. Propagation is only applicable for "physical" JoinRel, when we know exactly the join implementation, and whether the join implementation can retain sort order on either side or both sides.

          Show
          github-import GitHub Import added a comment - [Date: Fri Apr 18 01:27:18 CEST 2014, Author: jinfengni ] That's right. Propagation is only applicable for "physical" JoinRel, when we know exactly the join implementation, and whether the join implementation can retain sort order on either side or both sides.
          Hide
          github-import GitHub Import added a comment -

          [Date: Fri Apr 18 00:26:44 CEST 2014, Author: julianhyde]

          Propagation is only relevant for certain kinds of JoinRel. For example, EnumerableJoinRel retains sort order of the LHS. JdbcJoinRel does not retain any sorting. A merge join (we don't have one currently) would retain sort order on both sides.

          If columns x1 and y1 are aliases, and output of the join is sorted on x1, then we would generate multiple collations: [x1], [y1]. A single collation [x1, y1] would be wrong.

          Show
          github-import GitHub Import added a comment - [Date: Fri Apr 18 00:26:44 CEST 2014, Author: julianhyde ] Propagation is only relevant for certain kinds of JoinRel. For example, EnumerableJoinRel retains sort order of the LHS. JdbcJoinRel does not retain any sorting. A merge join (we don't have one currently) would retain sort order on both sides. If columns x1 and y1 are aliases, and output of the join is sorted on x1, then we would generate multiple collations: [x1] , [y1] . A single collation [x1, y1] would be wrong.

            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