[C++] Add an sql-compatible is_in variant

[westonpace]

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++

[R-JunmingChen]

take

[R-JunmingChen]

This could be a new compute function or it could be new options for the existing is_in function.

Let me investigate, I will leave a message about my choice before I implement it

[westonpace]

Thanks

[R-JunmingChen]

I think adding a new Function named in could be a good way. Let me implement it

[R-JunmingChen]

Hi, @westonpace, I have implemented an In Function. However, the test codes for is_in in scalar_set_lookup_test.cc are very large. Do I need to add a coresponding amount of tests or I just need to test the different parts between In and is_in? Besides, do I also need to add a benchmark for In in scalar_set_lookup_benchmark.cc?

[jorisvandenbossche]

@westonpace essentially, the behaviour you propose is to have a variant of is_in that is actually the equivalent of (arr == val1) or (arr == val2) or ..., right?
(i.e. how you can explain the "is_in" function, being an easier and more performant version of checking equality for multiple values, although at the moment it's not exactly equivalent when it comes to null handling)

[westonpace]

@westonpace essentially, the behaviour you propose is to have a variant of is_in that is actually the equivalent of (arr == val1) or (arr == val2) or ..., right?

Yes. That is correct. I encountered this when working on #34834 because Substrait translates SQL's WHERE x IN [1, 5] to a SingularOrList and I want to translate that SingularOrList to is_in to get better performance.

[ianmcook]

I have some doubt about this one case, in which the lookup set contains a null but the input value is non-null:

Input Value	Lookup Set	Skip Nulls	Result
5	[1, NULL]	any value	NULL

I think it is not so simple to say that SQL engines return a null result in this case.

For example, DuckDB returns a different result in this case depending on whether you are using IN or list_contains:

duckdb> SELECT 5 IN (VALUES (1), (NULL)) AS result;
┌────────┐
│ result │
╞════════╡
│  NULL  │
└────────┘

duckdb> SELECT list_contains([1,  NULL], 5) AS result;
┌────────┐
│ result │
╞════════╡
│  false │
└────────┘

I wonder whether we really need to add two new options, one to handle the semantics of nulls in the value set and one to handle the semantics of nulls in the lookup set.

[ianmcook]

P.S. to be clear: the behavior of DuckDB's list_contains cannot be reproduced using the existing is_in options. The case that demonstrates that is the last one in Weston's table:

Input Value	Lookup Set	Skip Nulls	Result
NULL	[5, NULL]	any value	NULL

duckdb> SELECT list_contains([5, NULL], NULL) AS result;
┌────────┐
│ result │
╞════════╡
│  NULL  │
└────────┘

This is why I think maybe we need two new options.

[ianmcook]

Regarding my two comments above ☝️
I suggested a solution here: #36739 (comment)