jayzhan211 opened a new issue, #13285: URL: https://github.com/apache/datafusion/issues/13285
# Issue abs() is one of the example that support numeric string In Postgres, the type is determined lately so `-1.2` is not string type but inferred into float type for abs() [doc](https://www.postgresql.org/docs/current/typeconv-func.html) ``` # abs: numeric string # TODO: In Postgres, '-1.2' is unknown type and interpreted to float8 so they don't fail on this query query error DataFusion error: This feature is not implemented: Unsupported data type Utf8 for function abs select abs('-1.2'); query error DataFusion error: This feature is not implemented: Unsupported data type Utf8 for function abs select abs(arrow_cast('-1.2', 'Utf8')); ``` # Additional context i confirm this works in PostgreSQL (was testing with PostgreSQL v 17) ```sql select abs('-123'); ``` https://www.postgresql.org/docs/17/typeconv-oper.html#id-1.5.9.7.8 suggests why this works '...' is not a varchar literal in PostgreSQL. it's "unknown-type" literal, which gets interpreted as float8 > Here the system has implicitly resolved the unknown-type literal as type float8 before applying the chosen operator. indeed, `select pg_typeof(abs('-123'));` returns `double precision` However, this doesn't work in PostgreSQL ```sql select abs(CAST('-123' AS varchar)); ``` This fails with "Query Error: function abs(character varying) does not exist" Indicating that there is no special coercion rules from varchar to numbers when calling functions like `abs()`. ----------- BTW, coercion rules can be retrieved from PostgreSQL from https://www.postgresql.org/docs/17/catalog-pg-cast.html PostgreSQL doesn't declare any implicit coercions between (selected) numeric types and (selected) varchar type, and `abs()` function resolution behavior as described above matches that. ```sql with selected_types(name) AS (VALUES ('int4'), ('int8'), ('float8'), ('varchar')) select src.typname src_type, dst.typname dst_type, castcontext, case castcontext when 'e' then 'only explicit' when 'a' then 'explicit | implicitly in assignment' when 'i' then 'implicitly in expressions, as well as the other cases' else '???' -- undocumented @ https://www.postgresql.org/docs/17/catalog-pg-cast.html end castcontext_explained from pg_cast join pg_type src on pg_cast.castsource = src.oid join pg_type dst on pg_cast.casttarget = dst.oid where true and src.oid != dst.oid and src.typname in (select name from selected_types) and dst.typname in (select name from selected_types) order by src.typname, dst.typname ; ``` src_type | dst_type | castcontext | castcontext_explained -- | -- | -- | -- float8 | int4 | a | explicit \| implicitly in assignment float8 | int8 | a | explicit \| implicitly in assignment int4 | float8 | i | implicitly in expressions, as well as the other cases int4 | int8 | i | implicitly in expressions, as well as the other cases int8 | float8 | i | implicitly in expressions, as well as the other cases int8 | int4 | a | explicit \| implicitly in assignment _Originally posted by @findepi in https://github.com/apache/datafusion/pull/13240#discussion_r1829086791_ -- 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: github-unsubscr...@datafusion.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
