RESET SESSION, plus related new DDL commands. Patch from Marko Kreen,

[postgresql] / src / test / regress / sql / opr_sanity.sql

diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql
index cbf9baf6728c17d17b8c5f0374a29c764eb5fae2..f93a71ddf9c65a5ef1c524e3cce46a0db840fd52 100644 (file)
--- a/src/test/regress/sql/opr_sanity.sql
+++ b/src/test/regress/sql/opr_sanity.sql
@@ -315,6 +315,14 @@ SELECT p1.oid, p1.oprname FROM pg_operator AS p1
 WHERE (p1.oprcanmerge OR p1.oprcanhash) AND NOT
     (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND p1.oprcom != 0);
 
+-- What's more, the commutator had better be mergejoinable/hashjoinable too.
+
+SELECT p1.oid, p1.oprname, p2.oid, p2.oprname
+FROM pg_operator AS p1, pg_operator AS p2
+WHERE p1.oprcom = p2.oid AND
+    (p1.oprcanmerge != p2.oprcanmerge OR
+     p1.oprcanhash != p2.oprcanhash);
+
 -- Mergejoinable operators should appear as equality members of btree index
 -- opfamilies.
 
@@ -656,13 +664,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 +703,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