Regression: write_csv result has incorrect formatting

[DDtKey]

Describe the bug
write_csv result contains unexpected format for EXTRACT(YEAR FROM ...). It looks like floating number for some reason.

To Reproduce
Example of file:

name,created_at,last_report
Sales,1825-08-29T07:29:01.256,2022-08-29
Marketing,2017-02-16T07:29:01.256,2022-02-16
IT,2019-04-04T07:29:01.256,2021-04-04
Finance,2016-09-14T07:29:01.256,2021-09-14
HR,2017-03-01T07:29:01.256,2022-03-01

SQL:

SELECT d.name, EXTRACT(YEAR FROM d.created_at) as year, d.last_report + INTERVAL '12' MONTH as deadline FROM deps d ORDER BY d.created_at

It returns:

name,year,deadline
Sales,1825.0,2023-08-29
Finance,2016.0,2022-09-14
Marketing,2017.0,2023-02-16
HR,2017.0,2023-03-01
IT,2019.0,2022-04-04

So result of EXTRACT(YEAR FROM d.created_at) as year has floating format for some reason.

While data_frame.show() works expected:

+-----------+------+------------+
| name      | year | deadline   |
+-----------+------+------------+
| Sales     | 1825 | 2023-08-29 |
| Finance   | 2016 | 2022-09-14 |
| Marketing | 2017 | 2023-02-16 |
| HR        | 2017 | 2023-03-01 |
| IT        | 2019 | 2022-04-04 |
+-----------+------+------------+

Expected behavior
Result should be consistent with show and previous version datafusion 15.0.0 (it used to work)

Additional context
Add any other context about the problem here.

[alamb]

(arrow_dev) alamb@MacBook-Pro-8:~/Software/influxdb_iox2$ cat /tmp/data.csv 
name,created_at,last_report
Sales,1825-08-29T07:29:01.256,2022-08-29
Marketing,2017-02-16T07:29:01.256,2022-02-16
IT,2019-04-04T07:29:01.256,2021-04-04
Finance,2016-09-14T07:29:01.256,2021-09-14
HR,2017-03-01T07:29:01.256,2022-03-01

You are correct that extract is now a float -- I think that was on purpose to match posgres: #3997

(arrow_dev) alamb@MacBook-Pro-8:~/Software/influxdb_iox2$ datafusion-cli 
DataFusion CLI v16.0.0
❯ select * from '/tmp/data.csv';
+-----------+-------------------------+-------------+
| name      | created_at              | last_report |
+-----------+-------------------------+-------------+
| Sales     | 1825-08-29T07:29:01.256 | 2022-08-29  |
| Marketing | 2017-02-16T07:29:01.256 | 2022-02-16  |
| IT        | 2019-04-04T07:29:01.256 | 2021-04-04  |
| Finance   | 2016-09-14T07:29:01.256 | 2021-09-14  |
| HR        | 2017-03-01T07:29:01.256 | 2022-03-01  |
+-----------+-------------------------+-------------+
5 rows in set. Query took 0.020 seconds.
❯ SELECT d.name, arrow_typeof(EXTRACT(YEAR FROM d.created_at)) as year, d.last_report + INTERVAL '12' MONTH as deadline FROM '/tmp/data.csv' d ORDER BY d.created_at;
+-----------+---------+------------+
| name      | year    | deadline   |
+-----------+---------+------------+
| Sales     | Float64 | 2023-08-29 |
| Finance   | Float64 | 2022-09-14 |
| Marketing | Float64 | 2023-02-16 |
| HR        | Float64 | 2023-03-01 |
| IT        | Float64 | 2022-04-04 |
+-----------+---------+------------+
❯ 

Perhaps you can add a workaround by explicitly casting to numeric:

❯ SELECT d.name, EXTRACT(YEAR FROM d.created_at)::int as year, d.last_report + INTERVAL '12' MONTH as deadline FROM '/tmp/data.csv' d ORDER BY d.created_at;

[DDtKey]

Thanks for clarifying @alamb!

But I have concern about difference between show and write_csv. A bit misleading.

Why does it have different formatting rules? I just expected that show just merge content and prints it.

But in general, we can close this issue I guess

[alamb]

The different formatting is due to differences in the upstream arrow crate -- I have filed apache/arrow-rs#3513 to track

[alamb]

But in general, we can close this issue I guess

I will do so -- thank you very much for this report DDtKey - keep them coming!

[DDtKey]

@alamb btw, just wanted to mention, that postgres returns exactly 2023 for SELECT EXTRACT(YEAR FROM now());, but with numeric type (SELECT pg_typeof(EXTRACT(YEAR FROM now()));)

I just meant that it could lead to misunderstanding why the same query before returned expected results and now it's float for year, so I hope issue with formatting in arrow gonna negotiate this one

[alamb]

postgres=# SELECT pg_typeof(EXTRACT(YEAR FROM now()));
 pg_typeof 
-----------
 numeric
(1 row)

Indeed -- I think we switched extract to return float but maybe it should be numeric? I can't remember the details -- perhaps @waitingkuo does?

[waitingkuo]

Postgrseql's extract returns double precision (f64 for datafusion) and
date_part return numeric (decimal for datafusion)

quoted from https://www.postgresql.org/docs/15/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
For historical reasons, the date_part function returns values of type double precision. This can result in a loss of precision in certain uses. Using extract is recommended instead.

the return type for extract is still float but not decimal yet, it's tracked here
#3996

It's pending since the Decimal type wasn't consistent yet. #3996 (comment)
Is it recommended to change the return type now? @alamb @tustvold

[alamb]

Is it recommended to change the return type now? @alamb @tustvold

I think it would be interesting to try out. I am not sure what else is left related to "experimental" support for Decimal -- it seems pretty full featured now to me, given the work from @liukun4515 and others