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

'Must-filter' columns

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • New Feature
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.37.0
    • None

    Description

      Suppose that a user's Table definitions come in with a tag on certain columns that indicates the the column is required to have a filter on it for all incoming queries. 

       

      I would like to add support to validate that incoming queries satisfies the table condition.

      If all of the table's specified fields has a filter on it (present in a WHERE or HAVING clause for the query), then it will not error. 

       

      ex. 

      EMP table specifies that EMPNO requires a filter

       

      select * from emp where empno = 1  -> No error

      select * from emp where ename = 'bob' -> Error

      select * from emp -> Error

       

      The validation would occur after the namespace validation in SqlValidatorImpl as a separate pass.

       

      I am envisioning that the full filter validation algorithm will form a couple of key steps

      •  Scanning the catalog/schema/tables and determining which fields are tagged to always require filters
      • A pass through the SQL statement to see if a certain field needs to be filtered multiple times (potentially for CTE, joins? needs further investigation) 
      • A pass through the SQL statement to discover filters on the statement 

       

      In determining whether a WHERE or HAVING clause contains a certain field identifier, there will need to be a helper visitor for WHERE or HAVING SqlNodes to collect all of the SqlIdentifiers that could be nested within the operandList 

       

      Special considerations:

      • joins
      • CTEs
      • subqueries

      Attachments

        Issue Links

        Activity

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

          People

            oliverlee Oliver Lee
            oliverlee Oliver Lee
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment