[jira] [Updated] (FLINK-41032) PyFlink Table.to_pandas() loses column dtypes when the result is empty

["Biao Geng (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/FLINK-41032?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Biao Geng updated FLINK-41032:
------------------------------
    Component/s: API / Python

> PyFlink Table.to_pandas() loses column dtypes when the result is empty
> ----------------------------------------------------------------------
>
>                 Key: FLINK-41032
>                 URL: https://issues.apache.org/jira/browse/FLINK-41032
>             Project: Flink
>          Issue Type: Bug
>          Components: API / Python
>            Reporter: Biao Geng
>            Priority: Minor
>
> From Nick Crews's report in Slack:
>  
> **Summary:** {{Table.to_pandas()}} returns object dtypes for every column when the result is empty
> **Description:**
> When a query returns no rows, {{Table.to_pandas()}} skips the Arrow path and returns {{{}pd.DataFrame.from_records([], columns=...){}}}. The table schema is ignored, so every column has {{object}} dtype. The same query with rows returns properly typed columns. Code that concatenates or compares results, or checks dtypes, then behaves differently depending on whether a filter happened to match anything.
> python
> from pyflink.table import EnvironmentSettings, TableEnvironment
> t_env = TableEnvironment.create(EnvironmentSettings.in_batch_mode())
> t_env.execute_sql(
>     "CREATE TEMPORARY VIEW t AS SELECT * FROM (VALUES "
>     "(1, TIMESTAMP '2024-01-01 00:00:00.123456')) AS v(a, ts)"
> )
> t_env.sql_query("SELECT a, ts FROM t").to_pandas().dtypes
> # a     int32
> # ts    datetime64[us]
> t_env.sql_query("SELECT a, ts FROM t WHERE a > 5").to_pandas().dtypes
> # a     object
> # ts    object
> Reproduced on 1.20.3 and 2.3.0, and the code is unchanged on master ({{{}flink-python/pyflink/table/table.py{}}}, the {{else}} branch of {{{}to_pandas{}}}).
> **Suggested fix:** build the empty frame from the Arrow schema already computed for the non-empty path, so both paths share one type mapping:
>  
> python arrow_schema = create_arrow_schema(schema.get_field_names(), schema.get_field_data_types()) pdf = arrow_schema.empty_table().to_pandas()
> then run the same {{tz_convert_from_internal}} loop, so {{TIMESTAMP_LTZ}} columns match too.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)