[SERVER-74637] $or query with text index failed Created: 06/Mar/23  Updated: 27/Oct/23  Resolved: 28/Apr/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: ming 2hi Assignee: Yuan Fang
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:

1.create text index for "a"

2.create nomral index for "b"

3.execute

db.getCollection('xxx').find({  
    "$or" : 
        [
            { "$text" : { "$search" : "xx"}},
            {"b" :  "xx"}
        ]
    }
   ).collation({"locale":"en", "numericOrdering":true})

 

4.get error

 "planner returned error :: caused by :: Failed to produce a solution for TEXT under OR - other non-TEXT clauses under OR have to be indexed as well.",

Participants:

 Description   

An error occurred when using &or and adding collation({"locale":"en", "numericOrdering":true}) for query with text index



 Comments   
Comment by Arun Banala [ 28/Apr/23 ]

449287455@qq.com This issue here is that the index on field b is created with default collation, but the query is using a non-default collation. So the existing index on b is not eligible for this query. You will have to drop the index on b and re-create the index that matches the query collation. Something like below should address the problem.

> db.getCollection("xxx").dropIndex({b: 1});
> db.getCollection("xxx").createIndex({b: 1}, {collation: {"locale":"en", numericOrdering: true}})

Comment by Yuan Fang [ 12/Apr/23 ]

Hi 449287455@qq.com,

I apologize for the delay in my response. The error message you received indicates that MongoDB requires an index to be defined on all fields included in the $or expression. Can you confirm that you have successfully created the necessary index on both fields? If so, could you please provide the command used to create the index so that we can attempt to reproduce the issue on our end?

Regards,
Yuan

Comment by ming 2hi [ 08/Mar/23 ]

Hi yuan.fang@mongodb.com,

 

Thank you for your reply, the mongo version we use is "4.2.15".

 

Thanks,

Mingzhi

Comment by Yuan Fang [ 07/Mar/23 ]

Hi 449287455@qq.com,

Thank you for your report. To assist in reproducing the same error, could you please provide the version of MongoDB in which the error occurred?

Regards,
Yuan

Generated at Thu Feb 08 06:28:02 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.