Details
-
Improvement
-
Status: Open
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
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
Attachments
Issue Links
- links to