(2011/09/05 22:05), Kohei Kaigai wrote:
>> In my usual environment that test passed, but finally I've reproduced the failure with setting
>> $LC_COLLATE to "es_ES.UTF-8". Do you have set any $LC_COLLATE in your test environment?
>>
> It is not set in my environment.
>
> I checked the behavior of ORDER BY when we set a collation on the regular relation, not a foreign table.
> Do we hit something other unexpected bug in collation here?
>
> postgres=# CREATE TABLE t1 (word1 text);
> CREATE TABLE
> postgres=# INSERT INTO t1 VALUES ('ABC'),('abc'),('123'),('NULL');
> INSERT 0 4
> postgres=# ALTER TABLE t1 ALTER word1 TYPE text COLLATE "ja_JP.utf8";
> ALTER TABLE
> postgres=# SELECT * FROM t1 ORDER BY word1;
> word1
> -------
> 123
> ABC
> NULL
> abc
> (4 rows)
>
> postgres=# ALTER TABLE t1 ALTER word1 TYPE text COLLATE "en_US.utf8";
> ALTER TABLE
> postgres=# SELECT * FROM t1 ORDER BY word1;
> word1
> -------
> 123
> abc
> ABC
> NULL
> (4 rows)
Thanks for the checking. FYI, I mainly use Fedora 15 box with Japanese
environment for my development.
ISTM that your results are reasonable for each collation setting.
Former ordering is same as C locale, and in latter case alphabetical
order has priority over case distinctions. Do you mean that ordering
used in file_fdw is affected from something unexpected, without
collation or locale setting?
BTW, I found a thread which is related to this issue.
http://archives.postgresql.org/pgsql-hackers/2011-09/msg00130.php
I changed the test data so that it uses only upper case alphabets,
because case distinction is not important for that test. I also removed
digits to avoid test failure in some locales which sort alphabets before
digits.
Regards,
--
Shigeru Hanada