SOSL query field limitations

Question

Someone asked me why a SOQL query for a product value on the Case object returned all the records with the product name queried while a SOSL search on the same returned only 1 record.

Example using SOSL:

FIND {GC3060} IN ALL FIELDS RETURNING Case

Example using SOQL:

SELECT ID from CASE where product__c='GC3060'

Normally (without having invested much time in searching and reading documentation), they would have expected the same amount of records from both queries or maybe even more from the SOSL if there had been other fields referencing the product name elsewhere. However, this was not the case.

the SOSL returned 1 record, and the SOQL 10 (for Ex.)

I went through the documentation and other SFSE posts in order to find why:

• SOSL on Lookup Fields not working.
• Dynamic sosl query not returning all events with search term in their subject
• SOSL Limits along other articles from the SOQL and SOSL Reference
• SOSL Operation Limitations from the Tooling api guide
• SOSL searching picklists

However, I did not find a complete concrete guide on what is and isn't supported by SOSL (in one place at least). I am also thinking this could benefit the community. Feel free to provide an answer on what limitations there are to SOSL queries on objects and fields which would result in this behavior.

Product__c is a picklist (usually included in a fresh Dev org on the CASE Object) (FYI: the above is easily reproduce-able in a Dev Org using the developer console, you can copy paste the queries separately.)

Answer 1

It would appear the picklist fields are not searchable with SOSL.

You can confirm this by checking the DescribeFieldResult for the field in question.

Schema.DescribeFieldResult dfr = Schema.SObjectType.Case.fields.Product__c;
System.debug(dfr.isSearchPrefilterable());

What isn't 100% clear is why a field that isn't "prefilterable" in the SOSL where clause would also be excluded from any SOSL indexes.

There is also the associated Idea : Include pick list values in search, which confirms other users finding that picklists are excluded from SOSL results.

See also:

• Searchable Objects and Fields