You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by GitBox <gi...@apache.org> on 2021/04/22 20:03:32 UTC
[GitHub] [spark] allisonwang-db opened a new pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
allisonwang-db opened a new pull request #32303:
URL: https://github.com/apache/spark/pull/32303
### What changes were proposed in this pull request?
This PR adds support for lateral subqueries. A lateral subquery is a subquery preceded by the `LATERAL` keyword in the FROM clause of a query that can reference columns in the preceding FROM items. For example:
```sql
SELECT * FROM t1, LATERAL (SELECT * FROM t2 WHERE t1.a = t2.c)
```
A new join type: `LateralJoin` is introduced to represent a join with a lateral subquery. Currently `INNER`, `CROSS` and `LEFT OUTER` join types are supported with lateral join. Here is the analyzed plan for the above query:
```scala
Project [a, b, c, d]
:- Join LateralJoin(Inner)
:- Relation t1
+- Project [c, d]
+- Filter (outer(a) = c)
+- Relation t2
```
Similar to a correlated subquery, a lateral join can be viewed as a dependent (nested loop) join where the evaluation of the right subtree depends on the current value of the left subtree. The same technique to decorrelate a subquery is used to decorrelate a lateral join:
```scala
Project [a, b, c, d]
:- Join LateralJoin(Inner) (a = c) // pull up correlated predicates as join conditions
:- Relation t1
+- Project [c1, c2]
+- Relation t2
```
Then a lateral join can be rewritten into a normal join:
```scala
Project [a, b, c, d]
:- Join Inner (a = c)
:- Relation t1
+- Project [c1, c2]
+- Relation t2
```
#### What is not supported:
- Correlation in the right subtree of a lateral join. This means a lateral subquery cannot contain another lateral or correlated subquery.
Other limitations are the same as correlated subqueries as they use the same decorrelation framework.
**Note:** similar to rewriting correlated scalar subqueries, rewriting lateral joins is also subject to the COUNT bug (See SPARK-15370 for more details). This is **not** handled in the current PR as it requires a sizeable amount of refactoring. It will be addressed in a subsequent PR.
### Why are the changes needed?
To support an ANSI SQL feature.
### Does this PR introduce _any_ user-facing change?
Yes. It allows users to use the `LATERAL` keyword in the FROM clause of a query.
### How was this patch tested?
- Parser test: `PlanParserSuite.scala`
- Analyzer test: `ResolveLateralJoinSuite.scala`
- Optimizer test: `PullupCorrelatedPredicatesSuite.scala`
- SQL test: `join-lateral.sql`
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857389131
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-846313915
Kubernetes integration test starting
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/43339/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857460918
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139528/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647229290
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
##########
@@ -871,7 +871,13 @@ class AstBuilder extends SqlBaseBaseVisitor[AnyRef] with SQLConfHelper with Logg
override def visitFromClause(ctx: FromClauseContext): LogicalPlan = withOrigin(ctx) {
val from = ctx.relation.asScala.foldLeft(null: LogicalPlan) { (left, relation) =>
val right = plan(relation.relationPrimary)
- val join = right.optionalMap(left)(Join(_, _, Inner, None, JoinHint.NONE))
+ val join = right.optionalMap(left) { (left, right) =>
+ if (relation.LATERAL != null) {
+ LateralJoin(left, LateralSubquery(right), Inner, None)
Review comment:
Hints exist for the physical planner and the planner does not tell a difference between normal joins and lateral joins (cuz the optimizer rewrites lateral join into normal ones), so adding hints for this case makes sense to me. In fact, I think users will eventually want to control physical joins types for lateral joins, too, to tune user's queries for their workloads.　Anyway, since this is an improvement, I think it is okay to fix it in follow-up.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827131355
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/137967/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r619335548
##########
File path: sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
##########
@@ -588,7 +588,7 @@ hintStatement
;
fromClause
- : FROM relation (',' relation)* lateralView* pivotClause?
+ : FROM relation (',' lateralClause)* (',' relation)* lateralView* pivotClause?
Review comment:
Yea, it seems PosgreSQL accepts simple cases without outer refs.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r646814848
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -2364,11 +2364,20 @@ class Analyzer(override val catalogManager: CatalogManager)
* Resolve and rewrite all subqueries in an operator tree..
*/
def apply(plan: LogicalPlan): LogicalPlan = plan.resolveOperatorsUpWithPruning(
- _.containsAnyPattern(SCALAR_SUBQUERY, EXISTS_SUBQUERY, IN_SUBQUERY), ruleId) {
+ _.containsPattern(PLAN_EXPRESSION), ruleId) {
// In case of HAVING (a filter after an aggregate) we use both the aggregate and
// its child for resolution.
case f @ Filter(_, a: Aggregate) if f.childrenResolved =>
resolveSubQueries(f, Seq(a, a.child))
+ case j @ LateralJoin(left, right, _, condition) if left.resolved && !right.resolved =>
+ val newRight = resolveSubQuery(right, j.children)(LateralSubquery(_, _, right.exprId))
+ .asInstanceOf[LateralSubquery]
+ val newCond = if (newRight.resolved && condition.exists(!_.resolved)) {
+ condition.map(resolveExpressionByPlanOutput(_, newRight.plan))
Review comment:
How about we overwrite `resolveChildren` in `LateralJoin`, so that we consider input columns from both left and right tables? Then we don't need the special logic here, and we also follow normal join behavior and fail analysis if the name is ambiguous (`t1 JOIN LATERAL t2 ON a = 1` while both t1 and t2 have column a).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r639390074
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -174,13 +174,21 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
hof.failAnalysis(
s"cannot resolve '${hof.sql}' due to argument data type mismatch: $message")
}
+ // Check if there are expressions with children containing star expressions.
+ case e if e.children.exists(_.isInstanceOf[Star]) =>
+ e.failAnalysis(s"Invalid usage of '*' in expression '${e.prettyName}'")
}
operator transformExpressionsUp {
case a: Attribute if !a.resolved =>
val from = operator.inputSet.toSeq.map(_.qualifiedName).mkString(", ")
a.failAnalysis(s"cannot resolve '${a.sql}' given input columns: [$from]")
+ case s @ UnresolvedStar(Some(target)) =>
Review comment:
Yup L178 checks invalid star usage inside expressions first, for example, `sum(t.*)` and here the unresolved star is not inside any expressions.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853543357
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139250/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837593393
**[Test build #138348 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/138348/testReport)** for PR 32303 at commit [`62d8a97`](https://github.com/apache/spark/commit/62d8a9756239ba92129ce6a20d037aa00b0707f1).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-854249253
@maropu @cloud-fan @viirya
I updated the logical plan structure to handle a few cases I found during the test that do not work with the original plan representation. Originally, we directly use the logical Join node with a new join type `LateralJoin`. This structure can't handle the case when deduping relations, for instance:
```scala
'Join
:- LocalRelation [a, b]
+- Join LateralJoin(Inner)
:- LocalRelation [a, b] <--- after DeduplicateRelation: LocalRelation ['a, 'b]
+- Project [outer(a) + outer(b)] <--- but outer references won't be updated
+- OneRowRelation
```
`DeduplicateRelation` rule will return a new instance of the `LocalRelation[a, b]`, but the current `transformUpWithNewOutput` won't be able to update the existing outer references in the right subtree. Also, the resolution logic has a lot of overlaps with `ResolveSubquery`. The new logic unifies the analysis logic for lateral subqueries and existing subqueries. It adds a new unary node `LateralJoin` and a new subquery expression `LateralSubquery` to represent a lateral subquery. I've updated the PR description.
Also, I removed the logic to resolve star expressions in subqueries. It is not lateral subquery specific and I've created a new JIRA ticket: SPARK-35618.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647026800
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/PlanHelper.scala
##########
@@ -45,6 +45,7 @@ object PlanHelper {
case e: AggregateExpression
if !(plan.isInstanceOf[Aggregate] ||
plan.isInstanceOf[Window] ||
+ plan.isInstanceOf[LateralJoin] ||
Review comment:
This is because the children (outerAttrs) field can contain aggregate expressions `LateralJoin(left, LateralSubquery(plan, [sum(a)]), ..)` when collecting outer references in the subquery:
https://github.com/apache/spark/blob/dfd8a8dc676c388c0c1bb7e4cb8d55eab10504ab/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/subquery.scala#L201-L211
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-825313305
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/137824/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857577807
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857840813
Kubernetes integration test starting
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/44106/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853709790
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139269/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857577807
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857393663
**[Test build #139543 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139543/testReport)** for PR 32303 at commit [`08385f4`](https://github.com/apache/spark/commit/08385f4a0cbba256ae93131100aee16db2feef64).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857404004
Kubernetes integration test status success
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/44053/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857387520
**[Test build #139540 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139540/testReport)** for PR 32303 at commit [`eb46d13`](https://github.com/apache/spark/commit/eb46d13a2b5696bd0307c4727a69af5a06de92d9).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857650250
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139562/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-825309546
**[Test build #137824 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/137824/testReport)** for PR 32303 at commit [`8f6ea0a`](https://github.com/apache/spark/commit/8f6ea0a385beda190f93b7edff587d4d4e2aef18).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds the following public classes _(experimental)_:
* `case class ExpandedStar(star: UnresolvedStar) extends Star with Unevaluable `
* `case class LateralJoin(tpe: JoinType) extends JoinType `
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853625431
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/43792/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853524175
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/43774/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837901718
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/138348/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857584466
**[Test build #139546 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139546/testReport)** for PR 32303 at commit [`1b6d8f3`](https://github.com/apache/spark/commit/1b6d8f31d2dcce6ebfdd5709b3d61ad5cef9a8b9).
* This patch **fails Spark unit tests**.
* This patch merges cleanly.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857455557
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/44064/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan closed pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan closed pull request #32303:
URL: https://github.com/apache/spark/pull/32303
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857554026
**[Test build #139535 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139535/testReport)** for PR 32303 at commit [`f00f210`](https://github.com/apache/spark/commit/f00f210cafbaae94d0fdf81d44d678172a75b1d1).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857393663
**[Test build #139543 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139543/testReport)** for PR 32303 at commit [`08385f4`](https://github.com/apache/spark/commit/08385f4a0cbba256ae93131100aee16db2feef64).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r620004435
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/unresolved.scala
##########
@@ -392,16 +392,27 @@ case class UnresolvedStar(target: Option[Seq[String]]) extends Star with Unevalu
throw QueryCompilationErrors.starExpandDataTypeNotSupportedError(target.get)
}
} else {
- val from = input.inputSet.map(_.name).mkString(", ")
- val targetString = target.get.mkString(".")
- throw QueryCompilationErrors.cannotResolveStarExpandGivenInputColumnsError(
- targetString, from)
+ // Change the unresolved star to an expanded star to indicate the star has been
+ // expanded once.
+ ExpandedStar(this) :: Nil
}
}
override def toString: String = target.map(_ + ".").getOrElse("") + "*"
}
+/**
+ * Represent an [[UnresolvedStar]] that has been to expand once.
+ * This can be used to resolve a star expression using multiple plans.
+ */
+case class ExpandedStar(star: UnresolvedStar) extends Star with Unevaluable {
Review comment:
This is used to differenate between an unresolved star and a star that has been tried to resolved once. It's intended to keep the error message consistent when the star usage is invalid in an expression. But indeed this is confusing and I will think of other ways to check the invalid usage.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853510102
Kubernetes integration test status success
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/43774/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-846320283
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/43339/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-828917759
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/42578/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857454162
Kubernetes integration test unable to build dist.
exiting with code: 1
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/44064/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-825154175
**[Test build #137824 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/137824/testReport)** for PR 32303 at commit [`8f6ea0a`](https://github.com/apache/spark/commit/8f6ea0a385beda190f93b7edff587d4d4e2aef18).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r622637714
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -2234,6 +2260,76 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
+ /**
+ * This rule resolves lateral joins.
+ */
+ object ResolveLateralJoin extends Rule[LogicalPlan] {
+ import ResolveReferences._
+
+ /**
+ * Build a project list for Project/Aggregate and expand the star if possible by first using
+ * the inner query plan. If failed, use the outer query plan to expand the star and wrap all
+ * expanded attributes in [[OuterReference]]s.
+ */
+ private def expandOuterReference(
+ expressions: Seq[NamedExpression],
+ inner: LogicalPlan,
+ outer: LogicalPlan): Seq[NamedExpression] = {
+
+ // Expand the star expression using the inner plan.
+ def expandInner(star: Star): Seq[NamedExpression] =
+ star.expand(inner, resolver)
+
+ // Leave the star unchanged if the outer plan is unable to resolve the star.
+ // Otherwise wrap the resolved attributes in outer references.
+ def expandOuter(star: Star): Seq[NamedExpression] = {
+ star.expand(outer, resolver).map {
+ case s: Star => s
+ case other => other
+ .transform { case a: Attribute => OuterReference(a) }
+ .asInstanceOf[NamedExpression]
+ }
+ }
+
+ buildExpandedProjectList(expressions, inner, expandInner) match {
+ case expanded if !containsStar(expanded) => expanded
+ // Expand the remaining star expressions using the outer query plan.
+ case other => buildExpandedProjectList(other, outer, expandOuter)
+ }
+ }
+
+ /**
+ * Resolve the right sub-tree by first using the right query plan itself, and then try
+ * resolving the unresolved attributes and star expressions using the left query plan.
+ */
+ private def resolveRightChild(right: LogicalPlan, left: LogicalPlan): LogicalPlan = {
+ right.resolveOperators {
+ case p: LogicalPlan if !p.childrenResolved => p
+ case p: Project if containsStar(p.projectList) =>
+ p.copy(projectList = expandOuterReference(p.projectList, p.child, left))
+ case a: Aggregate if containsStar(a.aggregateExpressions) =>
+ a.copy(aggregateExpressions =
+ expandOuterReference(a.aggregateExpressions, a.child, left))
+ case p: LogicalPlan if !p.resolved =>
+ p transformExpressions {
+ case u @ UnresolvedAttribute(nameParts) =>
+ withPosition(u) {
+ p.resolveChildren(nameParts, resolver)
+ .orElse(resolveLiteralFunction(nameParts))
+ .orElse(resolveOuterReference(nameParts, left))
+ .getOrElse(u)
+ }
+ }
+ }
+ }
+
+ override def apply(plan: LogicalPlan): LogicalPlan = plan.resolveOperatorsUp {
+ case j @ Join(left, right, LateralJoin(_), _, _) if left.resolved && !right.resolved =>
Review comment:
This is trickier than expected. We can't simply check that `right` doesn't contain unresolved lateral join because the left subtree of a nested lateral join can contain outer references:
```sql
SELECT * FROM t1, LATERAL (SELECT * FROM (SELECT t1.c1 + 1 AS c1), LATERAL (SELECT c1))
```
Plan
```scala
Join LateralJoin (1)
:- Relation T1
+- Join LateralJoin (2)
:- Project [unresolvedalias(t1.c1 + 1, c1)]
: +- OneRowRelation
+- Project ['c1]
+- OneRowRelation
```
Here the outermost lateral join's right subtree will get resolved first, which will resolve `'c1` as `t1.c1`. But in this case `c1` should be resolved as `t1.c1 + a AS c1` from the inner lateral join's left subtree instead of `t1.c1` (double checked with Postgres). I will update the resolution logic here.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827100090
> ```
> scala> sql("SELECT * FROM (VALUES (1), (2)) tx(a), LATERAL (SELECT a FROM tx) ty(a)").show()
> org.apache.spark.sql.AnalysisException: Table or view not found: tx; line 1 pos 62;
> 'Project [*]
> +- 'Join LateralJoin(Inner)
> :- SubqueryAlias tx
> : +- Project [col1#345 AS a#346]
> : +- LocalRelation [col1#345]
> +- 'SubqueryAlias ty
> +- 'UnresolvedSubqueryColumnAliases [a]
> +- 'Project ['a]
> +- 'UnresolvedRelation [tx], [], false
> ```
>
> How bout the case above? The table resolution couldn't work well.
In this case `tx` in `LATERAL (SELECT a FROM tx)` will be resolved as a relation instead of a lateral reference. Postgres also raises an error in this case:
```
SELECT * FROM (VALUES (1), (2)) tx(a), LATERAL (SELECT a FROM tx) ty(a)
relation "tx" does not exist
```
Both of the following queries will work:
```sql
SELECT * FROM (VALUES (1), (2)) tx(a), LATERAL (SELECT a) ty(a)
SELECT * FROM (VALUES (1), (2)) tx(a), LATERAL (SELECT tx.a) ty(a)
```
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647032535
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuery.scala
##########
@@ -244,7 +244,17 @@ object DecorrelateInnerQuery extends PredicateHelper {
case _ => Join(child, domain, Inner, None, JoinHint.NONE)
}
} else {
- throw QueryExecutionErrors.cannotRewriteDomainJoinWithConditionsError(conditions, d)
+ // Leave it unchanged when the domain join does not belong to the current outer
+ // plan, i.e we cannot construct the domain attribute mapping from the join conditions.
+ // This can happen when the outer plan itself has another DomainJoin. E.g:
+ // LateralJoin lateral-subquery#263 [c1#262 && (c1#271 <=> c1#262)], Inner
+ // : +- Project [c1#271 AS c1#265, c1#271]
+ // : +- DomainJoin [c1#271]
+ // : +- OneRowRelation
+ // +- Project [(c1#270 + 1) AS c1#262, c1#270]
+ // +- DomainJoin [c1#270]
+ // +- OneRowRelation
+ d
Review comment:
It will be rewritten during the optimization for the outer lateral joins. You can think of each DomainJoin has a "scope" defined, i.e which outer plan it belongs to, and here we only rewrite a domain join if it belongs to the current outer plan.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837081829
**[Test build #138341 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/138341/testReport)** for PR 32303 at commit [`f726c83`](https://github.com/apache/spark/commit/f726c8375e70f3fb984276e713fca747fe765f45).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-825187747
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/42354/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857499054
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/44068/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837637417
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857497315
**[Test build #139562 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139562/testReport)** for PR 32303 at commit [`1b6d8f3`](https://github.com/apache/spark/commit/1b6d8f31d2dcce6ebfdd5709b3d61ad5cef9a8b9).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-825993263
> It would be nice to add a doc page for the literal join in follow-up PRs.
Sounds good. Created SPARK-35208 as a follow up task.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857586028
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139546/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857876277
The GA failure is in `Linters, licenses, dependencies and documentation generation`, which is unrelated to this PR. I'm merging it to master, thanks!
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647191162
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/basicLogicalOperators.scala
##########
@@ -1419,3 +1419,58 @@ case class DomainJoin(domainAttrs: Seq[Attribute], child: LogicalPlan) extends U
override protected def withNewChildInternal(newChild: LogicalPlan): DomainJoin =
copy(child = newChild)
}
+
+/**
+ * A logical plan for lateral join.
+ */
+case class LateralJoin(
+ left: LogicalPlan,
+ right: LateralSubquery,
+ joinType: JoinType,
+ condition: Option[Expression]) extends UnaryNode {
+
+ require(Seq(Inner, LeftOuter, Cross).contains(joinType),
+ s"Unsupported lateral join type $joinType")
+
+ override def child: LogicalPlan = left
+
+ override def output: Seq[Attribute] = {
+ joinType match {
+ case LeftOuter => left.output ++ right.plan.output.map(_.withNullability(true))
+ case _ => left.output ++ right.plan.output
+ }
+ }
+
+ /**
+ * Optionally resolves the given strings to a [[NamedExpression]] using the input from
+ * both the left plan and the lateral subquery's plan.
+ */
+ override def resolveChildren(
+ nameParts: Seq[String],
+ resolver: Resolver): Option[NamedExpression] = {
+ val children = left :: right.plan :: Nil
+ val childAttributes = AttributeSeq(children.flatMap(_.output))
+ val childMetadataAttributes = AttributeSeq(children.flatMap(_.metadataOutput))
Review comment:
to speed this up, can we add
```
private[this] lazy val childAttributes = AttributeSeq(...)
private[this] lazy val childMetadataAttributes = AttributeSeq(...)
```
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647186984
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -2357,18 +2356,22 @@ class Analyzer(override val catalogManager: CatalogManager)
ListQuery(plan, exprs, exprId, plan.output)
})
InSubquery(values, expr.asInstanceOf[ListQuery])
+ case s @ LateralSubquery(sub, _, exprId, _) if !sub.resolved =>
+ resolveSubQuery(s, plans)(LateralSubquery(_, _, exprId))
}
}
/**
* Resolve and rewrite all subqueries in an operator tree..
*/
def apply(plan: LogicalPlan): LogicalPlan = plan.resolveOperatorsUpWithPruning(
- _.containsAnyPattern(SCALAR_SUBQUERY, EXISTS_SUBQUERY, IN_SUBQUERY), ruleId) {
+ _.containsPattern(PLAN_EXPRESSION), ruleId) {
// In case of HAVING (a filter after an aggregate) we use both the aggregate and
// its child for resolution.
case f @ Filter(_, a: Aggregate) if f.childrenResolved =>
resolveSubQueries(f, Seq(a, a.child))
+ case j @ LateralJoin(left, right, _, _) if left.resolved && !right.resolved =>
Review comment:
nit: do we need `!right.resolved`? Other cases don't have 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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827205038
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857417819
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853693049
**[Test build #139269 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139269/testReport)** for PR 32303 at commit [`b83c571`](https://github.com/apache/spark/commit/b83c57108d6cfb839128e8e554d8f2f51906cf83).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857280116
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/44035/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857343892
**[Test build #139528 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139528/testReport)** for PR 32303 at commit [`8f19ab7`](https://github.com/apache/spark/commit/8f19ab732fa0117a72626c601e964140c9b4bd42).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857459416
**[Test build #139528 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139528/testReport)** for PR 32303 at commit [`8f19ab7`](https://github.com/apache/spark/commit/8f19ab732fa0117a72626c601e964140c9b4bd42).
* This patch **fails PySpark unit tests**.
* This patch merges cleanly.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-846320283
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r620553370
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/DeduplicateRelations.scala
##########
@@ -156,6 +156,16 @@ object DeduplicateRelations extends Rule[LogicalPlan] {
if findAliases(projectList).intersect(conflictingAttributes).nonEmpty =>
Seq((oldVersion, oldVersion.copy(projectList = newAliases(projectList))))
+ // Handle projects that create conflicting outer references.
+ case oldVersion @ Project(projectList, _)
+ if findOuterReferences(projectList).intersect(conflictingAttributes).nonEmpty =>
+ // Add alias to conflicting outer references.
+ val aliasedProjectList = projectList.map {
+ case o @ OuterReference(a) if conflictingAttributes.contains(a) => Alias(o, a.name)()
+ case other => other
+ }
Review comment:
Outer references can be inside an expression tree, which can be handled by the deduplicating aliases case, for example `outer(a) AS a`. Here the dedup logic is trying to handle this case:
```
Join LatealJoin(Inner)
:- Project [c1#0, c2#0]
:- ...
+- Project [outer(c1#0)]
+- ...
```
Since an outer reference is also a named expression, it can appear in the project list without an alias. Then `c1#0` and `outer(c1#0)` will conflict with each other. (`outer(c1#0).toAttribute => c1#0`)
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r639383358
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -1607,33 +1622,30 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
f1.copy(arguments = f1.arguments.flatMap {
- case s: Star => s.expand(child, resolver)
+ case s: Star => expand(s)
case o => o :: Nil
})
case c: CreateNamedStruct if containsStar(c.valExprs) =>
val newChildren = c.children.grouped(2).flatMap {
- case Seq(k, s : Star) => CreateStruct(s.expand(child, resolver)).children
+ case Seq(k, s : Star) => CreateStruct(expand(s)).children
case kv => kv
}
c.copy(children = newChildren.toList )
case c: CreateArray if containsStar(c.children) =>
c.copy(children = c.children.flatMap {
- case s: Star => s.expand(child, resolver)
+ case s: Star => expand(s)
case o => o :: Nil
})
case p: Murmur3Hash if containsStar(p.children) =>
p.copy(children = p.children.flatMap {
- case s: Star => s.expand(child, resolver)
+ case s: Star => expand(s)
case o => o :: Nil
})
case p: XxHash64 if containsStar(p.children) =>
p.copy(children = p.children.flatMap {
- case s: Star => s.expand(child, resolver)
+ case s: Star => expand(s)
case o => o :: Nil
})
- // count(*) has been replaced by count(1)
- case o if containsStar(o.children) =>
- throw QueryCompilationErrors.invalidStarUsageError(s"expression '${o.prettyName}'")
Review comment:
Yes it was moved to CheckAnalysis because the star expression might be resolved using outer plan references.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827086777
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/42487/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-828914017
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r620004435
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/unresolved.scala
##########
@@ -392,16 +392,27 @@ case class UnresolvedStar(target: Option[Seq[String]]) extends Star with Unevalu
throw QueryCompilationErrors.starExpandDataTypeNotSupportedError(target.get)
}
} else {
- val from = input.inputSet.map(_.name).mkString(", ")
- val targetString = target.get.mkString(".")
- throw QueryCompilationErrors.cannotResolveStarExpandGivenInputColumnsError(
- targetString, from)
+ // Change the unresolved star to an expanded star to indicate the star has been
+ // expanded once.
+ ExpandedStar(this) :: Nil
}
}
override def toString: String = target.map(_ + ".").getOrElse("") + "*"
}
+/**
+ * Represent an [[UnresolvedStar]] that has been to expand once.
+ * This can be used to resolve a star expression using multiple plans.
+ */
+case class ExpandedStar(star: UnresolvedStar) extends Star with Unevaluable {
Review comment:
This is used to differenate between an unresolved star and a star that has been tried to resolved once. It's intended to keep the error message consistent when the star usage is invalid in an expression. But indeed this is confusing and I will think of other ways to check the invalid usage.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857366765
**[Test build #139535 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139535/testReport)** for PR 32303 at commit [`f00f210`](https://github.com/apache/spark/commit/f00f210cafbaae94d0fdf81d44d678172a75b1d1).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-825505071
cc @maropu @viirya
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-833958597
@maropu @viirya @cloud-fan Thanks for the review! I've addressed the comments and activated some Postgres SQL tests for lateral subqueries. The results also match Postgres' results.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647092456
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -784,11 +791,10 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
failAnalysis(s"IN/EXISTS predicate sub-queries can only be used in" +
s" Filter/Join and a few commands: $plan")
}
+ // Validate to make sure the correlations appearing in the query are valid and
+ // allowed by spark.
+ checkCorrelationsInSubquery(expr.plan)
}
-
- // Validate to make sure the correlations appearing in the query are valid and
- // allowed by spark.
- checkCorrelationsInSubquery(expr.plan)
Review comment:
Instead of moving this check into each `case` statement, can we just check if `plan` is lateral or not here?
```
checkCorrelationsInSubquery(expr.plan, isLateral = plan.isInstanceOf[LateralJoin])
```
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853709790
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139269/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r621909386
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -1037,6 +1057,10 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
failOnInvalidOuterReference(j)
failOnOuterReferenceInSubTree(left)
+ // Lateral join is not supported inside a correlated subquery.
+ case LateralJoin(_) =>
+ failOnOuterReferenceInSubTree(j)
Review comment:
what's the error message? Do we clearly mention that lateral join inside correlated subquery is not allowed?
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647113231
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
##########
@@ -871,7 +871,13 @@ class AstBuilder extends SqlBaseBaseVisitor[AnyRef] with SQLConfHelper with Logg
override def visitFromClause(ctx: FromClauseContext): LogicalPlan = withOrigin(ctx) {
val from = ctx.relation.asScala.foldLeft(null: LogicalPlan) { (left, relation) =>
val right = plan(relation.relationPrimary)
- val join = right.optionalMap(left)(Join(_, _, Inner, None, JoinHint.NONE))
+ val join = right.optionalMap(left) { (left, right) =>
+ if (relation.LATERAL != null) {
+ LateralJoin(left, LateralSubquery(right), Inner, None)
Review comment:
Good point. I will add 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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857389118
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837177729
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/42863/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837164157
Kubernetes integration test starting
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/42863/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647650077
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
##########
@@ -871,7 +871,13 @@ class AstBuilder extends SqlBaseBaseVisitor[AnyRef] with SQLConfHelper with Logg
override def visitFromClause(ctx: FromClauseContext): LogicalPlan = withOrigin(ctx) {
val from = ctx.relation.asScala.foldLeft(null: LogicalPlan) { (left, relation) =>
val right = plan(relation.relationPrimary)
- val join = right.optionalMap(left)(Join(_, _, Inner, None, JoinHint.NONE))
+ val join = right.optionalMap(left) { (left, right) =>
+ if (relation.LATERAL != null) {
+ LateralJoin(left, LateralSubquery(right), Inner, None)
Review comment:
Sounds good.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827205065
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/42493/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-828988917
**[Test build #138059 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/138059/testReport)** for PR 32303 at commit [`2e87e18`](https://github.com/apache/spark/commit/2e87e18351a1eea358fcb98090deb3a11e839059).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-825416964
cc @cloud-fan
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837330924
**[Test build #138341 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/138341/testReport)** for PR 32303 at commit [`f726c83`](https://github.com/apache/spark/commit/f726c8375e70f3fb984276e713fca747fe765f45).
* This patch passes all tests.
* This patch **does not merge cleanly**.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r619415048
##########
File path: sql/core/src/test/resources/sql-tests/inputs/join-lateral.sql
##########
@@ -0,0 +1,84 @@
+-- Test cases for lateral join
+
+CREATE VIEW t1(c1, c2) AS VALUES (0, 1), (1, 2);
+CREATE VIEW t2(c1, c2) AS VALUES (0, 2), (0, 3);
+
+-- lateral join with single column select
+SELECT * FROM t1, LATERAL (SELECT c1);
Review comment:
> Is this compiled into a join query? We cannot compile it into a simple scan query like PostgreSQL?
>
> ```
> postgres=# explain SELECT * FROM t1, LATERAL (SELECT c1) t2;
> QUERY PLAN
> --------------------------------------------------------------
> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=12)
> (1 row)
> ```
Yes it should be rewritten as a simple scan query`SELECT c1 FROM t1` instead of a join query. I am thinking about adding an optimizer rule for OneRowRelation. This can be applied to correlated subqueries as well.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-845556718
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/138764/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837651747
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/42870/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-856523389
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/43975/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r637627094
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -1663,6 +1675,27 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
+ /**
+ * Optionally resolve the name parts using the outer query plan and wrap resolved attributes
+ * with [[OuterReference]]s.
+ */
+ private def resolveOuterReference(
Review comment:
nit: `tryResolveOuterReference`?
##########
File path: sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
##########
@@ -643,12 +643,12 @@ setQuantifier
;
relation
- : relationPrimary joinRelation*
+ : LATERAL? relationPrimary joinRelation*
;
joinRelation
- : (joinType) JOIN right=relationPrimary joinCriteria?
- | NATURAL joinType JOIN right=relationPrimary
+ : (joinType) JOIN LATERAL? right=relationPrimary joinCriteria?
+ | NATURAL joinType JOIN LATERAL? right=relationPrimary
Review comment:
Oh, super cleaner than the previous one. Nice.
##########
File path: sql/core/src/test/resources/sql-tests/inputs/join-lateral.sql
##########
@@ -0,0 +1,97 @@
+-- Test cases for lateral join
+
+CREATE VIEW t1(c1, c2) AS VALUES (0, 1), (1, 2);
+CREATE VIEW t2(c1, c2) AS VALUES (0, 2), (0, 3);
+
+-- lateral join with single column select
+SELECT * FROM t1, LATERAL (SELECT c1);
+SELECT * FROM t1, LATERAL (SELECT c1 FROM t2);
+SELECT * FROM t1, LATERAL (SELECT t1.c1 FROM t2);
+SELECT * FROM t1, LATERAL (SELECT t1.c1 + t2.c1 FROM t2);
+
+-- lateral join with star expansion
Review comment:
Could you add tests for `quotedRegexColumnNames`, too?
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/joins.scala
##########
@@ -168,6 +168,21 @@ object EliminateOuterJoin extends Rule[LogicalPlan] with PredicateHelper {
}
}
+/**
+ * Rewrite lateral joins by rewriting all dependent joins (if any) inside the right
+ * sub-tree of the lateral join and converting the lateral join into a base join type.
+ */
+object RewriteLateralJoin extends Rule[LogicalPlan] with PredicateHelper {
+
+ def apply(plan: LogicalPlan): LogicalPlan = plan transformUp {
+ case j @ Join(left, right, LateralJoin(joinType), condition, _) =>
+ val conditions = condition.map(splitConjunctivePredicates).getOrElse(Nil)
+ val newRight = DecorrelateInnerQuery.rewriteDomainJoins(left, right, conditions)
+ // TODO: handle the COUNT bug
Review comment:
Can you add the jira number (SPARK-15370) here?
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -1607,33 +1622,30 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
f1.copy(arguments = f1.arguments.flatMap {
- case s: Star => s.expand(child, resolver)
+ case s: Star => expand(s)
case o => o :: Nil
})
case c: CreateNamedStruct if containsStar(c.valExprs) =>
val newChildren = c.children.grouped(2).flatMap {
- case Seq(k, s : Star) => CreateStruct(s.expand(child, resolver)).children
+ case Seq(k, s : Star) => CreateStruct(expand(s)).children
case kv => kv
}
c.copy(children = newChildren.toList )
case c: CreateArray if containsStar(c.children) =>
c.copy(children = c.children.flatMap {
- case s: Star => s.expand(child, resolver)
+ case s: Star => expand(s)
case o => o :: Nil
})
case p: Murmur3Hash if containsStar(p.children) =>
p.copy(children = p.children.flatMap {
- case s: Star => s.expand(child, resolver)
+ case s: Star => expand(s)
case o => o :: Nil
})
case p: XxHash64 if containsStar(p.children) =>
p.copy(children = p.children.flatMap {
- case s: Star => s.expand(child, resolver)
+ case s: Star => expand(s)
case o => o :: Nil
})
- // count(*) has been replaced by count(1)
- case o if containsStar(o.children) =>
- throw QueryCompilationErrors.invalidStarUsageError(s"expression '${o.prettyName}'")
Review comment:
Did this error handling is moved into `CheckAnalysis` https://github.com/apache/spark/pull/32303/files#diff-583171e935b2dc349378063a5841c5b98b30a2d57ac3743a9eccfe7bffcb8f2aR177-R179 ?
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -276,6 +276,7 @@ class Analyzer(override val catalogManager: CatalogManager)
ResolveAliases ::
ResolveSubquery ::
ResolveSubqueryColumnAliases ::
+ ResolveLateralJoin ::
Review comment:
Since `ResolveLateralJoin` has a similar functionality with `ResolveReferences`, how about putting this rule just after `ResolveReferences`?
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -2395,6 +2421,70 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
+ /**
+ * This rule resolves lateral joins.
+ */
+ object ResolveLateralJoin extends Rule[LogicalPlan] {
Review comment:
`ResolveReferencesInLateralJoin`?
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -174,13 +174,21 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
hof.failAnalysis(
s"cannot resolve '${hof.sql}' due to argument data type mismatch: $message")
}
+ // Check if there are expressions with children containing star expressions.
+ case e if e.children.exists(_.isInstanceOf[Star]) =>
+ e.failAnalysis(s"Invalid usage of '*' in expression '${e.prettyName}'")
}
operator transformExpressionsUp {
case a: Attribute if !a.resolved =>
val from = operator.inputSet.toSeq.map(_.qualifiedName).mkString(", ")
a.failAnalysis(s"cannot resolve '${a.sql}' given input columns: [$from]")
+ case s @ UnresolvedStar(Some(target)) =>
Review comment:
We still need this error check even if we check it in L178?
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -174,13 +174,21 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
hof.failAnalysis(
s"cannot resolve '${hof.sql}' due to argument data type mismatch: $message")
}
+ // Check if there are expressions with children containing star expressions.
+ case e if e.children.exists(_.isInstanceOf[Star]) =>
+ e.failAnalysis(s"Invalid usage of '*' in expression '${e.prettyName}'")
Review comment:
We cannot use `QueryCompilationErrors.invalidStarUsageError` here?
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuery.scala
##########
@@ -244,7 +244,8 @@ object DecorrelateInnerQuery extends PredicateHelper {
case _ => Join(child, domain, Inner, None, JoinHint.NONE)
}
} else {
- throw QueryExecutionErrors.cannotRewriteDomainJoinWithConditionsError(conditions, d)
Review comment:
ditto: It seems no one uses this method now?
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -254,6 +262,27 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
s"join condition '${condition.sql}' " +
s"of type ${condition.dataType.catalogString} is not a boolean.")
+ case Join(left, right, LateralJoin(_), _, _) =>
+ // Check all outer references in the right operand are a subset of output columns
+ // of the left operand.
+ def checkOuterReferences(plan: LogicalPlan, outer: LogicalPlan): Unit = {
+ plan match {
+ case Join(left, _, LateralJoin(_), _, _) =>
+ checkOuterReferences(left, outer)
+ case p: LogicalPlan => p transformExpressions {
+ case o @ OuterReference(e) if !outer.outputSet.contains(e) =>
+ val column = outer.outputSet.toSeq.map(_.qualifiedName).mkString(", ")
+ o.failAnalysis(
+ s"Found an outer column reference '${e.sql}' in a lateral subquery " +
+ s"that is not present in the preceding FROM items of its own query " +
Review comment:
nit: remove `s`
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/unresolved.scala
##########
@@ -402,10 +402,8 @@ case class UnresolvedStar(target: Option[Seq[String]]) extends Star with Unevalu
throw QueryCompilationErrors.starExpandDataTypeNotSupportedError(target.get)
}
} else {
- val from = input.inputSet.map(_.name).mkString(", ")
- val targetString = target.get.mkString(".")
- throw QueryCompilationErrors.cannotResolveStarExpandGivenInputColumnsError(
Review comment:
It seems no one uses this method now?
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -174,13 +174,21 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
hof.failAnalysis(
s"cannot resolve '${hof.sql}' due to argument data type mismatch: $message")
}
+ // Check if there are expressions with children containing star expressions.
+ case e if e.children.exists(_.isInstanceOf[Star]) =>
+ e.failAnalysis(s"Invalid usage of '*' in expression '${e.prettyName}'")
Review comment:
If we add this rule here, I think we need to update the comment above.
https://github.com/apache/spark/pull/32303/files#diff-583171e935b2dc349378063a5841c5b98b30a2d57ac3743a9eccfe7bffcb8f2aL165-L168
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/joinTypes.scala
##########
@@ -107,6 +107,11 @@ case class UsingJoin(tpe: JoinType, usingColumns: Seq[String]) extends JoinType
override def sql: String = "USING " + tpe.sql
}
+case class LateralJoin(tpe: JoinType) extends JoinType {
+ require(Seq(Inner, LeftOuter, Cross).contains(tpe), "Unsupported lateral join type " + tpe)
Review comment:
> Currently INNER, CROSS and LEFT OUTER join types are supported with lateral join
If we can support the other join types in future, how about checking this requirement in the `AstBuilder` side?
##########
File path: sql/core/src/test/resources/sql-tests/inputs/postgreSQL/groupingsets.sql
##########
@@ -499,14 +499,14 @@ SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,(
-- COMMIT;
-- More rescan tests
--- [SPARK-27877] ANSI SQL: LATERAL derived table(T491)
+-- Expressions referencing the outer query are not supported outside of WHERE/HAVING clauses:
-- select * from (values (1),(2)) v(a) left join lateral (select v.a, four, ten, count(*) from onek group by cube(four,ten)) s on true order by v.a,four,ten;
-- [SPARK-27878] Support ARRAY(sub-SELECT) expressions
-- select array(select row(v.a,s1.*) from (select two,four, count(*) from onek group by cube(two,four) order by two,four) s1) from (values (1),(2)) v(a);
-- Rescan logic changes when there are no empty grouping sets, so test
-- that too:
--- [SPARK-27877] ANSI SQL: LATERAL derived table(T491)
+-- Expressions referencing the outer query are not supported outside of WHERE/HAVING clauses:
Review comment:
We can support this case in future? If we can, can you file jira for it and update the jira number above (`[SPARK-27877] ANSI SQL: LATERAL derived table(T491)`)?
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -2395,6 +2421,70 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
+ /**
+ * This rule resolves lateral joins.
+ */
+ object ResolveLateralJoin extends Rule[LogicalPlan] {
Review comment:
Q: Btw, is it difficult to merge this resolution logic with the `ResolveReferences` one?
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/DeduplicateRelations.scala
##########
@@ -182,6 +182,16 @@ object DeduplicateRelations extends Rule[LogicalPlan] {
if findAliases(projectList).intersect(conflictingAttributes).nonEmpty =>
Seq((oldVersion, oldVersion.copy(projectList = newAliases(projectList))))
+ // Handle projects that create conflicting outer references.
+ case oldVersion @ Project(projectList, _)
Review comment:
cc: @Ngone51
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857417819
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-829007855
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/138059/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857387520
**[Test build #139540 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139540/testReport)** for PR 32303 at commit [`eb46d13`](https://github.com/apache/spark/commit/eb46d13a2b5696bd0307c4727a69af5a06de92d9).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857650250
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139562/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853524175
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/43774/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857384983
Kubernetes integration test starting
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/44053/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r620567034
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -1638,6 +1660,27 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
+ /**
+ * Optionally resolve the name parts using the outer query plan and wrap resolved attributes
+ * with [[OuterReference]]s.
+ */
+ private def resolveOuterReference(
Review comment:
Pulling up correlated conditions can be tricky. In this case, it works because it's a simple equality condition, but for other cases such as having correlated non-equality conditions below an aggregate, decorrelation becomes more complex, and directly pulling up the filter conditions as join conditions may yield incorrect results (SPARK-17348 and SPARK-35080).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647032535
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuery.scala
##########
@@ -244,7 +244,17 @@ object DecorrelateInnerQuery extends PredicateHelper {
case _ => Join(child, domain, Inner, None, JoinHint.NONE)
}
} else {
- throw QueryExecutionErrors.cannotRewriteDomainJoinWithConditionsError(conditions, d)
+ // Leave it unchanged when the domain join does not belong to the current outer
+ // plan, i.e we cannot construct the domain attribute mapping from the join conditions.
+ // This can happen when the outer plan itself has another DomainJoin. E.g:
+ // LateralJoin lateral-subquery#263 [c1#262 && (c1#271 <=> c1#262)], Inner
+ // : +- Project [c1#271 AS c1#265, c1#271]
+ // : +- DomainJoin [c1#271]
+ // : +- OneRowRelation
+ // +- Project [(c1#270 + 1) AS c1#262, c1#270]
+ // +- DomainJoin [c1#270]
+ // +- OneRowRelation
+ d
Review comment:
It will be rewritten during the optimization for the outer lateral joins. You can think of each DomainJoin has a "scope" defined, i.e which outer plans it belongs to, and here we only rewrite a domain join if it belongs to the current outer plan.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853531752
**[Test build #139250 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139250/testReport)** for PR 32303 at commit [`72facb0`](https://github.com/apache/spark/commit/72facb0de716ed18b087017539e9dfb4dcd47d7a).
* This patch **fails Spark unit tests**.
* This patch merges cleanly.
* This patch adds the following public classes _(experimental)_:
* `case class LateralSubquery(`
* `case class LateralJoin(`
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-829007855
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/138059/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857260071
**[Test build #139512 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139512/testReport)** for PR 32303 at commit [`e045dbc`](https://github.com/apache/spark/commit/e045dbcad81c52647554046287b7c3a4c213d190).
* This patch **fails to build**.
* This patch merges cleanly.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857652499
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/44089/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r622435322
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -2234,6 +2260,76 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
+ /**
+ * This rule resolves lateral joins.
+ */
+ object ResolveLateralJoin extends Rule[LogicalPlan] {
+ import ResolveReferences._
+
+ /**
+ * Build a project list for Project/Aggregate and expand the star if possible by first using
+ * the inner query plan. If failed, use the outer query plan to expand the star and wrap all
+ * expanded attributes in [[OuterReference]]s.
+ */
+ private def expandOuterReference(
+ expressions: Seq[NamedExpression],
+ inner: LogicalPlan,
+ outer: LogicalPlan): Seq[NamedExpression] = {
+
+ // Expand the star expression using the inner plan.
+ def expandInner(star: Star): Seq[NamedExpression] =
+ star.expand(inner, resolver)
+
+ // Leave the star unchanged if the outer plan is unable to resolve the star.
+ // Otherwise wrap the resolved attributes in outer references.
+ def expandOuter(star: Star): Seq[NamedExpression] = {
+ star.expand(outer, resolver).map {
+ case s: Star => s
+ case other => other
+ .transform { case a: Attribute => OuterReference(a) }
+ .asInstanceOf[NamedExpression]
+ }
+ }
+
+ buildExpandedProjectList(expressions, inner, expandInner) match {
+ case expanded if !containsStar(expanded) => expanded
+ // Expand the remaining star expressions using the outer query plan.
+ case other => buildExpandedProjectList(other, outer, expandOuter)
+ }
+ }
+
+ /**
+ * Resolve the right sub-tree by first using the right query plan itself, and then try
+ * resolving the unresolved attributes and star expressions using the left query plan.
+ */
+ private def resolveRightChild(right: LogicalPlan, left: LogicalPlan): LogicalPlan = {
+ right.resolveOperators {
+ case p: LogicalPlan if !p.childrenResolved => p
+ case p: Project if containsStar(p.projectList) =>
+ p.copy(projectList = expandOuterReference(p.projectList, p.child, left))
+ case a: Aggregate if containsStar(a.aggregateExpressions) =>
+ a.copy(aggregateExpressions =
+ expandOuterReference(a.aggregateExpressions, a.child, left))
+ case p: LogicalPlan if !p.resolved =>
+ p transformExpressions {
+ case u @ UnresolvedAttribute(nameParts) =>
+ withPosition(u) {
+ p.resolveChildren(nameParts, resolver)
+ .orElse(resolveLiteralFunction(nameParts))
+ .orElse(resolveOuterReference(nameParts, left))
+ .getOrElse(u)
+ }
+ }
+ }
+ }
+
+ override def apply(plan: LogicalPlan): LogicalPlan = plan.resolveOperatorsUp {
+ case j @ Join(left, right, LateralJoin(_), _, _) if left.resolved && !right.resolved =>
Review comment:
Good point. Right now the decorrelation framework won't be able to support deep correlation for example
```sql
select * from t1, lateral (select * from t2, lateral (select t1.c1 + t2.c1));
```
But this can be resolved successfully in the Analyzer.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r619532923
##########
File path: sql/core/src/test/resources/sql-tests/inputs/join-lateral.sql
##########
@@ -0,0 +1,84 @@
+-- Test cases for lateral join
+
+CREATE VIEW t1(c1, c2) AS VALUES (0, 1), (1, 2);
+CREATE VIEW t2(c1, c2) AS VALUES (0, 2), (0, 3);
+
+-- lateral join with single column select
+SELECT * FROM t1, LATERAL (SELECT c1);
Review comment:
Postgres requires all subqueries in the FROM clause to have aliases, not just lateral, but aliases are not required for subqueries in Spark.
```
select * from (select * from t1);
subquery in FROM must have an alias
```
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-856619561
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139452/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-856617964
**[Test build #139452 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139452/testReport)** for PR 32303 at commit [`d646720`](https://github.com/apache/spark/commit/d646720edf977ea50ac8f273eea75b045915038e).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853545043
**[Test build #139269 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139269/testReport)** for PR 32303 at commit [`b83c571`](https://github.com/apache/spark/commit/b83c57108d6cfb839128e8e554d8f2f51906cf83).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857586028
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139546/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853498285
Kubernetes integration test starting
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/43774/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853606443
Kubernetes integration test status success
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/43792/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827054441
**[Test build #137967 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/137967/testReport)** for PR 32303 at commit [`07d764a`](https://github.com/apache/spark/commit/07d764a3593888bc9c7b6ad3ed03733c56212925).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857280116
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/44035/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-854249253
@maropu @cloud-fan @viirya
I updated the logical plan structure to handle a few cases I found during the test that do not work with the original plan representation. Originally, we directly use the logical Join node with a new join type `LateralJoin`. This structure can't handle the case when deduping relations, for instance:
```scala
'Join
:- LocalRelation [a, b]
+- Join LateralJoin(Inner)
:- LocalRelation [a, b] <--- after DeduplicateRelation: LocalRelation ['a, 'b]
+- Project [outer(a) + outer(b)] <--- but outer references won't be updated
+- OneRowRelation
```
`DeduplicateRelation` rule will return a new instance of the `LocalRelation[a, b]`, but the current `transformUpWithNewOutput` won't be able to update the existing outer references in the right subtree. Also, the resolution logic has a lot of overlaps with `ResolveSubquery`. The new logic unifies the analysis logic for lateral subqueries and existing subqueries. It adds a new unary node `LateralJoin` and a new subquery expression `LateralSubquery` to represent a lateral subquery. I've updated the PR description.
Also, I removed the logic to resolve star expressions in subqueries. It is not lateral subquery specific and I've created a new JIRA ticket: SPARK-35618.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827131355
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/137967/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857419054
**[Test build #139546 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139546/testReport)** for PR 32303 at commit [`1b6d8f3`](https://github.com/apache/spark/commit/1b6d8f31d2dcce6ebfdd5709b3d61ad5cef9a8b9).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827205065
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/42493/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-845397908
**[Test build #138764 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/138764/testReport)** for PR 32303 at commit [`fe2f941`](https://github.com/apache/spark/commit/fe2f9415fe020acd214557327cf17bd1174c1bf2).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-825187747
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/42354/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853482286
**[Test build #139250 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139250/testReport)** for PR 32303 at commit [`72facb0`](https://github.com/apache/spark/commit/72facb0de716ed18b087017539e9dfb4dcd47d7a).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-856619561
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139452/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857257148
**[Test build #139512 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139512/testReport)** for PR 32303 at commit [`e045dbc`](https://github.com/apache/spark/commit/e045dbcad81c52647554046287b7c3a4c213d190).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647092456
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -784,11 +791,10 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
failAnalysis(s"IN/EXISTS predicate sub-queries can only be used in" +
s" Filter/Join and a few commands: $plan")
}
+ // Validate to make sure the correlations appearing in the query are valid and
+ // allowed by spark.
+ checkCorrelationsInSubquery(expr.plan)
}
-
- // Validate to make sure the correlations appearing in the query are valid and
- // allowed by spark.
- checkCorrelationsInSubquery(expr.plan)
Review comment:
Instead of moving this check into each `case` statement, we just check if `plan` is lateral or not here?
```
checkCorrelationsInSubquery(expr.plan, isLateral = plan.isInstanceOf[LateralJoin])
```
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala
##########
@@ -315,19 +314,28 @@ object PullupCorrelatedPredicates extends Rule[LogicalPlan] with PredicateHelper
case ListQuery(sub, children, exprId, childOutputs, conditions) if children.nonEmpty =>
val (newPlan, newCond) = pullOutCorrelatedPredicates(sub, outerPlans)
ListQuery(newPlan, children, exprId, childOutputs, getJoinCondition(newCond, conditions))
+ case LateralSubquery(sub, children, exprId, conditions) if children.nonEmpty =>
+ val (newPlan, newCond) = decorrelate(sub, outerPlans)
+ LateralSubquery(newPlan, children, exprId, getJoinCondition(newCond, conditions))
}
}
/**
* Pull up the correlated predicates and rewrite all subqueries in an operator tree..
*/
def apply(plan: LogicalPlan): LogicalPlan = plan.transformUpWithPruning(
- _.containsAnyPattern(SCALAR_SUBQUERY, EXISTS_SUBQUERY, LIST_SUBQUERY)) {
+ _.containsPattern(PLAN_EXPRESSION)) {
case f @ Filter(_, a: Aggregate) =>
rewriteSubQueries(f, Seq(a, a.child))
- // Only a few unary nodes (Project/Filter/Aggregate) can contain subqueries.
+ // Only a few unary nodes (Project/Filter/Aggregate/LateralJoin) can contain subqueries.
case q: UnaryNode =>
- rewriteSubQueries(q, q.children)
+ val newPlan = rewriteSubQueries(q, q.children)
+ // Preserve the original output of the node.
+ if (newPlan.output != q.output) {
Review comment:
`q.sameOutput(newPlan)`?
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/subquery.scala
##########
@@ -285,6 +285,39 @@ object ScalarSubquery {
}
}
+/**
+ * A subquery that can return multiple rows and columns. This should be rewritten as a join
+ * with the outer query during the optimization phase.
+ *
+ * Note: `exprId` is used to have a unique name in explain string output.
+ */
+case class LateralSubquery(
+ plan: LogicalPlan,
+ outerAttrs: Seq[Expression] = Seq.empty,
+ exprId: ExprId = NamedExpression.newExprId,
+ joinCond: Seq[Expression] = Seq.empty)
+ extends SubqueryExpression(plan, outerAttrs, exprId, joinCond) with Unevaluable {
+ override def dataType: DataType = plan.output.toStructType
+ override def nullable: Boolean = true
+ override def withNewPlan(plan: LogicalPlan): LateralSubquery = copy(plan = plan)
+ override def toString: String = s"lateral-subquery#${exprId.id} $conditionString"
Review comment:
Could you add simple tests for checking if an explain can print this new node correctly?
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
##########
@@ -871,7 +871,13 @@ class AstBuilder extends SqlBaseBaseVisitor[AnyRef] with SQLConfHelper with Logg
override def visitFromClause(ctx: FromClauseContext): LogicalPlan = withOrigin(ctx) {
val from = ctx.relation.asScala.foldLeft(null: LogicalPlan) { (left, relation) =>
val right = plan(relation.relationPrimary)
- val join = right.optionalMap(left)(Join(_, _, Inner, None, JoinHint.NONE))
+ val join = right.optionalMap(left) { (left, right) =>
+ if (relation.LATERAL != null) {
+ LateralJoin(left, LateralSubquery(right), Inner, None)
Review comment:
We do not handle join hints for the lateral case?
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/PlanHelper.scala
##########
@@ -45,6 +45,7 @@ object PlanHelper {
case e: AggregateExpression
if !(plan.isInstanceOf[Aggregate] ||
plan.isInstanceOf[Window] ||
+ plan.isInstanceOf[LateralJoin] ||
Review comment:
How about more strictly checking that the right plan exprs of the lateral join are aggr ones?
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837651747
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/42870/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-845397908
**[Test build #138764 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/138764/testReport)** for PR 32303 at commit [`fe2f941`](https://github.com/apache/spark/commit/fe2f9415fe020acd214557327cf17bd1174c1bf2).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857419054
**[Test build #139546 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139546/testReport)** for PR 32303 at commit [`1b6d8f3`](https://github.com/apache/spark/commit/1b6d8f31d2dcce6ebfdd5709b3d61ad5cef9a8b9).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837081829
**[Test build #138341 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/138341/testReport)** for PR 32303 at commit [`f726c83`](https://github.com/apache/spark/commit/f726c8375e70f3fb984276e713fca747fe765f45).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853524175
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853482286
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647208461
##########
File path: sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/ResolveSubquerySuite.scala
##########
@@ -53,4 +58,117 @@ class ResolveSubquerySuite extends AnalysisTest {
JoinHint.NONE)
assertAnalysisSuccess(expr)
}
+
+ test("deduplicate lateral subquery") {
+ val plan = LateralJoin(t1, LateralSubquery(t0.select('a)), Inner, None)
+ // The subquery's output OuterReference(a#0) conflicts with the left child output
+ // attribute a#0. So an alias should be added to deduplicate the subquery's outputs.
+ val expected = LateralJoin(
+ t1,
+ LateralSubquery(Project(Seq(OuterReference(a).as(a.name)), t0), Seq(a)),
+ Inner,
+ None)
+ checkAnalysis(plan, expected)
+ }
+
+ test("lateral join with ambiguous join conditions") {
+ val plan = LateralJoin(t1, LateralSubquery(t0.select('b)), Inner, Some('b === 1))
+ assertAnalysisError(plan, "Reference 'b' is ambiguous, could be: b, b." :: Nil)
+ }
+
+ test("prefer resolving lateral subquery attributes from the inner query") {
+ val plan = LateralJoin(t1, LateralSubquery(t2.select('a, 'b, 'c)), Inner, None)
+ val expected = LateralJoin(
+ t1,
+ LateralSubquery(Project(Seq(OuterReference(a).as(a.name), b, c), t2), Seq(a)),
+ Inner, None)
+ checkAnalysis(plan, expected)
+ }
+
+ test("qualified column names in lateral subquery") {
+ val t1b = b.withQualifier(Seq("t1"))
+ val t2b = b.withQualifier(Seq("t2"))
+ checkAnalysis(
+ LateralJoin(t1.as("t1"), LateralSubquery(t0.select($"t1.b")), Inner, None),
+ LateralJoin(
+ t1,
+ LateralSubquery(Project(Seq(OuterReference(t1b).as(b.name)), t0), Seq(t1b)),
+ Inner, None)
+ )
+ checkAnalysis(
+ LateralJoin(
+ t1.as("t1"),
+ LateralSubquery(t2.as("t2").select($"t1.b", $"t2.b")),
+ Inner, None),
+ LateralJoin(
+ t1,
+ LateralSubquery(Project(Seq(OuterReference(t1b).as(b.name), t2b), t2.as("t2")), Seq(t1b)),
+ Inner, None)
+ )
+ }
+
+ test("resolve nested lateral subqueries") {
+ // SELECT * FROM t1, LATERAL (SELECT * FROM t2, LATERAL (SELECT b, c))
+ val plan = LateralJoin(t1,
+ LateralSubquery(
+ LateralJoin(t2, LateralSubquery(t0.select('b, 'c)), Inner, None)),
+ Inner, None)
+ val expected = LateralJoin(t1,
+ LateralSubquery(LateralJoin(t2,
+ LateralSubquery(
+ Project(Seq(OuterReference(b).as(b.name), OuterReference(c).as(c.name)), t0),
+ Seq(b, c)),
+ Inner, None)),
+ Inner, None)
+ checkAnalysis(plan, expected)
+ }
+
+ test("lateral subquery with unresolvable attributes") {
+ // SELECT * FROM t1, LATERAL (SELECT a, c)
+ assertAnalysisError(
+ LateralJoin(t1, LateralSubquery(t0.select('a, 'c)), Inner, None),
+ Seq("cannot resolve 'c' given input columns: []")
Review comment:
note: we can improve the error message in the future, to include columns from the outer plan as well.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827286546
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/137973/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857533584
Kubernetes integration test starting
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/44071/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647112288
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala
##########
@@ -315,19 +314,28 @@ object PullupCorrelatedPredicates extends Rule[LogicalPlan] with PredicateHelper
case ListQuery(sub, children, exprId, childOutputs, conditions) if children.nonEmpty =>
val (newPlan, newCond) = pullOutCorrelatedPredicates(sub, outerPlans)
ListQuery(newPlan, children, exprId, childOutputs, getJoinCondition(newCond, conditions))
+ case LateralSubquery(sub, children, exprId, conditions) if children.nonEmpty =>
+ val (newPlan, newCond) = decorrelate(sub, outerPlans)
+ LateralSubquery(newPlan, children, exprId, getJoinCondition(newCond, conditions))
}
}
/**
* Pull up the correlated predicates and rewrite all subqueries in an operator tree..
*/
def apply(plan: LogicalPlan): LogicalPlan = plan.transformUpWithPruning(
- _.containsAnyPattern(SCALAR_SUBQUERY, EXISTS_SUBQUERY, LIST_SUBQUERY)) {
+ _.containsPattern(PLAN_EXPRESSION)) {
case f @ Filter(_, a: Aggregate) =>
rewriteSubQueries(f, Seq(a, a.child))
- // Only a few unary nodes (Project/Filter/Aggregate) can contain subqueries.
+ // Only a few unary nodes (Project/Filter/Aggregate/LateralJoin) can contain subqueries.
case q: UnaryNode =>
- rewriteSubQueries(q, q.children)
+ val newPlan = rewriteSubQueries(q, q.children)
+ // Preserve the original output of the node.
+ if (newPlan.output != q.output) {
Review comment:
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647142502
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
##########
@@ -871,7 +871,13 @@ class AstBuilder extends SqlBaseBaseVisitor[AnyRef] with SQLConfHelper with Logg
override def visitFromClause(ctx: FromClauseContext): LogicalPlan = withOrigin(ctx) {
val from = ctx.relation.asScala.foldLeft(null: LogicalPlan) { (left, relation) =>
val right = plan(relation.relationPrimary)
- val join = right.optionalMap(left)(Join(_, _, Inner, None, JoinHint.NONE))
+ val join = right.optionalMap(left) { (left, right) =>
+ if (relation.LATERAL != null) {
+ LateralJoin(left, LateralSubquery(right), Inner, None)
Review comment:
Actually, does it make sense to have join hints for lateral joins? A lateral join is essentially a nested loop join. Ideally, the evaluation logic should be for each row in the left, plug the outer query attribute values into the outer references and evaluate the subquery. So it should only be planned as a (correlated) nested loop join. But since Spark doesn't support such execution, it first decorrelates the subquery and then rewrites the lateral join as a normal join. This seems to be an implementation detail and it doesn't make sense to add join hints here.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r619316658
##########
File path: sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
##########
@@ -588,7 +588,7 @@ hintStatement
;
fromClause
- : FROM relation (',' relation)* lateralView* pivotClause?
+ : FROM relation (',' lateralClause)* (',' relation)* lateralView* pivotClause?
Review comment:
Interesting. But what outer references we can use? It's the leftmost query...
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-828902507
**[Test build #138059 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/138059/testReport)** for PR 32303 at commit [`2e87e18`](https://github.com/apache/spark/commit/2e87e18351a1eea358fcb98090deb3a11e839059).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827286546
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/137973/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837177729
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/42863/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853543357
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139250/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857260098
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139512/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853625431
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/43792/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857862672
Kubernetes integration test status success
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/44106/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-846320278
Kubernetes integration test status success
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/43339/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857499054
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/44068/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] viirya commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
viirya commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r619571998
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/unresolved.scala
##########
@@ -392,16 +392,27 @@ case class UnresolvedStar(target: Option[Seq[String]]) extends Star with Unevalu
throw QueryCompilationErrors.starExpandDataTypeNotSupportedError(target.get)
}
} else {
- val from = input.inputSet.map(_.name).mkString(", ")
- val targetString = target.get.mkString(".")
- throw QueryCompilationErrors.cannotResolveStarExpandGivenInputColumnsError(
- targetString, from)
+ // Change the unresolved star to an expanded star to indicate the star has been
+ // expanded once.
+ ExpandedStar(this) :: Nil
}
}
override def toString: String = target.map(_ + ".").getOrElse("") + "*"
}
+/**
+ * Represent an [[UnresolvedStar]] that has been to expand once.
Review comment:
`has been to expand once` sounds uncertain definition to me. By its definition, cannot it be expanded more than once?
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-845440638
Kubernetes integration test unable to build dist.
exiting with code: 1
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/43286/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
maropu commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-826015290
```
scala> sql("SELECT * FROM (VALUES (1), (2)) tx(a), LATERAL (SELECT a FROM tx) ty(a)").show()
org.apache.spark.sql.AnalysisException: Table or view not found: tx; line 1 pos 62;
'Project [*]
+- 'Join LateralJoin(Inner)
:- SubqueryAlias tx
: +- Project [col1#345 AS a#346]
: +- LocalRelation [col1#345]
+- 'SubqueryAlias ty
+- 'UnresolvedSubqueryColumnAliases [a]
+- 'Project ['a]
+- 'UnresolvedRelation [tx], [], false
```
How bout the case above? The table resolution couldn't work well.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857741700
**[Test build #139577 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139577/testReport)** for PR 32303 at commit [`01fa70e`](https://github.com/apache/spark/commit/01fa70ec156ad5540b862c4b13dd1257062f31fb).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r646822129
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/subquery.scala
##########
@@ -285,6 +285,39 @@ object ScalarSubquery {
}
}
+/**
+ * A subquery that can return multiple rows and columns. This should be rewritten as a join
+ * with the outer query during the optimization phase.
+ *
+ * Note: `exprId` is used to have a unique name in explain string output.
+ */
+case class LateralSubquery(
+ plan: LogicalPlan,
+ outerAttrs: Seq[Expression] = Seq.empty,
Review comment:
I see, this is the outer references, the name follows `ScalarSubquery`
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] viirya commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
viirya commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r619571428
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -857,12 +865,21 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
}
}
+ // Check whether the logical plan node can host outer references.
+ // A `Project` can host outer references if it is inside a lateral join. Otherwise,
+ // only Filter can only outer references.
+ def canHostOuter(plan: LogicalPlan): Boolean = plan match {
+ case _: Filter => true
+ case _: Project => isLateral
+ case _ => false
+ }
+
// Make sure a plan's expressions do not contain :
// 1. Aggregate expressions that have mixture of outer and local references.
// 2. Expressions containing outer references on plan nodes other than Filter.
Review comment:
Also update this 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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857652469
Kubernetes integration test unable to build dist.
exiting with code: 1
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/44089/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r641255370
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/joinTypes.scala
##########
@@ -107,6 +107,11 @@ case class UsingJoin(tpe: JoinType, usingColumns: Seq[String]) extends JoinType
override def sql: String = "USING " + tpe.sql
}
+case class LateralJoin(tpe: JoinType) extends JoinType {
+ require(Seq(Inner, LeftOuter, Cross).contains(tpe), "Unsupported lateral join type " + tpe)
Review comment:
@maropu Postgres supports INNER, CROSS, and LEFT lateral join, and it doesn't make sense to support RIGHT OUTER and FULL OUTER lateral join. How about let's add the other two types of supported left joins: left semi and left anti here. Then lateral join types shouldn't be changed in the future.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-828917759
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/42578/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-825313305
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/137824/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837354594
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/138341/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r644404041
##########
File path: sql/core/src/test/resources/sql-tests/inputs/join-lateral.sql
##########
@@ -0,0 +1,97 @@
+-- Test cases for lateral join
+
+CREATE VIEW t1(c1, c2) AS VALUES (0, 1), (1, 2);
+CREATE VIEW t2(c1, c2) AS VALUES (0, 2), (0, 3);
+
+-- lateral join with single column select
+SELECT * FROM t1, LATERAL (SELECT c1);
+SELECT * FROM t1, LATERAL (SELECT c1 FROM t2);
+SELECT * FROM t1, LATERAL (SELECT t1.c1 FROM t2);
+SELECT * FROM t1, LATERAL (SELECT t1.c1 + t2.c1 FROM t2);
+
+-- lateral join with star expansion
Review comment:
All star expressions will be handled in a separate PR: SPARK-35618
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-846305088
**[Test build #138817 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/138817/testReport)** for PR 32303 at commit [`8e6cab3`](https://github.com/apache/spark/commit/8e6cab33e701a55d698a7239947ab85423010b7e).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-856505385
Kubernetes integration test status success
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/43975/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827054441
**[Test build #137967 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/137967/testReport)** for PR 32303 at commit [`07d764a`](https://github.com/apache/spark/commit/07d764a3593888bc9c7b6ad3ed03733c56212925).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857257148
**[Test build #139512 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139512/testReport)** for PR 32303 at commit [`e045dbc`](https://github.com/apache/spark/commit/e045dbcad81c52647554046287b7c3a4c213d190).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r646811744
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -2364,11 +2364,20 @@ class Analyzer(override val catalogManager: CatalogManager)
* Resolve and rewrite all subqueries in an operator tree..
*/
def apply(plan: LogicalPlan): LogicalPlan = plan.resolveOperatorsUpWithPruning(
- _.containsAnyPattern(SCALAR_SUBQUERY, EXISTS_SUBQUERY, IN_SUBQUERY), ruleId) {
+ _.containsPattern(PLAN_EXPRESSION), ruleId) {
// In case of HAVING (a filter after an aggregate) we use both the aggregate and
// its child for resolution.
case f @ Filter(_, a: Aggregate) if f.childrenResolved =>
resolveSubQueries(f, Seq(a, a.child))
+ case j @ LateralJoin(left, right, _, condition) if left.resolved && !right.resolved =>
+ val newRight = resolveSubQuery(right, j.children)(LateralSubquery(_, _, right.exprId))
+ .asInstanceOf[LateralSubquery]
+ val newCond = if (newRight.resolved && condition.exists(!_.resolved)) {
+ condition.map(resolveExpressionByPlanOutput(_, newRight.plan))
Review comment:
It's ok to have special column resolution logic. If you checkout the rule `ResolveReferences`, there are many special cases.
Let's put the special case for `LateralJoin` in `ResolveReferences` as well, to centralize all the column resolution logic. Let's also follow the general column resolution rule: fail if the name is ambiguous. For example, if join condition refers to a column in both left and right sides (un-qualified column name), we should fail.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-858031990
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139577/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-825181319
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647207140
##########
File path: sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/ResolveSubquerySuite.scala
##########
@@ -53,4 +58,117 @@ class ResolveSubquerySuite extends AnalysisTest {
JoinHint.NONE)
assertAnalysisSuccess(expr)
}
+
+ test("deduplicate lateral subquery") {
+ val plan = LateralJoin(t1, LateralSubquery(t0.select('a)), Inner, None)
+ // The subquery's output OuterReference(a#0) conflicts with the left child output
+ // attribute a#0. So an alias should be added to deduplicate the subquery's outputs.
+ val expected = LateralJoin(
+ t1,
+ LateralSubquery(Project(Seq(OuterReference(a).as(a.name)), t0), Seq(a)),
+ Inner,
+ None)
+ checkAnalysis(plan, expected)
+ }
+
+ test("lateral join with ambiguous join conditions") {
+ val plan = LateralJoin(t1, LateralSubquery(t0.select('b)), Inner, Some('b === 1))
+ assertAnalysisError(plan, "Reference 'b' is ambiguous, could be: b, b." :: Nil)
+ }
+
+ test("prefer resolving lateral subquery attributes from the inner query") {
+ val plan = LateralJoin(t1, LateralSubquery(t2.select('a, 'b, 'c)), Inner, None)
+ val expected = LateralJoin(
+ t1,
+ LateralSubquery(Project(Seq(OuterReference(a).as(a.name), b, c), t2), Seq(a)),
+ Inner, None)
+ checkAnalysis(plan, expected)
+ }
+
+ test("qualified column names in lateral subquery") {
+ val t1b = b.withQualifier(Seq("t1"))
+ val t2b = b.withQualifier(Seq("t2"))
+ checkAnalysis(
+ LateralJoin(t1.as("t1"), LateralSubquery(t0.select($"t1.b")), Inner, None),
+ LateralJoin(
+ t1,
+ LateralSubquery(Project(Seq(OuterReference(t1b).as(b.name)), t0), Seq(t1b)),
+ Inner, None)
+ )
+ checkAnalysis(
+ LateralJoin(
+ t1.as("t1"),
+ LateralSubquery(t2.as("t2").select($"t1.b", $"t2.b")),
+ Inner, None),
+ LateralJoin(
+ t1,
+ LateralSubquery(Project(Seq(OuterReference(t1b).as(b.name), t2b), t2.as("t2")), Seq(t1b)),
+ Inner, None)
+ )
+ }
+
+ test("resolve nested lateral subqueries") {
+ // SELECT * FROM t1, LATERAL (SELECT * FROM t2, LATERAL (SELECT b, c))
+ val plan = LateralJoin(t1,
+ LateralSubquery(
+ LateralJoin(t2, LateralSubquery(t0.select('b, 'c)), Inner, None)),
Review comment:
can we test a query here that refers to the outer plan instead of just t2? e.g. `t2.select('a, 'b, 'c)`
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857260098
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139512/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857279732
Kubernetes integration test unable to build dist.
exiting with code: 1
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/44035/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857455557
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/44064/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r646818840
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/subquery.scala
##########
@@ -285,6 +285,39 @@ object ScalarSubquery {
}
}
+/**
+ * A subquery that can return multiple rows and columns. This should be rewritten as a join
+ * with the outer query during the optimization phase.
+ *
+ * Note: `exprId` is used to have a unique name in explain string output.
+ */
+case class LateralSubquery(
+ plan: LogicalPlan,
+ outerAttrs: Seq[Expression] = Seq.empty,
Review comment:
can this be `Seq[Attribute]`?
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857389131
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857460918
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139528/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857560220
Kubernetes integration test status failure
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/44071/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-845440662
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/43286/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r620556049
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -2234,6 +2270,74 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
+ /**
+ * This rule resolves lateral joins.
+ */
+ object ResolveLateralJoin extends Rule[LogicalPlan] {
+ import ResolveReferences._
+
+ /**
+ * Build a project list for Project/Aggregate and expand the star if possible by first using
+ * the inner query plan. If failed, use the outer query plan to expand the star and wrap all
+ * expanded attributes in [[OuterReference]]s.
+ */
+ private def expandOuterReference(
+ expressions: Seq[NamedExpression],
+ inner: LogicalPlan,
+ outer: LogicalPlan): Seq[NamedExpression] = {
+
+ def expandInner(star: Star): Seq[NamedExpression] =
+ star.expand(inner, resolver)
+
+ // Leave the star unchanged if the outer plan is unable to resolve the star.
+ // Otherwise wrap the resolved attributes in outer references.
+ def expandOuter(star: Star): Seq[NamedExpression] = {
+ star.expand(outer, resolver).map {
+ case s: Star => s
+ case other => other
+ .transform { case a: Attribute => OuterReference(a) }
+ .asInstanceOf[NamedExpression]
+ }
+ }
+
+ buildExpandedProjectList(
+ buildExpandedProjectList(expressions, inner, expandInner),
+ outer,
+ expandOuter)
+ }
+
+ /**
+ * Resolve the right sub-tree by first using the right query plan itself, and then try
+ * resolving the unresolved attributes and star expressions using the left query plan.
+ */
+ private def resolveRightChild(right: LogicalPlan, left: LogicalPlan): LogicalPlan = {
+ right.resolveOperators {
+ case p: LogicalPlan if !p.childrenResolved => p
+ case p: Project if containsStar(p.projectList) =>
+ p.copy(projectList = expandOuterReference(p.projectList, p.child, left))
+ case a: Aggregate if containsStar(a.aggregateExpressions) =>
+ a.copy(aggregateExpressions =
+ expandOuterReference(a.aggregateExpressions, a.child, left))
+ case p: LogicalPlan if !p.resolved =>
+ p transformExpressions {
+ case u @ UnresolvedAttribute(nameParts) =>
+ withPosition(u) {
+ p.resolveChildren(nameParts, resolver)
+ .orElse(resolveLiteralFunction(nameParts))
+ .orElse(resolveOuterReference(nameParts, left))
Review comment:
Hmm I am getting this optimized plan from the query:
```
scala> sql("SELECT * FROM t1, LATERAL (SELECT c2 FROM t2 WHERE t1.c1 = t2.c1) tx(a);").explain(true)
...
== Optimized Logical Plan ==
Project [c1#0, c2#1, a#8]
+- Join Inner, (c1#0 = c1#4)
:- LocalRelation [c1#0, c2#1]
+- LocalRelation [a#8, c1#4]
```
The outer references inside the Filter should have been pulled up as join conditions.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu edited a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
maropu edited a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-825728810
It would be nice to add a doc page for the literal join in follow-up PRs.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-858031990
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/139577/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827083701
Kubernetes integration test status failure
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/42487/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827183412
**[Test build #137973 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/137973/testReport)** for PR 32303 at commit [`af0c3b0`](https://github.com/apache/spark/commit/af0c3b0bb4647ce50700151f6c04089c34f915d9).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-845440662
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/43286/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837870821
**[Test build #138348 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/138348/testReport)** for PR 32303 at commit [`62d8a97`](https://github.com/apache/spark/commit/62d8a9756239ba92129ce6a20d037aa00b0707f1).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857975276
**[Test build #139577 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139577/testReport)** for PR 32303 at commit [`01fa70e`](https://github.com/apache/spark/commit/01fa70ec156ad5540b862c4b13dd1257062f31fb).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r641253529
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/joins.scala
##########
@@ -168,6 +168,21 @@ object EliminateOuterJoin extends Rule[LogicalPlan] with PredicateHelper {
}
}
+/**
+ * Rewrite lateral joins by rewriting all dependent joins (if any) inside the right
+ * sub-tree of the lateral join and converting the lateral join into a base join type.
+ */
+object RewriteLateralJoin extends Rule[LogicalPlan] with PredicateHelper {
+
+ def apply(plan: LogicalPlan): LogicalPlan = plan transformUp {
+ case j @ Join(left, right, LateralJoin(joinType), condition, _) =>
+ val conditions = condition.map(splitConjunctivePredicates).getOrElse(Nil)
+ val newRight = DecorrelateInnerQuery.rewriteDomainJoins(left, right, conditions)
+ // TODO: handle the COUNT bug
Review comment:
Created a new ticket for handling the COUNT bug in lateral subqueries: https://issues.apache.org/jira/browse/SPARK-35551
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r620684327
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -2234,6 +2270,74 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
+ /**
+ * This rule resolves lateral joins.
+ */
+ object ResolveLateralJoin extends Rule[LogicalPlan] {
+ import ResolveReferences._
+
+ /**
+ * Build a project list for Project/Aggregate and expand the star if possible by first using
+ * the inner query plan. If failed, use the outer query plan to expand the star and wrap all
+ * expanded attributes in [[OuterReference]]s.
+ */
+ private def expandOuterReference(
+ expressions: Seq[NamedExpression],
+ inner: LogicalPlan,
+ outer: LogicalPlan): Seq[NamedExpression] = {
+
+ def expandInner(star: Star): Seq[NamedExpression] =
+ star.expand(inner, resolver)
+
+ // Leave the star unchanged if the outer plan is unable to resolve the star.
+ // Otherwise wrap the resolved attributes in outer references.
+ def expandOuter(star: Star): Seq[NamedExpression] = {
+ star.expand(outer, resolver).map {
+ case s: Star => s
+ case other => other
+ .transform { case a: Attribute => OuterReference(a) }
+ .asInstanceOf[NamedExpression]
+ }
+ }
+
+ buildExpandedProjectList(
+ buildExpandedProjectList(expressions, inner, expandInner),
+ outer,
+ expandOuter)
+ }
+
+ /**
+ * Resolve the right sub-tree by first using the right query plan itself, and then try
+ * resolving the unresolved attributes and star expressions using the left query plan.
+ */
+ private def resolveRightChild(right: LogicalPlan, left: LogicalPlan): LogicalPlan = {
+ right.resolveOperators {
+ case p: LogicalPlan if !p.childrenResolved => p
+ case p: Project if containsStar(p.projectList) =>
+ p.copy(projectList = expandOuterReference(p.projectList, p.child, left))
+ case a: Aggregate if containsStar(a.aggregateExpressions) =>
+ a.copy(aggregateExpressions =
+ expandOuterReference(a.aggregateExpressions, a.child, left))
+ case p: LogicalPlan if !p.resolved =>
+ p transformExpressions {
+ case u @ UnresolvedAttribute(nameParts) =>
+ withPosition(u) {
+ p.resolveChildren(nameParts, resolver)
+ .orElse(resolveLiteralFunction(nameParts))
+ .orElse(resolveOuterReference(nameParts, left))
Review comment:
I rebased the master and this is due to not adding a proper tree pattern. Should be fixed now :)
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r621906079
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -2234,6 +2260,76 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
+ /**
+ * This rule resolves lateral joins.
+ */
+ object ResolveLateralJoin extends Rule[LogicalPlan] {
+ import ResolveReferences._
+
+ /**
+ * Build a project list for Project/Aggregate and expand the star if possible by first using
+ * the inner query plan. If failed, use the outer query plan to expand the star and wrap all
+ * expanded attributes in [[OuterReference]]s.
+ */
+ private def expandOuterReference(
+ expressions: Seq[NamedExpression],
+ inner: LogicalPlan,
+ outer: LogicalPlan): Seq[NamedExpression] = {
+
+ // Expand the star expression using the inner plan.
+ def expandInner(star: Star): Seq[NamedExpression] =
+ star.expand(inner, resolver)
+
+ // Leave the star unchanged if the outer plan is unable to resolve the star.
+ // Otherwise wrap the resolved attributes in outer references.
+ def expandOuter(star: Star): Seq[NamedExpression] = {
+ star.expand(outer, resolver).map {
+ case s: Star => s
+ case other => other
+ .transform { case a: Attribute => OuterReference(a) }
+ .asInstanceOf[NamedExpression]
+ }
+ }
+
+ buildExpandedProjectList(expressions, inner, expandInner) match {
+ case expanded if !containsStar(expanded) => expanded
+ // Expand the remaining star expressions using the outer query plan.
+ case other => buildExpandedProjectList(other, outer, expandOuter)
+ }
+ }
+
+ /**
+ * Resolve the right sub-tree by first using the right query plan itself, and then try
+ * resolving the unresolved attributes and star expressions using the left query plan.
+ */
+ private def resolveRightChild(right: LogicalPlan, left: LogicalPlan): LogicalPlan = {
+ right.resolveOperators {
+ case p: LogicalPlan if !p.childrenResolved => p
+ case p: Project if containsStar(p.projectList) =>
+ p.copy(projectList = expandOuterReference(p.projectList, p.child, left))
+ case a: Aggregate if containsStar(a.aggregateExpressions) =>
+ a.copy(aggregateExpressions =
+ expandOuterReference(a.aggregateExpressions, a.child, left))
+ case p: LogicalPlan if !p.resolved =>
+ p transformExpressions {
+ case u @ UnresolvedAttribute(nameParts) =>
+ withPosition(u) {
+ p.resolveChildren(nameParts, resolver)
+ .orElse(resolveLiteralFunction(nameParts))
+ .orElse(resolveOuterReference(nameParts, left))
+ .getOrElse(u)
+ }
+ }
+ }
+ }
+
+ override def apply(plan: LogicalPlan): LogicalPlan = plan.resolveOperatorsUp {
+ case j @ Join(left, right, LateralJoin(_), _, _) if left.resolved && !right.resolved =>
Review comment:
shall we also make sure `right` doesn't contain unresolved lateral 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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r622372553
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -1037,6 +1057,10 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
failOnInvalidOuterReference(j)
failOnOuterReferenceInSubTree(left)
+ // Lateral join is not supported inside a correlated subquery.
+ case LateralJoin(_) =>
+ failOnOuterReferenceInSubTree(j)
Review comment:
The error message here is "Accessing outer query column is not allowed in LateralJoin...". I will make the error message more explicit.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837593393
**[Test build #138348 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/138348/testReport)** for PR 32303 at commit [`62d8a97`](https://github.com/apache/spark/commit/62d8a9756239ba92129ce6a20d037aa00b0707f1).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647027740
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala
##########
@@ -315,19 +314,28 @@ object PullupCorrelatedPredicates extends Rule[LogicalPlan] with PredicateHelper
case ListQuery(sub, children, exprId, childOutputs, conditions) if children.nonEmpty =>
val (newPlan, newCond) = pullOutCorrelatedPredicates(sub, outerPlans)
ListQuery(newPlan, children, exprId, childOutputs, getJoinCondition(newCond, conditions))
+ case LateralSubquery(sub, children, exprId, conditions) if children.nonEmpty =>
+ val (newPlan, newCond) = decorrelate(sub, outerPlans)
+ LateralSubquery(newPlan, children, exprId, getJoinCondition(newCond, conditions))
}
}
/**
* Pull up the correlated predicates and rewrite all subqueries in an operator tree..
*/
def apply(plan: LogicalPlan): LogicalPlan = plan.transformUpWithPruning(
- _.containsAnyPattern(SCALAR_SUBQUERY, EXISTS_SUBQUERY, LIST_SUBQUERY)) {
+ _.containsPattern(PLAN_EXPRESSION)) {
case f @ Filter(_, a: Aggregate) =>
rewriteSubQueries(f, Seq(a, a.child))
- // Only a few unary nodes (Project/Filter/Aggregate) can contain subqueries.
+ // Only a few unary nodes (Project/Filter/Aggregate/LateralJoin) can contain subqueries.
case q: UnaryNode =>
- rewriteSubQueries(q, q.children)
+ val newPlan = rewriteSubQueries(q, q.children)
+ // Preserve the original output of the node.
Review comment:
After decorrelation, the right child can have domain attributes preserved which can change its output, for example
// lateral subquery (before)
Project [a, b]
+- Filter [outer(c) = c]
+- LocalRelation [a, b, c]
// lateral subquery (after decorrelate)
Project [a, b, c]
+- LocalRelation [a, b, c]
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r637630300
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -2395,6 +2421,70 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
+ /**
+ * This rule resolves lateral joins.
+ */
+ object ResolveLateralJoin extends Rule[LogicalPlan] {
Review comment:
Q: Btw, is it difficult to merge this resolution logic into the `ResolveReferences` one?
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r644404041
##########
File path: sql/core/src/test/resources/sql-tests/inputs/join-lateral.sql
##########
@@ -0,0 +1,97 @@
+-- Test cases for lateral join
+
+CREATE VIEW t1(c1, c2) AS VALUES (0, 1), (1, 2);
+CREATE VIEW t2(c1, c2) AS VALUES (0, 2), (0, 3);
+
+-- lateral join with single column select
+SELECT * FROM t1, LATERAL (SELECT c1);
+SELECT * FROM t1, LATERAL (SELECT c1 FROM t2);
+SELECT * FROM t1, LATERAL (SELECT t1.c1 FROM t2);
+SELECT * FROM t1, LATERAL (SELECT t1.c1 + t2.c1 FROM t2);
+
+-- lateral join with star expansion
Review comment:
All star expressions will be handled in a separate PR: SPARK-35618
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857889167
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/44106/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-846340882
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/138817/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r619272576
##########
File path: sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
##########
@@ -631,6 +631,10 @@ lateralView
: LATERAL VIEW (OUTER)? qualifiedName '(' (expression (',' expression)*)? ')' tblName=identifier (AS? colName+=identifier (',' colName+=identifier)*)?
;
+lateralClause
+ : LATERAL right=relationPrimary
+ ;
Review comment:
It seems the parser of PostgreSQL handles this lateral clause in the table definition; https://github.com/postgres/postgres/blob/master/src/backend/parser/gram.y#L12091-L12117
So, could we write it like this with reference to it?
```
relation
: relationPrimary joinRelation*
: LATERAL right=relationPrimary ....
;
```
##########
File path: sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
##########
@@ -588,7 +588,7 @@ hintStatement
;
fromClause
- : FROM relation (',' relation)* lateralView* pivotClause?
+ : FROM relation (',' lateralClause)* (',' relation)* lateralView* pivotClause?
Review comment:
A leftmost lateral clause cannot be accepted? It seems PostgreSQL can;
```
postgres=# SELECT * FROM LATERAL (SELECT c1 from t2) tx, t1;
c1 | c1 | c2
----+----+----
0 | 0 | 1
0 | 1 | 2
0 | 0 | 1
0 | 1 | 2
(4 rows)
```
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/unresolved.scala
##########
@@ -392,16 +392,27 @@ case class UnresolvedStar(target: Option[Seq[String]]) extends Star with Unevalu
throw QueryCompilationErrors.starExpandDataTypeNotSupportedError(target.get)
}
} else {
- val from = input.inputSet.map(_.name).mkString(", ")
- val targetString = target.get.mkString(".")
- throw QueryCompilationErrors.cannotResolveStarExpandGivenInputColumnsError(
- targetString, from)
+ // Change the unresolved star to an expanded star to indicate the star has been
+ // expanded once.
+ ExpandedStar(this) :: Nil
}
}
override def toString: String = target.map(_ + ".").getOrElse("") + "*"
}
+/**
+ * Represent an [[UnresolvedStar]] that has been to expand once.
+ * This can be used to resolve a star expression using multiple plans.
+ */
+case class ExpandedStar(star: UnresolvedStar) extends Star with Unevaluable {
Review comment:
Why do we need this?
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -1638,6 +1660,27 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
+ /**
+ * Optionally resolve the name parts using the outer query plan and wrap resolved attributes
+ * with [[OuterReference]]s.
+ */
+ private def resolveOuterReference(
Review comment:
Just a question; instead of using the outer reference, we cannot simply pull up a filter condition into a join condition?
For example, in a query below, can an analyzer converts`'Join LateralJoin(Inner)` into `'Join LateralJoin(Inner), 't1.c1 = c1#171`, then resolves `'t1.c1` by `ResolveReferences`?
```
+- 'Join LateralJoin(Inner)
:- SubqueryAlias spark_catalog.default.t1
: +- View (`default`.`t1`, [c1#167,c2#168])
: +- Project [cast(col1#169 as int) AS c1#167, cast(col2#170 as int) AS c2#168]
: +- LocalRelation [col1#169, col2#170]
+- 'SubqueryAlias __auto_generated_subquery_name
+- 'Project ['c2]
+- 'Filter ('t1.c1 = c1#171)
+- SubqueryAlias spark_catalog.default.t2
+- View (`default`.`t2`, [c1#171,c2#172])
+- Project [cast(col1#173 as int) AS c1#171, cast(col2#174 as int) AS c2#172]
+- LocalRelation [col1#173, col2#174]
```
##########
File path: sql/core/src/test/resources/sql-tests/inputs/join-lateral.sql
##########
@@ -0,0 +1,84 @@
+-- Test cases for lateral join
Review comment:
Could you activate some tests in `postgreSQL/join.sql`?;
https://github.com/apache/spark/blob/e503b9c462676c381ba51ad48edd968d6e184fc2/sql/core/src/test/resources/sql-tests/inputs/postgreSQL/join.sql#L1278
##########
File path: sql/core/src/test/resources/sql-tests/inputs/join-lateral.sql
##########
@@ -0,0 +1,84 @@
+-- Test cases for lateral join
+
+CREATE VIEW t1(c1, c2) AS VALUES (0, 1), (1, 2);
+CREATE VIEW t2(c1, c2) AS VALUES (0, 2), (0, 3);
+
+-- lateral join with single column select
+SELECT * FROM t1, LATERAL (SELECT c1);
Review comment:
Is this compiled into a join query? We cannot compile it into a simple scan query like PostgreSQL?
```
postgres=# explain SELECT * FROM t1, LATERAL (SELECT c1) t2;
QUERY PLAN
--------------------------------------------------------------
Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=12)
(1 row)
```
##########
File path: sql/core/src/test/resources/sql-tests/inputs/join-lateral.sql
##########
@@ -0,0 +1,84 @@
+-- Test cases for lateral join
+
+CREATE VIEW t1(c1, c2) AS VALUES (0, 1), (1, 2);
+CREATE VIEW t2(c1, c2) AS VALUES (0, 2), (0, 3);
+
+-- lateral join with single column select
+SELECT * FROM t1, LATERAL (SELECT c1);
Review comment:
Btw, in PostgreSQL, it seems a table alias must be needed for this literal query;
```
postgres=# SELECT * FROM t1, LATERAL (SELECT c1);
2021-04-23 23:43:11.763 JST [43125] ERROR: subquery in FROM must have an alias at character 27
2021-04-23 23:43:11.763 JST [43125] HINT: For example, FROM (SELECT ...) [AS] foo.
2021-04-23 23:43:11.763 JST [43125] STATEMENT: SELECT * FROM t1, LATERAL (SELECT c1);
ERROR: subquery in FROM must have an alias
LINE 1: SELECT * FROM t1, LATERAL (SELECT c1);
^
HINT: For example, FROM (SELECT ...) [AS] foo.
```
Could you check the ANSI/SQL definition or the other database behaviours?
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857646477
**[Test build #139562 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139562/testReport)** for PR 32303 at commit [`1b6d8f3`](https://github.com/apache/spark/commit/1b6d8f31d2dcce6ebfdd5709b3d61ad5cef9a8b9).
* This patch **fails Spark unit tests**.
* This patch merges cleanly.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-846340285
**[Test build #138817 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/138817/testReport)** for PR 32303 at commit [`8e6cab3`](https://github.com/apache/spark/commit/8e6cab33e701a55d698a7239947ab85423010b7e).
* This patch **fails Spark unit tests**.
* This patch merges cleanly.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] viirya commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
viirya commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r619571772
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/DeduplicateRelations.scala
##########
@@ -156,6 +156,16 @@ object DeduplicateRelations extends Rule[LogicalPlan] {
if findAliases(projectList).intersect(conflictingAttributes).nonEmpty =>
Seq((oldVersion, oldVersion.copy(projectList = newAliases(projectList))))
+ // Handle projects that create conflicting outer references.
+ case oldVersion @ Project(projectList, _)
+ if findOuterReferences(projectList).intersect(conflictingAttributes).nonEmpty =>
+ // Add alias to conflicting outer references.
+ val aliasedProjectList = projectList.map {
+ case o @ OuterReference(a) if conflictingAttributes.contains(a) => Alias(o, a.name)()
+ case other => other
+ }
Review comment:
Could outer reference be inside an expression tree?
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r619562857
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -2234,6 +2270,74 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
+ /**
+ * This rule resolves lateral joins.
+ */
+ object ResolveLateralJoin extends Rule[LogicalPlan] {
+ import ResolveReferences._
+
+ /**
+ * Build a project list for Project/Aggregate and expand the star if possible by first using
+ * the inner query plan. If failed, use the outer query plan to expand the star and wrap all
+ * expanded attributes in [[OuterReference]]s.
+ */
+ private def expandOuterReference(
+ expressions: Seq[NamedExpression],
+ inner: LogicalPlan,
+ outer: LogicalPlan): Seq[NamedExpression] = {
+
+ def expandInner(star: Star): Seq[NamedExpression] =
+ star.expand(inner, resolver)
+
+ // Leave the star unchanged if the outer plan is unable to resolve the star.
+ // Otherwise wrap the resolved attributes in outer references.
+ def expandOuter(star: Star): Seq[NamedExpression] = {
+ star.expand(outer, resolver).map {
+ case s: Star => s
+ case other => other
+ .transform { case a: Attribute => OuterReference(a) }
+ .asInstanceOf[NamedExpression]
+ }
+ }
+
+ buildExpandedProjectList(
+ buildExpandedProjectList(expressions, inner, expandInner),
+ outer,
+ expandOuter)
+ }
+
+ /**
+ * Resolve the right sub-tree by first using the right query plan itself, and then try
+ * resolving the unresolved attributes and star expressions using the left query plan.
+ */
+ private def resolveRightChild(right: LogicalPlan, left: LogicalPlan): LogicalPlan = {
+ right.resolveOperators {
+ case p: LogicalPlan if !p.childrenResolved => p
+ case p: Project if containsStar(p.projectList) =>
+ p.copy(projectList = expandOuterReference(p.projectList, p.child, left))
+ case a: Aggregate if containsStar(a.aggregateExpressions) =>
+ a.copy(aggregateExpressions =
+ expandOuterReference(a.aggregateExpressions, a.child, left))
+ case p: LogicalPlan if !p.resolved =>
+ p transformExpressions {
+ case u @ UnresolvedAttribute(nameParts) =>
+ withPosition(u) {
+ p.resolveChildren(nameParts, resolver)
+ .orElse(resolveLiteralFunction(nameParts))
+ .orElse(resolveOuterReference(nameParts, left))
Review comment:
It looks you forget to remove off the outer reference from the an optimized plan and the ref is unevaluable.
```
scala> sql("SELECT * FROM t1, LATERAL (SELECT c2 FROM t2 WHERE t1.c1 = t2.c1) tx(a);").explain(true)
...
== Optimized Logical Plan ==
Join Inner
:- LocalRelation [c1#323, c2#324]
+- Project [c2#328 AS a#331]
+- Filter (outer(c1#323) = c1#327) <---- !!!HERE!!!
+- LocalRelation [c1#327, c2#328]
```
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
maropu commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-825728810
It would be nice to add a page for the literal join in follow-up PRs.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837354594
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/138341/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r620541594
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -2234,6 +2270,74 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
+ /**
+ * This rule resolves lateral joins.
+ */
+ object ResolveLateralJoin extends Rule[LogicalPlan] {
+ import ResolveReferences._
+
+ /**
+ * Build a project list for Project/Aggregate and expand the star if possible by first using
+ * the inner query plan. If failed, use the outer query plan to expand the star and wrap all
+ * expanded attributes in [[OuterReference]]s.
+ */
+ private def expandOuterReference(
+ expressions: Seq[NamedExpression],
+ inner: LogicalPlan,
+ outer: LogicalPlan): Seq[NamedExpression] = {
+
+ def expandInner(star: Star): Seq[NamedExpression] =
+ star.expand(inner, resolver)
+
+ // Leave the star unchanged if the outer plan is unable to resolve the star.
+ // Otherwise wrap the resolved attributes in outer references.
+ def expandOuter(star: Star): Seq[NamedExpression] = {
+ star.expand(outer, resolver).map {
+ case s: Star => s
+ case other => other
+ .transform { case a: Attribute => OuterReference(a) }
+ .asInstanceOf[NamedExpression]
+ }
+ }
+
+ buildExpandedProjectList(
+ buildExpandedProjectList(expressions, inner, expandInner),
Review comment:
Good point. If there is no star in the expanded list using the inner plan, then we don't need to expand it again using the outer plan.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837901718
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/138348/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857366765
**[Test build #139535 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139535/testReport)** for PR 32303 at commit [`f00f210`](https://github.com/apache/spark/commit/f00f210cafbaae94d0fdf81d44d678172a75b1d1).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857481346
retest this please
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857343892
**[Test build #139528 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139528/testReport)** for PR 32303 at commit [`8f19ab7`](https://github.com/apache/spark/commit/8f19ab732fa0117a72626c601e964140c9b4bd42).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r646814848
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -2364,11 +2364,20 @@ class Analyzer(override val catalogManager: CatalogManager)
* Resolve and rewrite all subqueries in an operator tree..
*/
def apply(plan: LogicalPlan): LogicalPlan = plan.resolveOperatorsUpWithPruning(
- _.containsAnyPattern(SCALAR_SUBQUERY, EXISTS_SUBQUERY, IN_SUBQUERY), ruleId) {
+ _.containsPattern(PLAN_EXPRESSION), ruleId) {
// In case of HAVING (a filter after an aggregate) we use both the aggregate and
// its child for resolution.
case f @ Filter(_, a: Aggregate) if f.childrenResolved =>
resolveSubQueries(f, Seq(a, a.child))
+ case j @ LateralJoin(left, right, _, condition) if left.resolved && !right.resolved =>
+ val newRight = resolveSubQuery(right, j.children)(LateralSubquery(_, _, right.exprId))
+ .asInstanceOf[LateralSubquery]
+ val newCond = if (newRight.resolved && condition.exists(!_.resolved)) {
+ condition.map(resolveExpressionByPlanOutput(_, newRight.plan))
Review comment:
How about we overwrite `resolveChildren` in `LateralJoin`, so that we consider input columns from both left and right tables? Then we don't need the special logic here, and we also follow normal join behavior and fail analysis if the name is ambiguous (`t1 JOIN LATERAL t2` ON a = 1 while both t1 and t2 have column a).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r639384170
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -174,13 +174,21 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
hof.failAnalysis(
s"cannot resolve '${hof.sql}' due to argument data type mismatch: $message")
}
+ // Check if there are expressions with children containing star expressions.
+ case e if e.children.exists(_.isInstanceOf[Star]) =>
+ e.failAnalysis(s"Invalid usage of '*' in expression '${e.prettyName}'")
Review comment:
Good point. The error message is slightly different from `invalidStarUsageError`(with an extra word 'expression') but it should be fine to unify them.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827278662
**[Test build #137973 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/137973/testReport)** for PR 32303 at commit [`af0c3b0`](https://github.com/apache/spark/commit/af0c3b0bb4647ce50700151f6c04089c34f915d9).
* This patch **fails PySpark unit tests**.
* This patch merges cleanly.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-856483304
Kubernetes integration test starting
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/43975/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827080952
Kubernetes integration test starting
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/42487/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647189370
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuery.scala
##########
@@ -217,36 +217,36 @@ object DecorrelateInnerQuery extends PredicateHelper {
def rewriteDomainJoins(
outerPlan: LogicalPlan,
innerPlan: LogicalPlan,
- conditions: Seq[Expression]): LogicalPlan = {
- innerPlan transform {
- case d @ DomainJoin(domainAttrs, child) =>
- val domainAttrMap = buildDomainAttrMap(conditions, domainAttrs)
- // We should only rewrite a domain join when all corresponding outer plan attributes
- // can be found from the join condition.
- if (domainAttrMap.size == domainAttrs.size) {
- val groupingExprs = domainAttrs.map(domainAttrMap)
- val aggregateExprs = groupingExprs.zip(domainAttrs).map {
- // Rebuild the aliases.
- case (inputAttr, outputAttr) => Alias(inputAttr, outputAttr.name)(outputAttr.exprId)
- }
- // Construct a domain with the outer query plan.
- // DomainJoin [a', b'] => Aggregate [a, b] [a AS a', b AS b']
- // +- Relation [a, b]
- val domain = Aggregate(groupingExprs, aggregateExprs, outerPlan)
- child match {
- // A special optimization for OneRowRelation.
- // TODO: add a more general rule to optimize join with OneRowRelation.
- case _: OneRowRelation => domain
- // Construct a domain join.
- // Join Inner
- // :- Inner Query
- // +- Domain
- case _ => Join(child, domain, Inner, None, JoinHint.NONE)
- }
- } else {
- throw QueryExecutionErrors.cannotRewriteDomainJoinWithConditionsError(conditions, d)
+ conditions: Seq[Expression]): LogicalPlan = innerPlan match {
+ case d @ DomainJoin(domainAttrs, child) =>
+ val domainAttrMap = buildDomainAttrMap(conditions, domainAttrs)
+ // We should only rewrite a domain join when all corresponding outer plan attributes
+ // can be found from the join condition.
+ if (domainAttrMap.size == domainAttrs.size) {
+ val groupingExprs = domainAttrs.map(domainAttrMap)
+ val aggregateExprs = groupingExprs.zip(domainAttrs).map {
+ // Rebuild the aliases.
+ case (inputAttr, outputAttr) => Alias(inputAttr, outputAttr.name)(outputAttr.exprId)
}
- }
+ // Construct a domain with the outer query plan.
+ // DomainJoin [a', b'] => Aggregate [a, b] [a AS a', b AS b']
+ // +- Relation [a, b]
+ val domain = Aggregate(groupingExprs, aggregateExprs, outerPlan)
+ child match {
+ // A special optimization for OneRowRelation.
+ // TODO: add a more general rule to optimize join with OneRowRelation.
+ case _: OneRowRelation => domain
+ // Construct a domain join.
+ // Join Inner
+ // :- Inner Query
+ // +- Domain
+ case _ => Join(child, domain, Inner, None, JoinHint.NONE)
Review comment:
shall we add an assert that: `child` has no more `DomainJoin`?
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r646821026
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/PlanHelper.scala
##########
@@ -45,6 +45,7 @@ object PlanHelper {
case e: AggregateExpression
if !(plan.isInstanceOf[Aggregate] ||
plan.isInstanceOf[Window] ||
+ plan.isInstanceOf[LateralJoin] ||
Review comment:
`LateralJoin` can host aggregate expressions?
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r646818552
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuery.scala
##########
@@ -244,7 +244,17 @@ object DecorrelateInnerQuery extends PredicateHelper {
case _ => Join(child, domain, Inner, None, JoinHint.NONE)
}
} else {
- throw QueryExecutionErrors.cannotRewriteDomainJoinWithConditionsError(conditions, d)
+ // Leave it unchanged when the domain join does not belong to the current outer
+ // plan, i.e we cannot construct the domain attribute mapping from the join conditions.
+ // This can happen when the outer plan itself has another DomainJoin. E.g:
+ // LateralJoin lateral-subquery#263 [c1#262 && (c1#271 <=> c1#262)], Inner
+ // : +- Project [c1#271 AS c1#265, c1#271]
+ // : +- DomainJoin [c1#271]
+ // : +- OneRowRelation
+ // +- Project [(c1#270 + 1) AS c1#262, c1#270]
+ // +- DomainJoin [c1#270]
+ // +- OneRowRelation
+ d
Review comment:
what will we do later to resolve 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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857394986
**[Test build #139543 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139543/testReport)** for PR 32303 at commit [`08385f4`](https://github.com/apache/spark/commit/08385f4a0cbba256ae93131100aee16db2feef64).
* This patch **fails Scala style tests**.
* This patch merges cleanly.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827131062
**[Test build #137967 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/137967/testReport)** for PR 32303 at commit [`07d764a`](https://github.com/apache/spark/commit/07d764a3593888bc9c7b6ad3ed03733c56212925).
* This patch **fails Spark unit tests**.
* This patch merges cleanly.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853588123
Kubernetes integration test starting
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/43792/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857889167
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/44106/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853482286
**[Test build #139250 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139250/testReport)** for PR 32303 at commit [`72facb0`](https://github.com/apache/spark/commit/72facb0de716ed18b087017539e9dfb4dcd47d7a).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857652499
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/44089/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-856451925
**[Test build #139452 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139452/testReport)** for PR 32303 at commit [`d646720`](https://github.com/apache/spark/commit/d646720edf977ea50ac8f273eea75b045915038e).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-837171174
Kubernetes integration test status failure
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/42863/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] viirya commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
viirya commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r619571252
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -2234,6 +2270,74 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
+ /**
+ * This rule resolves lateral joins.
+ */
+ object ResolveLateralJoin extends Rule[LogicalPlan] {
+ import ResolveReferences._
+
+ /**
+ * Build a project list for Project/Aggregate and expand the star if possible by first using
+ * the inner query plan. If failed, use the outer query plan to expand the star and wrap all
+ * expanded attributes in [[OuterReference]]s.
+ */
+ private def expandOuterReference(
+ expressions: Seq[NamedExpression],
+ inner: LogicalPlan,
+ outer: LogicalPlan): Seq[NamedExpression] = {
+
+ def expandInner(star: Star): Seq[NamedExpression] =
+ star.expand(inner, resolver)
+
+ // Leave the star unchanged if the outer plan is unable to resolve the star.
+ // Otherwise wrap the resolved attributes in outer references.
+ def expandOuter(star: Star): Seq[NamedExpression] = {
+ star.expand(outer, resolver).map {
+ case s: Star => s
+ case other => other
+ .transform { case a: Attribute => OuterReference(a) }
+ .asInstanceOf[NamedExpression]
+ }
+ }
+
+ buildExpandedProjectList(
+ buildExpandedProjectList(expressions, inner, expandInner),
Review comment:
If inner `buildExpandedProjectList` was fully resolved, do we still need run outer `buildExpandedProjectList`?
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r637626356
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
##########
@@ -1607,33 +1622,30 @@ class Analyzer(override val catalogManager: CatalogManager)
}
}
f1.copy(arguments = f1.arguments.flatMap {
- case s: Star => s.expand(child, resolver)
+ case s: Star => expand(s)
case o => o :: Nil
})
case c: CreateNamedStruct if containsStar(c.valExprs) =>
val newChildren = c.children.grouped(2).flatMap {
- case Seq(k, s : Star) => CreateStruct(s.expand(child, resolver)).children
+ case Seq(k, s : Star) => CreateStruct(expand(s)).children
case kv => kv
}
c.copy(children = newChildren.toList )
case c: CreateArray if containsStar(c.children) =>
c.copy(children = c.children.flatMap {
- case s: Star => s.expand(child, resolver)
+ case s: Star => expand(s)
case o => o :: Nil
})
case p: Murmur3Hash if containsStar(p.children) =>
p.copy(children = p.children.flatMap {
- case s: Star => s.expand(child, resolver)
+ case s: Star => expand(s)
case o => o :: Nil
})
case p: XxHash64 if containsStar(p.children) =>
p.copy(children = p.children.flatMap {
- case s: Star => s.expand(child, resolver)
+ case s: Star => expand(s)
case o => o :: Nil
})
- // count(*) has been replaced by count(1)
- case o if containsStar(o.children) =>
- throw QueryCompilationErrors.invalidStarUsageError(s"expression '${o.prettyName}'")
Review comment:
Was this error handling moved into `CheckAnalysis` https://github.com/apache/spark/pull/32303/files#diff-583171e935b2dc349378063a5841c5b98b30a2d57ac3743a9eccfe7bffcb8f2aR177-R179 ?
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-856451925
**[Test build #139452 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139452/testReport)** for PR 32303 at commit [`d646720`](https://github.com/apache/spark/commit/d646720edf977ea50ac8f273eea75b045915038e).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-845549032
**[Test build #138764 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/138764/testReport)** for PR 32303 at commit [`fe2f941`](https://github.com/apache/spark/commit/fe2f9415fe020acd214557327cf17bd1174c1bf2).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-856523389
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/43975/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-825154175
**[Test build #137824 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/137824/testReport)** for PR 32303 at commit [`8f6ea0a`](https://github.com/apache/spark/commit/8f6ea0a385beda190f93b7edff587d4d4e2aef18).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853545043
**[Test build #139269 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139269/testReport)** for PR 32303 at commit [`b83c571`](https://github.com/apache/spark/commit/b83c57108d6cfb839128e8e554d8f2f51906cf83).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-828902507
**[Test build #138059 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/138059/testReport)** for PR 32303 at commit [`2e87e18`](https://github.com/apache/spark/commit/2e87e18351a1eea358fcb98090deb3a11e839059).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827086777
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/42487/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r646819941
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala
##########
@@ -315,19 +314,28 @@ object PullupCorrelatedPredicates extends Rule[LogicalPlan] with PredicateHelper
case ListQuery(sub, children, exprId, childOutputs, conditions) if children.nonEmpty =>
val (newPlan, newCond) = pullOutCorrelatedPredicates(sub, outerPlans)
ListQuery(newPlan, children, exprId, childOutputs, getJoinCondition(newCond, conditions))
+ case LateralSubquery(sub, children, exprId, conditions) if children.nonEmpty =>
+ val (newPlan, newCond) = decorrelate(sub, outerPlans)
+ LateralSubquery(newPlan, children, exprId, getJoinCondition(newCond, conditions))
}
}
/**
* Pull up the correlated predicates and rewrite all subqueries in an operator tree..
*/
def apply(plan: LogicalPlan): LogicalPlan = plan.transformUpWithPruning(
- _.containsAnyPattern(SCALAR_SUBQUERY, EXISTS_SUBQUERY, LIST_SUBQUERY)) {
+ _.containsPattern(PLAN_EXPRESSION)) {
case f @ Filter(_, a: Aggregate) =>
rewriteSubQueries(f, Seq(a, a.child))
- // Only a few unary nodes (Project/Filter/Aggregate) can contain subqueries.
+ // Only a few unary nodes (Project/Filter/Aggregate/LateralJoin) can contain subqueries.
case q: UnaryNode =>
- rewriteSubQueries(q, q.children)
+ val newPlan = rewriteSubQueries(q, q.children)
+ // Preserve the original output of the node.
Review comment:
when can this happen?
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-845556718
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/138764/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857499023
Kubernetes integration test unable to build dist.
exiting with code: 1
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/44068/
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853482286
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] allisonwang-db commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r620547937
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/unresolved.scala
##########
@@ -392,16 +392,27 @@ case class UnresolvedStar(target: Option[Seq[String]]) extends Star with Unevalu
throw QueryCompilationErrors.starExpandDataTypeNotSupportedError(target.get)
}
} else {
- val from = input.inputSet.map(_.name).mkString(", ")
- val targetString = target.get.mkString(".")
- throw QueryCompilationErrors.cannotResolveStarExpandGivenInputColumnsError(
- targetString, from)
+ // Change the unresolved star to an expanded star to indicate the star has been
+ // expanded once.
+ ExpandedStar(this) :: Nil
}
}
override def toString: String = target.map(_ + ".").getOrElse("") + "*"
}
+/**
+ * Represent an [[UnresolvedStar]] that has been to expand once.
Review comment:
This `ExpandedStar` is indeed confusing. It was used to detect invalid usage of star expressions. I will update the logic here.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-827183412
**[Test build #137973 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/137973/testReport)** for PR 32303 at commit [`af0c3b0`](https://github.com/apache/spark/commit/af0c3b0bb4647ce50700151f6c04089c34f915d9).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-853524175
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857741700
**[Test build #139577 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139577/testReport)** for PR 32303 at commit [`01fa70e`](https://github.com/apache/spark/commit/01fa70ec156ad5540b862c4b13dd1257062f31fb).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r647209229
##########
File path: sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/ResolveSubquerySuite.scala
##########
@@ -53,4 +58,117 @@ class ResolveSubquerySuite extends AnalysisTest {
JoinHint.NONE)
assertAnalysisSuccess(expr)
}
+
+ test("deduplicate lateral subquery") {
+ val plan = LateralJoin(t1, LateralSubquery(t0.select('a)), Inner, None)
+ // The subquery's output OuterReference(a#0) conflicts with the left child output
+ // attribute a#0. So an alias should be added to deduplicate the subquery's outputs.
+ val expected = LateralJoin(
+ t1,
+ LateralSubquery(Project(Seq(OuterReference(a).as(a.name)), t0), Seq(a)),
+ Inner,
+ None)
+ checkAnalysis(plan, expected)
+ }
+
+ test("lateral join with ambiguous join conditions") {
+ val plan = LateralJoin(t1, LateralSubquery(t0.select('b)), Inner, Some('b === 1))
+ assertAnalysisError(plan, "Reference 'b' is ambiguous, could be: b, b." :: Nil)
+ }
+
+ test("prefer resolving lateral subquery attributes from the inner query") {
+ val plan = LateralJoin(t1, LateralSubquery(t2.select('a, 'b, 'c)), Inner, None)
+ val expected = LateralJoin(
+ t1,
+ LateralSubquery(Project(Seq(OuterReference(a).as(a.name), b, c), t2), Seq(a)),
+ Inner, None)
+ checkAnalysis(plan, expected)
+ }
+
+ test("qualified column names in lateral subquery") {
+ val t1b = b.withQualifier(Seq("t1"))
+ val t2b = b.withQualifier(Seq("t2"))
+ checkAnalysis(
+ LateralJoin(t1.as("t1"), LateralSubquery(t0.select($"t1.b")), Inner, None),
+ LateralJoin(
+ t1,
+ LateralSubquery(Project(Seq(OuterReference(t1b).as(b.name)), t0), Seq(t1b)),
+ Inner, None)
+ )
+ checkAnalysis(
+ LateralJoin(
+ t1.as("t1"),
+ LateralSubquery(t2.as("t2").select($"t1.b", $"t2.b")),
+ Inner, None),
+ LateralJoin(
+ t1,
+ LateralSubquery(Project(Seq(OuterReference(t1b).as(b.name), t2b), t2.as("t2")), Seq(t1b)),
+ Inner, None)
+ )
+ }
+
+ test("resolve nested lateral subqueries") {
+ // SELECT * FROM t1, LATERAL (SELECT * FROM t2, LATERAL (SELECT b, c))
+ val plan = LateralJoin(t1,
+ LateralSubquery(
+ LateralJoin(t2, LateralSubquery(t0.select('b, 'c)), Inner, None)),
+ Inner, None)
+ val expected = LateralJoin(t1,
+ LateralSubquery(LateralJoin(t2,
+ LateralSubquery(
+ Project(Seq(OuterReference(b).as(b.name), OuterReference(c).as(c.name)), t0),
+ Seq(b, c)),
+ Inner, None)),
+ Inner, None)
+ checkAnalysis(plan, expected)
+ }
+
+ test("lateral subquery with unresolvable attributes") {
+ // SELECT * FROM t1, LATERAL (SELECT a, c)
+ assertAnalysisError(
+ LateralJoin(t1, LateralSubquery(t0.select('a, 'c)), Inner, None),
+ Seq("cannot resolve 'c' given input columns: []")
+ )
+ // SELECT * FROM t1, LATERAL (SELECT a, b, c, d FROM t2)
+ assertAnalysisError(
+ LateralJoin(t1, LateralSubquery(t2.select('a, 'b, 'c, 'd)), Inner, None),
+ Seq("cannot resolve 'd' given input columns: [b, c]")
+ )
+ // SELECT * FROM t1, LATERAL (SELECT * FROM t2, LATERAL (SELECT t1.a))
+ assertAnalysisError(
+ LateralJoin(
+ t1,
+ LateralSubquery(LateralJoin(t2, LateralSubquery(t0.select($"t1.a")), Inner, None)),
+ Inner, None),
+ Seq("cannot resolve 't1.a' given input columns: []")
+ )
+ // SELECT * FROM t1, LATERAL (SELECT * FROM t2, LATERAL (SELECT a, b))
+ assertAnalysisError(
+ LateralJoin(
+ t1,
+ LateralSubquery(LateralJoin(t2, LateralSubquery(t0.select('a, 'b)), Inner, None)),
+ Inner, None),
+ Seq("cannot resolve 'a' given input columns: []")
+ )
+ }
+
+ test("lateral subquery with struct type") {
+ val xa = GetStructField(x, 0, Some("a"))
+ val ya = GetStructField(y, 0, Some("a"))
+ checkAnalysis(
+ LateralJoin(t4, LateralSubquery(t0.select($"x.a", $"y.a")), Inner, None),
+ LateralJoin(t4,
+ LateralSubquery(
+ Project(Seq(OuterReference(xa.as(a.name)), OuterReference(ya.as(a.name))), t0),
Review comment:
should the outer reference wrap the attribute instead of `GetStructField`?
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-857497315
**[Test build #139562 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/139562/testReport)** for PR 32303 at commit [`1b6d8f3`](https://github.com/apache/spark/commit/1b6d8f31d2dcce6ebfdd5709b3d61ad5cef9a8b9).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32303:
URL: https://github.com/apache/spark/pull/32303#issuecomment-846305088
**[Test build #138817 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/138817/testReport)** for PR 32303 at commit [`8e6cab3`](https://github.com/apache/spark/commit/8e6cab33e701a55d698a7239947ab85423010b7e).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] viirya commented on a change in pull request #32303: [SPARK-34382][SQL] Support LATERAL subqueries
Posted by GitBox <gi...@apache.org>.
viirya commented on a change in pull request #32303:
URL: https://github.com/apache/spark/pull/32303#discussion_r619569665
##########
File path: sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
##########
@@ -588,7 +588,7 @@ hintStatement
;
fromClause
- : FROM relation (',' relation)* lateralView* pivotClause?
+ : FROM relation (',' lateralClause)* (',' relation)* lateralView* pivotClause?
Review comment:
I think it was mentioned in the [manual](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL):
> A LATERAL item can appear at top level in the FROM list, or within a JOIN tree. In the latter case it can also refer to any items that are on the left-hand side of a JOIN that it is on the right-hand side of.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org