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

Add IS_NOT_NULL predicate on not-nullable equi-join keys

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 1.13.0
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      In equi-joins, if the join keys are not-nullable according to join type, we can assign a IS_NOT_NULL predicate to reduce input. For example, query:

      select * from src1 join src2 on src1.id = src2.id
      

      can be rewrite to

      select * from 
      (select * from src1 where src1.id is not null) src1
      join 
      (select * from src2 where src2.id is not null) src2
      on src1.id = src2.id
      

        Issue Links

          Activity

          Hide
          tedxu Ted Xu added a comment -

          Looks like CALCITE-1526 already fixed this one.

          Show
          tedxu Ted Xu added a comment - Looks like CALCITE-1526 already fixed this one.
          Hide
          julianhyde Julian Hyde added a comment -

          It might be worth writing a test case to make sure that you get the behavior you want in, say, RelOptRulesTest. I can see that converting

          SELECT * FROM t1 JOIN (SELECT DISTINCT k FROM t2) USING (k)

          to

          SELECT * FROM t1 JOIN (SELECT DISTINCT k FROM t2 WHERE k IS NOT NULL) USING (k)

          might improve the query significantly.

          Show
          julianhyde Julian Hyde added a comment - It might be worth writing a test case to make sure that you get the behavior you want in, say, RelOptRulesTest. I can see that converting SELECT * FROM t1 JOIN (SELECT DISTINCT k FROM t2) USING (k) to SELECT * FROM t1 JOIN (SELECT DISTINCT k FROM t2 WHERE k IS NOT NULL) USING (k) might improve the query significantly.

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              tedxu Ted Xu
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development