FREETEXT() does not honor order of evaluation in CASE statements (no aggregates involved) - by Cornan The Iowan

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 780132 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 2/27/2013 9:37:52 AM
Access Restriction Public


Even though there are no aggregates involved in my CASE statements (the effects of aggregates are documented), I have discovered that FREETEXT() fails to honor order of evaluation in CASE statements.
Sign in to post a comment.
Posted by Daniel Adeniji on 11/23/2013 at 8:28 AM
Microsoft and Amit:

I think this problem is quite common with Full-Text Searches; as it affects both Contain/Freetext.

I first observed it over 10 years ago and re-visited it this week.

I posted my own experience @ (Technical: Microsoft – SQL Server – Full Text Search and the problem with null/empty predicates).

It appears that the problem is the "disjointed" query path between regular sql and fulltext sql.

But, we will for additional feedback \ workarounds; that is hopefully forthcoming.

BTW, Tested with MS SQL Server v2008/R2 - SP2.

Daniel Adeniji
Posted by Microsoft on 3/5/2013 at 4:21 PM

Looking at the original query posted, it seems like you are using a searched case expression. This evaluates a set of boolean expressions only(unlike the simple case which compares expressions). In this case, even though the first check is boolean, the second(i.e. FREETEXT()) isn't which could be resulting in the observed behavior.

Posted by Paul White NZ on 2/27/2013 at 4:28 PM
I encountered another example using LAG recently. The CASE order-of-evaluation 'guarantee' seems shakier by the day! :)