Hi, On 01/11/2026 21:07, best xmg wrote:
Thanks for the reproducer. The root cause is the operators in your test: the <> is not a valid negator of the =. The documentation [1] requires "(x A y) equals NOT (x B y) for all possible inputs x, y", but for a NULL input both return false. So NOT IN does not work well with them even on master. The same question gets different answers, depending on the length of the list: SELECT v <> ALL (ARRAY[1,2,3,4,5,6,7,8,9]::my_int[]) FROM (SELECT NULL::my_int AS v OFFSET 0) s; -- t (9 items) SELECT v <> ALL (ARRAY[1,2]::my_int[]) FROM (SELECT NULL::my_int AS v OFFSET 0) s; -- f (2 items) The reason: a list of 9 or more items is hashed. A hashed NOT IN looks for an equal item, so it calls = and inverts the result (since PG 15, 29f45e299e7). A shorter list is searched linearly, and that calls <>. The two ways agree only if <> is the negator of =. v5 hashes $1 too, so the example in your test now gets the hashed answer. Master's code for a hashed NOT IN depends on <> being the negator of =. So does the recent fix for non-strict operators [2]. So I chose not to fix it for now. [1]https://www.google.com/url?q=https://www.postgresql.org/docs/current/xoper-optimization.html%23XOPER-NEGATOR&source=gmail&ust=1793649679948000&sa=E [2]https://www.google.com/url?q=https://postgr.es/m/[email protected]&source=gmail&ust=1793649679948000&sa=E
Indeed, missed that info, thanks! Regards, Ayoub
