You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by GitBox <gi...@apache.org> on 2022/05/26 18:44:13 UTC

[GitHub] [arrow-datafusion] WinkerDu opened a new pull request, #2627: Like, NotLike expressions work with literal `NULL`

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

   # Which issue does this PR close?
   
   <!--
   We generally require a GitHub issue to be filed for all bug fixes and enhancements and this helps us generate change logs for our releases. You can link an issue to this PR using the GitHub syntax. For example `Closes #123` indicates that this PR will close issue #123.
   -->
   
   Closes #2626 .
   
    # Rationale for this change
   `like`, `not like` expressions doesn't work well with literal `NULL` in DF.
   
   **To Reproduce**
   ```
   > SELECT column1 like NULL from (values('a'), ('b'), (NULL)) as t
   Plan("'Utf8 LIKE Null' can't be evaluated because there isn't a common type to coerce the types to")
   ```
   Postgres works like
   ```
   # SELECT column1 like NULL from (values('a'), ('b'), (NULL)) as t;
    ?column? 
   ----------
    
    
    
   (3 rows)
   ```
   
   # What changes are included in this PR?
   - Introduces `null_coercion` to `like_coercion`
   - Enhances `compute_utf8_op_scalar` to produce null array when  scalar value `NULL` inputs.
   
   # Are there any user-facing changes?
   No.
   
   # Does this PR break compatibility with Ballista?
   No.


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

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

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

[GitHub] [arrow-datafusion] alamb merged pull request #2627: Like, NotLike expressions work with literal `NULL`

Posted by GitBox <gi...@apache.org>.
alamb merged PR #2627:
URL: https://github.com/apache/arrow-datafusion/pull/2627


-- 
This is an automated message from the 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] WinkerDu commented on pull request #2627: Like, NotLike expressions work with literal `NULL`

Posted by GitBox <gi...@apache.org>.
WinkerDu commented on PR #2627:
URL: https://github.com/apache/arrow-datafusion/pull/2627#issuecomment-1139232470

   cc @andygrove @alamb @yjshen , Please have a review, 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.

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 #2627: Like, NotLike expressions work with literal `NULL`

Posted by GitBox <gi...@apache.org>.
alamb commented on code in PR #2627:
URL: https://github.com/apache/arrow-datafusion/pull/2627#discussion_r883505848


##########
datafusion/core/tests/sql/expr.rs:
##########
@@ -1202,6 +1202,36 @@ async fn nested_subquery() -> Result<()> {
     Ok(())
 }
 
+#[tokio::test]
+async fn like_nlike_with_null_lt() {
+    let ctx = SessionContext::new();
+    let sql = "SELECT column1 like NULL from (values('a'), ('b'), (NULL)) as t";

Review Comment:
   Can you also add a test when the arguments are reversed? For example,:
   
   
   ```suggestion
       let sql = "SELECT column1 like NULL as col_null, NULL like column1 as null_col from (values('a'), ('b'), (NULL)) as t";
   ```



##########
datafusion/core/tests/sql/expr.rs:
##########
@@ -1202,6 +1202,36 @@ async fn nested_subquery() -> Result<()> {
     Ok(())
 }
 
+#[tokio::test]
+async fn like_nlike_with_null_lt() {
+    let ctx = SessionContext::new();
+    let sql = "SELECT column1 like NULL from (values('a'), ('b'), (NULL)) as t";
+    let actual = execute_to_batches(&ctx, sql).await;
+    let expected = vec![
+        "+---------------------+",
+        "| t.column1 Like NULL |",
+        "+---------------------+",
+        "|                     |",
+        "|                     |",
+        "|                     |",
+        "+---------------------+",
+    ];
+    assert_batches_eq!(expected, &actual);
+
+    let sql = "SELECT column1 not like NULL from (values('a'), ('b'), (NULL)) as t";

Review Comment:
   ```suggestion
       let sql = "SELECT column1 not like NULL as col_null, NULL not like column as null_col from (values('a'), ('b'), (NULL)) as t";
   ```



-- 
This is an automated message from the 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] WinkerDu commented on a diff in pull request #2627: Like, NotLike expressions work with literal `NULL`

Posted by GitBox <gi...@apache.org>.
WinkerDu commented on code in PR #2627:
URL: https://github.com/apache/arrow-datafusion/pull/2627#discussion_r883592158


##########
datafusion/core/tests/sql/expr.rs:
##########
@@ -1202,6 +1202,36 @@ async fn nested_subquery() -> Result<()> {
     Ok(())
 }
 
+#[tokio::test]
+async fn like_nlike_with_null_lt() {
+    let ctx = SessionContext::new();
+    let sql = "SELECT column1 like NULL from (values('a'), ('b'), (NULL)) as t";
+    let actual = execute_to_batches(&ctx, sql).await;
+    let expected = vec![
+        "+---------------------+",
+        "| t.column1 Like NULL |",
+        "+---------------------+",
+        "|                     |",
+        "|                     |",
+        "|                     |",
+        "+---------------------+",
+    ];
+    assert_batches_eq!(expected, &actual);
+
+    let sql = "SELECT column1 not like NULL from (values('a'), ('b'), (NULL)) as t";

Review Comment:
   Done



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

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

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

[GitHub] [arrow-datafusion] WinkerDu commented on a diff in pull request #2627: Like, NotLike expressions work with literal `NULL`

Posted by GitBox <gi...@apache.org>.
WinkerDu commented on code in PR #2627:
URL: https://github.com/apache/arrow-datafusion/pull/2627#discussion_r883591982


##########
datafusion/core/tests/sql/expr.rs:
##########
@@ -1202,6 +1202,36 @@ async fn nested_subquery() -> Result<()> {
     Ok(())
 }
 
+#[tokio::test]
+async fn like_nlike_with_null_lt() {
+    let ctx = SessionContext::new();
+    let sql = "SELECT column1 like NULL from (values('a'), ('b'), (NULL)) as t";

Review Comment:
   @alamb Sounds reasonable, I've changed the ut, 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.

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

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