You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by "milanisvet (via GitHub)" <gi...@apache.org> on 2025/01/20 09:42:39 UTC

[PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

milanisvet opened a new pull request, #49571:
URL: https://github.com/apache/spark/pull/49571

   <!--
   Thanks for sending a pull request!  Here are some tips for you:
     1. If this is your first time, please read our contributor guidelines: https://spark.apache.org/contributing.html
     2. Ensure you have added or run the appropriate tests for your PR: https://spark.apache.org/developer-tools.html
     3. If the PR is unfinished, add '[WIP]' in your PR title, e.g., '[WIP][SPARK-XXXX] Your PR title ...'.
     4. Be sure to keep the PR description updated to reflect all changes.
     5. Please write your PR title to summarize what this PR proposes.
     6. If possible, provide a concise example to reproduce the issue for a faster review.
     7. If you want to add a new configuration, please read the guideline first for naming configurations in
        'core/src/main/scala/org/apache/spark/internal/config/ConfigEntry.scala'.
     8. If you want to add or modify an error type or message, please read the guideline first in
        'common/utils/src/main/resources/error/README.md'.
   -->
   
   ### What changes were proposed in this pull request?
   <!--
   Please clarify what changes you are proposing. The purpose of this section is to outline the changes and how this PR fixes the issue. 
   If possible, please consider writing useful notes for better and faster reviews in your PR. See the examples below.
     1. If you refactor some codes with changing classes, showing the class hierarchy will help reviewers.
     2. If you fix some SQL features, you can provide some references of other DBMSes.
     3. If there is design documentation, please add the link.
     4. If there is a discussion in the mailing list, please add the link.
   -->
   This PR introduces UnionLoopExec, physical operator for recursion: UnionLoop is converted to UnionLoopExec during execution. 
   For now only UNION ALL case is supported.
   The execution is performed by iteratively substituting UnionLoopRef with the plan obtained in previous step, as long as we are still generating new elements
   
   In addition, small changes to `Optimizer.scala` and `InlineCTE.scala` are added in case UnionLoop node is encountered, to make the execution work.
   
   ### Why are the changes needed?
   <!--
   Please clarify why the changes are needed. For instance,
     1. If you propose a new API, clarify the use case for a new API.
     2. If you fix a bug, you can clarify why it is a bug.
   -->
   Support for recursive CTE.
   
   
   ### Does this PR introduce _any_ user-facing change?
   <!--
   Note that it means *any* user-facing change including all aspects such as new features, bug fixes, or other behavior changes. Documentation-only updates are not considered user-facing changes.
   
   If yes, please clarify the previous behavior and the change this PR proposes - provide the console output, description and/or an example to show the behavior difference if possible.
   If possible, please also clarify if this is a user-facing change compared to the released Spark versions or within the unreleased branches such as master.
   If no, write 'No'.
   -->
   No.
   
   ### How was this patch tested?
   <!--
   If tests were added, say they were added here. Please make sure to add some test cases that check the changes thoroughly including negative and positive cases if possible.
   If it was tested in a way different from regular unit tests, please clarify how you tested step by step, ideally copy and paste-able, so that other reviewers can test and check, and descendants can verify in the future.
   If tests were not added, please describe why they were not added and/or why it was difficult to add.
   If benchmark tests were added, please run the benchmarks in GitHub Actions for the consistent environment, and the instructions could accord to: https://spark.apache.org/developer-tools.html#github-workflow-benchmarks.
   -->
   WIP: Additional golden files will be generated for the recursive use cases after we agree on the correct code structure.
   
   ### Was this patch authored or co-authored using generative AI tooling?
   <!--
   If generative AI tooling has been used in the process of authoring this patch, please include the
   phrase: 'Generated-by: ' followed by the name of the tool and its version.
   If no, write 'No'.
   Please refer to the [ASF Generative Tooling Guidance](https://www.apache.org/legal/generative-tooling.html) for details.
   -->
   No.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1922492638


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,140 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit An optional limit that can be pushed down to the node to stop the loop earlier.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {

Review Comment:
   I need a bit more context to understand how LIMIT is applied for recursive CTE. So every loop iteration produce some results, and the loop ends when the current iteration returns no result? And with a LIMIT we can end the loop earlier?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1932460808


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,133 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ *              Note here: limit can be applied in the main query calling the recursive CTE, and not
+ *              inside the recursive term of recursive CTE.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,

Review Comment:
   As far as I understand, @transient means that the object won't be serialized when caching, improving the speed of caching, but needing more storage.
   As we changed the caching strategy from cache/persist to repartition, I now removed it. Thanks for pointing this out.



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,133 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.

Review Comment:
   Done



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1926876850


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala:
##########
@@ -1031,6 +1038,9 @@ object ColumnPruning extends Rule[LogicalPlan] {
       } else {
         p
       }
+    // TODO: Pruning `UnionLoop`s needs to take into account both the outer `Project` and the inner
+    //  `UnionLoopRef` nodes.

Review Comment:
   I tried to run the code without this line, but it does not work, `mergeChildOutputs` throws an error that not all child outputs are of the same size, so we probably keep it then.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1932337835


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,133 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ *              Note here: limit can be applied in the main query calling the recursive CTE, and not
+ *              inside the recursive term of recursive CTE.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, currentLimit: Int) = {
+    // In case limit is defined, we create a (global) limit node above the plan and execute
+    // the newly created plan.
+    // Note here: global limit requires coordination (shuffle) between partitions.
+    val planOrLimitedPlan = if (limit.isDefined) {
+      Limit(Literal(currentLimit), plan)
+    } else {
+      plan
+    }
+    val df = Dataset.ofRows(session, planOrLimitedPlan)
+    val cachedDF = df.repartition()
+    val count = cachedDF.count()
+    (cachedDF, count)
+  }
+
+  override protected def doExecute(): RDD[InternalRow] = {
+    val executionId = sparkContext.getLocalProperty(SQLExecution.EXECUTION_ID_KEY)
+    val numOutputRows = longMetric("numOutputRows")
+    val levelLimit = conf.getConf(SQLConf.CTE_RECURSION_LEVEL_LIMIT)
+
+    // currentLimit is initialized from the limit argument, and in each step it is decreased by
+    // the number of rows generated in that step.
+    // If limit is not passed down, currentLimit is set to be zero and won't be considered in the
+    // condition of while loop down (limit.isEmpty will be true).
+    var currentLimit = limit.getOrElse(0)
+    val unionChildren = mutable.ArrayBuffer.empty[LogicalRDD]
+
+    var (prevDF, prevCount) = executeAndCacheAndCount(anchor, currentLimit)
+
+    var currentLevel = 1
+
+    // Main loop for obtaining the result of the recursive query.
+    while (prevCount > 0 && (limit.isEmpty || currentLimit > 0)) {
+
+      if (levelLimit != -1 && currentLevel > levelLimit) {
+        throw new SparkException(s"Recursion level limit ${levelLimit} reached but query has not " +

Review Comment:
   Added. Although not sure if I also need to add new `sqlState` for this, as the one used now, which I used for all the recursive CTEs is not so adequate for this.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1925763851


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,147 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  val levelLimit = conf.getConf(SQLConf.CTE_RECURSION_LEVEL_LIMIT)
+  val cacheMode = CTERecursionCacheMode.withName(conf.getConf(SQLConf.CTE_RECURSION_CACHE_MODE))
+
+  // We store the initial and generated children of the loop in this buffer.
+  // Please note that all elements are cached because of performance reasons as they are needed for
+  // next iteration.
+  @transient private val unionDFs = mutable.ArrayBuffer.empty[DataFrame]
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, limit: Option[Long]) = {
+    // In case limit is defined, we create a limit node above the plan and execute
+    // the newly created plan.
+    val limitedPlan = limit.map(l => Limit(Literal(l.toInt), plan)).getOrElse(plan)
+    val df = Dataset.ofRows(session, limitedPlan)
+    val cachedDF = cacheMode match {
+      case CTERecursionCacheMode.NONE => df
+      case CTERecursionCacheMode.REPARTITION => df.repartition()
+      case CTERecursionCacheMode.PERSIST => df.persist()
+      case CTERecursionCacheMode.LOCAL_CHECKPOINT => df.localCheckpoint()
+      case CTERecursionCacheMode.CHECKPOINT => df.checkpoint()
+    }
+    val count = cachedDF.count()
+    (cachedDF, count)
+  }
+
+  override protected def doExecute(): RDD[InternalRow] = {
+    val executionId = sparkContext.getLocalProperty(SQLExecution.EXECUTION_ID_KEY)
+    val numOutputRows = longMetric("numOutputRows")
+
+    // currentLimit is initialized from the limit argument, and in each step it is decreased by
+    // the number of rows generated in that step.
+    var currentLimit = limit.map(_.toLong)

Review Comment:
   Seems to be because `cachedDF.count()` from `executeCacheAndCount` returns Long, and then Peter probably wanted all of them to be Long.
   I set now currentLimit to a default value Int.MaxValue - which is at the same tame maximum number of rows that we allow to be generated 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.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1930280753


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala:
##########
@@ -848,6 +848,13 @@ object LimitPushDown extends Rule[LogicalPlan] {
     case LocalLimit(exp, u: Union) =>
       LocalLimit(exp, u.copy(children = u.children.map(maybePushLocalLimit(exp, _))))
 
+    // If limit node is present, we should propagate it down to UnionLoop, so that it is later
+    // propagated to UnionLoopExec.
+    case l @ LocalLimit(IntegerLiteral(limit), p @ Project(_, u: UnionLoop)) =>

Review Comment:
   I changed it now considering how it is pushed down through `Aggregate`. Now thinking regarding `EliminateSorts` if it is necessary or not



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1930275453


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala:
##########
@@ -4530,6 +4530,15 @@ object SQLConf {
     .checkValues(LegacyBehaviorPolicy.values.map(_.toString))
     .createWithDefault(LegacyBehaviorPolicy.CORRECTED.toString)
 
+  val CTE_RECURSION_LEVEL_LIMIT = buildConf("spark.sql.cteRecursionLevelLimit")
+    .internal()

Review Comment:
   Done



##########
sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala:
##########
@@ -4530,6 +4530,15 @@ object SQLConf {
     .checkValues(LegacyBehaviorPolicy.values.map(_.toString))
     .createWithDefault(LegacyBehaviorPolicy.CORRECTED.toString)
 
+  val CTE_RECURSION_LEVEL_LIMIT = buildConf("spark.sql.cteRecursionLevelLimit")
+    .internal()

Review Comment:
   Should be changed 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.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on PR #49571:
URL: https://github.com/apache/spark/pull/49571#issuecomment-2616761255

   I went through the outputs of all golden files tests and compared them with the outputs of the same (or syntactically slightly adapted) queries in Snowflake and PostgreSQL engines. All the outputs match.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1932461569


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,133 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ *              Note here: limit can be applied in the main query calling the recursive CTE, and not
+ *              inside the recursive term of recursive CTE.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, currentLimit: Int) = {
+    // In case limit is defined, we create a (global) limit node above the plan and execute
+    // the newly created plan.
+    // Note here: global limit requires coordination (shuffle) between partitions.
+    val planOrLimitedPlan = if (limit.isDefined) {
+      Limit(Literal(currentLimit), plan)
+    } else {
+      plan
+    }
+    val df = Dataset.ofRows(session, planOrLimitedPlan)
+    val cachedDF = df.repartition()
+    val count = cachedDF.count()
+    (cachedDF, count)
+  }
+
+  override protected def doExecute(): RDD[InternalRow] = {

Review Comment:
   Done



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1924863992


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala:
##########
@@ -1031,6 +1038,9 @@ object ColumnPruning extends Rule[LogicalPlan] {
       } else {
         p
       }
+    // TODO: Pruning `UnionLoop`s needs to take into account both the outer `Project` and the inner
+    //  `UnionLoopRef` nodes.

Review Comment:
   seems OK to just place a `Project` above `UnionLoopRef` to do column pruning?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1924873504


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,147 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  val levelLimit = conf.getConf(SQLConf.CTE_RECURSION_LEVEL_LIMIT)
+  val cacheMode = CTERecursionCacheMode.withName(conf.getConf(SQLConf.CTE_RECURSION_CACHE_MODE))
+
+  // We store the initial and generated children of the loop in this buffer.
+  // Please note that all elements are cached because of performance reasons as they are needed for
+  // next iteration.
+  @transient private val unionDFs = mutable.ArrayBuffer.empty[DataFrame]
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, limit: Option[Long]) = {
+    // In case limit is defined, we create a limit node above the plan and execute
+    // the newly created plan.
+    val limitedPlan = limit.map(l => Limit(Literal(l.toInt), plan)).getOrElse(plan)
+    val df = Dataset.ofRows(session, limitedPlan)
+    val cachedDF = cacheMode match {
+      case CTERecursionCacheMode.NONE => df
+      case CTERecursionCacheMode.REPARTITION => df.repartition()
+      case CTERecursionCacheMode.PERSIST => df.persist()
+      case CTERecursionCacheMode.LOCAL_CHECKPOINT => df.localCheckpoint()
+      case CTERecursionCacheMode.CHECKPOINT => df.checkpoint()
+    }
+    val count = cachedDF.count()
+    (cachedDF, count)
+  }
+
+  override protected def doExecute(): RDD[InternalRow] = {
+    val executionId = sparkContext.getLocalProperty(SQLExecution.EXECUTION_ID_KEY)
+    val numOutputRows = longMetric("numOutputRows")
+
+    // currentLimit is initialized from the limit argument, and in each step it is decreased by
+    // the number of rows generated in that step.
+    var currentLimit = limit.map(_.toLong)

Review Comment:
   does it have to be a long type 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.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1925831054


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,147 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  val levelLimit = conf.getConf(SQLConf.CTE_RECURSION_LEVEL_LIMIT)
+  val cacheMode = CTERecursionCacheMode.withName(conf.getConf(SQLConf.CTE_RECURSION_CACHE_MODE))
+
+  // We store the initial and generated children of the loop in this buffer.
+  // Please note that all elements are cached because of performance reasons as they are needed for
+  // next iteration.
+  @transient private val unionDFs = mutable.ArrayBuffer.empty[DataFrame]
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, limit: Option[Long]) = {
+    // In case limit is defined, we create a limit node above the plan and execute
+    // the newly created plan.
+    val limitedPlan = limit.map(l => Limit(Literal(l.toInt), plan)).getOrElse(plan)
+    val df = Dataset.ofRows(session, limitedPlan)
+    val cachedDF = cacheMode match {
+      case CTERecursionCacheMode.NONE => df
+      case CTERecursionCacheMode.REPARTITION => df.repartition()

Review Comment:
   Done.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1930841157


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala:
##########
@@ -4530,6 +4530,14 @@ object SQLConf {
     .checkValues(LegacyBehaviorPolicy.values.map(_.toString))
     .createWithDefault(LegacyBehaviorPolicy.CORRECTED.toString)
 
+  val CTE_RECURSION_LEVEL_LIMIT = buildConf("spark.sql.cteRecursionLevelLimit")
+    .doc("Maximum level of recursion that is allowed wile executing a recursive CTE definition." +
+      "If a query does not get exhausted before reaching this limit it fails. Use -1 for " +
+      "unlimited.")
+    .version("4.0.0")

Review Comment:
   Wenchen commented that this should be public config, therefore I removed internal (https://github.com/apache/spark/pull/49571#discussion_r1928712023) 



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1932245598


##########
common/utils/src/main/resources/error/error-conditions.json:
##########
@@ -5059,6 +5059,12 @@
     ],
     "sqlState" : "42846"
   },
+  "UNION_NOT_SUPPORTED_IN_RECURSIVE_CTE" : {
+    "message" : [
+      "UNION operator not yet supported in recursive CTEs. Use UNION ALL."

Review Comment:
   Rewritten, is much more clear now, I agree. Thanks a lot



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1932512215


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,133 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ *              Note here: limit can be applied in the main query calling the recursive CTE, and not
+ *              inside the recursive term of recursive CTE.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))

Review Comment:
   Added



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1924879887


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,147 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  val levelLimit = conf.getConf(SQLConf.CTE_RECURSION_LEVEL_LIMIT)
+  val cacheMode = CTERecursionCacheMode.withName(conf.getConf(SQLConf.CTE_RECURSION_CACHE_MODE))
+
+  // We store the initial and generated children of the loop in this buffer.
+  // Please note that all elements are cached because of performance reasons as they are needed for
+  // next iteration.
+  @transient private val unionDFs = mutable.ArrayBuffer.empty[DataFrame]
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, limit: Option[Long]) = {
+    // In case limit is defined, we create a limit node above the plan and execute
+    // the newly created plan.
+    val limitedPlan = limit.map(l => Limit(Literal(l.toInt), plan)).getOrElse(plan)

Review Comment:
   I'm wondering if we should insert `LocalLimit` here. This is an optimization but a global LIMIT needs an extra shuffle which might make the performance worse.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1924870068


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,147 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.

Review Comment:
   Can we add a bit more comment to introduce the execution workflow for this operator?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1925709323


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,147 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  val levelLimit = conf.getConf(SQLConf.CTE_RECURSION_LEVEL_LIMIT)
+  val cacheMode = CTERecursionCacheMode.withName(conf.getConf(SQLConf.CTE_RECURSION_CACHE_MODE))
+
+  // We store the initial and generated children of the loop in this buffer.
+  // Please note that all elements are cached because of performance reasons as they are needed for
+  // next iteration.
+  @transient private val unionDFs = mutable.ArrayBuffer.empty[DataFrame]
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, limit: Option[Long]) = {
+    // In case limit is defined, we create a limit node above the plan and execute
+    // the newly created plan.
+    val limitedPlan = limit.map(l => Limit(Literal(l.toInt), plan)).getOrElse(plan)

Review Comment:
   Yess



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1932458482


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,133 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.

Review Comment:
   Done



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1932566218


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala:
##########
@@ -848,6 +848,15 @@ object LimitPushDown extends Rule[LogicalPlan] {
     case LocalLimit(exp, u: Union) =>
       LocalLimit(exp, u.copy(children = u.children.map(maybePushLocalLimit(exp, _))))
 
+    // If limit node is present, we should propagate it down to UnionLoop, so that it is later
+    // propagated to UnionLoopExec.
+    // Limit node is constructed by placing GlobalLimit over LocalLimit (look at Limit apply method)
+    // that is the reason why we match it this way.
+    case g @ GlobalLimit(IntegerLiteral(limit), l @ LocalLimit(_, p @ Project(_, ul: UnionLoop))) =>

Review Comment:
   Playing out with this now, I saw the following problem:
   
   This query:
   ```
   WITH RECURSIVE t(n) AS (
       VALUES (1)
   UNION ALL
       SELECT n+1 FROM t)
   SELECT * FROM t LIMIT 10
   ```
   will return numbers in the following order: 1, 10, 2, 3, 4, 5, 6, 7, 8, 9
   
   Somehow, the order is not 1, 2, 3, 4....
   The correct result with an OFFSET 5 should be 6, 7, ..., 15 (postgres)
   
   So I think allowing OFFSET, it is not just enough to retain it in the outside query and apply it from there, but would require a similar approach as passing down the limit to UnionLoopExec and then adding some additional logic there.
   
   So maybe I would rather throw an error now if somebody try to use it, and leave the implementation for a follow-up PR.
   For now, in case OFFSET is used it will just perform an infinite loop and throw [RECURSION_LEVEL_LIMIT_EXCEEDED], but I will try to add a specific error for this case



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1922509471


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/InlineCTE.scala:
##########
@@ -61,7 +61,8 @@ case class InlineCTE(
     // 1) It is fine to inline a CTE if it references another CTE that is non-deterministic;
     // 2) Any `CTERelationRef` that contains `OuterReference` would have been inlined first.
     refCount == 1 ||
-      cteDef.deterministic ||
+      // Don't inline recursive CTEs if not necessary as recursion is very costly.

Review Comment:
   Yes, I had same opinion, you can not inline them as inlining something with a self-reference would require infinite inlining if I understand it correctly.
   For some reason kept Peter's comment, but I will rewrite it.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1922515273


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala:
##########
@@ -4520,6 +4520,31 @@ object SQLConf {
     .checkValues(LegacyBehaviorPolicy.values.map(_.toString))
     .createWithDefault(LegacyBehaviorPolicy.CORRECTED.toString)
 
+  val CTE_RECURSION_LEVEL_LIMIT = buildConf("spark.sql.cteRecursionLevelLimit")
+    .internal()
+    .doc("Maximum level of recursion that is allowed wile executing a recursive CTE definition." +
+      "If a query does not get exhausted before reaching this limit it fails. Use -1 for " +
+      "unlimited.")
+    .version("4.0.0")
+    .intConf
+    .createWithDefault(100)
+
+  object CTERecursionCacheMode extends Enumeration {
+    val NONE, REPARTITION, PERSIST, LOCAL_CHECKPOINT, CHECKPOINT = Value
+  }
+
+  val CTE_RECURSION_CACHE_MODE = buildConf("spark.sql.cteRecursionCacheMode")

Review Comment:
   Not really sure which option we should use, I suppose there are cases where one option will be better than other. I probably need to test this and see.
   But I agree it does not have to be exposed to the end user probably.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1932461286


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,133 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then

Review Comment:
   Done



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1922482014


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/InlineCTE.scala:
##########
@@ -61,7 +61,8 @@ case class InlineCTE(
     // 1) It is fine to inline a CTE if it references another CTE that is non-deterministic;
     // 2) Any `CTERelationRef` that contains `OuterReference` would have been inlined first.
     refCount == 1 ||
-      cteDef.deterministic ||
+      // Don't inline recursive CTEs if not necessary as recursion is very costly.

Review Comment:
   is it really for perf? Semantically we can't inline recursive CTE as we must run it, right?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1926577857


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,129 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ *              Note here: limit can be applied in the main query calling the recursive CTE, and not
+ *              inside the recursive term of recursive CTE.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  // We store the initial and generated children of the loop in this buffer.
+  // Please note that all elements are cached because of performance reasons as they are needed for
+  // next iteration.
+  @transient private val unionDFs = mutable.ArrayBuffer.empty[DataFrame]
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, currentLimit: Int) = {
+    // In case limit is defined, we create a limit node above the plan and execute
+    // the newly created plan.
+    val limitedPlan = Limit(Literal(currentLimit), plan)

Review Comment:
   ```suggestion
       val limitedPlan = LocalLimit(Literal(currentLimit), 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.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1924881213


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,147 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  val levelLimit = conf.getConf(SQLConf.CTE_RECURSION_LEVEL_LIMIT)
+  val cacheMode = CTERecursionCacheMode.withName(conf.getConf(SQLConf.CTE_RECURSION_CACHE_MODE))
+
+  // We store the initial and generated children of the loop in this buffer.
+  // Please note that all elements are cached because of performance reasons as they are needed for
+  // next iteration.
+  @transient private val unionDFs = mutable.ArrayBuffer.empty[DataFrame]
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, limit: Option[Long]) = {
+    // In case limit is defined, we create a limit node above the plan and execute
+    // the newly created plan.
+    val limitedPlan = limit.map(l => Limit(Literal(l.toInt), plan)).getOrElse(plan)

Review Comment:
   ah it's actually local limit: https://github.com/apache/spark/pull/49571/files#diff-11264d807efa58054cca2d220aae8fba644ee0f0f2a4722c46d52828394846efR853



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1932566218


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala:
##########
@@ -848,6 +848,15 @@ object LimitPushDown extends Rule[LogicalPlan] {
     case LocalLimit(exp, u: Union) =>
       LocalLimit(exp, u.copy(children = u.children.map(maybePushLocalLimit(exp, _))))
 
+    // If limit node is present, we should propagate it down to UnionLoop, so that it is later
+    // propagated to UnionLoopExec.
+    // Limit node is constructed by placing GlobalLimit over LocalLimit (look at Limit apply method)
+    // that is the reason why we match it this way.
+    case g @ GlobalLimit(IntegerLiteral(limit), l @ LocalLimit(_, p @ Project(_, ul: UnionLoop))) =>

Review Comment:
   Playing out with this now, I saw the following problem:
   
   This query:
   ```
   WITH RECURSIVE t(n) AS (
       VALUES (1)
   UNION ALL
       SELECT n+1 FROM t)
   SELECT * FROM t LIMIT 10
   ```
   will return numbers in the following order: 1, 10, 2, 3, 4, 5, 6, 7, 8, 9
   
   Somehow, the order is not 1, 2, 3, 4....
   The correct result with an OFFSET 5 should be 6, 7, ..., 15 (postgres)
   
   So I think allowing OFFSET, it is not just enough to retain it in the outside query and apply it from there, but would require a similar approach as passing down the limit to UnionLoopExec and then adding some additional logic there.
   
   So maybe I would rather throw an error now if somebody try to use it, and leave the implementation for a follow-up PR.
   For now, in case OFFSET is used it will just perform an infinite loop and throw `[RECURSION_LEVEL_LIMIT_EXCEEDED]`, but I will try to add a specific error for this case



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1924876120


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,147 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  val levelLimit = conf.getConf(SQLConf.CTE_RECURSION_LEVEL_LIMIT)
+  val cacheMode = CTERecursionCacheMode.withName(conf.getConf(SQLConf.CTE_RECURSION_CACHE_MODE))
+
+  // We store the initial and generated children of the loop in this buffer.
+  // Please note that all elements are cached because of performance reasons as they are needed for
+  // next iteration.
+  @transient private val unionDFs = mutable.ArrayBuffer.empty[DataFrame]
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, limit: Option[Long]) = {
+    // In case limit is defined, we create a limit node above the plan and execute
+    // the newly created plan.
+    val limitedPlan = limit.map(l => Limit(Literal(l.toInt), plan)).getOrElse(plan)
+    val df = Dataset.ofRows(session, limitedPlan)
+    val cachedDF = cacheMode match {
+      case CTERecursionCacheMode.NONE => df
+      case CTERecursionCacheMode.REPARTITION => df.repartition()
+      case CTERecursionCacheMode.PERSIST => df.persist()
+      case CTERecursionCacheMode.LOCAL_CHECKPOINT => df.localCheckpoint()
+      case CTERecursionCacheMode.CHECKPOINT => df.checkpoint()
+    }
+    val count = cachedDF.count()
+    (cachedDF, count)
+  }
+
+  override protected def doExecute(): RDD[InternalRow] = {
+    val executionId = sparkContext.getLocalProperty(SQLExecution.EXECUTION_ID_KEY)
+    val numOutputRows = longMetric("numOutputRows")
+
+    // currentLimit is initialized from the limit argument, and in each step it is decreased by
+    // the number of rows generated in that step.
+    var currentLimit = limit.map(_.toLong)

Review Comment:
   BTW to simplify the code we can assign a default value to it: Int.MaxValue.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1925698021


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,147 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  val levelLimit = conf.getConf(SQLConf.CTE_RECURSION_LEVEL_LIMIT)

Review Comment:
   Moved there.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1922518700


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,140 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit An optional limit that can be pushed down to the node to stop the loop earlier.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {

Review Comment:
   Yes, and in that case the exception is thrown to say that _limit is reach but query has not exhausted yet_. It is again left there because Peter initially put it there. 
   If you think limit is not necessary/does not make sense in this context, I will remove it.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1922519875


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,140 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit An optional limit that can be pushed down to the node to stop the loop earlier.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {

Review Comment:
   If not, I can add some comments to clarify it



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "gatorsmile (via GitHub)" <gi...@apache.org>.
gatorsmile commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1930838380


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala:
##########
@@ -4530,6 +4530,14 @@ object SQLConf {
     .checkValues(LegacyBehaviorPolicy.values.map(_.toString))
     .createWithDefault(LegacyBehaviorPolicy.CORRECTED.toString)
 
+  val CTE_RECURSION_LEVEL_LIMIT = buildConf("spark.sql.cteRecursionLevelLimit")
+    .doc("Maximum level of recursion that is allowed wile executing a recursive CTE definition." +
+      "If a query does not get exhausted before reaching this limit it fails. Use -1 for " +
+      "unlimited.")
+    .version("4.0.0")

Review Comment:
   internal ?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1929961370


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala:
##########
@@ -848,6 +848,13 @@ object LimitPushDown extends Rule[LogicalPlan] {
     case LocalLimit(exp, u: Union) =>
       LocalLimit(exp, u.copy(children = u.children.map(maybePushLocalLimit(exp, _))))
 
+    // If limit node is present, we should propagate it down to UnionLoop, so that it is later
+    // propagated to UnionLoopExec.
+    case l @ LocalLimit(IntegerLiteral(limit), p @ Project(_, u: UnionLoop)) =>

Review Comment:
   pushing down local limit is not very useful, as we can't use it to stop the loop earlier. We should push down the global limit.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1930771259


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,129 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ *              Note here: limit can be applied in the main query calling the recursive CTE, and not
+ *              inside the recursive term of recursive CTE.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  // We store the initial and generated children of the loop in this buffer.
+  // Please note that all elements are cached because of performance reasons as they are needed for
+  // next iteration.
+  @transient private val unionDFs = mutable.ArrayBuffer.empty[DataFrame]
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, currentLimit: Int) = {
+    // In case limit is defined, we create a limit node above the plan and execute
+    // the newly created plan.
+    val limitedPlan = Limit(Literal(currentLimit), plan)

Review Comment:
   Changed back to Limit with other changes, more comments about the reason below



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,147 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  val levelLimit = conf.getConf(SQLConf.CTE_RECURSION_LEVEL_LIMIT)
+  val cacheMode = CTERecursionCacheMode.withName(conf.getConf(SQLConf.CTE_RECURSION_CACHE_MODE))
+
+  // We store the initial and generated children of the loop in this buffer.
+  // Please note that all elements are cached because of performance reasons as they are needed for
+  // next iteration.
+  @transient private val unionDFs = mutable.ArrayBuffer.empty[DataFrame]
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, limit: Option[Long]) = {
+    // In case limit is defined, we create a limit node above the plan and execute
+    // the newly created plan.
+    val limitedPlan = limit.map(l => Limit(Literal(l.toInt), plan)).getOrElse(plan)

Review Comment:
   Changed back to Limit with other changes, more comments about the reason below



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1930777522


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,147 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  val levelLimit = conf.getConf(SQLConf.CTE_RECURSION_LEVEL_LIMIT)
+  val cacheMode = CTERecursionCacheMode.withName(conf.getConf(SQLConf.CTE_RECURSION_CACHE_MODE))
+
+  // We store the initial and generated children of the loop in this buffer.
+  // Please note that all elements are cached because of performance reasons as they are needed for
+  // next iteration.
+  @transient private val unionDFs = mutable.ArrayBuffer.empty[DataFrame]
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, limit: Option[Long]) = {
+    // In case limit is defined, we create a limit node above the plan and execute
+    // the newly created plan.
+    val limitedPlan = limit.map(l => Limit(Literal(l.toInt), plan)).getOrElse(plan)
+    val df = Dataset.ofRows(session, limitedPlan)
+    val cachedDF = cacheMode match {
+      case CTERecursionCacheMode.NONE => df
+      case CTERecursionCacheMode.REPARTITION => df.repartition()
+      case CTERecursionCacheMode.PERSIST => df.persist()
+      case CTERecursionCacheMode.LOCAL_CHECKPOINT => df.localCheckpoint()
+      case CTERecursionCacheMode.CHECKPOINT => df.checkpoint()
+    }
+    val count = cachedDF.count()
+    (cachedDF, count)
+  }
+
+  override protected def doExecute(): RDD[InternalRow] = {
+    val executionId = sparkContext.getLocalProperty(SQLExecution.EXECUTION_ID_KEY)
+    val numOutputRows = longMetric("numOutputRows")
+
+    // currentLimit is initialized from the limit argument, and in each step it is decreased by
+    // the number of rows generated in that step.
+    var currentLimit = limit.map(_.toLong)

Review Comment:
   Removed Int.MaxValue as a default currentLimit. Detailed explanation in the last comment on limit.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1930771259


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,129 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ *              Note here: limit can be applied in the main query calling the recursive CTE, and not
+ *              inside the recursive term of recursive CTE.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  // We store the initial and generated children of the loop in this buffer.
+  // Please note that all elements are cached because of performance reasons as they are needed for
+  // next iteration.
+  @transient private val unionDFs = mutable.ArrayBuffer.empty[DataFrame]
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, currentLimit: Int) = {
+    // In case limit is defined, we create a limit node above the plan and execute
+    // the newly created plan.
+    val limitedPlan = Limit(Literal(currentLimit), plan)

Review Comment:
   Changed back to Limit with other changes, another comments on limit below



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1924001588


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/InlineCTE.scala:
##########
@@ -61,7 +61,8 @@ case class InlineCTE(
     // 1) It is fine to inline a CTE if it references another CTE that is non-deterministic;
     // 2) Any `CTERelationRef` that contains `OuterReference` would have been inlined first.
     refCount == 1 ||
-      cteDef.deterministic ||
+      // Don't inline recursive CTEs if not necessary as recursion is very costly.

Review Comment:
   This will be addressed in error checking PR, therefore I reverted these changes



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1925694719


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,147 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.

Review Comment:
   Comment extended :)



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1924870778


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,147 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  val levelLimit = conf.getConf(SQLConf.CTE_RECURSION_LEVEL_LIMIT)

Review Comment:
   it's only used in `def doExecute` and let's define it as a local variable there.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1922484925


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala:
##########
@@ -4520,6 +4520,31 @@ object SQLConf {
     .checkValues(LegacyBehaviorPolicy.values.map(_.toString))
     .createWithDefault(LegacyBehaviorPolicy.CORRECTED.toString)
 
+  val CTE_RECURSION_LEVEL_LIMIT = buildConf("spark.sql.cteRecursionLevelLimit")
+    .internal()
+    .doc("Maximum level of recursion that is allowed wile executing a recursive CTE definition." +
+      "If a query does not get exhausted before reaching this limit it fails. Use -1 for " +
+      "unlimited.")
+    .version("4.0.0")
+    .intConf
+    .createWithDefault(100)
+
+  object CTERecursionCacheMode extends Enumeration {
+    val NONE, REPARTITION, PERSIST, LOCAL_CHECKPOINT, CHECKPOINT = Value
+  }
+
+  val CTE_RECURSION_CACHE_MODE = buildConf("spark.sql.cteRecursionCacheMode")

Review Comment:
   do we really want end users to control it? shall we always use repartition?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1930549621


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala:
##########
@@ -848,6 +848,13 @@ object LimitPushDown extends Rule[LogicalPlan] {
     case LocalLimit(exp, u: Union) =>
       LocalLimit(exp, u.copy(children = u.children.map(maybePushLocalLimit(exp, _))))
 
+    // If limit node is present, we should propagate it down to UnionLoop, so that it is later
+    // propagated to UnionLoopExec.
+    case l @ LocalLimit(IntegerLiteral(limit), p @ Project(_, u: UnionLoop)) =>

Review Comment:
   Changed in the following way:
   1. `GlobalLimit` is matched in optimizer and pushed down
   2. Global limit (`Limit`) node is prepended to the plan in `UnionLoopExec` in case `limit` is passed down and not `None`
   3. `Limit` is not prepended to the plan in case no limit is passed down (`limit` is `None`) to prevent unnecessary shuffle due to `GlobalLimit`
   
   Just a side note here: `Limit` node always creates `GlobalLimit` on top of `LocalLimit`



##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala:
##########
@@ -848,6 +848,13 @@ object LimitPushDown extends Rule[LogicalPlan] {
     case LocalLimit(exp, u: Union) =>
       LocalLimit(exp, u.copy(children = u.children.map(maybePushLocalLimit(exp, _))))
 
+    // If limit node is present, we should propagate it down to UnionLoop, so that it is later
+    // propagated to UnionLoopExec.
+    case l @ LocalLimit(IntegerLiteral(limit), p @ Project(_, u: UnionLoop)) =>

Review Comment:
   Changed now in the following way after rethinking again:
   1. `GlobalLimit` is matched in optimizer and pushed down
   2. Global limit (`Limit`) node is prepended to the plan in `UnionLoopExec` in case `limit` is passed down and not `None`
   3. `Limit` is not prepended to the plan in case no limit is passed down (`limit` is `None`) to prevent unnecessary shuffle due to `GlobalLimit`
   
   Just a side note here: `Limit` node always creates `GlobalLimit` on top of `LocalLimit`



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1932507711


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,133 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ *              Note here: limit can be applied in the main query calling the recursive CTE, and not
+ *              inside the recursive term of recursive CTE.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,

Review Comment:
   Actually now I had to add back @transient as the tests threw java.io.NotSerializableException without it. These objects probably should not be serialized and therefore we added this annotation. 
   Although still not 100% sure why they should not need to.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1930280753


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala:
##########
@@ -848,6 +848,13 @@ object LimitPushDown extends Rule[LogicalPlan] {
     case LocalLimit(exp, u: Union) =>
       LocalLimit(exp, u.copy(children = u.children.map(maybePushLocalLimit(exp, _))))
 
+    // If limit node is present, we should propagate it down to UnionLoop, so that it is later
+    // propagated to UnionLoopExec.
+    case l @ LocalLimit(IntegerLiteral(limit), p @ Project(_, u: UnionLoop)) =>

Review Comment:
   I changed it now considering how it is pushed down through `Aggregate`. Now thinking regarding `EliminateSorts` if it is necessary or not. Current tests with LIMIT are passing



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "dtenedor (via GitHub)" <gi...@apache.org>.
dtenedor commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1931294394


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala:
##########
@@ -848,6 +848,15 @@ object LimitPushDown extends Rule[LogicalPlan] {
     case LocalLimit(exp, u: Union) =>
       LocalLimit(exp, u.copy(children = u.children.map(maybePushLocalLimit(exp, _))))
 
+    // If limit node is present, we should propagate it down to UnionLoop, so that it is later
+    // propagated to UnionLoopExec.
+    // Limit node is constructed by placing GlobalLimit over LocalLimit (look at Limit apply method)
+    // that is the reason why we match it this way.
+    case g @ GlobalLimit(IntegerLiteral(limit), l @ LocalLimit(_, p @ Project(_, ul: UnionLoop))) =>

Review Comment:
   Should we add helpers like `LimitAndOffset` [1] to match against global + local limits like this?
   
   [1] https://github.com/apache/spark/blob/fef1b2375c3074cb3b53d5c29df1aa27c269469c/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/basicLogicalOperators.scala#L1605



##########
common/utils/src/main/resources/error/error-conditions.json:
##########
@@ -5059,6 +5059,12 @@
     ],
     "sqlState" : "42846"
   },
+  "UNION_NOT_SUPPORTED_IN_RECURSIVE_CTE" : {
+    "message" : [
+      "UNION operator not yet supported in recursive CTEs. Use UNION ALL."

Review Comment:
   ```suggestion
         "The UNION operator is not yet supported within recursive common table expressions (WITH clauses that refer to themselves, directly or indirectly). Please use UNION ALL instead."
   ```



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,133 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.

Review Comment:
   also mention what this is used for, and who sets it and references it?



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,133 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ *              Note here: limit can be applied in the main query calling the recursive CTE, and not
+ *              inside the recursive term of recursive CTE.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,

Review Comment:
   why is this transient?



##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala:
##########
@@ -1031,6 +1038,9 @@ object ColumnPruning extends Rule[LogicalPlan] {
       } else {
         p
       }
+    // TODO: Pruning `UnionLoop`s needs to take into account both the outer `Project` and the inner
+    //  `UnionLoopRef` nodes.

Review Comment:
   Why do we drop the `UnionLoop` here?



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,133 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ *              Note here: limit can be applied in the main query calling the recursive CTE, and not
+ *              inside the recursive term of recursive CTE.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))

Review Comment:
   is it possible to include the number of recursive loops in here as well?



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,133 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ *              Note here: limit can be applied in the main query calling the recursive CTE, and not
+ *              inside the recursive term of recursive CTE.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, currentLimit: Int) = {
+    // In case limit is defined, we create a (global) limit node above the plan and execute
+    // the newly created plan.
+    // Note here: global limit requires coordination (shuffle) between partitions.
+    val planOrLimitedPlan = if (limit.isDefined) {
+      Limit(Literal(currentLimit), plan)
+    } else {
+      plan
+    }
+    val df = Dataset.ofRows(session, planOrLimitedPlan)
+    val cachedDF = df.repartition()
+    val count = cachedDF.count()
+    (cachedDF, count)
+  }
+
+  override protected def doExecute(): RDD[InternalRow] = {
+    val executionId = sparkContext.getLocalProperty(SQLExecution.EXECUTION_ID_KEY)
+    val numOutputRows = longMetric("numOutputRows")
+    val levelLimit = conf.getConf(SQLConf.CTE_RECURSION_LEVEL_LIMIT)
+
+    // currentLimit is initialized from the limit argument, and in each step it is decreased by
+    // the number of rows generated in that step.
+    // If limit is not passed down, currentLimit is set to be zero and won't be considered in the
+    // condition of while loop down (limit.isEmpty will be true).
+    var currentLimit = limit.getOrElse(0)
+    val unionChildren = mutable.ArrayBuffer.empty[LogicalRDD]
+
+    var (prevDF, prevCount) = executeAndCacheAndCount(anchor, currentLimit)
+
+    var currentLevel = 1
+
+    // Main loop for obtaining the result of the recursive query.
+    while (prevCount > 0 && (limit.isEmpty || currentLimit > 0)) {
+
+      if (levelLimit != -1 && currentLevel > levelLimit) {
+        throw new SparkException(s"Recursion level limit ${levelLimit} reached but query has not " +

Review Comment:
   please add an error class for this



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,133 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then

Review Comment:
   in other words, this represents a limit on the total number of rows output by this operator, correct? if so, let's mention that first as it's the high-order bit of information here.



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,133 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.

Review Comment:
   also reiterate here what the `[[UnionLoopRef]]` represents? This could be improved with an example above the @params with a short SQL snippet and corresponding logical plan.



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +717,133 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ * In the first iteration, anchor term is executed.
+ * Then, in each following iteration, the UnionLoopRef node is substituted with the plan from the
+ * previous iteration, and such plan is executed.
+ * After every iteration, the dataframe is repartitioned.
+ * The recursion stops when the generated dataframe is empty, or either the limit or
+ * the specified maximum depth from the config is reached.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ *              Note here: limit can be applied in the main query calling the recursive CTE, and not
+ *              inside the recursive term of recursive CTE.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, currentLimit: Int) = {
+    // In case limit is defined, we create a (global) limit node above the plan and execute
+    // the newly created plan.
+    // Note here: global limit requires coordination (shuffle) between partitions.
+    val planOrLimitedPlan = if (limit.isDefined) {
+      Limit(Literal(currentLimit), plan)
+    } else {
+      plan
+    }
+    val df = Dataset.ofRows(session, planOrLimitedPlan)
+    val cachedDF = df.repartition()
+    val count = cachedDF.count()
+    (cachedDF, count)
+  }
+
+  override protected def doExecute(): RDD[InternalRow] = {

Review Comment:
   could we please have a descriptive comment here that says what the general steps are of the execution implementation?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1924871173


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -714,6 +718,147 @@ case class UnionExec(children: Seq[SparkPlan]) extends SparkPlan {
     copy(children = newChildren)
 }
 
+/**
+ * The physical node for recursion. Currently only UNION ALL case is supported.
+ *
+ * @param loopId The id of the loop.
+ * @param anchor The logical plan of the initial element of the loop.
+ * @param recursion The logical plan that describes the recursion with an [[UnionLoopRef]] node.
+ * @param output The output attributes of this loop.
+ * @param limit In case we have a plan with the limit node, it is pushed down to UnionLoop and then
+ *              transferred to UnionLoopExec, to stop the recursion after specific amount of rows
+ *              is generated.
+ */
+case class UnionLoopExec(
+    loopId: Long,
+    @transient anchor: LogicalPlan,
+    @transient recursion: LogicalPlan,
+    override val output: Seq[Attribute],
+    limit: Option[Int] = None) extends LeafExecNode {
+
+  val levelLimit = conf.getConf(SQLConf.CTE_RECURSION_LEVEL_LIMIT)
+  val cacheMode = CTERecursionCacheMode.withName(conf.getConf(SQLConf.CTE_RECURSION_CACHE_MODE))
+
+  // We store the initial and generated children of the loop in this buffer.
+  // Please note that all elements are cached because of performance reasons as they are needed for
+  // next iteration.
+  @transient private val unionDFs = mutable.ArrayBuffer.empty[DataFrame]
+
+  override def innerChildren: Seq[QueryPlan[_]] = Seq(anchor, recursion)
+
+  override lazy val metrics = Map(
+    "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"))
+
+  /**
+   * This function executes the plan (optionally with appended limit node) and caches the result,
+   * with the caching mode specified in config.
+   */
+  private def executeAndCacheAndCount(
+     plan: LogicalPlan, limit: Option[Long]) = {
+    // In case limit is defined, we create a limit node above the plan and execute
+    // the newly created plan.
+    val limitedPlan = limit.map(l => Limit(Literal(l.toInt), plan)).getOrElse(plan)
+    val df = Dataset.ofRows(session, limitedPlan)
+    val cachedDF = cacheMode match {
+      case CTERecursionCacheMode.NONE => df
+      case CTERecursionCacheMode.REPARTITION => df.repartition()

Review Comment:
   let's always use `df.repartition()`



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "milanisvet (via GitHub)" <gi...@apache.org>.
milanisvet commented on code in PR #49571:
URL: https://github.com/apache/spark/pull/49571#discussion_r1925710464


##########
sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala:
##########
@@ -4520,6 +4520,31 @@ object SQLConf {
     .checkValues(LegacyBehaviorPolicy.values.map(_.toString))
     .createWithDefault(LegacyBehaviorPolicy.CORRECTED.toString)
 
+  val CTE_RECURSION_LEVEL_LIMIT = buildConf("spark.sql.cteRecursionLevelLimit")
+    .internal()
+    .doc("Maximum level of recursion that is allowed wile executing a recursive CTE definition." +
+      "If a query does not get exhausted before reaching this limit it fails. Use -1 for " +
+      "unlimited.")
+    .version("4.0.0")
+    .intConf
+    .createWithDefault(100)
+
+  object CTERecursionCacheMode extends Enumeration {
+    val NONE, REPARTITION, PERSIST, LOCAL_CHECKPOINT, CHECKPOINT = Value
+  }
+
+  val CTE_RECURSION_CACHE_MODE = buildConf("spark.sql.cteRecursionCacheMode")

Review Comment:
   Will be set to be REPARTITION always as discussed



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #49571:
URL: https://github.com/apache/spark/pull/49571#issuecomment-2864765547

   We're closing this PR because it hasn't been updated in a while. This isn't a judgement on the merit of the PR in any way. It's just a way of keeping the PR queue manageable.
   If you'd like to revive this PR, please reopen it and ask a committer to remove the Stale tag!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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


Re: [PR] [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries [spark]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed pull request #49571: [WIP][SPARK-50892][SQL]Add UnionLoopExec, physical operator for recursion, to perform execution of recursive queries
URL: https://github.com/apache/spark/pull/49571


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

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