diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/name.out | 18 | ||||
| -rw-r--r-- | src/test/regress/expected/opr_sanity.out | 168 | ||||
| -rw-r--r-- | src/test/regress/expected/rules.out | 6 | ||||
| -rw-r--r-- | src/test/regress/expected/select_having.out | 10 | ||||
| -rw-r--r-- | src/test/regress/expected/select_having_1.out | 10 | ||||
| -rw-r--r-- | src/test/regress/expected/select_implicit.out | 12 | ||||
| -rw-r--r-- | src/test/regress/expected/select_implicit_1.out | 12 | ||||
| -rw-r--r-- | src/test/regress/expected/strings.out | 22 | ||||
| -rw-r--r-- | src/test/regress/expected/union.out | 16 | ||||
| -rw-r--r-- | src/test/regress/sql/opr_sanity.sql | 153 | ||||
| -rw-r--r-- | src/test/regress/sql/strings.sql | 2 | ||||
| -rw-r--r-- | src/test/regress/sql/union.sql | 4 |
12 files changed, 242 insertions, 191 deletions
diff --git a/src/test/regress/expected/name.out b/src/test/regress/expected/name.out index 8cc09dae744..f6c3a17fe51 100644 --- a/src/test/regress/expected/name.out +++ b/src/test/regress/expected/name.out @@ -27,7 +27,7 @@ INSERT INTO NAME_TBL(f1) VALUES ('d34aaasdf'); INSERT INTO NAME_TBL(f1) VALUES (''); INSERT INTO NAME_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ'); SELECT '' AS seven, NAME_TBL.*; - seven | f1 + seven | f1 -------+----------------------------------------------------------------- | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq @@ -39,7 +39,7 @@ SELECT '' AS seven, NAME_TBL.*; (7 rows) SELECT '' AS six, c.f1 FROM NAME_TBL c WHERE c.f1 <> '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; - six | f1 + six | f1 -----+----------------------------------------------------------------- | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq | asdfghjkl; @@ -49,20 +49,20 @@ SELECT '' AS six, c.f1 FROM NAME_TBL c WHERE c.f1 <> '1234567890ABCDEFGHIJKLMNOP (5 rows) SELECT '' AS one, c.f1 FROM NAME_TBL c WHERE c.f1 = '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; - one | f1 + one | f1 -----+----------------------------------------------------------------- | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ (2 rows) SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 < '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; - three | f1 + three | f1 -------+---- | (1 row) SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 <= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; - four | f1 + four | f1 ------+----------------------------------------------------------------- | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ | @@ -70,7 +70,7 @@ SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 <= '1234567890ABCDEFGHIJKLMNO (3 rows) SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 > '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; - three | f1 + three | f1 -------+----------------------------------------------------------------- | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq | asdfghjkl; @@ -79,7 +79,7 @@ SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 > '1234567890ABCDEFGHIJKLMNO (4 rows) SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 >= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; - four | f1 + four | f1 ------+----------------------------------------------------------------- | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq @@ -90,7 +90,7 @@ SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 >= '1234567890ABCDEFGHIJKLMNO (6 rows) SELECT '' AS seven, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*'; - seven | f1 + seven | f1 -------+----------------------------------------------------------------- | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq @@ -107,7 +107,7 @@ SELECT '' AS zero, c.f1 FROM NAME_TBL c WHERE c.f1 !~ '.*'; (0 rows) SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '[0-9]'; - three | f1 + three | f1 -------+----------------------------------------------------------------- | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 2573d2011c3..e0504706f3c 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -14,6 +14,23 @@ -- -- NB: run this test earlier than the create_operator test, because -- that test creates some bogus operators... +-- Helper functions to deal with cases where binary-coercible matches are +-- allowed. +-- This should match IsBinaryCoercible() in parse_coerce.c. +create function binary_coercible(oid, oid) returns bool as +'SELECT ($1 = $2) OR + EXISTS(select 1 from pg_cast where + castsource = $1 and casttarget = $2 and + castfunc = 0 and castcontext = ''i'')' +language sql; +-- This one ignores castcontext, so it considers only physical equivalence +-- and not whether the coercion can be invoked implicitly. +create function physically_coercible(oid, oid) returns bool as +'SELECT ($1 = $2) OR + EXISTS(select 1 from pg_cast where + castsource = $1 and casttarget = $2 and + castfunc = 0)' +language sql; -- **************** pg_proc **************** -- Look for illegal values in pg_proc fields. -- NOTE: in reality pronargs could be more than 10, but I'm too lazy to put @@ -105,11 +122,10 @@ WHERE p1.oid != p2.oid AND -------------+------------- 25 | 1042 25 | 1043 - 1042 | 1043 1114 | 1184 1560 | 1562 2277 | 2283 -(6 rows) +(5 rows) SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1] FROM pg_proc AS p1, pg_proc AS p2 @@ -120,13 +136,12 @@ WHERE p1.oid != p2.oid AND (p1.proargtypes[1] < p2.proargtypes[1]); proargtypes | proargtypes -------------+------------- + 23 | 28 25 | 1042 - 25 | 1043 - 1042 | 1043 1114 | 1184 1560 | 1562 2277 | 2283 -(6 rows) +(5 rows) SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2] FROM pg_proc AS p1, pg_proc AS p2 @@ -228,23 +243,17 @@ SELECT c.* FROM pg_cast c, pg_proc p WHERE c.castfunc = p.oid AND (p.pronargs <> 1 - OR NOT (c.castsource = p.proargtypes[0] OR - EXISTS (SELECT 1 FROM pg_cast k - WHERE k.castfunc = 0 AND - k.castsource = c.castsource AND - k.casttarget = p.proargtypes[0])) - OR NOT (p.prorettype = c.casttarget OR - EXISTS (SELECT 1 FROM pg_cast k - WHERE k.castfunc = 0 AND - k.castsource = p.prorettype AND - k.casttarget = c.casttarget))); + OR NOT binary_coercible(c.castsource, p.proargtypes[0]) + OR NOT binary_coercible(p.prorettype, c.casttarget)); castsource | casttarget | castfunc | castcontext ------------+------------+----------+------------- (0 rows) -- Look for binary compatible casts that do not have the reverse -- direction registered as well, or where the reverse direction is not --- also binary compatible. This is legal, but probably not intended. +-- also binary compatible. This is legal, but usually not intended. +-- As of 7.4, this finds the casts from text and varchar to bpchar, because +-- those are binary-compatible while the reverse way goes through rtrim(). SELECT * FROM pg_cast c WHERE c.castfunc = 0 AND @@ -254,7 +263,9 @@ WHERE c.castfunc = 0 AND k.casttarget = c.castsource); castsource | casttarget | castfunc | castcontext ------------+------------+----------+------------- -(0 rows) + 25 | 1042 | 0 | i + 1043 | 1042 | 0 | i +(2 rows) -- **************** pg_operator **************** -- Look for illegal values in pg_operator fields. @@ -425,14 +436,15 @@ WHERE p1.oprlsortop != p1.oprrsortop AND -- 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 "=". --- NOTE: in 7.3, this search finds xideqint4. --- Until we have some cleaner way of dealing with binary-equivalent types, --- just leave that tuple in the expected output. +-- NOTE: as of 7.3, this search finds xideqint4. Since we do not mark +-- xid and int4 as binary-equivalent in pg_cast, there's no easy way to +-- recognize that case as OK; just leave that tuple in the expected output. SELECT p1.oid, p1.oprname FROM pg_operator AS p1 WHERE p1.oprcanhash AND NOT (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND - p1.oprleft = p1.oprright AND p1.oprname IN ('=', '~=~') AND p1.oprcom = p1.oid); + p1.oprleft = p1.oprright AND p1.oprname IN ('=', '~=~') AND + p1.oprcom = p1.oid); oid | oprname -----+--------- 353 | = @@ -464,33 +476,26 @@ WHERE p1.oprcanhash AND p1.oprcode = p2.oid AND p2.proname = 'array_eq'; -- Check that each operator defined in pg_operator matches its oprcode entry -- in pg_proc. Easiest to do this separately for each oprkind. --- FIXME: want to check that argument/result types match, but how to do that --- in the face of binary-compatible types? SELECT p1.oid, p1.oprname, p2.oid, p2.proname FROM pg_operator AS p1, pg_proc AS p2 WHERE p1.oprcode = p2.oid AND p1.oprkind = 'b' AND (p2.pronargs != 2 --- diked out until we find a way of marking binary-compatible types --- OR --- p1.oprresult != p2.prorettype OR --- (p1.oprleft != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR --- (p1.oprright != p2.proargtypes[1] AND p2.proargtypes[1] != 0) -); + OR NOT binary_coercible(p2.prorettype, p1.oprresult) + OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0]) + OR NOT binary_coercible(p1.oprright, p2.proargtypes[1])); oid | oprname | oid | proname -----+---------+-----+--------- (0 rows) --- These two selects can be left as-is because there are no binary-compatible --- cases that they trip over, at least in 6.5: SELECT p1.oid, p1.oprname, p2.oid, p2.proname FROM pg_operator AS p1, pg_proc AS p2 WHERE p1.oprcode = p2.oid AND p1.oprkind = 'l' AND - (p2.pronargs != 1 OR - p1.oprresult != p2.prorettype OR - (p1.oprright != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR - p1.oprleft != 0); + (p2.pronargs != 1 + OR NOT binary_coercible(p2.prorettype, p1.oprresult) + OR NOT binary_coercible(p1.oprright, p2.proargtypes[0]) + OR p1.oprleft != 0); oid | oprname | oid | proname -----+---------+-----+--------- (0 rows) @@ -499,10 +504,10 @@ SELECT p1.oid, p1.oprname, p2.oid, p2.proname FROM pg_operator AS p1, pg_proc AS p2 WHERE p1.oprcode = p2.oid AND p1.oprkind = 'r' AND - (p2.pronargs != 1 OR - p1.oprresult != p2.prorettype OR - (p1.oprleft != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR - p1.oprright != 0); + (p2.pronargs != 1 + OR NOT binary_coercible(p2.prorettype, p1.oprresult) + OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0]) + OR p1.oprright != 0); oid | oprname | oid | proname -----+---------+-----+--------- (0 rows) @@ -591,48 +596,46 @@ WHERE a.aggfnoid = p.oid AND (0 rows) -- Cross-check transfn against its entry in pg_proc. --- FIXME: what about binary-compatible types? --- NOTE: in 7.1, this search finds max and min on abstime, which are --- 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 a.aggfnoid::oid, p.proname, p2.oid, p2.proname -FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2 +-- NOTE: use physically_coercible here, not binary_coercible, because +-- max and min on abstime are implemented using int4larger/int4smaller. +SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname +FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr WHERE a.aggfnoid = p.oid AND - a.aggtransfn = p2.oid AND - (p2.proretset OR - 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] = '"any"'::regtype))) -ORDER BY 1; - aggfnoid | proname | oid | proname -----------+---------+-----+------------- - 2121 | max | 768 | int4larger - 2137 | min | 769 | int4smaller -(2 rows) + a.aggtransfn = ptr.oid AND + (ptr.proretset + OR NOT physically_coercible(ptr.prorettype, a.aggtranstype) + OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0]) + OR NOT ((ptr.pronargs = 2 AND + physically_coercible(p.proargtypes[0], ptr.proargtypes[1])) + OR + (ptr.pronargs = 1 AND + p.proargtypes[0] = '"any"'::regtype))); + aggfnoid | proname | oid | proname +----------+---------+-----+--------- +(0 rows) -- Cross-check finalfn (if present) against its entry in pg_proc. --- FIXME: what about binary-compatible types? -SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname -FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2 +SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname +FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn WHERE a.aggfnoid = p.oid AND - a.aggfinalfn = p2.oid AND - (p2.proretset OR p.prorettype != p2.prorettype OR - p2.pronargs != 1 OR - a.aggtranstype != p2.proargtypes[0]); + a.aggfinalfn = pfn.oid AND + (pfn.proretset + OR NOT binary_coercible(pfn.prorettype, p.prorettype) + OR pfn.pronargs != 1 + OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0])); aggfnoid | proname | oid | proname ----------+---------+-----+--------- (0 rows) -- If transfn is strict then either initval should be non-NULL, or --- input type should equal transtype so that the first non-null input +-- input type should match transtype so that the first non-null input -- can be assigned as the state value. -SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname -FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2 +SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname +FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr WHERE a.aggfnoid = p.oid AND - a.aggtransfn = p2.oid AND p2.proisstrict AND - a.agginitval IS NULL AND p.proargtypes[0] != a.aggtranstype; + a.aggtransfn = ptr.oid AND ptr.proisstrict AND + a.agginitval IS NULL AND + NOT binary_coercible(p.proargtypes[0], a.aggtranstype); aggfnoid | proname | oid | proname ----------+---------+-----+--------- (0 rows) @@ -714,7 +717,8 @@ WHERE p1.amopopr = p2.oid AND SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND - (p3.opcintype != p2.oprleft OR p3.opcintype != p2.oprright); + (NOT binary_coercible(p3.opcintype, p2.oprleft) OR + p2.oprleft != p2.oprright); amopclaid | amopopr | oid | oprname | opcname -----------+---------+-----+---------+--------- (0 rows) @@ -752,7 +756,8 @@ WHERE p2.opcamid = p1.oid AND -- signature of the function may be different for different support routines -- or different base data types. -- We can check that all the referenced instances of the same support --- routine number take the same number of parameters, but that's about it... +-- routine number take the same number of parameters, but that's about it +-- for a general check... SELECT p1.amopclaid, p1.amprocnum, p2.oid, p2.proname, p3.opcname, @@ -769,3 +774,22 @@ WHERE p1.amopclaid = p3.oid AND p4.amopclaid = p6.oid AND -----------+-----------+-----+---------+---------+-----------+-----------+-----+---------+--------- (0 rows) +-- For btree, though, we can do better since we know the support routines +-- must be of the form cmp(input, input) returns int4. +SELECT p1.amopclaid, p1.amprocnum, + p2.oid, p2.proname, + p3.opcname +FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3 +WHERE p3.opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') + AND p1.amopclaid = p3.oid AND p1.amproc = p2.oid AND + (opckeytype != 0 + OR amprocnum != 1 + OR proretset + OR prorettype != 23 + OR pronargs != 2 + OR NOT binary_coercible(opcintype, proargtypes[0]) + OR proargtypes[0] != proargtypes[1]); + amopclaid | amprocnum | oid | proname | opcname +-----------+-----------+-----+---------+--------- +(0 rows) + diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index f173b6cf544..322b84ff100 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1317,9 +1317,9 @@ SELECT tablename, rulename, definition FROM pg_rules ---------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- pg_settings | pg_settings_n | CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING; pg_settings | pg_settings_u | CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config; - rtest_emp | rtest_emp_del | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired '::bpchar, '$0.00'::money, old.salary); - rtest_emp | rtest_emp_ins | CREATE RULE rtest_emp_ins AS ON INSERT TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'hired '::bpchar, new.salary, '$0.00'::money); - rtest_emp | rtest_emp_upd | CREATE RULE rtest_emp_upd AS ON UPDATE TO rtest_emp WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'honored '::bpchar, new.salary, old.salary); + rtest_emp | rtest_emp_del | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired'::bpchar, '$0.00'::money, old.salary); + rtest_emp | rtest_emp_ins | CREATE RULE rtest_emp_ins AS ON INSERT TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'hired'::bpchar, new.salary, '$0.00'::money); + rtest_emp | rtest_emp_upd | CREATE RULE rtest_emp_upd AS ON UPDATE TO rtest_emp WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'honored'::bpchar, new.salary, old.salary); rtest_nothn1 | rtest_nothn_r1 | CREATE RULE rtest_nothn_r1 AS ON INSERT TO rtest_nothn1 WHERE ((new.a >= 10) AND (new.a < 20)) DO INSTEAD NOTHING; rtest_nothn1 | rtest_nothn_r2 | CREATE RULE rtest_nothn_r2 AS ON INSERT TO rtest_nothn1 WHERE ((new.a >= 30) AND (new.a < 40)) DO INSTEAD NOTHING; rtest_nothn2 | rtest_nothn_r3 | CREATE RULE rtest_nothn_r3 AS ON INSERT TO rtest_nothn2 WHERE (new.a >= 100) DO INSTEAD INSERT INTO rtest_nothn3 (a, b) VALUES (new.a, new.b); diff --git a/src/test/regress/expected/select_having.out b/src/test/regress/expected/select_having.out index 4447a9df4d6..37793d49b57 100644 --- a/src/test/regress/expected/select_having.out +++ b/src/test/regress/expected/select_having.out @@ -33,11 +33,11 @@ SELECT b, c FROM test_having SELECT lower(c), count(c) FROM test_having GROUP BY lower(c) HAVING count(*) > 2 OR min(a) = max(a) ORDER BY lower(c); - lower | count -----------+------- - bbbb | 3 - cccc | 4 - xxxx | 1 + lower | count +-------+------- + bbbb | 3 + cccc | 4 + xxxx | 1 (3 rows) SELECT c, max(a) FROM test_having diff --git a/src/test/regress/expected/select_having_1.out b/src/test/regress/expected/select_having_1.out index ffbb591b125..6154bcbfe86 100644 --- a/src/test/regress/expected/select_having_1.out +++ b/src/test/regress/expected/select_having_1.out @@ -33,11 +33,11 @@ SELECT b, c FROM test_having SELECT lower(c), count(c) FROM test_having GROUP BY lower(c) HAVING count(*) > 2 OR min(a) = max(a) ORDER BY lower(c); - lower | count -----------+------- - bbbb | 3 - cccc | 4 - xxxx | 1 + lower | count +-------+------- + bbbb | 3 + cccc | 4 + xxxx | 1 (3 rows) SELECT c, max(a) FROM test_having diff --git a/src/test/regress/expected/select_implicit.out b/src/test/regress/expected/select_implicit.out index 47d2e80289e..d8a9edbd781 100644 --- a/src/test/regress/expected/select_implicit.out +++ b/src/test/regress/expected/select_implicit.out @@ -248,12 +248,12 @@ SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2; -- w/ existing GROUP BY target using a relation name in target SELECT lower(test_missing_target.c), count(c) FROM test_missing_target GROUP BY lower(c) ORDER BY lower(c); - lower | count -----------+------- - aaaa | 2 - bbbb | 3 - cccc | 4 - xxxx | 1 + lower | count +-------+------- + aaaa | 2 + bbbb | 3 + cccc | 4 + xxxx | 1 (4 rows) -- w/o existing GROUP BY target diff --git a/src/test/regress/expected/select_implicit_1.out b/src/test/regress/expected/select_implicit_1.out index 80abfd8f9a2..fa67bf3a5d1 100644 --- a/src/test/regress/expected/select_implicit_1.out +++ b/src/test/regress/expected/select_implicit_1.out @@ -248,12 +248,12 @@ SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2; -- w/ existing GROUP BY target using a relation name in target SELECT lower(test_missing_target.c), count(c) FROM test_missing_target GROUP BY lower(c) ORDER BY lower(c); - lower | count -----------+------- - aaaa | 2 - bbbb | 3 - cccc | 4 - xxxx | 1 + lower | count +-------+------- + aaaa | 2 + bbbb | 3 + cccc | 4 + xxxx | 1 (4 rows) -- w/o existing GROUP BY target diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out index bb999785eb1..26c18d1690b 100644 --- a/src/test/regress/expected/strings.out +++ b/src/test/regress/expected/strings.out @@ -26,8 +26,8 @@ ERROR: parser: parse error at or near "' - third line'" at character 75 SELECT CAST(f1 AS text) AS "text(char)" FROM CHAR_TBL; text(char) ------------ - a - ab + a + ab abcd abcd (4 rows) @@ -88,8 +88,8 @@ SELECT CAST(f1 AS varchar) AS "varchar(text)" FROM TEXT_TBL; SELECT CAST(f1 AS varchar) AS "varchar(char)" FROM CHAR_TBL; varchar(char) --------------- - a - ab + a + ab abcd abcd (4 rows) @@ -570,16 +570,16 @@ SELECT text 'text' || ' and unknown' AS "Concat text to unknown type"; text and unknown (1 row) -SELECT char(20) 'characters' || 'and text' AS "Concat char to unknown type"; - Concat char to unknown type ------------------------------- - characters and text +SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown type"; + Concat char to unknown type +----------------------------- + characters and text (1 row) SELECT text 'text' || char(20) ' and characters' AS "Concat text to char"; - Concat text to char --------------------------- - text and characters + Concat text to char +--------------------- + text and characters (1 row) SELECT text 'text' || varchar ' and varchar' AS "Concat text to varchar"; diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out index a46a44becab..43d32298a58 100644 --- a/src/test/regress/expected/union.out +++ b/src/test/regress/expected/union.out @@ -203,21 +203,19 @@ SELECT f1 FROM INT4_TBL 123456 (5 rows) -SELECT f1 AS five FROM VARCHAR_TBL +SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL UNION SELECT f1 FROM CHAR_TBL; - five ------- - a + three +------- a - ab ab abcd -(5 rows) +(3 rows) SELECT f1 AS three FROM VARCHAR_TBL UNION -SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL; +SELECT CAST(f1 AS varchar) FROM CHAR_TBL; three ------- a @@ -234,8 +232,8 @@ SELECT f1 FROM CHAR_TBL; ab abcd abcd - a - ab + a + ab abcd abcd (8 rows) diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index 5be76aa3b41..4b7bd7b4dd5 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -15,6 +15,27 @@ -- NB: run this test earlier than the create_operator test, because -- that test creates some bogus operators... + +-- Helper functions to deal with cases where binary-coercible matches are +-- allowed. + +-- This should match IsBinaryCoercible() in parse_coerce.c. +create function binary_coercible(oid, oid) returns bool as +'SELECT ($1 = $2) OR + EXISTS(select 1 from pg_cast where + castsource = $1 and casttarget = $2 and + castfunc = 0 and castcontext = ''i'')' +language sql; + +-- This one ignores castcontext, so it considers only physical equivalence +-- and not whether the coercion can be invoked implicitly. +create function physically_coercible(oid, oid) returns bool as +'SELECT ($1 = $2) OR + EXISTS(select 1 from pg_cast where + castsource = $1 and casttarget = $2 and + castfunc = 0)' +language sql; + -- **************** pg_proc **************** -- Look for illegal values in pg_proc fields. @@ -180,20 +201,15 @@ SELECT c.* FROM pg_cast c, pg_proc p WHERE c.castfunc = p.oid AND (p.pronargs <> 1 - OR NOT (c.castsource = p.proargtypes[0] OR - EXISTS (SELECT 1 FROM pg_cast k - WHERE k.castfunc = 0 AND - k.castsource = c.castsource AND - k.casttarget = p.proargtypes[0])) - OR NOT (p.prorettype = c.casttarget OR - EXISTS (SELECT 1 FROM pg_cast k - WHERE k.castfunc = 0 AND - k.castsource = p.prorettype AND - k.casttarget = c.casttarget))); + OR NOT binary_coercible(c.castsource, p.proargtypes[0]) + OR NOT binary_coercible(p.prorettype, c.casttarget)); -- Look for binary compatible casts that do not have the reverse -- direction registered as well, or where the reverse direction is not --- also binary compatible. This is legal, but probably not intended. +-- also binary compatible. This is legal, but usually not intended. + +-- As of 7.4, this finds the casts from text and varchar to bpchar, because +-- those are binary-compatible while the reverse way goes through rtrim(). SELECT * FROM pg_cast c @@ -347,15 +363,17 @@ WHERE p1.oprlsortop != p1.oprrsortop AND -- 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 "=". --- NOTE: in 7.3, this search finds xideqint4. --- Until we have some cleaner way of dealing with binary-equivalent types, --- just leave that tuple in the expected output. + +-- NOTE: as of 7.3, this search finds xideqint4. Since we do not mark +-- xid and int4 as binary-equivalent in pg_cast, there's no easy way to +-- recognize that case as OK; just leave that tuple in the expected output. SELECT p1.oid, p1.oprname FROM pg_operator AS p1 WHERE p1.oprcanhash AND NOT (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND - p1.oprleft = p1.oprright AND p1.oprname IN ('=', '~=~') AND p1.oprcom = p1.oid); + p1.oprleft = p1.oprright AND p1.oprname IN ('=', '~=~') AND + p1.oprcom = p1.oid); -- In 6.5 we accepted hashable array equality operators when the array element -- type is hashable. However, what we actually need to make hashjoin work on @@ -382,41 +400,33 @@ WHERE p1.oprcanhash AND p1.oprcode = p2.oid AND p2.proname = 'array_eq'; -- Check that each operator defined in pg_operator matches its oprcode entry -- in pg_proc. Easiest to do this separately for each oprkind. --- FIXME: want to check that argument/result types match, but how to do that --- in the face of binary-compatible types? SELECT p1.oid, p1.oprname, p2.oid, p2.proname FROM pg_operator AS p1, pg_proc AS p2 WHERE p1.oprcode = p2.oid AND p1.oprkind = 'b' AND (p2.pronargs != 2 --- diked out until we find a way of marking binary-compatible types --- OR --- p1.oprresult != p2.prorettype OR --- (p1.oprleft != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR --- (p1.oprright != p2.proargtypes[1] AND p2.proargtypes[1] != 0) -); - --- These two selects can be left as-is because there are no binary-compatible --- cases that they trip over, at least in 6.5: + OR NOT binary_coercible(p2.prorettype, p1.oprresult) + OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0]) + OR NOT binary_coercible(p1.oprright, p2.proargtypes[1])); SELECT p1.oid, p1.oprname, p2.oid, p2.proname FROM pg_operator AS p1, pg_proc AS p2 WHERE p1.oprcode = p2.oid AND p1.oprkind = 'l' AND - (p2.pronargs != 1 OR - p1.oprresult != p2.prorettype OR - (p1.oprright != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR - p1.oprleft != 0); + (p2.pronargs != 1 + OR NOT binary_coercible(p2.prorettype, p1.oprresult) + OR NOT binary_coercible(p1.oprright, p2.proargtypes[0]) + OR p1.oprleft != 0); SELECT p1.oid, p1.oprname, p2.oid, p2.proname FROM pg_operator AS p1, pg_proc AS p2 WHERE p1.oprcode = p2.oid AND p1.oprkind = 'r' AND - (p2.pronargs != 1 OR - p1.oprresult != p2.prorettype OR - (p1.oprleft != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR - p1.oprright != 0); + (p2.pronargs != 1 + OR NOT binary_coercible(p2.prorettype, p1.oprresult) + OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0]) + OR p1.oprright != 0); -- If the operator is mergejoinable or hashjoinable, its underlying function -- should not be volatile. @@ -489,42 +499,42 @@ WHERE a.aggfnoid = p.oid AND a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype; -- Cross-check transfn against its entry in pg_proc. --- FIXME: what about binary-compatible types? --- NOTE: in 7.1, this search finds max and min on abstime, which are --- 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 a.aggfnoid::oid, p.proname, p2.oid, p2.proname -FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2 +-- NOTE: use physically_coercible here, not binary_coercible, because +-- max and min on abstime are implemented using int4larger/int4smaller. +SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname +FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr WHERE a.aggfnoid = p.oid AND - a.aggtransfn = p2.oid AND - (p2.proretset OR - 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] = '"any"'::regtype))) -ORDER BY 1; + a.aggtransfn = ptr.oid AND + (ptr.proretset + OR NOT physically_coercible(ptr.prorettype, a.aggtranstype) + OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0]) + OR NOT ((ptr.pronargs = 2 AND + physically_coercible(p.proargtypes[0], ptr.proargtypes[1])) + OR + (ptr.pronargs = 1 AND + p.proargtypes[0] = '"any"'::regtype))); -- Cross-check finalfn (if present) against its entry in pg_proc. --- FIXME: what about binary-compatible types? -SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname -FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2 +SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname +FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn WHERE a.aggfnoid = p.oid AND - a.aggfinalfn = p2.oid AND - (p2.proretset OR p.prorettype != p2.prorettype OR - p2.pronargs != 1 OR - a.aggtranstype != p2.proargtypes[0]); + a.aggfinalfn = pfn.oid AND + (pfn.proretset + OR NOT binary_coercible(pfn.prorettype, p.prorettype) + OR pfn.pronargs != 1 + OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0])); -- If transfn is strict then either initval should be non-NULL, or --- input type should equal transtype so that the first non-null input +-- input type should match transtype so that the first non-null input -- can be assigned as the state value. -SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname -FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2 +SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname +FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr WHERE a.aggfnoid = p.oid AND - a.aggtransfn = p2.oid AND p2.proisstrict AND - a.agginitval IS NULL AND p.proargtypes[0] != a.aggtranstype; + a.aggtransfn = ptr.oid AND ptr.proisstrict AND + a.agginitval IS NULL AND + NOT binary_coercible(p.proargtypes[0], a.aggtranstype); -- **************** pg_opclass **************** @@ -592,7 +602,8 @@ WHERE p1.amopopr = p2.oid AND SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND - (p3.opcintype != p2.oprleft OR p3.opcintype != p2.oprright); + (NOT binary_coercible(p3.opcintype, p2.oprleft) OR + p2.oprleft != p2.oprright); -- **************** pg_amproc **************** @@ -622,7 +633,8 @@ WHERE p2.opcamid = p1.oid AND -- signature of the function may be different for different support routines -- or different base data types. -- We can check that all the referenced instances of the same support --- routine number take the same number of parameters, but that's about it... +-- routine number take the same number of parameters, but that's about it +-- for a general check... SELECT p1.amopclaid, p1.amprocnum, p2.oid, p2.proname, @@ -636,3 +648,20 @@ WHERE p1.amopclaid = p3.oid AND p4.amopclaid = p6.oid AND p3.opcamid = p6.opcamid AND p1.amprocnum = p4.amprocnum AND p1.amproc = p2.oid AND p4.amproc = p5.oid AND (p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs); + +-- For btree, though, we can do better since we know the support routines +-- must be of the form cmp(input, input) returns int4. + +SELECT p1.amopclaid, p1.amprocnum, + p2.oid, p2.proname, + p3.opcname +FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3 +WHERE p3.opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') + AND p1.amopclaid = p3.oid AND p1.amproc = p2.oid AND + (opckeytype != 0 + OR amprocnum != 1 + OR proretset + OR prorettype != 23 + OR pronargs != 2 + OR NOT binary_coercible(opcintype, proargtypes[0]) + OR proargtypes[0] != proargtypes[1]); diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql index b84d0cb8adb..d2368d6ba53 100644 --- a/src/test/regress/sql/strings.sql +++ b/src/test/regress/sql/strings.sql @@ -199,7 +199,7 @@ SELECT 'unknown' || ' and unknown' AS "Concat unknown types"; SELECT text 'text' || ' and unknown' AS "Concat text to unknown type"; -SELECT char(20) 'characters' || 'and text' AS "Concat char to unknown type"; +SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown type"; SELECT text 'text' || char(20) ' and characters' AS "Concat text to char"; diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql index 747d281948f..c69e4c3f152 100644 --- a/src/test/regress/sql/union.sql +++ b/src/test/regress/sql/union.sql @@ -66,13 +66,13 @@ UNION SELECT f1 FROM INT4_TBL WHERE f1 BETWEEN 0 AND 1000000; -SELECT f1 AS five FROM VARCHAR_TBL +SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL UNION SELECT f1 FROM CHAR_TBL; SELECT f1 AS three FROM VARCHAR_TBL UNION -SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL; +SELECT CAST(f1 AS varchar) FROM CHAR_TBL; SELECT f1 AS eight FROM VARCHAR_TBL UNION ALL |