[ https://issues.apache.org/jira/browse/CALCITE-6301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17824175#comment-17824175 ]
Oliver Lee commented on CALCITE-6301: ------------------------------------- One example that comes to mind: Let's say you have a table {{ORDERS}} with columns {{order_id, order_date, price, etc}} , and that {{order_id}} is ascending numbers A user might want : [must-filter on order_id > 1000] and bypass the {{order_id}} filter requirement if they filter by {{order_date}} > 2024-01-01 > 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, 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 -- This message was sent by Atlassian Jira (v8.20.10#820010)