Shuheng Ma created SPARK-60499:
----------------------------------
Summary: 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
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]
