Fulltextsqlquery not returning expected results with an "OR" clause

SharePoint - Search

NigelP

I have a FullTextSQLQuery which I am trying to bring back linkurls and aspx pages or bring back a .doc file(s)

Examples of queries We have used below :-

This brings back 1

SELECT Title, Rank, Size, Description, Write, Path, FileExtension, IsDocument, HitHighLightedSummary, HitHighlightedProperties, ContentClass, CollapsingStatus, Author, WorkId FROM portal..scope() WHERE CONTAINS('"Manor*"') AND (("ISDOCUMENT" = 0) OR (("FILEEXTENSION" = 'ASPX'))) AND (( "SCOPE" = 'NCCINTRANET')) ORDER BY "Rank" DESC

This brings back 1 result.
SELECT Title, Rank, Size, Description, Write, Path, FileExtension, IsDocument, HitHighLightedSummary, HitHighlightedProperties, ContentClass, CollapsingStatus, Author, WorkId FROM portal..scope() WHERE CONTAINS('"Manor*"') AND (("ISDOCUMENT" = 0) OR (("FILEEXTENSION" = 'ASPX')) AND (( "SCOPE" = 'NCCINTRANET'))) ORDER BY "Rank" DESC

This brings back 36 results
SELECT Title, Rank, Size, Description, Write, Path, FileExtension, IsDocument, HitHighLightedSummary, HitHighlightedProperties, ContentClass, CollapsingStatus, Author, WorkId FROM scope() WHERE ("scope" = 'NCCIntranet') AND FREETEXT (defaultproperties, 'manor') AND ("FILEEXTENSION" = 'ASPX') OR ("ISDOCUMENT"=1)

This brings back 512 RESULTS
SELECT Title, Rank, Size, Description, Write, Path, FileExtension, IsDocument, HitHighLightedSummary, HitHighlightedProperties, ContentClass, CollapsingStatus, Author, WorkId FROM scope() WHERE ("scope" = 'NCCIntranet') AND ("FILEEXTENSION" = 'ASPX') OR ("ISDOCUMENT"=1) AND FREETEXT (defaultproperties, 'Manor')

Has anyone got a clue how we can search and produce relevent results.we should get four results - 3 web pages (linkurls) and .one .aspx page

Thanks

Nigel
Top

Re: SharePoint - Search Fulltextsqlquery not returning expected results with an "OR" clause

Victor Magidson - MSFT

I'm not sure I quite understand your intent. You have 3 properties involved in the query: defaultproperties (either with freetext or contains), isdocument and filteextension. And you say you expect 4 results. Can you please table docs with the appropriate property values

Also, restructure your queries with explicitely specified parenteses - especially in cases you use mixed AND and OR clauses. It will make the query more readable. Once you share intent and new results, we can look for farther adjustment.




Top

Re: SharePoint - Search Fulltextsqlquery not returning expected results with an "OR" clause

NigelP

- <ResponsePacket xmlns="urn:Microsoft.Search.Response">

- <Response domain="QDomain">
- <Range>
<StartAt>1</StartAt>
<Count>4</Count>
<TotalAvailable>4</TotalAvailable>
- <Results>
- <Document xmlns="urn:Microsoft.Search.Response.Document">
- <Action>
<LinkUrl>http://intranet.test</LinkUrl>
</Action>
- <Properties xmlns="urn:Microsoft.Search.Response.Document.Document">
- <Property>
<Name>TITLE</Name>
<Type>String</Type>
<Value>Home</Value>
</Property>
- <Property>
<Name>RANK</Name>
<Type>Int64</Type>
<Value>1000</Value>
</Property>
- <Property>
<Name>SIZE</Name>
<Type>Int64</Type>
<Value>0</Value>
</Property>
- <Property>
<Name>DESCRIPTION</Name>
<Type>String</Type>
<Value>This is the prototype for the NCC Intranet</Value>
</Property>
- <Property>
<Name>WRITE</Name>
<Type>DateTime</Type>
<Value>2007-10-31T16:19:14+00:00</Value>
</Property>
- <Property>
<Name>PATH</Name>
<Type>String</Type>
<Value>http://intranet.test</Value>
</Property>
</Properties>
</Document>
- <Document xmlns="urn:Microsoft.Search.Response.Document">
- <Action>
<LinkUrl>http://intranet.test/NewsandMediaCentre</LinkUrl>
</Action>
- <Properties xmlns="urn:Microsoft.Search.Response.Document.Document">
- <Property>
<Name>TITLE</Name>
<Type>String</Type>
<Value>News and Media Centre</Value>
</Property>
- <Property>
<Name>RANK</Name>
<Type>Int64</Type>
<Value>1000</Value>
</Property>
- <Property>
<Name>SIZE</Name>
<Type>Int64</Type>
<Value>0</Value>
</Property>
- <Property>
<Name>WRITE</Name>
<Type>DateTime</Type>
<Value>2007-10-31T16:19:14+00:00</Value>
</Property>
- <Property>
<Name>PATH</Name>
<Type>String</Type>
<Value>http://intranet.test/NewsandMediaCentre</Value>
</Property>
</Properties>


Top

Re: SharePoint - Search Fulltextsqlquery not returning expected results with an "OR" clause

NigelP

