You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "stuartcarnie (via GitHub)" <gi...@apache.org> on 2025/09/05 04:34:52 UTC

[PR] feat: Support binary data types for `SortMergeJoin` `on` clause [datafusion]

stuartcarnie opened a new pull request, #17431:
URL: https://github.com/apache/datafusion/pull/17431

   ## Which issue does this PR close?
   
   N/A
   
   ## Rationale for this change
   
   Adds the ability for the `SortMergeJoin` physical node to join on binary types:
   
   - `Binary`,
   - `FixedSizeBinary`
   - `BinaryView`
   - `LargeBinary`
   
   ## What changes are included in this PR?
   
   - An update to the comparitor functions to support the listed binary types
   - Tests to verify binary types are supported via the `ON` clause
   
   ## Are these changes tested?
   
   ✅ 
   
   ## Are there any user-facing changes?
   
   The documentation does not list the subset of types supported by the `ON` clause of `SortMergeJoin`.
   


-- 
This is an automated message from the 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@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] feat: Support binary data types for `SortMergeJoin` `on` clause [datafusion]

Posted by "jonathanc-n (via GitHub)" <gi...@apache.org>.
jonathanc-n commented on PR #17431:
URL: https://github.com/apache/datafusion/pull/17431#issuecomment-3257192246

   > > I'm surprised binary data isnt part of the join fuzz testing, this could be put in a follow up issue
   > 
   > Sounds good. Where is the fuzz testing, as I was looking for a place to write some test SQL to verify at a higher level via some integration tests.
   
   The fuzz testing is in `datafusion/core/tests/fuzz_cases/join_fuzz.rs`, I forget if it contains the data generation as well. Integration tests might be overkill but it'd be nice to see if the binary values are working as we would expect.


-- 
This is an automated message from the 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@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] feat: Support binary data types for `SortMergeJoin` `on` clause [datafusion]

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


##########
datafusion/physical-plan/src/joins/sort_merge_join/exec.rs:
##########
@@ -1923,6 +1974,100 @@ mod tests {
         Ok(())
     }
 
+    #[tokio::test]
+    async fn join_binary() -> Result<()> {
+        let left = build_binary_table(
+            (
+                "a1",
+                &vec![
+                    &[0xc0, 0xff, 0xee],
+                    &[0xde, 0xca, 0xde],
+                    &[0xfa, 0xca, 0xde],
+                ],
+            ),
+            ("b1", &vec![5, 10, 15]), // this has a repetition
+            ("c1", &vec![7, 8, 9]),
+        );
+        let right = build_binary_table(
+            (
+                "a1",
+                &vec![
+                    &[0xc0, 0xff, 0xee],
+                    &[0xde, 0xca, 0xde],
+                    &[0xfa, 0xca, 0xde],
+                ],
+            ),
+            ("b2", &vec![105, 110, 115]),
+            ("c2", &vec![70, 80, 90]),
+        );
+
+        let on = vec![(
+            Arc::new(Column::new_with_schema("a1", &left.schema())?) as _,
+            Arc::new(Column::new_with_schema("a1", &right.schema())?) as _,
+        )];
+
+        let (_, batches) = join_collect(left, right, on, Inner).await?;
+
+        // The output order is important as SMJ preserves sortedness
+        assert_snapshot!(batches_to_string(&batches), @r#"
+            +--------+----+----+--------+-----+----+
+            | a1     | b1 | c1 | a1     | b2  | c2 |
+            +--------+----+----+--------+-----+----+
+            | c0ffee | 5  | 7  | c0ffee | 105 | 70 |
+            | decade | 10 | 8  | decade | 110 | 80 |
+            | facade | 15 | 9  | facade | 115 | 90 |
+            +--------+----+----+--------+-----+----+
+            "#);
+        Ok(())
+    }
+
+    #[tokio::test]
+    async fn join_fixed_size_binary() -> Result<()> {

Review Comment:
   That would be great if you could do so



-- 
This is an automated message from the 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@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] feat: Support binary data types for `SortMergeJoin` `on` clause [datafusion]

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

   > I'm surprised binary data isnt part of the join fuzz testing, this could be put in a follow up issue
   
   Sounds good. Where is the fuzz testing, as I was looking for a place to write some test SQL to verify at a higher level via some integration tests.


-- 
This is an automated message from the 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@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] feat: Support binary data types for `SortMergeJoin` `on` clause [datafusion]

Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead merged PR #17431:
URL: https://github.com/apache/datafusion/pull/17431


