ghareh@godot:~$psql
psql (9.1.9)
Type "help" for help.
 
ghareh=# \d messages
                            Table "public.messages"
    Column     |            Type             |            Modifiers            
---------------+-----------------------------+---------------------------------
 messageid     | character varying(200)      | not null
 senderaliasid | integer                     | not null
 referenceid   | character varying(200)      | default NULL::character varying
 recipaliasid  | integer                     | 
 datetime      | timestamp(2) with time zone | not null
 subject       | character varying(512)      | not null
 body          | text                        | not null
 listid        | integer                     | 
Indexes:
    "messages_pkey" PRIMARY KEY,btree (messageid)
    "messages_datetime_idx" btree (datetime)
    "recipaliasid_idx" btree (recipaliasid)
    "referenceid_idx" btree (referenceid)
    "senderaliasid_idx" btree (senderaliasid)
Foreign-key constraints:
    "messages_listid_fkey" FOREIGN KEY (listid) REFERENCES lists(listid)
    "messages_recip_fkey" FOREIGN KEY (recipaliasid,listid) REFERENCES aliases(aliasid,listid)
    "messages_sender_fkey" FOREIGN KEY (senderaliasid,listid)
Referenced by:
    TABLE "messages_attachments" CONSTRAINT "pkfkmid" FOREIGN KEY (messageid) REFERENCES messages(messageid)

我的问题涉及专栏,正文和主题.

我有一个生成一组结果的查询.然后,为了优化我的查询,我添加了一个术语：where body就像’％JSON％’,即body的结构子集,其中body包含字符串’JSON’.
我得到了一些包含这个词的结果,有些则没有！但是,如果我搜索任意字符串,结果就可以了.我查了一下,发现查询不只是搜索正文列,还有主题列,这也很疯狂.

这是我的初步查询：

select * from messages where messageid = '44BC310F.1090305@torrez.us'

返回1行：

messageid: "44BC310F.1090305@torrez.us";
senderaliasid: 13777;
referenceid: "7edfeeef0607171746r7d708067g15c77c3aa0ef9158@mail.gmail.com";
recipaliasid: ;
datetime: "2006-07-17 20:53:35-07";
listid: 251;
subject: "Re: svn commit: r422930 - /incubator/abdera/java/trunk/extensions/src/main/java/org/apache/abdera/ext/json/JSONWriter.java";
body: "busted! thanks for the thorough review.
 
 
-Elias
 
Garrett Rooney wrote:
> On 7/17/06,eliast@apache.org <eliast@apache.org> wrote:
>> Author: eliast
>> Date: Mon Jul 17 17:44:10 2006
>> New Revision: 422930
>>
>> URL: http://svn.apache.org/viewvc?rev=422930 (...)"

如果我搜索：

select * from messages
where messageid = '44BC310F.1090305@torrez.us'
and body like '%JSON%'

我不应该得到任何结果,因为身体里没有.但我仍然得到相同的行返回 – 这似乎是因为’JSON’在主题中？

我甚至试过这个：

select * from messages
where messageid = '44BC310F.1090305@torrez.us'
and body like '%incubator/abdera/java/trunk/extensions/src/main/java/org/apache/abdera/ext/json/JSONWriter.java%'

而且我还有同样的一排.我非常困惑.

我试图在sqlfiddle.com上重现结果,但我没有成功.在那里,我得到了sql select查询的预期：
http://sqlfiddle.com/#!1/ec74c/4