Add int2-vs-int8 comparison operators. These are now necessary because

the planner may try to generate them as a result of transitivity of the
existing int2-vs-int4 and int4-vs-int8 operators.  In fact, it is now
necessary that mergejoinable cross-datatype operators form closed sets.
Add an opr_sanity regress test to detect missing operators.

2 files changed, 33 insertions, 0 deletions

diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index e784486f81d..18582c1f5bb 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -310,6 +310,23 @@ WHERE p1.oprlsortop != 0 AND NOT
 -----+---------
 (0 rows)
 
+-- Mergejoinable operators across datatypes must come in closed sets, 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 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.
+SELECT p1.oid, p2.oid FROM pg_operator AS p1, pg_operator AS p2
+WHERE p1.oprlsortop != p1.oprrsortop AND
+      p1.oprrsortop = p2.oprlsortop AND
+      p2.oprlsortop != p2.oprrsortop AND
+      NOT EXISTS (SELECT 1 FROM pg_operator p3 WHERE
+      p3.oprlsortop = p1.oprlsortop AND p3.oprrsortop = p2.oprrsortop);
+ oid | oid 
+-----+-----
+(0 rows)
+
 -- Hashing only works on simple equality operators "type = sametype",
 -- since the hash itself depends on the bitwise representation of the type.
 -- Check that allegedly hashable operators look like they might be "=".
diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql
index fb43748a1cb..a79d0eae754 100644
--- a/src/test/regress/sql/opr_sanity.sql
+++ b/src/test/regress/sql/opr_sanity.sql
@@ -256,6 +256,22 @@ WHERE p1.oprlsortop != 0 AND NOT
         p2.oprright = p1.oprright AND
         p2.oprkind = 'b');
 
+-- Mergejoinable operators across datatypes must come in closed sets, 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 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.
+
+SELECT p1.oid, p2.oid FROM pg_operator AS p1, pg_operator AS p2
+WHERE p1.oprlsortop != p1.oprrsortop AND
+      p1.oprrsortop = p2.oprlsortop AND
+      p2.oprlsortop != p2.oprrsortop AND
+      NOT EXISTS (SELECT 1 FROM pg_operator p3 WHERE
+      p3.oprlsortop = p1.oprlsortop AND p3.oprrsortop = p2.oprrsortop);
+
+
 -- Hashing only works on simple equality operators "type = sametype",
 -- since the hash itself depends on the bitwise representation of the type.
 -- Check that allegedly hashable operators look like they might be "=".