[SERVER-13803] Query planner should be able to plan $text queries that require collection scan Created: 30/Apr/14  Updated: 28/Dec/23

Status: Backlog
Project: Core Server
Component/s: Querying, Text Search
Affects Version/s: 2.6.0
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Will Shaver Assignee: Backlog - Query Integration
Resolution: Unresolved Votes: 3
Labels: qi-text-search
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
is depended on by SERVER-61259 $or queries with text indexes fail on... Closed
Duplicate
is duplicated by SERVER-24913 Remove index restriction on $or claus... Closed
Related
related to SERVER-17648 Implement matcher for text predicates Backlog
Assigned Teams:
Query Integration
Participants:

 Description   

On a schema like the following:

{ _id : ObjectID, description: String}

With an index like:

{ fields: { 'description': 'text' } }

Doing this with $and query succeeds as expected:

db.products.find({$and:[{$text: {$search : 'box'} }, {search:'Box'}] );

But this query fails:

db.products.find({$or:[{$text: {$search : 'Bob'} }, {search:'Bob'}] );

With this error

$err" : "Runner error: BadValue error processing query: ns=unit_test.products limit=0 skip=0
Tree: $or
description== \"Bob\"
TEXT : query=Bob, language=, tag=NULL
Sort: {}
Proj: {}
planner returned error: Failed to produce a solution for TEXT under OR - other non-TEXT clauses under OR have to be indexed as well.",
"code" : 17144

(I realize this seems asinine, but my actual code is quite a bit more complicated with a full-text index on several different text fields.)



 Comments   
Comment by Jerome Mutgeert [ 16/Jun/21 ]

Hi all in trouble, I've got a decent workaround solutuion:

 

Given that I have an index on the column:

db.Products.find({$or:[

  {name:{$regex:'hi', $options:'i'}, created: {$ne: 'nonExistingString'}},

  {$text: {$search: 'Hi'}}

]})

 

Comment by Billy Tetrud [ 25/Jan/17 ]

"can't currently be used for queries that require a collection scan"

A. mongo doesn't normally prevent collection scans, as that is sometimes a valid thing to do, and B. having a $text query alongside an unindexed clause does not always result in a collection scan (in my case it doesn't).

Here's an example:

MyCollection.find({parent: ObjectId("32fj8sf328f"), $or:[{$text:{$search:"hello"}}, {$regex:"hello", $options: '-i'}}]

Where parent is indexed, that search might be very cheap regardless of the lack of an index.

Comment by J Rassi [ 01/May/14 ]

$text predicates can't currently be used for queries that require a collection scan (yep, documented under $text Behavior).

Updating title, changing ticket type from "Bug" to "Improvement".

Comment by Will Shaver [ 30/Apr/14 ]

Just in case it is different, my actual use-case is to perform a regex on some of the fields that are also in the full text search.

Currently full text restricts some results that I would like to include, and a regex $or on the same field would allow this inclusion.

Comment by Will Shaver [ 30/Apr/14 ]

From the docs, "To use a $text query in an $or expression, all clauses in the $or array must be indexed."

Generated at Thu Feb 08 03:32:57 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.