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

Remove predicates from Filter if they can be proved to be always true or false

    Details

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

      Description

      Remove predicates from Filter if they can be proved to be always true or false. For example, the query

      select *
      from (select * from emp where deptno > 10)
      where  empno > 3 and deptno > 5
      

      can be simplified to

      select *
      from (select * from emp where deptno > 10)
      where  empno > 3
      

      because deptno > 5 can be proved to be always true, due the predicate deptno > 10 on its input relational expression.

      Implement this by expanding the capabilities of FilterReduceExpressionsRule.

        Issue Links

          Activity

          Hide
          julianhyde Julian Hyde added a comment -

          Resolved in release 1.15.0 (2017-12-11).

          Show
          julianhyde Julian Hyde added a comment - Resolved in release 1.15.0 (2017-12-11).
          Hide
          julianhyde Julian Hyde added a comment -

          Fixed in a9ac3e4, but there is a follow-up issue CALCITE-2074.

          Show
          julianhyde Julian Hyde added a comment - Fixed in a9ac3e4 , but there is a follow-up issue CALCITE-2074 .
          Hide
          jcamachorodriguez Jesus Camacho Rodriguez added a comment -

          Thanks Julian Hyde, patch LGTM.

          Show
          jcamachorodriguez Jesus Camacho Rodriguez added a comment - Thanks Julian Hyde , patch LGTM.
          Hide
          julianhyde Julian Hyde added a comment -

          There is a patch in https://github.com/julianhyde/calcite/tree/1995-redundant-filter. Jesus Camacho Rodriguez, can you please review?

          The change also removes conjunctions. For example, the condition x > 5 AND y > 1, given the predicate on the input x > 10, can be simplified to y > 1.

          Sometimes conditions are proved unsatisfiable. For example, the condition x > 5 AND y > 1, given the predicate on the input x < 1, can be simplified to false.

          We strengthen conjunctions if the result is a point. For example, given the predicate on the input x >= 10, we change the condition x <= 10 AND y > 1 to (x = 10 AND y > 1.

          We do not strengthen conjunctions to a range, because the range tends to be more verbose and therefore more expensive to implement. For example, given the predicate on the input x > 10, we could safely change the condition x < 20 AND y > 1 to (x > 10 AND x < 20 AND y > 1, but we do not change it.

          Show
          julianhyde Julian Hyde added a comment - There is a patch in https://github.com/julianhyde/calcite/tree/1995-redundant-filter . Jesus Camacho Rodriguez , can you please review? The change also removes conjunctions. For example, the condition x > 5 AND y > 1 , given the predicate on the input x > 10 , can be simplified to y > 1 . Sometimes conditions are proved unsatisfiable. For example, the condition x > 5 AND y > 1 , given the predicate on the input x < 1 , can be simplified to false . We strengthen conjunctions if the result is a point. For example, given the predicate on the input x >= 10 , we change the condition x <= 10 AND y > 1 to (x = 10 AND y > 1 . We do not strengthen conjunctions to a range, because the range tends to be more verbose and therefore more expensive to implement. For example, given the predicate on the input x > 10 , we could safely change the condition x < 20 AND y > 1 to (x > 10 AND x < 20 AND y > 1 , but we do not change it.
          Hide
          julianhyde Julian Hyde added a comment -

          This ought to work even if FilterMergeRule is not enabled. This should work just based on the predicates bubbling up, not based on the particular type of the input expression.

          My motivation for this: when we add stored generated columns and check constraints in CALCITE-1991, even scans on base tables will have predicates.

          Show
          julianhyde Julian Hyde added a comment - This ought to work even if FilterMergeRule is not enabled. This should work just based on the predicates bubbling up, not based on the particular type of the input expression. My motivation for this: when we add stored generated columns and check constraints in CALCITE-1991 , even scans on base tables will have predicates.
          Hide
          jcamachorodriguez Jesus Camacho Rodriguez added a comment -

          It seems FilterMergeRule is not enabled (just from looking at the test case)?

          Show
          jcamachorodriguez Jesus Camacho Rodriguez added a comment - It seems FilterMergeRule is not enabled (just from looking at the test case)?
          Hide
          julianhyde Julian Hyde added a comment -

          I'd hoped that RexSimplify would do it. But I added a test case in https://github.com/julianhyde/calcite/tree/1995-redundant-filter and apparently the code isn't doing its job.

          Show
          julianhyde Julian Hyde added a comment - I'd hoped that RexSimplify would do it. But I added a test case in https://github.com/julianhyde/calcite/tree/1995-redundant-filter and apparently the code isn't doing its job.
          Hide
          jcamachorodriguez Jesus Camacho Rodriguez added a comment -

          Julian Hyde, I think this is taken care of in RexSimplify already (method processRange), unless we are missing some cases and needs an extension.

          Show
          jcamachorodriguez Jesus Camacho Rodriguez added a comment - Julian Hyde , I think this is taken care of in RexSimplify already (method processRange ), unless we are missing some cases and needs an extension.

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development