Re: NOT IN subquery optimization

David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> On Sun, 3 Mar 2019 at 17:11, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> (At the code level, this is implicit in the fact that the comparison
>> function will be called via FunctionCall2Coll or a sibling, and those
>> all throw an error if the called function returns NULL.)

> Ah okay. I can get it to misbehave by setting fcinfo->isnull = true in
> the debugger from int4eq(). I see the NULL result there is not
> verified as that's just translated into "false" by ExecInterpExpr()'s
> EEOP_QUAL case. If you're saying something doing that is
> fundamentally broken, then I guess we're okay.

No, what I'm thinking of is this bit in _bt_compare:

result = DatumGetInt32(FunctionCall2Coll(&scankey->sk_func,
scankey->sk_collation,
datum,
scankey->sk_argument));

You absolutely will get errors during btree insertions and searches
if a datatype's btree comparison functions ever return NULL (for
non-NULL inputs).

For hash indexes, that kind of restriction only directly applies to
hash-calculation functions, which perhaps are not as tightly tied to the
opclass's user-visible operators as is the case for btree opclasses.
But I think you might be able to find places in hash join or grouping
that are calling the actual equality operator and not allowing for it
to return NULL.

regards, tom lane