[SERVER-37888] Collation causes issue with a certain find condition Created: 02/Nov/18 Updated: 27/Oct/23 Resolved: 12/Nov/18 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Text Search |
| Affects Version/s: | 4.0.1 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Prateek Narendra | Assignee: | Danny Hatcher (Inactive) |
| Resolution: | Works as Designed | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Operating System: | ALL |
| Steps To Reproduce: |
|
| Participants: |
| Description |
|
I have a query -
There is an index on '_id' and a Full text search index ("$**":"text") This query fails saying -
Is there a workaround for it ? How do I specify collation of a collection along with full text search index? |
| Comments |
| Comment by Danny Hatcher (Inactive) [ 12/Nov/18 ] | |||||||||||
|
After an offline conversation with asya, confirmed that this is working as designed. When specifying a collation on a cursor, it checks the indexes for each of the strings in the query. The collation for the query must match the collation specified for the index. Because the collection did not have a collation specified, the _id index was using the default "simple" collation. Thus, the collation for the query did not match the collation for the index and the index was not used. The error correctly states that there are fields in the $or that are not using an index; the situation described in this ticket is working as designed. pnarendr, text indexes and collation serve two different needs. If you would like to specify a specific language for a text index, please follow our documentation here. As mentioned in our text indexes documentation, non-simple collation cannot be used with text indexes. | |||||||||||
| Comment by Asya Kamsky [ 09/Nov/18 ] | |||||||||||
|
daniel.hatcher collation applies to index matching and sorting it does not apply to text. | |||||||||||
| Comment by Danny Hatcher (Inactive) [ 09/Nov/18 ] | |||||||||||
|
The only two indexes for the collection are _id : 1 and name : "text". Removing the collation, both _id : 1 and _id : "asdf" runs have exactly the same explain plan except for the index bounds; both of them use the text index to satisfy the $text part of the OR and the _id index to satisfy the _id part. The error states "other non-TEXT clauses under OR have to be indexed as well" which _id is. Per asya's suggestion, I created a collation at the collection-level. Running the same exact query (_id as a string and collation at the end), it failed the original collection and succeeded on new collection. I would expect the outcomes to be identical. In either case, I specified a text search and a collation with all fields being indexed. Even if _id is a string, it is not part of the text index and isn't treated as such by the query planner. | |||||||||||
| Comment by Asya Kamsky [ 09/Nov/18 ] | |||||||||||
|
In other words, collation needs to match index that would be used for the query. | |||||||||||
| Comment by Asya Kamsky [ 09/Nov/18 ] | |||||||||||
|
If the collection collation was set when it was created wouldn't this work correctly (without specifying collation for the query)? Collation applies to regular indexes and query, not text indexes so it's not clear to me that there is a bug - it seems to be working as documented.
| |||||||||||
| Comment by Danny Hatcher (Inactive) [ 02/Nov/18 ] | |||||||||||
|
To add further detail: This succeeds when the _id is a number and collation is used:
And this succeeds when the _id is a string and collation is not used:
But an error occurs when _id is a string and collation is used:
|