You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by li...@apache.org on 2022/03/04 13:24:28 UTC
[calcite] 24/41: [CALCITE-4997] Keep APPROX_COUNT_DISTINCT in some SqlDialects
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 <ji...@foxmail.com>
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"