Details
-
New Feature
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
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