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]