|
| 1 | +-- |
| 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. |
| 6 | +-- |
| 7 | +-- NB: run this test earlier than the create_operator test, because |
| 8 | +-- that test creates some bogus operators... |
| 9 | +-- |
| 10 | + |
| 11 | +-- Look for bogus data types. |
| 12 | + |
| 13 | +SELECTp1.oid, p1.*FROM pg_operatorAS p1 |
| 14 | +WHEREp1.oprleft!=0AND NOT EXISTS(SELECT*FROM pg_typeAS t1WHEREt1.oid=p1.oprleft); |
| 15 | + |
| 16 | +SELECTp1.oid, p1.*FROM pg_operatorAS p1 |
| 17 | +WHEREp1.oprright!=0AND NOT EXISTS(SELECT*FROM pg_typeAS t1WHEREt1.oid=p1.oprright); |
| 18 | + |
| 19 | +SELECTp1.oid, p1.*FROM pg_operatorAS p1 |
| 20 | +WHEREp1.oprresult!=0AND NOT EXISTS(SELECT*FROM pg_typeAS t1WHEREt1.oid=p1.oprresult); |
| 21 | + |
| 22 | +-- Look for dangling links to other operators. |
| 23 | + |
| 24 | +SELECTp1.oid, p1.*FROM pg_operatorAS p1 |
| 25 | +WHEREp1.oprcom!=0AND NOT |
| 26 | + EXISTS(SELECT*FROM pg_operatorAS p2WHEREp2.oid=p1.oprcom); |
| 27 | + |
| 28 | +SELECTp1.oid, p1.*FROM pg_operatorAS p1 |
| 29 | +WHEREp1.oprnegate!=0AND NOT |
| 30 | + EXISTS(SELECT*FROM pg_operatorAS p2WHEREp2.oid=p1.oprnegate); |
| 31 | + |
| 32 | +SELECTp1.oid, p1.*FROM pg_operatorAS p1 |
| 33 | +WHEREp1.oprlsortop!=0AND NOT |
| 34 | + EXISTS(SELECT*FROM pg_operatorAS p2WHEREp2.oid=p1.oprlsortop); |
| 35 | + |
| 36 | +SELECTp1.oid, p1.*FROM pg_operatorAS p1 |
| 37 | +WHEREp1.oprrsortop!=0AND NOT |
| 38 | + EXISTS(SELECT*FROM pg_operatorAS p2WHEREp2.oid=p1.oprrsortop); |
| 39 | + |
| 40 | +-- FIXME: how can we test for a dangling OPRCODE value? |
| 41 | + |
| 42 | +-- Look for conflicting operator definitions (same names and input datatypes). |
| 43 | + |
| 44 | +SELECTp1.oid,p1.oprcode,p2.oid,p2.oprcode |
| 45 | +FROM pg_operatorAS p1, pg_operatorAS p2 |
| 46 | +WHEREp1.oid!=p2.oidAND |
| 47 | +p1.oprname=p2.oprnameAND |
| 48 | +p1.oprkind=p2.oprkindAND |
| 49 | +p1.oprleft=p2.oprleftAND |
| 50 | +p1.oprright=p2.oprright; |
| 51 | + |
| 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. |
| 56 | + |
| 57 | +SELECTp1.oid,p1.oprcode,p2.oid,p2.oprcode |
| 58 | +FROM pg_operatorAS p1, pg_operatorAS p2 |
| 59 | +WHEREp1.oprcom=p2.oidAND |
| 60 | + (p1.oprkind!='b'OR |
| 61 | +p1.oprleft!=p2.oprrightOR |
| 62 | +p1.oprright!=p2.oprleftOR |
| 63 | +p1.oprresult!=p2.oprresultOR |
| 64 | +p1.oid!=p2.oprcom); |
| 65 | + |
| 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. |
| 73 | + |
| 74 | +SELECTp1.oid,p1.oprcode,p2.oid,p2.oprcode |
| 75 | +FROM pg_operatorAS p1, pg_operatorAS p2 |
| 76 | +WHEREp1.oprnegate=p2.oidAND |
| 77 | + (p1.oprkind!=p2.oprkindOR |
| 78 | +p1.oprleft!=p2.oprleftOR |
| 79 | +p1.oprright!=p2.oprrightOR |
| 80 | +p1.oprresult!=16OR |
| 81 | +p2.oprresult!=16OR |
| 82 | +p1.oid!=p2.oprnegate); |
| 83 | + |
| 84 | +-- Look for sort operators that don't match. |
| 85 | +-- A sort link normally leads from an '=' |
| 86 | +-- operator to the matching '<' operator. |
| 87 | +-- Sort links are not commutative. |
| 88 | + |
| 89 | +SELECTp1.oid,p1.oprcode,p2.oid,p2.oprcode |
| 90 | +FROM pg_operatorAS p1, pg_operatorAS p2 |
| 91 | +WHEREp1.oprlsortop=p2.oidAND |
| 92 | + (p1.oprname!='='OR |
| 93 | +p1.oprkind!='b'ORp2.oprkind!='b'OR |
| 94 | +p1.oprleft!=p2.oprleftOR |
| 95 | +p1.oprright!=p2.oprrightOR |
| 96 | +p1.oprresult!=16OR |
| 97 | +p2.oprresult!=16OR |
| 98 | +p1.oprrsortop=0); |
| 99 | + |
| 100 | +SELECTp1.oid,p1.oprcode,p2.oid,p2.oprcode |
| 101 | +FROM pg_operatorAS p1, pg_operatorAS p2 |
| 102 | +WHEREp1.oprrsortop=p2.oidAND |
| 103 | + (p1.oprname!='='OR |
| 104 | +p1.oprkind!='b'ORp2.oprkind!='b'OR |
| 105 | +p1.oprleft!=p2.oprleftOR |
| 106 | +p1.oprright!=p2.oprrightOR |
| 107 | +p1.oprresult!=16OR |
| 108 | +p2.oprresult!=16OR |
| 109 | +p1.oprlsortop=0); |