[
https://issues.apache.org/jira/browse/CALCITE-5303?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17611769#comment-17611769
]
Julian Hyde commented on CALCITE-5303:
--------------------------------------
Another approach would be for only built-in aggregate functions ({{SUM}},
{{COUNT}}, {{MIN}}, {{MAX}} etc.) to force a query into aggregate mode.
In aggregate mode, if you use an expression not in the GROUP BY and not in an
aggregate function, you get an error.
In non-aggregate mode, if you use an aggregate function, you get an error. Thus:
{code:java}
# Legal, and in aggregate mode
SELECT SUM(deptno)
FROM emp;
# Illegal, use of non-grouped 'empno' in aggregate mode
SELECT SUM(deptno), empno
FROM emp;
# Legal, in aggregate mode (due to SUM), therefore ST_Union allowed
SELECT SUM(deptno), ST_Union(homeLocation)
FROM emp;
# Illegal, cannot use ST_Union in non-aggregate mode query
SELECT ST_Union(homeLocation)
FROM emp;
# Legal, in aggregate mode due to GROUP BY, therefore ST_Union allowed
SELECT ST_Union(homeLocation)
FROM emp
GROUP BY deptno;
# Legal, in aggregate mode due to GROUP BY, therefore ST_Union allowed SELECT
ST_Union(homeLocation)
FROM emp
GROUP BY ();
# Legal, in aggregate mode due to GROUP BY, therefore ST_Union allowed SELECT
ST_Union(homeLocation)
FROM emp
GROUP BY ();
# Illegal. In aggregate mode due to GROUP BY, therefore ST_Union allowed,
# but illegal due to use of non-grouped 'empno'.
SELECT ST_Union(homeLocation), empno
FROM emp
GROUP BY ();
{code}
Before we do this, we should also study the behavior of other DBMS with respect
to overloaded aggregate functions and aggregate mode.
There isn't a reliable cross-dialect way to force queries into aggregate mode.
I know that in Oracle "{{SELECT COUNT\(\*) FROM t}}" has slightly different
behavior than "{{SELECT COUNT\(\*) FROM t GROUP BY ()}}" (there are differences
if {{t}} is empty). And I also know that in BigQuery, adding a {{HAVING}}
clause gives an error rather than forcing a query into aggregate mode.
> Add support for overloaded aggregate functions
> ----------------------------------------------
>
> Key: CALCITE-5303
> URL: https://issues.apache.org/jira/browse/CALCITE-5303
> Project: Calcite
> Issue Type: New Feature
> Reporter: Bertil Chapuis
> Priority: Major
>
> The idea would be to make the validation and function resolution process more
> robust in the presence of overloaded aggregate functions. When determining if
> a function is an aggregate, the validator simply checks if one of the
> matching functions is an aggregate. The number of operands, their type, and
> the precedence of scalar function over aggregate functions are not taken into
> account.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
