7.0.4 Service Release 1 and Search

[dc8johnson]

We've installed Service Release 1 and the search is working much better but there seems to be an inconsistency problem.

I ran a simple search using two words - booth* filter* - (asterisks included).

Using the SQL Server Profiler I captured the four queries that were run. The first query correctly returns the total number of products, the second query correctly returns the total number products per manufacturer, the four query correctly returns the total number of products for the current category. The third query returns no results. The first, second and fourth queries use the fulltext CONTAINS feature while the third query uses LIKE.

If I change the simple search to just - booth filter - then the third query uses CONTAINS like the counts and returns the sames results. The (booth* filter*) returns the 8 results I'm looking for versus the 6 results from the (booth filter) search because two results contain "booth filters". I can manually run the Revised Query 3 to include the wild cards and it returns the 8 results.

I've double checked my files against the SR1 files and everything seems to be up to date. Did I miss something or is this how the search was designed to work or is there a problem?

Thanks for your help,

Query 1

Code: Select all

exec sp_executesql N'SELECT COUNT(DISTINCT P.ProductId) As TotalProducts FROM  (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) WHERE PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM ac_Products P WHERE P.StoreId = @storeId AND CONTAINS(P.*, @keyword) AND P.VisibilityId = 0)',N'@storeId int,@keyword nvarchar(22)',@storeId=1,@keyword=N'"booth*" and "filter*"'

Query 2

Code: Select all

exec sp_executesql N'SELECT M.ManufacturerId, M.Name, COUNT(DISTINCT P.ProductId) As ProductCount FROM (ac_Products P LEFT JOIN ac_Manufacturers M ON P.ManufacturerId = M.ManufacturerId) WHERE P.StoreId = @storeId AND CONTAINS(P.*, @keyword) AND P.VisibilityId = 0 GROUP BY M.ManufacturerId, M.Name ORDER BY M.Name',N'@storeId int,@keyword nvarchar(22)',@storeId=1,@keyword=N'"booth*" and "filter*"'

Query 3 (booth* filter*)

Code: Select all

exec sp_executesql N'SELECT  TOP 12 P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured, FTS.RANK, MIN(PC.OrderBy) AS OrderBy FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) INNER JOIN (SELECT [KEY], SUM(weightRank) as [RANK]
FROM
(
SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Name, @keyword)
UNION ALL
SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Sku, @keyword)
UNION ALL
SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ModelNumber, @keyword)
UNION ALL
SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, SearchKeywords, @keyword)
UNION ALL
SELECT [KEY], (RANK * 1.5) AS weightRank FROM CONTAINSTABLE(ac_Products, Summary, @keyword)
UNION ALL
SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, [Description], @keyword)
UNION ALL
SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ExtendedDescription, @keyword)
) AS INNERFTS
GROUP BY [KEY]) AS FTS ON FTS.[KEY] = P.ProductId WHERE PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM ac_Products P WHERE P.StoreId = @storeId AND (P.Name LIKE @keyword OR P.SKU LIKE @keyword OR P.ModelNumber LIKE @keyword OR P.Summary LIKE @keyword OR P.Description LIKE @keyword OR P.ExtendedDescription LIKE @keyword OR P.SearchKeywords LIKE @keyword) AND P.VisibilityId = 0) GROUP BY  P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured, FTS.RANK  ORDER BY Name ASC',N'@storeId int,@keyword nvarchar(22)',@storeId=1,@keyword=N'"booth%" and "filter%"'

Query 4

Code: Select all

exec sp_executesql N'SELECT COUNT(DISTINCT P.ProductId) As TotalProducts FROM  (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) WHERE PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) WHERE P.StoreId = @storeId AND CONTAINS(P.*, @keyword) AND PC.CatalogNodeTypeId = 1 AND PC.CategoryId IN (SELECT CategoryId FROM ac_CategoryParents WHERE ParentId = @categoryId) AND P.VisibilityId = 0)',N'@storeId int,@keyword nvarchar(22),@categoryId int',@storeId=1,@keyword=N'"booth*" and "filter*"',@categoryId=14

Revised Query 3 (booth filter)

Code: Select all

exec sp_executesql N'SELECT  TOP 12 P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured, FTS.RANK, MIN(PC.OrderBy) AS OrderBy FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) INNER JOIN (SELECT [KEY], SUM(weightRank) as [RANK]
FROM
(
SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Name, @keyword)
UNION ALL
SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Sku, @keyword)
UNION ALL
SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ModelNumber, @keyword)
UNION ALL
SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, SearchKeywords, @keyword)
UNION ALL
SELECT [KEY], (RANK * 1.5) AS weightRank FROM CONTAINSTABLE(ac_Products, Summary, @keyword)
UNION ALL
SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, [Description], @keyword)
UNION ALL
SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ExtendedDescription, @keyword)
) AS INNERFTS
GROUP BY [KEY]) AS FTS ON FTS.[KEY] = P.ProductId WHERE PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM ac_Products P WHERE P.StoreId = @storeId AND CONTAINS(P.*, @keyword) AND P.VisibilityId = 0) GROUP BY  P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured, FTS.RANK  ORDER BY Name ASC',N'@storeId int,@keyword nvarchar(20)',@storeId=1,@keyword=N'"booth" and "filter"'

[Logan Rhodehamel]

I remember discovering an issue with wild cards that is along the lines of what you describe. I will try to reproduce your results right now and also check to ensure that if this problem was fixed it was correctly patched into the 7.0.4 codebase.

[Logan Rhodehamel]

The current working set of the code does not seem to exhibit the problem. It is possible an update was missed in the patch. I am now going to try to reproduce on the 7.0.4 version.

[Logan Rhodehamel]

A clean 7.0.4 install does seem to exhibit the problem you describe. It appears the correction for this symptom was not included in the patch.