Re: Convert ALL SubLinks to ANY SubLinks

Hi Richard,

Thanks for working on this optimization.

I found a valid custom operator class for which v2 turns a working
row-comparison ALL query into an error. Using the existing int4_tbl
regression table:

CREATE OPERATOR #< (LEFTARG = integer, RIGHTARG = integer,
FUNCTION = int4lt, NEGATOR = #>=);
CREATE OPERATOR #>= (LEFTARG = integer, RIGHTARG = integer,
FUNCTION = int4ge, NEGATOR = #<);
CREATE OPERATOR CLASS regress_all_int_ops
FOR TYPE integer USING btree AS
OPERATOR 1 #<,
OPERATOR 2 <=,
OPERATOR 3 =,
OPERATOR 4 >=,
OPERATOR 5 >,
FUNCTION 1 btint4cmp(integer, integer);

SELECT f1 FROM int4_tbl
WHERE (f1, f1) OPERATOR(#<) ALL
(SELECT f1, f1 FROM int4_tbl WHERE f1 = 123456)
ORDER BY f1;

Master returns -2147483647, -123456 and 0. On v2 I get:

ERROR: operator 16417 is not a member of opfamily 16419

NOT ALL fails too. The operator class passes amvalidate(). Its strategy
4 operator is the built-in >=, while the negator of #< is #>=. Both >=
and #>= call int4ge, but only >= belongs to this family.

negate_sublink_testexpr() replaces the operators with their negators
but keeps rcexpr->opfamilies. ExecInitExprRec() then calls
get_op_opfamily_properties() with #>= and the original family, which
causes the error.

The attached patch checks op_in_opfamily() before making that change.
If a negator is outside the original family, it leaves the ALL sublink
unchanged.

I also considered two other fixes:

1. Find a B-tree family containing the negator and update the
corresponding entry in rcexpr->opfamilies.
2. Keep the original family and use get_opfamily_member() to find its
operator for the opposite comparison. For example, when negating <,
use the family's strategy 4 operator (>=).

In the built-in B-tree families and extensions I checked, including
citext, pgvector and pg_roaringbitmap, the negators were already in the
same family. So I think the attached small check is sufficient.

The patch includes regression tests for ALL and NOT ALL; both fail on
v2 and return the expected rows with the fix.

Regards,
Rui