andygrove opened a new issue, #229:
URL: https://github.com/apache/datafusion-spark/issues/229

   Part of #175 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `to_date(date_str[, fmt])` returns a `DATE`. `try_to_date` is the same, 
except that invalid input always gives NULL,
   even in ANSI mode. `to_date` since 1.5.0. `try_to_date` is annotated `since 
= "4.0.0"`, but it is only registered in
   `FunctionRegistry` from 4.1.
   
   `ParseToDate` is `RuntimeReplaceable`:
   - **Without `fmt`**: `CAST(date_str AS DATE)`. The eval mode is ANSI when 
ANSI is on; never ANSI for `try_`. The
     one-argument form therefore needs Spark's string-to-date cast rules: 
whitespace is trimmed, `yyyy`,
     `yyyy-[m]m`, `yyyy-[m]m-[d]d` are accepted, and a trailing time part after 
`T` or space is ignored.
   - **With `fmt`**: `CAST(GetTimestamp(date_str, fmt, TIMESTAMP) AS DATE)`. 
The string is parsed with the Spark
     datetime pattern into a `TIMESTAMP` in the session time zone, then the 
local date in the session time zone is
     taken. Pattern semantics and errors are identical to `to_timestamp`.
   
   Accepted first-argument types (`ImplicitCastInputTypes`): `STRING`, `DATE`, 
`TIMESTAMP`, `TIMESTAMP_NTZ`. `DATE`
   passes through. `TIMESTAMP` gives its local date in the session zone. 
`TIMESTAMP_NTZ` gives its wall-clock date.
   `fmt` is a `STRING`. The result is NULL if either argument is NULL.
   
   ### Examples
   
   Session time zone `America/Los_Angeles`. Outputs come from 
`ExpressionDescription`, `date.sql.out` /
   `nonansi/date.sql.out` and `datetime-parsing-invalid.sql.out`.
   
   ```sql
   SELECT to_date('2009-07-30 04:17:52');
   -- 2009-07-30
   SELECT to_date(null), to_date('2016-12-31'), to_date('2016-12-31', 
'yyyy-MM-dd');
   -- NULL      2016-12-31      2016-12-31
   SELECT to_date('16', 'dd');
   -- 1970-01-16
   SELECT to_date('02-29', 'MM-dd');
   -- ANSI on : CANNOT_PARSE_TIMESTAMP, message "Invalid date 'February 29' as 
'1970' is not a leap year", func `try_to_date`
   -- ANSI off: NULL
   SELECT to_date('2020-01-27T20:06:11.847', 'yyyy-MM-dd HH:mm:ss.SSS');
   -- ANSI on : CANNOT_PARSE_TIMESTAMP, "Text '2020-01-27T20:06:11.847' could 
not be parsed at index 10"
   -- ANSI off: NULL
   SELECT to_date('26/October/2015', 'dd/MMMMM/yyyy');
   -- SparkUpgradeException 
INCONSISTENT_BEHAVIOR_CROSS_VERSION.DATETIME_PATTERN_RECOGNITION
   SELECT try_to_date('2016-12-31'), try_to_date('2016-12-31', 'yyyy-MM-dd'), 
try_to_date('foo', 'yyyy-MM-dd');
   -- 2016-12-31        2016-12-31      NULL
   ```
   
   ### Edge cases to test
   
   - NULL in each argument; column formats.
   - One-argument form (cast rules): `'2016-12-31 '` and `' 2016-12-31'` 
(trimmed), `'2016'`, `'2016-7'`,
     `'2016-7-3'`, `'2016-12-31T10:00'`, `'2016-12-31 garbage'`, 
`'2016-13-01'`, `''`, signed and 5+ digit years. With
     ANSI on, invalid strings raise `CAST_INVALID_INPUT` (target `"DATE"`). 
With ANSI off, and always for `try_`, they
     give NULL.
   - Formatted form: every `to_timestamp` parsing rule applies. Fields default 
to 1970-01-01; `D` must be consistent
     with `M`/`d`; `yy` uses base 2000; the whole input must be consumed; `E F 
q Q` are not allowed when parsing;
     week-based letters are rejected; `S` fraction width limits apply. Time 
