Floor built in function return type

[Ted-Jiang]

https://github.com/apache/arrow-datafusion/blob/f0af5eb949e2c5fa9f66eb6f6a9fcdf8f7389c9d/datafusion/expr/src/built_in_function.rs#L899-L915

from this code floor() only retun float type

but from postgresql

floor ( numeric ) → numeric

floor ( double precision ) → double precision

Nearest integer less than or equal to argument

floor(42.8) → 42

floor(-42.8) → -43

https://www.postgresql.org/docs/current/functions-math.html

[Ted-Jiang]

seems cast in some place

❯ explain verbose  select floor(-1);
+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------+
| plan_type                                                  | plan                                                                                                   |
+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------+
| initial_logical_plan                                       | Projection: floor(Int64(-1))                                                                           |
|                                                            |   EmptyRelation                                                                                        |
| logical_plan after inline_table_scan                       | SAME TEXT AS ABOVE                                                                                     |
| logical_plan after type_coercion                           | Projection: floor(CAST(Int64(-1) AS Float64))                                                          |
|                                                            |   EmptyRelation                                                                                        |
| logical_plan after count_wildcard_rule                     | SAME TEXT AS ABOVE                                                                                     |
| analyzed_logical_plan                                      | SAME TEXT AS ABOVE                                                                                     |
| logical_plan after eliminate_nested_union                  | SAME TEXT AS ABOVE                                                                                     |
| logical_plan after simplify_expressions                    | Projection: Float64(-1) AS floor(Int64(-1))                                                            |
|                                                            |   EmptyRelation                                                                                        |
| logical_plan after unwrap_cast_in_comparison               | SAME TEXT AS ABOVE                                                                                     |
| logical_plan after replace_distinct_aggregate              | SAME TEXT AS ABOVE                                                                                     |
| logical_plan after eliminate_join                          | SAME TEXT AS ABOVE

[alamb]

If you are saying that floor(decimal) should return decimal that makes sense to me

[maruschin]

@alamb, @Ted-Jiang, hi! I created draft PR

if the idea is ok, would like to compare all return types in pg and fixed them

[Ted-Jiang]

If you are saying that floor(decimal) should return decimal that makes sense to me

Yes, basiclly all numeric type should have the return type same as input.
And i have check the numeric type list is same as pg.

[theirix]

The floor and ceil udfs now support decimal return type via #18979.

Non-trivial functions like pow, log, etc. are also supported (linked in the parent EPIC).

So I think this issue is fixed and could be closed

[Jefffrey]

Looks like we're consistent with integers too:

psql (18.2 (Debian 18.2-1.pgdg13+1))
Type "help" for help.

postgres=# select pg_typeof(1);
 pg_typeof
-----------
 integer
(1 row)

postgres=# select pg_typeof(floor(1));
    pg_typeof
------------------
 double precision
(1 row)

DF:

DataFusion CLI v52.1.0
> select floor(1);
+-----------------+
| floor(Int64(1)) |
+-----------------+
| 1.0             |
+-----------------+
1 row(s) fetched.
Elapsed 0.059 seconds.

> select arrow_typeof(floor(1));
+-------------------------------+
| arrow_typeof(floor(Int64(1))) |
+-------------------------------+
| Float64                       |
+-------------------------------+
1 row(s) fetched.
Elapsed 0.008 seconds.

Thanks for checking @theirix