[
https://issues.apache.org/jira/browse/CALCITE-4321?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17210522#comment-17210522
]
Julian Hyde commented on CALCITE-4321:
--------------------------------------
First, I'd add a boolean method to {{SqlDialect}} that says whether it supports
FILTER. Then I'd make {{JdbcAggregate}}'s constructor throw if you give it a
filtered {{AggregateCall}} in a dialect that doesn't support filters. Last, I'd
make {{SqlImplementor}} smarter when it converts an {{Aggregate}} to a
{{JdbcAggregate}}; it could try to convert, say
{code:java}
sum(x) filter(where b){code}
to
{code:java}
sum(case when b then x end){code}
(That rewrite only works for aggregate functions that ignore nulls, but let's
pretend that's all of them, for now.)
> JDBC adapter omits FILTER (WHERE ...) expressions when generating SQL
> ---------------------------------------------------------------------
>
> Key: CALCITE-4321
> URL: https://issues.apache.org/jira/browse/CALCITE-4321
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
> Environment: MacOS 10.15, Java 11, PostgreSQL 12
> Reporter: Jeremiah Rhoads Hall
> Assignee: Julian Hyde
> Priority: Major
> Fix For: 1.27.0
>
>
> I tried out the PIVOT feature added in CALCITE-3752 and found what I think is
> a bug where the JDBC adapter will not generate `FILTER (WHERE ...)`
> expressions from relational expressions.
> Initially, I noticed that the SQL that was generated when trying out the
> pivot featue was missing the filter-where, but even when included directly in
> the Calcite SQL it's not in the SQL issued by the JDBC adapter.
> For example if I try to execute the following with Calcite:
> {code}
> select sum(amount) filter (where some_date_column = '2020-10-01') from
> my_table{code}
> The SQL issued by the adapter will be:
> {code}
> select sum(amount) from my_table
> {code}
> I have a small change here with a test case and a fix:
> https://github.com/apache/calcite/pull/2204
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
