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 8c1ec07ebc [CALCITE-6290] Incorrect return type for BigQuery TRUNC
8c1ec07ebc is described below
commit 8c1ec07ebc483799cfdf4f21552db0908981e0e4
Author: Tanner Clary <[email protected]>
AuthorDate: Mon Mar 4 10:01:36 2024 -0800
[CALCITE-6290] Incorrect return type for BigQuery TRUNC
Adjust nullability for BigQuery's CEIL and FLOOR to correct [CALCITE-5747]
---
.../calcite/adapter/enumerable/RexImpTable.java | 4 +--
.../calcite/sql/fun/SqlLibraryOperators.java | 9 +++--
.../org/apache/calcite/sql/type/ReturnTypes.java | 8 ++---
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 14 ++++++++
.../org/apache/calcite/test/SqlOperatorTest.java | 40 +++++++++++-----------
5 files changed, 46 insertions(+), 29 deletions(-)
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 bc76a59e88..0b9deb11b5 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_CHAR;
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 @@ public class RexImpTable {
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 86a72d2652..f1166fd8e3 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 @@ public abstract class SqlLibraryOperators {
.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 abstract class SqlLibraryOperators {
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 13e68071c0..342284dd15 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 abstract class ReturnTypes {
*/
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 1de16cb69e..461ce045fc 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 @@ class RelToSqlConverterTest {
+ "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 0ca4b26f9e..1e81c6839a 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 @@ public class SqlOperatorTest {
@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 @@ public class SqlOperatorTest {
@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 @@ public class SqlOperatorTest {
+ "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 @@ public class SqlOperatorTest {
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 @@ public class SqlOperatorTest {
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 @@ public class SqlOperatorTest {
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))");
}
