Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Full-Text Search / July 2008

Tip: Looking for answers? Try searching our database.

Boolean queries not working over the all columns in the index

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
markeboy - 10 Jul 2008 12:06 GMT
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

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.