> If you are using NOT IN, then once you find a NULL in the outer input (if > the outer input is the in-list: clearly you can't reverse the two inputs in > this case), you don't even need to finish reading the outer input, nor > start reading the inner input, because all rows are automatically excluded > by the weird semantics of NOT IN.
The point I'm trying to make is that the goal of most join types is to give an answer without having necessarily read all of either input. For instance, if we tried to do this with a mergejoin it wouldn't work reliably: it might suppose that it could accept an outer row on the basis of no match in a higher-order sort column before it'd reached any nulls appearing in lower-order sort columns.
You might be right that we could hot-wire the hash join case in particular, but I'm failing to see the point of expending lots of extra effort in order to deliver a useless answer faster. If there are NULLs in the inner input, then NOT IN is simply the wrong query to make, and giving an empty output in a relatively short amount of time isn't going to help clue the user in on that. (If the SQL standard would let us do so, I'd be arguing for throwing an error if we found a NULL.)
This got me thinking. It is probably a bit useless and wrong to perform a NOT IN when the subquery in the IN() clause can have NULL values, so I guess in any realistic useful case, the user will either have a NOT NULL constraint on the columns, or they'll do a WHERE col IS NOT NULL, so I should likely also allow a query such as:
SELECT * FROM a WHERE id NOT IN(SELECT nullable_col FROM b WHERE nullable_col IS NOT NULL);
to also perform an ANTI JOIN. I think it's just a matter of changing targetListIsGuaranteedNotToHaveNulls so that if it does not find the NOT NULL constraint, to check the WHERE clause of the query to see if there's any not null quals.
I'm about to put this to the test, but if it works then I think it should cover many more cases for using NOT IN(), I guess we're only leaving out function calls and calculations in the target list.
