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

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

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.27.0
    • 1.27.0
    • core
    • 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

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            Unassigned Unassigned
            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

                Slack

                  Issue deployment