Interesting .... I think most of the mathematical scalar functions are only implemented for primitive numerics:

• DOUBLE
• FLOAT
• BYTE
• SHORT
• INTEGER
• LONG

cr> SELECT abs(power(2,7)::NUMERIC);
SQLParseException[Cannot cast `128.0` of type `numeric` to type `byte`]

cr> SELECT abs(power(2,6)::NUMERIC);
+----+
| 64 |
+----+
| 64 |
+----+
SELECT 1 row in set (0.008 sec)

Right

SELECT abs(127::NUMERIC);
> 127

SELECT abs(128::NUMERIC);
> SQLParseException[Cannot cast `128` of type `numeric` to type `byte`]

SELECT ceil(128::NUMERIC);
> SQLParseException[Cannot cast `128` of type `numeric` to type `byte`]

SELECT ceil(128::INTEGER);
> 128

Thanks for reporting and further investigating @surister @proddata . I also think that we need to add abs(numeric) -> numeric variant which is required to prevent any loss of precision. We could reference https://www.postgresql.org/docs/current/functions-math.html to see what other scalars require f(numeric) -> numeric variant.

Confirmed:
abs
ceil
ceiling
exp
floor
round
sqrt

This leads to breaking changes, for example round, CrateDB documents that it Returns: bigint or integer, however in order to be Postgres compatible, we need round(numeric) -> numeric overload. I think this could also be postponed to the next major release(Or 5.7? similar to https://github.com/crate/crate/pull/15643/files#r1510845633 cc @mfussenegger).