fields in the pattern are parsed and then
     discarded by the cast.
   - A zone or offset in the text changes the result date. The string is 
converted to an instant using the parsed
     zone, and the cast takes the date in the session zone. From code reading:
     `to_date('2020-01-01 01:00 +09:00', 'yyyy-MM-dd HH:mm XXX')` in 
`America/Los_Angeles` gives `2019-12-31`.
   - DST: a local midnight inside a gap (zones whose DST starts at 00:00) is 
shifted forward but stays on the same
     date. Test a few such zones anyway.
   - `TIMESTAMP` input near midnight under different session zones; 
`TIMESTAMP_NTZ` input ignores the zone.
   - Year range: dates beyond 9999 and negative years via `y` (rewritten to 
`u`). The overflow
     `java.lang.ArithmeticException: long overflow` is raised by the 
intermediate `TIMESTAMP` parse (e.g. a year of
     294248 with `y`), even with ANSI off or `try_`.
   - Pattern errors (`SparkUpgradeException` `DATETIME_PATTERN_RECOGNITION`, 
`INVALID_DATETIME_PATTERN.*`,
     `DATETIME_WEEK_BASED_PATTERN`) are raised by `try_to_date` too.
   - ANSI on: `CANNOT_PARSE_TIMESTAMP` (SQLSTATE 22007) for formatted parse 
failures, `CAST_INVALID_INPUT` for the
     one-argument form.
   - `spark.sql.legacy.timeParserPolicy`: `EXCEPTION` (3.x default) raises 
`PARSE_DATETIME_BY_NEW_PARSER` when only
     `SimpleDateFormat` could parse the input. `LEGACY` uses 
`SimpleDateFormat`. A native kernel can target `CORRECTED`.
   
   ### Differences across Spark versions
   
   - 3.4/3.5: no `ansiEnabled` parameter. The rewrite used the session ANSI 
flag implicitly, the policy default was
     `EXCEPTION`, and the `CANNOT_PARSE_TIMESTAMP` message said `If necessary 
set "spark.sql.ansi.enabled" to "false"`.
   - 4.0: ANSI on by default; policy default `CORRECTED`; the error's `func` is 
`try_to_timestamp`; `try_to_date` is not
     registered yet.
   - 4.1+: `try_to_date` is registered; the error's `func` is `try_to_date`.
   - 4.0 widened the string arguments to collated strings. Pattern-error 
classes changed as described in the
     `date_format` issue.
   
   ### Implementation notes
   
   - Spark: 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala`,
     `ParseToDate`, `TryToDateExpressionBuilder`, `GetTimestamp`. 
String-to-date cast: `SparkDateTimeUtils.stringToDate`
     
(`sql/api/src/main/scala/org/apache/spark/sql/catalyst/util/SparkDateTimeUtils.scala`).
   - DataFusion core `to_date` takes chrono `strftime` formats, tries them in 
order, and errors on failure. Its
     one-argument parsing also differs from Spark's cast rules. Don't wrap it.
   - Implement on top of the shared Spark pattern parser proposed for 
`to_timestamp` (parse to session-zone
     microseconds, then floor-divide to local days in the session zone). Use 
the same verified native subset and the
     same "unsupported pattern" fallback signal. A Spark string-to-date cast 
helper (missing from this crate today) is
     needed for the one-argument form, and is also useful for `date_format` / 
`date_add` string inputs.
   - Put it in `crates/spark/src/function/datetime/`. Session zone from 
`datafusion.execution.time_zone` via
     `SparkTimeZone` (`timezone.rs`), ANSI from 
`datafusion.execution.enable_ansi_mode`.
   - Tests: `crates/sqllogictest/test_files/datetime/to_date.slt` exists but 
every query is commented out (ported
     placeholder). Uncomment and expand it, and add `try_to_date.slt`. Document 
with `#[user_doc]`.
   
   ---
   _This issue was generated by an LLM (Claude) from the Apache Spark source 
code and tests. Examples come from Spark's `ExpressionDescription` examples, 
test suites and `.sql.out` golden files, but please verify them against Spark 
before relying on them._


-- 
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: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to