hzmsh opened a new pull request, #59740: URL: https://github.com/apache/spark/pull/59740
### What changes were proposed in this pull request? Fixes [SPARK-60499](https://issues.apache.org/jira/browse/SPARK-60499), a sub-task of [SPARK-55139](https://issues.apache.org/jira/browse/SPARK-55139) (Support Pandas 3). Fix: - `_convert_arrow_table_to_pandas` passes `coerce_temporal_nanoseconds=True` to pyarrow only when pandas < 3, the same version check `_to_corrected_pandas_type` uses to map `TimestampType` and `TimestampNTZType` to `datetime64[ns]` (pandas < 3) or `datetime64[us]` (pandas 3). Both classic `toPandas()` and Spark Connect `toPandas()` call this function, so both are fixed. - To keep pandas 2 behavior, pandas < 3 still coerces to nanoseconds. The pandas-on-Spark change for the same pandas 3 timestamp unit issue ([SPARK-55225](https://issues.apache.org/jira/browse/SPARK-55225), [#54017](https://github.com/apache/spark/pull/54017)) did the same. - New test `test_toPandas_timestamp_out_of_ns_range` in `ArrowTestsMixin`, with its Connect parity override. ### Why are the changes needed? 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 non-Arrow path already returns `datetime64[us]` on pandas 3 and converts the same values correctly. ```python # pandas 3.0.6, pyarrow 26.0.0, classic 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() # master: 2262-04-11 23:34:33.709551 (wrong, no error), expected: 1677-09-21 df.toPandas() # master: pyarrow.lib.ArrowInvalid: Casting from timestamp[us, tz=America/Los_Angeles] to timestamp[ns] # would result in out of bounds timestamp: 253402243200000000 # expected: 9999-12-31 in both columns, as with Arrow disabled ``` Spark Connect 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. When it started: - `coerce_temporal_nanoseconds=True` was added in [SPARK-45143](https://issues.apache.org/jira/browse/SPARK-45143) ([#42920](https://github.com/apache/spark/pull/42920), 4.0.0) to keep the nanosecond output after pyarrow 13 started returning the original unit. - [SPARK-55843](https://issues.apache.org/jira/browse/SPARK-55843) (4.2.0) then mapped `TimestampType` and `TimestampNTZType` to `datetime64[us]` on pandas 3, and [SPARK-56310](https://issues.apache.org/jira/browse/SPARK-56310) (4.2.0) made the `TimestampType` converter cast back to that dtype after the time zone conversion. Since then, on pandas 3 the Arrow path goes us -> ns -> us. The ns step fails for values outside the ns range, and values near 1677-09-21 can wrap around during the time zone conversion. - pandas 3 isn't officially supported yet ([SPARK-57974](https://issues.apache.org/jira/browse/SPARK-57974) caps pandas below 3.0.0), so this is part of the pandas 3 work in SPARK-55139. Reports from users: - None on pandas 3 that I found, since pandas 3 isn't officially supported yet. On pandas < 3, which this PR doesn't change, users have reported out-of-range timestamp errors on Stack Overflow: `ArrowInvalid` with Arrow (one of them on `9999-12-31`) and `OutOfBoundsDatetime` without Arrow. The links are in the [JIRA comment](https://issues.apache.org/jira/browse/SPARK-60499?focusedCommentId=18130657&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-18130657). What goes wrong: - In [`pandas/conversion.py`](https://github.com/apache/spark/blob/eacf31bf9a429d28e34ebe20ad7c7444c3d026bd/python/pyspark/sql/pandas/conversion.py#L251), `_convert_arrow_table_to_pandas` sets `coerce_temporal_nanoseconds=True` for every pandas version, so pyarrow casts every timestamp to nanoseconds before Spark's converters run. ### Does this PR introduce _any_ user-facing change? Yes, on pandas 3 only. pandas 3 isn't officially supported in any release yet, so this only matters for users who already run pandas 3. Behavior change: - With pandas 3, Arrow `toPandas()` (classic and Connect) now returns timestamps after 2262-04-11, such as `9999-12-31`, instead of raising `ArrowInvalid`. Values like `1677-09-21 00:00` in America/Los_Angeles are now correct instead of silently wrong. Both now match the non-Arrow path. - With pandas 3, `DayTimeIntervalType` values beyond about 292 years (for example `INTERVAL '365000' DAY`) are now correct. Before, the Arrow path silently returned a wrong value (`-62008 days +00:50:52.580896`). - In-range values, dates and nested timestamps are unchanged. The result was already `datetime64[us]` on pandas 3. - Nothing changes on pandas < 3. Not covered: - pandas < 3: `datetime64[ns]` can't hold these values, so `9999-12-31` still fails on both paths there, and with Arrow `1677-09-21 00:00` in America/Los_Angeles still comes back wrong. I asked on the [JIRA](https://issues.apache.org/jira/browse/SPARK-60499?focusedCommentId=18130657&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-18130657) whether a clearer error would be welcome. - pandas UDF, `applyInPandas` and `mapInPandas` inputs still coerce timestamps to nanoseconds on pandas 3, in [`ArrowArrayConversion.preprocess_time`](https://github.com/apache/spark/blob/eacf31bf9a429d28e34ebe20ad7c7444c3d026bd/python/pyspark/sql/conversion.py#L1797-L1809) and [`ArrowToPandasConversion`](https://github.com/apache/spark/blob/eacf31bf9a429d28e34ebe20ad7c7444c3d026bd/python/pyspark/sql/conversion.py#L2028). Changing those changes the dtype UDFs receive, so I left them out. - On pandas 3, `TimestampType` values before 1677-09-21 (UTC) in a zone with local mean time, such as America/Los_Angeles, now come back a few minutes off with Arrow: `0001-01-01 00:00` becomes `0000-12-31 23:52:58`. pandas' `tz_convert` uses -8:00 there instead of -7:52:58. Before this PR these values raised `ArrowInvalid`, and the non-Arrow path raises `ValueError` for them. ### How was this patch tested? Tests: - `test_toPandas_timestamp_out_of_ns_range` converts `9999-12-31` and `1677-09-21` in America/Los_Angeles, as `TimestampType` and `TimestampNTZType`, with Arrow enabled and disabled, and compares with the expected values. The Connect parity override runs it with Arrow, as the other `toPandas` parity tests do. It is skipped on pandas < 3, where nothing changes. - Without the fix, on pandas 3.0.6, the test fails in both classic and Connect with `ArrowInvalid: Casting from timestamp[us, tz=America/Los_Angeles] to timestamp[ns] would result in out of bounds timestamp: 253402243200000000`. - PR builds run pandas 2.3.3, so they skip the new test. The scheduled pandas 3 build (`build_python_3.12_pandas_3.yml`) will run it after merge. I ran it locally on pandas 3.0.6 (pyarrow 26.0.0), with the machine time zone set to America/Chicago, Pacific/Kiritimati, Etc/GMT+12 and Asia/Tokyo. - The modules below pass on pandas 2.3.3 and 3.0.6. On pandas 3, I also compared outputs on master and with this PR for in-range timestamps, dates, nested timestamps (struct, array, map; all `structHandlingMode` values), nanosecond timestamp types, empty DataFrames and `selfDestruct`. They are identical. ``` python/run-tests --testnames 'pyspark.sql.tests.arrow.test_arrow ArrowTests.test_toPandas_timestamp_out_of_ns_range,pyspark.sql.tests.connect.arrow.test_parity_arrow ArrowParityTests.test_toPandas_timestamp_out_of_ns_range' python/run-tests --testnames pyspark.sql.tests.test_conversion,pyspark.sql.tests.arrow.test_arrow,pyspark.sql.tests.connect.arrow.test_parity_arrow,pyspark.sql.tests.pandas.test_converter,pyspark.sql.tests.pandas.test_pandas_udf_scalar,pyspark.sql.tests.pandas.test_pandas_grouped_map,pyspark.sql.tests.pandas.test_pandas_map,pyspark.tests.upstream.pyarrow.test_pyarrow_table_to_pandas dev/lint-python ``` ### Was this patch authored or co-authored using generative AI tooling? Generated-by: Claude Code (Claude Opus 5.5) Co-developed with Claude Code (Claude Opus 5.5): the investigation, the fix, the tests and this description. The checks above were run locally. -- 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]
