Signature::Equal for `NVL` and `NullIf` about arrow-datafusion HOT 5 CLOSED

I checked the behavior in Postgres and found it doing casting implicitly, maybe the idea of Signature::Equal is not suitable for nullif. 😢

create table t1 (a int);

postgres=# select pg_typeof(nullif(a, 3.1)) from t1;
 pg_typeof 
-----------
 numeric
 numeric
 numeric
 numeric
 numeric
(5 rows)

postgres=# select pg_typeof(nullif(a, '2')) from t1;
 pg_typeof 
-----------
 integer
 integer
 integer
 integer
 integer
(5 rows)

postgres=# select pg_typeof(nullif(a, 2)) from t1;
 pg_typeof 
-----------
 integer
 integer
 integer
 integer
 integer
(5 rows)

from arrow-datafusion.

I think the coercion logic here is similar to coalesce and array. #10261 (comment)

from arrow-datafusion.

I think this would not need a lot of changes and I want to take it. :)

from arrow-datafusion.

After a initial try, there is an issue shown below:

statement ok
CREATE TABLE test(
int_field INT,
bool_field BOOLEAN,
text_field TEXT,
more_ints INT
) as VALUES
(1, true, 'abc', 2),
(2, false, 'def', 2),
(3, NULL, 'ghij', 3),
(NULL, NULL, NULL, 4),
(4, false, 'zxc', 5),
(NULL, true, NULL, 6)
;

query I rowsort
SELECT NULLIF(int_field, 2) FROM test;

External error: query failed: DataFusion error: Error during planning: No function matches the given name and argument types 'nvl(Int32, Int64)'. You might need to add explicit type casts.

I think it's because the first type is INT(32), however datafusion will treat the const 2 as int64.🤔
I will also see whether there is a solution.

Edit:
I think adding CAST would resolve this, would it be a good way?
SELECT NULLIF(int_field, CAST(2 as INT)) FROM test;

from arrow-datafusion.

Got it, it seems that datafusion can't do casting implicitly now, so the idea may be not available now. 👀

from arrow-datafusion.