[
https://issues.apache.org/jira/browse/CALCITE-7838?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sean Broeder updated CALCITE-7838:
----------------------------------
Description:
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.
was:
For an aggregate over an expression containing another aggregate, the JDBC
adapter pushes down a nested aggregate function, but HSQLDB rejects it 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.
> 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)
