DO free text search with SQL server returns unexpected results : a search for 1-1BL6-3006 returns way too much entities, that does not have this string in their full text indexed column. I suppose this is because '-' is interpreted as a special character.

Besides Sql profiler shows that the query executed uses FREETEXTTABLE and not CONTAINSTABLE. In my case :

FROM FREETEXTTABLE([dbo].[SPB.Document], *, '1-1BL6-3006') as ft
INNER JOIN [SPB.Document] d ON (ft.[KEY] = d.[Id]);

returns 154 results.

FROM CONTAINSTABLE([dbo].[SPB.Document], *, '1-1BL6-3006') as ft
INNER JOIN [SPB.Document] d ON (ft.[KEY] = d.[Id]);

returns 3 results. (correct)

Can you :

  • Use FREETEXTABLE which was used in DO 3.X (or provide an option / alternative method to do this)
  • Show me how I can replace FREETEXTTABLE by CONTAINSTABLE in generated sql. (the example does not cover this case : is this possible?)

This would also allow me to fix this question : Full text search with *

asked Oct 08 '10 at 09:17

olorin's gravatar image


edited Oct 08 '10 at 09:18

One Answer:

Currently DO supports only FREETEXTTABLE-based queries, i.e. it's impossible to make it executing a similar query with CONTAINSTABLE instead (the link you gave explains this).

On the other hand, the problem you face occurs because stemmer splits the string you pass and performs search of its parts. On the other hand, it's pretty easy to disable stemming for the whole passed string - manual says, "If freetext_string is enclosed in double quotation marks, a phrase match is instead performed; stemming and thesaurus are not performed."

Although I'm not sure if this will work on PostgreSQL as well (likely, won't).

Actually, that's why we decided to initially add support only for Query.FreeText: there is no need to develop complex full-text query language abstraction layer (although we anyway developed a tiny one - Query.FreeText, I mean).

answered Oct 09 '10 at 08:01

Alex%20Yakunin's gravatar image

Alex Yakunin

edited Oct 09 '10 at 08:04

You're right : enclosing the searched terms for with double quotes force a search with exact match.

(Oct 11 '10 at 03:55) olorin olorin's gravatar image

I understand : the support of the more complex sql server COTNAINSTABLE would require an abstraction layer to be able to use the query on another database software (postgres, ...). And possibly a custom implementation for db that does not support full text search and in memory storage.

Is this planned? And with what priority? We would be interested by simple full text language support (search with "" or *) for sql server and maybe for in memory provider.

(Oct 11 '10 at 04:00) olorin olorin's gravatar image

Is this planned? And with what priority?

Until now no one requested this, so we don't have near-time plans related to this feature, neither a precise vision of possible API.

I'll create an issue for this - let's try to define the features you need more precisely and see when this can be done.

(Oct 11 '10 at 05:55) Alex Yakunin Alex%20Yakunin's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA