You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2023/07/25 14:20:49 UTC

[GitHub] [arrow-datafusion] alamb opened a new pull request, #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs

alamb opened a new pull request, #7088:
URL: https://github.com/apache/arrow-datafusion/pull/7088

   # Which issue does this PR close?
   
   Closes https://github.com/apache/arrow-datafusion/issues/7087
   
   # Rationale for this change
   
   This implements the prospoal from @jayzhan211 @izveigor @vincev  and @smiklos from https://github.com/apache/arrow-datafusion/pull/7044#discussion_r1271437027
   
   # What changes are included in this PR?
   1. Make `unnest` consistent with DuckDB/ClickHouse (do not preserve nulls), 
   2. add option for preserve_nulls
   3. Add test
   4. update docs
   
   # Are these changes tested?
   yes
   
   # Are there any user-facing changes?
   yes, API has changed
   <!--
   If there are user-facing changes then we may require documentation to be updated before approving the PR.
   -->
   
   <!--
   If there are any breaking changes to public APIs, please add the `api change` label.
   -->


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

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

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


[GitHub] [arrow-datafusion] alamb commented on pull request #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #7088:
URL: https://github.com/apache/arrow-datafusion/pull/7088#issuecomment-1652408228

   Sorry I did not make progress on this today; I have it on my list for tomorrow (or anyone else feel free to make a PR using any of this PR they want)


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

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

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


[GitHub] [arrow-datafusion] vincev commented on a diff in pull request #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs

Posted by "vincev (via GitHub)" <gi...@apache.org>.
vincev commented on code in PR #7088:
URL: https://github.com/apache/arrow-datafusion/pull/7088#discussion_r1273692987


