[
https://issues.apache.org/jira/browse/CALCITE-7838?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18124217#comment-18124217
]
Sean Broeder commented on CALCITE-7838:
---------------------------------------
To be clear, if supportsNestedAggregations() returns the default true,
SELECT SUM(m)
FROM (
SELECT CAST(SUM(sal) AS DOUBLE) AS m
FROM scott.emp
GROUP BY deptno
);
is rewritten to
SELECT SUM(CAST(SUM("SAL") AS DOUBLE))
FROM "SCOTT"."EMP"
GROUP BY "DEPTNO"
and this causes HSQLDB to throw
"java.sql.SQLSyntaxErrorException: user lacks privilege or object not found:
SAL (org.hsqldb.HsqlException, raised from
ExpressionColumn.checkColumnsResolved while compiling the statement)"
> HsqldbSqlDialect incorrectly claims to support nested aggregations
> ------------------------------------------------------------------
>
> Key: CALCITE-7838
> URL: https://issues.apache.org/jira/browse/CALCITE-7838
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.42.0
> Reporter: Sean Broeder
> Assignee: Sean Broeder
> Priority: Minor
> Labels: pull-request-available
> Fix For: 1.43.0
>
>
> HsqldbSqlDialect incorrectly uses the default true behavior for
> supportsNestedAggregations(), so an aggregate over an expression
> of another aggregate gets pushed down by JDBC adapter.
> HSQLDB itself rejects the pushed down nested aggregation by
> throwing an exception.
> Can be demonstrated with:
> SELECT SUM(m)
> FROM (
> SELECT CAST(SUM(sal) AS DOUBLE) AS m
> FROM scott.emp
> GROUP BY deptno
> );
> This returns
> "java.sql.SQLSyntaxErrorException: user lacks privilege or object not found:
> SAL (org.hsqldb.HsqlException, raised from
> ExpressionColumn.checkColumnsResolved while compiling the statement)"
> The dialect should report that it doesn't support nested aggregations so that
> a wrapped sub-SELECT is generated. Override supportsNestedAggregations() to
> return false so that the
> converter generates a sub-SELECT, similar to how MySQL and
> PostgreSQL are already handled.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
