[CALCITE-6306] JDBC adapter should not generate FILTER (WHERE) in MySQL and StarRocks dialect
diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
index b303641..1bccbc5 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
@@ -350,6 +350,9 @@
writer.endList(frame);
}
+ @Override public boolean supportsAggregateFunctionFilter() {
+ return false;
+ }
@Override public void unparseSqlIntervalQualifier(SqlWriter writer,
SqlIntervalQualifier qualifier, RelDataTypeSystem typeSystem) {
diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 461ce04..6affbf4 100644
--- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -306,7 +306,10 @@
* JDBC adapter omits FILTER (WHERE ...) expressions when generating SQL</a>
* and
* <a href="https://issues.apache.org/jira/browse/CALCITE-5270">[CALCITE-5270]
- * JDBC adapter should not generate FILTER (WHERE) in Firebolt dialect</a>. */
+ * JDBC adapter should not generate FILTER (WHERE) in Firebolt dialect</a>
+ * and
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-6306">[CALCITE-6306]
+ * JDBC adapter should not generate FILTER (WHERE) in MySQL and StarRocks dialect</a>. */
@Test void testAggregateFilterWhere() {
String query = "select\n"
+ " sum(\"shelf_width\") filter (where \"net_weight\" > 0),\n"
@@ -334,9 +337,23 @@
+ "FROM \"foodmart\".\"product\"\n"
+ "WHERE \"product_id\" > 0\n"
+ "GROUP BY \"product_id\"";
+ final String expectedMysql = "SELECT"
+ + " SUM(CASE WHEN `net_weight` > 0 IS TRUE"
+ + " THEN `shelf_width` ELSE NULL END), SUM(`shelf_width`)\n"
+ + "FROM `foodmart`.`product`\n"
+ + "WHERE `product_id` > 0\n"
+ + "GROUP BY `product_id`";
+ final String expectedStarRocks = "SELECT"
+ + " SUM(CASE WHEN `net_weight` > 0 IS TRUE"
+ + " THEN `shelf_width` ELSE NULL END), SUM(`shelf_width`)\n"
+ + "FROM `foodmart`.`product`\n"
+ + "WHERE `product_id` > 0\n"
+ + "GROUP BY `product_id`";
sql(query).ok(expectedDefault)
.withBigQuery().ok(expectedBigQuery)
- .withFirebolt().ok(expectedFirebolt);
+ .withFirebolt().ok(expectedFirebolt)
+ .withMysql().ok(expectedMysql)
+ .withStarRocks().ok(expectedStarRocks);
}
@Test void testPivotToSqlFromProductTable() {
