This is an automated email from the ASF dual-hosted git repository.
shenghang pushed a commit to branch dev
in repository https://gitbox.apache.org/repos/asf/seatunnel.git
The following commit(s) were added to refs/heads/dev by this push:
new 46f7cc014f [Fix][Transform-V2] Fix datetime/numeric/string function
edge cases and update tests (#10168)
46f7cc014f is described below
commit 46f7cc014fca0294c6dd375cc7bf6ce9780ed312
Author: yzeng1618 <[email protected]>
AuthorDate: Sat Dec 13 21:55:56 2025 +0800
[Fix][Transform-V2] Fix datetime/numeric/string function edge cases and
update tests (#10168)
Co-authored-by: zengyi <[email protected]>
---
docs/en/concept/incompatible-changes.md | 6 ++-
.../resources/sql_transform/func_datetime.conf | 2 +-
.../sql/zeta/functions/DateTimeFunction.java | 7 ++--
.../sql/zeta/functions/NumericFunction.java | 10 ++---
.../sql/zeta/functions/StringFunction.java | 8 +++-
.../sql/zeta/functions/SystemFunction.java | 3 --
.../transform/sql/zeta/DateTimeFunctionTest.java | 13 +++++++
.../transform/sql/zeta/NumericFunctionTest.java | 15 ++++++++
.../sql/zeta/functions/StringFunctionTest.java | 24 ++++++++++++
.../sql/zeta/functions/SystemFunctionTest.java | 43 ++++++++++++++++++++++
10 files changed, 116 insertions(+), 15 deletions(-)
diff --git a/docs/en/concept/incompatible-changes.md
b/docs/en/concept/incompatible-changes.md
index 65ec0b6835..6e036bc48d 100644
--- a/docs/en/concept/incompatible-changes.md
+++ b/docs/en/concept/incompatible-changes.md
@@ -13,6 +13,10 @@ You need to check this document before you upgrade to
related version.
### Transform Changes
+- Adjusted SQL Transform date & time functions:
+ - `DATEDIFF(<start>, <end>, 'MONTH')` now returns the total number of months
between the two dates across years (for example, from `2023-01-01` to
`2024-03-01` returns `14` instead of `15`).
+ - `WEEK(<datetime>)` now returns the ISO week number directly (previous
behavior added an extra `+1` to the ISO week value).
+
### Engine Behavior Changes
-### Dependency Upgrades
\ No newline at end of file
+### Dependency Upgrades
diff --git
a/seatunnel-e2e/seatunnel-transforms-v2-e2e/seatunnel-transforms-v2-e2e-part-2/src/test/resources/sql_transform/func_datetime.conf
b/seatunnel-e2e/seatunnel-transforms-v2-e2e/seatunnel-transforms-v2-e2e-part-2/src/test/resources/sql_transform/func_datetime.conf
index 390e139092..10e1e90d03 100644
---
a/seatunnel-e2e/seatunnel-transforms-v2-e2e/seatunnel-transforms-v2-e2e-part-2/src/test/resources/sql_transform/func_datetime.conf
+++
b/seatunnel-e2e/seatunnel-transforms-v2-e2e/seatunnel-transforms-v2-e2e-part-2/src/test/resources/sql_transform/func_datetime.conf
@@ -380,7 +380,7 @@ sink {
field_name = "c4_10"
field_type = "int"
field_value = [
- {equals_to = 15}
+ {equals_to = 14}
]
},
{
diff --git
a/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java
index e677a37009..bfb00f9dfa 100644
---
a/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java
+++
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java
@@ -186,7 +186,7 @@ public class DateTimeFunction {
break;
case "MONTH":
if (date1 != null && date2 != null) {
- return (long) Period.between(date1, date2).getMonths();
+ return Period.between(date1, date2).toTotalMonths();
}
break;
case "WEEK":
@@ -500,6 +500,7 @@ public class DateTimeFunction {
}
break;
case "DOW":
+ case "DAYOFWEEK":
if (datetime instanceof LocalDate) {
return ((LocalDate) datetime).getDayOfWeek().getValue() %
7;
}
@@ -550,8 +551,6 @@ public class DateTimeFunction {
return (year > 0) ? (year - 1) / 1000 + 1 : year / 1000;
}
break;
- case "DAYOFWEEK":
- return dayOfWeek(args);
default:
throw new TransformException(
CommonErrorCodeDeprecated.UNSUPPORTED_OPERATION,
@@ -688,7 +687,7 @@ public class DateTimeFunction {
}
LocalDate localDate = convertToLocalDate(datetime);
WeekFields weekFields = WeekFields.ISO;
- return localDate.get(weekFields.weekOfYear()) + 1;
+ return localDate.get(weekFields.weekOfYear());
}
public static Integer year(List<Object> args) {
diff --git
a/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/NumericFunction.java
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/NumericFunction.java
index 64e1c20d60..1be6a44ef3 100644
---
a/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/NumericFunction.java
+++
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/NumericFunction.java
@@ -208,7 +208,7 @@ public class NumericFunction {
c:
switch (t.toUpperCase()) {
case "INTEGER":
- case "SHOT":
+ case "SHORT":
case "LONG":
{
if (scale < 0) {
@@ -262,10 +262,10 @@ public class NumericFunction {
}
private static Number convertTo(String valueType, Number column) {
- switch (valueType) {
+ switch (valueType.toUpperCase()) {
case "INTEGER":
return column.intValue();
- case "SHOT":
+ case "SHORT":
return column.shortValue();
case "LONG":
return column.longValue();
@@ -403,10 +403,10 @@ public class NumericFunction {
return round(v1, v2, RoundingMode.HALF_UP);
}
- public static int sign(List<Object> args) {
+ public static Integer sign(List<Object> args) {
Number v1 = (Number) args.get(0);
if (v1 == null) {
- return 0;
+ return null;
}
if (v1 instanceof Integer) {
return Integer.signum((Integer) v1);
diff --git
a/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/StringFunction.java
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/StringFunction.java
index dec3948f1b..428a48183c 100644
---
a/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/StringFunction.java
+++
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/StringFunction.java
@@ -50,7 +50,7 @@ public class StringFunction {
public static Integer ascii(List<Object> args) {
String arg = (String) args.get(0);
- if (arg == null) {
+ if (arg == null || arg.isEmpty()) {
return null;
} else {
return (int) arg.charAt(0);
@@ -231,6 +231,9 @@ public class StringFunction {
return null;
}
int count = ((Number) args.get(1)).intValue();
+ if (count < 0) {
+ return "";
+ }
if (count > arg.length()) {
count = arg.length();
}
@@ -243,6 +246,9 @@ public class StringFunction {
return null;
}
int count = ((Number) args.get(1)).intValue();
+ if (count < 0) {
+ return "";
+ }
int length = arg.length();
if (count > length) {
count = length;
diff --git
a/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/SystemFunction.java
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/SystemFunction.java
index e7ea8eb16e..95e2c564e8 100644
---
a/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/SystemFunction.java
+++
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/SystemFunction.java
@@ -107,9 +107,6 @@ public class SystemFunction {
if (v1 == null) {
return null;
}
- if (v1.equals(v2)) {
- return null;
- }
switch (v2) {
case "VARCHAR":
case "STRING":
diff --git
a/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/DateTimeFunctionTest.java
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/DateTimeFunctionTest.java
index d30a4b0877..ed6586a4b4 100644
---
a/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/DateTimeFunctionTest.java
+++
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/DateTimeFunctionTest.java
@@ -24,13 +24,16 @@ import org.apache.seatunnel.api.table.type.SeaTunnelRow;
import org.apache.seatunnel.api.table.type.SeaTunnelRowType;
import org.apache.seatunnel.transform.sql.SQLEngine;
import org.apache.seatunnel.transform.sql.SQLEngineFactory;
+import org.apache.seatunnel.transform.sql.zeta.functions.DateTimeFunction;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
+import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
+import java.util.Arrays;
public class DateTimeFunctionTest {
@@ -176,4 +179,14 @@ public class DateTimeFunctionTest {
// Both Integer and Long inputs should produce the same result
Assertions.assertEquals(fieldInt.toString(), fieldLong.toString());
}
+
+ @Test
+ public void testDateDiffMonthAcrossYearUsesTotalMonths() {
+ LocalDate start = LocalDate.of(2023, 1, 1);
+ LocalDate end = LocalDate.of(2024, 3, 1);
+
+ Long months = DateTimeFunction.datediff(Arrays.asList(start, end,
"MONTH"));
+
+ Assertions.assertEquals(14L, months);
+ }
}
diff --git
a/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/NumericFunctionTest.java
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/NumericFunctionTest.java
index bf60b0aa57..07a194feea 100644
---
a/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/NumericFunctionTest.java
+++
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/NumericFunctionTest.java
@@ -30,6 +30,7 @@ import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import java.math.BigDecimal;
+import java.util.Arrays;
import java.util.Collections;
public class NumericFunctionTest {
@@ -78,4 +79,18 @@ public class NumericFunctionTest {
Assertions.assertEquals("0",
NumericFunction.trimScale(Collections.singletonList(0)));
Assertions.assertNull(NumericFunction.trimScale(Collections.singletonList((Object)
null)));
}
+
+ @Test
+ public void testRoundShortNegativeScale() {
+ short shortValue = 123;
+
+ Number result = NumericFunction.round(Arrays.asList(shortValue, -1));
+
+ Assertions.assertEquals(120, result.intValue());
+ }
+
+ @Test
+ public void testSignNullReturnsNull() {
+
Assertions.assertNull(NumericFunction.sign(Collections.singletonList(null)));
+ }
}
diff --git
a/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/StringFunctionTest.java
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/StringFunctionTest.java
index 038e56042a..0adcf9c15c 100644
---
a/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/StringFunctionTest.java
+++
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/StringFunctionTest.java
@@ -121,4 +121,28 @@ public class StringFunctionTest {
// Should extract time part from formatted string "15:30:45"
Assertions.assertEquals("15:30", StringFunction.substring(args));
}
+
+ @Test
+ public void testAsciiNullAndEmptyReturnNull() {
+ List<Object> args = new ArrayList<>();
+ args.add(null);
+ Assertions.assertNull(StringFunction.ascii(args));
+
+ args.clear();
+ args.add("");
+ Assertions.assertNull(StringFunction.ascii(args));
+ }
+
+ @Test
+ public void testLeftAndRightNegativeCountReturnEmpty() {
+ List<Object> args = new ArrayList<>();
+ args.add("abc");
+ args.add(-1);
+ Assertions.assertEquals("", StringFunction.left(args));
+
+ args.clear();
+ args.add("abc");
+ args.add(-2);
+ Assertions.assertEquals("", StringFunction.right(args));
+ }
}
diff --git
a/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/SystemFunctionTest.java
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/SystemFunctionTest.java
new file mode 100644
index 0000000000..d582feffbd
--- /dev/null
+++
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/SystemFunctionTest.java
@@ -0,0 +1,43 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.seatunnel.transform.sql.zeta.functions;
+
+import org.apache.seatunnel.api.table.type.BasicType;
+import org.apache.seatunnel.api.table.type.SeaTunnelDataType;
+
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+import java.util.Arrays;
+
+public class SystemFunctionTest {
+
+ @Test
+ public void testCastAsDoesNotReturnNullWhenValueEqualsTypeName() {
+ Object result = SystemFunction.castAs(Arrays.asList("VARCHAR",
"VARCHAR"));
+ Assertions.assertEquals("VARCHAR", result);
+ }
+
+ @Test
+ public void testCoalesceRespectsTargetType() {
+ SeaTunnelDataType<?> targetType = BasicType.INT_TYPE;
+ Object result = SystemFunction.coalesce(Arrays.asList(null, "123"),
targetType);
+
+ Assertions.assertEquals(123, result);
+ }
+}
