[
https://issues.apache.org/jira/browse/SPARK-60499?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18130657#comment-18130657
]
Shuheng Ma commented on SPARK-60499:
------------------------------------
Hi all, I have a related question about pandas < 3, which this ticket doesn't
change. On pandas < 3, {{datetime64\[ns\]}} can't hold these values, so both
the Arrow and non-Arrow {{toPandas()}} fail on {{9999-12-31}} (ArrowInvalid /
OutOfBoundsDatetime). Users have run into this, for example:
* [https://stackoverflow.com/q/70519461] (Databricks, Arrow, ArrowInvalid)
* [https://stackoverflow.com/q/78123175] (Databricks, Arrow, ArrowInvalid on
9999-12-31)
* [https://stackoverflow.com/q/67085412] (Synapse, Arrow, ArrowInvalid)
* [https://stackoverflow.com/q/66320966] (non-Arrow, OutOfBoundsDatetime)
{{df.toArrow().to_pandas()}} already works there (Spark 4.0+ with pyarrow 13+):
it returns microsecond values with the timestamps intact. TimestampType columns
come back tz-aware in UTC ({{datetime64\[us, UTC\]}}) rather than as naive
session-local times, though.
Separately, on pandas < 3 the Arrow path silently returns {{2262-04-11
23:34:31.709551616}} for {{1677-09-21 00:00}} in America/Los_Angeles, where the
non-Arrow path raises OutOfBoundsDatetime.
Would a clearer error on pandas < 3 be welcome, one that names the column and
mentions that workaround? Or would you rather keep the current ArrowInvalid /
OutOfBoundsDatetime errors? Either way I'd keep the {{datetime64\[ns\]}} output
as it is. If a clearer error makes sense, I'm happy to file a separate ticket
for it. Thanks!
> toPandas with Arrow fails or returns wrong values for timestamps outside the
> datetime64[ns] range with pandas 3
> ---------------------------------------------------------------------------------------------------------------
>
> Key: SPARK-60499
> URL: https://issues.apache.org/jira/browse/SPARK-60499
> Project: Spark
> Issue Type: Sub-task
> Components: PySpark
> Affects Versions: 5.0.0
> Reporter: Shuheng Ma
> Priority: Major
>
> h3. Description
> With pandas 3 and Arrow enabled (the default since Spark 4.2), {{toPandas()}}
> fails on timestamps after 2262-04-11, such as the common sentinel
> {{9999-12-31}}. Values that fit in {{datetime64\[ns\]}} as UTC but not as
> local time, such as {{1677-09-21 00:00}} in America/Los_Angeles, come back
> silently wrong. The Arrow path still forces every timestamp to nanoseconds,
> while the non-Arrow path already returns {{datetime64\[us\]}} on pandas 3 and
> converts the same values correctly. pandas 3 isn't officially supported yet
> (SPARK-57974 caps pandas below 3.0.0), so I'm filing this under SPARK-55139.
> h3. Reproducer
> Run line by line in the pyspark shell (Spark master eacf31b, pandas 3.0.6,
> pyarrow 26.0.0, classic):
> {code:python}
> spark.conf.set("spark.sql.session.timeZone", "America/Los_Angeles")
> df = spark.sql(
> "SELECT TIMESTAMP'9999-12-31 00:00:00' AS ts, TIMESTAMP_NTZ'9999-12-31
> 00:00:00' AS ts_ntz"
> )
> df2 = spark.sql("SELECT TIMESTAMP'1677-09-21 00:00:00' AS ts")
> spark.conf.set("spark.sql.execution.arrow.pyspark.enabled", "false")
> df.toPandas() # 9999-12-31 in both columns, dtype datetime64[us]
> df2.toPandas() # 1677-09-21
> spark.conf.set("spark.sql.execution.arrow.pyspark.enabled", "true")
> df2.toPandas() # 2262-04-11 23:34:33.709551 (wrong, no error)
> df.toPandas()
> # pyarrow.lib.ArrowInvalid: Casting from timestamp[us,
> tz=America/Los_Angeles] to timestamp[ns]
> # would result in out of bounds timestamp: 253402243200000000
> {code}
> With Spark Connect, {{toPandas()}} always goes through Arrow, so it fails on
> {{9999-12-31}} and returns the wrong {{1677-09-21}} value whatever
> {{spark.sql.execution.arrow.pyspark.enabled}} is set to.
> h3. Cause
> {{_convert_arrow_table_to_pandas}} in
> [python/pyspark/sql/pandas/conversion.py|https://github.com/apache/spark/blob/eacf31bf9a429d28e34ebe20ad7c7444c3d026bd/python/pyspark/sql/pandas/conversion.py#L251]
> always passes {{coerce_temporal_nanoseconds=True}} to pyarrow. Both classic
> and Connect {{toPandas()}} use it.
> * The flag was added in SPARK-45143 to keep the nanosecond output after
> pyarrow 13.
> * Since SPARK-55843, {{_to_corrected_pandas_type}} maps TimestampType and
> TimestampNTZType to {{datetime64\[us\]}} on pandas 3.
> * Since SPARK-56310, the TimestampType converter casts back to that dtype
> after the time zone conversion.
> So on pandas 3 the values go us -> ns -> us. The ns step fails for values
> outside the ns range, and values near 1677-09-21 can wrap around silently
> during the time zone conversion.
> h3. Suggested fix
> Only pass {{coerce_temporal_nanoseconds=True}} when pandas < 3, matching
> {{_to_corrected_pandas_type}}. In-range values are unchanged, because the
> result was already {{datetime64\[us\]}} on pandas 3. Nothing changes on
> pandas < 3: the output stays {{datetime64\[ns\]}}. As a side effect,
> DayTimeIntervalType values beyond about 292 years also come back correct on
> pandas 3. Today they silently overflow on the Arrow path.
> One edge remains. On pandas 3, TimestampType values before 1677-09-21 (UTC)
> in a zone with local mean time, such as America/Los_Angeles, come back a few
> minutes off after the fix: {{0001-01-01 00:00}} becomes {{0000-12-31
> 23:52:58}}. This is because pandas' {{tz_convert}} uses -8:00 there instead
> of -7:52:58. Today these values fail on the Arrow path, and the non-Arrow
> path raises ValueError for them.
> I have a fix with tests ready and will open a PR shortly.
> Co-developed with Claude Code (Claude Opus 5.5): the investigation, the
> reproducer and this description.
> Generated-by: Claude Code (Claude Opus 5.5)
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
