This is an automated email from the ASF dual-hosted git repository.
xuzifu666 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 90c82303c7 [CALCITE-7323] Result of cast Number to Boolean is not
correct
90c82303c7 is described below
commit 90c82303c7c81e482c02186ce58cb36bf53275a6
Author: xuzifu666 <[email protected]>
AuthorDate: Wed Dec 10 19:20:26 2025 +0800
[CALCITE-7323] Result of cast Number to Boolean is not correct
---
.../adapter/enumerable/RexToLixTranslator.java | 14 ++++++++++
.../org/apache/calcite/runtime/SqlFunctions.java | 12 +++++++++
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 4 +++
.../org/apache/calcite/rex/RexProgramTest.java | 4 ++-
core/src/test/resources/sql/cast.iq | 24 +++++++++++++++++
.../org/apache/calcite/test/SqlOperatorTest.java | 30 ++++++++++++++++++++++
7 files changed, 88 insertions(+), 1 deletion(-)
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java
index e82cde56a9..7e5f1948ab 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java
@@ -428,6 +428,20 @@ private Expression getConvertExpression(
case CHAR:
case VARCHAR:
return Expressions.call(BuiltInMethod.STRING_TO_BOOLEAN.method,
operand);
+ // Numberic type handle
+ case TINYINT:
+ case SMALLINT:
+ case INTEGER:
+ case BIGINT:
+ case UTINYINT:
+ case USMALLINT:
+ case UINTEGER:
+ case UBIGINT:
+ case DECIMAL:
+ case FLOAT:
+ case REAL:
+ case DOUBLE:
+ return Expressions.call(BuiltInMethod.NUMBER_TO_BOOLEAN.method,
operand);
default:
return defaultExpression.get();
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index ede46373b8..52031cf494 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -4735,6 +4735,18 @@ public static boolean toBoolean(String s) {
}
public static boolean toBoolean(Number number) {
+ if (number instanceof BigDecimal) {
+ BigDecimal decimal = (BigDecimal) number;
+ return decimal.compareTo(BigDecimal.ZERO) != 0;
+ }
+ if (number instanceof Double) {
+ Double d = (Double) number;
+ return !d.equals(Double.valueOf(0));
+ }
+ if (number instanceof Float) {
+ Float f = (Float) number;
+ return !f.equals(Float.valueOf(0));
+ }
return !number.equals(0);
}
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 5fec119d65..14cb12d3e7 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -682,6 +682,7 @@ public enum BuiltInMethod {
"getModifiableCollection"),
SCANNABLE_TABLE_SCAN(ScannableTable.class, "scan", DataContext.class),
STRING_TO_BOOLEAN(SqlFunctions.class, "toBoolean", String.class),
+ NUMBER_TO_BOOLEAN(SqlFunctions.class, "toBoolean", Number.class),
INTERNAL_TO_DATE(SqlFunctions.class, "internalToDate", int.class),
INTERNAL_TO_TIME(SqlFunctions.class, "internalToTime", int.class),
INTERNAL_TO_TIMESTAMP(SqlFunctions.class, "internalToTimestamp", long.class),
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 1b34d42c28..d2125c0ec8 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
@@ -771,6 +771,10 @@ private static String toSql(RelNode root, SqlDialect
dialect,
query = "select FALSE = 0.0e0";
expected = "SELECT *\nFROM (VALUES (TRUE)) AS \"t\" (\"EXPR$0\")";
sql(query).ok(expected);
+
+ query = "select cast(\"product_id\" as BOOLEAN) from \"product\"";
+ expected = "SELECT \"product_id\" <> 0\nFROM \"foodmart\".\"product\"";
+ sql(query).ok(expected);
}
@Test void testSelectQueryWithWhereClauseOfBasicOperators() {
diff --git a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
index 877878aede..3daf4b8962 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
@@ -3152,7 +3152,9 @@ private SqlOperator getNoDeterministicOperator() {
checkSimplify(cast(literal(1), varcharType), "'1':VARCHAR(10)");
checkSimplifyUnchanged(cast(literalAbc, booleanType));
checkSimplify(cast(literal(1), booleanType),
- "false"); // different from Hive
+ "true");
+ checkSimplify(cast(literal(0), booleanType),
+ "false");
checkSimplifyUnchanged(cast(literalAbc, dateType));
checkSimplify(cast(literal(1), dateType),
"1970-01-02"); // different from Hive
diff --git a/core/src/test/resources/sql/cast.iq
b/core/src/test/resources/sql/cast.iq
index 95d11e5b5a..de08cd3807 100644
--- a/core/src/test/resources/sql/cast.iq
+++ b/core/src/test/resources/sql/cast.iq
@@ -1978,4 +1978,28 @@ SELECT ARRAY[cast(null as integer), cast(null as
integer), cast(null as integer)
EXPR$0 INTEGER ARRAY NOT NULL
!type
+# [CALCITE-7323] Result of cast Number to Boolean is not correct
+select cast(mod(deptno, 3) as BOOLEAN), mod(deptno, 3) from emp;
++--------+--------+
+| EXPR$0 | EXPR$1 |
++--------+--------+
+| false | 0 |
+| false | 0 |
+| false | 0 |
+| false | 0 |
+| false | 0 |
+| false | 0 |
+| true | 1 |
+| true | 1 |
+| true | 1 |
+| true | 2 |
+| true | 2 |
+| true | 2 |
+| true | 2 |
+| true | 2 |
++--------+--------+
+(14 rows)
+
+!ok
+
# End cast.iq
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 351f44584f..90839a1daa 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -825,6 +825,36 @@ void testCastToExactNumeric(CastType castType,
SqlOperatorFixture f) {
f.checkNull("CAST(CAST(NULL AS VARCHAR) AS VARBINARY)");
}
+ /**
+ * Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7323";>
+ * Result of cast Number to Boolean is not correct</a>. */
+ @Test public void testNumbericBooleanCast() {
+ SqlOperatorFixture f = fixture();
+ f.setFor(SqlStdOperatorTable.MAX, VM_EXPAND);
+ String[] values = {"0", "CAST(null AS INTEGER)", "2", "2"};
+ f.checkAgg("cast(max(x) as BOOLEAN)", values, isSingle(true));
+ String[] values1 = {"0", "CAST(null AS INTEGER)", "0", "-2"};
+ f.checkAgg("cast(max(x) as BOOLEAN)", values1, isSingle(false));
+ f.checkScalar("CAST(1 AS BOOLEAN)", "true", "BOOLEAN NOT NULL");
+ f.checkScalar("CAST(2 AS BOOLEAN)", "true", "BOOLEAN NOT NULL");
+ f.checkScalar("CAST(abs(2) AS BOOLEAN)", "true", "BOOLEAN NOT NULL");
+ f.checkScalar("CAST(abs(0) AS BOOLEAN)", "false", "BOOLEAN NOT NULL");
+ f.checkScalar("CAST(-1 AS BOOLEAN)", "true", "BOOLEAN NOT NULL");
+ f.checkScalar("CAST(1.2 AS BOOLEAN)", "true", "BOOLEAN NOT NULL");
+ f.checkScalar("CAST(CAST(100.5e0 AS DECIMAL(4, 1)) AS BOOLEAN)", "true",
"BOOLEAN NOT NULL");
+ f.checkScalar("CAST(0 AS BOOLEAN)", "false", "BOOLEAN NOT NULL");
+ f.checkScalar("CAST(0.0 AS BOOLEAN)", "false", "BOOLEAN NOT NULL");
+ f.checkScalar("CAST(0.0e0 AS BOOLEAN)", "false", "BOOLEAN NOT NULL");
+ f.checkScalar("CAST(0.01e0 AS BOOLEAN)", "true", "BOOLEAN NOT NULL");
+ f.checkScalar("CAST(0e0 AS BOOLEAN)", "false", "BOOLEAN NOT NULL");
+ f.checkScalar("CAST(-0e0 AS BOOLEAN)", "false", "BOOLEAN NOT NULL");
+ f.checkNull("CAST(NULL AS BOOLEAN)");
+ f.checkScalar("CAST(CAST(1.2 AS FLOAT) AS BOOLEAN)", "true", "BOOLEAN NOT
NULL");
+ f.checkScalar("CAST(CAST(1.2 AS Double) AS BOOLEAN)", "true", "BOOLEAN NOT
NULL");
+ f.checkScalar("CAST(cast(1 as INTEGER UNSIGNED) AS BOOLEAN)", "true",
"BOOLEAN NOT NULL");
+ f.checkScalar("CAST(cast(1 as TINYINT UNSIGNED) AS BOOLEAN)", "true",
"BOOLEAN NOT NULL");
+ }
+
@ParameterizedTest
@MethodSource("safeParameters")
void testCastStringToDecimal(CastType castType, SqlOperatorFixture f) {
