You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by GitBox <gi...@apache.org> on 2020/11/16 10:04:59 UTC

[GitHub] [spark] beliefer opened a new pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

beliefer opened a new pull request #30387:
URL: https://github.com/apache/spark/pull/30387


   ### What changes were proposed in this pull request?
   The mainstream database support `[ IGNORE NULLS | RESPECT NULLS ]` for `LEAD`/`LAG`/`NTH_VALUE`/`FIRST_VALUE`/`LAST_VALUE`.
   But the current implement of `LEAD`/`LAG` don't support this syntax.
   
   **Oracle**
   https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/LEAD.html#GUID-0A0481F1-E98F-4535-A739-FCCA8D1B5B77
   
   **Presto**
   https://prestodb.io/docs/current/functions/window.html
   
   **Redshift**
   https://docs.aws.amazon.com/redshift/latest/dg/r_WF_LEAD.html
   
   
   ### Why are the changes needed?
   Support `[ IGNORE NULLS | RESPECT NULLS ]` for `LEAD`/`LAG` is very useful.
   
   
   ### Does this PR introduce _any_ user-facing change?
   'Yes'.
   
   
   ### How was this patch tested?
   Jenkins test.
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728664988






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r527610591



##########
File path: sql/core/src/test/scala/org/apache/spark/sql/DataFrameWindowFunctionsSuite.scala
##########
@@ -700,6 +700,42 @@ class DataFrameWindowFunctionsSuite extends QueryTest
         Row("b", 3, null, null, null)))
   }
 
