src/test/regress/expected/collate.out

   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 CREATE SCHEMA collate_tests;
  13 SET search_path = collate_tests;
  14 CREATE TABLE collate_test1 (
  15     a int,
  16     b text COLLATE "C" NOT NULL
  17 );
  18 \d collate_test1
  19   Table "collate_tests.collate_test1"
  20  Column |  Type   |     Modifiers
  21 --------+---------+--------------------
  22  a      | integer |
  23  b      | text    | collate C not null
  24
  25 CREATE TABLE collate_test_fail (
  26     a int COLLATE "C",
  27     b text
  28 );
  29 ERROR:  collations are not supported by type integer
  30 LINE 2:     a int COLLATE "C",
  31                   ^
  32 CREATE TABLE collate_test_like (
  33     LIKE collate_test1
  34 );
  35 \d collate_test_like
  36 Table "collate_tests.collate_test_like"
  37  Column |  Type   |     Modifiers
  38 --------+---------+--------------------
  39  a      | integer |
  40  b      | text    | collate C not null
  41
  42 CREATE TABLE collate_test2 (
  43     a int,
  44     b text COLLATE "POSIX"
  45 );
  46 INSERT INTO collate_test1 VALUES (1, 'abc'), (2, 'Abc'), (3, 'bbc'), (4, 'ABD');
  47 INSERT INTO collate_test2 SELECT * FROM collate_test1;
  48 SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'abc';
  49  a |  b
  50 ---+-----
  51  1 | abc
  52  3 | bbc
  53 (2 rows)
  54
  55 SELECT * FROM collate_test1 WHERE b >= 'abc' COLLATE "C";
  56  a |  b
  57 ---+-----
  58  1 | abc
  59  3 | bbc
  60 (2 rows)
  61
  62 SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'abc' COLLATE "C";
  63  a |  b
  64 ---+-----
  65  1 | abc
  66  3 | bbc
  67 (2 rows)
  68
  69 SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "POSIX"; -- fail
  70 ERROR:  collation mismatch between explicit collations "C" and "POSIX"
  71 LINE 1: ...* FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "P...
  72                                                              ^
  73 CREATE DOMAIN testdomain_p AS text COLLATE "POSIX";
  74 CREATE DOMAIN testdomain_i AS int COLLATE "POSIX"; -- fail
  75 ERROR:  collations are not supported by type integer
  76 CREATE TABLE collate_test4 (
  77     a int,
  78     b testdomain_p
  79 );
  80 INSERT INTO collate_test4 SELECT * FROM collate_test1;
  81 SELECT a, b FROM collate_test4 ORDER BY b;
  82  a |  b
  83 ---+-----
  84  4 | ABD
  85  2 | Abc
  86  1 | abc
  87  3 | bbc
  88 (4 rows)
  89
  90 CREATE TABLE collate_test5 (
  91     a int,
  92     b testdomain_p COLLATE "C"
  93 );
  94 INSERT INTO collate_test5 SELECT * FROM collate_test1;
  95 SELECT a, b FROM collate_test5 ORDER BY b;
  96  a |  b
  97 ---+-----
  98  4 | ABD
  99  2 | Abc
 100  1 | abc
 101  3 | bbc
 102 (4 rows)
 103
 104 SELECT a, b FROM collate_test1 ORDER BY b;
 105  a |  b
 106 ---+-----
 107  4 | ABD
 108  2 | Abc
 109  1 | abc
 110  3 | bbc
 111 (4 rows)
 112
 113 SELECT a, b FROM collate_test2 ORDER BY b;
 114  a |  b
 115 ---+-----
 116  4 | ABD
 117  2 | Abc
 118  1 | abc
 119  3 | bbc
 120 (4 rows)
 121
 122 SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C";
 123  a |  b
 124 ---+-----
 125  4 | ABD
 126  2 | Abc
 127  1 | abc
 128  3 | bbc
 129 (4 rows)
 130
 131 -- star expansion
 132 SELECT * FROM collate_test1 ORDER BY b;
 133  a |  b
 134 ---+-----
 135  4 | ABD
 136  2 | Abc
 137  1 | abc
 138  3 | bbc
 139 (4 rows)
 140
 141 SELECT * FROM collate_test2 ORDER BY b;
 142  a |  b
 143 ---+-----
 144  4 | ABD
 145  2 | Abc
 146  1 | abc
 147  3 | bbc
 148 (4 rows)
 149
 150 -- constant expression folding
 151 SELECT 'bbc' COLLATE "C" > 'Abc' COLLATE "C" AS "true";
 152  true
 153 ------
 154  t
 155 (1 row)
 156
 157 SELECT 'bbc' COLLATE "POSIX" < 'Abc' COLLATE "POSIX" AS "false";
 158  false
 159 -------
 160  f
 161 (1 row)
 162
 163 -- upper/lower
 164 CREATE TABLE collate_test10 (
 165     a int,
 166     x text COLLATE "C",
 167     y text COLLATE "POSIX"
 168 );
 169 INSERT INTO collate_test10 VALUES (1, 'hij', 'hij'), (2, 'HIJ', 'HIJ');
 170 SELECT a, lower(x), lower(y), upper(x), upper(y), initcap(x), initcap(y) FROM collate_test10;
 171  a | lower | lower | upper | upper | initcap | initcap
 172 ---+-------+-------+-------+-------+---------+---------
 173  1 | hij   | hij   | HIJ   | HIJ   | Hij     | Hij
 174  2 | hij   | hij   | HIJ   | HIJ   | Hij     | Hij
 175 (2 rows)
 176
 177 SELECT a, lower(x COLLATE "C"), lower(y COLLATE "C") FROM collate_test10;
 178  a | lower | lower
 179 ---+-------+-------
 180  1 | hij   | hij
 181  2 | hij   | hij
 182 (2 rows)
 183
 184 SELECT a, x, y FROM collate_test10 ORDER BY lower(y), a;
 185  a |  x  |  y
 186 ---+-----+-----
 187  1 | hij | hij
 188  2 | HIJ | HIJ
 189 (2 rows)
 190
 191 -- backwards parsing
 192 CREATE VIEW collview1 AS SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc';
 193 CREATE VIEW collview2 AS SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C";
 194 CREATE VIEW collview3 AS SELECT a, lower((x || x) COLLATE "POSIX") FROM collate_test10;
 195 SELECT table_name, view_definition FROM information_schema.views
 196   WHERE table_name LIKE 'collview%' ORDER BY 1;
 197  table_name |                                                    view_definition
 198 ------------+------------------------------------------------------------------------------------------------------------------------
 199  collview1  | SELECT collate_test1.a, collate_test1.b FROM collate_test1 WHERE ((collate_test1.b COLLATE "C") >= 'bbc'::text);
 200  collview2  | SELECT collate_test1.a, collate_test1.b FROM collate_test1 ORDER BY (collate_test1.b COLLATE "C");
 201  collview3  | SELECT collate_test10.a, lower(((collate_test10.x || collate_test10.x) COLLATE "POSIX")) AS lower FROM collate_test10;
 202 (3 rows)
 203
 204 -- collation propagation in various expression type
 205 SELECT a, coalesce(b, 'foo') FROM collate_test1 ORDER BY 2;
 206  a | coalesce
 207 ---+----------
 208  4 | ABD
 209  2 | Abc
 210  1 | abc
 211  3 | bbc
 212 (4 rows)
 213
 214 SELECT a, coalesce(b, 'foo') FROM collate_test2 ORDER BY 2;
 215  a | coalesce
 216 ---+----------
 217  4 | ABD
 218  2 | Abc
 219  1 | abc
 220  3 | bbc
 221 (4 rows)
 222
 223 SELECT a, lower(coalesce(x, 'foo')), lower(coalesce(y, 'foo')) FROM collate_test10;
 224  a | lower | lower
 225 ---+-------+-------
 226  1 | hij   | hij
 227  2 | hij   | hij
 228 (2 rows)
 229
 230 SELECT a, b, greatest(b, 'CCC') FROM collate_test1 ORDER BY 3;
 231  a |  b  | greatest
 232 ---+-----+----------
 233  2 | Abc | CCC
 234  4 | ABD | CCC
 235  1 | abc | abc
 236  3 | bbc | bbc
 237 (4 rows)
 238
 239 SELECT a, b, greatest(b, 'CCC') FROM collate_test2 ORDER BY 3;
 240  a |  b  | greatest
 241 ---+-----+----------
 242  2 | Abc | CCC
 243  4 | ABD | CCC
 244  1 | abc | abc
 245  3 | bbc | bbc
 246 (4 rows)
 247
 248 SELECT a, x, y, lower(greatest(x, 'foo')), lower(greatest(y, 'foo')) FROM collate_test10;
 249  a |  x  |  y  | lower | lower
 250 ---+-----+-----+-------+-------
 251  1 | hij | hij | hij   | hij
 252  2 | HIJ | HIJ | foo   | foo
 253 (2 rows)
 254
 255 SELECT a, nullif(b, 'abc') FROM collate_test1 ORDER BY 2;
 256  a | nullif
 257 ---+--------
 258  4 | ABD
 259  2 | Abc
 260  3 | bbc
 261  1 |
 262 (4 rows)
 263
 264 SELECT a, nullif(b, 'abc') FROM collate_test2 ORDER BY 2;
 265  a | nullif
 266 ---+--------
 267  4 | ABD
 268  2 | Abc
 269  3 | bbc
 270  1 |
 271 (4 rows)
 272
 273 SELECT a, lower(nullif(x, 'foo')), lower(nullif(y, 'foo')) FROM collate_test10;
 274  a | lower | lower
 275 ---+-------+-------
 276  1 | hij   | hij
 277  2 | hij   | hij
 278 (2 rows)
 279
 280 SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test1 ORDER BY 2;
 281  a |  b
 282 ---+------
 283  4 | ABD
 284  2 | Abc
 285  1 | abcd
 286  3 | bbc
 287 (4 rows)
 288
 289 SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test2 ORDER BY 2;
 290  a |  b
 291 ---+------
 292  4 | ABD
 293  2 | Abc
 294  1 | abcd
 295  3 | bbc
 296 (4 rows)
 297
 298 CREATE DOMAIN testdomain AS text;
 299 SELECT a, b::testdomain FROM collate_test1 ORDER BY 2;
 300  a |  b
 301 ---+-----
 302  4 | ABD
 303  2 | Abc
 304  1 | abc
 305  3 | bbc
 306 (4 rows)
 307
 308 SELECT a, b::testdomain FROM collate_test2 ORDER BY 2;
 309  a |  b
 310 ---+-----
 311  4 | ABD
 312  2 | Abc
 313  1 | abc
 314  3 | bbc
 315 (4 rows)
 316
 317 SELECT a, b::testdomain_p FROM collate_test2 ORDER BY 2;
 318  a |  b
 319 ---+-----
 320  4 | ABD
 321  2 | Abc
 322  1 | abc
 323  3 | bbc
 324 (4 rows)
 325
 326 SELECT a, lower(x::testdomain), lower(y::testdomain) FROM collate_test10;
 327  a | lower | lower
 328 ---+-------+-------
 329  1 | hij   | hij
 330  2 | hij   | hij
 331 (2 rows)
 332
 333 SELECT min(b), max(b) FROM collate_test1;
 334  min | max
 335 -----+-----
 336  ABD | bbc
 337 (1 row)
 338
 339 SELECT min(b), max(b) FROM collate_test2;
 340  min | max
 341 -----+-----
 342  ABD | bbc
 343 (1 row)
 344
 345 SELECT array_agg(b ORDER BY b) FROM collate_test1;
 346      array_agg
 347 -------------------
 348  {ABD,Abc,abc,bbc}
 349 (1 row)
 350
 351 SELECT array_agg(b ORDER BY b) FROM collate_test2;
 352      array_agg
 353 -------------------
 354  {ABD,Abc,abc,bbc}
 355 (1 row)
 356
 357 SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test1 ORDER BY 2;
 358  a |  b
 359 ---+-----
 360  4 | ABD
 361  4 | ABD
 362  2 | Abc
 363  2 | Abc
 364  1 | abc
 365  1 | abc
 366  3 | bbc
 367  3 | bbc
 368 (8 rows)
 369
 370 SELECT a, b FROM collate_test2 UNION SELECT a, b FROM collate_test2 ORDER BY 2;
 371  a |  b
 372 ---+-----
 373  4 | ABD
 374  2 | Abc
 375  1 | abc
 376  3 | bbc
 377 (4 rows)
 378
 379 SELECT a, b FROM collate_test2 WHERE a < 4 INTERSECT SELECT a, b FROM collate_test2 WHERE a > 1 ORDER BY 2;
 380  a |  b
 381 ---+-----
 382  2 | Abc
 383  3 | bbc
 384 (2 rows)
 385
 386 SELECT a, b FROM collate_test2 EXCEPT SELECT a, b FROM collate_test2 WHERE a < 2 ORDER BY 2;
 387  a |  b
 388 ---+-----
 389  4 | ABD
 390  2 | Abc
 391  3 | bbc
 392 (3 rows)
 393
 394 SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2 ORDER BY 2; -- fail
 395 ERROR:  could not determine which collation to use for string comparison
 396 HINT:  Use the COLLATE clause to set the collation explicitly.
 397 SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2; -- ok
 398  a |  b
 399 ---+-----
 400  1 | abc
 401  2 | Abc
 402  3 | bbc
 403  4 | ABD
 404  1 | abc
 405  2 | Abc
 406  3 | bbc
 407  4 | ABD
 408 (8 rows)
 409
 410 SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collate_test2 ORDER BY 2; -- fail
 411 ERROR:  collation mismatch between implicit collations "C" and "POSIX"
 412 LINE 1: SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collat...
 413                                                        ^
 414 HINT:  You can choose the collation by applying the COLLATE clause to one or both expressions.
 415 SELECT a, b COLLATE "C" FROM collate_test1 UNION SELECT a, b FROM collate_test2 ORDER BY 2; -- ok
 416  a |  b
 417 ---+-----
 418  4 | ABD
 419  2 | Abc
 420  1 | abc
 421  3 | bbc
 422 (4 rows)
 423
 424 SELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM collate_test2 ORDER BY 2; -- fail
 425 ERROR:  collation mismatch between implicit collations "C" and "POSIX"
 426 LINE 1: ...ELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM col...
 427                                                              ^
 428 HINT:  You can choose the collation by applying the COLLATE clause to one or both expressions.
 429 SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM collate_test2 ORDER BY 2; -- fail
 430 ERROR:  collation mismatch between implicit collations "C" and "POSIX"
 431 LINE 1: SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM colla...
 432                                                         ^
 433 HINT:  You can choose the collation by applying the COLLATE clause to one or both expressions.
 434 CREATE TABLE test_u AS SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2; -- fail
 435 ERROR:  no collation was derived for column "b" with collatable type text
 436 HINT:  Use the COLLATE clause to set the collation explicitly.
 437 -- ideally this would be a parse-time error, but for now it must be run-time:
 438 select x < y from collate_test10; -- fail
 439 ERROR:  could not determine which collation to use for string comparison
 440 HINT:  Use the COLLATE clause to set the collation explicitly.
 441 select x || y from collate_test10; -- ok, because || is not collation aware
 442  ?column?
 443 ----------
 444  hijhij
 445  HIJHIJ
 446 (2 rows)
 447
 448 select x, y from collate_test10 order by x || y; -- not so ok
 449 ERROR:  collation mismatch between implicit collations "C" and "POSIX"
 450 LINE 1: select x, y from collate_test10 order by x || y;
 451                                                       ^
 452 HINT:  You can choose the collation by applying the COLLATE clause to one or both expressions.
 453 -- collation mismatch between recursive and non-recursive term
 454 WITH RECURSIVE foo(x) AS
 455    (SELECT x FROM (VALUES('a' COLLATE "C"),('b')) t(x)
 456    UNION ALL
 457    SELECT (x || 'c') COLLATE "POSIX" FROM foo WHERE length(x) < 10)
 458 SELECT * FROM foo;
 459 ERROR:  recursive query "foo" column 1 has collation "C" in non-recursive term but collation "POSIX" overall
 460 LINE 2:    (SELECT x FROM (VALUES('a' COLLATE "C"),('b')) t(x)
 461                    ^
 462 HINT:  Use the COLLATE clause to set the collation of the non-recursive term.
 463 -- casting
 464 SELECT CAST('42' AS text COLLATE "C");
 465 ERROR:  syntax error at or near "COLLATE"
 466 LINE 1: SELECT CAST('42' AS text COLLATE "C");
 467                                  ^
 468 SELECT a, CAST(b AS varchar) FROM collate_test1 ORDER BY 2;
 469  a |  b
 470 ---+-----
 471  4 | ABD
 472  2 | Abc
 473  1 | abc
 474  3 | bbc
 475 (4 rows)
 476
 477 SELECT a, CAST(b AS varchar) FROM collate_test2 ORDER BY 2;
 478  a |  b
 479 ---+-----
 480  4 | ABD
 481  2 | Abc
 482  1 | abc
 483  3 | bbc
 484 (4 rows)
 485
 486 -- polymorphism
 487 SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test1)) ORDER BY 1;
 488  unnest
 489 --------
 490  ABD
 491  Abc
 492  abc
 493  bbc
 494 (4 rows)
 495
 496 SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test2)) ORDER BY 1;
 497  unnest
 498 --------
 499  ABD
 500  Abc
 501  abc
 502  bbc
 503 (4 rows)
 504
 505 CREATE FUNCTION dup (anyelement) RETURNS anyelement
 506     AS 'select $1' LANGUAGE sql;
 507 SELECT a, dup(b) FROM collate_test1 ORDER BY 2;
 508  a | dup
 509 ---+-----
 510  4 | ABD
 511  2 | Abc
 512  1 | abc
 513  3 | bbc
 514 (4 rows)
 515
 516 SELECT a, dup(b) FROM collate_test2 ORDER BY 2;
 517  a | dup
 518 ---+-----
 519  4 | ABD
 520  2 | Abc
 521  1 | abc
 522  3 | bbc
 523 (4 rows)
 524
 525 -- indexes
 526 CREATE INDEX collate_test1_idx1 ON collate_test1 (b);
 527 CREATE INDEX collate_test1_idx2 ON collate_test1 (b COLLATE "POSIX");
 528 CREATE INDEX collate_test1_idx3 ON collate_test1 ((b COLLATE "POSIX")); -- this is different grammatically
 529 CREATE INDEX collate_test1_idx4 ON collate_test1 (((b||'foo') COLLATE "POSIX"));
 530 CREATE INDEX collate_test1_idx5 ON collate_test1 (a COLLATE "POSIX"); -- fail
 531 ERROR:  collations are not supported by type integer
 532 CREATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "POSIX")); -- fail
 533 ERROR:  collations are not supported by type integer
 534 LINE 1: ...ATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "P...
 535                                                              ^
 536 SELECT relname, pg_get_indexdef(oid) FROM pg_class WHERE relname LIKE 'collate_test%_idx%' ORDER BY 1;
 537       relname       |                                           pg_get_indexdef
 538 --------------------+-----------------------------------------------------------------------------------------------------
 539  collate_test1_idx1 | CREATE INDEX collate_test1_idx1 ON collate_test1 USING btree (b)
 540  collate_test1_idx2 | CREATE INDEX collate_test1_idx2 ON collate_test1 USING btree (b COLLATE "POSIX")
 541  collate_test1_idx3 | CREATE INDEX collate_test1_idx3 ON collate_test1 USING btree (b COLLATE "POSIX")
 542  collate_test1_idx4 | CREATE INDEX collate_test1_idx4 ON collate_test1 USING btree (((b || 'foo'::text)) COLLATE "POSIX")
 543 (4 rows)
 544
 545 --
 546 -- Clean up.  Many of these table names will be re-used if the user is
 547 -- trying to run any platform-specific collation tests later, so we
 548 -- must get rid of them.
 549 --
 550 DROP SCHEMA collate_tests CASCADE;
 551 NOTICE:  drop cascades to 12 other objects
 552 DETAIL:  drop cascades to table collate_test1
 553 drop cascades to table collate_test_like
 554 drop cascades to table collate_test2
 555 drop cascades to type testdomain_p
 556 drop cascades to table collate_test4
 557 drop cascades to table collate_test5
 558 drop cascades to table collate_test10
 559 drop cascades to view collview1
 560 drop cascades to view collview2
 561 drop cascades to view collview3
 562 drop cascades to type testdomain
 563 drop cascades to function dup(anyelement)