This is an automated email from the ASF dual-hosted git repository.
FrankChen021 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/druid.git
The following commit(s) were added to refs/heads/master by this push:
new 7e300b9ccf6 fix: Proper handling of numeric CAST to INTERVAL. (#20754)
7e300b9ccf6 is described below
commit 7e300b9ccf6cca26d7bbe632f790415b31f123c1
Author: Gian Merlino <[email protected]>
AuthorDate: Tue Nov 3 18:10:20 2026 -0800
fix: Proper handling of numeric CAST to INTERVAL. (#20754)
This patch fixes handling of numeric types to INTERVAL types.
Previously, the unit was not respected, and expressions would yield
planner errors.
---
.../expression/builtin/CastOperatorConversion.java | 39 +++++++
.../apache/druid/sql/calcite/CalciteQueryTest.java | 119 +++++++++++++++++++++
2 files changed, 158 insertions(+)
diff --git
a/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/CastOperatorConversion.java
b/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/CastOperatorConversion.java
index d82d3ea9546..c1425101dae 100644
---
a/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/CastOperatorConversion.java
+++
b/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/CastOperatorConversion.java
@@ -19,9 +19,12 @@
package org.apache.druid.sql.calcite.expression.builtin;
+import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
+import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.sql.SqlIntervalQualifier;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.type.SqlTypeName;
@@ -39,6 +42,7 @@ import org.apache.druid.sql.calcite.planner.Calcites;
import org.apache.druid.sql.calcite.planner.PlannerContext;
import org.joda.time.Period;
+import java.math.BigDecimal;
import java.util.function.Function;
public class CastOperatorConversion implements SqlOperatorConversion
@@ -79,6 +83,12 @@ public class CastOperatorConversion implements
SqlOperatorConversion
);
} else if (SqlTypeName.DATETIME_TYPES.contains(fromType) &&
SqlTypeName.CHAR_TYPES.contains(toType)) {
return castDateTimeToChar(plannerContext, operandExpression, fromType,
Calcites.getColumnTypeForRelDataType(rexNode.getType()));
+ } else if (SqlTypeName.NUMERIC_TYPES.contains(fromType) &&
SqlTypeName.INTERVAL_TYPES.contains(toType)) {
+ return castNumericToInterval(
+ operandExpression,
+ Calcites.getColumnTypeForRelDataType(operand.getType()),
+ rexNode.getType()
+ );
} else {
// Handle other casts. If either type is ANY, use the other type
instead. If both are ANY, this means nulls
// downstream, Druid will try its best
@@ -166,6 +176,35 @@ public class CastOperatorConversion implements
SqlOperatorConversion
}
}
+ /**
+ * Returns an expression that scales a number to the representation of an
interval: milliseconds for day-time
+ * intervals and months for year-month intervals.
+ */
+ private static DruidExpression castNumericToInterval(
+ final DruidExpression operand,
+ final ColumnType fromDruidType,
+ final RelDataType toType
+ )
+ {
+ final SqlIntervalQualifier intervalQualifier =
+ Preconditions.checkNotNull(toType.getIntervalQualifier(),
"intervalQualifier");
+ final BigDecimal multiplier = intervalQualifier.getUnit().multiplier;
+
+ return DruidExpression.ofExpression(
+ ColumnType.LONG,
+ args -> {
+ final String scaled;
+ if (multiplier.compareTo(BigDecimal.ONE) != 0) {
+ scaled = StringUtils.format("(%s * %s)",
args.getFirst().getExpression(), multiplier.toPlainString());
+ } else {
+ scaled = args.getFirst().getExpression();
+ }
+ return ColumnType.LONG.equals(fromDruidType) ? scaled :
StringUtils.format("CAST(%s, 'LONG')", scaled);
+ },
+ ImmutableList.of(operand)
+ );
+ }
+
private static DruidExpression castDateTimeToChar(
final PlannerContext plannerContext,
final DruidExpression operand,
diff --git
a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
index 961e4b08c89..e69f8e9c463 100644
--- a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
+++ b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
@@ -5128,6 +5128,125 @@ public class CalciteQueryTest extends
BaseCalciteQueryTest
);
}
+ @Test
+ public void testInfixCastToInterval()
+ {
+ // Numbers cast to intervals are in units of the interval's field.
+ testQuery(
+ "SELECT __time + CAST(cnt AS INTERVAL DAY), __time + cnt::INTERVAL
HOUR, __time + (cnt * 2)::INTERVAL MONTH,"
+ + " __time - m1::BIGINT::INTERVAL SECOND, __time + (cnt - 3)::INTERVAL
YEAR FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(
+ expressionVirtualColumn("v0", "(\"__time\" + (\"cnt\" *
86400000))", ColumnType.LONG),
+ expressionVirtualColumn("v1", "(\"__time\" + (\"cnt\" *
3600000))", ColumnType.LONG),
+ expressionVirtualColumn(
+ "v2",
+ "timestamp_shift(\"__time\",concat('P', (\"cnt\" * 2),
'M'),1,'UTC')",
+ ColumnType.LONG
+ ),
+ expressionVirtualColumn("v3", "(\"__time\" - (CAST(\"m1\",
'LONG') * 1000))", ColumnType.LONG),
+ expressionVirtualColumn(
+ "v4",
+ "timestamp_shift(\"__time\",concat('P', ((\"cnt\" - 3)
* 12), 'M'),1,'UTC')",
+ ColumnType.LONG
+ )
+ )
+ .columns("v0", "v1", "v2", "v3", "v4")
+ .columnTypes(ColumnType.LONG, ColumnType.LONG,
ColumnType.LONG, ColumnType.LONG, ColumnType.LONG)
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{
+ timestamp("2000-01-02"),
+ timestamp("2000-01-01T01"),
+ timestamp("2000-03-01"),
+ timestamp("1999-12-31T23:59:59"),
+ timestamp("1998-01-01")
+ },
+ new Object[]{
+ timestamp("2000-01-03"),
+ timestamp("2000-01-02T01"),
+ timestamp("2000-03-02"),
+ timestamp("2000-01-01T23:59:58"),
+ timestamp("1998-01-02")
+ },
+ new Object[]{
+ timestamp("2000-01-04"),
+ timestamp("2000-01-03T01"),
+ timestamp("2000-03-03"),
+ timestamp("2000-01-02T23:59:57"),
+ timestamp("1998-01-03")
+ },
+ new Object[]{
+ timestamp("2001-01-02"),
+ timestamp("2001-01-01T01"),
+ timestamp("2001-03-01"),
+ timestamp("2000-12-31T23:59:56"),
+ timestamp("1999-01-01")
+ },
+ new Object[]{
+ timestamp("2001-01-03"),
+ timestamp("2001-01-02T01"),
+ timestamp("2001-03-02"),
+ timestamp("2001-01-01T23:59:55"),
+ timestamp("1999-01-02")
+ },
+ new Object[]{
+ timestamp("2001-01-04"),
+ timestamp("2001-01-03T01"),
+ timestamp("2001-03-03"),
+ timestamp("2001-01-02T23:59:54"),
+ timestamp("1999-01-03")
+ }
+ )
+ );
+ }
+
+ @Test
+ public void testInfixCastDecimalToInterval()
+ {
+ // Fractions are kept down to the millisecond for day-time intervals,
rather than truncated to the field.
+ testQuery(
+ "SELECT __time + (m1 / 2)::DECIMAL(5, 1)::INTERVAL DAY, __time + (m1 -
3.5)::DECIMAL(5, 1)::INTERVAL SECOND"
+ + " FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(
+ expressionVirtualColumn(
+ "v0",
+ "(\"__time\" + CAST(((\"m1\" / 2) * 86400000),
'LONG'))",
+ ColumnType.LONG
+ ),
+ expressionVirtualColumn(
+ "v1",
+ "(\"__time\" + CAST(((\"m1\" - 3.5) * 1000), 'LONG'))",
+ ColumnType.LONG
+ )
+ )
+ .columns("v0", "v1")
+ .columnTypes(ColumnType.LONG, ColumnType.LONG)
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{timestamp("2000-01-01T12"),
timestamp("1999-12-31T23:59:57.500")},
+ new Object[]{timestamp("2000-01-03"),
timestamp("2000-01-01T23:59:58.500")},
+ new Object[]{timestamp("2000-01-04T12"),
timestamp("2000-01-02T23:59:59.500")},
+ new Object[]{timestamp("2001-01-03"),
timestamp("2001-01-01T00:00:00.500")},
+ new Object[]{timestamp("2001-01-04T12"),
timestamp("2001-01-02T00:00:01.500")},
+ new Object[]{timestamp("2001-01-06"),
timestamp("2001-01-03T00:00:02.500")}
+ )
+ );
+ }
+
@Test
public void testInfixCastMixedWithCastInGroupBy()
{
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
