2 -- Sanity checks for common errors in making pg_operator table.
3 -- None of the SELECTs here should ever find any matching entries,
4 -- so the expected output is easy to maintain ;-).
5 -- A test failure indicates someone messed up an entry in pg_operator.h.
7 -- NB: run this test earlier than the create_operator test, because
8 -- that test creates some bogus operators...
11 -- Look for bogus data types.
13 SELECT p1.oid, p1.* FROM pg_operator AS p1
14 WHERE p1.oprleft != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = p1.oprleft);
16 SELECT p1.oid, p1.* FROM pg_operator AS p1
17 WHERE p1.oprright != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = p1.oprright);
19 SELECT p1.oid, p1.* FROM pg_operator AS p1
20 WHERE p1.oprresult != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = p1.oprresult);
22 -- Look for dangling links to other operators.
24 SELECT p1.oid, p1.* FROM pg_operator AS p1
25 WHERE p1.oprcom != 0 AND NOT
26 EXISTS(SELECT * FROM pg_operator AS p2 WHERE p2.oid = p1.oprcom);
28 SELECT p1.oid, p1.* FROM pg_operator AS p1
29 WHERE p1.oprnegate != 0 AND NOT
30 EXISTS(SELECT * FROM pg_operator AS p2 WHERE p2.oid = p1.oprnegate);
32 SELECT p1.oid, p1.* FROM pg_operator AS p1
33 WHERE p1.oprlsortop != 0 AND NOT
34 EXISTS(SELECT * FROM pg_operator AS p2 WHERE p2.oid = p1.oprlsortop);
36 SELECT p1.oid, p1.* FROM pg_operator AS p1
37 WHERE p1.oprrsortop != 0 AND NOT
38 EXISTS(SELECT * FROM pg_operator AS p2 WHERE p2.oid = p1.oprrsortop);
40 -- FIXME: how can we test for a dangling OPRCODE value?
42 -- Look for conflicting operator definitions (same names and input datatypes).
44 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
45 FROM pg_operator AS p1, pg_operator AS p2
46 WHERE p1.oid != p2.oid AND
47 p1.oprname = p2.oprname AND
48 p1.oprkind = p2.oprkind AND
49 p1.oprleft = p2.oprleft AND
50 p1.oprright = p2.oprright;
52 -- Look for commutative operators that don't commute.
53 -- DEFINITIONAL NOTE: If A.oprcom = B, then x A y has the same result as y B x.
54 -- We expect that B will always say that B.oprcom = A as well; that's not
55 -- inherently essential, but it would be inefficient not to mark it so.
57 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
58 FROM pg_operator AS p1, pg_operator AS p2
59 WHERE p1.oprcom = p2.oid AND
61 p1.oprleft != p2.oprright OR
62 p1.oprright != p2.oprleft OR
63 p1.oprresult != p2.oprresult OR
66 -- Look for negatory operators that don't agree.
67 -- DEFINITIONAL NOTE: If A.oprnegate = B, then both A and B must yield
68 -- boolean results, and (x A y) == ! (x B y), or the equivalent for
69 -- single-operand operators.
70 -- We expect that B will always say that B.oprnegate = A as well; that's not
71 -- inherently essential, but it would be inefficient not to mark it so.
72 -- NOTE hardwired assumption that type bool has OID 16.
74 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
75 FROM pg_operator AS p1, pg_operator AS p2
76 WHERE p1.oprnegate = p2.oid AND
77 (p1.oprkind != p2.oprkind OR
78 p1.oprleft != p2.oprleft OR
79 p1.oprright != p2.oprright OR
82 p1.oid != p2.oprnegate);
84 -- Look for mergesort operators that don't match.
85 -- A mergesort link leads from an '=' operator to the
86 -- sort operator ('<' operator) that's appropriate for
87 -- its left-side or right-side data type.
89 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
90 FROM pg_operator AS p1, pg_operator AS p2
91 WHERE p1.oprlsortop = p2.oid AND
92 (p1.oprname != '=' OR p2.oprname != '<' OR
93 p1.oprkind != 'b' OR p2.oprkind != 'b' OR
94 p1.oprleft != p2.oprleft OR
95 p1.oprleft != p2.oprright OR
100 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
101 FROM pg_operator AS p1, pg_operator AS p2
102 WHERE p1.oprrsortop = p2.oid AND
103 (p1.oprname != '=' OR p2.oprname != '<' OR
104 p1.oprkind != 'b' OR p2.oprkind != 'b' OR
105 p1.oprright != p2.oprleft OR
106 p1.oprright != p2.oprright OR
107 p1.oprresult != 16 OR
108 p2.oprresult != 16 OR