You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "jayzhan211 (via GitHub)" <gi...@apache.org> on 2024/04/27 06:52:22 UTC
[I] Use non-comparison coercion for `Coalesce` or even avoid implicit casting for `Coalesce` [datafusion]
jayzhan211 opened a new issue, #10261:
URL: https://github.com/apache/datafusion/issues/10261
### Is your feature request related to a problem or challenge?
# What does Coalesce do
The coalesce function implicitly coerces types with Signature::VariadicEqual which has `comparison_coercion` internally, and the coercion is taken considered for all the columns, not only those we need, which gives us back unexpected casting results.
# Coerce types after first non-null values are known
We can see the following example,
```
statement ok
create table test1 (a int, b int) as values (null, 3), (2, null);
# test coercion string
query TT
select
coalesce(a, b, 'none_set'),
arrow_typeof(coalesce(a, b, 'none_set'))
from test1;
----
3 Utf8
2 Utf8
```
Since they are coerced to Utf8, so we get 3 and 2 with Utf8.
Ideally, If we take the first non-null value, we should expect to get Int, not Utf8.
another example, dict is cast to Int64
```
query IT
select
coalesce(arrow_cast(123, 'Dictionary(Int32, Int8)'), 34),
arrow_typeof(coalesce(arrow_cast(123, 'Dictionary(Int32, Int8)'), 34))
----
123 Int64
```
The reason we need coercion is that it is possible that we have different types for different columns. Coerce them can help we get the final single type.
```
statement ok
create table test1 as values
(arrow_cast(1, 'Int8'), arrow_cast(2, 'Int32')),
(null, arrow_cast(3, 'Int32'))
query IT
select
coalesce(column1, column2),
arrow_typeof(coalesce(column1, column2))
from test1;
----
1 Int32
3 Int32
```
We get (1, Int8) and (3, Int32) for respective row, and finally cast them to I32.
**I suggest that we apply coercion after we collect those first non-null values for each row.**
# Use non-comparison coercion
## Comparision coercion (fn comparison_coercion) vs non-comparison coercion (fn coerced_from)
Those two logic are quite different, comparison coercion is for `comparision`. For example, compare dict with dict returns value type, since dict key is not important. Compare i64 with u64 we fallback to i64 because we don't have i128, even there is possible of lossy if you have large U64 value, but most of the cases like U64(1) and I64(1) is comparable, we will not block for those edge cases in comparison. And, there might be more.
Given the difference between these two coercion, I think non-comparison coercion is more suitable for Coalesce function. Btw, I think make_array should switch to non-comparison coercion too.
**I suggest we switch VariadicEqual to non-comparison coercion or introduce another one signature if comparison coercion is needed somewhere**.
```
query IT
select
coalesce(arrow_cast(3, 'Dictionary(Int32, Int32)'), arrow_cast(3, 'Dictionary(Int32, Int64)')),
arrow_typeof(coalesce(arrow_cast(3, 'Dictionary(Int32, Int32)'), arrow_cast(3, 'Dictionary(Int32, Int64)')));
----
3 Int64
```
## Maybe disable implicit coercion for Coalesce function?
I'm not sure why is coalesce function introduce implicit coercion, but I found that Postgres and Duckdb does not do implicit casting for Coalesce, maybe we should follow them?
### Describe the solution you'd like
_No response_
### Describe alternatives you've considered
_No response_
### Additional context
_No response_
--
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-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Use non-comparison coercion for `Coalesce` or even avoid implicit casting for `Coalesce` [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #10261:
URL: https://github.com/apache/datafusion/issues/10261#issuecomment-2080555582
Alright, so I think we should remove casting logic for coalesce, which follows what Postgres and DuckDB do. Returns error if there are mixed types for different columns.
--
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-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Use non-comparison coercion for `Coalesce` or even avoid implicit casting for `Coalesce` [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #10261:
URL: https://github.com/apache/datafusion/issues/10261#issuecomment-2081465710
Note:
I think we should change `coerced_from` to `can_cast_types` here, pre-computed reasonable valid types in `get_valid_types`.
https://github.com/apache/datafusion/blob/ed146828fdeb9784eb2963ae255909b9ac53c61b/datafusion/expr/src/type_coercion/functions.rs#L67-L71
https://github.com/apache/datafusion/blob/ed146828fdeb9784eb2963ae255909b9ac53c61b/datafusion/expr/src/type_coercion/functions.rs#L82-L261
--
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-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Use non-comparison coercion for `Coalesce` or even avoid implicit casting for `Coalesce` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #10261:
URL: https://github.com/apache/datafusion/issues/10261#issuecomment-2088944901
BTW this is a really nicely written ticket
--
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-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Use non-comparison coercion for `Coalesce` or even avoid implicit casting for `Coalesce` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #10261:
URL: https://github.com/apache/datafusion/issues/10261#issuecomment-2080464218
If possible, I think we should follow the model of existing implementations here (rather than invent DataFusion specific semants)
Specifically if we are going to change the semantics of coalsce I think we should follow either postgres or spark's behavior -- I haven't done the research to know how close/far what DataFusion does compared to those systems
--
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-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Use non-comparison coercion for `Coalesce` or even avoid implicit casting for `Coalesce` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #10261: Use non-comparison coercion for `Coalesce` or even avoid implicit casting for `Coalesce`
URL: https://github.com/apache/datafusion/issues/10261
--
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-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org