Search in all indexed columns + operator "NOT"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matthias Kientz - 06 Dec 2004 15:19 GMT

I have a fulltext indexed table (aTest) which look like this
(strCaption and strText are both fulltext indexed columns):

lngStoryId strCaption strText
----------- ------------------------ ------------------------------
1 Kerry Bush meets Kerry

Now, let's search:

SELECT strCaption, strText
FROM aTest WHERE CONTAINS(aTest.*,'kerry AND NOT bush')

strCaption strText
------------------------------ ------------------------------
Kerry Bush meets Kerry

(1 row(s) affected)

This is not what I've expected!
It seems to be true, that I'll get a result if my COMPLETE search term
matches ANY of the indexed columns. I would expect, that "NOT" excludes
such results - irrespective of the matching column.

Is it really true, that SQL Server works like this? I've not found which
rules are used in this case in the server documentation.

Thanks in advance
Matthias

Reply to this Message

Hilary Cotter - 06 Dec 2004 15:44 GMT

try

SELECT strCaption, strText
FROM aTest WHERE CONTAINS(aTest.*,'"kerry" AND NOT "bush"')

Signature

Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html

>I have a fulltext indexed table (aTest) which look like this
> (strCaption and strText are both fulltext indexed columns):
[quoted text clipped - 24 lines]
> Thanks in advance
> Matthias

Reply to this Message

Matthias Kientz - 08 Dec 2004 17:13 GMT

> try
>
> SELECT strCaption, strText
> FROM aTest WHERE CONTAINS(aTest.*,'"kerry" AND NOT "bush"')

Thanks, but using double quotes makes no difference.

Reply to this Message

Hilary Cotter - 08 Dec 2004 17:28 GMT

This works for me. Are the words kerrey and bush in the same column?

Signature

Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html

>> try
>>
>> SELECT strCaption, strText
>> FROM aTest WHERE CONTAINS(aTest.*,'"kerry" AND NOT "bush"')
>
> Thanks, but using double quotes makes no difference.

Reply to this Message

Matthias Kientz - 08 Dec 2004 23:40 GMT

> This works for me. Are the words kerrey and bush in the same column?

No, they're not in the same column.
The table looks like this:

lngStoryId strCaption strText
----------- ------------------------ ------------------------------
1 Kerry Bush meets Kerry

