You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@seatunnel.apache.org by "aksmf1442 (via GitHub)" <gi...@apache.org> on 2026/01/17 20:29:46 UTC

[PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

aksmf1442 opened a new pull request, #10360:
URL: https://github.com/apache/seatunnel/pull/10360

   <!--
   
   Thank you for contributing to SeaTunnel! Please make sure that your code changes
   are covered with tests. And in case of new features or big changes
   remember to adjust the documentation.
   
   Feel free to ping committers for the review!
   
   ## Contribution Checklist
     - Make sure that the pull request corresponds to a [GITHUB issue](https://github.com/apache/seatunnel/issues).
     - Name the pull request in the form "[Feature] [component] Title of the pull request", where *Feature* can be replaced by `Hotfix`, `Bug`, etc.
     - Minor fixes should be named following this pattern: `[hotfix] [docs] Fix typo in README.md doc`.
   -->
   
   ### Purpose of this pull request
   
   This PR addresses the ambiguity and potential parsing errors in the PARSEDATETIME and TO_DATE functions by introducing
     whitelist-based validation for datetime format patterns.
   
     **Problem:**
     Currently, these functions use pattern matching (contains("yy"), contains("mm")) to infer the return type
     (LocalDateTime/LocalDate/LocalTime), which leads to:
     1. Unclear documentation on which formats are officially supported
     2. Unpredictable behavior due to delegating all format validation to DateTimeFormatter, making it impossible to control or
     predict which format patterns will be accepted and how they will behave
   
     **Solution:**
     - Introduced ZetaDateTimeFormat enum with predefined, validated format patterns
     - Added strict whitelist validation that rejects unsupported formats with clear error messages
     - Updated documentation to explicitly list all supported formats
   
     **Supported Format Patterns:**
     - DateTime: yyyy-MM-dd HH:mm:ss, yyyy-MM-dd HH:mm:ss.SSS, yyyy-MM-dd'T'HH:mm:ss, yyyy-MM-dd'T'HH:mm:ss.SSS
     - Date: yyyy-MM-dd
     - Time: HH:mm:ss, HH:mm:ss.SSS
   
     **Related issues:** #10352 
   
   
   <!-- Describe the purpose of this pull request. For example: This pull request adds checkstyle plugin.-->
   
   
   ### Does this PR introduce _any_ user-facing change?
   
   Yes. Users who were using unsupported datetime format patterns (e.g., yyyy-M-d, dd/MM/yyyy, HH:mm) will now receive a clear
     TransformException with the message:
     Unsupported datetime format: '<pattern>'
   
   
   <!--
   Note that it means *any* user-facing change including all aspects such as the documentation fix.
   If yes, please clarify the previous behavior and the change this PR proposes - provide the console output, description and/or an example to show the behavior difference if possible.
   If possible, please also clarify if this is a user-facing change compared to the released SeaTunnel versions or within the unreleased branches such as dev.
   If no, write 'No'.
   If you are adding/modifying connector documents, please follow our new specifications: https://github.com/apache/seatunnel/issues/4544.
   -->
   
   
   ### How was this patch tested?
   
   Unit Tests Added:
   
     **1. ZetaDateTimeFormatTest.java**
    - testFromPatternWithAllDateTimeFormats() - Validates all DateTime format patterns
    - testFromPatternWithAllDateFormats() - Validates Date format patterns
    - testFromPatternWithAllTimeFormats() - Validates Time format patterns
    - testFromPatternWithInvalidFormat() - Ensures invalid patterns return empty Optional
    - testFromPatternIsCaseSensitive() - Verifies case sensitivity
    - testAllEnumValuesAreUnique() - Prevents duplicate patterns
   
     **2. DateTimeFunctionsTest.java**
   - testParseDateTimeWithAllDateTimeFormats() - Tests all 4 DateTime patterns
   - testParseDateTimeWithAllDateFormats() - Tests Date pattern
   - testParseDateTimeWithAllTimeFormats() - Tests both Time patterns
   - testParseDateTimeWithUnsupportedFormat() - Verifies exception for unsupported patterns
   - testParseDateTimeWithMalformedInput() - Verifies exception for malformed input
   - testToDateWithVariousFormats() - Tests TO_DATE alias function
   
   
   <!--
   If tests were added, say they were added here. Please make sure to add some test cases that check the changes thoroughly including negative and positive cases if possible.
   If it was tested in a way different from regular unit tests, please clarify how you tested step by step, ideally copy and paste-able, so that other reviewers can test and check, and descendants can verify in the future.
   If tests were not added, please describe why they were not added and/or why it was difficult to add.
   If you are adding E2E test cases, maybe refer to https://github.com/apache/seatunnel/blob/dev/seatunnel-e2e/seatunnel-connector-v2-e2e/connector-cdc-mysql-e2e/src/test/resources/mysqlcdc_to_mysql.conf, here is a good example.
   -->
   
   
   ### Check list
   
   * [ ] If any new Jar binary package adding in your PR, please add License Notice according
     [New License Guide](https://github.com/apache/seatunnel/blob/dev/docs/en/contribution/new-license.md)
   * [ ] If necessary, please update the documentation to describe the new feature. https://github.com/apache/seatunnel/tree/dev/docs
   * [ ] If necessary, please update `incompatible-changes.md` to describe the incompatibility caused by this PR.
   * [ ] If you are contributing the connector code, please check that the following files are updated:
     1. Update [plugin-mapping.properties](https://github.com/apache/seatunnel/blob/dev/plugin-mapping.properties) and add new connector information in it
     2. Update the pom file of [seatunnel-dist](https://github.com/apache/seatunnel/blob/dev/seatunnel-dist/pom.xml)
     3. Add ci label in [label-scope-conf](https://github.com/apache/seatunnel/blob/dev/.github/workflows/labeler/label-scope-conf.yml)
     4. Add e2e testcase in [seatunnel-e2e](https://github.com/apache/seatunnel/tree/dev/seatunnel-e2e/seatunnel-connector-v2-e2e/)
     5. Update connector [plugin_config](https://github.com/apache/seatunnel/blob/dev/config/plugin_config)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "aksmf1442 (via GitHub)" <gi...@apache.org>.
aksmf1442 commented on PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#issuecomment-3847303894

   @dybyte 
   Just a friendly reminder in case this got buried.
   Totally understand if things are busy — thanks for taking a look when possible!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "aksmf1442 (via GitHub)" <gi...@apache.org>.
aksmf1442 commented on PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#issuecomment-3852979615

   @davidzollo 
   
   Done! Refactored to avoid exception-based control flow for better performance in ETL scenarios.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "aksmf1442 (via GitHub)" <gi...@apache.org>.
aksmf1442 commented on PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#issuecomment-3784751617

   > Could you please resolve the merge conflicts?
   
   @dybyte 
   Merge conflicts have been resolved.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "aksmf1442 (via GitHub)" <gi...@apache.org>.
aksmf1442 commented on PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#issuecomment-3799851205

   > > Hi @aksmf1442 , Good job. I found there is one issue you can do it better ^_^
   > > ### Performance Bottleneck
   > > **Description**: The code calls `DateTimeFormatter.ofPattern(dateTimeFormat.getPattern())` inside the `parsedatetime` method. Since this UDF executes **per row**, compiling the pattern every time is extremely inefficient for high-throughput scenarios. **Location**: `seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java` (Line ~646) **Recommendation**: Cache the `DateTimeFormatter` instance within the `ZetaDateTimeFormat` enum. `DateTimeFormatter` is thread-safe and should be created only once.
   > > **Suggested Change**: Refactor `ZetaDateTimeFormat` to initialize the formatter in its constructor:
   > > ```java
   > > public enum ZetaDateTimeFormat {
   > >     // ...
   > >     private final DateTimeFormatter formatter;
   > > 
   > >     ZetaDateTimeFormat(String pattern, FormatType type) {
   > >         this.pattern = pattern;
   > >         this.type = type;
   > >         this.formatter = DateTimeFormatter.ofPattern(pattern); // Initialize once
   > >     }
   > >     
   > >     public DateTimeFormatter getFormatter() { return formatter; }
   > > }
   > > ```
   > > 
   > > 
   > >     
   > >       
   > >     
   > > 
   > >       
   > >     
   > > 
   > >     
   > >   
   > > Then use `dateTimeFormat.getFormatter()` in the function.
   > > By the way, there is a smaill Issue: Whitelist Too Strict **Description**: The current whitelist misses common formats like compact dates (`yyyyMMdd`) or slash separators (`yyyy/MM/dd`). **Recommendation**: It's better if you can expand the whitelist to include these common variations to reduce migration friction for users.
   > 
   > @davidzollo Thank you for the valuable suggestions. I’ll reflect them in t
   
   @davidzollo 
   I've addressed both issues.
   
     1. **Performance optimization**: Cached `DateTimeFormatter` in the enum constructor to avoid repeated pattern compilation
     2. **Expanded whitelist**: Added common formats including:
        - Compact: `yyyyMMdd`, `yyyyMMddHHmmss`, `HHmmss`
        - Slash separator: `yyyy/MM/dd`, `yyyy/MM/dd HH:mm:ss`, `yyyy/MM/dd HH:mm:ss.SSS`


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "dybyte (via GitHub)" <gi...@apache.org>.
dybyte commented on code in PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#discussion_r2715681320


##########
docs/en/introduction/concepts/incompatible-changes.md:
##########
@@ -13,6 +13,14 @@ You need to check this document before you upgrade to related version.
 
 ### Transform Changes
 
+- **[BREAKING]** SQL Transform `PARSEDATETIME` and `TO_DATE` functions now only accept whitelisted datetime format patterns. Custom format patterns that were previously accepted will now fail at runtime. The supported patterns are:
+  - DateTime: `yyyy-MM-dd HH:mm:ss`, `yyyy-MM-dd HH:mm:ss.SSS`, `yyyy-MM-dd'T'HH:mm:ss`, `yyyy-MM-dd'T'HH:mm:ss.SSS`
+  - Date: `yyyy-MM-dd`
+  - Time: `HH:mm:ss`, `HH:mm:ss.SSS`
+
+
+  **Migration Guide**: If you are using custom datetime format patterns in `PARSEDATETIME` or `TO_DATE` functions, you must update your queries to use one of the supported patterns above. If your data uses a different format, you may need to preprocess the input data to match a supported format, or use string manipulation functions to transform the format before parsing.

Review Comment:
   Since the exception for invalid formats has changed from `TransformException` to `SeaTunnelRuntimeException`, it would be great to mention this change in the compatibility documentation. This will help users update their error handling or monitoring systems accordingly.



##########
seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunctionsTest.java:
##########
@@ -319,11 +319,127 @@ public void testDateAddWithUnsupportedField() {
                         new SeaTunnelDataType[] {LocalTimeType.LOCAL_DATE_TYPE});
 
         Assertions.assertThrows(
-                TransformException.class,
+                SeaTunnelRuntimeException.class,
                 () ->
                         runSql(
                                 "select DATEADD(dt, 1, 'UNSUPPORTED') as d from dual",
                                 rowType,
                                 LocalDate.of(2024, 6, 15)));

Review Comment:
   I don’t think this test is affected by the current PR. Is it related?



##########
seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLTypeTest.java:
##########
@@ -355,14 +356,14 @@ public void testCoalesceMultiIfModAndUdfTypes() {
         Function multiIfNoParams = new Function();
         multiIfNoParams.setName(ZetaSQLFunction.MULTI_IF);
         Assertions.assertThrows(
-                TransformException.class, () -> type.getExpressionType(multiIfNoParams));
+                SeaTunnelRuntimeException.class, () -> type.getExpressionType(multiIfNoParams));
 
         Function multiIfEvenArgs = new Function();
         multiIfEvenArgs.setName(ZetaSQLFunction.MULTI_IF);
         multiIfEvenArgs.setParameters(
                 new ExpressionList<>(Arrays.asList(new LongValue(1), new LongValue(1))));
         Assertions.assertThrows(
-                TransformException.class, () -> type.getExpressionType(multiIfEvenArgs));
+                SeaTunnelRuntimeException.class, () -> type.getExpressionType(multiIfEvenArgs));

Review Comment:
   ditto



##########
seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLTypeTest.java:
##########
@@ -437,6 +438,6 @@ public void testGetMaxTypeCollection() {
         Assertions.assertEquals(BasicType.DOUBLE_TYPE, result);
 
         Assertions.assertThrows(
-                TransformException.class, () -> type.getMaxType(Collections.emptyList()));
+                SeaTunnelRuntimeException.class, () -> type.getMaxType(Collections.emptyList()));

Review Comment:
   ditto



##########
seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLTypeTest.java:
##########
@@ -388,7 +389,7 @@ public void testCoalesceMultiIfModAndUdfTypes() {
         unknownFunc.setParameters(
                 new ExpressionList<>(Collections.singletonList(new LongValue(1))));
         Assertions.assertThrows(
-                TransformException.class, () -> udfType.getExpressionType(unknownFunc));
+                SeaTunnelRuntimeException.class, () -> udfType.getExpressionType(unknownFunc));

Review Comment:
   ditto



##########
seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLTypeTest.java:
##########
@@ -338,7 +339,7 @@ public void testCoalesceMultiIfModAndUdfTypes() {
         Function badCoalesce = new Function();
         badCoalesce.setName(ZetaSQLFunction.COALESCE);
         Assertions.assertThrows(
-                TransformException.class, () -> type.getExpressionType(badCoalesce));
+                SeaTunnelRuntimeException.class, () -> type.getExpressionType(badCoalesce));

Review Comment:
   ditto



##########
seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLTypeTest.java:
##########
@@ -420,7 +421,7 @@ public void testIsNumberTypeAndGetMaxType() {
         Assertions.assertEquals(intType, type.getMaxType(intType, null));
 
         Assertions.assertThrows(
-                TransformException.class,
+                SeaTunnelRuntimeException.class,

Review Comment:
   ditto



##########
seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunctionsTest.java:
##########
@@ -319,11 +319,127 @@ public void testDateAddWithUnsupportedField() {
                         new SeaTunnelDataType[] {LocalTimeType.LOCAL_DATE_TYPE});
 
         Assertions.assertThrows(
-                TransformException.class,
+                SeaTunnelRuntimeException.class,
                 () ->
                         runSql(
                                 "select DATEADD(dt, 1, 'UNSUPPORTED') as d from dual",
                                 rowType,
                                 LocalDate.of(2024, 6, 15)));
     }
+
+    @Test
+    public void testParseDateTimeWithAllDateTimeFormats() {
+        SeaTunnelRowType rowType =
+                new SeaTunnelRowType(
+                        new String[] {"dummy"},
+                        new SeaTunnelDataType[] {LocalTimeType.LOCAL_DATE_TIME_TYPE});
+
+        // DATETIME_STANDARD: yyyy-MM-dd HH:mm:ss
+        SeaTunnelRow row1 =
+                runSql(
+                        "select PARSEDATETIME('2024-06-15 14:30:45', 'yyyy-MM-dd HH:mm:ss') as dt from dual",
+                        rowType,
+                        LocalDateTime.now());
+        Assertions.assertEquals(LocalDateTime.of(2024, 6, 15, 14, 30, 45), row1.getField(0));
+
+        // DATETIME_WITH_MILLIS: yyyy-MM-dd HH:mm:ss.SSS
+        SeaTunnelRow row2 =
+                runSql(
+                        "select PARSEDATETIME('2024-06-15 14:30:45.123', 'yyyy-MM-dd HH:mm:ss.SSS') as dt from dual",
+                        rowType,
+                        LocalDateTime.now());
+        Assertions.assertEquals(
+                LocalDateTime.of(2024, 6, 15, 14, 30, 45, 123000000), row2.getField(0));
+
+        // DATETIME_ISO8601: yyyy-MM-dd'T'HH:mm:ss
+        SeaTunnelRow row3 =
+                runSql(
+                        "select PARSEDATETIME('2024-06-15T14:30:45', 'yyyy-MM-dd''T''HH:mm:ss') as dt from dual",
+                        rowType,
+                        LocalDateTime.now());
+        Assertions.assertEquals(LocalDateTime.of(2024, 6, 15, 14, 30, 45), row3.getField(0));
+
+        // DATETIME_ISO8601_WITH_MILLIS: yyyy-MM-dd'T'HH:mm:ss.SSS
+        SeaTunnelRow row4 =
+                runSql(
+                        "select PARSEDATETIME('2024-06-15T14:30:45.987', 'yyyy-MM-dd''T''HH:mm:ss.SSS') as dt from dual",
+                        rowType,
+                        LocalDateTime.now());
+        Assertions.assertEquals(
+                LocalDateTime.of(2024, 6, 15, 14, 30, 45, 987000000), row4.getField(0));
+    }
+
+    @Test
+    public void testParseDateTimeWithAllTimeFormats() {
+        SeaTunnelRowType rowType =
+                new SeaTunnelRowType(
+                        new String[] {"dummy"},
+                        new SeaTunnelDataType[] {LocalTimeType.LOCAL_DATE_TIME_TYPE});
+
+        // TIME_STANDARD: HH:mm:ss
+        SeaTunnelRow row1 =
+                runSql(
+                        "select PARSEDATETIME('14:30:45', 'HH:mm:ss') as dt from dual",
+                        rowType,
+                        LocalDateTime.now());
+        Assertions.assertEquals(java.time.LocalTime.of(14, 30, 45), row1.getField(0));
+
+        // TIME_WITH_MILLIS: HH:mm:ss.SSS
+        SeaTunnelRow row2 =
+                runSql(
+                        "select PARSEDATETIME('14:30:45.123', 'HH:mm:ss.SSS') as dt from dual",
+                        rowType,
+                        LocalDateTime.now());
+        Assertions.assertEquals(java.time.LocalTime.of(14, 30, 45, 123000000), row2.getField(0));
+    }
+
+    @Test
+    public void testParseDateTimeWithUnsupportedFormat() {
+        SeaTunnelRowType rowType =
+                new SeaTunnelRowType(
+                        new String[] {"dummy"},
+                        new SeaTunnelDataType[] {LocalTimeType.LOCAL_DATE_TIME_TYPE});
+
+        // Test with completely unsupported format
+        Assertions.assertThrows(
+                SeaTunnelRuntimeException.class,
+                () ->
+                        runSql(
+                                "select PARSEDATETIME('2024-06-15', 'dd/MM/yyyy') as dt from dual",
+                                rowType,
+                                LocalDateTime.now()));
+    }
+
+    @Test
+    public void testParseDateTimeWithMalformedInput() {
+        SeaTunnelRowType rowType =
+                new SeaTunnelRowType(
+                        new String[] {"dummy"},
+                        new SeaTunnelDataType[] {LocalTimeType.LOCAL_DATE_TIME_TYPE});
+
+        // Test with malformed input string
+        Assertions.assertThrows(
+                SeaTunnelRuntimeException.class,
+                () ->
+                        runSql(
+                                "select PARSEDATETIME('not-a-date', 'yyyy-MM-dd') as dt from dual",
+                                rowType,
+                                LocalDateTime.now()));
+    }
+
+    @Test
+    public void testToDateWithVariousFormats() {

Review Comment:
   ```suggestion
       public void testToDateWithDateFormat() {
   ```



##########
seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLTypeTest.java:
##########
@@ -355,14 +356,14 @@ public void testCoalesceMultiIfModAndUdfTypes() {
         Function multiIfNoParams = new Function();
         multiIfNoParams.setName(ZetaSQLFunction.MULTI_IF);
         Assertions.assertThrows(
-                TransformException.class, () -> type.getExpressionType(multiIfNoParams));
+                SeaTunnelRuntimeException.class, () -> type.getExpressionType(multiIfNoParams));

Review Comment:
   ditto



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "dybyte (via GitHub)" <gi...@apache.org>.
dybyte commented on code in PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#discussion_r2715688060


##########
seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunctionsTest.java:
##########
@@ -319,11 +319,127 @@ public void testDateAddWithUnsupportedField() {
                         new SeaTunnelDataType[] {LocalTimeType.LOCAL_DATE_TYPE});
 
         Assertions.assertThrows(
-                TransformException.class,
+                SeaTunnelRuntimeException.class,
                 () ->
                         runSql(
                                 "select DATEADD(dt, 1, 'UNSUPPORTED') as d from dual",
                                 rowType,
                                 LocalDate.of(2024, 6, 15)));

Review Comment:
   ditto



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "dybyte (via GitHub)" <gi...@apache.org>.
dybyte commented on code in PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#discussion_r2707015450


##########
seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java:
##########
@@ -633,23 +635,39 @@ public static Temporal parsedatetime(List<Object> args) {
             return null;
         }
         String format = (String) args.get(1);
-        if (format.contains("yy") && format.contains("mm")) {
-            DateTimeFormatter df = DateTimeFormatter.ofPattern(format);
-            return LocalDateTime.parse(str, df);
-        }
-        if (format.contains("yy")) {
-            DateTimeFormatter df = DateTimeFormatter.ofPattern(format);
-            return LocalDate.parse(str, df);
-        }
-        if (format.contains("mm")) {
-            DateTimeFormatter df = DateTimeFormatter.ofPattern(format);
-            return LocalTime.parse(str, df);
-        }
-        throw new TransformException(
-                CommonErrorCodeDeprecated.UNSUPPORTED_OPERATION,
-                String.format(
-                        "Unknown pattern letter %s for function: %s",
-                        format, ZetaSQLFunction.PARSEDATETIME));
+
+        ZetaDateTimeFormat dateTimeFormat =
+                ZetaDateTimeFormat.fromPattern(format)
+                        .orElseThrow(
+                                () ->
+                                        new TransformException(
+                                                CommonErrorCodeDeprecated.UNSUPPORTED_OPERATION,
+                                                String.format(
+                                                        "Unsupported datetime format: '%s'.",
+                                                        format)));
+
+        try {
+            DateTimeFormatter formatter = DateTimeFormatter.ofPattern(dateTimeFormat.getPattern());
+
+            switch (dateTimeFormat.getType()) {
+                case DATETIME:
+                    return LocalDateTime.parse(str, formatter);
+                case DATE:
+                    return LocalDate.parse(str, formatter);
+                case TIME:
+                    return LocalTime.parse(str, formatter);
+                default:
+                    throw new TransformException(
+                            CommonErrorCodeDeprecated.UNSUPPORTED_OPERATION,
+                            "Unknown format type: " + dateTimeFormat.getType());
+            }
+        } catch (DateTimeParseException e) {
+            throw new TransformException(
+                    CommonErrorCodeDeprecated.ILLEGAL_ARGUMENT,
+                    String.format(
+                            "Failed to parse '%s' with format '%s': %s",
+                            str, format, e.getMessage()));
+        }

Review Comment:
   We could use https://github.com/apache/seatunnel/blob/d14cd21276354fae956e036bb9864cedc8ca34fc/seatunnel-common/src/main/java/org/apache/seatunnel/common/exception/CommonError.java#L300-L305 here



##########
seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java:
##########
@@ -633,23 +635,39 @@ public static Temporal parsedatetime(List<Object> args) {
             return null;
         }
         String format = (String) args.get(1);
-        if (format.contains("yy") && format.contains("mm")) {
-            DateTimeFormatter df = DateTimeFormatter.ofPattern(format);
-            return LocalDateTime.parse(str, df);
-        }
-        if (format.contains("yy")) {
-            DateTimeFormatter df = DateTimeFormatter.ofPattern(format);
-            return LocalDate.parse(str, df);
-        }
-        if (format.contains("mm")) {
-            DateTimeFormatter df = DateTimeFormatter.ofPattern(format);
-            return LocalTime.parse(str, df);
-        }
-        throw new TransformException(
-                CommonErrorCodeDeprecated.UNSUPPORTED_OPERATION,
-                String.format(
-                        "Unknown pattern letter %s for function: %s",
-                        format, ZetaSQLFunction.PARSEDATETIME));
+
+        ZetaDateTimeFormat dateTimeFormat =
+                ZetaDateTimeFormat.fromPattern(format)
+                        .orElseThrow(
+                                () ->
+                                        new TransformException(
+                                                CommonErrorCodeDeprecated.UNSUPPORTED_OPERATION,
+                                                String.format(
+                                                        "Unsupported datetime format: '%s'.",
+                                                        format)));

Review Comment:
   ditto



##########
seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java:
##########
@@ -633,23 +635,39 @@ public static Temporal parsedatetime(List<Object> args) {
             return null;
         }
         String format = (String) args.get(1);
-        if (format.contains("yy") && format.contains("mm")) {
-            DateTimeFormatter df = DateTimeFormatter.ofPattern(format);
-            return LocalDateTime.parse(str, df);
-        }
-        if (format.contains("yy")) {
-            DateTimeFormatter df = DateTimeFormatter.ofPattern(format);
-            return LocalDate.parse(str, df);
-        }
-        if (format.contains("mm")) {
-            DateTimeFormatter df = DateTimeFormatter.ofPattern(format);
-            return LocalTime.parse(str, df);
-        }
-        throw new TransformException(
-                CommonErrorCodeDeprecated.UNSUPPORTED_OPERATION,
-                String.format(
-                        "Unknown pattern letter %s for function: %s",
-                        format, ZetaSQLFunction.PARSEDATETIME));
+
+        ZetaDateTimeFormat dateTimeFormat =
+                ZetaDateTimeFormat.fromPattern(format)
+                        .orElseThrow(
+                                () ->
+                                        new TransformException(
+                                                CommonErrorCodeDeprecated.UNSUPPORTED_OPERATION,
+                                                String.format(
+                                                        "Unsupported datetime format: '%s'.",
+                                                        format)));
+
+        try {
+            DateTimeFormatter formatter = DateTimeFormatter.ofPattern(dateTimeFormat.getPattern());
+
+            switch (dateTimeFormat.getType()) {
+                case DATETIME:
+                    return LocalDateTime.parse(str, formatter);
+                case DATE:
+                    return LocalDate.parse(str, formatter);
+                case TIME:
+                    return LocalTime.parse(str, formatter);
+                default:
+                    throw new TransformException(
+                            CommonErrorCodeDeprecated.UNSUPPORTED_OPERATION,
+                            "Unknown format type: " + dateTimeFormat.getType());

Review Comment:
   ditto



##########
seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLType.java:
##########
@@ -430,21 +430,41 @@ private SeaTunnelDataType<?> getFunctionType(Function function) {
             case ZetaSQLFunction.PARSEDATETIME:
             case ZetaSQLFunction.TO_DATE:
                 {
-                    String format = function.getParameters().getExpressions().get(1).toString();
-                    if (format.contains("yy") && format.contains("mm")) {
-                        return LocalTimeType.LOCAL_DATE_TIME_TYPE;
+                    Expression formatExpr = function.getParameters().getExpressions().get(1);
+                    String format;
+                    if (formatExpr instanceof StringValue) {
+                        format = ((StringValue) formatExpr).getNotExcapedValue();
+                    } else {
+                        throw new TransformException(
+                                CommonErrorCodeDeprecated.UNSUPPORTED_OPERATION,
+                                String.format(
+                                        "Format parameter must be a string literal for function: %s",
+                                        function.getName()));
                     }
-                    if (format.contains("yy")) {
-                        return LocalTimeType.LOCAL_DATE_TYPE;
-                    }
-                    if (format.contains("mm")) {
-                        return LocalTimeType.LOCAL_TIME_TYPE;
+
+                    ZetaDateTimeFormat dateTimeFormat =
+                            ZetaDateTimeFormat.fromPattern(format)
+                                    .orElseThrow(
+                                            () ->
+                                                    new TransformException(
+                                                            CommonErrorCodeDeprecated
+                                                                    .UNSUPPORTED_OPERATION,
+                                                            String.format(
+                                                                    "Unsupported datetime format: '%s'.",
+                                                                    format)));

Review Comment:
   We could use https://github.com/apache/seatunnel/blob/d14cd21276354fae956e036bb9864cedc8ca34fc/seatunnel-common/src/main/java/org/apache/seatunnel/common/exception/CommonError.java#L213-L219 here



##########
seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLType.java:
##########
@@ -430,21 +430,41 @@ private SeaTunnelDataType<?> getFunctionType(Function function) {
             case ZetaSQLFunction.PARSEDATETIME:
             case ZetaSQLFunction.TO_DATE:
                 {
-                    String format = function.getParameters().getExpressions().get(1).toString();
-                    if (format.contains("yy") && format.contains("mm")) {
-                        return LocalTimeType.LOCAL_DATE_TIME_TYPE;
+                    Expression formatExpr = function.getParameters().getExpressions().get(1);
+                    String format;
+                    if (formatExpr instanceof StringValue) {
+                        format = ((StringValue) formatExpr).getNotExcapedValue();
+                    } else {
+                        throw new TransformException(
+                                CommonErrorCodeDeprecated.UNSUPPORTED_OPERATION,
+                                String.format(
+                                        "Format parameter must be a string literal for function: %s",
+                                        function.getName()));
                     }
-                    if (format.contains("yy")) {
-                        return LocalTimeType.LOCAL_DATE_TYPE;
-                    }
-                    if (format.contains("mm")) {
-                        return LocalTimeType.LOCAL_TIME_TYPE;
+
+                    ZetaDateTimeFormat dateTimeFormat =
+                            ZetaDateTimeFormat.fromPattern(format)
+                                    .orElseThrow(
+                                            () ->
+                                                    new TransformException(
+                                                            CommonErrorCodeDeprecated
+                                                                    .UNSUPPORTED_OPERATION,
+                                                            String.format(
+                                                                    "Unsupported datetime format: '%s'.",
+                                                                    format)));
+
+                    switch (dateTimeFormat.getType()) {
+                        case DATETIME:
+                            return LocalTimeType.LOCAL_DATE_TIME_TYPE;
+                        case DATE:
+                            return LocalTimeType.LOCAL_DATE_TYPE;
+                        case TIME:
+                            return LocalTimeType.LOCAL_TIME_TYPE;
+                        default:
+                            throw new TransformException(
+                                    CommonErrorCodeDeprecated.UNSUPPORTED_OPERATION,
+                                    "Unknown format type: " + dateTimeFormat.getType());

Review Comment:
   ditto



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "yzeng1618 (via GitHub)" <gi...@apache.org>.
yzeng1618 commented on PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#issuecomment-3793795949

   https://github.com/apache/seatunnel/blob/dev/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java 
   
   ```
   public static boolean isDate(List<Object> args) {
           try {
               parsedatetime(args);
               return true;
           } catch (Throwable e) {
               return false;
           }
       }
   In the code, IS_DATE currently directly reuses parsedatetime, so the question is whether we need to maintain compatibility for this part or modify the documentation.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "aksmf1442 (via GitHub)" <gi...@apache.org>.
aksmf1442 commented on PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#issuecomment-3799284849

   > Hi @aksmf1442 , Good job. I found there is one issue you can do it better ^_^
   > 
   > ### Performance Bottleneck
   > **Description**: The code calls `DateTimeFormatter.ofPattern(dateTimeFormat.getPattern())` inside the `parsedatetime` method. Since this UDF executes **per row**, compiling the pattern every time is extremely inefficient for high-throughput scenarios. **Location**: `seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java` (Line ~646) **Recommendation**: Cache the `DateTimeFormatter` instance within the `ZetaDateTimeFormat` enum. `DateTimeFormatter` is thread-safe and should be created only once.
   > 
   > **Suggested Change**: Refactor `ZetaDateTimeFormat` to initialize the formatter in its constructor:
   > 
   > ```java
   > public enum ZetaDateTimeFormat {
   >     // ...
   >     private final DateTimeFormatter formatter;
   > 
   >     ZetaDateTimeFormat(String pattern, FormatType type) {
   >         this.pattern = pattern;
   >         this.type = type;
   >         this.formatter = DateTimeFormatter.ofPattern(pattern); // Initialize once
   >     }
   >     
   >     public DateTimeFormatter getFormatter() { return formatter; }
   > }
   > ```
   > 
   > Then use `dateTimeFormat.getFormatter()` in the function.
   > 
   > By the way, there is a smaill Issue: Whitelist Too Strict **Description**: The current whitelist misses common formats like compact dates (`yyyyMMdd`) or slash separators (`yyyy/MM/dd`). **Recommendation**: It's better if you can expand the whitelist to include these common variations to reduce migration friction for users.
   
   @davidzollo 
   Thank you for the valuable suggestions. I’ll reflect them in t


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "DanielCarter-stack (via GitHub)" <gi...@apache.org>.
DanielCarter-stack commented on PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#issuecomment-3764429339

   <!-- code-pr-reviewer -->
   Thanks for this enhancement! Here are some items to address before merge:
   
   **BLOCKER**
   
   * `docs/en/introduction/concepts/incompatible-changes.md:16-18` - This PR introduces a breaking change (restricting datetime format to whitelist) but doesn't document it in incompatible-changes.md. Users' existing PARSEDATETIME/TO_DATE queries will fail at runtime after upgrade without any migration guide.
   
   **MAJOR**
   
   * `seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLType.java:433-436` - Uses `expression.toString()` then manual regex `replaceAll("^'|'$", "")` instead of JSQLParser's dedicated API like `StringValue.getNotExcapedValue()`. This depends on implementation details and may break with future versions. Consider using type-specific value extraction APIs.
   
   * `seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLType.java:433-449` - Assumes format is a string literal. If format is a column expression, `toString()` returns the column name instead of a format string, causing validation failure with unclear error messages. Please add type checking and provide clear error when format is not a literal.
   
   **MINOR**
   
   * `seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLType.java:447-449` vs `DateTimeFunction.java:653-655` - Inconsistent error messages: "Unknown pattern letter %s for function: %s" vs "Unsupported datetime format: '%s'." The former is inaccurate since entire patterns are rejected, not individual letters.
   
   * `seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java:638-641` - Redundant null check before `fromPattern(format)`, since `fromPattern(null)` returns `Optional.empty()` which is handled by `orElseThrow()`.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "dybyte (via GitHub)" <gi...@apache.org>.
dybyte commented on PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#issuecomment-3765061005

   Please enable CI following the [instructions](https://github.com/apache/seatunnel/pull/10360/checks?check_run_id=60683934901).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "dybyte (via GitHub)" <gi...@apache.org>.
dybyte commented on code in PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#discussion_r2715683902


##########
seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLTypeTest.java:
##########
@@ -338,7 +339,7 @@ public void testCoalesceMultiIfModAndUdfTypes() {
         Function badCoalesce = new Function();
         badCoalesce.setName(ZetaSQLFunction.COALESCE);
         Assertions.assertThrows(
-                TransformException.class, () -> type.getExpressionType(badCoalesce));
+                SeaTunnelRuntimeException.class, () -> type.getExpressionType(badCoalesce));

Review Comment:
   I don’t think this test is affected by the current PR. Is it related?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "dybyte (via GitHub)" <gi...@apache.org>.
dybyte commented on PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#issuecomment-3784639362

   Could you please resolve the merge conflicts?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "aksmf1442 (via GitHub)" <gi...@apache.org>.
aksmf1442 commented on PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#issuecomment-3765180641

   > Thanks for this enhancement! Here are some items to address before merge:
   > 
   > **BLOCKER**
   > 
   > * `docs/en/introduction/concepts/incompatible-changes.md:16-18` - This PR introduces a breaking change (restricting datetime format to whitelist) but doesn't document it in incompatible-changes.md. Users' existing PARSEDATETIME/TO_DATE queries will fail at runtime after upgrade without any migration guide.
   > 
   > **MAJOR**
   > 
   > * `seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLType.java:433-436` - Uses `expression.toString()` then manual regex `replaceAll("^'|'$", "")` instead of JSQLParser's dedicated API like `StringValue.getNotExcapedValue()`. This depends on implementation details and may break with future versions. Consider using type-specific value extraction APIs.
   > * `seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLType.java:433-449` - Assumes format is a string literal. If format is a column expression, `toString()` returns the column name instead of a format string, causing validation failure with unclear error messages. Please add type checking and provide clear error when format is not a literal.
   > 
   > **MINOR**
   > 
   > * `seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLType.java:447-449` vs `DateTimeFunction.java:653-655` - Inconsistent error messages: "Unknown pattern letter %s for function: %s" vs "Unsupported datetime format: '%s'." The former is inaccurate since entire patterns are rejected, not individual letters.
   > * `seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java:638-641` - Redundant null check before `fromPattern(format)`, since `fromPattern(null)` returns `Optional.empty()` which is handled by `orElseThrow()`.
   
   @DanielCarter-stack 
   Thanks for the review. All the raised items have been addressed.  
   Could you please take another look when you have a chance?
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "dybyte (via GitHub)" <gi...@apache.org>.
dybyte commented on code in PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#discussion_r2707044584


##########
seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunctionsTest.java:
##########
@@ -326,4 +326,143 @@ public void testDateAddWithUnsupportedField() {
                                 rowType,
                                 LocalDate.of(2024, 6, 15)));
     }
+
+    @Test
+    public void testParseDateTimeWithAllDateTimeFormats() {
+        SeaTunnelRowType rowType =
+                new SeaTunnelRowType(
+                        new String[] {"dummy"},
+                        new SeaTunnelDataType[] {LocalTimeType.LOCAL_DATE_TIME_TYPE});
+
+        // DATETIME_STANDARD: yyyy-MM-dd HH:mm:ss
+        SeaTunnelRow row1 =
+                runSql(
+                        "select PARSEDATETIME('2024-06-15 14:30:45', 'yyyy-MM-dd HH:mm:ss') as dt from dual",
+                        rowType,
+                        LocalDateTime.now());
+        Assertions.assertEquals(LocalDateTime.of(2024, 6, 15, 14, 30, 45), row1.getField(0));
+
+        // DATETIME_WITH_MILLIS: yyyy-MM-dd HH:mm:ss.SSS
+        SeaTunnelRow row2 =
+                runSql(
+                        "select PARSEDATETIME('2024-06-15 14:30:45.123', 'yyyy-MM-dd HH:mm:ss.SSS') as dt from dual",
+                        rowType,
+                        LocalDateTime.now());
+        Assertions.assertEquals(
+                LocalDateTime.of(2024, 6, 15, 14, 30, 45, 123000000), row2.getField(0));
+
+        // DATETIME_ISO8601: yyyy-MM-dd'T'HH:mm:ss
+        SeaTunnelRow row3 =
+                runSql(
+                        "select PARSEDATETIME('2024-06-15T14:30:45', 'yyyy-MM-dd''T''HH:mm:ss') as dt from dual",
+                        rowType,
+                        LocalDateTime.now());
+        Assertions.assertEquals(LocalDateTime.of(2024, 6, 15, 14, 30, 45), row3.getField(0));
+
+        // DATETIME_ISO8601_WITH_MILLIS: yyyy-MM-dd'T'HH:mm:ss.SSS
+        SeaTunnelRow row4 =
+                runSql(
+                        "select PARSEDATETIME('2024-06-15T14:30:45.987', 'yyyy-MM-dd''T''HH:mm:ss.SSS') as dt from dual",
+                        rowType,
+                        LocalDateTime.now());
+        Assertions.assertEquals(
+                LocalDateTime.of(2024, 6, 15, 14, 30, 45, 987000000), row4.getField(0));
+    }
+
+    @Test
+    public void testParseDateTimeWithAllDateFormats() {
+        SeaTunnelRowType rowType =
+                new SeaTunnelRowType(
+                        new String[] {"dummy"},
+                        new SeaTunnelDataType[] {LocalTimeType.LOCAL_DATE_TIME_TYPE});
+
+        // DATE_ISO8601: yyyy-MM-dd
+        SeaTunnelRow row1 =
+                runSql(
+                        "select PARSEDATETIME('2024-06-15', 'yyyy-MM-dd') as dt from dual",
+                        rowType,
+                        LocalDateTime.now());
+        Assertions.assertEquals(LocalDate.of(2024, 6, 15), row1.getField(0));
+    }
+
+    @Test
+    public void testParseDateTimeWithAllTimeFormats() {
+        SeaTunnelRowType rowType =
+                new SeaTunnelRowType(
+                        new String[] {"dummy"},
+                        new SeaTunnelDataType[] {LocalTimeType.LOCAL_DATE_TIME_TYPE});
+
+        // TIME_STANDARD: HH:mm:ss
+        SeaTunnelRow row1 =
+                runSql(
+                        "select PARSEDATETIME('14:30:45', 'HH:mm:ss') as dt from dual",
+                        rowType,
+                        LocalDateTime.now());
+        Assertions.assertEquals(java.time.LocalTime.of(14, 30, 45), row1.getField(0));
+
+        // TIME_WITH_MILLIS: HH:mm:ss.SSS
+        SeaTunnelRow row2 =
+                runSql(
+                        "select PARSEDATETIME('14:30:45.123', 'HH:mm:ss.SSS') as dt from dual",
+                        rowType,
+                        LocalDateTime.now());
+        Assertions.assertEquals(java.time.LocalTime.of(14, 30, 45, 123000000), row2.getField(0));
+    }
+
+    @Test
+    public void testParseDateTimeWithUnsupportedFormat() {
+        SeaTunnelRowType rowType =
+                new SeaTunnelRowType(
+                        new String[] {"dummy"},
+                        new SeaTunnelDataType[] {LocalTimeType.LOCAL_DATE_TIME_TYPE});
+
+        // Test with completely unsupported format
+        Assertions.assertThrows(
+                TransformException.class,
+                () ->
+                        runSql(
+                                "select PARSEDATETIME('2024-06-15', 'dd/MM/yyyy') as dt from dual",
+                                rowType,
+                                LocalDateTime.now()));
+    }
+
+    @Test
+    public void testParseDateTimeWithMalformedInput() {
+        SeaTunnelRowType rowType =
+                new SeaTunnelRowType(
+                        new String[] {"dummy"},
+                        new SeaTunnelDataType[] {LocalTimeType.LOCAL_DATE_TIME_TYPE});
+
+        // Test with malformed input string
+        Assertions.assertThrows(
+                TransformException.class,
+                () ->
+                        runSql(
+                                "select PARSEDATETIME('not-a-date', 'yyyy-MM-dd') as dt from dual",
+                                rowType,
+                                LocalDateTime.now()));
+    }
+
+    @Test
+    public void testToDateWithVariousFormats() {
+        SeaTunnelRowType rowType =
+                new SeaTunnelRowType(
+                        new String[] {"dummy"},
+                        new SeaTunnelDataType[] {LocalTimeType.LOCAL_DATE_TIME_TYPE});
+
+        // TO_DATE is an alias for PARSEDATETIME
+        SeaTunnelRow row1 =
+                runSql(
+                        "select TO_DATE('2024-06-15', 'yyyy-MM-dd') as dt from dual",
+                        rowType,
+                        LocalDateTime.now());
+        Assertions.assertEquals(LocalDate.of(2024, 6, 15), row1.getField(0));
+
+        SeaTunnelRow row2 =
+                runSql(
+                        "select TO_DATE('2024-06-15 14:30:45', 'yyyy-MM-dd HH:mm:ss') as dt from dual",
+                        rowType,
+                        LocalDateTime.now());
+        Assertions.assertEquals(LocalDateTime.of(2024, 6, 15, 14, 30, 45), row2.getField(0));
+    }

Review Comment:
   It seems to be duplicated with `testParseDateTimeWithAllDateFormats()`, `testParseDateTimeWithAllTimeFormats()`.



##########
seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaDateTimeFormatTest.java:
##########
@@ -0,0 +1,177 @@
+/*
+ * 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;
+
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+import java.util.Optional;
+
+public class ZetaDateTimeFormatTest {
+
+    @Test
+    public void testFromPatternWithAllDateTimeFormats() {
+        // DATETIME_STANDARD
+        Optional<ZetaDateTimeFormat> format1 =
+                ZetaDateTimeFormat.fromPattern("yyyy-MM-dd HH:mm:ss");
+        Assertions.assertTrue(format1.isPresent());
+        Assertions.assertEquals(ZetaDateTimeFormat.DATETIME_STANDARD, format1.get());
+        Assertions.assertEquals(ZetaDateTimeFormat.FormatType.DATETIME, format1.get().getType());
+
+        // DATETIME_WITH_MILLIS
+        Optional<ZetaDateTimeFormat> format2 =
+                ZetaDateTimeFormat.fromPattern("yyyy-MM-dd HH:mm:ss.SSS");
+        Assertions.assertTrue(format2.isPresent());
+        Assertions.assertEquals(ZetaDateTimeFormat.DATETIME_WITH_MILLIS, format2.get());
+        Assertions.assertEquals(ZetaDateTimeFormat.FormatType.DATETIME, format2.get().getType());
+
+        // DATETIME_ISO8601
+        Optional<ZetaDateTimeFormat> format3 =
+                ZetaDateTimeFormat.fromPattern("yyyy-MM-dd'T'HH:mm:ss");
+        Assertions.assertTrue(format3.isPresent());
+        Assertions.assertEquals(ZetaDateTimeFormat.DATETIME_ISO8601, format3.get());
+        Assertions.assertEquals(ZetaDateTimeFormat.FormatType.DATETIME, format3.get().getType());
+
+        // DATETIME_ISO8601_WITH_MILLIS
+        Optional<ZetaDateTimeFormat> format4 =
+                ZetaDateTimeFormat.fromPattern("yyyy-MM-dd'T'HH:mm:ss.SSS");
+        Assertions.assertTrue(format4.isPresent());
+        Assertions.assertEquals(ZetaDateTimeFormat.DATETIME_ISO8601_WITH_MILLIS, format4.get());
+        Assertions.assertEquals(ZetaDateTimeFormat.FormatType.DATETIME, format4.get().getType());
+    }
+
+    @Test
+    public void testFromPatternWithAllDateFormats() {
+        // DATE_ISO8601
+        Optional<ZetaDateTimeFormat> format1 = ZetaDateTimeFormat.fromPattern("yyyy-MM-dd");
+        Assertions.assertTrue(format1.isPresent());
+        Assertions.assertEquals(ZetaDateTimeFormat.DATE_ISO8601, format1.get());
+        Assertions.assertEquals(ZetaDateTimeFormat.FormatType.DATE, format1.get().getType());
+    }
+
+    @Test
+    public void testFromPatternWithAllTimeFormats() {
+        // TIME_STANDARD
+        Optional<ZetaDateTimeFormat> format1 = ZetaDateTimeFormat.fromPattern("HH:mm:ss");
+        Assertions.assertTrue(format1.isPresent());
+        Assertions.assertEquals(ZetaDateTimeFormat.TIME_STANDARD, format1.get());
+        Assertions.assertEquals(ZetaDateTimeFormat.FormatType.TIME, format1.get().getType());
+
+        // TIME_WITH_MILLIS
+        Optional<ZetaDateTimeFormat> format2 = ZetaDateTimeFormat.fromPattern("HH:mm:ss.SSS");
+        Assertions.assertTrue(format2.isPresent());
+        Assertions.assertEquals(ZetaDateTimeFormat.TIME_WITH_MILLIS, format2.get());
+        Assertions.assertEquals(ZetaDateTimeFormat.FormatType.TIME, format2.get().getType());
+    }
+
+    @Test
+    public void testFromPatternWithInvalidFormat() {
+        Optional<ZetaDateTimeFormat> format = ZetaDateTimeFormat.fromPattern("invalid_pattern");
+
+        Assertions.assertFalse(format.isPresent());
+    }
+
+    @Test
+    public void testFromPatternWithNullFormat() {
+        Optional<ZetaDateTimeFormat> format = ZetaDateTimeFormat.fromPattern(null);
+
+        Assertions.assertFalse(format.isPresent());
+    }
+
+    @Test
+    public void testAllDateTimeFormatsHaveCorrectType() {
+        Assertions.assertEquals(
+                ZetaDateTimeFormat.FormatType.DATETIME,
+                ZetaDateTimeFormat.DATETIME_STANDARD.getType());
+        Assertions.assertEquals(
+                ZetaDateTimeFormat.FormatType.DATETIME,
+                ZetaDateTimeFormat.DATETIME_WITH_MILLIS.getType());
+        Assertions.assertEquals(
+                ZetaDateTimeFormat.FormatType.DATETIME,
+                ZetaDateTimeFormat.DATETIME_ISO8601.getType());
+        Assertions.assertEquals(
+                ZetaDateTimeFormat.FormatType.DATETIME,
+                ZetaDateTimeFormat.DATETIME_ISO8601_WITH_MILLIS.getType());
+    }
+
+    @Test
+    public void testAllDateFormatsHaveCorrectType() {
+        Assertions.assertEquals(
+                ZetaDateTimeFormat.FormatType.DATE, ZetaDateTimeFormat.DATE_ISO8601.getType());
+    }
+
+    @Test
+    public void testAllTimeFormatsHaveCorrectType() {
+        Assertions.assertEquals(
+                ZetaDateTimeFormat.FormatType.TIME, ZetaDateTimeFormat.TIME_STANDARD.getType());
+        Assertions.assertEquals(
+                ZetaDateTimeFormat.FormatType.TIME, ZetaDateTimeFormat.TIME_WITH_MILLIS.getType());
+    }
+
+    @Test
+    public void testGetPatternForAllFormats() {
+        Assertions.assertEquals(
+                "yyyy-MM-dd HH:mm:ss", ZetaDateTimeFormat.DATETIME_STANDARD.getPattern());
+        Assertions.assertEquals(
+                "yyyy-MM-dd HH:mm:ss.SSS", ZetaDateTimeFormat.DATETIME_WITH_MILLIS.getPattern());
+        Assertions.assertEquals(
+                "yyyy-MM-dd'T'HH:mm:ss", ZetaDateTimeFormat.DATETIME_ISO8601.getPattern());
+        Assertions.assertEquals(
+                "yyyy-MM-dd'T'HH:mm:ss.SSS",
+                ZetaDateTimeFormat.DATETIME_ISO8601_WITH_MILLIS.getPattern());
+
+        Assertions.assertEquals("yyyy-MM-dd", ZetaDateTimeFormat.DATE_ISO8601.getPattern());
+
+        Assertions.assertEquals("HH:mm:ss", ZetaDateTimeFormat.TIME_STANDARD.getPattern());
+        Assertions.assertEquals("HH:mm:ss.SSS", ZetaDateTimeFormat.TIME_WITH_MILLIS.getPattern());
+    }
+
+    @Test
+    public void testFromPatternWithISO8601Formats() {
+        Optional<ZetaDateTimeFormat> format1 =
+                ZetaDateTimeFormat.fromPattern("yyyy-MM-dd'T'HH:mm:ss");
+        Assertions.assertTrue(format1.isPresent());
+        Assertions.assertEquals(ZetaDateTimeFormat.DATETIME_ISO8601, format1.get());
+
+        Optional<ZetaDateTimeFormat> format2 =
+                ZetaDateTimeFormat.fromPattern("yyyy-MM-dd'T'HH:mm:ss.SSS");
+        Assertions.assertTrue(format2.isPresent());
+        Assertions.assertEquals(ZetaDateTimeFormat.DATETIME_ISO8601_WITH_MILLIS, format2.get());
+    }

Review Comment:
   It seems to be duplicated with `testFromPatternWithAllDateTimeFormats()`.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "aksmf1442 (via GitHub)" <gi...@apache.org>.
aksmf1442 commented on PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#issuecomment-3784035377

   @dybyte 
   Sorry for touching unrelated test code. I’ve limited the changes to only the relevant code and have also updated the documentation


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "aksmf1442 (via GitHub)" <gi...@apache.org>.
aksmf1442 commented on PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#issuecomment-3778003217

   @dybyte 
   Removed duplicated test code and refactored to use a common error-handling utility!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "aksmf1442 (via GitHub)" <gi...@apache.org>.
aksmf1442 commented on code in PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#discussion_r2715800074


##########
seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLTypeTest.java:
##########
@@ -338,7 +339,7 @@ public void testCoalesceMultiIfModAndUdfTypes() {
         Function badCoalesce = new Function();
         badCoalesce.setName(ZetaSQLFunction.COALESCE);
         Assertions.assertThrows(
-                TransformException.class, () -> type.getExpressionType(badCoalesce));
+                SeaTunnelRuntimeException.class, () -> type.getExpressionType(badCoalesce));

Review Comment:
   The test build succeeded in the local environment, but the tests failed in GitHub Actions after deployment. I made a change thinking it might be related, but it doesn’t seem to be, so I will roll it back.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "davidzollo (via GitHub)" <gi...@apache.org>.
davidzollo commented on PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#issuecomment-3796509819

   Good job.
   I found there is one issue you can do it better ^_^
   ### Performance Bottleneck
   **Location**: `seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java` (Line ~646)
   **Description**: The code calls `DateTimeFormatter.ofPattern(dateTimeFormat.getPattern())` inside the `parsedatetime` method. Since this UDF executes **per row**, compiling the pattern every time is extremely inefficient for high-throughput scenarios.
   **Recommendation**: 
   Cache the `DateTimeFormatter` instance within the `ZetaDateTimeFormat` enum. `DateTimeFormatter` is thread-safe and should be created only once.
   
   **Suggested Change**:
   Refactor `ZetaDateTimeFormat` to initialize the formatter in its constructor:
   ```java
   public enum ZetaDateTimeFormat {
       // ...
       private final DateTimeFormatter formatter;
   
       ZetaDateTimeFormat(String pattern, FormatType type) {
           this.pattern = pattern;
           this.type = type;
           this.formatter = DateTimeFormatter.ofPattern(pattern); // Initialize once
       }
       
       public DateTimeFormatter getFormatter() { return formatter; }
   }
   ```
   Then use `dateTimeFormat.getFormatter()` in the function.
   
   By the way, there is a smaill Issue: Whitelist Too Strict 
   **Description**: The current whitelist misses common formats like compact dates (`yyyyMMdd`) or slash separators (`yyyy/MM/dd`).
   **Recommendation**: Expand the whitelist to include these common variations to reduce migration friction for users.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "aksmf1442 (via GitHub)" <gi...@apache.org>.
aksmf1442 commented on PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#issuecomment-3799846396

   > > https://github.com/apache/seatunnel/blob/dev/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java
   > > ```
   > > public static boolean isDate(List<Object> args) {
   > >         try {
   > >             parsedatetime(args);
   > >             return true;
   > >         } catch (Throwable e) {
   > >             return false;
   > >         }
   > >     }
   > > ```
   > > 
   > > 
   > >     
   > >       
   > >     
   > > 
   > >       
   > >     
   > > 
   > >     
   > >   
   > > 현재 코드에서 IS_DATE는 parsedatetime을 직접 재사용하고 있으므로, 이 부분에 대한 호환성을 유지해야 할지 아니면 문서를 수정해야 할지가 문제입니다.
   > 
   > @yzeng1618 It would be good to document this behavior. Since **'isDate'** reuses **'parsedatetime'**, it is subject to the same whitelist constraints. This ensures consistent behavior for patterns like **'CASE WHEN IS_DATE(s, format) THEN TO_DATE(s, format)'**.
   
   @
   
   > > https://github.com/apache/seatunnel/blob/dev/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java
   > > ```
   > > public static boolean isDate(List<Object> args) {
   > >         try {
   > >             parsedatetime(args);
   > >             return true;
   > >         } catch (Throwable e) {
   > >             return false;
   > >         }
   > >     }
   > > ```
   > > 
   > > 
   > >     
   > >       
   > >     
   > > 
   > >       
   > >     
   > > 
   > >     
   > >   
   > > In the code, IS_DATE currently directly reuses parsedatetime, so the question is whether we need to maintain compatibility for this part or modify the documentation.
   > 
   > @yzeng1618 It would be good to document this behavior. Since **'isDate'** reuses **'parsedatetime'**, it is subject to the same whitelist constraints. This ensures consistent behavior for patterns like **'CASE WHEN IS_DATE(s, format) THEN TO_DATE(s, format)'**.
   
   @yzeng1618 
   Updated the documentation to clarify that IS_DATE reuses parsedatetime and therefore shares the same whitelist constraints.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] [Improve][Transform-V2] Add whitelist-based validation for datetime format patterns in PARSEDATETIME/TO_DATE [seatunnel]

Posted by "aksmf1442 (via GitHub)" <gi...@apache.org>.
aksmf1442 commented on PR #10360:
URL: https://github.com/apache/seatunnel/pull/10360#issuecomment-3799275758

   > https://github.com/apache/seatunnel/blob/dev/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java
   > 
   > ```
   > public static boolean isDate(List<Object> args) {
   >         try {
   >             parsedatetime(args);
   >             return true;
   >         } catch (Throwable e) {
   >             return false;
   >         }
   >     }
   > ```
   > 
   > 현재 코드에서 IS_DATE는 parsedatetime을 직접 재사용하고 있으므로, 이 부분에 대한 호환성을 유지해야 할지 아니면 문서를 수정해야 할지가 문제입니다.
   
   @yzeng1618 
   It would be good to document this behavior. Since **'isDate'** reuses **'parsedatetime'**, it is subject to the same whitelist constraints. This ensures consistent behavior for patterns like **'CASE WHEN IS_DATE(s, format) THEN TO_DATE(s, format)'**.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org