+  test("lead with ignoreNulls") {

Review comment:
       I will open another PR to unify the support for [IGNORE NULLS | RESPECT NULLS] in the SQL parser.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728905674


   **[Test build #131210 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131210/testReport)** for PR 30387 at commit [`be70bbe`](https://github.com/apache/spark/commit/be70bbe4a02a1523ca32fbd234c803b696bd7313).
    * This patch passes all tests.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750042554


   **[Test build #133290 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133290/testReport)** for PR 30387 at commit [`8510fea`](https://github.com/apache/spark/commit/8510fea02d0e6face7100446d0fe180d5fbccfca).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750807617


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133340/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750211551


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37901/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750188094






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547269707



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**
+   *  The number of UnsafeRows skipped to get the next UnsafeRow where
+   *  the input operator by function is not null.
+   */
+  private var skipNonNullCount = 0
+
+  /** find the offset row whose input is not null */
+  private def findNextRowWithNonNullInput(): Unit = {
+    while (skipNonNullCount < offset && inputIndex < input.length) {
+      val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+      if (!r.isNullAt(idx)) {
+        nextSelectedRow = r
+        skipNonNullCount += 1
+      }
+      inputIndex += 1
+    }
+  }
+
   override def prepare(rows: ExternalAppendOnlyUnsafeRowArray): Unit = {
     input = rows
     inputIterator = input.generateIterator()
     // drain the first few rows if offset is larger than zero
     inputIndex = 0
-    while (inputIndex < offset) {
-      if (inputIterator.hasNext) inputIterator.next()
-      inputIndex += 1
+    if (ignoreNulls) {
+      findNextRowWithNonNullInput
+    } else {
+      while (inputIndex < offset) {
+        if (inputIterator.hasNext) inputIterator.next()
+        inputIndex += 1
+      }
+      inputIndex = offset
     }
-    inputIndex = offset
+  }
+
+  private val doWrite = if (ignoreNulls && offset > 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lead(input, 2) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, next selected row -> y, output: y;
+    // 2. current row -> x, next selected row -> z, output: z;
+    // 3. current row -> null, next selected row -> z, output: z;
+    // 4. current row -> null, next selected row -> z, output: z;
+    // 5. current row -> y, next selected row -> empty, output: null;
+    // ... next selected row is empty, all following return null.
+    (current: InternalRow) =>
+      if (current.isNullAt(idx)) {
+        if (nextSelectedRow == EmptyRow) {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+        } else {
+          projection(nextSelectedRow)
+        }
+      } else {
+        skipNonNullCount -= 1
+        findNextRowWithNonNullInput
+        if (skipNonNullCount == offset) {
+          projection(nextSelectedRow)
+        } else {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+          nextSelectedRow = EmptyRow
+        }
+      }
+  } else if (ignoreNulls && offset < 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lag(input, 1) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, next selected row -> empty, output: null;
+    // 2. current row -> x, next selected row -> empty, output: null;
+    // 3. current row -> null, next selected row -> x, output: x;
+    // 4. current row -> null, next selected row -> x, output: x;
+    // 5. current row -> y, next selected row -> x, output: x;
+    // 6. current row -> null, next selected row -> y, output: y;
+    // 7. current row -> z, next selected row -> y, output: y;
+    // 8. current row -> z, next selected row -> z, output: z;
+    val absOffset = Math.abs(offset)
+    (current: InternalRow) =>
+      if (nextSelectedRow == EmptyRow && skipNonNullCount == absOffset) {

Review comment:
       We can implement the algorithm with 3 steps:
   1. find the next non-null when `skippedNonNullCount == absOffset`
   2. set the result row w.r.t. the `nextSelectedRow`
   3. check the current row to update `skippedNonNullCount`
   
   The code can be simplified
   ```
   // comment for step 1
   if (skipNonNullCount == absOffset) {
     nextSelectedRow = EmptyRow
     skipNonNullCount -= 1
     while (nextSelectedRow == EmptyRow && inputIndex < input.length) {
       ...
     }
   }
   // comment for step 2
   if (nextSelectedRow == EmptyRow) ... else ...
   // comment for step 3
   if (!current.isNullAt(idx)) {
     skipNonNullCount += 1
   }
   ```




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r526947812



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -151,6 +152,24 @@ class FrameLessOffsetWindowFunctionFrame(
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** Whether null values of input expression are included in or eliminated from the calculation. */
+  private val ignoreNulls = expressions.head.ignoreNulls

Review comment:
       can we add an assert here to make sure all expressions have the same `ignoreNulls` value?




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r545746333



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +150,115 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Cache some UnsafeRow that will be used many times. */
+  private val rowBuffer = new ArrayBuffer[UnsafeRow]

Review comment:
       Good idea.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730458041






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-746347913


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/132884/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748863068


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37733/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-746128937


   **[Test build #132884 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/132884/testReport)** for PR 30387 at commit [`2023897`](https://github.com/apache/spark/commit/2023897982d2d852d0fd09c1939777c7740c621e).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547269707



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**
+   *  The number of UnsafeRows skipped to get the next UnsafeRow where
+   *  the input operator by function is not null.
+   */
+  private var skipNonNullCount = 0
+
+  /** find the offset row whose input is not null */
+  private def findNextRowWithNonNullInput(): Unit = {
+    while (skipNonNullCount < offset && inputIndex < input.length) {
+      val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+      if (!r.isNullAt(idx)) {
+        nextSelectedRow = r
+        skipNonNullCount += 1
+      }
+      inputIndex += 1
+    }
+  }
+
   override def prepare(rows: ExternalAppendOnlyUnsafeRowArray): Unit = {
     input = rows
     inputIterator = input.generateIterator()
     // drain the first few rows if offset is larger than zero
     inputIndex = 0
-    while (inputIndex < offset) {
-      if (inputIterator.hasNext) inputIterator.next()
-      inputIndex += 1
+    if (ignoreNulls) {
+      findNextRowWithNonNullInput
+    } else {
+      while (inputIndex < offset) {
+        if (inputIterator.hasNext) inputIterator.next()
+        inputIndex += 1
+      }
+      inputIndex = offset
     }
-    inputIndex = offset
+  }
+
+  private val doWrite = if (ignoreNulls && offset > 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lead(input, 2) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, next selected row -> y, output: y;
+    // 2. current row -> x, next selected row -> z, output: z;
+    // 3. current row -> null, next selected row -> z, output: z;
+    // 4. current row -> null, next selected row -> z, output: z;
+    // 5. current row -> y, next selected row -> empty, output: null;
+    // ... next selected row is empty, all following return null.
+    (current: InternalRow) =>
+      if (current.isNullAt(idx)) {
+        if (nextSelectedRow == EmptyRow) {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+        } else {
+          projection(nextSelectedRow)
+        }
+      } else {
+        skipNonNullCount -= 1
+        findNextRowWithNonNullInput
+        if (skipNonNullCount == offset) {
+          projection(nextSelectedRow)
+        } else {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+          nextSelectedRow = EmptyRow
+        }
+      }
+  } else if (ignoreNulls && offset < 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lag(input, 1) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, next selected row -> empty, output: null;
+    // 2. current row -> x, next selected row -> empty, output: null;
+    // 3. current row -> null, next selected row -> x, output: x;
+    // 4. current row -> null, next selected row -> x, output: x;
+    // 5. current row -> y, next selected row -> x, output: x;
+    // 6. current row -> null, next selected row -> y, output: y;
+    // 7. current row -> z, next selected row -> y, output: y;
+    // 8. current row -> z, next selected row -> z, output: z;
+    val absOffset = Math.abs(offset)
+    (current: InternalRow) =>
+      if (nextSelectedRow == EmptyRow && skipNonNullCount == absOffset) {

Review comment:
       We can implement the algorithm with 3 steps:
   1. find the next non-null when `skippedNonNullCount == absOffset`
   2. set the result row w.r.t. the `nextSelectedRow`
   3. check the current row to update `skippedNonNullCount`
   
   The code can be simplied
   ```
   // comment for step 1
   if (skipNonNullCount == absOffset) {
     nextSelectedRow = EmptyRow
     while (nextSelectedRow == EmptyRow && inputIndex < input.length) {
       ...
     }
   }
   // comment for step 2
   if (nextSelectedRow == EmptyRow) ... else ...
   // comment for step 3
   if (!current.isNullAt(idx)) {
     skipNonNullCount += 1
   }
   ```

##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**
+   *  The number of UnsafeRows skipped to get the next UnsafeRow where
+   *  the input operator by function is not null.
+   */
+  private var skipNonNullCount = 0
+
+  /** find the offset row whose input is not null */
+  private def findNextRowWithNonNullInput(): Unit = {
+    while (skipNonNullCount < offset && inputIndex < input.length) {
+      val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+      if (!r.isNullAt(idx)) {
+        nextSelectedRow = r
+        skipNonNullCount += 1
+      }
+      inputIndex += 1
+    }
+  }
+
   override def prepare(rows: ExternalAppendOnlyUnsafeRowArray): Unit = {
     input = rows
     inputIterator = input.generateIterator()
     // drain the first few rows if offset is larger than zero
     inputIndex = 0
-    while (inputIndex < offset) {
-      if (inputIterator.hasNext) inputIterator.next()
-      inputIndex += 1
+    if (ignoreNulls) {
+      findNextRowWithNonNullInput
+    } else {
+      while (inputIndex < offset) {
+        if (inputIterator.hasNext) inputIterator.next()
+        inputIndex += 1
+      }
+      inputIndex = offset
     }
-    inputIndex = offset
+  }
+
+  private val doWrite = if (ignoreNulls && offset > 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lead(input, 2) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, next selected row -> y, output: y;
+    // 2. current row -> x, next selected row -> z, output: z;
+    // 3. current row -> null, next selected row -> z, output: z;
+    // 4. current row -> null, next selected row -> z, output: z;
+    // 5. current row -> y, next selected row -> empty, output: null;
+    // ... next selected row is empty, all following return null.
+    (current: InternalRow) =>
+      if (current.isNullAt(idx)) {
+        if (nextSelectedRow == EmptyRow) {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+        } else {
+          projection(nextSelectedRow)
+        }
+      } else {
+        skipNonNullCount -= 1
+        findNextRowWithNonNullInput
+        if (skipNonNullCount == offset) {
+          projection(nextSelectedRow)
+        } else {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+          nextSelectedRow = EmptyRow
+        }
+      }
+  } else if (ignoreNulls && offset < 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lag(input, 1) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, next selected row -> empty, output: null;
+    // 2. current row -> x, next selected row -> empty, output: null;
+    // 3. current row -> null, next selected row -> x, output: x;
+    // 4. current row -> null, next selected row -> x, output: x;
+    // 5. current row -> y, next selected row -> x, output: x;
+    // 6. current row -> null, next selected row -> y, output: y;
+    // 7. current row -> z, next selected row -> y, output: y;
+    // 8. current row -> z, next selected row -> z, output: z;
+    val absOffset = Math.abs(offset)
+    (current: InternalRow) =>
+      if (nextSelectedRow == EmptyRow && skipNonNullCount == absOffset) {

Review comment:
       We can implement the algorithm with 3 steps:
   1. find the next non-null when `skippedNonNullCount == absOffset`
   2. set the result row w.r.t. the `nextSelectedRow`
   3. check the current row to update `skippedNonNullCount`
   
   The code can be simplified
   ```
   // comment for step 1
   if (skipNonNullCount == absOffset) {
     nextSelectedRow = EmptyRow
     while (nextSelectedRow == EmptyRow && inputIndex < input.length) {
       ...
     }
   }
   // comment for step 2
   if (nextSelectedRow == EmptyRow) ... else ...
   // comment for step 3
   if (!current.isNullAt(idx)) {
     skipNonNullCount += 1
   }
   ```




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-745774398


   Kubernetes integration test status failure
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37459/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547627102



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**
+   *  The number of UnsafeRows skipped to get the next UnsafeRow where
+   *  the input operator by function is not null.
+   */
+  private var skipNonNullCount = 0
+
+  /** find the offset row whose input is not null */
+  private def findNextRowWithNonNullInput(): Unit = {
+    while (skipNonNullCount < offset && inputIndex < input.length) {
+      val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+      if (!r.isNullAt(idx)) {
+        nextSelectedRow = r
+        skipNonNullCount += 1
+      }
+      inputIndex += 1
+    }
+  }
+
   override def prepare(rows: ExternalAppendOnlyUnsafeRowArray): Unit = {
     input = rows
     inputIterator = input.generateIterator()
     // drain the first few rows if offset is larger than zero
     inputIndex = 0
-    while (inputIndex < offset) {
-      if (inputIterator.hasNext) inputIterator.next()
-      inputIndex += 1
+    if (ignoreNulls) {
+      findNextRowWithNonNullInput
+    } else {
+      while (inputIndex < offset) {
+        if (inputIterator.hasNext) inputIterator.next()
+        inputIndex += 1
+      }
+      inputIndex = offset
     }
-    inputIndex = offset
+  }
+
+  private val doWrite = if (ignoreNulls && offset > 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lead(input, 2) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, next selected row -> y, output: y;
+    // 2. current row -> x, next selected row -> z, output: z;
+    // 3. current row -> null, next selected row -> z, output: z;
+    // 4. current row -> null, next selected row -> z, output: z;
+    // 5. current row -> y, next selected row -> empty, output: null;
+    // ... next selected row is empty, all following return null.
+    (current: InternalRow) =>
+      if (current.isNullAt(idx)) {

Review comment:
       OK




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r530151304



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/functions.scala
##########
@@ -938,8 +938,40 @@ object functions {
    * @group window_funcs
    * @since 1.4.0
    */
-  def lag(e: Column, offset: Int, defaultValue: Any): Column = withExpr {
-    Lag(e.expr, Literal(offset), Literal(defaultValue))
+  def lag(e: Column, offset: Int, defaultValue: Any): Column = {
+    lag(e, offset, defaultValue, false)
+  }
+
+  /**
+   * Window function: returns the value that is `offset` rows before the current row, and
+   * `defaultValue` if there is less than `offset` rows before the current row. `ignoreNulls`
+   * determines whether null values of row are included in or eliminated from the calculation.
+   * For example, an `offset` of one will return the previous row at any given point in the
+   * window partition.
+   *
+   * This is equivalent to the LAG function in SQL.
+   *
+   * @group window_funcs
+   * @since 3.1.0
+   */
+  def lag(columnName: String, offset: Int, defaultValue: Any, ignoreNulls: Boolean): Column = {

Review comment:
       cc @HyukjinKwon too.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750186103


   Kubernetes integration test status failure
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37886/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-731143377






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750744600


   retest this please


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750114958


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37889/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r526949881



##########
File path: sql/core/src/test/scala/org/apache/spark/sql/DataFrameWindowFunctionsSuite.scala
##########
@@ -700,6 +700,42 @@ class DataFrameWindowFunctionsSuite extends QueryTest
         Row("b", 3, null, null, null)))
   }
 
+  test("lead with ignoreNulls") {

Review comment:
       can we add SQL tests as well?




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-747308839


   **[Test build #132927 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/132927/testReport)** for PR 30387 at commit [`0d30d13`](https://github.com/apache/spark/commit/0d30d13d004828bcd570f29ab042f4b0b8472e79).
    * This patch passes all tests.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748791381


   **[Test build #133132 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133132/testReport)** for PR 30387 at commit [`8e9d7db`](https://github.com/apache/spark/commit/8e9d7db16d8fd85c637b9db32a7c6796cad28d9f).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-749976551


   **[Test build #133272 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133272/testReport)** for PR 30387 at commit [`4101603`](https://github.com/apache/spark/commit/4101603d92ea1ffe92e4205777c7fdb8492c2969).
    * This patch **fails to build**.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750143210


   retest this please


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-731143358


   Kubernetes integration test status success
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/36026/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r527569444



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowExecBase.scala
##########
@@ -136,8 +136,8 @@ trait WindowExecBase extends UnaryExecNode {
           val frame = spec.frameSpecification.asInstanceOf[SpecifiedWindowFrame]
           function match {
             case AggregateExpression(f, _, _, _, _) => collect("AGGREGATE", frame, e, f)
-            case f: FrameLessOffsetWindowFunction =>
-              collect("FRAME_LESS_OFFSET", f.fakeFrame, e, f)
+            case f: FrameLessOffsetWindowFunction => collect(
+              s"FRAME_LESS_OFFSET_${f.ignoreNulls}_${f.input.prettyName}", f.fakeFrame, e, f)

Review comment:
       Can't distinguish input completely according to index. Different window function may use the same input.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750159177


   Kubernetes integration test status failure
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37889/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547262205



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**
+   *  The number of UnsafeRows skipped to get the next UnsafeRow where
+   *  the input operator by function is not null.
+   */
+  private var skipNonNullCount = 0

Review comment:
       `skippedNonNullCount`




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748059738


   Kubernetes integration test status success
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37615/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-746810082


   **[Test build #132890 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/132890/testReport)** for PR 30387 at commit [`2023897`](https://github.com/apache/spark/commit/2023897982d2d852d0fd09c1939777c7740c621e).
    * This patch passes all tests.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-746355866


   **[Test build #132890 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/132890/testReport)** for PR 30387 at commit [`2023897`](https://github.com/apache/spark/commit/2023897982d2d852d0fd09c1939777c7740c621e).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750050459


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133290/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547621589



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head

Review comment:
       Because the child plan of `WindowExec` exists a project assign alias (e.g. w0) to `concat(value, key)`. So the `inputAttrs` is `[key, order, value, w0]`.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750268935


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37895/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730113741


   Merged build finished. Test FAILed.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750750932


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37928/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750133259


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133301/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748006387


   **[Test build #133014 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133014/testReport)** for PR 30387 at commit [`817012a`](https://github.com/apache/spark/commit/817012a1fd8fc9b041a92fcad8e866ab8a480617).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750014471


   **[Test build #133282 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133282/testReport)** for PR 30387 at commit [`df62967`](https://github.com/apache/spark/commit/df62967b6ef671101318c6dd2aae2f75639a9eaa).
    * This patch **fails Scala style tests**.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748949162


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133134/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750123680


   **[Test build #133301 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133301/testReport)** for PR 30387 at commit [`a06bacb`](https://github.com/apache/spark/commit/a06bacb35111cf7d0e9a36c340a516ea8069a0a1).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748025267


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37612/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748820059


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37731/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750737029


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133337/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-731155847


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/36029/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-746849126


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/132890/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-745957514


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/132857/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547623075



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**
+   *  The number of UnsafeRows skipped to get the next UnsafeRow where

Review comment:
       OK




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728906621






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-738754520


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/36820/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547621589



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head

Review comment:
       Because the child plan of `WindowExec` exists a project assign alias (e.g. w0) to `concat(value, key)`.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-738671613


   **[Test build #132220 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/132220/testReport)** for PR 30387 at commit [`3bff141`](https://github.com/apache/spark/commit/3bff141ce5a5d2ccee9e4b638f9d56b2e1390cff).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-747290360


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/132920/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730199405


   **[Test build #131328 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131328/testReport)** for PR 30387 at commit [`19faf6c`](https://github.com/apache/spark/commit/19faf6cfc5192f30b9b7ce9ad6f2d8639be331fa).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750014745


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133274/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730252058


   **[Test build #131339 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131339/testReport)** for PR 30387 at commit [`e189ed4`](https://github.com/apache/spark/commit/e189ed4ce1b33da7f877b6e61242866e85221a2f).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730490856






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-749987584






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730456301


   **[Test build #131337 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131337/testReport)** for PR 30387 at commit [`1c7b74d`](https://github.com/apache/spark/commit/1c7b74d801cf06d4d3f8b13c31e041da148970bb).
    * This patch passes all tests.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-746436277


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37492/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r544781188



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/functions.scala
##########
@@ -938,8 +938,40 @@ object functions {
    * @group window_funcs
    * @since 1.4.0
    */
-  def lag(e: Column, offset: Int, defaultValue: Any): Column = withExpr {
-    Lag(e.expr, Literal(offset), Literal(defaultValue))
+  def lag(e: Column, offset: Int, defaultValue: Any): Column = {
+    lag(e, offset, defaultValue, false)
+  }
+
+  /**
+   * Window function: returns the value that is `offset` rows before the current row, and
+   * `defaultValue` if there is less than `offset` rows before the current row. `ignoreNulls`
+   * determines whether null values of row are included in or eliminated from the calculation.
+   * For example, an `offset` of one will return the previous row at any given point in the
+   * window partition.
+   *
+   * This is equivalent to the LAG function in SQL.
+   *
+   * @group window_funcs
+   * @since 3.1.0

Review comment:
       OK




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730283155


   **[Test build #131344 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131344/testReport)** for PR 30387 at commit [`91f3917`](https://github.com/apache/spark/commit/91f3917f586d9dd896adf3837bfedab92aa8b633).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r535300627



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowExecBase.scala
##########
@@ -136,8 +136,8 @@ trait WindowExecBase extends UnaryExecNode {
           val frame = spec.frameSpecification.asInstanceOf[SpecifiedWindowFrame]
           function match {
             case AggregateExpression(f, _, _, _, _) => collect("AGGREGATE", frame, e, f)
-            case f: FrameLessOffsetWindowFunction =>
-              collect("FRAME_LESS_OFFSET", f.fakeFrame, e, f)
+            case f: FrameLessOffsetWindowFunction => collect(
+              s"FRAME_LESS_OFFSET_${f.ignoreNulls}_${f.input.prettyName}", f.fakeFrame, e, f)

Review comment:
       maybe we should do a refactor here to make the key more flexiable. I don't think `Expression.prettyName` can be used as a key reliably.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748182898


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133016/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547622745



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**

Review comment:
       OK




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547714735



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**
+   *  The number of UnsafeRows skipped to get the next UnsafeRow where
+   *  the input operator by function is not null.
+   */
+  private var skipNonNullCount = 0
+
+  /** find the offset row whose input is not null */
+  private def findNextRowWithNonNullInput(): Unit = {
+    while (skipNonNullCount < offset && inputIndex < input.length) {
+      val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+      if (!r.isNullAt(idx)) {
+        nextSelectedRow = r
+        skipNonNullCount += 1
+      }
+      inputIndex += 1
+    }
+  }
+
   override def prepare(rows: ExternalAppendOnlyUnsafeRowArray): Unit = {
     input = rows
     inputIterator = input.generateIterator()
     // drain the first few rows if offset is larger than zero
     inputIndex = 0
-    while (inputIndex < offset) {
-      if (inputIterator.hasNext) inputIterator.next()
-      inputIndex += 1
+    if (ignoreNulls) {
+      findNextRowWithNonNullInput
+    } else {
+      while (inputIndex < offset) {
+        if (inputIterator.hasNext) inputIterator.next()
+        inputIndex += 1
+      }
+      inputIndex = offset
     }
-    inputIndex = offset
+  }
+
+  private val doWrite = if (ignoreNulls && offset > 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lead(input, 2) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, next selected row -> y, output: y;
+    // 2. current row -> x, next selected row -> z, output: z;
+    // 3. current row -> null, next selected row -> z, output: z;
+    // 4. current row -> null, next selected row -> z, output: z;
+    // 5. current row -> y, next selected row -> empty, output: null;
+    // ... next selected row is empty, all following return null.
+    (current: InternalRow) =>
+      if (current.isNullAt(idx)) {
+        if (nextSelectedRow == EmptyRow) {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+        } else {
+          projection(nextSelectedRow)
+        }
+      } else {
+        skipNonNullCount -= 1
+        findNextRowWithNonNullInput
+        if (skipNonNullCount == offset) {
+          projection(nextSelectedRow)
+        } else {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+          nextSelectedRow = EmptyRow
+        }
+      }
+  } else if (ignoreNulls && offset < 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lag(input, 1) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, next selected row -> empty, output: null;
+    // 2. current row -> x, next selected row -> empty, output: null;
+    // 3. current row -> null, next selected row -> x, output: x;
+    // 4. current row -> null, next selected row -> x, output: x;
+    // 5. current row -> y, next selected row -> x, output: x;
+    // 6. current row -> null, next selected row -> y, output: y;
+    // 7. current row -> z, next selected row -> y, output: y;
+    // 8. current row -> z, next selected row -> z, output: z;
+    val absOffset = Math.abs(offset)
+    (current: InternalRow) =>
+      if (nextSelectedRow == EmptyRow && skipNonNullCount == absOffset) {

Review comment:
       Thank you




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748059753


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37615/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547262327



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**
+   *  The number of UnsafeRows skipped to get the next UnsafeRow where

Review comment:
       `UnsafeRows` -> `rows`




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-731276314


   **[Test build #131423 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131423/testReport)** for PR 30387 at commit [`dfdecd9`](https://github.com/apache/spark/commit/dfdecd9ca8d13da095fe675ddea12e752bf2cd35).
    * This patch passes all tests.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-746355866


   **[Test build #132890 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/132890/testReport)** for PR 30387 at commit [`2023897`](https://github.com/apache/spark/commit/2023897982d2d852d0fd09c1939777c7740c621e).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750772861


   **[Test build #133345 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133345/testReport)** for PR 30387 at commit [`4f18319`](https://github.com/apache/spark/commit/4f183190df6a324364b5f895acaacbfebc16b7ca).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728664988






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-738754520


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/36820/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r527610591



##########
File path: sql/core/src/test/scala/org/apache/spark/sql/DataFrameWindowFunctionsSuite.scala
##########
@@ -700,6 +700,42 @@ class DataFrameWindowFunctionsSuite extends QueryTest
         Row("b", 3, null, null, null)))
   }
 
+  test("lead with ignoreNulls") {

Review comment:
       I will open another PR to unify the support for `[IGNORE NULLS | RESPECT NULLS]` in the SQL parser.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730281558


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/35942/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728710223


   Kubernetes integration test status failure
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/35798/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748791381


   **[Test build #133132 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133132/testReport)** for PR 30387 at commit [`8e9d7db`](https://github.com/apache/spark/commit/8e9d7db16d8fd85c637b9db32a7c6796cad28d9f).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-746549154


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37492/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750045763






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750192947


   **[Test build #133304 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133304/testReport)** for PR 30387 at commit [`a06bacb`](https://github.com/apache/spark/commit/a06bacb35111cf7d0e9a36c340a516ea8069a0a1).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan closed pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan closed pull request #30387:
URL: https://github.com/apache/spark/pull/30387


   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748039135


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37612/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-746347913


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/132884/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-727910371


   Merged build finished. Test FAILed.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730113741






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728757573


   **[Test build #131195 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131195/testReport)** for PR 30387 at commit [`3658b51`](https://github.com/apache/spark/commit/3658b5138f653f3b8a9d3f12797604c688f6c528).
    * This patch **fails due to an unknown error code, -9**.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-731143377






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-727902241


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/35754/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-731098941


   **[Test build #131420 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131420/testReport)** for PR 30387 at commit [`41785dd`](https://github.com/apache/spark/commit/41785ddf9f1cba610a24e20ad6555580c79b877f).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-727918714


   Kubernetes integration test status failure
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/35754/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730379275


   Test FAILed.
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/131328/
   Test FAILed.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547714479



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**
+   *  The number of UnsafeRows skipped to get the next UnsafeRow where
+   *  the input operator by function is not null.
+   */
+  private var skipNonNullCount = 0
+
+  /** find the offset row whose input is not null */
+  private def findNextRowWithNonNullInput(): Unit = {
+    while (skipNonNullCount < offset && inputIndex < input.length) {
+      val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+      if (!r.isNullAt(idx)) {
+        nextSelectedRow = r
+        skipNonNullCount += 1
+      }
+      inputIndex += 1
+    }
+  }
+
   override def prepare(rows: ExternalAppendOnlyUnsafeRowArray): Unit = {
     input = rows
     inputIterator = input.generateIterator()
     // drain the first few rows if offset is larger than zero
     inputIndex = 0
-    while (inputIndex < offset) {
-      if (inputIterator.hasNext) inputIterator.next()
-      inputIndex += 1
+    if (ignoreNulls) {
+      findNextRowWithNonNullInput
+    } else {
+      while (inputIndex < offset) {
+        if (inputIterator.hasNext) inputIterator.next()
+        inputIndex += 1
+      }
+      inputIndex = offset
     }
-    inputIndex = offset
+  }
+
+  private val doWrite = if (ignoreNulls && offset > 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lead(input, 2) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, next selected row -> y, output: y;
+    // 2. current row -> x, next selected row -> z, output: z;
+    // 3. current row -> null, next selected row -> z, output: z;
+    // 4. current row -> null, next selected row -> z, output: z;
+    // 5. current row -> y, next selected row -> empty, output: null;
+    // ... next selected row is empty, all following return null.
+    (current: InternalRow) =>
+      if (current.isNullAt(idx)) {
+        if (nextSelectedRow == EmptyRow) {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+        } else {
+          projection(nextSelectedRow)
+        }
+      } else {
+        skipNonNullCount -= 1
+        findNextRowWithNonNullInput
+        if (skipNonNullCount == offset) {
+          projection(nextSelectedRow)
+        } else {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+          nextSelectedRow = EmptyRow
+        }
+      }
+  } else if (ignoreNulls && offset < 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lag(input, 1) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, next selected row -> empty, output: null;
+    // 2. current row -> x, next selected row -> empty, output: null;
+    // 3. current row -> null, next selected row -> x, output: x;
+    // 4. current row -> null, next selected row -> x, output: x;
+    // 5. current row -> y, next selected row -> x, output: x;
+    // 6. current row -> null, next selected row -> y, output: y;
+    // 7. current row -> z, next selected row -> y, output: y;
+    // 8. current row -> z, next selected row -> z, output: z;
+    val absOffset = Math.abs(offset)
+    (current: InternalRow) =>
+      if (nextSelectedRow == EmptyRow && skipNonNullCount == absOffset) {
+        do {
+          val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+          if (!r.isNullAt(idx)) {
+            nextSelectedRow = r
+          }
+          inputIndex += 1
+        } while (nextSelectedRow == EmptyRow && inputIndex < input.length)
+      }
+      if (nextSelectedRow == EmptyRow) {
+        // Use default values since the offset row whose input value is not null does not exist.
+        fillDefaultValue(current)
+      } else {
+        projection(nextSelectedRow)
+      }
+      if (!current.isNullAt(idx)) {
+        if (skipNonNullCount < absOffset) {

Review comment:
       The original idea is that as long as skipNonNullCount is increased to absOffset, it will no longer increase




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547622869



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**
+   *  The number of UnsafeRows skipped to get the next UnsafeRow where
+   *  the input operator by function is not null.
+   */
+  private var skipNonNullCount = 0

Review comment:
       OK




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750726244


   **[Test build #133337 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133337/testReport)** for PR 30387 at commit [`0cd9bcc`](https://github.com/apache/spark/commit/0cd9bcce7475c2b189be32c0209c0ff017a0736e).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730283155


   **[Test build #131344 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131344/testReport)** for PR 30387 at commit [`91f3917`](https://github.com/apache/spark/commit/91f3917f586d9dd896adf3837bfedab92aa8b633).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750846326


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133345/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750013335


   **[Test build #133282 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133282/testReport)** for PR 30387 at commit [`df62967`](https://github.com/apache/spark/commit/df62967b6ef671101318c6dd2aae2f75639a9eaa).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-731109853


   **[Test build #131423 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131423/testReport)** for PR 30387 at commit [`dfdecd9`](https://github.com/apache/spark/commit/dfdecd9ca8d13da095fe675ddea12e752bf2cd35).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-747209940






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750194837


   **[Test build #133307 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133307/testReport)** for PR 30387 at commit [`a06bacb`](https://github.com/apache/spark/commit/a06bacb35111cf7d0e9a36c340a516ea8069a0a1).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] HyukjinKwon commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
HyukjinKwon commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r544762558



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/functions.scala
##########
@@ -938,8 +938,40 @@ object functions {
    * @group window_funcs
    * @since 1.4.0
    */
-  def lag(e: Column, offset: Int, defaultValue: Any): Column = withExpr {
-    Lag(e.expr, Literal(offset), Literal(defaultValue))
+  def lag(e: Column, offset: Int, defaultValue: Any): Column = {
+    lag(e, offset, defaultValue, false)
+  }
+
+  /**
+   * Window function: returns the value that is `offset` rows before the current row, and
+   * `defaultValue` if there is less than `offset` rows before the current row. `ignoreNulls`
+   * determines whether null values of row are included in or eliminated from the calculation.
+   * For example, an `offset` of one will return the previous row at any given point in the
+   * window partition.
+   *
+   * This is equivalent to the LAG function in SQL.
+   *
+   * @group window_funcs
+   * @since 3.1.0

Review comment:
       BTW, let's target 3.2.0




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r548391607



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -153,26 +153,24 @@ class FrameLessOffsetWindowFunctionFrame(
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
-  assert(expressions.toSeq.filterNot(_.input.isInstanceOf[Attribute]).isEmpty)
-
-  /** The input expression of Lead/Lag. */
-  private lazy val inputExpression = expressions.toSeq.map(_.input).head
-
-  /** The index of input expression in the row. */
-  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
-
   /** Holder the UnsafeRow where the input operator by function is not null. */
   private var nextSelectedRow = EmptyRow
 
   // The number of rows skipped to get the next UnsafeRow where the input operator by function
   // is not null.
   private var skippedNonNullCount = 0
 
+  /** Create the projection to determine whether input is null. */
+  private val project = UnsafeProjection.create(Seq(IsNull(expressions.head.input)), inputSchema)
+
+  /** Check if the output value of the first index is null. */
+  private val nullCheck: InternalRow => Boolean = row => project(row).getBoolean(0)

Review comment:
       this can be a def
   ```
   private def nullCheck(row: InternalRow): Boolean = project(row).getBoolean(0)
   ```




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-749991501






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750014484


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133282/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-745787680


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37459/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728757993


   Merged build finished. Test FAILed.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-738729050


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/36820/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730092986


   **[Test build #131312 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131312/testReport)** for PR 30387 at commit [`19faf6c`](https://github.com/apache/spark/commit/19faf6cfc5192f30b9b7ce9ad6f2d8639be331fa).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-747208521


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37530/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728700320


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/35798/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748880526


   Kubernetes integration test status success
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37733/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-731132943


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/36026/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728685444


   **[Test build #131195 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131195/testReport)** for PR 30387 at commit [`3658b51`](https://github.com/apache/spark/commit/3658b5138f653f3b8a9d3f12797604c688f6c528).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-746549154


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37492/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-738741637


   Kubernetes integration test status failure
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/36820/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730243472


   Test FAILed.
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/35932/
   Test FAILed.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730107192


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/35915/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750735642


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37928/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730294410


   Kubernetes integration test status failure
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/35942/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730243460






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748820038


   Kubernetes integration test status success
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37731/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r526945979



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowExecBase.scala
##########
@@ -136,8 +136,8 @@ trait WindowExecBase extends UnaryExecNode {
           val frame = spec.frameSpecification.asInstanceOf[SpecifiedWindowFrame]
           function match {
             case AggregateExpression(f, _, _, _, _) => collect("AGGREGATE", frame, e, f)
-            case f: FrameLessOffsetWindowFunction =>
-              collect("FRAME_LESS_OFFSET", f.fakeFrame, e, f)
+            case f: FrameLessOffsetWindowFunction => collect(
+              s"FRAME_LESS_OFFSET_${f.ignoreNulls}_${f.input.prettyName}", f.fakeFrame, e, f)

Review comment:
       `prettyName` can be very long. Can we use the index of the input?




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748941511


   **[Test build #133134 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133134/testReport)** for PR 30387 at commit [`0b67ee4`](https://github.com/apache/spark/commit/0b67ee4e8835586ab12f6c0f00f37a880ca14c87).
    * This patch passes all tests.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r543470019



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/functions.scala
##########
@@ -938,8 +938,40 @@ object functions {
    * @group window_funcs
    * @since 1.4.0
    */
-  def lag(e: Column, offset: Int, defaultValue: Any): Column = withExpr {
-    Lag(e.expr, Literal(offset), Literal(defaultValue))
+  def lag(e: Column, offset: Int, defaultValue: Any): Column = {
+    lag(e, offset, defaultValue, false)
+  }
+
+  /**
+   * Window function: returns the value that is `offset` rows before the current row, and
+   * `defaultValue` if there is less than `offset` rows before the current row. `ignoreNulls`
+   * determines whether null values of row are included in or eliminated from the calculation.
+   * For example, an `offset` of one will return the previous row at any given point in the
+   * window partition.
+   *
+   * This is equivalent to the LAG function in SQL.
+   *
+   * @group window_funcs
+   * @since 3.1.0
+   */
+  def lag(columnName: String, offset: Int, defaultValue: Any, ignoreNulls: Boolean): Column = {

Review comment:
       Let's drop it for now. We can always add it when there are requests.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750845798


   **[Test build #133345 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133345/testReport)** for PR 30387 at commit [`4f18319`](https://github.com/apache/spark/commit/4f183190df6a324364b5f895acaacbfebc16b7ca).
    * This patch passes all tests.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750726244


   **[Test build #133337 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133337/testReport)** for PR 30387 at commit [`0cd9bcc`](https://github.com/apache/spark/commit/0cd9bcce7475c2b189be32c0209c0ff017a0736e).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748779168


   retest this please


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-745752177


   **[Test build #132857 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/132857/testReport)** for PR 30387 at commit [`4187d41`](https://github.com/apache/spark/commit/4187d41c69b525549a1f1e53fa770411dad83cab).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-749973781


   **[Test build #133272 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133272/testReport)** for PR 30387 at commit [`4101603`](https://github.com/apache/spark/commit/4101603d92ea1ffe92e4205777c7fdb8492c2969).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750056656


   **[Test build #133292 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133292/testReport)** for PR 30387 at commit [`8976bca`](https://github.com/apache/spark/commit/8976bcabc3f41a3bc4330ec3bfbb4471db56e39a).
    * This patch **fails to build**.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-747209125


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37523/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750133182


   **[Test build #133301 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133301/testReport)** for PR 30387 at commit [`a06bacb`](https://github.com/apache/spark/commit/a06bacb35111cf7d0e9a36c340a516ea8069a0a1).
    * This patch **fails to build**.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r543469641



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +150,115 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Cache some UnsafeRow that will be used many times. */
+  private val rowBuffer = new ArrayBuffer[UnsafeRow]
+
   override def prepare(rows: ExternalAppendOnlyUnsafeRowArray): Unit = {
     input = rows
     inputIterator = input.generateIterator()
     // drain the first few rows if offset is larger than zero
     inputIndex = 0
-    while (inputIndex < offset) {
-      if (inputIterator.hasNext) inputIterator.next()
-      inputIndex += 1
+    if (!ignoreNulls || offset < 0) {
+      while (inputIndex < offset) {
+        if (inputIterator.hasNext) inputIterator.next()
+        inputIndex += 1
+      }
+      inputIndex = offset
     }
-    inputIndex = offset
+  }
+
+  private val doWrite = if (ignoreNulls && offset > 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, y, null, z, null.
+    // We use Lead(input, 1) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, row buffer: [x], output: x;
+    // 2. current row -> x, row buffer: [null, y], output: y;
+    // 3. current row -> null, row buffer: [y], output: y;
+    // 4. current row -> y, row buffer: [null, z], output: z;
+    // 5. current row -> null, row buffer: [z], output: z;
+    // 6. current row -> z, row buffer: [null], output: null;
+    // 7. current row -> null, row buffer: [], output: null;
+    (index: Int, current: InternalRow) =>
+      while (inputIndex <= index) {
+        if (inputIterator.hasNext) inputIterator.next()
+        inputIndex += 1
+      }
+      while (rowBuffer.filterNot(_ == null).size < offset && inputIndex < input.length) {
+        val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+        if (r.isNullAt(idx)) {
+          rowBuffer += null
+        } else {
+          rowBuffer += r
+        }
+        inputIndex += 1
+      }
+      if (rowBuffer.filterNot(_ == null).size == offset) {
+        projection(rowBuffer.filterNot(_ == null).last)
+        rowBuffer.remove(0)
+      } else {
+        // Use default values since the offset row whose input value is not null does not exist.
+        fillDefaultValue(current)
+      }
+  } else if (ignoreNulls && offset < 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, y, null, z, null.
+    // We use Lag(input, 1) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, row buffer: [], output: null;
+    // 2. current row -> x, row buffer: [], output: null;
+    // 3. current row -> null, row buffer: [x], output: x;
+    // 4. current row -> y, row buffer: [x], output: x;
+    // 5. current row -> null, row buffer: [y], output: y;
+    // 6. current row -> z, row buffer: [y], output: y;
+    // 7. current row -> null, row buffer: [z], output: z;
+    val maxSize = Math.abs(offset)
+    (index: Int, current: InternalRow) =>
+      if (inputIndex >= 0 && inputIndex < input.length) {
+        while (inputIndex < index) {
+          val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+          if (!r.isNullAt(idx)) {
+            if (rowBuffer.size == maxSize) {
+              rowBuffer.remove(0)
+            }
+            rowBuffer += r
+          }
+          inputIndex += 1
+        }
+        if (rowBuffer.size == maxSize) {
+          projection(rowBuffer.head)
+        } else {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+        }
+      } else {
+        // Use default values since the offset row does not exist.
+        fillDefaultValue(current)
+        inputIndex += 1
+      }
+  } else {

Review comment:
       what's the behavior if `offset == 0`?




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750735803


   **[Test build #133337 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133337/testReport)** for PR 30387 at commit [`0cd9bcc`](https://github.com/apache/spark/commit/0cd9bcce7475c2b189be32c0209c0ff017a0736e).
    * This patch **fails Spark unit tests**.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r544088225



##########
File path: sql/core/src/test/scala/org/apache/spark/sql/DataFrameWindowFunctionsSuite.scala
##########
@@ -700,6 +700,44 @@ class DataFrameWindowFunctionsSuite extends QueryTest
         Row("b", 3, null, null, null)))
   }
 
+  test("lead/lag with ignoreNulls") {
+    val nullStr: String = null
+    val df = Seq(
+      ("a", 0, nullStr),
+      ("a", 1, "x"),
+      ("a", 2, "y"),
+      ("a", 3, "z"),
+      ("a", 4, nullStr),
+      ("b", 1, nullStr),
+      ("b", 2, nullStr)).
+      toDF("key", "order", "value")
+    val window = Window.orderBy($"order")
+    checkAnswer(
+      df.select(
+        $"key",
+        $"value",
+        $"order",
+        lead($"value", 1).over(window),

Review comment:
       how about something like `value + 1`, `value + order` that are not simple attributes?




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-747312209


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/132927/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547263148



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**
+   *  The number of UnsafeRows skipped to get the next UnsafeRow where
+   *  the input operator by function is not null.
+   */
+  private var skipNonNullCount = 0
+
+  /** find the offset row whose input is not null */
+  private def findNextRowWithNonNullInput(): Unit = {
+    while (skipNonNullCount < offset && inputIndex < input.length) {
+      val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+      if (!r.isNullAt(idx)) {
+        nextSelectedRow = r
+        skipNonNullCount += 1
+      }
+      inputIndex += 1
+    }
+  }
+
   override def prepare(rows: ExternalAppendOnlyUnsafeRowArray): Unit = {
     input = rows
     inputIterator = input.generateIterator()
     // drain the first few rows if offset is larger than zero
     inputIndex = 0
-    while (inputIndex < offset) {
-      if (inputIterator.hasNext) inputIterator.next()
-      inputIndex += 1
+    if (ignoreNulls) {
+      findNextRowWithNonNullInput
+    } else {
+      while (inputIndex < offset) {
+        if (inputIterator.hasNext) inputIterator.next()
+        inputIndex += 1
+      }
+      inputIndex = offset
     }
-    inputIndex = offset
+  }
+
+  private val doWrite = if (ignoreNulls && offset > 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lead(input, 2) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, next selected row -> y, output: y;
+    // 2. current row -> x, next selected row -> z, output: z;
+    // 3. current row -> null, next selected row -> z, output: z;
+    // 4. current row -> null, next selected row -> z, output: z;
+    // 5. current row -> y, next selected row -> empty, output: null;
+    // ... next selected row is empty, all following return null.
+    (current: InternalRow) =>
+      if (current.isNullAt(idx)) {

Review comment:
       We can even skip the null check if the next selected row is already `EmptyRow`.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728768325


   retest this please


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748039135






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750765191


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37931/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728757993






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728681824


   Test FAILed.
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/131188/
   Test FAILed.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r544781741



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +150,115 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Cache some UnsafeRow that will be used many times. */
+  private val rowBuffer = new ArrayBuffer[UnsafeRow]
+
   override def prepare(rows: ExternalAppendOnlyUnsafeRowArray): Unit = {
     input = rows
     inputIterator = input.generateIterator()
     // drain the first few rows if offset is larger than zero
     inputIndex = 0
-    while (inputIndex < offset) {
-      if (inputIterator.hasNext) inputIterator.next()
-      inputIndex += 1
+    if (!ignoreNulls || offset < 0) {
+      while (inputIndex < offset) {
+        if (inputIterator.hasNext) inputIterator.next()
+        inputIndex += 1
+      }
+      inputIndex = offset
     }
-    inputIndex = offset
+  }
+
+  private val doWrite = if (ignoreNulls && offset > 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, y, null, z, null.
+    // We use Lead(input, 1) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, row buffer: [x], output: x;
+    // 2. current row -> x, row buffer: [null, y], output: y;
+    // 3. current row -> null, row buffer: [y], output: y;
+    // 4. current row -> y, row buffer: [null, z], output: z;
+    // 5. current row -> null, row buffer: [z], output: z;
+    // 6. current row -> z, row buffer: [null], output: null;
+    // 7. current row -> null, row buffer: [], output: null;
+    (index: Int, current: InternalRow) =>
+      while (inputIndex <= index) {
+        if (inputIterator.hasNext) inputIterator.next()
+        inputIndex += 1
+      }
+      while (rowBuffer.filterNot(_ == null).size < offset && inputIndex < input.length) {
+        val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+        if (r.isNullAt(idx)) {
+          rowBuffer += null
+        } else {
+          rowBuffer += r
+        }
+        inputIndex += 1
+      }
+      if (rowBuffer.filterNot(_ == null).size == offset) {
+        projection(rowBuffer.filterNot(_ == null).last)
+        rowBuffer.remove(0)
+      } else {
+        // Use default values since the offset row whose input value is not null does not exist.
+        fillDefaultValue(current)
+      }
+  } else if (ignoreNulls && offset < 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, y, null, z, null.
+    // We use Lag(input, 1) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, row buffer: [], output: null;
+    // 2. current row -> x, row buffer: [], output: null;
+    // 3. current row -> null, row buffer: [x], output: x;
+    // 4. current row -> y, row buffer: [x], output: x;
+    // 5. current row -> null, row buffer: [y], output: y;
+    // 6. current row -> z, row buffer: [y], output: y;
+    // 7. current row -> null, row buffer: [z], output: z;
+    val maxSize = Math.abs(offset)
+    (index: Int, current: InternalRow) =>
+      if (inputIndex >= 0 && inputIndex < input.length) {
+        while (inputIndex < index) {
+          val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+          if (!r.isNullAt(idx)) {
+            if (rowBuffer.size == maxSize) {
+              rowBuffer.remove(0)
+            }
+            rowBuffer += r
+          }
+          inputIndex += 1
+        }
+        if (rowBuffer.size == maxSize) {
+          projection(rowBuffer.head)
+        } else {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+        }
+      } else {
+        // Use default values since the offset row does not exist.
+        fillDefaultValue(current)
+        inputIndex += 1
+      }
+  } else {

Review comment:
       Return current row itself. 




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750133259


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133301/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748880547


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37733/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-747968289


   **[Test build #133009 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133009/testReport)** for PR 30387 at commit [`87d6c47`](https://github.com/apache/spark/commit/87d6c477025b401e5d177faa4a1bb04c19d00da6).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750329683


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133307/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750188094






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-727910371






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r548399696



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -153,26 +153,24 @@ class FrameLessOffsetWindowFunctionFrame(
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
-  assert(expressions.toSeq.filterNot(_.input.isInstanceOf[Attribute]).isEmpty)
-
-  /** The input expression of Lead/Lag. */
-  private lazy val inputExpression = expressions.toSeq.map(_.input).head
-
-  /** The index of input expression in the row. */
-  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
-
   /** Holder the UnsafeRow where the input operator by function is not null. */
   private var nextSelectedRow = EmptyRow
 
   // The number of rows skipped to get the next UnsafeRow where the input operator by function
   // is not null.
   private var skippedNonNullCount = 0
 
+  /** Create the projection to determine whether input is null. */
+  private val project = UnsafeProjection.create(Seq(IsNull(expressions.head.input)), inputSchema)
+
+  /** Check if the output value of the first index is null. */
+  private val nullCheck: InternalRow => Boolean = row => project(row).getBoolean(0)

Review comment:
       OK




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750056719


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133292/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730379264


   Merged build finished. Test FAILed.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r527610591



##########
File path: sql/core/src/test/scala/org/apache/spark/sql/DataFrameWindowFunctionsSuite.scala
##########
@@ -700,6 +700,42 @@ class DataFrameWindowFunctionsSuite extends QueryTest
         Row("b", 3, null, null, null)))
   }
 
+  test("lead with ignoreNulls") {

Review comment:
       I will consider it and unify the support for [IGNORE NULLS | RESPECT NULLS] in the SQL parser.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r543467066



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowExecBase.scala
##########
@@ -116,13 +116,17 @@ trait WindowExecBase extends UnaryExecNode {
    * [[WindowExpression]]s and factory function for the [[WindowFrameFunction]].
    */
   protected lazy val windowFrameExpressionFactoryPairs = {
-    type FrameKey = (String, FrameType, Expression, Expression)
+    type FrameKey = (String, FrameType, Expression, Expression, Expression)
     type ExpressionBuffer = mutable.Buffer[Expression]
     val framedFunctions = mutable.Map.empty[FrameKey, (ExpressionBuffer, ExpressionBuffer)]
 
     // Add a function and its function to the map for a given frame.
     def collect(tpe: String, fr: SpecifiedWindowFrame, e: Expression, fn: Expression): Unit = {
-      val key = (tpe, fr.frameType, fr.lower, fr.upper)
+      val key = fn match {
+        case f: FrameLessOffsetWindowFunction if f.ignoreNulls =>
+          (tpe, fr.frameType, fr.lower, fr.upper, f.canonicalized)

Review comment:
       And it's more precise to put `f.children.map(_.canonicalized)` as the key.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750041897






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750352990


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133304/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-746849126


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/132890/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750775294


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37931/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750750932


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37928/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750737029


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133337/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750807617


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133340/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547933400



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,134 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  assert(expressions.toSeq.filterNot(_.input.isInstanceOf[Attribute]).isEmpty)

Review comment:
       `assert(expressions.forall(_.input.isInstanceOf[Attribute]))`




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730194190






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730199026


   retest this please


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728819074


   Kubernetes integration test status success
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/35813/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r545000493



##########
File path: sql/core/src/test/scala/org/apache/spark/sql/DataFrameWindowFunctionsSuite.scala
##########
@@ -700,6 +700,44 @@ class DataFrameWindowFunctionsSuite extends QueryTest
         Row("b", 3, null, null, null)))
   }
 
+  test("lead/lag with ignoreNulls") {
+    val nullStr: String = null
+    val df = Seq(
+      ("a", 0, nullStr),
+      ("a", 1, "x"),
+      ("a", 2, "y"),
+      ("a", 3, "z"),
+      ("a", 4, nullStr),
+      ("b", 1, nullStr),
+      ("b", 2, nullStr)).
+      toDF("key", "order", "value")
+    val window = Window.orderBy($"order")
+    checkAnswer(
+      df.select(
+        $"key",
+        $"value",
+        $"order",
+        lead($"value", 1).over(window),

Review comment:
       The current implement of dsl only accept column.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748163704


   **[Test build #133016 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133016/testReport)** for PR 30387 at commit [`8e9d7db`](https://github.com/apache/spark/commit/8e9d7db16d8fd85c637b9db32a7c6796cad28d9f).
    * This patch **fails SparkR unit tests**.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547260121



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**
+   *  The number of UnsafeRows skipped to get the next UnsafeRow where
+   *  the input operator by function is not null.
+   */
+  private var skipNonNullCount = 0
+
+  /** find the offset row whose input is not null */
+  private def findNextRowWithNonNullInput(): Unit = {
+    while (skipNonNullCount < offset && inputIndex < input.length) {
+      val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+      if (!r.isNullAt(idx)) {
+        nextSelectedRow = r
+        skipNonNullCount += 1
+      }
+      inputIndex += 1
+    }
+  }
+
   override def prepare(rows: ExternalAppendOnlyUnsafeRowArray): Unit = {
     input = rows
     inputIterator = input.generateIterator()
     // drain the first few rows if offset is larger than zero
     inputIndex = 0
-    while (inputIndex < offset) {
-      if (inputIterator.hasNext) inputIterator.next()
-      inputIndex += 1
+    if (ignoreNulls) {
+      findNextRowWithNonNullInput
+    } else {
+      while (inputIndex < offset) {
+        if (inputIterator.hasNext) inputIterator.next()
+        inputIndex += 1
+      }
+      inputIndex = offset
     }
-    inputIndex = offset
+  }
+
+  private val doWrite = if (ignoreNulls && offset > 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lead(input, 2) with IGNORE NULLS and the process is as follows:

Review comment:
       nit: `Lead`  -> `lead`




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730276134


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/35941/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-745957514


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/132857/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-731277738






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-731109853


   **[Test build #131423 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131423/testReport)** for PR 30387 at commit [`dfdecd9`](https://github.com/apache/spark/commit/dfdecd9ca8d13da095fe675ddea12e752bf2cd35).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-745787680


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37459/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-738809907


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/132220/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748003287


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37608/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547259580



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head

Review comment:
       I'm a bit surprised that this works. Let's say the `inputAttrs` is `[key, order, value]`, and the `inputExpression` is `concat(value, key)`. How does this work?




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728681818






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750174492


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37895/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728710242


   Merged build finished. Test FAILed.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547261791



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**

Review comment:
       nit: let's not use javadoc syntax for private variables.
   ```
   // multi
   // line
   // comment
   ```




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728646966


   **[Test build #131188 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131188/testReport)** for PR 30387 at commit [`3658b51`](https://github.com/apache/spark/commit/3658b5138f653f3b8a9d3f12797604c688f6c528).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748807406


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37731/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728769710


   **[Test build #131210 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131210/testReport)** for PR 30387 at commit [`be70bbe`](https://github.com/apache/spark/commit/be70bbe4a02a1523ca32fbd234c803b696bd7313).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730292206


   Kubernetes integration test status success
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/35941/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-731098941


   **[Test build #131420 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131420/testReport)** for PR 30387 at commit [`41785dd`](https://github.com/apache/spark/commit/41785ddf9f1cba610a24e20ad6555580c79b877f).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547263352



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**
+   *  The number of UnsafeRows skipped to get the next UnsafeRow where
+   *  the input operator by function is not null.
+   */
+  private var skipNonNullCount = 0
+
+  /** find the offset row whose input is not null */
+  private def findNextRowWithNonNullInput(): Unit = {
+    while (skipNonNullCount < offset && inputIndex < input.length) {
+      val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+      if (!r.isNullAt(idx)) {
+        nextSelectedRow = r
+        skipNonNullCount += 1
+      }
+      inputIndex += 1
+    }
+  }
+
   override def prepare(rows: ExternalAppendOnlyUnsafeRowArray): Unit = {
     input = rows
     inputIterator = input.generateIterator()
     // drain the first few rows if offset is larger than zero
     inputIndex = 0
-    while (inputIndex < offset) {
-      if (inputIterator.hasNext) inputIterator.next()
-      inputIndex += 1
+    if (ignoreNulls) {
+      findNextRowWithNonNullInput
+    } else {
+      while (inputIndex < offset) {
+        if (inputIterator.hasNext) inputIterator.next()
+        inputIndex += 1
+      }
+      inputIndex = offset
     }
-    inputIndex = offset
+  }
+
+  private val doWrite = if (ignoreNulls && offset > 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lead(input, 2) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, next selected row -> y, output: y;
+    // 2. current row -> x, next selected row -> z, output: z;
+    // 3. current row -> null, next selected row -> z, output: z;
+    // 4. current row -> null, next selected row -> z, output: z;
+    // 5. current row -> y, next selected row -> empty, output: null;
+    // ... next selected row is empty, all following return null.
+    (current: InternalRow) =>
+      if (current.isNullAt(idx)) {
+        if (nextSelectedRow == EmptyRow) {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+        } else {
+          projection(nextSelectedRow)
+        }
+      } else {
+        skipNonNullCount -= 1
+        findNextRowWithNonNullInput
+        if (skipNonNullCount == offset) {
+          projection(nextSelectedRow)
+        } else {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+          nextSelectedRow = EmptyRow
+        }
+      }
+  } else if (ignoreNulls && offset < 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lag(input, 1) with IGNORE NULLS and the process is as follows:

Review comment:
       ditto `Lag` -> `lag`




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730243460


   Merged build finished. Test FAILed.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750227859


   Kubernetes integration test status failure
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37895/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-749983136


   retest this please


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750106237


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37886/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-746173752


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37486/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-746346282


   **[Test build #132884 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/132884/testReport)** for PR 30387 at commit [`2023897`](https://github.com/apache/spark/commit/2023897982d2d852d0fd09c1939777c7740c621e).
    * This patch **fails Spark unit tests**.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r543468304



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +150,115 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Cache some UnsafeRow that will be used many times. */
+  private val rowBuffer = new ArrayBuffer[UnsafeRow]

Review comment:
       instead of having a buffer, shall we just store the next non-null input and its index?




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728681818


   Merged build finished. Test FAILed.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750839454


   @cloud-fan Thanks for your great suggestion.
   @HyukjinKwon Thanks for your comment.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-727918731


   Merged build finished. Test FAILed.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748030091


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37608/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748129508


   **[Test build #133014 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133014/testReport)** for PR 30387 at commit [`817012a`](https://github.com/apache/spark/commit/817012a1fd8fc9b041a92fcad8e866ab8a480617).
    * This patch passes all tests.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748182898


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133016/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730354037


   Kubernetes integration test status success
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/35948/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748006387


   **[Test build #133014 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133014/testReport)** for PR 30387 at commit [`817012a`](https://github.com/apache/spark/commit/817012a1fd8fc9b041a92fcad8e866ab8a480617).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-747971286


   **[Test build #133009 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133009/testReport)** for PR 30387 at commit [`87d6c47`](https://github.com/apache/spark/commit/87d6c477025b401e5d177faa4a1bb04c19d00da6).
    * This patch **fails to build**.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750123680


   **[Test build #133301 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133301/testReport)** for PR 30387 at commit [`a06bacb`](https://github.com/apache/spark/commit/a06bacb35111cf7d0e9a36c340a516ea8069a0a1).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-747209940


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37530/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748836487


   **[Test build #133132 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133132/testReport)** for PR 30387 at commit [`8e9d7db`](https://github.com/apache/spark/commit/8e9d7db16d8fd85c637b9db32a7c6796cad28d9f).
    * This patch **fails Spark unit tests**.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730294428






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728664982


   Kubernetes integration test status success
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/35790/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750041897


   **[Test build #133286 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133286/testReport)** for PR 30387 at commit [`a34844a`](https://github.com/apache/spark/commit/a34844a10e12761549e7417cedf4ccc9978f9657).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730448421






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728769710


   **[Test build #131210 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131210/testReport)** for PR 30387 at commit [`be70bbe`](https://github.com/apache/spark/commit/be70bbe4a02a1523ca32fbd234c803b696bd7313).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-731169940


   Kubernetes integration test status failure
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/36029/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r543466546



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowExecBase.scala
##########
@@ -116,13 +116,17 @@ trait WindowExecBase extends UnaryExecNode {
    * [[WindowExpression]]s and factory function for the [[WindowFrameFunction]].
    */
   protected lazy val windowFrameExpressionFactoryPairs = {
-    type FrameKey = (String, FrameType, Expression, Expression)
+    type FrameKey = (String, FrameType, Expression, Expression, Expression)
     type ExpressionBuffer = mutable.Buffer[Expression]
     val framedFunctions = mutable.Map.empty[FrameKey, (ExpressionBuffer, ExpressionBuffer)]
 
     // Add a function and its function to the map for a given frame.
     def collect(tpe: String, fr: SpecifiedWindowFrame, e: Expression, fn: Expression): Unit = {
-      val key = (tpe, fr.frameType, fr.lower, fr.upper)
+      val key = fn match {
+        case f: FrameLessOffsetWindowFunction if f.ignoreNulls =>
+          (tpe, fr.frameType, fr.lower, fr.upper, f.canonicalized)

Review comment:
       We should add some comment to explain, why only `ignoreNulls = true` requires us to put the children of the function as the key.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750268935


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37895/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730243377


   Kubernetes integration test status failure
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/35932/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730354055






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r544780768



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/functions.scala
##########
@@ -938,8 +938,40 @@ object functions {
    * @group window_funcs
    * @since 1.4.0
    */
-  def lag(e: Column, offset: Int, defaultValue: Any): Column = withExpr {
-    Lag(e.expr, Literal(offset), Literal(defaultValue))
+  def lag(e: Column, offset: Int, defaultValue: Any): Column = {
+    lag(e, offset, defaultValue, false)
+  }
+
+  /**
+   * Window function: returns the value that is `offset` rows before the current row, and
+   * `defaultValue` if there is less than `offset` rows before the current row. `ignoreNulls`
+   * determines whether null values of row are included in or eliminated from the calculation.
+   * For example, an `offset` of one will return the previous row at any given point in the
+   * window partition.
+   *
+   * This is equivalent to the LAG function in SQL.
+   *
+   * @group window_funcs
+   * @since 3.1.0
+   */
+  def lag(columnName: String, offset: Int, defaultValue: Any, ignoreNulls: Boolean): Column = {

Review comment:
       OK




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728805808


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/35813/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728819094






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r544081471



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowExecBase.scala
##########
@@ -116,13 +116,21 @@ trait WindowExecBase extends UnaryExecNode {
    * [[WindowExpression]]s and factory function for the [[WindowFrameFunction]].
    */
   protected lazy val windowFrameExpressionFactoryPairs = {
-    type FrameKey = (String, FrameType, Expression, Expression)
+    type FrameKey = (String, FrameType, Expression, Expression, Seq[Expression])
     type ExpressionBuffer = mutable.Buffer[Expression]
     val framedFunctions = mutable.Map.empty[FrameKey, (ExpressionBuffer, ExpressionBuffer)]
 
     // Add a function and its function to the map for a given frame.
     def collect(tpe: String, fr: SpecifiedWindowFrame, e: Expression, fn: Expression): Unit = {
-      val key = (tpe, fr.frameType, fr.lower, fr.upper)
+      val key = fn match {
+        // This branch is used for Lead/Lag to support ignoring null.
+        // All window frames move in rows. If there are multiple Leads or Lags acting on a row
+        // and operating on different input expressions, they should not be moved uniformly
+        // by row. Therefore, we put these functions in different window frames.
+        case f: FrameLessOffsetWindowFunction if f.ignoreNulls =>
+          (tpe, fr.frameType, fr.lower, fr.upper, f.children.map(_.canonicalized))
+        case _ => (tpe, fr.frameType, fr.lower, fr.upper, null)

Review comment:
       nit: we can probably use Nil instead of null.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750800112


   thanks, merging to master!


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-731169958






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748841905


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133132/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750016923


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37880/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-731247410


   Merged build finished. Test FAILed.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547265624



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**
+   *  The number of UnsafeRows skipped to get the next UnsafeRow where
+   *  the input operator by function is not null.
+   */
+  private var skipNonNullCount = 0
+
+  /** find the offset row whose input is not null */
+  private def findNextRowWithNonNullInput(): Unit = {
+    while (skipNonNullCount < offset && inputIndex < input.length) {
+      val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+      if (!r.isNullAt(idx)) {
+        nextSelectedRow = r
+        skipNonNullCount += 1
+      }
+      inputIndex += 1
+    }
+  }
+
   override def prepare(rows: ExternalAppendOnlyUnsafeRowArray): Unit = {
     input = rows
     inputIterator = input.generateIterator()
     // drain the first few rows if offset is larger than zero
     inputIndex = 0
-    while (inputIndex < offset) {
-      if (inputIterator.hasNext) inputIterator.next()
-      inputIndex += 1
+    if (ignoreNulls) {
+      findNextRowWithNonNullInput
+    } else {
+      while (inputIndex < offset) {
+        if (inputIterator.hasNext) inputIterator.next()
+        inputIndex += 1
+      }
+      inputIndex = offset
     }
-    inputIndex = offset
+  }
+
+  private val doWrite = if (ignoreNulls && offset > 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lead(input, 2) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, next selected row -> y, output: y;
+    // 2. current row -> x, next selected row -> z, output: z;
+    // 3. current row -> null, next selected row -> z, output: z;
+    // 4. current row -> null, next selected row -> z, output: z;
+    // 5. current row -> y, next selected row -> empty, output: null;
+    // ... next selected row is empty, all following return null.
+    (current: InternalRow) =>
+      if (current.isNullAt(idx)) {
+        if (nextSelectedRow == EmptyRow) {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+        } else {
+          projection(nextSelectedRow)
+        }
+      } else {
+        skipNonNullCount -= 1
+        findNextRowWithNonNullInput
+        if (skipNonNullCount == offset) {
+          projection(nextSelectedRow)
+        } else {
+          // Use default values since the offset row whose input value is not null does not exist.
+          fillDefaultValue(current)
+          nextSelectedRow = EmptyRow
+        }
+      }
+  } else if (ignoreNulls && offset < 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lag(input, 1) with IGNORE NULLS and the process is as follows:
+    // 1. current row -> null, next selected row -> empty, output: null;
+    // 2. current row -> x, next selected row -> empty, output: null;
+    // 3. current row -> null, next selected row -> x, output: x;
+    // 4. current row -> null, next selected row -> x, output: x;
+    // 5. current row -> y, next selected row -> x, output: x;
+    // 6. current row -> null, next selected row -> y, output: y;
+    // 7. current row -> z, next selected row -> y, output: y;
+    // 8. current row -> z, next selected row -> z, output: z;
+    val absOffset = Math.abs(offset)
+    (current: InternalRow) =>
+      if (nextSelectedRow == EmptyRow && skipNonNullCount == absOffset) {
+        do {
+          val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+          if (!r.isNullAt(idx)) {
+            nextSelectedRow = r
+          }
+          inputIndex += 1
+        } while (nextSelectedRow == EmptyRow && inputIndex < input.length)
+      }
+      if (nextSelectedRow == EmptyRow) {

Review comment:
       This if-else sets the result row. Shall we do it at the end?




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-747209125


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37523/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-750772861


   **[Test build #133345 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/133345/testReport)** for PR 30387 at commit [`4f18319`](https://github.com/apache/spark/commit/4f183190df6a324364b5f895acaacbfebc16b7ca).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-746210187


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/37486/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-727910381


   Test FAILed.
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/131151/
   Test FAILed.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730378141


   **[Test build #131328 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131328/testReport)** for PR 30387 at commit [`19faf6c`](https://github.com/apache/spark/commit/19faf6cfc5192f30b9b7ce9ad6f2d8639be331fa).
    * This patch **fails SparkR unit tests**.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748880547






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728683880


   retest this please


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730446111


   **[Test build #131339 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131339/testReport)** for PR 30387 at commit [`e189ed4`](https://github.com/apache/spark/commit/e189ed4ce1b33da7f877b6e61242866e85221a2f).
    * This patch passes all tests.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748039120


   Kubernetes integration test status success
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/37612/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728646966


   **[Test build #131188 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131188/testReport)** for PR 30387 at commit [`3658b51`](https://github.com/apache/spark/commit/3658b5138f653f3b8a9d3f12797604c688f6c528).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730252058


   **[Test build #131339 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131339/testReport)** for PR 30387 at commit [`e189ed4`](https://github.com/apache/spark/commit/e189ed4ce1b33da7f877b6e61242866e85221a2f).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730092986


   **[Test build #131312 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/131312/testReport)** for PR 30387 at commit [`19faf6c`](https://github.com/apache/spark/commit/19faf6cfc5192f30b9b7ce9ad6f2d8639be331fa).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-728710242






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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-747289339


   **[Test build #132920 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/132920/testReport)** for PR 30387 at commit [`f3aad97`](https://github.com/apache/spark/commit/f3aad970fc9615dda6af6910e5743c757b63dba4).
    * This patch passes all tests.
    * This patch merges cleanly.
    * This patch adds no public classes.


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r527571534



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowExecBase.scala
##########
@@ -136,8 +136,8 @@ trait WindowExecBase extends UnaryExecNode {
           val frame = spec.frameSpecification.asInstanceOf[SpecifiedWindowFrame]
           function match {
             case AggregateExpression(f, _, _, _, _) => collect("AGGREGATE", frame, e, f)
-            case f: FrameLessOffsetWindowFunction =>
-              collect("FRAME_LESS_OFFSET", f.fakeFrame, e, f)
+            case f: FrameLessOffsetWindowFunction => collect(

Review comment:
       Maybe we can execute `lead(a, ignoreNulls = true)` and `lead(b, ignoreNulls = true)` with one window frame, but it will bring additional complexity.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-729281466


   cc @cloud-fan 


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-738809907


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/132220/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r543863851



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowExecBase.scala
##########
@@ -116,13 +116,17 @@ trait WindowExecBase extends UnaryExecNode {
    * [[WindowExpression]]s and factory function for the [[WindowFrameFunction]].
    */
   protected lazy val windowFrameExpressionFactoryPairs = {
-    type FrameKey = (String, FrameType, Expression, Expression)
+    type FrameKey = (String, FrameType, Expression, Expression, Expression)
     type ExpressionBuffer = mutable.Buffer[Expression]
     val framedFunctions = mutable.Map.empty[FrameKey, (ExpressionBuffer, ExpressionBuffer)]
 
     // Add a function and its function to the map for a given frame.
     def collect(tpe: String, fr: SpecifiedWindowFrame, e: Expression, fn: Expression): Unit = {
-      val key = (tpe, fr.frameType, fr.lower, fr.upper)
+      val key = fn match {
+        case f: FrameLessOffsetWindowFunction if f.ignoreNulls =>
+          (tpe, fr.frameType, fr.lower, fr.upper, f.canonicalized)

Review comment:
       Thanks for your suggestion.




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-730113733


   Kubernetes integration test status failure
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/35915/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-747180679


   **[Test build #132920 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/132920/testReport)** for PR 30387 at commit [`f3aad97`](https://github.com/apache/spark/commit/f3aad970fc9615dda6af6910e5743c757b63dba4).


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] beliefer commented on a change in pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
beliefer commented on a change in pull request #30387:
URL: https://github.com/apache/spark/pull/30387#discussion_r547622370



##########
File path: sql/core/src/main/scala/org/apache/spark/sql/execution/window/WindowFunctionFrame.scala
##########
@@ -147,31 +148,133 @@ class FrameLessOffsetWindowFunctionFrame(
     expressions: Array[OffsetWindowFunction],
     inputSchema: Seq[Attribute],
     newMutableProjection: (Seq[Expression], Seq[Attribute]) => MutableProjection,
-    offset: Int)
+    offset: Int,
+    ignoreNulls: Boolean = false)
   extends OffsetWindowFunctionFrameBase(
     target, ordinal, expressions, inputSchema, newMutableProjection, offset) {
 
+  /** The input expression of Lead/Lag. */
+  private lazy val inputExpression = expressions.toSeq.map(_.input).head
+
+  /** The index of input expression in the row. */
+  private lazy val idx = inputAttrs.zipWithIndex.find(_._1 == inputExpression).map(_._2).head
+
+  /** Holder the UnsafeRow where the input operator by function is not null. */
+  private var nextSelectedRow = EmptyRow
+
+  /**
+   *  The number of UnsafeRows skipped to get the next UnsafeRow where
+   *  the input operator by function is not null.
+   */
+  private var skipNonNullCount = 0
+
+  /** find the offset row whose input is not null */
+  private def findNextRowWithNonNullInput(): Unit = {
+    while (skipNonNullCount < offset && inputIndex < input.length) {
+      val r = WindowFunctionFrame.getNextOrNull(inputIterator)
+      if (!r.isNullAt(idx)) {
+        nextSelectedRow = r
+        skipNonNullCount += 1
+      }
+      inputIndex += 1
+    }
+  }
+
   override def prepare(rows: ExternalAppendOnlyUnsafeRowArray): Unit = {
     input = rows
     inputIterator = input.generateIterator()
     // drain the first few rows if offset is larger than zero
     inputIndex = 0
-    while (inputIndex < offset) {
-      if (inputIterator.hasNext) inputIterator.next()
-      inputIndex += 1
+    if (ignoreNulls) {
+      findNextRowWithNonNullInput
+    } else {
+      while (inputIndex < offset) {
+        if (inputIterator.hasNext) inputIterator.next()
+        inputIndex += 1
+      }
+      inputIndex = offset
     }
-    inputIndex = offset
+  }
+
+  private val doWrite = if (ignoreNulls && offset > 0) {
+    // For illustration, here is one example: the input data contains six rows,
+    // and the input values of each row are: null, x, null, null, y, null, z, null.
+    // We use Lead(input, 2) with IGNORE NULLS and the process is as follows:

Review comment:
       OK




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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #30387: [SPARK-33443][SQL] LEAD/LAG should support [ IGNORE NULLS | RESPECT NULLS ]

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #30387:
URL: https://github.com/apache/spark/pull/30387#issuecomment-748147913


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/133014/
   


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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org