##########
datafusion/core/src/dataframe.rs:
##########
@@ -162,9 +164,10 @@ impl DataFrame {
     /// # Ok(())
     /// # }
     /// ```
-    pub fn unnest_column(self, column: &str) -> Result<DataFrame> {
+    /// [Unnest]: crate::logical_expr::Unnest
+    pub fn unnest_column(self, column: &str, preserve_nulls: bool) -> Result<DataFrame> {

Review Comment:
   `_with_options` looks nice to me too, `DataFrame::unnest` would call `_with_options` preserving nulls so that users will keep getting the same behavior as before that is consistent with other data frame libraries and SQL unnest would use the DuckDB/ClickHouse behavior.  



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

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

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


[GitHub] [arrow-datafusion] alamb commented on pull request #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #7088:
URL: https://github.com/apache/arrow-datafusion/pull/7088#issuecomment-1687055841

   This was superceded by https://github.com/apache/arrow-datafusion/pull/7168


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

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

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


[GitHub] [arrow-datafusion] smiklos commented on a diff in pull request #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs

Posted by "smiklos (via GitHub)" <gi...@apache.org>.
smiklos commented on code in PR #7088:
URL: https://github.com/apache/arrow-datafusion/pull/7088#discussion_r1273666633


##########
datafusion/core/src/dataframe.rs:
##########
@@ -162,9 +164,10 @@ impl DataFrame {
     /// # Ok(())
     /// # }
     /// ```
-    pub fn unnest_column(self, column: &str) -> Result<DataFrame> {
+    /// [Unnest]: crate::logical_expr::Unnest
+    pub fn unnest_column(self, column: &str, preserve_nulls: bool) -> Result<DataFrame> {

Review Comment:
   we could just keep the overload but add the options struct instead of only the bool flag.



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

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

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


[GitHub] [arrow-datafusion] alamb commented on a diff in pull request #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #7088:
URL: https://github.com/apache/arrow-datafusion/pull/7088#discussion_r1273630091


##########
datafusion/core/tests/dataframe/mod.rs:
##########
@@ -1035,15 +1039,70 @@ async fn unnest_columns() -> Result<()> {
     // Test aggregate results for points and tags.
     let df = table_with_nested_types(NUM_ROWS).await?;
     let count = df
-        .unnest_column("points")?
-        .unnest_column("tags")?
+        .unnest_column("points", preserve_nulls)?
+        .unnest_column("tags", preserve_nulls)?
         .count()
         .await?;
     assert_eq!(count, results.iter().map(|r| r.num_rows()).sum::<usize>());
 
     Ok(())
 }
 
+#[tokio::test]
+async fn unnest_column_nulls() -> Result<()> {

Review Comment:
   Here is a test that I think shows the difference between the two modes (or will once we implement it)



##########
datafusion/core/src/dataframe.rs:
##########
@@ -162,9 +164,10 @@ impl DataFrame {
     /// # Ok(())
     /// # }
     /// ```
-    pub fn unnest_column(self, column: &str) -> Result<DataFrame> {
+    /// [Unnest]: crate::logical_expr::Unnest
+    pub fn unnest_column(self, column: &str, preserve_nulls: bool) -> Result<DataFrame> {

Review Comment:
   I think this is different than what @vincev proposed in  https://github.com/apache/arrow-datafusion/pull/7044#discussion_r1271325311
   
   Specifically, there are three ways to make unnest plans:
   1. `unnest` function
   2. `Dataframe::unnest`
   3. `LogicalPlanBuilder`
   
   It seemed inconsistent to add a `preserve_nulls` argument in only one place
   
   An alternative would be to add new `unest_with_options`, `DataFrame::unest_with_options` and `LogicalPlanBuilder::with_options` and leave the existing signatures alone.
   
   What do reviewers think?



##########
datafusion/expr/src/logical_plan/plan.rs:
##########
@@ -1762,7 +1767,47 @@ pub enum Partitioning {
     DistributeBy(Vec<Expr>),
 }
 
-/// Unnest a column that contains a nested list type.
+/// Unnest a column that contains a nested list type, replicating
+/// values in the other, non nested rows.
+///
+/// Conceptually this operation is like joining each row with all the

Review Comment:
   Here is the proposed behavior



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

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

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


[GitHub] [arrow-datafusion] smiklos commented on pull request #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs

Posted by "smiklos (via GitHub)" <gi...@apache.org>.
smiklos commented on PR #7088:
URL: https://github.com/apache/arrow-datafusion/pull/7088#issuecomment-1653050105

   > Sorry I did not make progress on this today; I have it on my list for tomorrow (or anyone else feel free to make a PR using any of this PR they want)
   
   I can work on this but can't promise to be done before the weekend... 
   
   The plan is to leave (and mark deprecated) the existing UnnestExec and create a new variant that handles options such as preserve_nulls. Then move all api calls to this new physical plan where sql uses perserve_nulls = false, and the dataframe api uses preserve_nulls = true. Right? 


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

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

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


[GitHub] [arrow-datafusion] alamb commented on pull request #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #7088:
URL: https://github.com/apache/arrow-datafusion/pull/7088#issuecomment-1657109615

   BTW I am sorry for my delay -- unfortunately sorting out list / nested types is not as high a priority for us at InfluxData at this time, even though I think it is very important for DataFusion overall. Thus I have to work on this project  in my spare time.
   
   I am definitely still planning to work on this PR, but if anyone else has time and would like to try and take it on that would also be great 🙏 
   
   I was thinking about making `UnnestOptions` in `datafusion_common` and then update the API to use it
   
   ```rust
   /// <docs about unnest behavior here>
   struct UnestOptions {
     pub preserve_nulls: ...
   }
   ```
   
   I would suggest adding a new `options: UnnestOptions` field to the existing Unest LogicalPlan and UnnestExec rather than making entirely new operators
   
   To keep the size of PRs down, I also would personally suggest adding in the API in one PR but implementing the new `preserve_nulls: true` behavior as a second PR.


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

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

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


[GitHub] [arrow-datafusion] alamb closed pull request #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed pull request #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs
URL: https://github.com/apache/arrow-datafusion/pull/7088


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

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

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


[GitHub] [arrow-datafusion] smiklos commented on pull request #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs

Posted by "smiklos (via GitHub)" <gi...@apache.org>.
smiklos commented on PR #7088:
URL: https://github.com/apache/arrow-datafusion/pull/7088#issuecomment-1649993963

   Looks great imo, would love to move the improvements form #7002 into a new branch on top of this + implement handling the flag 


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

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

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


[GitHub] [arrow-datafusion] alamb commented on a diff in pull request #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #7088:
URL: https://github.com/apache/arrow-datafusion/pull/7088#discussion_r1273636946


##########
datafusion/core/src/dataframe.rs:
##########
@@ -162,9 +164,10 @@ impl DataFrame {
     /// # Ok(())
     /// # }
     /// ```
-    pub fn unnest_column(self, column: &str) -> Result<DataFrame> {
+    /// [Unnest]: crate::logical_expr::Unnest
+    pub fn unnest_column(self, column: &str, preserve_nulls: bool) -> Result<DataFrame> {

Review Comment:
   🤔  seeing the scope of the required changes to add a new parameter, I am thinking `_with_options` would be preferable 



##########
datafusion/core/src/dataframe.rs:
##########
@@ -162,9 +164,10 @@ impl DataFrame {
     /// # Ok(())
     /// # }
     /// ```
-    pub fn unnest_column(self, column: &str) -> Result<DataFrame> {
+    /// [Unnest]: crate::logical_expr::Unnest
+    pub fn unnest_column(self, column: &str, preserve_nulls: bool) -> Result<DataFrame> {

Review Comment:
   🤔  seeing the scope of the required changes to add a new parameter, I am thinking `_with_options` would be preferable 



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

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

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


[GitHub] [arrow-datafusion] alamb commented on a diff in pull request #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #7088:
URL: https://github.com/apache/arrow-datafusion/pull/7088#discussion_r1273808447


##########
datafusion/core/src/dataframe.rs:
##########
@@ -162,9 +164,10 @@ impl DataFrame {
     /// # Ok(())
     /// # }
     /// ```
-    pub fn unnest_column(self, column: &str) -> Result<DataFrame> {
+    /// [Unnest]: crate::logical_expr::Unnest
+    pub fn unnest_column(self, column: &str, preserve_nulls: bool) -> Result<DataFrame> {

Review Comment:
   Ok, I'll give it a try



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

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

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


[GitHub] [arrow-datafusion] alamb commented on pull request #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #7088:
URL: https://github.com/apache/arrow-datafusion/pull/7088#issuecomment-1660297730

   starting to make another proposal PR...


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

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

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


[GitHub] [arrow-datafusion] alamb commented on pull request #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #7088:
URL: https://github.com/apache/arrow-datafusion/pull/7088#issuecomment-1661076372

   > Sorry for not picking this up. 
   
   No worries -- sorry for the delay
   
   > I'm not sure it makes sense to add the Options as a struct vs just a single preserve_nulls field in the logical/physical plan.
   
   Yeah I can see the argument for just a field. The nice thing about the struct was it gave me a place to put the documentation on that could be referenced as well as adding a place that any potential future options could be added
   
   > Happy to drive the implementation and include the improvements mentioned https://github.com/apache/arrow-datafusion/issues/6961
   
   That would be great


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

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

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


[GitHub] [arrow-datafusion] smiklos commented on pull request #7088: Make `unnest` consistent with DuckDB/ClickHouse, add option for preserve_nulls, update docs

Posted by "smiklos (via GitHub)" <gi...@apache.org>.
smiklos commented on PR #7088:
URL: https://github.com/apache/arrow-datafusion/pull/7088#issuecomment-1660319525

   > starting to make another proposal PR...
   
   Sorry for not picking this up. I'm not sure it makes sense to add the Options as a struct vs just a single preserve_nulls field in the logical/physical plan. 
   
   Happy to drive the implementation and include the improvements mentioned [here](https://github.com/apache/arrow-datafusion/issues/6961)


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

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

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