SQL Server – Full Text Search and the problem with null/empty predicates

Introduction

Early this week, I was looking into OData and that lead me down the path of reviewing one of Microsoft’s Implementation pattern.  That particular pattern has heavy reliance on Entity Framework (EF) and Windows Communication Framework (WCF).

Background

One of the good things about being a DBA is that the little code that one writes is often hidden away in a Database somewhere.  If it is at all peer reviewed, it only occurs during inception.

Entity Framework

As Entity Framework is an Object Relation Mapping (ORM) offering it relies quite a bit on writing its own data manipulation boiler-plate access code.

That is, it nicely covers the traditional Insert/Update/Delete code for each mapped object.

In essence, one has less SQL and SQL Procedure code to write.

Margin

But, we all know that LIFE is not always black and white.  And, so one still has a bit of code to write.  In this case, I have one Stored Procedure that searches for data that matches an end-user’s inquiry.

Sample Code

Sample Introductory Code

The sample code below does the following things:

  • Takes as input two arguments applicationTypeID and searchTag.
  • Neither of the two arguments are required, the programmer\user can send in either of them, both of them, or neither
  • And, so the Transact SQL covers the various scenarios

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

if object_id('[dbo].[usp_GetApplicationList_FilteringByID_Blog]') is null
begin

  exec(
      'create procedure [dbo].[usp_GetApplicationList_FilteringByID_Blog] 
            as select 1/0 as [undefined]
      '
            )

end
go

ALTER procedure [dbo].[usp_GetApplicationList_FilteringByID_Blog]
(
   	  @ApplicationTypeID uniqueidentifier = null
	, @SearchTag  nvarchar(255) = null
)
as

	/*
	  IF @SearchTag is null
	  BEGIN
		SET @SearchTag = '""'
	  END

	*/

	select tblApp.*
	from   dbo.Application tblApp
	where  
	    (

		   (	
		           (@SearchTag is null)			
			or (@SearchTag = '')	
			or (@SearchTag = '""')								
			or 
			   (
				contains (tblApp.*, @searchTag )

				--FREETEXT(tblApp.*, @SearchTag)

			   )
                    )

		    and
			  (	
				   (@ApplicationTypeID is null)	
				or (tblApp.appTypeID = @ApplicationTypeID)
			   )

	  )

go

Explanation

Everything works well until the user sends in an empty or null searchTag.  When that happens we run into an immutable error. The error message reads “Null or empty full-text predicate“.

Query Invocation Syntax:


declare @ApplicationTypeID varchar(255) = null
declare @searchTag varchar(255) = null

exec [dbo].[usp_GetApplicationList_FilteredByID_Blog]
         @ApplicationTypeID = @ApplicationTypeID
       , @SearchTag = @SearchTag

Output:


Msg 7645, Level 15, State 1, 
Procedure usp_GetApplicationList_FilteringByID_Blog, Line 17
Null or empty full-text predicate.

Sample Code – ‘Escape’ the Search Tag

Sample Introductory Code

The sample code below does the following things:

  • Within the SQL Script, it tries escaping the Search Tag

This is sample modification:


