Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support numeric string argument in functions #13285

Open
jayzhan211 opened this issue Nov 7, 2024 · 2 comments
Open

Support numeric string argument in functions #13285

jayzhan211 opened this issue Nov 7, 2024 · 2 comments

Comments

@jayzhan211
Copy link
Contributor

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

# 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)
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

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.

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 #13240 (comment)

@jayzhan211
Copy link
Contributor Author

Does this mean we need LogicalType::Unknown 🤔 Then we can infer type in the later stage

@jayzhan211
Copy link
Contributor Author

jayzhan211 commented Nov 8, 2024

DuckDB doesn't support this...

D select abs('-1.2');
Binder Error: Could not choose a best candidate function for the function call "abs(STRING_LITERAL)". In order to select one, please add explicit type casts.
	Candidate functions:
	abs(SMALLINT) -> SMALLINT
	abs(INTEGER) -> INTEGER
	abs(BIGINT) -> BIGINT
	abs(HUGEINT) -> HUGEINT
	abs(FLOAT) -> FLOAT
	abs(DOUBLE) -> DOUBLE
	abs(UTINYINT) -> UTINYINT
	abs(USMALLINT) -> USMALLINT
	abs(UINTEGER) -> UINTEGER
	abs(UBIGINT) -> UBIGINT
	abs(UHUGEINT) -> UHUGEINT
	abs(TINYINT) -> TINYINT

LINE 1: select abs('-1.2');
               ^

But both support to_timestamp

D select to_timestamp('-1');
┌───────────────────────────┐
│    to_timestamp('-1')     │
│ timestamp with time zone  │
├───────────────────────────┤
│ 1970-01-01 07:29:59+07:30 │
└───────────────────────────┘

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant