You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@arrow.apache.org by dh...@apache.org on 2024/03/18 12:20:41 UTC

(arrow-datafusion) branch main updated: Support IGNORE NULLS for NTH_VALUE window function (#9625)

This is an automated email from the ASF dual-hosted git repository.

dheres pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/arrow-datafusion.git


The following commit(s) were added to refs/heads/main by this push:
     new 40bf0ea183 Support IGNORE NULLS for NTH_VALUE window function (#9625)
40bf0ea183 is described below

commit 40bf0ea183b4aa8ecab01f8816f0fb83328cd262
Author: Huaxin Gao <hu...@gmail.com>
AuthorDate: Mon Mar 18 05:20:36 2024 -0700

    Support IGNORE NULLS for NTH_VALUE window function (#9625)
    
    Co-authored-by: Huaxin Gao <hu...@apple.com>
---
 datafusion/physical-expr/src/window/nth_value.rs |  32 ++--
 datafusion/physical-plan/src/windows/mod.rs      |   1 -
 datafusion/sqllogictest/test_files/window.slt    | 182 +++++++++++++++++++++++
 3 files changed, 205 insertions(+), 10 deletions(-)

diff --git a/datafusion/physical-expr/src/window/nth_value.rs b/datafusion/physical-expr/src/window/nth_value.rs
index 5c7c891f92..e913f39333 100644
--- a/datafusion/physical-expr/src/window/nth_value.rs
+++ b/datafusion/physical-expr/src/window/nth_value.rs
@@ -83,19 +83,16 @@ impl NthValue {
         name: impl Into<String>,
         expr: Arc<dyn PhysicalExpr>,
         data_type: DataType,
-        n: u32,
+        n: i64,
         ignore_nulls: bool,
     ) -> Result<Self> {
-        if ignore_nulls {
-            return exec_err!("NTH_VALUE ignore_nulls is not supported yet");
-        }
         match n {
             0 => exec_err!("NTH_VALUE expects n to be non-zero"),
             _ => Ok(Self {
                 name: name.into(),
                 expr,
                 data_type,
-                kind: NthValueKind::Nth(n as i64),
+                kind: NthValueKind::Nth(n),
                 ignore_nulls,
             }),
         }
@@ -267,20 +264,37 @@ impl PartitionEvaluator for NthValueEvaluator {
                             if index >= n_range {
                                 // Outside the range, return NULL:
                                 ScalarValue::try_from(arr.data_type())
+                            } else if self.ignore_nulls {
+                                let valid_indices = valid_indices.unwrap();
+                                if index >= valid_indices.len() {
+                                    return ScalarValue::try_from(arr.data_type());
+                                }
+                                ScalarValue::try_from_array(
+                                    &slice.unwrap(),
+                                    valid_indices[index],
+                                )
                             } else {
                                 ScalarValue::try_from_array(arr, range.start + index)
                             }
                         }
                         Ordering::Less => {
                             let reverse_index = (-n) as usize;
-                            if n_range >= reverse_index {
+                            if n_range < reverse_index {
+                                // Outside the range, return NULL:
+                                ScalarValue::try_from(arr.data_type())
+                            } else if self.ignore_nulls {
+                                let valid_indices = valid_indices.unwrap();
+                                if reverse_index > valid_indices.len() {
+                                    return ScalarValue::try_from(arr.data_type());
+                                }
+                                let new_index =
+                                    valid_indices[valid_indices.len() - reverse_index];
+                                ScalarValue::try_from_array(&slice.unwrap(), new_index)
+                            } else {
                                 ScalarValue::try_from_array(
                                     arr,
                                     range.start + n_range - reverse_index,
                                 )
-                            } else {
-                                // Outside the range, return NULL:
-                                ScalarValue::try_from(arr.data_type())
                             }
                         }
                         Ordering::Equal => {
diff --git a/datafusion/physical-plan/src/windows/mod.rs b/datafusion/physical-plan/src/windows/mod.rs
index 6712bc855f..da2b24487d 100644
--- a/datafusion/physical-plan/src/windows/mod.rs
+++ b/datafusion/physical-plan/src/windows/mod.rs
@@ -249,7 +249,6 @@ fn create_built_in_window_expr(
                 .clone()
                 .try_into()
                 .map_err(|e| DataFusionError::Execution(format!("{e:?}")))?;
-            let n: u32 = n as u32;
             Arc::new(NthValue::nth(
                 name,
                 arg,
diff --git a/datafusion/sqllogictest/test_files/window.slt b/datafusion/sqllogictest/test_files/window.slt
index efb180b10c..a309a97137 100644
--- a/datafusion/sqllogictest/test_files/window.slt
+++ b/datafusion/sqllogictest/test_files/window.slt
@@ -4617,3 +4617,185 @@ NULL 1
 
 statement ok
 DROP TABLE t;
+
+# Test for ignore nulls with ORDER BY in nth_VALUE
+statement ok
+CREATE TABLE t AS VALUES  (3, 3), (4, 4), (null::bigint, 1), (null::bigint, 2), (5, 5), (6, 6);
+
+query I
+SELECT column1 FROM t ORDER BY column2;
+----
+NULL
+NULL
+3
+4
+5
+6
+
+query I
+SELECT nth_VALUE(column1, 3) OVER(ORDER BY column2) FROM t;
+----
+NULL
+NULL
+3
+3
+3
+3
+
+query I
+SELECT nth_VALUE(column1, 3) IGNORE NULLS OVER(ORDER BY column2) FROM t;
+----
+NULL
+NULL
+NULL
+NULL
+5
+5
+
+query I
+SELECT nth_VALUE(column1, 3) OVER(ORDER BY column2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t;
+----
+3
+3
+3
+3
+3
+3
+
+query I
+SELECT nth_VALUE(column1, 3) IGNORE NULLS OVER(ORDER BY column2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t;
+----
+5
+5
+5
+5
+5
+5
+
+query I
+SELECT nth_value(column1, 3) OVER(ORDER BY column2 RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t;
+----
+NULL
+3
+4
+5
+6
+NULL
+
+query I
+SELECT nth_value(column1, 3) IGNORE NULLS OVER (ORDER BY column2 RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t;
+----
+NULL
+NULL
+NULL
+5
+6
+NULL
+
+statement ok
+DROP TABLE t;
+
+# Test for negative index in NTH_VALUE
+statement ok
+CREATE TABLE t AS VALUES  (3, 3), (4, 4), (null::bigint, 1), (null::bigint, 2), (5, 5), (6, 6);
+
+query I
+SELECT column1 FROM t ORDER BY column2 DESC NULLS LAST;
+----
+6
+5
+4
+3
+NULL
+NULL
+
+query I
+SELECT nth_VALUE(column1, -2) OVER(ORDER BY column2 DESC NULLS LAST) FROM t;
+----
+NULL
+6
+5
+4
+3
+NULL
+
+query I
+SELECT nth_VALUE(column1, -2) IGNORE NULLS OVER(ORDER BY column2 DESC NULLS LAST) FROM t;
+----
+NULL
+6
+5
+4
+4
+4
+
+query I
+SELECT nth_VALUE(column1, -2) OVER(ORDER BY column2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t;
+----
+5
+5
+5
+5
+5
+5
+
+query I
+SELECT nth_VALUE(column1, -2) IGNORE NULLS OVER(ORDER BY column2 DESC NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t;
+----
+4
+4
+4
+4
+4
+4
+
+query I
+SELECT nth_value(column1, -2) OVER (ORDER BY column2 DESC NULLS LAST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t;
+----
+6
+5
+4
+3
+NULL
+NULL
+
+query I
+SELECT nth_value(column1, -2) IGNORE NULLS OVER (ORDER BY column2 DESC NULLS LAST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t;
+----
+6
+5
+4
+4
+NULL
+NULL
+
+statement ok
+DROP TABLE t;
+
+# Test for ignore nulls with ORDER BY in NTH_VALUE with all NULLs
+statement ok
+CREATE TABLE t AS VALUES  (null::bigint, 4), (null::bigint, 3), (null::bigint, 1), (null::bigint, 2);
+
+query II
+SELECT NTH_VALUE(column1, 2) OVER(ORDER BY column2 DESC NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), column2 FROM t;
+----
+NULL 4
+NULL 3
+NULL 2
+NULL 1
+
+query II
+SELECT NTH_VALUE(column1, 2) RESPECT NULLS OVER(ORDER BY column2 DESC NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), column2 FROM t;
+----
+NULL 4
+NULL 3
+NULL 2
+NULL 1
+
+query II
+SELECT NTH_VALUE(column1, 2) IGNORE NULLS OVER(ORDER BY column2 DESC NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), column2 FROM t;
+----
+NULL 4
+NULL 3
+NULL 2
+NULL 1