SQL Server 2000:Incorrect results from full-text search on several columns

SQL Server 2000:Incorrect results from full-text search on several columns

Post by maha hamda » Tue, 12 Jun 2001 16:07:22



hi,
i'm trying to use the AND operator between search conditions in a CONTAINS
predicate to search all columns of table 'categories' in database 'northwind'.

the following query returns 0 rows:

USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE CONTAINS( *, ' beers AND beverages' )
GO

while the following query returns 1 row:

USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE CONTAINS( *, ' beers') AND  CONTAINS( *, 'beverages')
GO

Note that the value 'beverages' occurs in column 'categoryname' while the value
'beers' occurs in column 'Description'.
i know that this behaviour was confirmed a bug in sql server 7.0 (bug #:58135)
but was fixed in sp3.

 
 
 

SQL Server 2000:Incorrect results from full-text search on several columns

Post by maha hamda » Tue, 12 Jun 2001 16:07:25


hi,
i'm trying to use the AND operator between search conditions in a CONTAINS
predicate to search all columns of table 'categories' in database 'northwind'.

the following query returns 0 rows:

USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE CONTAINS( *, ' beers AND beverages' )
GO

while the following query returns 1 row:

USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE CONTAINS( *, ' beers') AND  CONTAINS( *, 'beverages')
GO

Note that the value 'beverages' occurs in column 'categoryname' while the value
'beers' occurs in column 'Description'.
i know that this behaviour was confirmed a bug in sql server 7.0 (bug #:58135)
but was fixed in sp3.

 
 
 

SQL Server 2000:Incorrect results from full-text search on several columns

Post by Morten Olse » Tue, 12 Jun 2001 18:19:11


Hi maha,

Are you serious about this behaviour being fixed in SP3 for SQL7?

I'm running SQL7 with SP3 on NT4 and I'm having the same problem as you. It
seems that the AND in the CONTAINS criteria, means that both conditions must
be true within a single column. And I'm (like you) trying to search the
whole row.
Also I read through the fixlist for SP3, and there is not fix for a bug
58135. Actually they are listed in numerically order and the bugfix after
58133 is 58137.

So where did you get this from? I'd appreciate a link.

Regards,
Morten


hi,
i'm trying to use the AND operator between search conditions in a CONTAINS
predicate to search all columns of table 'categories' in database
'northwind'.

the following query returns 0 rows:

USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE CONTAINS( *, ' beers AND beverages' )
GO

while the following query returns 1 row:

USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE CONTAINS( *, ' beers') AND  CONTAINS( *, 'beverages')
GO

Note that the value 'beverages' occurs in column 'categoryname' while the
value
'beers' occurs in column 'Description'.
i know that this behaviour was confirmed a bug in sql server 7.0 (bug
#:58135)
but was fixed in sp3.

 
 
 

SQL Server 2000:Incorrect results from full-text search on several columns

Post by maha » Tue, 12 Jun 2001 22:05:06


Q286787 - FIX Incorrect Results From Full-Text Search on Several Columns.htm

  Q286787 - FIX Incorrect Results From Full-Text Search on Several Columns.htm
20K Download
 
 
 

SQL Server 2000:Incorrect results from full-text search on several columns

Post by Morten Olse » Tue, 12 Jun 2001 22:38:43


Hi maha,

I belive the article, speaks about something quite different, than what you
are trying to do. It describes an error, that occurs when searching for a
phrase. The error is somewhat, what we both want; to ignore the barrier
between columns.
If you look at the last part of the article, it shows an example, where the
query,

   SELECT t1.* FROM t1 WHERE CONTAINS (*, ' "Sean Moore" '),

will give the result

     pk    col_1             col_2
     ----- ----------------- ------------------
     1     Sean Connery      Roger Moore

And the correct answer should be no rows, because the phrase "Sean Moore"
does not occur in any of the columns. Only "Sean" and "Moore" individaully.
But I guess the "wrong" answer, is what we are trying to do, with AND.
Perhaps the workaround for getting SQL Server to behave, is uninstalling SP3
and using phrase-searching instead of AND ?-)

Regards,
Morten


Q286787 - FIX Incorrect Results From Full-Text Search on Several Columns.htm

 
 
 

SQL Server 2000:Incorrect results from full-text search on several columns

Post by Morten Olse » Tue, 12 Jun 2001 22:38:48


Hi maha,

I belive the article, speaks about something quite different, than what you
are trying to do. It describes an error, that occurs when searching for a
phrase. The error is somewhat, what we both want; to ignore the barrier
between columns.
If you look at the last part of the article, it shows an example, where the
query,

   SELECT t1.* FROM t1 WHERE CONTAINS (*, ' "Sean Moore" '),

will give the result

     pk    col_1             col_2
     ----- ----------------- ------------------
     1     Sean Connery      Roger Moore

And the correct answer should be no rows, because the phrase "Sean Moore"
does not occur in any of the columns. Only "Sean" and "Moore" individaully.
But I guess the "wrong" answer, is what we are trying to do, with AND.
Perhaps the workaround for getting SQL Server to behave, is uninstalling SP3
and using phrase-searching instead of AND ?-)

Regards,
Morten


Q286787 - FIX Incorrect Results From Full-Text Search on Several Columns.htm

 
 
 

SQL Server 2000:Incorrect results from full-text search on several columns

Post by Morten Olse » Tue, 12 Jun 2001 22:38:21


Hi maha,

I belive the article, speaks about something quite different, than what you
are trying to do. It describes an error, that occurs when searching for a
phrase. The error is somewhat, what we both want; to ignore the barrier
between columns.
If you look at the last part of the article, it shows an example, where the
query,

   SELECT t1.* FROM t1 WHERE CONTAINS (*, ' "Sean Moore" '),

will give the result

     pk    col_1             col_2
     ----- ----------------- ------------------
     1     Sean Connery      Roger Moore

And the correct answer should be no rows, because the phrase "Sean Moore"
does not occur in any of the columns. Only "Sean" and "Moore" individaully.
But I guess the "wrong" answer, is what we are trying to do, with AND.
Perhaps the workaround for getting SQL Server to behave, is uninstalling SP3
and using phrase-searching instead of AND ?-)

