You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
optimize/simplify the literal data type and remove unnecessary cast、try_cast #3031
Is your feature request related to a problem or challenge? Please describe what you are trying to do.
Schema:
c1 decimal(18,)
select * from table where c1 = 20;
select * from table where c1 in (12,2)
We will get the sql plan like below
.....
....
cast(c1 as decimal(20,0), cast(20 as decimal(20,0)
The type of 20 or 12,2 is INT64 in the datafusion, the coerced data type of decimal(18,0) compare with int64 is decimal(20,0) according to the rule get_comparison_common_decimal_type.
We need to optimize this point like spark-issue, and it will reduce unnecessary cast/try_cast in many literal case.
Describe the solution you'd like
A clear and concise description of what you want to happen.
Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.
Additional context
There is the plan to do the rule:
I agree the ideal outcome would be that the logical plan has the casts. I think the reason the type coercion / casting happens in the physical planner was some idea that it could be possible to use different implementations of physical exprs that might have different comparison rules (like some system that could natively compare i32 and i64 -- via a native kernel, for example)
I am not sure if this ability has every been used in practice but I am not sure it would be a simple thing to change now
And basically special case if the argument is a literal, do the cast at plan creation time. It wouldn't be super be super general, but I think it would work in this case for you
Here is a self contained reproducer for anyone following along:
❯ create table foo asselect column1 as d from (values (1), (2));
+---+
| d |
+---+
| 1 |
| 2 |
+---+2 rows inset. Query took 0.005 seconds.
❯ create table bar asselect cast (d asdecimal) as d from foo;
+--------------+
| d |
+--------------+
| 1.0000000000 |
| 2.0000000000 |
+--------------+2 rows inset. Query took 0.005 seconds.
❯ explain select*from bar where d =1.4;
+---------------+-----------------------------------------------------------------------------------+
| plan_type | plan |
+---------------+-----------------------------------------------------------------------------------+
| logical_plan | Projection: #bar.d |
| | Filter: #bar.d = Float64(1.4) |
| | TableScan: bar projection=[d] |
| physical_plan | ProjectionExec: expr=[d@0as d] |
| | CoalesceBatchesExec: target_batch_size=4096 |
| | FilterExec: CAST(d@0AS Decimal128(38, 15)) = CAST(1.4AS Decimal128(38, 15)) |
| | RepartitionExec: partitioning=RoundRobinBatch(16) |
| | MemoryExec: partitions=1, partition_sizes=[1] |
| | |
+---------------+-----------------------------------------------------------------------------------+2 rows inset. Query took 0.005 seconds.
The FilterExec line above should not have the CAST operations in them
The cast is added in the creation of physical expr/physical plan.
It follow a generate rule for coerced binary comparison.
Like below:
INT32 < INT64 -> INT64
DECIMAL(10,2) < DOUBLE -> Other decimal data type.
I think it all in the comparison_binary_numeric_coercion function.
This is just the general rule, and it works well in all cases.
But in many user case, we just use the literal as filter expr and other condition as this issue, the new optimizer rule should resolve this case like in the spark.
I have file a draft pr which add a logical optimizer rule to do this, but it maybe ready tomorrow because of some changes of plan needed to reviewed by myself first. I think the rule can works well for us. #3185
The cast is added in the creation of physical expr/physical plan.
Yes I agree
It sounds like you are proposing moving the coercion to the logical planning phase -- while I am not opposed to doing so I do think it is likely a large change which I think we'll want to run by other maintainers. But getting a PR up to do the change is the first step
I do think it is worth considering special casing just the casting in the physical planner (as the physical planner is what is adding the casts in the first place)
+1 for optimization rules to perform type coercion in the logical plan. I am running into some issues around this myself and am seeing quite a lot of inconsistencies in how we currently handle this.
+1 for optimization rules to perform type coercion in the logical plan. I am running into some issues around this myself and am seeing quite a lot of inconsistencies in how we currently handle this.
Do you mean that you wish to migrate the type coercion from physical plan creation to logical plan creation?
But this issue just add a rule to optimize the expr and improve the performance, and will not do and changes in the generation of physical plan or physical expr.
The cast is added in the creation of physical expr/physical plan.
Yes I agree
It sounds like you are proposing moving the coercion to the logical planning phase -- while I am not opposed to doing so I do think it is likely a large change which I think we'll want to run by other maintainers. But getting a PR up to do the change is the first step
In my knowledge from other database system, the generation for physical expr will not do any changes for original expr and just do transformation from logical expr to physical expr.
I remember we discussed this a long time ago.
But this issue is not used to do this, and just to optimize the case described in the beginning of this issue.
I do think it is worth considering special casing just the casting in the physical planner (as the physical planner is what is adding the casts in the first place)
We can pull a new issue to discussion why and how to migrate the type coercion from physical parse to logical parse. What benefit we can get from the work.
We can pull a new issue to discussion why and how to migrate the type coercion from physical parse to logical parse. What benefit we can get from the work.
To be clear I think moving coercion to logical planning is a good idea, but I think it will be a non trivial change that will take some time. Thus, I was suggesting the "special case physical expr planning" idea as a way to speed up casting in the short term
Activity
liukun4515 commentedon Aug 4, 2022
Optimize the decimal value from arrow-rs apache/arrow-rs#2313
alamb commentedon Aug 17, 2022
Here is a self contained reproducer for anyone following along:
The FilterExec line above should not have the CAST operations in them
alamb commentedon Aug 17, 2022
And we can see that as @liukun4515 says the casting is added in the physical plan (rather than the logical plan):
alamb commentedon Aug 17, 2022
I agree the ideal outcome would be that the logical plan has the casts. I think the reason the type coercion / casting happens in the physical planner was some idea that it could be possible to use different implementations of physical exprs that might have different comparison rules (like some system that could natively compare i32 and i64 -- via a native kernel, for example)
I am not sure if this ability has every been used in practice but I am not sure it would be a simple thing to change now
I suggest you investigate special casing the creation of
PhysicalExprs for for cast https://github.com/apache/arrow-datafusion/blob/89bcfc4827a84c37abdf6476ec164611b270492d/datafusion/physical-expr/src/planner.rs#L172-L181And basically special case if the argument is a literal, do the cast at plan creation time. It wouldn't be super be super general, but I think it would work in this case for you
alamb commentedon Aug 17, 2022
Another alternative might be to follow the model of ConstEvaluator
(which operates onExpr`s) any apply it to physical expressions 🤔https://github.com/apache/arrow-datafusion/blob/3eb55e9a0510d872f6f7765b1a5f17db46486e45/datafusion/optimizer/src/simplify_expressions.rs#L397-L415
You would likely need changes to PhysicalExpr similar to what @iajoiner and i were discussing on #3014 (comment)
liukun4515 commentedon Aug 17, 2022
The
castis added in the creation of physical expr/physical plan.It follow a generate rule for coerced binary comparison.
Like below:
INT32 < INT64 -> INT64
DECIMAL(10,2) < DOUBLE -> Other decimal data type.
I think it all in the
comparison_binary_numeric_coercionfunction.This is just the general rule, and it works well in all cases.
But in many user case, we just use the literal as filter expr and other condition as this issue, the new optimizer rule should resolve this case like in the spark.
I have file a draft pr which add a logical optimizer rule to do this, but it maybe ready tomorrow because of some changes of plan needed to reviewed by myself first. I think the rule can works well for us. #3185
@alamb
alamb commentedon Aug 17, 2022
Yes I agree
It sounds like you are proposing moving the coercion to the logical planning phase -- while I am not opposed to doing so I do think it is likely a large change which I think we'll want to run by other maintainers. But getting a PR up to do the change is the first step
I do think it is worth considering special casing just the casting in the physical planner (as the physical planner is what is adding the casts in the first place)
andygrove commentedon Aug 17, 2022
+1 for optimization rules to perform type coercion in the logical plan. I am running into some issues around this myself and am seeing quite a lot of inconsistencies in how we currently handle this.
liukun4515 commentedon Aug 18, 2022
Do you mean that you wish to migrate the type coercion from physical plan creation to logical plan creation?
But this issue just add a rule to optimize the expr and improve the performance, and will not do and changes in the generation of physical plan or physical expr.
liukun4515 commentedon Aug 18, 2022
In my knowledge from other database system, the generation for physical expr will not do any changes for original expr and just do transformation from logical expr to physical expr.
I remember we discussed this a long time ago.
But this issue is not used to do this, and just to optimize the case described in the beginning of this issue.
We can pull a new issue to discussion why and how to migrate the type coercion from physical parse to logical parse. What benefit we can get from the work.
@alamb
alamb commentedon Aug 18, 2022
Makes sense to me 👍 thank you @liukun4515
To be clear I think moving coercion to logical planning is a good idea, but I think it will be a non trivial change that will take some time. Thus, I was suggesting the "special case physical expr planning" idea as a way to speed up casting in the short term
alamb commentedon Sep 19, 2022
@liukun4515 I wonder if this ticket is now complete?
liukun4515 commentedon Sep 20, 2022
There are some follow-up optimization tasks.
I will list them and then close this pr.
alamb commentedon Dec 14, 2022
ping
liukun4515 commentedon Dec 15, 2022
we can close it after merged #4634