[SQL] Fix incorrect conversion of long intervals to integers

mihaibudiu · mihaibudiu · commit ca3aff41c3f1 · 2026-03-27T16:28:44.000Z
Signed-off-by: Mihai Budiu &lt;mbudiu@feldera.com&gt;
diff --git a/crates/sqllib/src/casts.rs b/crates/sqllib/src/casts.rs
@@ -1739,212 +1739,103 @@ pub fn cast_to_i64_Weight(w: Weight) -> SqlResult<i64> {
 }
 
 #[doc(hidden)]
-pub fn cast_to_i8_ShortInterval_DAYS(value: ShortInterval) -> SqlResult<i8> {
-    cast_to_i8_i64(value.milliseconds())
+pub fn cast_to_i8_LongInterval_YEARS(value: LongInterval) -> SqlResult<i8> {
+    cast_to_i8_i32(value.years())
 }
 
 #[doc(hidden)]
-pub fn cast_to_i16_ShortInterval_DAYS(value: ShortInterval) -> SqlResult<i16> {
-    cast_to_i16_i64(value.milliseconds())
+pub fn cast_to_i8_LongInterval_MONTHS(value: LongInterval) -> SqlResult<i8> {
+    cast_to_i8_i32(value.months())
 }
 
 #[doc(hidden)]
-pub fn cast_to_i32_ShortInterval_DAYS(value: ShortInterval) -> SqlResult<i32> {
-    cast_to_i32_i64(value.milliseconds())
+pub fn cast_to_i16_LongInterval_YEARS(value: LongInterval) -> SqlResult<i16> {
+    cast_to_i16_i32(value.years())
 }
 
 #[doc(hidden)]
-pub fn cast_to_i64_ShortInterval_DAYS(value: ShortInterval) -> SqlResult<i64> {
-    Ok(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_i8_ShortInterval_HOURS(value: ShortInterval) -> SqlResult<i8> {
-    cast_to_i8_i64(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_i16_ShortInterval_HOURS(value: ShortInterval) -> SqlResult<i16> {
-    cast_to_i16_i64(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_i32_ShortInterval_HOURS(value: ShortInterval) -> SqlResult<i32> {
-    cast_to_i32_i64(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_i64_ShortInterval_HOURS(value: ShortInterval) -> SqlResult<i64> {
-    Ok(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_i8_ShortInterval_MINUTES(value: ShortInterval) -> SqlResult<i8> {
-    cast_to_i8_i64(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_i16_ShortInterval_MINUTES(value: ShortInterval) -> SqlResult<i16> {
-    cast_to_i16_i64(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_i32_ShortInterval_MINUTES(value: ShortInterval) -> SqlResult<i32> {
-    cast_to_i32_i64(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_i64_ShortInterval_MINUTES(value: ShortInterval) -> SqlResult<i64> {
-    Ok(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_i8_ShortInterval_SECONDS(value: ShortInterval) -> SqlResult<i8> {
-    cast_to_i8_i64(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_i16_ShortInterval_SECONDS(value: ShortInterval) -> SqlResult<i16> {
-    cast_to_i16_i64(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_i32_ShortInterval_SECONDS(value: ShortInterval) -> SqlResult<i32> {
-    cast_to_i32_i64(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_i64_ShortInterval_SECONDS(value: ShortInterval) -> SqlResult<i64> {
-    Ok(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_u8_ShortInterval_DAYS(value: ShortInterval) -> SqlResult<u8> {
-    cast_to_u8_i64(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_u16_ShortInterval_DAYS(value: ShortInterval) -> SqlResult<u16> {
-    cast_to_u16_i64(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_u32_ShortInterval_DAYS(value: ShortInterval) -> SqlResult<u32> {
-    cast_to_u32_i64(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_u64_ShortInterval_DAYS(value: ShortInterval) -> SqlResult<u64> {
-    cast_to_u64_i64(value.milliseconds())
+pub fn cast_to_i16_LongInterval_MONTHS(value: LongInterval) -> SqlResult<i16> {
+    cast_to_i16_i32(value.months())
 }
 
 #[doc(hidden)]
-pub fn cast_to_u8_ShortInterval_HOURS(value: ShortInterval) -> SqlResult<u8> {
-    cast_to_u8_i64(value.milliseconds())
+pub fn cast_to_i32_LongInterval_YEARS(value: LongInterval) -> SqlResult<i32> {
+    Ok(value.years())
 }
 
 #[doc(hidden)]
-pub fn cast_to_u16_ShortInterval_HOURS(value: ShortInterval) -> SqlResult<u16> {
-    cast_to_u16_i64(value.milliseconds())
+pub fn cast_to_i32_LongInterval_MONTHS(value: LongInterval) -> SqlResult<i32> {
+    Ok(value.months())
 }
 
 #[doc(hidden)]
-pub fn cast_to_u32_ShortInterval_HOURS(value: ShortInterval) -> SqlResult<u32> {
-    cast_to_u32_i64(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_u64_ShortInterval_HOURS(value: ShortInterval) -> SqlResult<u64> {
-    cast_to_u64_i64(value.milliseconds())
-}
-
-#[doc(hidden)]
-pub fn cast_to_u8_ShortInterval_MINUTES(value: ShortInterval) -> SqlResult<u8> {
-    cast_to_u8_i64(value.milliseconds())
+pub fn cast_to_i64_LongInterval_YEARS(value: LongInterval) -> SqlResult<i64> {
+    Ok(value.years() as i64)
 }
 
 #[doc(hidden)]
-pub fn cast_to_u16_ShortInterval_MINUTES(value: ShortInterval) -> SqlResult<u16> {
-    cast_to_u16_i64(value.milliseconds())
+pub fn cast_to_i64_LongInterval_MONTHS(value: LongInterval) -> SqlResult<i64> {
+    Ok(value.months() as i64)
 }
 
 #[doc(hidden)]
-pub fn cast_to_u32_ShortInterval_MINUTES(value: ShortInterval) -> SqlResult<u32> {
-    cast_to_u32_i64(value.milliseconds())
+pub fn cast_to_u8_LongInterval_YEARS(value: LongInterval) -> SqlResult<u8> {
+    cast_to_u8_i32(value.years())
 }
 
 #[doc(hidden)]
-pub fn cast_to_u64_ShortInterval_MINUTES(value: ShortInterval) -> SqlResult<u64> {
-    cast_to_u64_i64(value.milliseconds())
+pub fn cast_to_u8_LongInterval_MONTHS(value: LongInterval) -> SqlResult<u8> {
+    cast_to_u8_i32(value.months())
 }
 
 #[doc(hidden)]
-pub fn cast_to_u8_ShortInterval_SECONDS(value: ShortInterval) -> SqlResult<u8> {
-    cast_to_u8_i64(value.milliseconds())
+pub fn cast_to_u16_LongInterval_YEARS(value: LongInterval) -> SqlResult<u16> {
+    cast_to_u16_i32(value.years())
 }
 
 #[doc(hidden)]
-pub fn cast_to_u16_ShortInterval_SECONDS(value: ShortInterval) -> SqlResult<u16> {
-    cast_to_u16_i64(value.milliseconds())
+pub fn cast_to_u16_LongInterval_MONTHS(value: LongInterval) -> SqlResult<u16> {
+    cast_to_u16_i32(value.months())
 }
 
 #[doc(hidden)]
-pub fn cast_to_u32_ShortInterval_SECONDS(value: ShortInterval) -> SqlResult<u32> {
-    cast_to_u32_i64(value.milliseconds())
+pub fn cast_to_u32_LongInterval_YEARS(value: LongInterval) -> SqlResult<u32> {
+    cast_to_u32_i32(value.years())
 }
 
 #[doc(hidden)]
-pub fn cast_to_u64_ShortInterval_SECONDS(value: ShortInterval) -> SqlResult<u64> {
-    cast_to_u64_i64(value.milliseconds())
+pub fn cast_to_u32_LongInterval_MONTHS(value: LongInterval) -> SqlResult<u32> {
+    cast_to_u32_i32(value.months())
 }
 
-cast_function!(i8, i8, ShortInterval_DAYS, ShortInterval);
-cast_function!(i16, i16, ShortInterval_DAYS, ShortInterval);
-cast_function!(i32, i32, ShortInterval_DAYS, ShortInterval);
-cast_function!(i64, i64, ShortInterval_DAYS, ShortInterval);
-cast_function!(i8, i8, ShortInterval_HOURS, ShortInterval);
-cast_function!(i16, i16, ShortInterval_HOURS, ShortInterval);
-cast_function!(i32, i32, ShortInterval_HOURS, ShortInterval);
-cast_function!(i64, i64, ShortInterval_HOURS, ShortInterval);
-cast_function!(i8, i8, ShortInterval_MINUTES, ShortInterval);
-cast_function!(i16, i16, ShortInterval_MINUTES, ShortInterval);
-cast_function!(i32, i32, ShortInterval_MINUTES, ShortInterval);
-cast_function!(i64, i64, ShortInterval_MINUTES, ShortInterval);
-cast_function!(i8, i8, ShortInterval_SECONDS, ShortInterval);
-cast_function!(i16, i16, ShortInterval_SECONDS, ShortInterval);
-cast_function!(i32, i32, ShortInterval_SECONDS, ShortInterval);
-cast_function!(i64, i64, ShortInterval_SECONDS, ShortInterval);
-
-cast_function!(u8, u8, ShortInterval_DAYS, ShortInterval);
-cast_function!(u16, u16, ShortInterval_DAYS, ShortInterval);
-cast_function!(u32, u32, ShortInterval_DAYS, ShortInterval);
-cast_function!(u64, u64, ShortInterval_DAYS, ShortInterval);
-cast_function!(u8, u8, ShortInterval_HOURS, ShortInterval);
-cast_function!(u16, u16, ShortInterval_HOURS, ShortInterval);
-cast_function!(u32, u32, ShortInterval_HOURS, ShortInterval);
-cast_function!(u64, u64, ShortInterval_HOURS, ShortInterval);
-cast_function!(u8, u8, ShortInterval_MINUTES, ShortInterval);
-cast_function!(u16, u16, ShortInterval_MINUTES, ShortInterval);
-cast_function!(u32, u32, ShortInterval_MINUTES, ShortInterval);
-cast_function!(u64, u64, ShortInterval_MINUTES, ShortInterval);
-cast_function!(u8, u8, ShortInterval_SECONDS, ShortInterval);
-cast_function!(u16, u16, ShortInterval_SECONDS, ShortInterval);
-cast_function!(u32, u32, ShortInterval_SECONDS, ShortInterval);
-cast_function!(u64, u64, ShortInterval_SECONDS, ShortInterval);
-
 #[doc(hidden)]
-pub fn cast_to_i64_LongInterval_YEARS(value: LongInterval) -> SqlResult<i64> {
-    Ok(value.months() as i64)
+pub fn cast_to_u64_LongInterval_YEARS(value: LongInterval) -> SqlResult<u64> {
+    cast_to_u64_i32(value.years())
 }
 
 #[doc(hidden)]
-pub fn cast_to_i64_LongInterval_MONTHS(value: LongInterval) -> SqlResult<i64> {
-    Ok(value.months() as i64)
+pub fn cast_to_u64_LongInterval_MONTHS(value: LongInterval) -> SqlResult<u64> {
+    cast_to_u64_i32(value.months())
 }
 
+cast_function!(i8, i8, LongInterval_MONTHS, LongInterval);
+cast_function!(i8, i8, LongInterval_YEARS, LongInterval);
+cast_function!(i16, i16, LongInterval_MONTHS, LongInterval);
+cast_function!(i16, i16, LongInterval_YEARS, LongInterval);
+cast_function!(i32, i32, LongInterval_MONTHS, LongInterval);
+cast_function!(i32, i32, LongInterval_YEARS, LongInterval);
 cast_function!(i64, i64, LongInterval_MONTHS, LongInterval);
 cast_function!(i64, i64, LongInterval_YEARS, LongInterval);
 
+cast_function!(u8, u8, LongInterval_MONTHS, LongInterval);
+cast_function!(u8, u8, LongInterval_YEARS, LongInterval);
+cast_function!(u16, u16, LongInterval_MONTHS, LongInterval);
+cast_function!(u16, u16, LongInterval_YEARS, LongInterval);
+cast_function!(u32, u32, LongInterval_MONTHS, LongInterval);
+cast_function!(u32, u32, LongInterval_YEARS, LongInterval);
+cast_function!(u64, u64, LongInterval_MONTHS, LongInterval);
+cast_function!(u64, u64, LongInterval_YEARS, LongInterval);
+
 //////// casts to Short interval
 
 #[doc(hidden)]
diff --git a/crates/sqllib/src/interval.rs b/crates/sqllib/src/interval.rs
@@ -53,6 +53,13 @@ pub struct ShortInterval {
     microseconds: i64,
 }
 
+#[doc(hidden)]
+pub fn reinterpret_ShortInterval(value: ShortInterval) -> i64 {
+    value.milliseconds()
+}
+
+some_polymorphic_function1!(reinterpret, ShortInterval, ShortInterval, i64);
+
 #[doc(hidden)]
 impl<D> ::rkyv::Deserialize<ShortInterval, D> for ArchivedShortInterval
 where
@@ -485,6 +492,13 @@ impl LongInterval {
     }
 }
 
+#[doc(hidden)]
+pub fn reinterpret_LongInterval(value: LongInterval) -> i64 {
+    value.months() as i64
+}
+
+some_polymorphic_function1!(reinterpret, LongInterval, LongInterval, i64);
+
 #[doc(hidden)]
 pub fn abs_LongInterval(value: LongInterval) -> LongInterval {
     LongInterval::from_months(num::abs(value.months))
diff --git a/sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/backend/rust/ToRustInnerVisitor.java b/sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/backend/rust/ToRustInnerVisitor.java
@@ -1876,6 +1876,17 @@ public VisitDecision preorder(DBSPUnaryExpression expression) {
             this.builder.append(")");
             this.pop(expression);
             return VisitDecision.STOP;
+        } else if (expression.opcode == DBSPOpcode.REINTERPRET) {
+            DBSPTypeBaseType sourceType = expression.source.getType().to(DBSPTypeBaseType.class);
+            this.builder.append(expression.opcode.toString())
+                    .append("_")
+                    .append(sourceType.shortName())
+                    .append(sourceType.nullableSuffix())
+                    .append("(");
+            expression.source.accept(this);
+            this.builder.append(")");
+            this.pop(expression);
+            return VisitDecision.STOP;
         }
         if (expression.source.getType().mayBeNull) {
             this.builder.append("(")
diff --git a/sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/frontend/ExpressionCompiler.java b/sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/frontend/ExpressionCompiler.java
@@ -41,7 +41,6 @@
 import org.apache.calcite.util.DateString;
 import org.apache.calcite.util.TimeString;
 import org.apache.calcite.util.TimestampString;
-import org.dbsp.sqlCompiler.circuit.operator.DBSPConcreteAsofJoinOperator;
 import org.dbsp.sqlCompiler.compiler.DBSPCompiler;
 import org.dbsp.sqlCompiler.compiler.ICompilerComponent;
 import org.dbsp.sqlCompiler.compiler.errors.BaseCompilerException;
@@ -1061,9 +1060,15 @@ public DBSPExpression visitCall(RexCall call) {
                 return makeBinaryExpressions(node, type, DBSPOpcode.BW_OR, ops);
             case BIT_XOR:
                 return makeBinaryExpressions(node, type, DBSPOpcode.XOR, ops);
+            case REINTERPRET:
+                // Calcite only seems to generate this for converting intervals to integers
+                if ((!type.is(DBSPTypeInteger.class) || type.to(DBSPTypeInteger.class).getWidth() != 64)
+                        && !ops.get(0).getType().is(IsIntervalType.class)) {
+                    throw new InternalCompilerError("Unexpected cast ", node);
+                }
+                return new DBSPUnaryExpression(node, type, DBSPOpcode.REINTERPRET, ops.get(0));
             case CAST:
             case SAFE_CAST:
-            case REINTERPRET:
                 if (!validateCast(ops.get(0).getType(), type, call.op.kind == SqlKind.SAFE_CAST)) {
                     throw new CompilationError(call.op.kind + " cannot be used to convert " +
                             ops.get(0).getType().asSqlString() +
diff --git a/sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/ir/expression/DBSPOpcode.java b/sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/ir/expression/DBSPOpcode.java
@@ -100,7 +100,9 @@ public enum DBSPOpcode {
     // Same as MAP_CONVERT, except the function applied returns SqlResult, and
     // the map function returns None in case any partial result is Error.
     MAP_CONVERT_SAFE("map_map_safe", false),
-    ;
+    // Used to implement Calcite's REINTERPRET casts;
+    // input is always an interval, output is always i64
+    REINTERPRET("reinterpret", false);
 
     private final String text;
     public final boolean isAggregate;
diff --git a/sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/ir/expression/DBSPUnaryExpression.java b/sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/ir/expression/DBSPUnaryExpression.java
@@ -79,7 +79,8 @@ public IIndentStream toString(IIndentStream builder) {
         if (this.opcode == DBSPOpcode.DECIMAL_TO_INTEGER ||
                 this.opcode == DBSPOpcode.INTEGER_TO_DECIMAL ||
                 this.opcode == DBSPOpcode.SHORT_INTERVAL_TO_INTEGER ||
-                this.opcode == DBSPOpcode.INTEGER_TO_SHORT_INTERVAL) {
+                this.opcode == DBSPOpcode.INTEGER_TO_SHORT_INTERVAL ||
+                this.opcode == DBSPOpcode.REINTERPRET) {
             return builder.append(this.opcode.toString())
                     .append("(")
                     .append(this.source)
diff --git a/sql-to-dbsp-compiler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/simple/CastTests.java b/sql-to-dbsp-compiler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/simple/CastTests.java
