[
https://issues.apache.org/jira/browse/CALCITE-7838?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18123883#comment-18123883
]
Julian Hyde commented on CALCITE-7838:
--------------------------------------
It would be helpful if you show the SQL query that Calcite has generated and
that HSQLDB rejects, since this is what contains the “nested aggregates”.
Since Calcite’s own SQL dialect supports nested aggregates I presume that a
nested aggregate query would also reproduce this problem.
> 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: Major
> Labels: pull-request-available
>
> 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)