</Document>
- <Document xmlns="urn:Microsoft.Search.Response.Document">
- <Action>
<LinkUrl>http://intranet.test/NewsandMediaCentre/LatestNews</LinkUrl>
</Action>
- <Properties xmlns="urn:Microsoft.Search.Response.Document.Document">
- <Property>
<Name>TITLE</Name>
<Type>String</Type>
<Value>Latest News</Value>
</Property>
- <Property>
<Name>RANK</Name>
<Type>Int64</Type>
<Value>1000</Value>
</Property>
- <Property>
<Name>SIZE</Name>
<Type>Int64</Type>
<Value>0</Value>
</Property>
- <Property>
<Name>WRITE</Name>
<Type>DateTime</Type>
<Value>2007-10-30T18:03:26+00:00</Value>
</Property>
- <Property>
<Name>PATH</Name>
<Type>String</Type>
<Value>http://intranet.test/NewsandMediaCentre/LatestNews</Value>
</Property>
</Properties>
</Document>
- <Document xmlns="urn:Microsoft.Search.Response.Document">
- <Action>
<LinkUrl fileExt="aspx">http://intranet.test/NewsandMediaCentre/LatestNews/Pages/Menorhouse.aspx</LinkUrl>
</Action>
- <Properties xmlns="urn:Microsoft.Search.Response.Document.Document">
- <Property>
<Name>TITLE</Name>
<Type>String</Type>
<Value>Future of Manor House to be considered</Value>
</Property>
- <Property>
<Name>RANK</Name>
<Type>Int64</Type>
<Value>1000</Value>
</Property>
- <Property>
<Name>SIZE</Name>
<Type>Int64</Type>
<Value>20810</Value>
</Property>
- <Property>
<Name>WRITE</Name>
<Type>DateTime</Type>
<Value>2007-10-10T17:09:28+01:00</Value>
</Property>
- <Property>
<Name>PATH</Name>
<Type>String</Type>
<Value>http://intranet.test/NewsandMediaCentre/LatestNews/Pages/Menorhouse.aspx</Value>
</Property>
</Properties>
</Document>
</Results>
</Range>
<Status>SUCCESS</Status>
</Response>
</ResponsePacket>


Top

Re: SharePoint - Search Fulltextsqlquery not returning expected results with an "OR" clause

NigelP

Hi Victor

This is what the search results should be.

I hope this helps

Nigel


Top

Re: SharePoint - Search Fulltextsqlquery not returning expected results with an "OR" clause

Victor Magidson - MSFT

Please restructure your queries with explicit parenteses and share the results. Make sure they explicitly reflect your intent of the query.




Top

Re: SharePoint - Search Fulltextsqlquery not returning expected results with an "OR" clause

ChelseaBlue

Hello Victor,

I am assisting on this issue as well, when you say restructure using explicit parentheses, I assume you mean further breakdown.

The closest resultset we were getting was from this query:

SELECT Title, Rank, Size, Description, Write, Path, FileExtension, IsDocument, HitHighLightedSummary, HitHighlightedProperties, ContentClass, CollapsingStatus, Author, WorkId FROM scope() WHERE ("scope" = 'NCCIntranet') AND FREETEXT (defaultproperties, 'manor') AND ("FILEEXTENSION" = 'ASPX') OR ("ISDOCUMENT"=1)

What we are trying to obtain is:

* Searching across an intranet scope (NCCIntranet)

* From within that scope, looking for any content with the value 'manor'

* Of that content found, returning all with an ASPX extension or DOC files

The only other 'breakdown' I can think of for the resultset we require is:

SELECT Title, Rank, Size, Description, Write, Path, FileExtension, IsDocument, HitHighLightedSummary, HitHighlightedProperties, ContentClass, CollapsingStatus, Author, WorkId FROM scope() WHERE ((\"scope\" = 'NCCIntranet') AND FREETEXT (defaultproperties, 'manor')) AND (("FILEEXTENSION" = 'ASPX') OR ("ISDOCUMENT"=1))

ChelseaBlue




Top

Re: SharePoint - Search Fulltextsqlquery not returning expected results with an "OR" clause

Victor Magidson - MSFT

Please try with extra parenteses as following (added ones in Red):

SELECT Title, Rank, Size, Description, Write, Path, FileExtension, IsDocument, HitHighLightedSummary, HitHighlightedProperties, ContentClass, CollapsingStatus, Author, WorkId FROM scope() WHERE ("scope" = 'NCCIntranet') AND FREETEXT (defaultproperties, 'manor') AND (("FILEEXTENSION" = 'ASPX') OR ("ISDOCUMENT"=1))

What unexpected results you get with this query Please give some examples.


Top

Re: SharePoint - Search Fulltextsqlquery not returning expected results with an "OR" clause

ChelseaBlue

Thank you Victor.

In the processing of testing this at the moment.

Will post up results once finished testing.


Top

Re: SharePoint - Search Fulltextsqlquery not returning expected results with an "OR" clause

ChelseaBlue

Victor, the query is not working with the answer you provided


Top

Re: SharePoint - Search Fulltextsqlquery not returning expected results with an "OR" clause

smc750

The isDocument=1 will not limit your results to just aspx or doc files as per your first post. isDocument=1 will also bring back other documents like bmp, tiff, pdf, xls on and on. There are known issues with MOSS search and the use of an "OR" clause when using full text sql. However, in this case it might not be a problem since these columns should be in all document libraries. The following blog entry discusses the problems with the existing MOSS search and WSS caml searching that Microsoft has not fixed and has not given any time line when these problems will be fixed. So, you should always be wary of using the "OR" clause in any full text sql search.

http://www.sharepointblogs.com/smc750/archive/2007/06/20/custom-cross-list-search-development-pitfalls-part-two.aspx





Top