[jira] [Updated] (CALCITE-6301) Extend ‘Must-filter’ columns to support a conditional bypass list

["Julian Hyde (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-6301?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde updated CALCITE-6301:
---------------------------------
    Description: 
In CALCITE-6219 we introduced {{{}interface SemanticTable{}}}. A table that implements this interface can define fields to be ‘must-filter’; a query that does not filter those fields in its {{WHERE}} or {{HAVING}} clause will throw a validation error.

In this case, we allow a {{SemanticTable}} to have a list of bypass fields. If a query filters on any bypass field in its {{WHERE}} or {{HAVING}} clause, then the must-filter fields are ignored. 

For example, the {{EMP}} table specifies must-filter-fields {{EMPNO}} and {{{}DEPTNO{}}},
and bypass-fields {{ENAME}} and {{{}SALARY{}}}. Consider the following queries on {{{}EMP{}}}.
{code:java}
// Valid. Query filters on both must-filter fields.
SELECT * FROM EMP WHERE EMPNO = 1 and DEPTNO = 2;

// Invalid. Query does not filter on must-filter field 'DEPTNO'.
SELECT * FROM EMP WHERE EMPNO = 1;

// Valid. Query filters on bypass field 'ENAME', and therefore does not
// need to filter on the must-filter field 'DEPTNO'.
SELECT * FROM EMP WHERE EMPNO = 1 and ENAME = ’name’ ;

// Valid. Query filters on bypass field 'ENAME', and therefore does not
// need to filter on any must-filter field.
SELECT * FROM EMP WHERE ENAME = ’name’;

// Valid. Query filters on bypass field 'SALARY', and therefore does not
// need to filter on any must-filter field.
SELECT * FROM EMP WHERE SALARY > 10;
{code}
 
As for must-filter fields, there are special considerations are for handling joins, CTEs and subqueries. We therefore include a comprehensive suite of tests, as we did for CALCITE-6219.

  was:
In CALCITE-6219 we introduced SemanticTable, where tables that implement this interface can define fields to be ‘must-filter’, and a query without those filters in any of its WHERE or HAVING clauses, it will throw a validation error.
 
I would like to extend this functionality to support a by-pass list of fields such that if any field from this secondary list is present in a WHERE / HAVING clause, then the must-filter fields can be ignored and will not raise an exception if not filtered on. 
 
Ex.
 
EMP table specifies the following:
Must-filter-fields: [EMPNO, DEPTNO]
Bypass-fields: [ENAME, SALARY]
 
 
SELECT * FROM EMP WHERE EMPNO = 1 and DEPTNO = 2 -> No error
SELECT * FROM EMP WHERE EMPNO = 1 -> Error
SELECT * FROM EMP WHERE EMPNO = 1 and ENAME = ’name’ -> No error
SELECT * FROM EMP WHERE ENAME = ’name’ -> No error

SELECT * FROM EMP WHERE SALARY > 10 -> No error


 
 
 
Again, special considerations are for handling 
 
 * Joins
 * CTEs
 * Subqueries

 
 
And a similar exhaustive suite of tests like the one for CALCITE-6219 should be employed


> Extend ‘Must-filter’ columns to support a conditional bypass list
> -----------------------------------------------------------------
>
>                 Key: CALCITE-6301
>                 URL: https://issues.apache.org/jira/browse/CALCITE-6301
>             Project: Calcite
>          Issue Type: Improvement
>            Reporter: Oliver Lee
>            Assignee: Oliver Lee
>            Priority: Major
>              Labels: pull-request-available
>
> In CALCITE-6219 we introduced {{{}interface SemanticTable{}}}. A table that implements this interface can define fields to be ‘must-filter’; a query that does not filter those fields in its {{WHERE}} or {{HAVING}} clause will throw a validation error.
> In this case, we allow a {{SemanticTable}} to have a list of bypass fields. If a query filters on any bypass field in its {{WHERE}} or {{HAVING}} clause, then the must-filter fields are ignored. 
> For example, the {{EMP}} table specifies must-filter-fields {{EMPNO}} and {{{}DEPTNO{}}},
> and bypass-fields {{ENAME}} and {{{}SALARY{}}}. Consider the following queries on {{{}EMP{}}}.
> {code:java}
> // Valid. Query filters on both must-filter fields.
> SELECT * FROM EMP WHERE EMPNO = 1 and DEPTNO = 2;
> // Invalid. Query does not filter on must-filter field 'DEPTNO'.
> SELECT * FROM EMP WHERE EMPNO = 1;
> // Valid. Query filters on bypass field 'ENAME', and therefore does not
> // need to filter on the must-filter field 'DEPTNO'.
> SELECT * FROM EMP WHERE EMPNO = 1 and ENAME = ’name’ ;
> // Valid. Query filters on bypass field 'ENAME', and therefore does not
> // need to filter on any must-filter field.
> SELECT * FROM EMP WHERE ENAME = ’name’;
> // Valid. Query filters on bypass field 'SALARY', and therefore does not
> // need to filter on any must-filter field.
> SELECT * FROM EMP WHERE SALARY > 10;
> {code}
>  
> As for must-filter fields, there are special considerations are for handling joins, CTEs and subqueries. We therefore include a comprehensive suite of tests, as we did for CALCITE-6219.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)