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

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Labels: pull-request-available
• Assignee: Oliver Lee
• Reporter: Oliver Lee
• Created: 06/Mar/24 00:49
• Updated: 5 days ago 19:18

Description

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

Issue Links

• is related to: CALCITE-6219 'Must-filter' columns
• links to: GitHub Pull Request #3716
• links to: GitHub Pull Request #3984