This is an automated email from the ASF dual-hosted git repository.
tanner 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 26b5e9be6a [CALCITE-6220] Rewrite MIN/MAX(bool) as BOOL_AND/BOOL_OR
for Postgres, Redshift
26b5e9be6a is described below
commit 26b5e9be6af1be61f4665c75e913b0d1f3459dfd
Author: Tanner Clary <[email protected]>
AuthorDate: Mon Jan 22 16:22:33 2024 -0800
[CALCITE-6220] Rewrite MIN/MAX(bool) as BOOL_AND/BOOL_OR for Postgres,
Redshift
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 3 ++
.../java/org/apache/calcite/sql/SqlDialect.java | 34 +++++++++++++++++++++-
.../calcite/sql/dialect/PostgresqlSqlDialect.java | 5 +++-
.../calcite/sql/dialect/RedshiftSqlDialect.java | 4 +++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 31 ++++++++++++++++++++
5 files changed, 75 insertions(+), 2 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 21be392f8f..92f8ec7cb3 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
@@ -81,6 +81,7 @@ import org.apache.calcite.sql.SqlTableRef;
import org.apache.calcite.sql.SqlUpdate;
import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.fun.SqlInternalOperators;
+import org.apache.calcite.sql.fun.SqlMinMaxAggFunction;
import org.apache.calcite.sql.fun.SqlSingleValueAggFunction;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
@@ -577,6 +578,8 @@ public class RelToSqlConverter extends SqlImplementor
RelDataType aggCallRelDataType = aggCall.getType();
if (aggCall.getAggregation() instanceof SqlSingleValueAggFunction) {
aggCallSqlNode = dialect.rewriteSingleValueExpr(aggCallSqlNode,
aggCallRelDataType);
+ } else if (aggCall.getAggregation() instanceof SqlMinMaxAggFunction) {
+ aggCallSqlNode = dialect.rewriteMaxMinExpr(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 a013c861e5..03602a5486 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -31,10 +31,12 @@ import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.sql.dialect.JethroDataSqlDialect;
import org.apache.calcite.sql.fun.SqlInternalOperators;
+import org.apache.calcite.sql.fun.SqlLibraryOperators;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.AbstractSqlType;
+import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.sql.validate.SqlConformance;
import org.apache.calcite.sql.validate.SqlConformanceEnum;
@@ -864,7 +866,7 @@ public class SqlDialect {
return SqlTypeUtil.convertTypeToSpec(type);
}
- /** Rewrite SINGLE_VALUE into expression based on database variants
+ /** Rewrites SINGLE_VALUE into expression based on database variants
* E.g. HSQLDB, MYSQL, ORACLE, etc.
*/
public SqlNode rewriteSingleValueExpr(SqlNode aggCall, RelDataType
relDataType) {
@@ -872,6 +874,36 @@ public class SqlDialect {
return aggCall;
}
+ /**
+ * Rewrites MAX(x)/MIN(x) as BOOL_OR(x)/BOOL_AND(x) for certain
+ * database variants (Postgres and Redshift, currently).
+ *
+ * @see #rewriteMaxMin(SqlNode, RelDataType)
+ */
+ public SqlNode rewriteMaxMinExpr(SqlNode aggCall, RelDataType relDataType) {
+ return aggCall;
+ }
+
+ /**
+ * Helper for rewrites of MAX/MIN.
+ * Some dialects (e.g. Postgres and Redshift), rewrite as
+ * BOOL_OR/BOOL_AND if the return type is BOOLEAN.
+ */
+ protected static SqlNode rewriteMaxMin(SqlNode aggCall, RelDataType
relDataType) {
+ // The behavior of this method depends on the argument type,
+ // and whether it is MIN/MAX
+ final SqlTypeName type = relDataType.getSqlTypeName();
+ final boolean isMax = aggCall.getKind() == SqlKind.MAX;
+ // If the type is BOOLEAN, create a new call to the correct operator
+ if (type == SqlTypeName.BOOLEAN) {
+ final SqlOperator op = isMax ? SqlLibraryOperators.BOOL_OR :
SqlLibraryOperators.BOOL_AND;
+ final SqlNode operand = ((SqlBasicCall) aggCall).operand(0);
+ return op.createCall(SqlParserPos.ZERO, operand);
+ }
+ // Otherwise, just return as it arrived
+ return aggCall;
+ }
+
/**
* Returns the SqlNode for emulating the null direction for the given field
* or <code>null</code> if no emulation needs to be done.
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 9dd41938a1..7071286c59 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
@@ -173,12 +173,15 @@ public class PostgresqlSqlDialect extends SqlDialect {
timeUnitNode.getParserPosition());
SqlFloorFunction.unparseDatetimeFunction(writer, call2, "DATE_TRUNC",
false);
break;
-
default:
super.unparseCall(writer, call, leftPrec, rightPrec);
}
}
+ @Override public SqlNode rewriteMaxMinExpr(SqlNode aggCall, RelDataType
relDataType) {
+ return rewriteMaxMin(aggCall, relDataType);
+ }
+
@Override public boolean supportsGroupByLiteral() {
return false;
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/RedshiftSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/RedshiftSqlDialect.java
index 4e94977ce3..3ad2f0b91e 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/RedshiftSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/RedshiftSqlDialect.java
@@ -107,6 +107,10 @@ public class RedshiftSqlDialect extends SqlDialect {
SqlParserPos.ZERO);
}
+ @Override public SqlNode rewriteMaxMinExpr(SqlNode aggCall, RelDataType
relDataType) {
+ return rewriteMaxMin(aggCall, relDataType);
+ }
+
@Override public boolean supportsGroupByLiteral() {
return false;
}
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 8e4fb1d082..e8632f7a80 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
@@ -6683,6 +6683,37 @@ class RelToSqlConverterTest {
sql(query).withLibrary(SqlLibrary.SNOWFLAKE).withSnowflake().ok(expectedSnowflake);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6220";>[CALCITE-6220]
+ * Rewrite MIN/MAX(bool) as BOOL_AND/BOOL_OR for Postgres, Redshift</a>. */
+ @Test void testMaxMinOnBooleanColumn() {
+ final String query = "select max(\"brand_name\" = 'a'), "
+ + "min(\"brand_name\" = 'a'), "
+ + "min(\"brand_name\")\n"
+ + "from \"product\"";
+ final String expected = "SELECT MAX(\"brand_name\" = 'a'), "
+ + "MIN(\"brand_name\" = 'a'), "
+ + "MIN(\"brand_name\")\n"
+ + "FROM \"foodmart\".\"product\"";
+ final String expectedBigQuery = "SELECT MAX(brand_name = 'a'), "
+ + "MIN(brand_name = 'a'), "
+ + "MIN(brand_name)\n"
+ + "FROM foodmart.product";
+ final String expectedPostgres = "SELECT BOOL_OR(\"brand_name\" = 'a'), "
+ + "BOOL_AND(\"brand_name\" = 'a'), "
+ + "MIN(\"brand_name\")\n"
+ + "FROM \"foodmart\".\"product\"";
+ final String expectedRedshift = "SELECT BOOL_OR(\"brand_name\" = 'a'), "
+ + "BOOL_AND(\"brand_name\" = 'a'), "
+ + "MIN(\"brand_name\")\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(query)
+ .ok(expected)
+ .withBigQuery().ok(expectedBigQuery)
+ .withPostgresql().ok(expectedPostgres)
+ .withRedshift().ok(expectedPostgres);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-6156";>[CALCITE-6156]
* Add ENDSWITH, STARTSWITH functions (enabled in Postgres, Snowflake
libraries)</a>. */
