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
Daniel Crichton - 14 Jul 2008 12:00 GMT
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*"')
[quoted text clipped - 3 lines]
> 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*"')
[quoted text clipped - 3 lines]
> 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.

Signature
Dan