If I ask the whole index for "X AND NOT Y" (in my example "Kerry AND NOT
Bush"), I would expect that all results are excluded which contain Y in
ANY indexed column.

Please see my last answer to John Kane for a more details of this problem.

Reply to this Message

John Kane - 06 Dec 2004 16:31 GMT

Matthias,
The following is from SQL Server 2000 BOL (Books Online) title "CONTAINS":
"AND | AND NOT | OR - Specifies a logical operation between two contains
search conditions.
When <contains_search_condition> contains parenthesized groups, these
parenthesized groups are evaluated first.
After evaluating parenthesized groups, these rules apply when using these
logical operators with contains search conditions:
NOT is applied before AND.
NOT can only occur after AND, as in AND NOT. The OR NOT operator is not
allowed.
NOT cannot be specified before the first term (for example, CONTAINS
(mycolumn, 'NOT "phrase_to_search_for" ' ).
AND is applied before OR.
Boolean operators of the same type (AND, OR) are associative and can
therefore be applied in any order.

While it is undocumente, you can also use the syntax (|, &, and !) for the
OR, AND, NOT operators. The following are some examples of using a stored
proc and NOT in SQL FTS queries:

use pubs
go
-- DROP PROCEDURE usp_FTSearchPubsInfo
CREATE PROCEDURE usp_FTSearchPubsInfo ( @vcSearchText varchar(7800))
AS
declare @s as varchar (8000)
set @s='select pub_id, pr_info from pub_info where
contains(pr_info,'+''''+@vcSearchText+''''+')'
exec (@s)
go
-- returns 2 rows
EXEC usp_FTSearchPubsInfo '("books" and "publisher")'
go
-- returns 0 rows
EXEC usp_FTSearchPubsInfo '("books" and not "publisher")'
go
-- returns 2 rows
EXEC usp_FTSearchPubsInfo '("book*") and not ("food" or "work")'
go
-- returns 2 rows when NOT, not included and 0 rows when NOT is included
SELECT p.pub_id, p.pr_info, c.[rank]
from pub_info AS p,
containstable(pub_info, *, '"books" and NOT "publisher"') as c
where c.[KEY] = p.pub_id
order by c.[rank]

Hope this helps!
John

> I have a fulltext indexed table (aTest) which look like this
> (strCaption and strText are both fulltext indexed columns):
[quoted text clipped - 24 lines]
> Thanks in advance
> Matthias

Reply to this Message

Matthias Kientz - 08 Dec 2004 19:14 GMT

> Matthias,
> The following is from SQL Server 2000 BOL (Books Online) title "CONTAINS":
[quoted text clipped - 12 lines]
> Boolean operators of the same type (AND, OR) are associative and can
> therefore be applied in any order.

That's all clear.
My problem is the behavior of SQL Server while quering a fulltext
catalog which contains more than one fulltext column.

My query was:

>>SELECT strCaption, strText
>>FROM aTest WHERE CONTAINS(aTest.*,'kerry AND NOT bush')
[quoted text clipped - 4 lines]
>>
>>(1 row(s) affected)

Note that, strCaption and strText are both fulltext indexed.
SQL Server seems to search internal like this:

SELECT strCaption, strText FROM aTest
WHERE
(
CONTAINS(aTest.strCaption,'kerry and not bush')
)
OR
(
CONTAINS(aTest.strText,'kerry and not bush')
)

But what I want to do is that (this is also my workaround):

SELECT strCaption, strText FROM aTest
WHERE CONTAINS(aTest.*,'kerry')
AND NOT CONTAINS (aTest.*,'bush')

==> I have to parse the search string and check whether there is a "NOT"
operator used, and use the workaround then.

I'm not really sure, whether this is an error or not, but I think this
is an unexpected and not very well documented "feature".

Reply to this Message

John Kane - 09 Dec 2004 06:22 GMT

Matthias,
Thank you for provide more info as querying multiple FT-enabled columns that
also include a NOT condition should be possible with the below example using
the Pubs database, Authors table with the last name & city columns as an
example. However, another important factor is the OS platform that you have
SQL Server (2000 or 7.0 ?) installed. Could you post the full output of --
SELECT @@version

use pubs
go
select pub_id, pr_info from pub_info
where contains(*,'"moon"') and NOT contains(*,'"Binnet"')
go
-- or using CONTAINSTABLE
SELECT FT_TBL.au_id, FT_TBL.au_lname, FT_TBL.au_fname, FT_TBL.city,
KEY_TBL.RANK
FROM authors as FT_TBL,
CONTAINSTABLE (authors,city, '"jose" and NOT "city"' ) AS KEY_TBL,
CONTAINSTABLE (authors,au_fname, 'Michael' ) AS KEY_TBL1
WHERE
FT_TBL.au_id = KEY_TBL.[KEY] or
FT_TBL.au_id = KEY_TBL1.[KEY]
/* -- returns:
au_id au_lname au_fname
city RANK
----------- ---------------------------------------- -------------------- --
------------------ -----------
267-41-2394 O'Leary Michael
San Jose 80
(1 row(s) affected)
*/

You may have to alter the OR condition between the KEY_TBL & FT_TBL.au_id to
an AND condition as well as need to use a DISTINCT qualifier in your select
list as well to get the desired results that you want. Keep in mind that
this is a workaround to a by design behavior for SQL Server 2000 as the SQL
Server 7.0 behavior was *fixed* to conform to the default SQL Server 2000
behavior, documented in the following KB articles:

286787 (Q286787) FIX: Incorrect Results From Full-Text Search on Several
Columns
http://support.microsoft.com/default.aspx?scid=kb;en-us;286787
294809 (Q294809) FIX: Full-Text Search Queries with CONTAINS Clause Search
Across Columns
http://support.microsoft.com/default.aspx?scid=kb;en-us;294809

This is not an error (or bug) as it is by design, but I would agree that
this is a DOC bug, i.e., a documentation bug, as it is not a very well
documented feature...
Regards,
John

> > Matthias,
> > The following is from SQL Server 2000 BOL (Books Online) title "CONTAINS":
[quoted text clipped - 52 lines]
> I'm not really sure, whether this is an error or not, but I think this
> is an unexpected and not very well documented "feature".

Reply to this Message

Matthias Kientz - 09 Dec 2004 16:32 GMT

> Matthias,
> Thank you for provide more info as querying multiple FT-enabled columns that
[quoted text clipped - 3 lines]
> SQL Server (2000 or 7.0 ?) installed. Could you post the full output of --
> SELECT @@version

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
    Dec 17 2002 14:22:05
    Copyright (c) 1988-2003 Microsoft Corporation
    Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

> use pubs
> go
[quoted text clipped - 39 lines]
> Regards,
> John

Yes, we agree in that.

It's a pitty, that the design does not distinguish between positive and
negative logic in the query, so the behavior is not the intuitive way to
search in a multi-column index.
I think I have to take it as it is and use my workaround to get the
results which are expected.

Thanks for your help
Matthias

Reply to this Message