You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by GitBox <gi...@apache.org> on 2022/12/18 14:06:28 UTC
[GitHub] [arrow-datafusion] ygf11 opened a new pull request, #4666: Support type coercion for equijoin
ygf11 opened a new pull request, #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666
# Which issue does this PR close?
Closes #2877.
# Rationale for this change
<!--
Why are you proposing this change? If this is already explained clearly in the issue then this section is not needed.
Explaining clearly why changes are proposed helps reviewers understand your changes and offer better suggestions for fixes.
-->
See #2877
# What changes are included in this PR?
* The `LogicalPlan::expressions()` will combine the two side of equijoin expression to one equality expression, then save the equality expression to vec.
* The existent `type coercion` rule will do type coercion for it.
* The `from_plan` method will split each equality expression to two parts, and save them to the new join plan.
# Are these changes tested?
<!--
We typically require tests for all PRs in order to:
1. Prevent the code from being accidentally broken by subsequent changes
2. Serve as another way to document the expected behavior of the code
If tests are not included in your PR, please explain why (for example, are they covered by existing tests)?
-->
Yes, tests will cover it.
# Are there any user-facing changes?
<!--
If there are user-facing changes then we may require documentation to be updated before approving the PR.
-->
<!--
If there are any breaking changes to public APIs, please add the `api change` label.
-->
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] ygf11 commented on a diff in pull request #4666: Support type coercion for equijoin
Posted by GitBox <gi...@apache.org>.
ygf11 commented on code in PR #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666#discussion_r1053192878
##########
datafusion/expr/src/utils.rs:
##########
@@ -478,20 +479,33 @@ pub fn from_plan(
}) => {
let schema =
build_join_schema(inputs[0].schema(), inputs[1].schema(), join_type)?;
+
+ let equi_expr_count = on.len();
+ // The preceding part of expr is equi-exprs,
+ // and the struct of each equi-expr is like `left-expr = right-expr`.
+ let new_on:Vec<(Expr,Expr)> = expr.iter().take(equi_expr_count).map(|equi_expr| {
Review Comment:
Make sense, I added a check -- `assert!(expr.len() >= equi_expr_count)`.
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on a diff in pull request #4666: Support type coercion for equijoin
Posted by GitBox <gi...@apache.org>.
alamb commented on code in PR #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666#discussion_r1053641377
##########
datafusion/core/tests/sql/joins.rs:
##########
@@ -2778,3 +2781,141 @@ async fn select_wildcard_with_expr_key_inner_join() -> Result<()> {
Ok(())
}
+
+#[tokio::test]
+async fn join_with_type_coercion_for_equi_expr() -> Result<()> {
+ let ctx = create_join_context("t1_id", "t2_id", false)?;
+
+ let sql = "select t1.t1_id, t1.t1_name, t2.t2_id from t1 inner join t2 on t1.t1_id + 11 = t2.t2_id";
+
+ // assert logical plan
+ let msg = format!("Creating logical plan for '{}'", sql);
+ let plan = ctx
+ .create_logical_plan(&("explain ".to_owned() + sql))
+ .expect(&msg);
+ let state = ctx.state();
+ let plan = state.optimize(&plan)?;
+
+ let expected = vec![
+ "Explain [plan_type:Utf8, plan:Utf8]",
+ " Projection: t1.t1_id, t1.t1_name, t2.t2_id [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
+ " Inner Join: CAST(t1.t1_id AS Int64) + Int64(11) = CAST(t2.t2_id AS Int64) [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
+ " TableScan: t1 projection=[t1_id, t1_name] [t1_id:UInt32;N, t1_name:Utf8;N]",
+ " TableScan: t2 projection=[t2_id] [t2_id:UInt32;N]",
+ ];
+
+ let formatted = plan.display_indent_schema().to_string();
+ let actual: Vec<&str> = formatted.trim().lines().collect();
+ assert_eq!(
+ expected, actual,
+ "\n\nexpected:\n\n{:#?}\nactual:\n\n{:#?}\n\n",
+ expected, actual
+ );
+
+ let expected = vec![
+ "+-------+---------+-------+",
+ "| t1_id | t1_name | t2_id |",
+ "+-------+---------+-------+",
+ "| 11 | a | 22 |",
+ "| 33 | c | 44 |",
+ "| 44 | d | 55 |",
+ "+-------+---------+-------+",
+ ];
+
+ let results = execute_to_batches(&ctx, sql).await;
+ assert_batches_sorted_eq!(expected, &results);
+
+ Ok(())
+}
+
+#[tokio::test]
+async fn join_only_with_filter() -> Result<()> {
+ let ctx = create_join_context("t1_id", "t2_id", false)?;
+
+ let sql = "select t1.t1_id, t1.t1_name, t2.t2_id from t1 inner join t2 on t1.t1_id * 4 < t2.t2_id";
Review Comment:
Updated in e73faab7f
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] ursabot commented on pull request #4666: Support type coercion for equijoin
Posted by GitBox <gi...@apache.org>.
ursabot commented on PR #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666#issuecomment-1360394251
Benchmark runs are scheduled for baseline = fddb3d3651041f41d66a801f10e27387e84374f7 and contender = ac2e5d15e5452e83c835d793a95335e87bf35569. ac2e5d15e5452e83c835d793a95335e87bf35569 is a master commit associated with this PR. Results will be available as each benchmark for each run completes.
Conbench compare runs links:
[Skipped :warning: Benchmarking of arrow-datafusion-commits is not supported on ec2-t3-xlarge-us-east-2] [ec2-t3-xlarge-us-east-2](https://conbench.ursa.dev/compare/runs/a9a98ab3fd1e4d279574fd8854231958...7a9969adac2e4f6e9126f8ceef96437a/)
[Skipped :warning: Benchmarking of arrow-datafusion-commits is not supported on test-mac-arm] [test-mac-arm](https://conbench.ursa.dev/compare/runs/37b9e7153b734202aea8362ea1c6a238...f99d52fda2c546e4a50089aa490ee0ea/)
[Skipped :warning: Benchmarking of arrow-datafusion-commits is not supported on ursa-i9-9960x] [ursa-i9-9960x](https://conbench.ursa.dev/compare/runs/e4a5659f679a4851b5f76734cbec86e4...ef1dccc545dd426f923be09e4345bd5f/)
[Skipped :warning: Benchmarking of arrow-datafusion-commits is not supported on ursa-thinkcentre-m75q] [ursa-thinkcentre-m75q](https://conbench.ursa.dev/compare/runs/ac9ad5650b88432aa7a20b7c87f43712...93054a62d66b42c0b6229a2f3e350ab4/)
Buildkite builds:
Supported benchmarks:
ec2-t3-xlarge-us-east-2: Supported benchmark langs: Python, R. Runs only benchmarks with cloud = True
test-mac-arm: Supported benchmark langs: C++, Python, R
ursa-i9-9960x: Supported benchmark langs: Python, R, JavaScript
ursa-thinkcentre-m75q: Supported benchmark langs: C++, Java
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on a diff in pull request #4666: Support type coercion for equijoin
Posted by GitBox <gi...@apache.org>.
alamb commented on code in PR #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666#discussion_r1052569474
##########
datafusion/expr/src/utils.rs:
##########
@@ -478,20 +479,33 @@ pub fn from_plan(
}) => {
let schema =
build_join_schema(inputs[0].schema(), inputs[1].schema(), join_type)?;
+
+ let equi_expr_count = on.len();
+ // The preceding part of expr is equi-exprs,
+ // and the struct of each equi-expr is like `left-expr = right-expr`.
+ let new_on:Vec<(Expr,Expr)> = expr.iter().take(equi_expr_count).map(|equi_expr| {
+ if let Expr::BinaryExpr(BinaryExpr { left, op, right }) = equi_expr {
+ assert!(op == &Operator::Eq);
+ Ok(((**left).clone(), (**right).clone()))
+ } else {
+ Err(DataFusionError::Internal(format!(
Review Comment:
👍
##########
datafusion/core/tests/sql/joins.rs:
##########
@@ -2778,3 +2781,141 @@ async fn select_wildcard_with_expr_key_inner_join() -> Result<()> {
Ok(())
}
+
+#[tokio::test]
+async fn join_with_type_coercion_for_equi_expr() -> Result<()> {
+ let ctx = create_join_context("t1_id", "t2_id", false)?;
+
+ let sql = "select t1.t1_id, t1.t1_name, t2.t2_id from t1 inner join t2 on t1.t1_id + 11 = t2.t2_id";
+
+ // assert logical plan
+ let msg = format!("Creating logical plan for '{}'", sql);
+ let plan = ctx
+ .create_logical_plan(&("explain ".to_owned() + sql))
+ .expect(&msg);
+ let state = ctx.state();
+ let plan = state.optimize(&plan)?;
+
+ let expected = vec![
+ "Explain [plan_type:Utf8, plan:Utf8]",
+ " Projection: t1.t1_id, t1.t1_name, t2.t2_id [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
+ " Inner Join: CAST(t1.t1_id AS Int64) + Int64(11) = CAST(t2.t2_id AS Int64) [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
+ " TableScan: t1 projection=[t1_id, t1_name] [t1_id:UInt32;N, t1_name:Utf8;N]",
+ " TableScan: t2 projection=[t2_id] [t2_id:UInt32;N]",
+ ];
+
+ let formatted = plan.display_indent_schema().to_string();
+ let actual: Vec<&str> = formatted.trim().lines().collect();
+ assert_eq!(
+ expected, actual,
+ "\n\nexpected:\n\n{:#?}\nactual:\n\n{:#?}\n\n",
+ expected, actual
+ );
+
+ let expected = vec![
+ "+-------+---------+-------+",
+ "| t1_id | t1_name | t2_id |",
+ "+-------+---------+-------+",
+ "| 11 | a | 22 |",
+ "| 33 | c | 44 |",
+ "| 44 | d | 55 |",
+ "+-------+---------+-------+",
+ ];
+
+ let results = execute_to_batches(&ctx, sql).await;
+ assert_batches_sorted_eq!(expected, &results);
+
+ Ok(())
+}
+
+#[tokio::test]
+async fn join_only_with_filter() -> Result<()> {
+ let ctx = create_join_context("t1_id", "t2_id", false)?;
+
+ let sql = "select t1.t1_id, t1.t1_name, t2.t2_id from t1 inner join t2 on t1.t1_id * 4 < t2.t2_id";
+
+ // assert logical plan
+ let msg = format!("Creating logical plan for '{}'", sql);
+ let plan = ctx
+ .create_logical_plan(&("explain ".to_owned() + sql))
+ .expect(&msg);
+ let state = ctx.state();
+ let plan = state.optimize(&plan)?;
+
+ let expected = vec![
+ "Explain [plan_type:Utf8, plan:Utf8]",
+ " Projection: t1.t1_id, t1.t1_name, t2.t2_id [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
+ " Filter: CAST(t1.t1_id AS Int64) * Int64(4) < CAST(t2.t2_id AS Int64) [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
+ " CrossJoin: [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
+ " TableScan: t1 projection=[t1_id, t1_name] [t1_id:UInt32;N, t1_name:Utf8;N]",
+ " TableScan: t2 projection=[t2_id] [t2_id:UInt32;N]",
+ ];
+
+ let formatted = plan.display_indent_schema().to_string();
+ let actual: Vec<&str> = formatted.trim().lines().collect();
+ assert_eq!(
+ expected, actual,
+ "\n\nexpected:\n\n{:#?}\nactual:\n\n{:#?}\n\n",
+ expected, actual
+ );
+
+ let expected = vec![
+ "+-------+---------+-------+",
+ "| t1_id | t1_name | t2_id |",
+ "+-------+---------+-------+",
+ "| 11 | a | 55 |",
+ "+-------+---------+-------+",
+ ];
+
+ let results = execute_to_batches(&ctx, sql).await;
+ assert_batches_sorted_eq!(expected, &results);
+
+ Ok(())
+}
+
+#[tokio::test]
+async fn type_coercion_join_with_filter_and_equi_expr() -> Result<()> {
+ let ctx = create_join_context("t1_id", "t2_id", false)?;
+
+ let sql = "select t1.t1_id, t1.t1_name, t2.t2_id \
+ from t1 \
+ inner join t2 \
+ on t1.t1_id * 5 = t2.t2_id and t1.t1_id * 4 < t2.t2_id";
+
+ // assert logical plan
+ let msg = format!("Creating logical plan for '{}'", sql);
+ let plan = ctx
+ .create_logical_plan(&("explain ".to_owned() + sql))
+ .expect(&msg);
+ let state = ctx.state();
+ let plan = state.optimize(&plan)?;
+
+ let expected = vec![
+ "Explain [plan_type:Utf8, plan:Utf8]",
+ " Projection: t1.t1_id, t1.t1_name, t2.t2_id [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
+ " Inner Join: CAST(t1.t1_id AS Int64) * Int64(5) = CAST(t2.t2_id AS Int64) Filter: CAST(t1.t1_id AS Int64) * Int64(4) < CAST(t2.t2_id AS Int64) [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
Review Comment:
👍
##########
datafusion/core/tests/sql/joins.rs:
##########
@@ -2778,3 +2781,141 @@ async fn select_wildcard_with_expr_key_inner_join() -> Result<()> {
Ok(())
}
+
+#[tokio::test]
+async fn join_with_type_coercion_for_equi_expr() -> Result<()> {
+ let ctx = create_join_context("t1_id", "t2_id", false)?;
+
+ let sql = "select t1.t1_id, t1.t1_name, t2.t2_id from t1 inner join t2 on t1.t1_id + 11 = t2.t2_id";
+
+ // assert logical plan
+ let msg = format!("Creating logical plan for '{}'", sql);
+ let plan = ctx
+ .create_logical_plan(&("explain ".to_owned() + sql))
+ .expect(&msg);
+ let state = ctx.state();
+ let plan = state.optimize(&plan)?;
+
+ let expected = vec![
+ "Explain [plan_type:Utf8, plan:Utf8]",
+ " Projection: t1.t1_id, t1.t1_name, t2.t2_id [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
+ " Inner Join: CAST(t1.t1_id AS Int64) + Int64(11) = CAST(t2.t2_id AS Int64) [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
Review Comment:
I think eventually it would be great to have these casts unwrapped too, like
```suggestion
" Inner Join: t1.t1_id + Int32(11) = t2.t2_id [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
```
To avoid the runtime casting
I am not quite sure why https://github.com/apache/arrow-datafusion/blob/master/datafusion/optimizer/src/unwrap_cast_in_comparison.rs is not doing so
##########
datafusion/expr/src/utils.rs:
##########
@@ -478,20 +479,33 @@ pub fn from_plan(
}) => {
let schema =
build_join_schema(inputs[0].schema(), inputs[1].schema(), join_type)?;
+
+ let equi_expr_count = on.len();
+ // The preceding part of expr is equi-exprs,
+ // and the struct of each equi-expr is like `left-expr = right-expr`.
+ let new_on:Vec<(Expr,Expr)> = expr.iter().take(equi_expr_count).map(|equi_expr| {
Review Comment:
I think we should error here if `expr` does not has at least `equi_expr_count` elements left. Otherwise I think `take` will silently return fewer than `equi_expr_count` elements, which might result in quite hard to track down bugs
https://doc.rust-lang.org/std/iter/trait.Iterator.html#method.take
##########
datafusion/core/tests/sql/joins.rs:
##########
@@ -1448,11 +1448,11 @@ async fn hash_join_with_decimal() -> Result<()> {
let state = ctx.state();
let plan = state.optimize(&plan)?;
let expected = vec![
- "Explain [plan_type:Utf8, plan:Utf8]",
- " Projection: t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3, t2.c4 [c1:Date32;N, c2:Date64;N, c3:Decimal128(5, 2);N, c4:Dictionary(Int32, Utf8);N, c1:Date32;N, c2:Date64;N, c3:Decimal128(10, 2);N, c4:Dictionary(Int32, Utf8);N]",
- " Right Join: t1.c3 = t2.c3 [c1:Date32;N, c2:Date64;N, c3:Decimal128(5, 2);N, c4:Dictionary(Int32, Utf8);N, c1:Date32;N, c2:Date64;N, c3:Decimal128(10, 2);N, c4:Dictionary(Int32, Utf8);N]",
Review Comment:
what happened previously with this plan? Would it error at runtime?
##########
datafusion/expr/src/logical_plan/plan.rs:
##########
@@ -253,9 +253,12 @@ impl LogicalPlan {
aggr_expr,
..
}) => group_expr.iter().chain(aggr_expr.iter()).cloned().collect(),
+ // There are two part of expression for join, equijoin(on) and non-equijoin(filter).
+ // 1. the first part is `on.len()` equijoin expressions, and the struct of each expr is `left-on = right-on`.
+ // 2. the second part is non-equijoin(filter).
LogicalPlan::Join(Join { on, filter, .. }) => on
.iter()
- .flat_map(|(l, r)| vec![l.clone(), r.clone()])
+ .map(|(l, r)| Expr::eq(l.clone(), r.clone()))
Review Comment:
This is the fix, right? It then exposes the `<l> = <r>` expr to the existing type coercion logic ?
Very nice 👍
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] liukun4515 commented on a diff in pull request #4666: Support type coercion for equijoin
Posted by GitBox <gi...@apache.org>.
liukun4515 commented on code in PR #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666#discussion_r1053121582
##########
datafusion/expr/src/logical_plan/plan.rs:
##########
@@ -253,9 +253,12 @@ impl LogicalPlan {
aggr_expr,
..
}) => group_expr.iter().chain(aggr_expr.iter()).cloned().collect(),
+ // There are two part of expression for join, equijoin(on) and non-equijoin(filter).
Review Comment:
👍
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] ygf11 commented on pull request #4666: Support type coercion for equijoin
Posted by GitBox <gi...@apache.org>.
ygf11 commented on PR #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666#issuecomment-1357305311
@liukun4515 this is what we disscuss in https://github.com/apache/arrow-datafusion/issues/4389#issuecomment-1331669666.
Also cc @andygrove @alamb
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] ygf11 commented on a diff in pull request #4666: Support type coercion for equijoin
Posted by GitBox <gi...@apache.org>.
ygf11 commented on code in PR #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666#discussion_r1053069863
##########
datafusion/core/tests/sql/joins.rs:
##########
@@ -1448,11 +1448,11 @@ async fn hash_join_with_decimal() -> Result<()> {
let state = ctx.state();
let plan = state.optimize(&plan)?;
let expected = vec![
- "Explain [plan_type:Utf8, plan:Utf8]",
- " Projection: t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3, t2.c4 [c1:Date32;N, c2:Date64;N, c3:Decimal128(5, 2);N, c4:Dictionary(Int32, Utf8);N, c1:Date32;N, c2:Date64;N, c3:Decimal128(10, 2);N, c4:Dictionary(Int32, Utf8);N]",
- " Right Join: t1.c3 = t2.c3 [c1:Date32;N, c2:Date64;N, c3:Decimal128(5, 2);N, c4:Dictionary(Int32, Utf8);N, c1:Date32;N, c2:Date64;N, c3:Decimal128(10, 2);N, c4:Dictionary(Int32, Utf8);N]",
Review Comment:
No, it succeeded without type coercion, but I think it is a coincidence.
The reason is for `decimal eq` operation, we only check value, but do not check the `precision` and `scale` are same.
In this test, each `c3` of the two matched rows have the same value, the `third` row is `789000` and the `fourth` is `-12312`, then it succeeded.
```rust
"+------------+------------+---------+-----+------------+------------+-----------+---------+",
"| c1 | c2 | c3 | c4 | c1 | c2 | c3 | c4 |",
"+------------+------------+---------+-----+------------+------------+-----------+---------+",
"| | | | | | | 100000.00 | abcdefg |",
"| | | | | | 1970-01-04 | 0.00 | qwerty |",
"| | 1970-01-04 | 789.00 | ghi | 1970-01-04 | | 789.00 | |",
"| 1970-01-04 | | -123.12 | jkl | 1970-01-02 | 1970-01-02 | -123.12 | abc |",
"+------------+------------+---------+-----+------------+------------+-----------+---------+",
```
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] liukun4515 commented on a diff in pull request #4666: Support type coercion for equijoin
Posted by GitBox <gi...@apache.org>.
liukun4515 commented on code in PR #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666#discussion_r1053117398
##########
datafusion/core/tests/sql/joins.rs:
##########
@@ -2778,3 +2781,141 @@ async fn select_wildcard_with_expr_key_inner_join() -> Result<()> {
Ok(())
}
+
+#[tokio::test]
+async fn join_with_type_coercion_for_equi_expr() -> Result<()> {
+ let ctx = create_join_context("t1_id", "t2_id", false)?;
+
+ let sql = "select t1.t1_id, t1.t1_name, t2.t2_id from t1 inner join t2 on t1.t1_id + 11 = t2.t2_id";
+
+ // assert logical plan
+ let msg = format!("Creating logical plan for '{}'", sql);
+ let plan = ctx
+ .create_logical_plan(&("explain ".to_owned() + sql))
+ .expect(&msg);
+ let state = ctx.state();
+ let plan = state.optimize(&plan)?;
+
+ let expected = vec![
+ "Explain [plan_type:Utf8, plan:Utf8]",
+ " Projection: t1.t1_id, t1.t1_name, t2.t2_id [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
+ " Inner Join: CAST(t1.t1_id AS Int64) + Int64(11) = CAST(t2.t2_id AS Int64) [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
Review Comment:
This rule just can apply to the pattern
```
expr `op` literal
```
cc @alamb
We can file a new issue to discuss this.
I must point out a problem about overflow for `add` operation, for example `i32::max` + `i32::max` maybe overflow.
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on pull request #4666: Support type coercion for equijoin
Posted by GitBox <gi...@apache.org>.
alamb commented on PR #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666#issuecomment-1359985090
I took the liberty of merging this branch with the latest `master` branch and resolving the logical plan conflict in e73faab7f -- I plan to merge this PR when CI passes
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb merged pull request #4666: Support type coercion for equijoin
Posted by GitBox <gi...@apache.org>.
alamb merged PR #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] liukun4515 commented on a diff in pull request #4666: Support type coercion for equijoin
Posted by GitBox <gi...@apache.org>.
liukun4515 commented on code in PR #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666#discussion_r1053117398
##########
datafusion/core/tests/sql/joins.rs:
##########
@@ -2778,3 +2781,141 @@ async fn select_wildcard_with_expr_key_inner_join() -> Result<()> {
Ok(())
}
+
+#[tokio::test]
+async fn join_with_type_coercion_for_equi_expr() -> Result<()> {
+ let ctx = create_join_context("t1_id", "t2_id", false)?;
+
+ let sql = "select t1.t1_id, t1.t1_name, t2.t2_id from t1 inner join t2 on t1.t1_id + 11 = t2.t2_id";
+
+ // assert logical plan
+ let msg = format!("Creating logical plan for '{}'", sql);
+ let plan = ctx
+ .create_logical_plan(&("explain ".to_owned() + sql))
+ .expect(&msg);
+ let state = ctx.state();
+ let plan = state.optimize(&plan)?;
+
+ let expected = vec![
+ "Explain [plan_type:Utf8, plan:Utf8]",
+ " Projection: t1.t1_id, t1.t1_name, t2.t2_id [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
+ " Inner Join: CAST(t1.t1_id AS Int64) + Int64(11) = CAST(t2.t2_id AS Int64) [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
Review Comment:
This rule just can apply the pattern
```
expr `op` literal
```
cc @alamb
We can file a new issue to discuss this.
I must point out a problem about overflow for `add` operation, for example `i32::max` + `i32::max` maybe overflow.
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] liukun4515 commented on a diff in pull request #4666: Support type coercion for equijoin
Posted by GitBox <gi...@apache.org>.
liukun4515 commented on code in PR #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666#discussion_r1053121944
##########
datafusion/expr/src/utils.rs:
##########
@@ -478,20 +479,33 @@ pub fn from_plan(
}) => {
let schema =
build_join_schema(inputs[0].schema(), inputs[1].schema(), join_type)?;
+
+ let equi_expr_count = on.len();
+ // The preceding part of expr is equi-exprs,
Review Comment:
👍
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] ygf11 commented on a diff in pull request #4666: Support type coercion for equijoin
Posted by GitBox <gi...@apache.org>.
ygf11 commented on code in PR #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666#discussion_r1053069863
##########
datafusion/core/tests/sql/joins.rs:
##########
@@ -1448,11 +1448,11 @@ async fn hash_join_with_decimal() -> Result<()> {
let state = ctx.state();
let plan = state.optimize(&plan)?;
let expected = vec![
- "Explain [plan_type:Utf8, plan:Utf8]",
- " Projection: t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3, t2.c4 [c1:Date32;N, c2:Date64;N, c3:Decimal128(5, 2);N, c4:Dictionary(Int32, Utf8);N, c1:Date32;N, c2:Date64;N, c3:Decimal128(10, 2);N, c4:Dictionary(Int32, Utf8);N]",
- " Right Join: t1.c3 = t2.c3 [c1:Date32;N, c2:Date64;N, c3:Decimal128(5, 2);N, c4:Dictionary(Int32, Utf8);N, c1:Date32;N, c2:Date64;N, c3:Decimal128(10, 2);N, c4:Dictionary(Int32, Utf8);N]",
Review Comment:
No, it succeeded without type coercion, but I think it is a coincidence.
The reason is for `decimal eq` operation, we only check value, but do not check the `precision` and `scale` are same.
In this test, the each c3 of the matched two rows has the same value -- `789000` and `-12312`, then it succeeded without type coercion.
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] liukun4515 commented on a diff in pull request #4666: Support type coercion for equijoin
Posted by GitBox <gi...@apache.org>.
liukun4515 commented on code in PR #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666#discussion_r1053120198
##########
datafusion/core/tests/sql/joins.rs:
##########
@@ -2778,3 +2781,141 @@ async fn select_wildcard_with_expr_key_inner_join() -> Result<()> {
Ok(())
}
+
+#[tokio::test]
+async fn join_with_type_coercion_for_equi_expr() -> Result<()> {
+ let ctx = create_join_context("t1_id", "t2_id", false)?;
+
+ let sql = "select t1.t1_id, t1.t1_name, t2.t2_id from t1 inner join t2 on t1.t1_id + 11 = t2.t2_id";
+
+ // assert logical plan
+ let msg = format!("Creating logical plan for '{}'", sql);
+ let plan = ctx
+ .create_logical_plan(&("explain ".to_owned() + sql))
+ .expect(&msg);
+ let state = ctx.state();
+ let plan = state.optimize(&plan)?;
+
+ let expected = vec![
+ "Explain [plan_type:Utf8, plan:Utf8]",
+ " Projection: t1.t1_id, t1.t1_name, t2.t2_id [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
+ " Inner Join: CAST(t1.t1_id AS Int64) + Int64(11) = CAST(t2.t2_id AS Int64) [t1_id:UInt32;N, t1_name:Utf8;N, t2_id:UInt32;N]",
+ " TableScan: t1 projection=[t1_id, t1_name] [t1_id:UInt32;N, t1_name:Utf8;N]",
+ " TableScan: t2 projection=[t2_id] [t2_id:UInt32;N]",
+ ];
+
+ let formatted = plan.display_indent_schema().to_string();
+ let actual: Vec<&str> = formatted.trim().lines().collect();
+ assert_eq!(
+ expected, actual,
+ "\n\nexpected:\n\n{:#?}\nactual:\n\n{:#?}\n\n",
+ expected, actual
+ );
+
+ let expected = vec![
+ "+-------+---------+-------+",
+ "| t1_id | t1_name | t2_id |",
+ "+-------+---------+-------+",
+ "| 11 | a | 22 |",
+ "| 33 | c | 44 |",
+ "| 44 | d | 55 |",
+ "+-------+---------+-------+",
+ ];
+
+ let results = execute_to_batches(&ctx, sql).await;
+ assert_batches_sorted_eq!(expected, &results);
+
+ Ok(())
+}
+
+#[tokio::test]
+async fn join_only_with_filter() -> Result<()> {
+ let ctx = create_join_context("t1_id", "t2_id", false)?;
+
+ let sql = "select t1.t1_id, t1.t1_name, t2.t2_id from t1 inner join t2 on t1.t1_id * 4 < t2.t2_id";
Review Comment:
after https://github.com/apache/arrow-datafusion/pull/4562 merged, the plan will be converted to NLJ
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] liukun4515 commented on pull request #4666: Support type coercion for equijoin
Posted by GitBox <gi...@apache.org>.
liukun4515 commented on PR #4666:
URL: https://github.com/apache/arrow-datafusion/pull/4666#issuecomment-1359071437
Sorry for the late reply, I want to take a look this pr tomorrow.
cc @alamb
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org