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]
