Boolean queries not working over the all columns in the index   

If I execute a query such as this one, I get back records that contain 
"DaVinci" in AddressLine1 column and "Kirkland" in the City column:

select ft.id, ft.name, Line1, Line2, Line3, line4, city, county
from company as ft
inner join containstable(company, * , '"barnes nobl*"') as [key]
on ft.id = [key].[key]
inner join containstable( Address, *, 'davinci') as [key2]
inner join Address a on a.ID = [key2].[key]
 on a.OwnerRecordID = ft.id
order by ft.id

Both the abovementioned columns are part of the address index.  However if I 
add a boolean operator to the above query:

select ft.id, ft.name, Line1, Line2, Line3, line4, city, county
from company as ft
inner join containstable(company, * , '"barnes nobl*"') as [key]
on ft.id = [key].[key]
inner join containstable( Address, *, 'davinci and kirkland') as [key2]
inner join Address a on a.ID = [key2].[key]
 on a.OwnerRecordID = ft.id
order by ft.id


I don't get any records.   It appears that if a boolean operator is in the 
full-text query then the full text search is constrained to only one column.  
This is not the first time I have encountered this.  Is this a bug?  

I am using the Katmai version of Sql Server

date: Thu, 10 Jul 2008 04:06:00 -0700   author:   markeboy

Re: Boolean queries not working over the all columns in the index   

markeboy wrote  on Thu, 10 Jul 2008 04:06:00 -0700:

> If I execute a query such as this one, I get back records that contain
> "DaVinci" in AddressLine1 column and "Kirkland" in the City column:

> select ft.id, ft.name, Line1, Line2, Line3, line4, city, county from
> company as ft inner join containstable(company, * , '"barnes nobl*"')
> as [key]
> on ft.id = [key].[key]
> inner join containstable( Address, *, 'davinci') as [key2]
> inner join Address a on a.ID = [key2].[key]
>  on a.OwnerRecordID = ft.id order by ft.id

> Both the abovementioned columns are part of the address index.  However
> if I  add a boolean operator to the above query:

> select ft.id, ft.name, Line1, Line2, Line3, line4, city, county from
> company as ft inner join containstable(company, * , '"barnes nobl*"')
> as [key]
> on ft.id = [key].[key]
> inner join containstable( Address, *, 'davinci and kirkland') as [key2]
> inner join Address a on a.ID = [key2].[key]
>  on a.OwnerRecordID = ft.id order by ft.id


> I don't get any records.   It appears that if a boolean operator is in
> the  full-text query then the full text search is constrained to only
> one column.
> This is not the first time I have encountered this.  Is this a bug?

> I am using the Katmai version of Sql Server


No, it's not a bug, it is in the documentation. Booleans are only ever 
checked against a single column, not across all columns.

-- 
Dan

date: Mon, 14 Jul 2008 12:00:23 +0100   author:   Daniel Crichton