[
https://issues.apache.org/jira/browse/CALCITE-6219?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810180#comment-17810180
]
Julian Hyde edited comment on CALCITE-6219 at 1/24/24 3:04 AM:
---------------------------------------------------------------
That sounds good.
Views are also a consideration; hopefully they work very much the same as CTEs.
A good algorithm might be:
* If a query (SELECT block) has tables in its FROM clause with 'filter
required' columns, then each of those columns must appear in an expression in
the WHERE, HAVING, ON or USING clause.
* If they do not, those columns must be in the SELECT clause, and those output
columns will be tagged 'filter required'.
* If a top-level query has any SELECT columns tagged 'filter required', it is
an error.
* If any 'filter required' columns are not filtered and not in the SELECT
clause, it is an error.
How are columns tagged? I suggest that
{{Table.unwrap(SemanticTable.class).isPresent()}} and
{{SemanticTable.getFilter(columnName)}} returns a not-null {{String}} value for
'filter required' columns. Add an interface in
{{{}order.apache.calcite.schema{}}}:
{code:java}
interface SemanticTable {
@Nullable String getFilter(String columnName);
}
{code}
was (Author: julianhyde):
That sounds good.
Views are also a consideration; hopefully they work very much the same as CTEs.
A good algorithm might be:
* If a query (SELECT block) has tables in its FROM clause with 'filter
required' columns, then each of those columns must appear in an expression in
the WHERE, HAVING, ON or USING clause.
* If they do not, those columns must be in the SELECT clause, and those output
columns will be tagged 'filter required'.
* If a top-level query has any SELECT columns tagged 'filter required', it is
an error.
* If any 'filter required' columns are not filtered and not in the SELECT
clause, it is an error.
How are columns tagged? I suggest that
{{Table.unwrap(SemanticTable.class).isPresent()}} and
{{SemanticTable.getFilter(columnName)}} returns a not-null {{String}} value for
'filter required' columns. Add an interface in
{{{}order.apache.calcite.schema{}}}:
{code:java}
interface SemanticTable {
@Nullable String getFilter(String columnName);
}
{code}
> 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
>
> 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)
