[CALCITE-6220] Rewrite MIN/MAX(bool) as BOOL_AND/BOOL_OR for Postgres, Redshift
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 21be392..92f8ec7 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.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 @@
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 a013c86..03602a5 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.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 @@
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) {
@@ -873,6 +875,36 @@
}
/**
+ * 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 9dd4193..7071286 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 @@
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 4e94977..3ad2f0b 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 @@
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 8e4fb1d..e8632f7 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
@@ -6684,6 +6684,37 @@
}
/** 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>. */
@Test void testSnowflakeStartsWith() {
