Julian Hyde created CALCITE-5692: ------------------------------------ Summary: Add AT operator, for context-sensitive expressions Key: CALCITE-5692 URL: https://issues.apache.org/jira/browse/CALCITE-5692 Project: Calcite Issue Type: Bug Reporter: Julian Hyde
In CALCITE-4496 we added measures, and the ability to evaluate a measure in a context determined by the GROUP BY clause. In this change, we add the ability to change that context using the AT operator. When used as a clause of an aggregate function call, {{AT}} is similar to {{FILTER}}. For example, in the query {code} SELECT deptno, AVG(sal) FILTER (WHERE job <> 'MANAGER') AS a1, AVG(sal) AT (VISIBLE WHERE job <> 'MANAGER') AS a2, AVG(sal) AT (WHERE job <> 'MANAGER') AS a2 FROM emp WHERE sal > 1000 GROUP BY deptno; {code} {{a1}} and {{a2}} always return the same value - the average salary of non-managers who earn more than $1000. But {{a3}} (similar to {{a2}} but missing the {{VISIBLE}} keyword) computes the average of all non-managers (not just those earning more than $1000). Sub-clauses of {{AT}}: * {{VISIBLE}} adds a filter equivalent to the {{WHERE}} clause (has no effect when used in the {{WHERE}} or {{ON}} clauses); * {{WHERE}} adds the given predicate as a filter; * {{CLEAR GROUP}} clears the predicate of the current group, if any (e.g. "deptno = 10 AND job = 'ANALYST'" if we are in the SELECT clause of a query with "GROUP BY deptno, job" evaluating the row (10, 'ANALYST'); * {{CLEAR WHERE}} clears the predicate of the {{WHERE}} clause (has no effect when used in the {{WHERE}} or {{ON}} clauses); * {{SET dimension = expression}} clears the predicate on {{dimension}} (if any) and adds a filter "dimension = expression". -- This message was sent by Atlassian Jira (v8.20.10#820010)