haohuaijin opened a new pull request, #8833: URL: https://github.com/apache/arrow-datafusion/pull/8833
## Which issue does this PR close?
Closes #8814
## Rationale for this change
as shown in #8814, in `CASE WHEN A THEN B ELSE C END` expr, the B and C only
have one can run,
so for query
```
select
case when B.column1 > 0 then A.column1/B.column1 else 0 end as value1,
case when B.column1 > 0 and B.column2 > 0 then A.column1/B.column1 else 0
end as value3
from (select column1, column2 from users) as A , (select column1, column2
from users where column1 = 0) as B;
```
the `A.column1/B.column1` actually should not run, but we extract it as a
common sub expr(see below plan), which results in `A.column1/B.column1` running
and getting a divide zero error.
```
| initial_logical_plan | Projection:
CASE WHEN b.column1 > Int64(0) THEN a.column1 / b.column1 ELSE Int64(0) END AS
value1, CASE WHEN b.column2 > Int64(0) THEN a.column1 / b.column1 ELSE Int64(0)
END AS value3
|
| | CrossJoin:
|
| |
SubqueryAlias: a
|
| |
Projection: users.column1, users.column2
|
| |
TableScan: users
|
| |
SubqueryAlias: b
|
| |
Projection: users.column1, users.column2
|
| |
Filter: users.column1 = Int64(0)
|
| |
TableScan: users
|
|
| logical_plan after common_sub_expression_eliminate | Projection:
CASE WHEN b.column1 > Int64(0) THEN a.column1 / b.column1b.column1a.column1 AS
a.column1 / b.column1 ELSE Int64(0) END AS value1, CASE WHEN b.column2 >
Int64(0) THEN a.column1 / b.column1b.column1a.column1 AS a.column1 / b.column1
ELSE Int64(0) END AS value3
|
| | Projection:
a.column1 / b.column1 AS a.column1 / b.column1b.column1a.column1, a.column1,
a.column2, b.column1, b.column2
|
| |
CrossJoin:
|
| |
SubqueryAlias: a
|
| |
Projection: users.column1, users.column2
|
| |
TableScan: users
|
| |
SubqueryAlias: b
|
| |
Projection: users.column1, users.column2
|
| |
Filter: users.column1 = Int64(0)
|
| |
TableScan: users
```
## What changes are included in this PR?
## Are these changes tested?
yes, add a test.
## Are there any user-facing changes?
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
