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
nocfbot-0001-Fix-operator-family-checks-when-negating-ALL-row-com.patch
Description: Binary data
