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

["Alessandro Solimando (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-6301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17823890#comment-17823890 ] 

Alessandro Solimando commented on CALCITE-6301:
-----------------------------------------------

Isn't this equivalent to say that "EMPNAME" is an alternative to the "Must-filter-fields"?

In that case I think it's simpler to extend the syntax to provide multiple "Must-filter-fields" lists, something like "[EMPNO, DEPTNO],[ENAME]" (a list of lists, conceptually, where each list is an independent alternative).

From your example it's not clear if multiple by-pass fields values would require to be there at the same time (very much like "Must-filter-fields"), or if any of them alone would do.

For instance, for Bypass-fields: [ENAME, EMPNO], would the following queries be valid or not?

SELECT * FROM EMP WHERE ENAME = ’name’
SELECT * FROM EMP WHERE EMPNO = 1 

In any case, the multiple "Must-filter-fields" syntax would allow to express both pretty naturally.

You can even probably re-use the same machinery you coded for "Must-filter-fields" more easily.

WDYT?

> 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
>
> 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]
>  
>  
> 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
>  
>  
>  
> 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



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