[CALCITE-4321] JDBC adapter omits FILTER (WHERE ...) expressions when generating SQL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.27.0
• Component/s: jdbc-adapter
• Labels: pull-request-available

Environment

MacOS 10.15, Java 11, PostgreSQL 12

• Assignee: Julian Hyde
• Reporter: Jeremiah Rhoads Hall
• Created: 08/Oct/20 21:20
• Updated: 08/Sep/22 05:53
• Resolved: 18/Oct/20 04:33

Description

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:

select sum(amount) filter (where some_date_column = '2020-10-01') from my_table

The SQL issued by the adapter will be:

select sum(amount) from my_table

I have a small change here with a test case and a fix: https://github.com/apache/calcite/pull/2204

Issue Links

• relates to: CALCITE-3752 Add PIVOT operator to SQL
• relates to: CALCITE-5270 JDBC adapter should not generate FILTER (WHERE) in Firebolt dialect
• links to: GitHub Pull Request #2204