diff options
Diffstat (limited to 'src/test/regress/expected/opr_sanity.out')
| -rw-r--r-- | src/test/regress/expected/opr_sanity.out | 169 |
1 files changed, 111 insertions, 58 deletions
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index c56ec07721..e7712b13bf 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -48,7 +48,7 @@ WHERE p1.oid != p2.oid AND -----+---------+-----+--------- (0 rows) --- Considering only built-in procs (prolang = 11/12), look for multiple uses +-- Considering only built-in procs (prolang = 12), look for multiple uses -- of the same internal function (ie, matching prosrc fields). It's OK to -- have several entries with different pronames for the same internal function, -- but conflicts in the number of arguments and other critical items should @@ -57,14 +57,14 @@ SELECT p1.oid, p1.proname, p2.oid, p2.proname FROM pg_proc AS p1, pg_proc AS p2 WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND - (p1.prolang = 11 OR p1.prolang = 12) AND - (p2.prolang = 11 OR p2.prolang = 12) AND + p1.prolang = 12 AND p2.prolang = 12 AND (p1.prolang != p2.prolang OR - p1.proisinh != p2.proisinh OR + p1.proisagg != p2.proisagg OR p1.proistrusted != p2.proistrusted OR + p1.proisstrict != p2.proisstrict OR + p1.proretset != p2.proretset OR p1.provolatile != p2.provolatile OR - p1.pronargs != p2.pronargs OR - p1.proretset != p2.proretset); + p1.pronargs != p2.pronargs); oid | proname | oid | proname -----+---------+-----+--------- (0 rows) @@ -75,12 +75,14 @@ WHERE p1.oid != p2.oid AND -- That's not wrong, necessarily, but we make lists of all the types being -- so treated. Note that the expected output of this part of the test will -- need to be modified whenever new pairs of types are made binary-equivalent! +-- Note: ignore aggregate functions here, since they all point to the same +-- dummy built-in function. SELECT DISTINCT p1.prorettype, p2.prorettype FROM pg_proc AS p1, pg_proc AS p2 WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND - (p1.prolang = 11 OR p1.prolang = 12) AND - (p2.prolang = 11 OR p2.prolang = 12) AND + p1.prolang = 12 AND p2.prolang = 12 AND + NOT p1.proisagg AND NOT p2.proisagg AND (p1.prorettype < p2.prorettype); prorettype | prorettype ------------+------------ @@ -92,8 +94,8 @@ SELECT DISTINCT p1.proargtypes[0], p2.proargtypes[0] FROM pg_proc AS p1, pg_proc AS p2 WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND - (p1.prolang = 11 OR p1.prolang = 12) AND - (p2.prolang = 11 OR p2.prolang = 12) AND + p1.prolang = 12 AND p2.prolang = 12 AND + NOT p1.proisagg AND NOT p2.proisagg AND (p1.proargtypes[0] < p2.proargtypes[0]); proargtypes | proargtypes -------------+------------- @@ -106,8 +108,8 @@ SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1] FROM pg_proc AS p1, pg_proc AS p2 WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND - (p1.prolang = 11 OR p1.prolang = 12) AND - (p2.prolang = 11 OR p2.prolang = 12) AND + p1.prolang = 12 AND p2.prolang = 12 AND + NOT p1.proisagg AND NOT p2.proisagg AND (p1.proargtypes[1] < p2.proargtypes[1]); proargtypes | proargtypes -------------+------------- @@ -119,8 +121,8 @@ SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2] FROM pg_proc AS p1, pg_proc AS p2 WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND - (p1.prolang = 11 OR p1.prolang = 12) AND - (p2.prolang = 11 OR p2.prolang = 12) AND + p1.prolang = 12 AND p2.prolang = 12 AND + NOT p1.proisagg AND NOT p2.proisagg AND (p1.proargtypes[2] < p2.proargtypes[2]); proargtypes | proargtypes -------------+------------- @@ -131,8 +133,8 @@ SELECT DISTINCT p1.proargtypes[3], p2.proargtypes[3] FROM pg_proc AS p1, pg_proc AS p2 WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND - (p1.prolang = 11 OR p1.prolang = 12) AND - (p2.prolang = 11 OR p2.prolang = 12) AND + p1.prolang = 12 AND p2.prolang = 12 AND + NOT p1.proisagg AND NOT p2.proisagg AND (p1.proargtypes[3] < p2.proargtypes[3]); proargtypes | proargtypes -------------+------------- @@ -143,8 +145,8 @@ SELECT DISTINCT p1.proargtypes[4], p2.proargtypes[4] FROM pg_proc AS p1, pg_proc AS p2 WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND - (p1.prolang = 11 OR p1.prolang = 12) AND - (p2.prolang = 11 OR p2.prolang = 12) AND + p1.prolang = 12 AND p2.prolang = 12 AND + NOT p1.proisagg AND NOT p2.proisagg AND (p1.proargtypes[4] < p2.proargtypes[4]); proargtypes | proargtypes -------------+------------- @@ -154,8 +156,8 @@ SELECT DISTINCT p1.proargtypes[5], p2.proargtypes[5] FROM pg_proc AS p1, pg_proc AS p2 WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND - (p1.prolang = 11 OR p1.prolang = 12) AND - (p2.prolang = 11 OR p2.prolang = 12) AND + p1.prolang = 12 AND p2.prolang = 12 AND + NOT p1.proisagg AND NOT p2.proisagg AND (p1.proargtypes[5] < p2.proargtypes[5]); proargtypes | proargtypes -------------+------------- @@ -165,8 +167,8 @@ SELECT DISTINCT p1.proargtypes[6], p2.proargtypes[6] FROM pg_proc AS p1, pg_proc AS p2 WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND - (p1.prolang = 11 OR p1.prolang = 12) AND - (p2.prolang = 11 OR p2.prolang = 12) AND + p1.prolang = 12 AND p2.prolang = 12 AND + NOT p1.proisagg AND NOT p2.proisagg AND (p1.proargtypes[6] < p2.proargtypes[6]); proargtypes | proargtypes -------------+------------- @@ -176,13 +178,29 @@ SELECT DISTINCT p1.proargtypes[7], p2.proargtypes[7] FROM pg_proc AS p1, pg_proc AS p2 WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND - (p1.prolang = 11 OR p1.prolang = 12) AND - (p2.prolang = 11 OR p2.prolang = 12) AND + p1.prolang = 12 AND p2.prolang = 12 AND + NOT p1.proisagg AND NOT p2.proisagg AND (p1.proargtypes[7] < p2.proargtypes[7]); proargtypes | proargtypes -------------+------------- (0 rows) +-- If a proc is marked as an implicit cast, then it should be something that +-- the system might actually use as a cast function: name same as the name +-- of its output type, and either one arg that's a different type, or two +-- args where the first is the same as the output type and the second is int4. +SELECT p1.oid, p1.proname +FROM pg_proc as p1 +WHERE p1.proimplicit AND + (NOT EXISTS (SELECT 1 FROM pg_type t WHERE t.oid = p1.prorettype AND + t.typname = p1.proname) OR + NOT ((p1.pronargs = 1 AND p1.proargtypes[0] != prorettype) OR + (p1.pronargs = 2 AND p1.proargtypes[0] = prorettype AND + p1.proargtypes[1] = 23))); + oid | proname +-----+--------- +(0 rows) + -- **************** pg_operator **************** -- Look for illegal values in pg_operator fields. SELECT p1.oid, p1.oprname @@ -238,6 +256,7 @@ WHERE p1.oprcom = p2.oid AND -- single-operand operators. -- We expect that B will always say that B.oprnegate = A as well; that's not -- inherently essential, but it would be inefficient not to mark it so. +-- Also, A and B had better not be the same operator. SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode FROM pg_operator AS p1, pg_operator AS p2 WHERE p1.oprnegate = p2.oid AND @@ -246,7 +265,8 @@ WHERE p1.oprnegate = p2.oid AND p1.oprright != p2.oprright OR p1.oprresult != 16 OR p2.oprresult != 16 OR - p1.oid != p2.oprnegate); + p1.oid != p2.oprnegate OR + p1.oid = p2.oid); oid | oprcode | oid | oprcode -----+---------+-----+--------- (0 rows) @@ -455,19 +475,38 @@ WHERE p1.oprjoin = p2.oid AND -- **************** pg_aggregate **************** -- Look for illegal values in pg_aggregate fields. -SELECT p1.oid, p1.aggname +SELECT ctid, aggfnoid::oid FROM pg_aggregate as p1 -WHERE aggtransfn = 0 OR aggtranstype = 0 OR aggfinaltype = 0; - oid | aggname +WHERE aggfnoid = 0 OR aggtransfn = 0 OR aggtranstype = 0; + ctid | aggfnoid +------+---------- +(0 rows) + +-- Make sure the matching pg_proc entry is sensible, too. +SELECT a.aggfnoid::oid, p.proname +FROM pg_aggregate as a, pg_proc as p +WHERE a.aggfnoid = p.oid AND + (NOT p.proisagg OR p.pronargs != 1 OR p.proretset); + aggfnoid | proname +----------+--------- +(0 rows) + +-- Make sure there are no proisagg pg_proc entries without matches. +SELECT oid, proname +FROM pg_proc as p +WHERE p.proisagg AND + NOT EXISTS (SELECT 1 FROM pg_aggregate a WHERE a.aggfnoid = p.oid); + oid | proname -----+--------- (0 rows) -- If there is no finalfn then the output type must be the transtype. -SELECT p1.oid, p1.aggname -FROM pg_aggregate as p1 -WHERE p1.aggfinalfn = 0 AND p1.aggfinaltype != p1.aggtranstype; - oid | aggname ------+--------- +SELECT a.aggfnoid::oid, p.proname +FROM pg_aggregate as a, pg_proc as p +WHERE a.aggfnoid = p.oid AND + a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype; + aggfnoid | proname +----------+--------- (0 rows) -- Cross-check transfn against its entry in pg_proc. @@ -476,41 +515,44 @@ WHERE p1.aggfinalfn = 0 AND p1.aggfinaltype != p1.aggtranstype; -- implemented using int4larger/int4smaller. Until we have -- some cleaner way of dealing with binary-equivalent types, just leave -- those two tuples in the expected output. -SELECT p1.oid, p1.aggname, p2.oid, p2.proname -FROM pg_aggregate AS p1, pg_proc AS p2 -WHERE p1.aggtransfn = p2.oid AND +SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname +FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2 +WHERE a.aggfnoid = p.oid AND + a.aggtransfn = p2.oid AND (p2.proretset OR - p1.aggtranstype != p2.prorettype OR - p1.aggtranstype != p2.proargtypes[0] OR - NOT ((p2.pronargs = 2 AND p1.aggbasetype = p2.proargtypes[1]) OR - (p2.pronargs = 1 AND p1.aggbasetype = 0))); - oid | aggname | oid | proname --------+---------+-----+------------- - 10021 | max | 768 | int4larger - 10037 | min | 769 | int4smaller + a.aggtranstype != p2.prorettype OR + a.aggtranstype != p2.proargtypes[0] OR + NOT ((p2.pronargs = 2 AND p.proargtypes[0] = p2.proargtypes[1]) OR + (p2.pronargs = 1 AND p.proargtypes[0] = 0))); + aggfnoid | proname | oid | proname +----------+---------+-----+------------- + 2121 | max | 768 | int4larger + 2137 | min | 769 | int4smaller (2 rows) -- Cross-check finalfn (if present) against its entry in pg_proc. -- FIXME: what about binary-compatible types? -SELECT p1.oid, p1.aggname, p2.oid, p2.proname -FROM pg_aggregate AS p1, pg_proc AS p2 -WHERE p1.aggfinalfn = p2.oid AND - (p2.proretset OR p1.aggfinaltype != p2.prorettype OR +SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname +FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2 +WHERE a.aggfnoid = p.oid AND + a.aggfinalfn = p2.oid AND + (p2.proretset OR p.prorettype != p2.prorettype OR p2.pronargs != 1 OR - p1.aggtranstype != p2.proargtypes[0]); - oid | aggname | oid | proname ------+---------+-----+--------- + a.aggtranstype != p2.proargtypes[0]); + aggfnoid | proname | oid | proname +----------+---------+-----+--------- (0 rows) -- If transfn is strict then either initval should be non-NULL, or --- basetype should equal transtype so that the first non-null input +-- input type should equal transtype so that the first non-null input -- can be assigned as the state value. -SELECT p1.oid, p1.aggname, p2.oid, p2.proname -FROM pg_aggregate AS p1, pg_proc AS p2 -WHERE p1.aggtransfn = p2.oid AND p2.proisstrict AND - p1.agginitval IS NULL AND p1.aggbasetype != p1.aggtranstype; - oid | aggname | oid | proname ------+---------+-----+--------- +SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname +FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2 +WHERE a.aggfnoid = p.oid AND + a.aggtransfn = p2.oid AND p2.proisstrict AND + a.agginitval IS NULL AND p.proargtypes[0] != a.aggtranstype; + aggfnoid | proname | oid | proname +----------+---------+-----+--------- (0 rows) -- **************** pg_opclass **************** @@ -574,6 +616,17 @@ WHERE p1.amopopr = p2.oid AND -----------+---------+-----+--------- (0 rows) +-- Check that all operators linked to by opclass entries have selectivity +-- estimators. This is not absolutely required, but it seems a reasonable +-- thing to insist on for all standard datatypes. +SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname +FROM pg_amop AS p1, pg_operator AS p2 +WHERE p1.amopopr = p2.oid AND + (p2.oprrest = 0 OR p2.oprjoin = 0); + amopclaid | amopopr | oid | oprname +-----------+---------+-----+--------- +(0 rows) + -- Check that operator input types match the opclass SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3 |