“OR” logic in fulltextsqlquery

In some instances, the OR logic in fulltextsqlquery works as an AND which could be very confusing when trying to build queries. For example, let’s say you have two managed properties that are both mapped to two different crawled properties. Now if you execute a query and search for a term that is available in one item and that this item meets one of the filtering criteria set up in your OR logic but not the other one then you will not get no results back (!). Suddenly your OR looks and behaves more like an AND. So basically, the item needs to have the two crawled properties in order to be filtered by both managed properties in order to get captured as a result. Here is a quick example:

Query #1:
WHERE ContentType="Doc" AND (ArticleTitle="Title1" OR ArticleAuthor="Billy")

Results #1: Item #1 and Item #2 get returned.

Now let’s consider the following data:

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

Query #2:
WHERE ContentType="Doc" AND (ArticleTitle="Title1" OR ArticlePrice="30")

Results #2: No results.

This will return no results though Item #1 should be returned.

One work around is to actually restate all conditions for each criteria in your query to obtain the correct results as show in this query:

Query #3:
WHERE (Content type="Doc" AND ArticleTitle =" Title1") OR (Content type="Doc" AND ArticlePrice ="30")

Results #3: Item #1 get returned.