This is an automated email from the ASF dual-hosted git repository. liyafan pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 9cecf84db385571507ff974da9edfa168208c1d9 Author: xiejiajun <[email protected]> AuthorDate: Tue Jan 11 10:49:49 2022 +0800 [CALCITE-4997] Keep APPROX_COUNT_DISTINCT in some SqlDialects Some engines(Hive,Spark,BigQuery,Oracle,Snowflake) support APPROX_COUNT_DISTINCT function, while others do not. So we can use the parameter SqlDialect#supportsApproxCountDistinct to control whether to use APPROX_COUNT_DISTINCT(It is the same as APPROX_DISTINCT for Presto). --- .../apache/calcite/rel/rel2sql/SqlImplementor.java | 12 +++++++----- .../main/java/org/apache/calcite/sql/SqlDialect.java | 5 +++++ .../org/apache/calcite/sql/SqlDialectFactoryImpl.java | 6 ++++-- .../calcite/sql/dialect/BigQuerySqlDialect.java | 4 ++++ .../apache/calcite/sql/dialect/HiveSqlDialect.java | 4 ++++ .../apache/calcite/sql/dialect/OracleSqlDialect.java | 4 ++++ .../apache/calcite/sql/dialect/PrestoSqlDialect.java | 7 +++++++ .../calcite/sql/dialect/SnowflakeSqlDialect.java | 4 ++++ .../apache/calcite/sql/dialect/SparkSqlDialect.java | 4 ++++ .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 19 +++++++++++++++++++ 10 files changed, 62 insertions(+), 7 deletions(-) diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java index 9155ab7..1215a76 100644 --- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java +++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java @@ -1151,18 +1151,18 @@ public abstract class SqlImplementor { public SqlNode toSql(AggregateCall aggCall) { return toSql(aggCall.getAggregation(), aggCall.isDistinct(), Util.transform(aggCall.getArgList(), this::field), - aggCall.filterArg, aggCall.collation); + aggCall.filterArg, aggCall.collation, aggCall.isApproximate()); } /** Converts a call to an aggregate function, with a given list of operands, * to an expression. */ private SqlCall toSql(SqlOperator op, boolean distinct, - List<SqlNode> operandList, int filterArg, RelCollation collation) { + List<SqlNode> operandList, int filterArg, RelCollation collation, boolean approximate) { final SqlLiteral qualifier = distinct ? SqlSelectKeyword.DISTINCT.symbol(POS) : null; if (op instanceof SqlSumEmptyIsZeroAggFunction) { final SqlNode node = toSql(SqlStdOperatorTable.SUM, distinct, - operandList, filterArg, collation); + operandList, filterArg, collation, approximate); return SqlStdOperatorTable.COALESCE.createCall(POS, node, ZERO); } @@ -1186,7 +1186,7 @@ public abstract class SqlImplementor { if (operandList.size() > 1) { newOperandList.addAll(Util.skip(operandList)); } - return toSql(op, distinct, newOperandList, -1, collation); + return toSql(op, distinct, newOperandList, -1, collation, approximate); } if (op instanceof SqlCountAggFunction && operandList.isEmpty()) { @@ -1199,7 +1199,9 @@ public abstract class SqlImplementor { // Handle filter by generating FILTER (WHERE ...) final SqlCall call2; - if (filterArg < 0) { + if (distinct && approximate && dialect.supportsApproxCountDistinct()) { + call2 = SqlStdOperatorTable.APPROX_COUNT_DISTINCT.createCall(POS, operandList); + } else if (filterArg < 0) { call2 = call; } else { assert dialect.supportsAggregateFunctionFilter(); // we checked above diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java index 26a998b..a3801c8 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java @@ -745,6 +745,11 @@ public class SqlDialect { return false; } + /** Returns whether this dialect supports APPROX_COUNT_DISTINCT functions. */ + public boolean supportsApproxCountDistinct() { + return false; + } + /** Returns whether this dialect supports the use of FILTER clauses for * aggregate functions. e.g. {@code COUNT(*) FILTER (WHERE a = 2)}. */ public boolean supportsAggregateFunctionFilter() { diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java b/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java index d527e4d..e94a6c8 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java @@ -213,14 +213,16 @@ public class SqlDialectFactoryImpl implements SqlDialectFactory { return PrestoSqlDialect.DEFAULT; case REDSHIFT: return RedshiftSqlDialect.DEFAULT; + case SNOWFLAKE: + return SnowflakeSqlDialect.DEFAULT; + case SPARK: + return SparkSqlDialect.DEFAULT; case SYBASE: return SybaseSqlDialect.DEFAULT; case TERADATA: return TeradataSqlDialect.DEFAULT; case VERTICA: return VerticaSqlDialect.DEFAULT; - case SPARK: - return SparkSqlDialect.DEFAULT; case SQLSTREAM: case UNKNOWN: default: diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java index baeec1e..df38d9c 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java @@ -117,6 +117,10 @@ public class BigQuerySqlDialect extends SqlDialect { && !SqlTypeUtil.isNumeric(call.type); } + @Override public boolean supportsApproxCountDistinct() { + return true; + } + @Override public boolean supportsNestedAggregations() { return false; } diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java index 3aba1e5..21fcbdf 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java @@ -131,6 +131,10 @@ public class HiveSqlDialect extends SqlDialect { return true; } + @Override public boolean supportsApproxCountDistinct() { + return true; + } + @Override public boolean supportsNestedAggregations() { return false; } diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java index b98b284..1865d51 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java @@ -74,6 +74,10 @@ public class OracleSqlDialect extends SqlDialect { super(context); } + @Override public boolean supportsApproxCountDistinct() { + return true; + } + @Override public boolean supportsCharSet() { return false; } diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/PrestoSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/PrestoSqlDialect.java index a2836a6..75b232e 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/PrestoSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/PrestoSqlDialect.java @@ -52,6 +52,10 @@ public class PrestoSqlDialect extends SqlDialect { super(context); } + @Override public boolean supportsApproxCountDistinct() { + return true; + } + @Override public boolean supportsCharSet() { return false; } @@ -119,6 +123,9 @@ public class PrestoSqlDialect extends SqlDialect { if (call.getOperator() == SqlStdOperatorTable.SUBSTRING) { RelToSqlConverterUtil.specialOperatorByName("SUBSTR") .unparse(writer, call, 0, 0); + } else if (call.getOperator() == SqlStdOperatorTable.APPROX_COUNT_DISTINCT) { + RelToSqlConverterUtil.specialOperatorByName("APPROX_DISTINCT") + .unparse(writer, call, 0, 0); } else { // Current impl is same with Postgresql. PostgresqlSqlDialect.DEFAULT.unparseCall(writer, call, leftPrec, rightPrec); diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/SnowflakeSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/SnowflakeSqlDialect.java index 7d07f9c..b12e647 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/SnowflakeSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/SnowflakeSqlDialect.java @@ -35,4 +35,8 @@ public class SnowflakeSqlDialect extends SqlDialect { public SnowflakeSqlDialect(Context context) { super(context); } + + @Override public boolean supportsApproxCountDistinct() { + return true; + } } diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java index c53bf3a..2bee312 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java @@ -78,6 +78,10 @@ public class SparkSqlDialect extends SqlDialect { return false; } + @Override public boolean supportsApproxCountDistinct() { + return true; + } + @Override public boolean supportsGroupByWithCube() { return true; } 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 8bc7c82..0523615 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 @@ -6069,6 +6069,25 @@ class RelToSqlConverterTest { sql.ok(expected); } + @Test void testSelectApproxCountDistinct() { + final String query = "select approx_count_distinct(\"product_id\") from \"product\""; + final String expectedExact = "SELECT COUNT(DISTINCT \"product_id\")\n" + + "FROM \"foodmart\".\"product\""; + final String expectedApprox = "SELECT APPROX_COUNT_DISTINCT(product_id)\n" + + "FROM foodmart.product"; + final String expectedApproxQuota = "SELECT APPROX_COUNT_DISTINCT(\"product_id\")\n" + + "FROM \"foodmart\".\"product\""; + final String expectedPrestoSql = "SELECT APPROX_DISTINCT(\"product_id\")\n" + + "FROM \"foodmart\".\"product\""; + sql(query).ok(expectedExact) + .withHive().ok(expectedApprox) + .withSpark().ok(expectedApprox) + .withBigQuery().ok(expectedApprox) + .withOracle().ok(expectedApproxQuota) + .withSnowflake().ok(expectedApproxQuota) + .withPresto().ok(expectedPrestoSql); + } + @Test void testRowValueExpression() { String sql = "insert into \"DEPT\"\n" + "values ROW(1,'Fred', 'San Francisco'),\n"
