Narrow multi-column NOT IN UNKNOWN checks by treating NOT NULL tuple elements as scope keys #26088

Is your feature request related to a problem or challenge?

A multi-column (a, b) NOT IN (SELECT x, y ...) (#25737, #19857) plans as a null-aware hash join that decides UNKNOWN per build row. Without correlation keys, every row with a NULL in any tuple element is paired with every row on the other side, and each pair is checked element by element for a definite mismatch. The cost grows with the product of the two sides. @comphead measured it in #19857 (comment), with equal row counts on both sides and no matches:

Describe the solution you'd like

A tuple element that is NOT NULL on both sides can never make the comparison UNKNOWN. Such an element could act as a correlation scope key instead of a value key, so the hash lookup on the scope keys narrows the candidate pairs, as it already does for correlated scalar NOT IN. For example, in (a, b) NOT IN (SELECT x, y ...) with b and y NOT NULL, a NULL a would only be paired with the subquery rows whose y equals its b.

This needs the value keys reordered so that the NOT NULL elements follow the nullable ones (HashJoinExec::null_aware_value_keys counts only the leading value keys), or a way to mark individual keys as scope keys.

Describe alternatives you've considered

Building the join on the subquery side instead (#26089), which also avoids keeping the outer side in memory.

Additional context

The null_aware_join benchmark suite has Q10 to Q12 for these shapes (no NULLs, NULLs in the subquery, NULLs in the outer side).