This is an automated email from the ASF dual-hosted git repository.
xiong 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 d1a12bb6f4 [CALCITE-5711] Implement the SINGLE_VALUE aggregation in
PostgreSQL Dialect
d1a12bb6f4 is described below
commit d1a12bb6f4d4f617234dfbc32cb0813c473b0029
Author: nobigo <nobigo...@gmail.com>
AuthorDate: Tue May 23 10:30:15 2023 +0800
[CALCITE-5711] Implement the SINGLE_VALUE aggregation in PostgreSQL Dialect
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 3 +-
.../java/org/apache/calcite/sql/SqlDialect.java | 2 +-
.../calcite/sql/dialect/HsqldbSqlDialect.java | 3 +-
.../calcite/sql/dialect/MysqlSqlDialect.java | 2 +-
.../calcite/sql/dialect/PostgresqlSqlDialect.java | 40 ++++++++++++++++++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 27 +++++++++++++++
6 files changed, 73 insertions(+), 4 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
index 3dd420ea27..5853ddd414 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
@@ -570,8 +570,9 @@ public class RelToSqlConverter extends SqlImplementor
List<SqlNode> selectList, List<SqlNode> groupByList) {
for (AggregateCall aggCall : e.getAggCallList()) {
SqlNode aggCallSqlNode = builder.context.toSql(aggCall);
+ RelDataType aggCallRelDataType = aggCall.getType();
if (aggCall.getAggregation() instanceof SqlSingleValueAggFunction) {
- aggCallSqlNode = dialect.rewriteSingleValueExpr(aggCallSqlNode);
+ aggCallSqlNode = dialect.rewriteSingleValueExpr(aggCallSqlNode,
aggCallRelDataType);
}
addSelect(selectList, aggCallSqlNode, e.getRowType());
}
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 31e96bf943..53a57eab94 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -853,7 +853,7 @@ public class SqlDialect {
/** Rewrite SINGLE_VALUE into expression based on database variants
* E.g. HSQLDB, MYSQL, ORACLE, etc.
*/
- public SqlNode rewriteSingleValueExpr(SqlNode aggCall) {
+ public SqlNode rewriteSingleValueExpr(SqlNode aggCall, RelDataType
relDataType) {
LOGGER.debug("SINGLE_VALUE rewrite not supported for {}", databaseProduct);
return aggCall;
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/HsqldbSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/HsqldbSqlDialect.java
index b1c8fc5be6..30f4436d03 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/HsqldbSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/HsqldbSqlDialect.java
@@ -17,6 +17,7 @@
package org.apache.calcite.sql.dialect;
import org.apache.calcite.avatica.util.TimeUnitRange;
+import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlDialect;
@@ -86,7 +87,7 @@ public class HsqldbSqlDialect extends SqlDialect {
unparseFetchUsingLimit(writer, offset, fetch);
}
- @Override public SqlNode rewriteSingleValueExpr(SqlNode aggCall) {
+ @Override public SqlNode rewriteSingleValueExpr(SqlNode aggCall, RelDataType
relDataType) {
final SqlNode operand = ((SqlBasicCall) aggCall).operand(0);
final SqlLiteral nullLiteral = SqlLiteral.createNull(SqlParserPos.ZERO);
final SqlNode unionOperand =
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 041bfd865a..b303641b15 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
@@ -184,7 +184,7 @@ public class MysqlSqlDialect extends SqlDialect {
return super.getCastSpec(type);
}
- @Override public SqlNode rewriteSingleValueExpr(SqlNode aggCall) {
+ @Override public SqlNode rewriteSingleValueExpr(SqlNode aggCall, RelDataType
relDataType) {
final SqlNode operand = ((SqlBasicCall) aggCall).operand(0);
final SqlLiteral nullLiteral = SqlLiteral.createNull(SqlParserPos.ZERO);
final SqlNode unionOperand =
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
index ac2307a014..9dd41938a1 100644
---
a/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
+++
b/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
@@ -22,14 +22,19 @@ import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeSystem;
import org.apache.calcite.rel.type.RelDataTypeSystemImpl;
import org.apache.calcite.sql.SqlAlienSystemTypeNameSpec;
+import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlDataTypeSpec;
import org.apache.calcite.sql.SqlDialect;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlOperator;
+import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.SqlWriter;
+import org.apache.calcite.sql.fun.SqlCase;
import org.apache.calcite.sql.fun.SqlFloorFunction;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.SqlTypeName;
@@ -37,6 +42,8 @@ import org.checkerframework.checker.nullness.qual.Nullable;
import java.util.List;
+import static org.apache.calcite.linq4j.Nullness.castNonNull;
+
/**
* A <code>SqlDialect</code> implementation for the PostgreSQL database.
*/
@@ -97,6 +104,39 @@ public class PostgresqlSqlDialect extends SqlDialect {
SqlParserPos.ZERO);
}
+ @Override public SqlNode rewriteSingleValueExpr(SqlNode aggCall, RelDataType
relDataType) {
+ final SqlNode operand = ((SqlBasicCall) aggCall).operand(0);
+ final SqlLiteral nullLiteral = SqlLiteral.createNull(SqlParserPos.ZERO);
+ final SqlNode unionOperand =
+ new SqlSelect(SqlParserPos.ZERO, SqlNodeList.EMPTY,
+ SqlNodeList.of(
+ SqlStdOperatorTable.CAST.createCall(SqlParserPos.ZERO,
SqlNodeList.of(nullLiteral),
+ SqlNodeList.of(castNonNull(getCastSpec(relDataType))))),
null, null, null, null,
+ SqlNodeList.EMPTY, null, null, null, null, SqlNodeList.EMPTY);
+ // For PostgreSQL, generate
+ // CASE COUNT(value)
+ // WHEN 0 THEN NULL
+ // WHEN 1 THEN min(value)
+ // ELSE (SELECT CAST(NULL AS valueDataType) UNION ALL SELECT CAST(NULL
AS valueDataType))
+ // END
+ final SqlNode caseExpr =
+ new SqlCase(SqlParserPos.ZERO,
+ SqlStdOperatorTable.COUNT.createCall(SqlParserPos.ZERO, operand),
+ SqlNodeList.of(
+ SqlLiteral.createExactNumeric("0", SqlParserPos.ZERO),
+ SqlLiteral.createExactNumeric("1", SqlParserPos.ZERO)),
+ SqlNodeList.of(
+ nullLiteral,
+ SqlStdOperatorTable.MIN.createCall(SqlParserPos.ZERO,
operand)),
+ SqlStdOperatorTable.SCALAR_QUERY.createCall(SqlParserPos.ZERO,
+ SqlStdOperatorTable.UNION_ALL
+ .createCall(SqlParserPos.ZERO, unionOperand,
unionOperand)));
+
+ LOGGER.debug("SINGLE_VALUE rewritten into [{}]", caseExpr);
+
+ return caseExpr;
+ }
+
@Override public boolean supportsFunction(SqlOperator operator,
RelDataType type, final List<RelDataType> paramTypes) {
switch (operator.kind) {
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 2904e144ac..8e57e64c9e 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
@@ -4647,6 +4647,33 @@ class RelToSqlConverterTest {
sql(query).withConfig(c -> c.withExpand(false)).ok(expected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5711";>[CALCITE-5711]
+ * Implement the SINGLE_VALUE aggregation in PostgreSQL Dialect</a>. */
+ @Test void testSubQueryWithSingleValue() {
+ final String query = "select \"product_class_id\" as c\n"
+ + "from \"product\" where \"net_weight\" > (select
\"product_class_id\" from \"product\")";
+ final String expectedMysql = "SELECT `product`.`product_class_id` AS `C`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "LEFT JOIN (SELECT CASE COUNT(`product_class_id`) "
+ + "WHEN 0 THEN NULL WHEN 1 THEN `product_class_id` ELSE (SELECT NULL\n"
+ + "UNION ALL\n"
+ + "SELECT NULL) END AS `$f0`\n"
+ + "FROM `foodmart`.`product`) AS `t0` ON TRUE\n"
+ + "WHERE `product`.`net_weight` > `t0`.`$f0`";
+ final String expectedPostgresql = "SELECT \"product\".\"product_class_id\"
AS \"C\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "LEFT JOIN (SELECT CASE COUNT(\"product_class_id\") WHEN 0 THEN NULL
WHEN 1 THEN MIN(\"product_class_id\") ELSE (SELECT CAST(NULL AS INTEGER)\n"
+ + "UNION ALL\n"
+ + "SELECT CAST(NULL AS INTEGER)) END AS \"$f0\"\n"
+ + "FROM \"foodmart\".\"product\") AS \"t0\" ON TRUE\n"
+ + "WHERE \"product\".\"net_weight\" > \"t0\".\"$f0\"";
+ sql(query)
+ .withConfig(c -> c.withExpand(true))
+ .withMysql().ok(expectedMysql)
+ .withPostgresql().ok(expectedPostgresql);
+ }
+
@Test void testLike() {
String query = "select \"product_name\" from \"product\" a "
+ "where \"product_name\" like 'abc'";
