[CALCITE-6306] JDBC adapter should not generate FILTER (WHERE) in MySQL and StarRocks dialect

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Fix Version/s: 1.37.0
• Labels: pull-request-available
• Assignee: hongyu guo
• Reporter: hongyu guo
• Created: 07/Mar/24 07:45
• Updated: 06/May/24 22:19
• Resolved: 11/Mar/24 03:49

Description

mysql> select sum(x) filter (where x = 1) from t;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(where x = 1) from t' at line 1 

See details in https://modern-sql.com/feature/filter

Calcite should not generate the agg() FILTER clause for the MySQL dialect. CALCITE-4321 introduced a way to convert the FILTER clause to an aggregation function with CASE.

Issue Links

• depends upon: CALCITE-4321 JDBC adapter omits FILTER (WHERE ...) expressions when generating SQL
• links to: GitHub Pull Request #3717