PostgreSQL的调试为其中'A'＆LT; '一个' [英] Debugging postgresql for where 'A' < 'a'

问题描述

在在Postgres 9.1一个简单的对比测试和8.4是得到以下奇怪的结果。

In a simple comparison test in postgres 9.1 and 8.4 is get the following weird results.

postgres=# select 1 one where 'A' < 'a';
 one 
-----
(0 rows)    // ..... I would have expected 1 row

postgres=# select 1 one where 'A' < 'b';
 one 
-----
   1
(1 row)    // ...... this looks OK

postgres=# select 1 one where 'A' = 'a';
 one 
-----
(0 rows)   // ...... This also looks OK

postgres=# select 1 one where 'A' > 'a';
 one 
-----
   1
(1 row)    // ...... This is inconsistent with the above results

的'A'的ASCII值是0×41和'一'是0x61所以ASCII值的直线比较应该意味着'A'是不是'一'小，或者某些情况下insentive魔法那么至少A> B和Alocale问题，但随后又 - 但是我的地方被设置为使用标准Centos5和Fedora16安装有相同的结果标准us_EN.utf8设置

The ascii value of 'A' is 0x41 and 'a' is 0x61 so a straight comparison of ascii values should mean that 'A' is smaller than 'a', or if some case insentive magic then at least A>b and Alocale problem, but then again -- however my local is set to a standard us_EN.utf8 setting using a standard Centos5 and Fedora16 installations with same results.

附加一个调试器的Postgres的过程中，我已经能够跟踪，问题来自于;

Attaching a debugger to the postgres process, I have been able to track down that the problem comes from that;

strcoll("A","a") returns 6;

其中，

strcoll("A","b") returns -1;

然而，这只能从Postgres的进程中证明（如GDB连接时），以及类似下面的外部程序提供了完美合理的结果。

However this can only be demonstrated from inside the postgres process (such as when attaching gdb), and an external program like the one below gives perfectly reasonable results.

main()
{
    char *a="a";
    char *b="b";
    char *A="A";

    printf("%s\n",setlocale(2,"us_ENG.utf8"));

    printf("%d\n",strcoll(A,a));
    printf("%d\n",strcoll(A,b));
    printf("%d\n",strcoll(a,a));
    printf("%d\n",strcoll(b,b));

    printf("%d\n",strcoll(a,A));
    printf("%d\n",strcoll(b,A));
    printf("%d\n",strcoll(b,a));
    printf("%d\n",strcoll(A,A));
}

的问题是：没有任何人有任何想法，什么会导致的strcoll返回错误值，并且任何建议如何解决它，所以我的样本SQL将正常工作

Question is: does anybody have any idea as to what would cause strcoll to return bad values, and any suggestion as how to fix it so my sample SQL will work correctly.

更新：我试图重新创建数据库 initdb的--locale = C ，而'A'＆LT;'一'给有预期的结果 - 但这并不解释为什么失败作为UTF-8创建的数据库

Update: I tried to recreate the database as initdb --locale=C, and the 'A'<'a' give expected results there -- however that does not explain why this fails in a database created as UTF-8.

推荐答案

序取决于您的数据库<一个href=\"http://www.postgresql.org/docs/current/interactive/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT\"相对=nofollow>区域，而不是系统区域设置。 （虽然应该注意的是PostgreSQL的依赖于操作系统提供细节上。而Postgres维基更多）结果
ASCII值仅与非​​语言环境C相关

Ordering depends on your database locale, not system locale. (Though it should be noted that PostgreSQL relies on the OS to provide specifics. More in the Postgres Wiki.)
The ASCII value is only relevant with the non-locale "C".

在当前设置请看下图：

SELECT * FROM pg_settings WHERE name ~~ 'lc%';

在具体地，设置 LC_COLLATE 是相关的。您还可以：

In particular, the setting for LC_COLLATE is relevant. You can also:

SHOW lc_collate;

在PostgreSQL 9.1你可以改变每个语句适用的排序规则。尝试：

In PostgreSQL 9.1 you can change the applicable collation per statement. Try:

SELECT 1 AS one WHERE 'A' < 'a' COLLATE "C";

在旧版本的你（大部分）坚持与您选择创建数据库集群的时候为 LC_COLLATE 的值。

In older versions you are (mostly) stuck with the value for LC_COLLATE that you chose when creating your database cluster.

这篇关于PostgreSQL的调试为其中'A'＆LT; '一个'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！