You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "jonahgao (via GitHub)" <gi...@apache.org> on 2024/07/12 08:57:04 UTC
[PR] fix: make sure JOIN ON expression is boolean type [datafusion]
jonahgao opened a new pull request, #11423:
URL: https://github.com/apache/datafusion/pull/11423
## Which issue does this PR close?
Closes #11414.
## Rationale for this change
The query in the issue generates the following plan after some optimizations.
```sql
select * from t1 inner join t2 on null right join t3 on true;
```
```sh
Projection: t1.v1, t2.v1, t3.v1
Right Join:
Inner Join:
Filter: Boolean(true) AND NULL
TableScan: t1
TableScan: t2
TableScan: t3
```
When the `simplify_expressions` rule attempts to simplify `Boolean(true) AND NULL`, it fails because the operand types on both sides are different (`DataType::Boolean` vs `DataType::NULL`).
The fix is to cast the NULL type to Boolean type.
## What changes are included in this PR?
## Are these changes tested?
Yes
## Are there any user-facing changes?
No
--
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: [PR] fix: make sure JOIN ON expression is boolean type [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #11423:
URL: https://github.com/apache/datafusion/pull/11423#discussion_r1675699247
##########
datafusion/sql/src/relation/join.rs:
##########
@@ -107,7 +110,20 @@ impl<'a, S: ContextProvider> SqlToRel<'a, S> {
JoinConstraint::On(sql_expr) => {
let join_schema = left.schema().join(right.schema())?;
// parse ON expression
- let expr = self.sql_to_expr(sql_expr, &join_schema, planner_context)?;
+ let mut expr =
+ self.sql_to_expr(sql_expr, &join_schema, planner_context)?;
+ // ON expression must be a boolean expression
+ match expr.get_type(&join_schema)? {
+ DataType::Boolean => {}
+ DataType::Null => {
+ expr = Expr::Cast(Cast::new(expr.into(), DataType::Boolean))
+ }
+ other => {
+ return plan_err!(
+ "ON expression must be boolean, but got {other:?}"
Review Comment:
I think the same rule applies to other APIs (not just SQL).
What would you think about moving the check to `LogicalPlanBuilder`
https://github.com/apache/datafusion/blob/6e637488188c6620ecd113bf47987bd98f6d7871/datafusion/expr/src/logical_plan/builder.rs#L671-L686
That way this check would be applied to the DataFrame API as well as any custom plans that were created?
##########
datafusion/sqllogictest/test_files/join.slt:
##########
@@ -998,11 +997,22 @@ CREATE TABLE t2 (v0 DOUBLE) AS VALUES (-1.663563947387);
statement ok
CREATE TABLE t3 (v0 DOUBLE) AS VALUES (0.05112015193508901);
+# Test issue: https://github.com/apache/datafusion/issues/11269
query RR
SELECT t3.v0, t2.v0 FROM t1,t2,t3 WHERE t3.v0 >= t1.v0;
----
0.051120151935 -1.663563947387
+# Test issue: https://github.com/apache/datafusion/issues/11414
+query IRR
+SELECT * FROM t1 INNER JOIN t2 ON NULL RIGHT JOIN t3 ON TRUE;
+----
+NULL NULL 0.051120151935
+
+# ON expression must be boolean type
+query error DataFusion error: Error during planning: ON expression must be boolean, but got Utf8
+SELECT * FROM t1 INNER JOIN t2 ON 'TRUE'
Review Comment:
I think the error is clear and is a reasonable behavior. If it is important for someone's usecase, perhaps we can add support for it then
--
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: [PR] fix: make sure JOIN ON expression is boolean type [datafusion]
Posted by "jonahgao (via GitHub)" <gi...@apache.org>.
jonahgao commented on code in PR #11423:
URL: https://github.com/apache/datafusion/pull/11423#discussion_r1676031292
##########
datafusion/sql/src/relation/join.rs:
##########
@@ -107,7 +110,20 @@ impl<'a, S: ContextProvider> SqlToRel<'a, S> {
JoinConstraint::On(sql_expr) => {
let join_schema = left.schema().join(right.schema())?;
// parse ON expression
- let expr = self.sql_to_expr(sql_expr, &join_schema, planner_context)?;
+ let mut expr =
+ self.sql_to_expr(sql_expr, &join_schema, planner_context)?;
+ // ON expression must be a boolean expression
+ match expr.get_type(&join_schema)? {
+ DataType::Boolean => {}
+ DataType::Null => {
+ expr = Expr::Cast(Cast::new(expr.into(), DataType::Boolean))
+ }
+ other => {
+ return plan_err!(
+ "ON expression must be boolean, but got {other:?}"
Review Comment:
Done. I re-implemented it in `type_coercion` to avoid creating a temporary [joined schema](https://github.com/apache/datafusion/blob/1dfac86a89750193491cf3e04917e37b92c64ffa/datafusion/sql/src/relation/join.rs#L108). Also added a test for DataFrame.
--
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: [PR] fix: make sure JOIN ON expression is boolean type [datafusion]
Posted by "jonahgao (via GitHub)" <gi...@apache.org>.
jonahgao commented on code in PR #11423:
URL: https://github.com/apache/datafusion/pull/11423#discussion_r1675556217
##########
datafusion/sqllogictest/test_files/join.slt:
##########
@@ -998,11 +997,22 @@ CREATE TABLE t2 (v0 DOUBLE) AS VALUES (-1.663563947387);
statement ok
CREATE TABLE t3 (v0 DOUBLE) AS VALUES (0.05112015193508901);
+# Test issue: https://github.com/apache/datafusion/issues/11269
query RR
SELECT t3.v0, t2.v0 FROM t1,t2,t3 WHERE t3.v0 >= t1.v0;
----
0.051120151935 -1.663563947387
+# Test issue: https://github.com/apache/datafusion/issues/11414
+query IRR
+SELECT * FROM t1 INNER JOIN t2 ON NULL RIGHT JOIN t3 ON TRUE;
+----
+NULL NULL 0.051120151935
+
+# ON expression must be boolean type
+query error DataFusion error: Error during planning: ON expression must be boolean, but got Utf8
+SELECT * FROM t1 INNER JOIN t2 ON 'TRUE'
Review Comment:
This query is available in PostgreSQL, but not available in Spark.
If we plan to support it, we can directly cast the ON expression to boolean without checking its current type.
--
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: [PR] fix: make sure JOIN ON expression is boolean type [datafusion]
Posted by "jonahgao (via GitHub)" <gi...@apache.org>.
jonahgao commented on code in PR #11423:
URL: https://github.com/apache/datafusion/pull/11423#discussion_r1676040776
##########
datafusion/core/src/dataframe/mod.rs:
##########
@@ -896,9 +896,8 @@ impl DataFrame {
join_type: JoinType,
on_exprs: impl IntoIterator<Item = Expr>,
) -> Result<DataFrame> {
- let expr = on_exprs.into_iter().reduce(Expr::and);
Review Comment:
It's redundant because the reduce operation will be performed inside the [join_on](https://github.com/apache/datafusion/blob/1dfac86a89750193491cf3e04917e37b92c64ffa/datafusion/expr/src/logical_plan/builder.rs#L677) function.
--
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: [PR] fix: make sure JOIN ON expression is boolean type [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #11423:
URL: https://github.com/apache/datafusion/pull/11423#discussion_r1676334942
##########
datafusion/sqllogictest/test_files/join.slt:
##########
@@ -998,11 +997,22 @@ CREATE TABLE t2 (v0 DOUBLE) AS VALUES (-1.663563947387);
statement ok
CREATE TABLE t3 (v0 DOUBLE) AS VALUES (0.05112015193508901);
+# Test issue: https://github.com/apache/datafusion/issues/11269
query RR
SELECT t3.v0, t2.v0 FROM t1,t2,t3 WHERE t3.v0 >= t1.v0;
----
0.051120151935 -1.663563947387
+# Test issue: https://github.com/apache/datafusion/issues/11414
+query IRR
+SELECT * FROM t1 INNER JOIN t2 ON NULL RIGHT JOIN t3 ON TRUE;
+----
+NULL NULL 0.051120151935
+
+# ON expression must be boolean type
+query error DataFusion error: Error during planning: ON expression must be boolean, but got Utf8
+SELECT * FROM t1 INNER JOIN t2 ON 'TRUE'
Review Comment:
I think the structure you have now implemented (in the analyzer) makes it pretty easy to support if desired
##########
datafusion/optimizer/src/analyzer/type_coercion.rs:
##########
@@ -151,9 +151,27 @@ impl<'a> TypeCoercionRewriter<'a> {
})
.collect::<Result<Vec<_>>>()?;
+ // Join filter must be boolean
+ join.filter = join
+ .filter
+ .map(|expr| self.coerce_join_filter(expr))
+ .transpose()?;
+
Ok(LogicalPlan::Join(join))
}
+ fn coerce_join_filter(&self, expr: Expr) -> Result<Expr> {
+ let expr_type = expr.get_type(self.schema)?;
+ match expr_type {
+ DataType::Boolean => Ok(expr),
+ DataType::Null => Ok(Expr::Cast(expr::Cast::new(
+ Box::new(expr),
+ DataType::Boolean,
+ ))),
Review Comment:
I think this could be more concise if we used `Expr::cast_to, like this:
```suggestion
DataType::Null => expr.cast_to(&DataType::Boolean, self.schema),
```
--
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: [PR] fix: make sure JOIN ON expression is boolean type [datafusion]
Posted by "jonahgao (via GitHub)" <gi...@apache.org>.
jonahgao commented on code in PR #11423:
URL: https://github.com/apache/datafusion/pull/11423#discussion_r1676791583
##########
datafusion/optimizer/src/analyzer/type_coercion.rs:
##########
@@ -151,9 +151,27 @@ impl<'a> TypeCoercionRewriter<'a> {
})
.collect::<Result<Vec<_>>>()?;
+ // Join filter must be boolean
+ join.filter = join
+ .filter
+ .map(|expr| self.coerce_join_filter(expr))
+ .transpose()?;
+
Ok(LogicalPlan::Join(join))
}
+ fn coerce_join_filter(&self, expr: Expr) -> Result<Expr> {
+ let expr_type = expr.get_type(self.schema)?;
+ match expr_type {
+ DataType::Boolean => Ok(expr),
+ DataType::Null => Ok(Expr::Cast(expr::Cast::new(
+ Box::new(expr),
+ DataType::Boolean,
+ ))),
Review Comment:
I agree, thank you @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@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: [PR] fix: make sure JOIN ON expression is boolean type [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb merged PR #11423:
URL: https://github.com/apache/datafusion/pull/11423
--
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