You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by "jiangzhx (via GitHub)" <gi...@apache.org> on 2023/04/07 07:02:38 UTC
[GitHub] [arrow-datafusion] jiangzhx opened a new pull request, #5907: optimize non-correlated where exists subquery
jiangzhx opened a new pull request, #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907
# Which issue does this PR close?
<!--
We generally require a GitHub issue to be filed for all bug fixes and enhancements and this helps us generate change logs for our releases. You can link an issue to this PR using the GitHub syntax. For example `Closes #123` indicates that this PR will close issue #123.
-->
Closes #5789.
# 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.
-->
# What changes are included in this PR?
<!--
There is no need to duplicate the description in the issue here but it is sometimes worth providing a summary of the individual changes in this PR.
-->
# 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)?
-->
# 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] mingmwang commented on pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "mingmwang (via GitHub)" <gi...@apache.org>.
mingmwang commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1513356957
Here it is:
https://github.com/cube-js/arrow-datafusion/commit/d14f0de4c042c9a14b40acd4768c6e4f6585ee93#diff-3f4616eb49284f339cb8f9e501af7f0df3af700520e955de000dd0b42e870291
--
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] jiangzhx commented on pull request #5907: optimize non-correlated where exists subquery rewrite to left semi join
Posted by "jiangzhx (via GitHub)" <gi...@apache.org>.
jiangzhx commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1546656654
Hi @jackwener, I wanted to update you that I've tried to unnest the 'where exists' clause to a 'left semi join', as we discussed earlier. If you have a moment, could you review it and let me know your thoughts? Thanks for your help!
--
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] mingmwang commented on pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "mingmwang (via GitHub)" <gi...@apache.org>.
mingmwang commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1533995329
> what is the status of this PR? Is it ready for review? Should we close it (or mark it as draft) as we work on an alternate implementation?
@alamb I think you can mark this PR as draft.
--
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] jiangzhx commented on a diff in pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "jiangzhx (via GitHub)" <gi...@apache.org>.
jiangzhx commented on code in PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#discussion_r1166651258
##########
datafusion/core/tests/sql/subqueries.rs:
##########
@@ -129,12 +129,14 @@ async fn exists_subquery_with_same_table() -> Result<()> {
let expected = vec![
"Explain [plan_type:Utf8, plan:Utf8]",
- " Filter: EXISTS (<subquery>) [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
- " Subquery: [t1_int:UInt32;N]",
- " Projection: t1.t1_int [t1_int:UInt32;N]",
- " Filter: t1.t1_id > t1.t1_int [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
- " TableScan: t1 [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
- " TableScan: t1 projection=[t1_id, t1_name, t1_int] [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
+ " Projection: t1.t1_id, t1.t1_name, t1.t1_int [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
+ " CrossJoin: [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N, COUNT(UInt8(1)):Int64;N]",
+ " TableScan: t1 projection=[t1_id, t1_name, t1_int] [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
+ " SubqueryAlias: __scalar_sq_1 [COUNT(UInt8(1)):Int64;N]",
+ " Filter: COUNT(UInt8(1)) > Int64(0) [COUNT(UInt8(1)):Int64;N]",
+ " Aggregate: groupBy=[[]], aggr=[[COUNT(UInt8(1))]] [COUNT(UInt8(1)):Int64;N]",
+ " Filter: t1.t1_id > t1.t1_int [t1_id:UInt32;N, t1_int:UInt32;N]",
+ " TableScan: t1 projection=[t1_id, t1_int] [t1_id:UInt32;N, t1_int:UInt32;N]",
Review Comment:
> actually do an aggregate to count how many rows
like spark did, the better way is
```
WHERE EXISTS (SELECT b FROM t2 );
rewrite to
WHERE (SELECT 1 FROM (SELECT b FROM t2) LIMIT 1) IS NOT NULL
```
but now the ScalarSubqueryToJoin optimizer can not rewrite this to join
--
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] jiangzhx commented on a diff in pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "jiangzhx (via GitHub)" <gi...@apache.org>.
jiangzhx commented on code in PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#discussion_r1166648579
##########
datafusion/core/tests/sql/subqueries.rs:
##########
@@ -129,12 +129,14 @@ async fn exists_subquery_with_same_table() -> Result<()> {
let expected = vec![
"Explain [plan_type:Utf8, plan:Utf8]",
- " Filter: EXISTS (<subquery>) [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
- " Subquery: [t1_int:UInt32;N]",
- " Projection: t1.t1_int [t1_int:UInt32;N]",
- " Filter: t1.t1_id > t1.t1_int [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
- " TableScan: t1 [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
- " TableScan: t1 projection=[t1_id, t1_name, t1_int] [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
+ " Projection: t1.t1_id, t1.t1_name, t1.t1_int [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
+ " CrossJoin: [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N, COUNT(UInt8(1)):Int64;N]",
+ " TableScan: t1 projection=[t1_id, t1_name, t1_int] [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
+ " SubqueryAlias: __scalar_sq_1 [COUNT(UInt8(1)):Int64;N]",
+ " Filter: COUNT(UInt8(1)) > Int64(0) [COUNT(UInt8(1)):Int64;N]",
+ " Aggregate: groupBy=[[]], aggr=[[COUNT(UInt8(1))]] [COUNT(UInt8(1)):Int64;N]",
+ " Filter: t1.t1_id > t1.t1_int [t1_id:UInt32;N, t1_int:UInt32;N]",
+ " TableScan: t1 projection=[t1_id, t1_int] [t1_id:UInt32;N, t1_int:UInt32;N]",
Review Comment:
> I think the classic way to rewrite a subquery like
>
> ```
> WHERE EXISTS (SELECT b FROM t2 where a>1 );
> ```
>
> Is to use a SEMI Join
>
> ```
> SemiJoin ()
> Filter(a > 1)
> TableScan(a, b)
> ```
>
> I don't think it is necessary to actually do an aggregate to count how many rows -- all that is needed is to check if there is any row
>
> Perhaps @jackwener or @ygf11 have some suggestion on the way to extend subquery rewrites to support non correlated exists 🤔
I am also confused, should directly from exists to join or let other optimizer to do the job.
there already a optimizer ScalarSubqueryToJoin can do the job from scalar subquery to Join.
like spark did
https://github.com/apache/spark/blob/4c938d62d791742b9f0c6a77b66fc06a90d7c0ad/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/finishAnalysis.scala#L62-L72
--
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] mingmwang commented on pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "mingmwang (via GitHub)" <gi...@apache.org>.
mingmwang commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1521389557
> ```sql
> LogicalCrossJoin
> ```
> Thanks @jiangzhx @mingmwang .
>
> I think a easy way is rewritten by using `limit 1`
>
> like this
>
> ```sql
> | LogicalProject |
> | +--LogicalCrossJoin |
> | |--LogicalProject |
> | | +--LogicalFilter[223] |
> | | +--LogicalOlapScan |
> | +--LogicalLimit ( limit=1, offset=0 ) |
> | +--LogicalProject[230] |
> | +--LogicalFilter[229]
> ```
I will start the enhancement and improvement on the current de-correlation rules.
Before that, there is some refactoring and code clean up work need to do first.
--
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 #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1504086091
I plan to review this carefully tomorrow
--
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] mingmwang commented on pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "mingmwang (via GitHub)" <gi...@apache.org>.
mingmwang commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1513345216
SparkSQL's plan
```
== Optimized Logical Plan ==
Filter isnotnull(scalar-subquery#44687821 []), Statistics(sizeInBytes=1.0 B)
: +- GlobalLimit 1
: +- LocalLimit 1
: +- Project [1 AS col#44687836]
: +- Filter (isnotnull(t2_id#44684191) AND (t2_id#44684191 > 0))
: +- Relation access_views.t2[t2_id#44684191,t2_name#44684192] parquet, Statistics(sizeInBytes=0.0 B)
+- Relation access_views.t1[t1_id#44683269,t1_name#44683270] parquet, Statistics(sizeInBytes=0.0 B)
```
PG:
```
QUERY PLAN
Result (cost=0.13..13.33 rows=320 width=222)
One-Time Filter: $0
InitPlan 1 (returns $0)
-> Seq Scan on t2 (cost=0.00..14.00 rows=107 width=0)
Filter: (id > 0)
-> Seq Scan on t1 (cost=0.13..13.33 rows=320 width=222)
```
--
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] jiangzhx commented on pull request #5907: optimize non-correlated where exists subquery rewrite to left semi join
Posted by "jiangzhx (via GitHub)" <gi...@apache.org>.
jiangzhx commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1567938163
there is a better choice.i will close this pr
--
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] mingmwang commented on pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "mingmwang (via GitHub)" <gi...@apache.org>.
mingmwang commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1513313446
I will take a look. Usually, for un-correlated scalar subqueries, it will be kept as the subquery but need to do count check.
WHERE EXISTS (SELECT b FROM t2 where a>1 )
rewrite to
WHERE EXISTS (SELECT b FROM t2 where a>1 limit 2)
We need to add a physical SubQueryExec.
--
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] jiangzhx commented on pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "jiangzhx (via GitHub)" <gi...@apache.org>.
jiangzhx commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1513437056
> I will take a look. Usually, for un-correlated scalar subqueries, it will be kept as the subquery but need to do count check.
>
> WHERE EXISTS (SELECT b FROM t2 where a>1 )
>
> rewrite to
>
> WHERE ScalarSubQuery(SELECT b FROM t2 where a>1 limit 1)
>
> We need to add a physical SubQueryExec.
yes，you are right,as you expressed, in Spark, rewriting `exists` to `scalar subquery` can solve the query requirements for this scenario.
```
/**
* Rewrite non correlated exists subquery to use ScalarSubquery
* WHERE EXISTS (SELECT A FROM TABLE B WHERE COL1 > 10)
* will be rewritten to
* WHERE (SELECT 1 FROM (SELECT A FROM TABLE B WHERE COL1 > 10) LIMIT 1) IS NOT NULL
*/
object RewriteNonCorrelatedExists extends Rule[LogicalPlan] {
override def apply(plan: LogicalPlan): LogicalPlan = plan.transformAllExpressionsWithPruning(
_.containsPattern(EXISTS_SUBQUERY)) {
case exists: Exists if exists.children.isEmpty =>
IsNotNull(
ScalarSubquery(
plan = Limit(Literal(1), Project(Seq(Alias(Literal(1), "col")()), exists.plan)),
exprId = exists.exprId,
hint = exists.hint))
}
}
```
--
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] jiangzhx commented on a diff in pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "jiangzhx (via GitHub)" <gi...@apache.org>.
jiangzhx commented on code in PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#discussion_r1166651258
##########
datafusion/core/tests/sql/subqueries.rs:
##########
@@ -129,12 +129,14 @@ async fn exists_subquery_with_same_table() -> Result<()> {
let expected = vec![
"Explain [plan_type:Utf8, plan:Utf8]",
- " Filter: EXISTS (<subquery>) [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
- " Subquery: [t1_int:UInt32;N]",
- " Projection: t1.t1_int [t1_int:UInt32;N]",
- " Filter: t1.t1_id > t1.t1_int [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
- " TableScan: t1 [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
- " TableScan: t1 projection=[t1_id, t1_name, t1_int] [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
+ " Projection: t1.t1_id, t1.t1_name, t1.t1_int [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
+ " CrossJoin: [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N, COUNT(UInt8(1)):Int64;N]",
+ " TableScan: t1 projection=[t1_id, t1_name, t1_int] [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
+ " SubqueryAlias: __scalar_sq_1 [COUNT(UInt8(1)):Int64;N]",
+ " Filter: COUNT(UInt8(1)) > Int64(0) [COUNT(UInt8(1)):Int64;N]",
+ " Aggregate: groupBy=[[]], aggr=[[COUNT(UInt8(1))]] [COUNT(UInt8(1)):Int64;N]",
+ " Filter: t1.t1_id > t1.t1_int [t1_id:UInt32;N, t1_int:UInt32;N]",
+ " TableScan: t1 projection=[t1_id, t1_int] [t1_id:UInt32;N, t1_int:UInt32;N]",
Review Comment:
> actually do an aggregate to count how many rows
like spark did, the better way is
```
WHERE EXISTS (SELECT b FROM t2 where a>1 );
rewrite to
WHERE (SELECT 1 FROM (SELECT b FROM t2) LIMIT 1) IS NOT NULL
```
but now the ScalarSubqueryToJoin optimizer can not rewrite this to join
--
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] jiangzhx commented on a diff in pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "jiangzhx (via GitHub)" <gi...@apache.org>.
jiangzhx commented on code in PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#discussion_r1166648579
##########
datafusion/core/tests/sql/subqueries.rs:
##########
@@ -129,12 +129,14 @@ async fn exists_subquery_with_same_table() -> Result<()> {
let expected = vec![
"Explain [plan_type:Utf8, plan:Utf8]",
- " Filter: EXISTS (<subquery>) [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
- " Subquery: [t1_int:UInt32;N]",
- " Projection: t1.t1_int [t1_int:UInt32;N]",
- " Filter: t1.t1_id > t1.t1_int [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
- " TableScan: t1 [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
- " TableScan: t1 projection=[t1_id, t1_name, t1_int] [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
+ " Projection: t1.t1_id, t1.t1_name, t1.t1_int [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
+ " CrossJoin: [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N, COUNT(UInt8(1)):Int64;N]",
+ " TableScan: t1 projection=[t1_id, t1_name, t1_int] [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
+ " SubqueryAlias: __scalar_sq_1 [COUNT(UInt8(1)):Int64;N]",
+ " Filter: COUNT(UInt8(1)) > Int64(0) [COUNT(UInt8(1)):Int64;N]",
+ " Aggregate: groupBy=[[]], aggr=[[COUNT(UInt8(1))]] [COUNT(UInt8(1)):Int64;N]",
+ " Filter: t1.t1_id > t1.t1_int [t1_id:UInt32;N, t1_int:UInt32;N]",
+ " TableScan: t1 projection=[t1_id, t1_int] [t1_id:UInt32;N, t1_int:UInt32;N]",
Review Comment:
> I think the classic way to rewrite a subquery like
>
> ```
> WHERE EXISTS (SELECT b FROM t2 where a>1 );
> ```
>
> Is to use a SEMI Join
>
> ```
> SemiJoin ()
> Filter(a > 1)
> TableScan(a, b)
> ```
>
> I don't think it is necessary to actually do an aggregate to count how many rows -- all that is needed is to check if there is any row
>
> Perhaps @jackwener or @ygf11 have some suggestion on the way to extend subquery rewrites to support non correlated exists 🤔
I am also confused, should directly from exists to join or let other optimizer to do the job.
there already a optimizer ScalarSubqueryToJoin can do the job from scalar subquery to Join.
like spark did,just rewrite exists to scalar subquery
https://github.com/apache/spark/blob/4c938d62d791742b9f0c6a77b66fc06a90d7c0ad/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/finishAnalysis.scala#L62-L72
--
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] jiangzhx closed pull request #5907: optimize non-correlated where exists subquery rewrite to left semi join
Posted by "jiangzhx (via GitHub)" <gi...@apache.org>.
jiangzhx closed pull request #5907: optimize non-correlated where exists subquery rewrite to left semi join
URL: https://github.com/apache/arrow-datafusion/pull/5907
--
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] mingmwang commented on pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "mingmwang (via GitHub)" <gi...@apache.org>.
mingmwang commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1513353636
@jiangzhx
Why not add a new `SubQueryExec` for all the uncorrelated subqueries? Since we will need this for those subqueries that can not be de-correlated.
I remember this was a similar PR for the purpose, but that PR was not merged or it was in someone's personal branch.
--
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] jiangzhx commented on pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "jiangzhx (via GitHub)" <gi...@apache.org>.
jiangzhx commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1513429846
> @jiangzhx Why not add a new `SubQueryExec` for all the uncorrelated subqueries? Since we will need this for those subqueries that can not be de-correlated. I remember this was a similar PR for the purpose, but that PR was not merged or it was in someone's personal branch.
@mingmwang Thank you very much for your response. Adding a SubQueryExec can solve many subquery scenarios. If this issue is not urgent, I would like to try working on it, even though I don't have much experience in this area.
--
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 #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1533658143
what is the status of this PR? Is it ready for review? Should we close it (or mark it as draft) as we work on an alternate implementation?
--
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] jiangzhx commented on pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "jiangzhx (via GitHub)" <gi...@apache.org>.
jiangzhx commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1534027456
I just finished my vacation, and I will resume this work.
--
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] jackwener commented on pull request #5907: optimize non-correlated where exists subquery rewrite to left semi join
Posted by "jackwener (via GitHub)" <gi...@apache.org>.
jackwener commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1546804535
It's OK, thank you @jiangzhx
--
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 #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#discussion_r1165877527
##########
datafusion/core/tests/sql/subqueries.rs:
##########
@@ -129,12 +129,14 @@ async fn exists_subquery_with_same_table() -> Result<()> {
let expected = vec![
"Explain [plan_type:Utf8, plan:Utf8]",
- " Filter: EXISTS (<subquery>) [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
- " Subquery: [t1_int:UInt32;N]",
- " Projection: t1.t1_int [t1_int:UInt32;N]",
- " Filter: t1.t1_id > t1.t1_int [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
- " TableScan: t1 [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
- " TableScan: t1 projection=[t1_id, t1_name, t1_int] [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
+ " Projection: t1.t1_id, t1.t1_name, t1.t1_int [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
+ " CrossJoin: [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N, COUNT(UInt8(1)):Int64;N]",
+ " TableScan: t1 projection=[t1_id, t1_name, t1_int] [t1_id:UInt32;N, t1_name:Utf8;N, t1_int:UInt32;N]",
+ " SubqueryAlias: __scalar_sq_1 [COUNT(UInt8(1)):Int64;N]",
+ " Filter: COUNT(UInt8(1)) > Int64(0) [COUNT(UInt8(1)):Int64;N]",
+ " Aggregate: groupBy=[[]], aggr=[[COUNT(UInt8(1))]] [COUNT(UInt8(1)):Int64;N]",
+ " Filter: t1.t1_id > t1.t1_int [t1_id:UInt32;N, t1_int:UInt32;N]",
+ " TableScan: t1 projection=[t1_id, t1_int] [t1_id:UInt32;N, t1_int:UInt32;N]",
Review Comment:
I think the classic way to rewrite a subquery like
```
WHERE EXISTS (SELECT b FROM t2 where a>1 );
```
Is to use a SEMI Join
```
SemiJoin ()
Filter(a > 1)
TableScan(a, b)
```
I don't think it is necessary to actually do an aggregate to count how many rows -- all that is needed is to check if there is any row
Perhaps @jackwener or @ygf11 have some suggestion on the way to extend subquery rewrites to support non correlated exists 🤔
--
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] jackwener commented on pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "jackwener (via GitHub)" <gi...@apache.org>.
jackwener commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1510407392
Current DF isn't good enough about `Subquery Unnesting`.
I am going to not only review this PR, but I want to study this issue systematically, it need some time.
--
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] mingmwang commented on pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "mingmwang (via GitHub)" <gi...@apache.org>.
mingmwang commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1513924591
> > @jiangzhx Why not add a new `SubQueryExec` for all the uncorrelated subqueries? Since we will need this for those subqueries that can not be de-correlated. I remember this was a similar PR for the purpose, but that PR was not merged or it was in someone's personal branch.
>
> @mingmwang Thank you very much for your response. Adding a SubQueryExec can solve many subquery scenarios. If this issue is not urgent, I would like to try working on it, even though I don't have much experience in this area.
Sure, thanks a lot. And I think this is not urgent.
--
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] jiangzhx commented on pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "jiangzhx (via GitHub)" <gi...@apache.org>.
jiangzhx commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1521714484
> > ```sql
> > LogicalCrossJoin
> > ```
>
> > Thanks @jiangzhx @mingmwang .
> > I think a easy way is rewritten by using `limit 1`
> > like this
> > ```sql
> > | LogicalProject |
> > | +--LogicalCrossJoin |
> > | |--LogicalProject |
> > | | +--LogicalFilter[223] |
> > | | +--LogicalOlapScan |
> > | +--LogicalLimit ( limit=1, offset=0 ) |
> > | +--LogicalProject[230] |
> > | +--LogicalFilter[229]
> > ```
>
> I will start the enhancement and improvement on the current de-correlation rules. Before that, there is some refactoring and code clean up work need to do first.
If there is anything that I need to do, I am happy to do it.
--
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] jackwener commented on pull request #5907: optimize non-correlated where exists subquery rewrite to scalar subquery
Posted by "jackwener (via GitHub)" <gi...@apache.org>.
jackwener commented on PR #5907:
URL: https://github.com/apache/arrow-datafusion/pull/5907#issuecomment-1517828937
Thanks @jiangzhx @mingmwang .
I think a easy way is rewritten by using `limit 1`
--
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