Very Computer

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.

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.

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.

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

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

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

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

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.