[
https://issues.apache.org/jira/browse/CALCITE-6219?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17816265#comment-17816265
]
Julian Hyde commented on CALCITE-6219:
--------------------------------------
[~oliverlee], In my
[6219-always-filter|https://github.com/julianhyde/calcite/tree/6219-always-filter]
branch I have reworked your PR, moving the logic into SqlValidatorNamespace
and reasoning in terms of fields (table alias + ordinal) rather than field
names. It handles self-joins correctly (because it can track the fields from
the two table aliases independently). It handles subqueries correctly
(returning must-filter fields in the namespace, and throwing if the top-level
namespace has any must-filter fields).
I think 'must-filter fields' is the best description of this feature for
Calcite. (Or 'must-feature columns' for end-user messages.)
I added a bunch of TODOs, including moving the message to CalciteResource, and
obsoleting AlwaysFilterValidatorImpl (because its functionality is now
elsewhere). Can you take a look at my changes, and work on the TODOs?
> Support SQL Validation for Tables with columns tagged as 'filter required'
> --------------------------------------------------------------------------
>
> Key: CALCITE-6219
> URL: https://issues.apache.org/jira/browse/CALCITE-6219
> Project: Calcite
> Issue Type: New Feature
> Reporter: Oliver Lee
> Assignee: Oliver Lee
> Priority: Major
> Labels: pull-request-available
>
> 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
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