-- 
This is an automated message from the 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@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] feat: Support binary data types for `SortMergeJoin` `on` clause [datafusion]

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

   > I added some additional sqllogictests as @jonathanc-n suggested and verified they fail without the code changes in this PR
   
   That's great, thanks @alamb!


-- 
This is an automated message from the 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@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] feat: Support binary data types for `SortMergeJoin` `on` clause [datafusion]

Posted by "jonathanc-n (via GitHub)" <gi...@apache.org>.
jonathanc-n commented on PR #17431:
URL: https://github.com/apache/datafusion/pull/17431#issuecomment-3257195263

   Here is the file for slt testing SMJ: https://github.com/apache/datafusion/blob/main/datafusion/sqllogictest/test_files/sort_merge_join.slt


-- 
This is an automated message from the 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@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] feat: Support binary data types for `SortMergeJoin` `on` clause [datafusion]

Posted by "jonathanc-n (via GitHub)" <gi...@apache.org>.
jonathanc-n commented on code in PR #17431:
URL: https://github.com/apache/datafusion/pull/17431#discussion_r2324191348


##########
datafusion/physical-plan/src/joins/sort_merge_join/exec.rs:
##########
@@ -1923,6 +1974,100 @@ mod tests {
         Ok(())
     }
 
+    #[tokio::test]
+    async fn join_binary() -> Result<()> {
+        let left = build_binary_table(
+            (
+                "a1",
+                &vec![
+                    &[0xc0, 0xff, 0xee],
+                    &[0xde, 0xca, 0xde],
+                    &[0xfa, 0xca, 0xde],
+                ],
+            ),
+            ("b1", &vec![5, 10, 15]), // this has a repetition
+            ("c1", &vec![7, 8, 9]),
+        );
+        let right = build_binary_table(
+            (
+                "a1",
+                &vec![
+                    &[0xc0, 0xff, 0xee],
+                    &[0xde, 0xca, 0xde],
+                    &[0xfa, 0xca, 0xde],
+                ],
+            ),
+            ("b2", &vec![105, 110, 115]),
+            ("c2", &vec![70, 80, 90]),
+        );
+
+        let on = vec![(
+            Arc::new(Column::new_with_schema("a1", &left.schema())?) as _,
+            Arc::new(Column::new_with_schema("a1", &right.schema())?) as _,
+        )];
+
+        let (_, batches) = join_collect(left, right, on, Inner).await?;
+
+        // The output order is important as SMJ preserves sortedness
+        assert_snapshot!(batches_to_string(&batches), @r#"
+            +--------+----+----+--------+-----+----+
+            | a1     | b1 | c1 | a1     | b2  | c2 |
+            +--------+----+----+--------+-----+----+
+            | c0ffee | 5  | 7  | c0ffee | 105 | 70 |
+            | decade | 10 | 8  | decade | 110 | 80 |
+            | facade | 15 | 9  | facade | 115 | 90 |
+            +--------+----+----+--------+-----+----+
+            "#);
+        Ok(())
+    }
+
+    #[tokio::test]
+    async fn join_fixed_size_binary() -> Result<()> {

Review Comment:
   Maybe we should include large binary test as well? 🤔 I noticed the amount of unit tests were getting out of hand, maybe I'll look into separating the tests into the other SMJ files.



-- 
This is an automated message from the 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@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] feat: Support binary data types for `SortMergeJoin` `on` clause [datafusion]

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

   > > I'm surprised binary data isnt part of the join fuzz testing, this could be put in a follow up issue
   > 
   > Sounds good. Where is the fuzz testing, as I was looking for a place to write some test SQL to verify at a higher level via some integration tests.
   
   I filed a ticket to track adding support to fuzz testing:
   - https://github.com/apache/datafusion/issues/17447


-- 
This is an automated message from the 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@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org