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 0d99a2e1d5 [CALCITE-6431] Implement the SINGLE_VALUE aggregation in
HiveSqlDialect And SparkSQLDialect
0d99a2e1d5 is described below
commit 0d99a2e1d5393baf0a8753061287611ba386f075
Author: Xiong Duan <[email protected]>
AuthorDate: Mon Mar 10 18:43:16 2025 +0800
[CALCITE-6431] Implement the SINGLE_VALUE aggregation in HiveSqlDialect And
SparkSQLDialect
---
.../apache/calcite/sql/dialect/HiveSqlDialect.java | 40 +++++++++++++++++++++
.../calcite/sql/dialect/SparkSqlDialect.java | 42 ++++++++++++++++++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 19 ++++++++--
3 files changed, 99 insertions(+), 2 deletions(-)
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 dbc6d81db3..e50229e683 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
@@ -19,18 +19,25 @@
import org.apache.calcite.config.NullCollation;
import org.apache.calcite.rel.type.RelDataType;
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.SqlIdentifier;
+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.SqlSyntax;
import org.apache.calcite.sql.SqlWriter;
+import org.apache.calcite.sql.fun.SqlCase;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.BasicSqlType;
import org.apache.calcite.util.RelToSqlConverterUtil;
+import com.google.common.collect.ImmutableList;
+
import org.checkerframework.checker.nullness.qual.Nullable;
import static
org.apache.calcite.util.RelToSqlConverterUtil.unparseSparkArrayAndMap;
@@ -166,4 +173,37 @@ public HiveSqlDialect(Context context) {
}
return super.getCastSpec(type);
}
+
+ /**
+ * Rewrite SINGLE_VALUE(result).
+ *
+ * <blockquote><pre>
+ * CASE COUNT(*)
+ * WHEN 0 THEN NULL
+ * WHEN 1 THEN MIN(<result>)
+ * ELSE ASSERT_TRUE(false)
+ * </pre></blockquote>
+ *
+ * <pre>ASSERT_TRUE(false) will throw assertion failed exception
+ * when result includes more than one value.</pre>
+ */
+ @Override public SqlNode rewriteSingleValueExpr(SqlNode aggCall, RelDataType
relDataType) {
+ final SqlNode operand = ((SqlBasicCall) aggCall).operand(0);
+ final SqlLiteral nullLiteral = SqlLiteral.createNull(SqlParserPos.ZERO);
+ SqlNodeList sqlNodesList = new SqlNodeList(SqlParserPos.ZERO);
+ sqlNodesList.add(SqlLiteral.createBoolean(false, SqlParserPos.ZERO));
+ final SqlNode caseExpr =
+ new SqlCase(SqlParserPos.ZERO,
+ SqlStdOperatorTable.COUNT.createCall(SqlParserPos.ZERO,
+ ImmutableList.of(SqlIdentifier.STAR)),
+ SqlNodeList.of(
+ SqlLiteral.createExactNumeric("0", SqlParserPos.ZERO),
+ SqlLiteral.createExactNumeric("1", SqlParserPos.ZERO)),
+ SqlNodeList.of(
+ nullLiteral,
+ SqlStdOperatorTable.MIN.createCall(SqlParserPos.ZERO,
operand)),
+
RelToSqlConverterUtil.specialOperatorByName("ASSERT_TRUE").createCall(sqlNodesList));
+ LOGGER.debug("SINGLE_VALUE rewritten into [{}]", caseExpr);
+ return caseExpr;
+ }
}
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 c2daf0ab67..4fa694841c 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
@@ -21,19 +21,26 @@
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.sql.JoinType;
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.SqlIdentifier;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlUtil;
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.BasicSqlType;
import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.util.RelToSqlConverterUtil;
+
+import com.google.common.collect.ImmutableList;
import org.checkerframework.checker.nullness.qual.Nullable;
@@ -152,4 +159,39 @@ public SparkSqlDialect(SqlDialect.Context context) {
}
return super.getCastSpec(type);
}
+
+ /**
+ * Rewrite SINGLE_VALUE(result).
+ *
+ * <blockquote><pre>
+ * CASE COUNT(*)
+ * WHEN 0 THEN NULL
+ * WHEN 1 THEN MIN(<result>)
+ * ELSE RAISE_ERROR("more than one value in agg SINGLE_VALUE")
+ * </pre></blockquote>
+ *
+ * <pre>RAISE_ERROR("more than one value in agg SINGLE_VALUE") will throw
exception
+ * when result includes more than one value.</pre>
+ */
+ @Override public SqlNode rewriteSingleValueExpr(SqlNode aggCall, RelDataType
relDataType) {
+ final SqlNode operand = ((SqlBasicCall) aggCall).operand(0);
+ final SqlLiteral nullLiteral = SqlLiteral.createNull(SqlParserPos.ZERO);
+ SqlNodeList sqlNodesList = new SqlNodeList(SqlParserPos.ZERO);
+ sqlNodesList.add(
+ SqlLiteral.createCharString("more than one value in agg SINGLE_VALUE",
SqlParserPos.ZERO));
+ final SqlNode caseExpr =
+ new SqlCase(SqlParserPos.ZERO,
+ SqlStdOperatorTable.COUNT.createCall(SqlParserPos.ZERO,
+ ImmutableList.of(SqlIdentifier.STAR)),
+ SqlNodeList.of(
+ SqlLiteral.createExactNumeric("0", SqlParserPos.ZERO),
+ SqlLiteral.createExactNumeric("1", SqlParserPos.ZERO)),
+ SqlNodeList.of(
+ nullLiteral,
+ SqlStdOperatorTable.MIN.createCall(SqlParserPos.ZERO,
operand)),
+
RelToSqlConverterUtil.specialOperatorByName("RAISE_ERROR").createCall(sqlNodesList));
+ LOGGER.debug("SINGLE_VALUE rewritten into [{}]", caseExpr);
+ return caseExpr;
+ }
+
}
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 f779e24a24..a4a3c802fd 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
@@ -5596,7 +5596,10 @@ private void checkLiteral2(String expression, String
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>. */
+ * Implement the SINGLE_VALUE aggregation in PostgreSQL Dialect</a>
+ * and
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6431";>[CALCITE-6431]
+ * Implement the SINGLE_VALUE aggregation in HiveSqlDialect And
SparkSQLDialect</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\")";
@@ -5622,11 +5625,23 @@ private void checkLiteral2(String expression, String
expected) {
+ "(VALUES 0E0)) END AS $f0\n"
+ "FROM foodmart.product) AS t0 ON TRUE\n"
+ "WHERE product.net_weight > CAST(t0.$f0 AS DOUBLE)";
+ final String expectedSpark = "SELECT `product`.`product_class_id` `C`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "LEFT JOIN (SELECT CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN
MIN(`product_class_id`) ELSE RAISE_ERROR('more than one value in agg
SINGLE_VALUE') END `$f0`\n"
+ + "FROM `foodmart`.`product`) `t0` ON TRUE\n"
+ + "WHERE `product`.`net_weight` > CAST(`t0`.`$f0` AS DOUBLE)";
+ final String expectedHive = "SELECT `product`.`product_class_id` `C`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "LEFT JOIN (SELECT CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN
MIN(`product_class_id`) ELSE ASSERT_TRUE(FALSE) END `$f0`\n"
+ + "FROM `foodmart`.`product`) `t0` ON TRUE\n"
+ + "WHERE `product`.`net_weight` > CAST(`t0`.`$f0` AS DOUBLE)";
sql(query)
.withConfig(c -> c.withExpand(true))
.withMysql().ok(expectedMysql)
.withPostgresql().ok(expectedPostgresql)
- .withHsqldb().ok(expectedHsqldb);
+ .withHsqldb().ok(expectedHsqldb)
+ .withSpark().ok(expectedSpark)
+ .withHive().ok(expectedHive);
}
@Test void testLike() {
