(calcite) branch main updated: [CALCITE-6306] JDBC adapter should not generate FILTER (WHERE) in MySQL and StarRocks dialect

[gu...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

guohongyu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git


The following commit(s) were added to refs/heads/main by this push:
     new 2ce3062633 [CALCITE-6306] JDBC adapter should not generate FILTER (WHERE) in MySQL and StarRocks dialect
2ce3062633 is described below

commit 2ce306263327a30255bda858ce437c184546ca9b
Author: Hongyu Guo <ma...@tencent.com>
AuthorDate: Thu Mar 7 15:32:40 2024 +0800

    [CALCITE-6306] JDBC adapter should not generate FILTER (WHERE) in MySQL and StarRocks dialect
---
 .../apache/calcite/sql/dialect/MysqlSqlDialect.java |  3 +++
 .../calcite/rel/rel2sql/RelToSqlConverterTest.java  | 21 +++++++++++++++++++--
 2 files changed, 22 insertions(+), 2 deletions(-)

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 b303641b15..1bccbc552d 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 @@ public class MysqlSqlDialect extends SqlDialect {
     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 461ce045fc..6affbf4a77 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 @@ class RelToSqlConverterTest {
    * 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 @@ class RelToSqlConverterTest {
         + "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() {