[CALCITE-6290] Incorrect return type for BigQuery TRUNC
Adjust nullability for BigQuery's CEIL and FLOOR to correct [CALCITE-5747]
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index bc76a59..0b9deb1 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -278,7 +278,7 @@
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_CODE_POINTS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_HEX;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRANSLATE3;
-import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRUNC;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRUNC_BIG_QUERY;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRY_CAST;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_DATE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_MICROS;
@@ -677,7 +677,7 @@
defineMethod(SINH, BuiltInMethod.SINH.method, NullPolicy.STRICT);
defineMethod(TAN, BuiltInMethod.TAN.method, NullPolicy.STRICT);
defineMethod(TANH, BuiltInMethod.TANH.method, NullPolicy.STRICT);
- defineMethod(TRUNC, BuiltInMethod.STRUNCATE.method, NullPolicy.STRICT);
+ defineMethod(TRUNC_BIG_QUERY, BuiltInMethod.STRUNCATE.method, NullPolicy.STRICT);
defineMethod(TRUNCATE, BuiltInMethod.STRUNCATE.method, NullPolicy.STRICT);
map.put(SAFE_ADD,
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index 86a72d2..f1166fd 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -456,7 +456,7 @@
.withName("CEIL_BIG_QUERY")
.withReturnTypeInference(ReturnTypes.ARG0_EXCEPT_INTEGER_NULLABLE);
- /** The "FLOOR(value)" function. Identical to the stadnard <code>FLOOR</code> function
+ /** The "FLOOR(value)" function. Identical to the standard <code>FLOOR</code> function
* except the return type should be a double if the operand is an integer. */
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlFunction FLOOR_BIG_QUERY = new SqlFloorFunction(SqlKind.FLOOR)
@@ -2161,9 +2161,12 @@
public static final SqlFunction POW =
SqlStdOperatorTable.POWER.withName("POW");
+ /** The "TRUNC(numeric1 [, integer2])" function. Identical to the standard <code>TRUNCATE</code>
+ * function except the return type should be a double if numeric1 is an integer. */
@LibraryOperator(libraries = {BIG_QUERY})
- public static final SqlFunction TRUNC =
- SqlStdOperatorTable.TRUNCATE.withName("TRUNC");
+ public static final SqlFunction TRUNC_BIG_QUERY = SqlStdOperatorTable.TRUNCATE
+ .withName("TRUNC")
+ .withReturnTypeInference(ReturnTypes.ARG0_EXCEPT_INTEGER_NULLABLE);
/** Infix "::" cast operator used by PostgreSQL, for example
* {@code '100'::INTEGER}. */
diff --git a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
index 13e6807..342284d 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
@@ -550,12 +550,12 @@
*/
public static final SqlReturnTypeInference ARG0_EXCEPT_INTEGER = opBinding -> {
RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
- SqlTypeName op = opBinding.getOperandType(0).getSqlTypeName();
- if (SqlTypeName.INT_TYPES.contains(op)) {
+ RelDataType opType = opBinding.getOperandType(0);
+ if (SqlTypeName.INT_TYPES.contains(opType.getSqlTypeName())) {
return typeFactory.createTypeWithNullability(
- typeFactory.createSqlType(SqlTypeName.DOUBLE), true);
+ typeFactory.createSqlType(SqlTypeName.DOUBLE), false);
} else {
- return typeFactory.createTypeWithNullability(typeFactory.createSqlType(op), true);
+ return opType;
}
};
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 1de16cb..461ce04 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
@@ -592,6 +592,20 @@
+ "created_thing\nFROM foodmart.product");
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-6290">[CALCITE-6290]
+ * Incorrect return type for BigQuery TRUNC</a>. */
+ @Test void testBigQueryTruncPreservesCast() {
+ final String query = "SELECT CAST(TRUNC(3) AS BIGINT) as created_thing\n"
+ + " FROM `foodmart`.`product`";
+ final SqlParser.Config parserConfig =
+ BigQuerySqlDialect.DEFAULT.configureParser(SqlParser.config());
+ final Sql sql = fixture()
+ .withBigQuery().withLibrary(SqlLibrary.BIG_QUERY).parserConfig(parserConfig);
+ sql.withSql(query).ok("SELECT CAST(TRUNC(3) AS INT64) AS created_thing\n"
+ + "FROM foodmart.product");
+ }
+
@Test void testSelectLiteralAgg() {
final Function<RelBuilder, RelNode> relFn = b -> b
.scan("EMP")
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 0ca4b26..1e81c68 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -8051,7 +8051,7 @@
@Test void testTruncFail() {
SqlOperatorFixture f = fixture();
f = f.setFor(SqlStdOperatorTable.TRUNCATE, VmName.EXPAND)
- .setFor(SqlLibraryOperators.TRUNC)
+ .setFor(SqlLibraryOperators.TRUNC_BIG_QUERY)
.withLibrary(SqlLibrary.BIG_QUERY);
f.checkFails("^truncate(42, CAST(2 as BIGINT))^",
"Cannot apply 'TRUNCATE' to arguments of type 'TRUNCATE\\(<INTEGER>, <BIGINT>\\)'\\. "
@@ -8190,12 +8190,12 @@
@Test void testTruncFunc() {
final SqlOperatorFixture f = fixture()
- .setFor(SqlLibraryOperators.TRUNC)
+ .setFor(SqlLibraryOperators.TRUNC_BIG_QUERY)
.withLibrary(SqlLibrary.BIG_QUERY);
- f.checkType("trunc(42, -1)", "INTEGER NOT NULL");
+ f.checkType("trunc(42, -1)", "DOUBLE NOT NULL");
f.checkType("trunc(cast(42 as float), 1)", "FLOAT NOT NULL");
f.checkType("trunc(case when false then 42 else null end, -1)",
- "INTEGER");
+ "DOUBLE");
f.enableTypeCoercion(false)
.checkFails("^trunc('abc', 'def')^",
"Cannot apply 'TRUNC' to arguments of type 'TRUNC\\(<CHAR\\(3\\)>, <CHAR\\(3\\)>\\)'\\."
@@ -8203,7 +8203,7 @@
+ "TRUNC\\(<NUMERIC>, <INTEGER>\\)",
false);
f.checkType("trunc('abc', 'def')", "DECIMAL(19, 9) NOT NULL");
- f.checkScalar("trunc(42, -1)", 40, "INTEGER NOT NULL");
+ f.checkScalar("trunc(42, -1)", 40.0, "DOUBLE NOT NULL");
f.checkScalar("trunc(cast(42.345 as decimal(2, 3)), 2)",
BigDecimal.valueOf(4234, 2), "DECIMAL(2, 3) NOT NULL");
f.checkScalar("trunc(cast(-42.345 as decimal(2, 3)), 2)",
@@ -8212,7 +8212,7 @@
f.checkNull("trunc(cast(null as double), 1)");
f.checkNull("trunc(43.21, cast(null as integer))");
- f.checkScalar("trunc(42)", 42, "INTEGER NOT NULL");
+ f.checkScalar("trunc(42)", 42.0, "DOUBLE NOT NULL");
f.checkScalar("trunc(42.324)",
BigDecimal.valueOf(42, 0), "DECIMAL(5, 3) NOT NULL");
f.checkScalar("trunc(cast(42.324 as float))", 42F,
@@ -11307,14 +11307,14 @@
f0.checkType("ceil(cast(3 as tinyint))", "TINYINT NOT NULL");
final SqlOperatorFixture f = f0.setFor(SqlLibraryOperators.FLOOR_BIG_QUERY)
.withLibrary(SqlLibrary.BIG_QUERY).withConformance(SqlConformanceEnum.BIG_QUERY);
- f.checkScalarExact("ceil(cast(3 as tinyint))", "DOUBLE", "3.0");
- f.checkScalarExact("ceil(cast(3 as smallint))", "DOUBLE", "3.0");
- f.checkScalarExact("ceil(cast(3 as integer))", "DOUBLE", "3.0");
- f.checkScalarExact("ceil(cast(3 as bigint))", "DOUBLE", "3.0");
- f.checkScalarExact("ceil(cast(3.5 as double))", "DOUBLE", "4.0");
+ f.checkScalarExact("ceil(cast(3 as tinyint))", "DOUBLE NOT NULL", "3.0");
+ f.checkScalarExact("ceil(cast(3 as smallint))", "DOUBLE NOT NULL", "3.0");
+ f.checkScalarExact("ceil(cast(3 as integer))", "DOUBLE NOT NULL", "3.0");
+ f.checkScalarExact("ceil(cast(3 as bigint))", "DOUBLE NOT NULL", "3.0");
+ f.checkScalarExact("ceil(cast(3.5 as double))", "DOUBLE NOT NULL", "4.0");
f.checkScalarExact("ceil(cast(3.45 as decimal))",
- "DECIMAL(19, 0)", "4");
- f.checkScalarExact("ceil(cast(3.45 as float))", "FLOAT", "4.0");
+ "DECIMAL(19, 0) NOT NULL", "4");
+ f.checkScalarExact("ceil(cast(3.45 as float))", "FLOAT NOT NULL", "4.0");
f.checkNull("ceil(cast(null as tinyint))");
}
@@ -11323,14 +11323,14 @@
f0.checkType("floor(cast(3 as tinyint))", "TINYINT NOT NULL");
final SqlOperatorFixture f = f0.setFor(SqlLibraryOperators.FLOOR_BIG_QUERY)
.withLibrary(SqlLibrary.BIG_QUERY).withConformance(SqlConformanceEnum.BIG_QUERY);
- f.checkScalarExact("floor(cast(3 as tinyint))", "DOUBLE", "3.0");
- f.checkScalarExact("floor(cast(3 as smallint))", "DOUBLE", "3.0");
- f.checkScalarExact("floor(cast(3 as integer))", "DOUBLE", "3.0");
- f.checkScalarExact("floor(cast(3 as bigint))", "DOUBLE", "3.0");
- f.checkScalarExact("floor(cast(3.5 as double))", "DOUBLE", "3.0");
+ f.checkScalarExact("floor(cast(3 as tinyint))", "DOUBLE NOT NULL", "3.0");
+ f.checkScalarExact("floor(cast(3 as smallint))", "DOUBLE NOT NULL", "3.0");
+ f.checkScalarExact("floor(cast(3 as integer))", "DOUBLE NOT NULL", "3.0");
+ f.checkScalarExact("floor(cast(3 as bigint))", "DOUBLE NOT NULL", "3.0");
+ f.checkScalarExact("floor(cast(3.5 as double))", "DOUBLE NOT NULL", "3.0");
f.checkScalarExact("floor(cast(3.45 as decimal))",
- "DECIMAL(19, 0)", "3");
- f.checkScalarExact("floor(cast(3.45 as float))", "FLOAT", "3.0");
+ "DECIMAL(19, 0) NOT NULL", "3");
+ f.checkScalarExact("floor(cast(3.45 as float))", "FLOAT NOT NULL", "3.0");
f.checkNull("floor(cast(null as tinyint))");
}
