westonpace opened a new issue, #36420:
URL: https://github.com/apache/arrow/issues/36420

   ### Describe the enhancement requested
   
   This could be a new compute function or it could be new options for the 
existing is_in function.  Currently, the way `is_in` treats `null` does not 
match SQL semantics which makes it difficult to use when interoperability is 
expected.
   
   The current behavior is summed up nicely in the 
`SetLookupOptions::skip_nulls` description:
   
   ```
   /// Whether nulls in `value_set` count for lookup.
   ///
   /// If true, any null in `value_set` is ignored and nulls in the input
   /// produce null (IndexIn) or false (IsIn) values in the output.
   /// If false, any null in `value_set` is successfully matched in
   /// the input.
   ```
   
   Or, to put it another way.  The behavior we currently get (regardless of 
skip_nulls) is:
   
   | Input Value | Lookup Set | Skip Nulls | Result |
   | --- | --- | --- | --- |
   | 5 | [1, NULL] | any value | false |
   | 5 | [5, NULL] | any value | true |
   | NULL | [5] | any value | false |
   | NULL | [5, NULL] | true | false |
   | NULL | [5, NULL] | false | true |
   
   This makes perfect sense for the "null as sentinel" understanding of null.  
However, SQL typically takes a "null is unknown" approach.  In which case we 
would want the following behavior:
   
   | Input Value | Lookup Set | Skip Nulls | Result |
   | --- | --- | --- | --- |
   | 5 | [1, NULL] | any value | NULL |
   | 5 | [5, NULL] | any value | true |
   | NULL | [5] | any value | NULL |
   | NULL | [5, NULL] | any value | NULL |
   
   
   ### Component(s)
   
   C++


-- 
This is an automated message from the 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: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to