1/* 2 * This test is intended to pass on all platforms supported by Postgres. 3 * We can therefore only assume that the default, C, and POSIX collations 4 * are available --- and since the regression tests are often run in a 5 * C-locale database, these may well all have the same behavior. But 6 * fortunately, the system doesn't know that and will treat them as 7 * incompatible collations. It is therefore at least possible to test 8 * parser behaviors such as collation conflict resolution. This test will, 9 * however, be more revealing when run in a database with non-C locale, 10 * since any departure from C sorting behavior will show as a failure. 11 */ 12 13CREATE SCHEMA collate_tests; 14SET search_path = collate_tests; 15 16CREATE TABLE collate_test1 ( 17 a int, 18 b text COLLATE "C" NOT NULL 19); 20 21\d collate_test1 22 23CREATE TABLE collate_test_fail ( 24 a int COLLATE "C", 25 b text 26); 27 28CREATE TABLE collate_test_like ( 29 LIKE collate_test1 30); 31 32\d collate_test_like 33 34CREATE TABLE collate_test2 ( 35 a int, 36 b text COLLATE "POSIX" 37); 38 39INSERT INTO collate_test1 VALUES (1, 'abc'), (2, 'Abc'), (3, 'bbc'), (4, 'ABD'); 40INSERT INTO collate_test2 SELECT * FROM collate_test1; 41 42SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'abc'; 43SELECT * FROM collate_test1 WHERE b >= 'abc' COLLATE "C"; 44SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'abc' COLLATE "C"; 45SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "POSIX"; -- fail 46 47CREATE DOMAIN testdomain_p AS text COLLATE "POSIX"; 48CREATE DOMAIN testdomain_i AS int COLLATE "POSIX"; -- fail 49CREATE TABLE collate_test4 ( 50 a int, 51 b testdomain_p 52); 53INSERT INTO collate_test4 SELECT * FROM collate_test1; 54SELECT a, b FROM collate_test4 ORDER BY b; 55 56CREATE TABLE collate_test5 ( 57 a int, 58 b testdomain_p COLLATE "C" 59); 60INSERT INTO collate_test5 SELECT * FROM collate_test1; 61SELECT a, b FROM collate_test5 ORDER BY b; 62 63 64SELECT a, b FROM collate_test1 ORDER BY b; 65SELECT a, b FROM collate_test2 ORDER BY b; 66 67SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; 68 69-- star expansion 70SELECT * FROM collate_test1 ORDER BY b; 71SELECT * FROM collate_test2 ORDER BY b; 72 73-- constant expression folding 74SELECT 'bbc' COLLATE "C" > 'Abc' COLLATE "C" AS "true"; 75SELECT 'bbc' COLLATE "POSIX" < 'Abc' COLLATE "POSIX" AS "false"; 76 77-- upper/lower 78 79CREATE TABLE collate_test10 ( 80 a int, 81 x text COLLATE "C", 82 y text COLLATE "POSIX" 83); 84 85INSERT INTO collate_test10 VALUES (1, 'hij', 'hij'), (2, 'HIJ', 'HIJ'); 86 87SELECT a, lower(x), lower(y), upper(x), upper(y), initcap(x), initcap(y) FROM collate_test10; 88SELECT a, lower(x COLLATE "C"), lower(y COLLATE "C") FROM collate_test10; 89 90SELECT a, x, y FROM collate_test10 ORDER BY lower(y), a; 91 92-- backwards parsing 93 94CREATE VIEW collview1 AS SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc'; 95CREATE VIEW collview2 AS SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; 96CREATE VIEW collview3 AS SELECT a, lower((x || x) COLLATE "POSIX") FROM collate_test10; 97 98SELECT table_name, view_definition FROM information_schema.views 99 WHERE table_name LIKE 'collview%' ORDER BY 1; 100 101 102-- collation propagation in various expression types 103 104SELECT a, coalesce(b, 'foo') FROM collate_test1 ORDER BY 2; 105SELECT a, coalesce(b, 'foo') FROM collate_test2 ORDER BY 2; 106SELECT a, lower(coalesce(x, 'foo')), lower(coalesce(y, 'foo')) FROM collate_test10; 107 108SELECT a, b, greatest(b, 'CCC') FROM collate_test1 ORDER BY 3; 109SELECT a, b, greatest(b, 'CCC') FROM collate_test2 ORDER BY 3; 110SELECT a, x, y, lower(greatest(x, 'foo')), lower(greatest(y, 'foo')) FROM collate_test10; 111 112SELECT a, nullif(b, 'abc') FROM collate_test1 ORDER BY 2; 113SELECT a, nullif(b, 'abc') FROM collate_test2 ORDER BY 2; 114SELECT a, lower(nullif(x, 'foo')), lower(nullif(y, 'foo')) FROM collate_test10; 115 116SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test1 ORDER BY 2; 117SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test2 ORDER BY 2; 118 119CREATE DOMAIN testdomain AS text; 120SELECT a, b::testdomain FROM collate_test1 ORDER BY 2; 121SELECT a, b::testdomain FROM collate_test2 ORDER BY 2; 122SELECT a, b::testdomain_p FROM collate_test2 ORDER BY 2; 123SELECT a, lower(x::testdomain), lower(y::testdomain) FROM collate_test10; 124 125SELECT min(b), max(b) FROM collate_test1; 126SELECT min(b), max(b) FROM collate_test2; 127 128SELECT array_agg(b ORDER BY b) FROM collate_test1; 129SELECT array_agg(b ORDER BY b) FROM collate_test2; 130 131-- In aggregates, ORDER BY expressions don't affect aggregate's collation 132SELECT string_agg(x COLLATE "C", y COLLATE "POSIX") FROM collate_test10; -- fail 133SELECT array_agg(x COLLATE "C" ORDER BY y COLLATE "POSIX") FROM collate_test10; 134SELECT array_agg(a ORDER BY x COLLATE "C", y COLLATE "POSIX") FROM collate_test10; 135SELECT array_agg(a ORDER BY x||y) FROM collate_test10; -- fail 136 137SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test1 ORDER BY 2; 138SELECT a, b FROM collate_test2 UNION SELECT a, b FROM collate_test2 ORDER BY 2; 139SELECT a, b FROM collate_test2 WHERE a < 4 INTERSECT SELECT a, b FROM collate_test2 WHERE a > 1 ORDER BY 2; 140SELECT a, b FROM collate_test2 EXCEPT SELECT a, b FROM collate_test2 WHERE a < 2 ORDER BY 2; 141 142SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2 ORDER BY 2; -- fail 143SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2; -- ok 144SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collate_test2 ORDER BY 2; -- fail 145SELECT a, b COLLATE "C" FROM collate_test1 UNION SELECT a, b FROM collate_test2 ORDER BY 2; -- ok 146SELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM collate_test2 ORDER BY 2; -- fail 147SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM collate_test2 ORDER BY 2; -- fail 148 149CREATE TABLE test_u AS SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2; -- fail 150 151-- ideally this would be a parse-time error, but for now it must be run-time: 152select x < y from collate_test10; -- fail 153select x || y from collate_test10; -- ok, because || is not collation aware 154select x, y from collate_test10 order by x || y; -- not so ok 155 156-- collation mismatch between recursive and non-recursive term 157WITH RECURSIVE foo(x) AS 158 (SELECT x FROM (VALUES('a' COLLATE "C"),('b')) t(x) 159 UNION ALL 160 SELECT (x || 'c') COLLATE "POSIX" FROM foo WHERE length(x) < 10) 161SELECT * FROM foo; 162 163SELECT a, b, a < b as lt FROM 164 (VALUES ('a', 'B'), ('A', 'b' COLLATE "C")) v(a,b); 165 166 167-- casting 168 169SELECT CAST('42' AS text COLLATE "C"); 170 171SELECT a, CAST(b AS varchar) FROM collate_test1 ORDER BY 2; 172SELECT a, CAST(b AS varchar) FROM collate_test2 ORDER BY 2; 173 174 175-- polymorphism 176 177SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test1)) ORDER BY 1; 178SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test2)) ORDER BY 1; 179 180CREATE FUNCTION dup (anyelement) RETURNS anyelement 181 AS 'select $1' LANGUAGE sql; 182 183SELECT a, dup(b) FROM collate_test1 ORDER BY 2; 184SELECT a, dup(b) FROM collate_test2 ORDER BY 2; 185 186 187-- indexes 188 189CREATE INDEX collate_test1_idx1 ON collate_test1 (b); 190CREATE INDEX collate_test1_idx2 ON collate_test1 (b COLLATE "POSIX"); 191CREATE INDEX collate_test1_idx3 ON collate_test1 ((b COLLATE "POSIX")); -- this is different grammatically 192CREATE INDEX collate_test1_idx4 ON collate_test1 (((b||'foo') COLLATE "POSIX")); 193 194CREATE INDEX collate_test1_idx5 ON collate_test1 (a COLLATE "POSIX"); -- fail 195CREATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "POSIX")); -- fail 196 197SELECT relname, pg_get_indexdef(oid) FROM pg_class WHERE relname LIKE 'collate_test%_idx%' ORDER BY 1; 198 199 200-- foreign keys 201 202-- force indexes and mergejoins to be used for FK checking queries, 203-- else they might not exercise collation-dependent operators 204SET enable_seqscan TO 0; 205SET enable_hashjoin TO 0; 206SET enable_nestloop TO 0; 207 208CREATE TABLE collate_test20 (f1 text COLLATE "C" PRIMARY KEY); 209INSERT INTO collate_test20 VALUES ('foo'), ('bar'); 210CREATE TABLE collate_test21 (f2 text COLLATE "POSIX" REFERENCES collate_test20); 211INSERT INTO collate_test21 VALUES ('foo'), ('bar'); 212INSERT INTO collate_test21 VALUES ('baz'); -- fail 213CREATE TABLE collate_test22 (f2 text COLLATE "POSIX"); 214INSERT INTO collate_test22 VALUES ('foo'), ('bar'), ('baz'); 215ALTER TABLE collate_test22 ADD FOREIGN KEY (f2) REFERENCES collate_test20; -- fail 216DELETE FROM collate_test22 WHERE f2 = 'baz'; 217ALTER TABLE collate_test22 ADD FOREIGN KEY (f2) REFERENCES collate_test20; 218 219RESET enable_seqscan; 220RESET enable_hashjoin; 221RESET enable_nestloop; 222 223 224-- EXPLAIN 225 226EXPLAIN (COSTS OFF) 227 SELECT * FROM collate_test10 ORDER BY x, y; 228EXPLAIN (COSTS OFF) 229 SELECT * FROM collate_test10 ORDER BY x DESC, y COLLATE "C" ASC NULLS FIRST; 230 231 232-- CREATE/DROP COLLATION 233 234CREATE COLLATION mycoll1 FROM "C"; 235CREATE COLLATION mycoll2 ( LC_COLLATE = "POSIX", LC_CTYPE = "POSIX" ); 236CREATE COLLATION mycoll3 FROM "default"; -- intentionally unsupported 237 238DROP COLLATION mycoll1; 239CREATE TABLE collate_test23 (f1 text collate mycoll2); 240DROP COLLATION mycoll2; -- fail 241 242 243-- 9.1 bug with useless COLLATE in an expression subject to length coercion 244 245CREATE TEMP TABLE vctable (f1 varchar(25)); 246INSERT INTO vctable VALUES ('foo' COLLATE "C"); 247 248 249SELECT collation for ('foo'); -- unknown type - null 250SELECT collation for ('foo'::text); 251SELECT collation for ((SELECT a FROM collate_test1 LIMIT 1)); -- non-collatable type - error 252SELECT collation for ((SELECT b FROM collate_test1 LIMIT 1)); 253 254-- old bug with not dropping COLLATE when coercing to non-collatable type 255CREATE VIEW collate_on_int AS 256SELECT c1+1 AS c1p FROM 257 (SELECT ('4' COLLATE "C")::INT AS c1) ss; 258\d+ collate_on_int 259 260 261-- 262-- Clean up. Many of these table names will be re-used if the user is 263-- trying to run any platform-specific collation tests later, so we 264-- must get rid of them. 265-- 266\set VERBOSITY terse 267DROP SCHEMA collate_tests CASCADE; 268