Add support for cross-type hashing in hashed subplans (hashed IN/NOT IN cases

that aren't turned into true joins).  Since this is the last missing bit of
infrastructure, go ahead and fill out the hash integer_ops and float_ops
opfamilies with cross-type operators.  The operator family project is now
DONE ... er, except for documentation ...

2 files changed, 59 insertions, 10 deletions

diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index fcbfe3cad51..2bc25de0525 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -829,13 +829,16 @@ WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
 ------------+---------+---------+--------
 (0 rows)
 
--- Multiple-datatype btree opclasses should provide closed sets of equality
+-- Multiple-datatype btree opfamilies should provide closed sets of equality
 -- operators; that is if you provide int2 = int4 and int4 = int8 then you
--- must also provide int2 = int8 (and commutators of all these).  This is
--- necessary because the planner tries to deduce additional qual clauses from
+-- should also provide int2 = int8 (and commutators of all these).  This is
+-- important because the planner tries to deduce additional qual clauses from
 -- transitivity of mergejoinable operators.  If there are clauses
--- int2var = int4var and int4var = int8var, the planner will deduce
--- int2var = int8var ... and it had better have a way to represent it.
+-- int2var = int4var and int4var = int8var, the planner will want to deduce
+-- int2var = int8var ... so there should be a way to represent that.  While
+-- a missing cross-type operator is now only an efficiency loss rather than
+-- an error condition, it still seems reasonable to insist that all built-in
+-- opfamilies be complete.
 -- check commutative closure
 SELECT p1.amoplefttype, p1.amoprighttype
 FROM pg_amop AS p1
@@ -870,6 +873,28 @@ WHERE p1.amopfamily = p2.amopfamily AND
 --------------+---------------+---------------
 (0 rows)
 
+-- We also expect that built-in multiple-datatype hash opfamilies provide
+-- complete sets of cross-type operators.  Again, this isn't required, but
+-- it is reasonable to expect it for built-in opfamilies.
+-- if same family has x=x and y=y, it should have x=y
+SELECT p1.amoplefttype, p2.amoplefttype
+FROM pg_amop AS p1, pg_amop AS p2
+WHERE p1.amopfamily = p2.amopfamily AND
+    p1.amoplefttype = p1.amoprighttype AND
+    p2.amoplefttype = p2.amoprighttype AND
+    p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
+    p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
+    p1.amopstrategy = 1 AND p2.amopstrategy = 1 AND
+    p1.amoplefttype != p2.amoplefttype AND
+    NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
+                 p3.amopfamily = p1.amopfamily AND
+                 p3.amoplefttype = p1.amoplefttype AND
+                 p3.amoprighttype = p2.amoplefttype AND
+                 p3.amopstrategy = 1);
+ amoplefttype | amoplefttype 
+--------------+--------------
+(0 rows)
+
 -- **************** pg_amproc ****************
 -- Look for illegal values in pg_amproc fields
 SELECT p1.amprocfamily, p1.amprocnum
diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql
index cbf9baf6728..9496422458e 100644
--- a/src/test/regress/sql/opr_sanity.sql
+++ b/src/test/regress/sql/opr_sanity.sql
@@ -656,13 +656,16 @@ WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
     p1.amoplefttype != p1.amoprighttype AND
     p3.provolatile = 'v';
 
--- Multiple-datatype btree opclasses should provide closed sets of equality
+-- Multiple-datatype btree opfamilies should provide closed sets of equality
 -- operators; that is if you provide int2 = int4 and int4 = int8 then you
--- must also provide int2 = int8 (and commutators of all these).  This is
--- necessary because the planner tries to deduce additional qual clauses from
+-- should also provide int2 = int8 (and commutators of all these).  This is
+-- important because the planner tries to deduce additional qual clauses from
 -- transitivity of mergejoinable operators.  If there are clauses
--- int2var = int4var and int4var = int8var, the planner will deduce
--- int2var = int8var ... and it had better have a way to represent it.
+-- int2var = int4var and int4var = int8var, the planner will want to deduce
+-- int2var = int8var ... so there should be a way to represent that.  While
+-- a missing cross-type operator is now only an efficiency loss rather than
+-- an error condition, it still seems reasonable to insist that all built-in
+-- opfamilies be complete.
 
 -- check commutative closure
 SELECT p1.amoplefttype, p1.amoprighttype
@@ -692,6 +695,27 @@ WHERE p1.amopfamily = p2.amopfamily AND
                  p3.amoprighttype = p2.amoprighttype AND
                  p3.amopstrategy = 3);
 
+-- We also expect that built-in multiple-datatype hash opfamilies provide
+-- complete sets of cross-type operators.  Again, this isn't required, but
+-- it is reasonable to expect it for built-in opfamilies.
+
+-- if same family has x=x and y=y, it should have x=y
+SELECT p1.amoplefttype, p2.amoplefttype
+FROM pg_amop AS p1, pg_amop AS p2
+WHERE p1.amopfamily = p2.amopfamily AND
+    p1.amoplefttype = p1.amoprighttype AND
+    p2.amoplefttype = p2.amoprighttype AND
+    p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
+    p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
+    p1.amopstrategy = 1 AND p2.amopstrategy = 1 AND
+    p1.amoplefttype != p2.amoplefttype AND
+    NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
+                 p3.amopfamily = p1.amopfamily AND
+                 p3.amoplefttype = p1.amoplefttype AND
+                 p3.amoprighttype = p2.amoplefttype AND
+                 p3.amopstrategy = 1);
+
+
 -- **************** pg_amproc ****************
 
 -- Look for illegal values in pg_amproc fields