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

RelBuilder/RexSimplify/Sarg incorrectly transforms complex expressions with IS NULL/IS NOT NULL

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.27.0
    • Fix Version/s: 1.27.0
    • Component/s: core
    • Environment:

      All environments

      Description

      Now, the following query with IS NOT NULL:

      SELECT *
       FROM emp
       WHERE deptno > 20 AND deptno < 30 AND mgr IS NOT NULL

      Is simplified to:

      SELECT *
       FROM emp
       WHERE deptno > 20 AND deptno < 30

      It seems that the simplification of complex `AND` filters incorrectly simplify `IS NOT NULL` to `TRUE`.
      During simplification, `IS NOT NULL` is transformed to `SEARCH($3, Sarg[(-∞..+∞), null])` instead of `SEARCH($3, Sarg[(-∞..+∞)])`.

      Also, the following query with IS NULL:

      SELECT *
      FROM emp
      WHERE deptno > 20 AND deptno < 30 AND mgr IS NULL
      

      Is simplified to the following query:

      SELECT * 
      FROM emp 
      WHERE deptno > 20 AND deptno < 30
      

      However, now it is correct when transformed into a Sarg, but incorrect after `RexUtil.expandSearch()` expansion.

       

      A simpler query does not show this issue (stays the same):

      SELECT *
       FROM emp
       WHERE mgr IS NOT NULL

      The patch with the corresponding test cases is in attachments.

       

        Attachments

        1. rex-simplify-issue-tests-3.patch
          5 kB
          Igor Lozynskyi

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                aigor Igor Lozynskyi
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 1h 20m
                  1h 20m