[jira] [Updated] (CALCITE-5692) Add AT operator, for context-sensitive expressions

["Julian Hyde (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-5692?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde updated CALCITE-5692:
---------------------------------
    Description: 
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 a3
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".

{{AT}} can be applied to any expression (not just aggregate functions like {{FILTER}}). For example,
 * {{avg_sal AT (CLEAR deptno)}} // applied to a measure
 * {{(COUNT(comm) + 4) AT (CLEAR deptno)}} // applied to an aggregateable expression

  was:
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".



> 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
>            Priority: Major
>
> 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 a3
> 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".
> {{AT}} can be applied to any expression (not just aggregate functions like {{FILTER}}). For example,
>  * {{avg_sal AT (CLEAR deptno)}} // applied to a measure
>  * {{(COUNT(comm) + 4) AT (CLEAR deptno)}} // applied to an aggregateable expression



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