Regards,
Morten


Q286787 - FIX Incorrect Results From Full-Text Search on Several Columns.htm

 
 
 

SQL Server 2000:Incorrect results from full-text search on several columns

Post by Trevor Dwye » Wed, 13 Jun 2001 12:22:21


Hi,

Details on this problems can be found in a recently published KB article
Q294809 titled 'Full-Text Search Queries with Contains Clause Do Not Search
All Columns.' - it applies to SQL Server 7.0 sp3 and SQL Server 2000.

Your second 'correct' query is also the same as the suggested workaround
although they also suggest placing all the data into one column.

Best Regards

Trevor Dwyer - SQL Server MVP


hi,
i'm trying to use the AND operator between search conditions in a CONTAINS
predicate to search all columns of table 'categories' in database
'northwind'.

the following query returns 0 rows:

USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE CONTAINS( *, ' beers AND beverages' )
GO

while the following query returns 1 row:

USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE CONTAINS( *, ' beers') AND  CONTAINS( *, 'beverages')
GO

Note that the value 'beverages' occurs in column 'categoryname' while the
value
'beers' occurs in column 'Description'.
i know that this behaviour was confirmed a bug in sql server 7.0 (bug
#:58135)
but was fixed in sp3.

 
 
 

1. difference between SQL Server 7.0 and SQL Server 2000 full-text search

I've noticed a difference between SQL Server 7.0 and SQL Server 2000
full-text search.  In a text field that contains:

... <TITLE>Microsoft</TITLE>  ...

SQL Server 7.0 will but SQL Server 2000 will not return a result for the
following queries:

select * from table where contains(text_field, 'Microsoft') or
select * from table where contains(text_field, '"Microsoft"')

My 'default full-text language'  setting is for US english.
exec sp_configure 'default full-text language'
shows
default full-text language          0           2147483647  1033
1033

It appears that language neutral full-text is implicitly turned on for SQL
Server 2000 because adding the <TITLE> start and end tags to the search
string will return a result for SQL Server 2000.

Steve

2. Add a stored Procedure to a MergeArticle via DMO

3. Searching on XML-Tag with SQL Server 2000 and Full-Text

4. Node name change

5. SQL Server 2000 - Full Text Search - Where to Start

6. Two Distinct Count measures

7. Full-text search doesn't work on SQL Server 2000 Developers Edition

8. Self/Outer Join

9. any document for full text search setup in sql server 2000

10. a couple of questions - win2k Adv server - SQL 2000 cluster - full text search

11. any document for full text search setup in sql server 2000

12. full-text searches on PDF files SQL Server 2000

13. SQL Server 2000: Full-Text Search Chat