--contains(tblApp.*, @searchTag)
contains(tblApp.*, isNull(@searchTag, '71zahagtt1')

Full SQL:


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

if object_id('[dbo].[usp_GetApplicationList_FilteringByID_Blog]') is null
begin

    exec(
      'create procedure [dbo].[usp_GetApplicationList_FilteringByID_Blog] 
          as select 1/0 as [undefined]
     '
        )

end
go

ALTER procedure [dbo].[usp_GetApplicationList_FilteringByID_Blog]
(
   	  @ApplicationTypeID uniqueidentifier = null
	, @SearchTag  nvarchar(255) = null
)
as

    /*
	IF @SearchTag is null
	BEGIN
	  SET @SearchTag = '""'
	END

    */

    select tblApp.*
    from   dbo.Application tblApp
    where  
	(

	   (	
	           (@SearchTag is null)			
		or (@SearchTag = '')	
		or (@SearchTag = '""')								
		or 
		   (
			--	contains (tblApp.*, @searchTag )
                        contains (tblApp.*, isNull(@searchTag, '161HYQ')
			--FREETEXT(tblApp.*, @SearchTag)

		   )
            )
   	    and
	    (	
		   (@ApplicationTypeID is null)						or (
		     (tblApp.appTypeID = @ApplicationTypeID)
		   )
	    )				

       )

go

Push Back

But, the code does not even compile:


Msg 102, Level 15, State 1
Procedure usp_GetApplicationList_FilteringID_Blog, Line 30
Incorrect syntax Near 'IsNull'

Tried various other things

Stubbornly tried various other things, but found out that once SQL Server sees the “contain” operator, there is quite a bit of restriction as how one can decorate the operands.

Sample Code – Replace Contains with FreeText

Sample Introductory Code

The sample code below does the following things:

  • Within the SQL Script, replaces contains with freetext

This is sample modification:


--contains(tblApp.*, @searchTag)
freetext(tblApp.*, @searchTag)

Error:

But, our results was same as using the “contains” operator

Sample Code – Tried Case Statement

Trying Case Statement

So I thought to myself, why allow myself to be so easily pushed around by SQL Server. Edged on by my web friends, I decided to try the case statement.


	     --contains (tblApp.*, @searchTag)
	     --contains (tblApp.*, isNull(@searchTag, 'ahagagatqtqyy' )
	     --FREETEXT(tblApp.*, @SearchTag)

	1 = 
	    (

		case 

		   when (@SearchTag is null) then 1
		   when (@SearchTag = '') then 1
		   when ( (@searchTag is not null) 
                            and (contains (tblApp.*, @searchTag )) ) then 1

		end 

	   )

Full SQL Code


ALTER procedure [dbo].[usp_GetApplicationList_FilteringByID_Blog]
(
   	  @ApplicationTypeID uniqueidentifier = null
	, @SearchTag  nvarchar(255) = null
)
as

  /*
     IF @SearchTag is null
     BEGIN
        SET @SearchTag = '""'
     END

  */

 select tblApp.*
 from   dbo.Application tblApp
 where  
	(

	   (	
	       (@SearchTag is null)			
		or (@SearchTag = '')	
		or (@SearchTag = '""')								
		or 
		   (

	            --contains (tblApp.*, @searchTag)
		    --contains (tblApp.*, isNull(@searchTag, 'ahagagayy')
		    --FREETEXT(tblApp.*, @SearchTag)

		     1 = 
		       (

			  case 
			   when (@SearchTag is null) then 1
			   when (@SearchTag = '') then 1
			   when ( (@searchTag is not null) 
                                and (contains (tblApp.*, @searchTag )) ) 
                                  then 1

			  end 

			 )

		  ) 

		and
		   (	
		       (@ApplicationTypeID is null)			
			or 
		  	  (
			     (tblApp.appTypeID = @ApplicationTypeID)
			   )
		   )				

          )

  )

go

Sample Code – Sanitize Arguments

Sanitize Code

The only thing that still works, is what has worked for the last decade or so.  And, that is to check the arguments before hand, and slightly modify them.


	IF (
		   (@SearchTag is null)
		or ( ltrim(rtrim(@SearchTag)) = '')
           )
	BEGIN
	   SET @SearchTag = '""'
	END

Full Code


ALTER procedure [dbo].[usp_GetApplicationList_FilteringByID_Blog]
(
   	  @ApplicationTypeID uniqueidentifier = null
	, @SearchTag  nvarchar(255) = null
)
as

	IF (
		   (@SearchTag is null)
		or ( ltrim(rtrim(@SearchTag)) = '')
        )
	BEGIN
	   SET @SearchTag = '""'
	END

	select tblApp.*
	from   dbo.Application tblApp
	where  
		(

	    	   (	
		        (@SearchTag is null)			
		     or (@SearchTag = '')	
		     or (@SearchTag = '""')								
		     or 
			(

			    contains (tblApp.*, @searchTag)
			    --FREETEXT(tblApp.*, @SearchTag)

			) 

                    )
		   and
			(	
			   (@ApplicationTypeID is null)			
			or 
			  (tblApp.appTypeID = @ApplicationTypeID)

			)				

                   )

go

Query Plan

As always, one should look a bit at the Execution Plan.  The Execution Plan is a good informant as to how the SQL Engine is addressing this Query.

QueryPlan

From the execution plan, we can see that SQL might very well be keeping FullText Operations a bit separate from regular SQL.  This separation might thus restrict what we might otherwise assume are regular SQL constructs.

Implications

The implications is a bit hidden with SQL Server Stored Procedures.  But, once one starts looking at SQL Functions and comparing them – Table Value Function vs In-Line User Defined Functions, one might sense subtle gaps in implementation and performance.

Conclusion

J.Cole in “Too Deep for the Intro” has that line “You call it rhymes I call it clearing out my mind“.  And, that is what this is – Cheap Therapy (for yours truly).

There are only a few options for avoiding the problem.  And, those are carefully checking your arguments in the front-end code or checking them beforehand in the SQL Script.

Addendum

Addendum – Microsoft Connect [2013-Nov-23 (Saturday)]

BTW, there is an existing “Microsoft Connect” Feedback Ticket that is tracking this issue.

FREETEXT() does not honor order of evaluation in CASE statements (no aggregates involved) by “Conan The Iowan”
https://connect.microsoft.com/SQLServer/feedback/details/780132/freetext-does-not-honor-order-of-evaluation-in-case-statements-no-aggregates-involved#tabs

If so led, please add your comments, workarounds, or simply add your count to those who have been able to reproduce it.

 

Addendum – Expression Short Circuiting [2013-Nov-23 (Saturday)]

As I often like to say, this is Science and so I think it is worthy to make sure we call things the same thing; and not pretend that is a new category. Some of what we are trying to do with SQL Case statements is what I just know found out is called “Expression Short Circuiting“.

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s