Home Dashboard Directory Help

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



Sign in
to vote
Type: Bug
ID: 780132
Opened: 2/27/2013 9:37:52 AM
Access Restriction: Public
User(s) can reproduce this bug


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 @ http://danieladeniji.wordpress.com/2013/11/22/technical-microsoft-sql-server-full-text-search-and-the-problem-with-nullempty-predicates/ (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! :)
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
Transact SQL abbreviated example.txt 2/27/2013 1 KB