Mango query with $eq operator returns no results, but $regex does

[Matt Cosentino]

This is very strange. I have a very simple query:

{ selector: { "type": { "$eq": "client" } } }

This returns no results, but there are documents with a type value of client, and the index exists.

I try other values in the query and they all work.

I try this query instead:

{ selector: { "type": { "$regex": "client" } } }

This gives me the results I expect. Why?

Here's another strange thing. I have another version of my database on another server. I run the original query there and it works. Both servers are running version 2.0 of CouchDB. Does anyone have any idea what could be wrong?


- Matt

[Garren Smith]

Hi Matt,

What are your indexes? Also could you try these queries on CouchDB 2.1. The
index selector was improved significantly in 2.1 to avoid these types of
issues.

Cheers
Garren

On Wed, Aug 23, 2017 at 9:49 PM, Matt Cosentino <mcose...@cacorp.com>
wrote:

[Matt Cosentino]

I have an index created for "type".

{
"type": "json",
"def": {
"fields": [
{
"type": "asc"
}
]
}
}

I'll try upgrading my test server to 2.1 and see if that helps, thanks.

- Matt

[Matt Cosentino]

Ugraded to 2.1, still get no results, and now get no results for the $regex query as well. If I do a query of $gt null, the results I'm looking for are there.

[Garren Smith]

Can you give me a full list of all your indexes and the query you are
running.

[Matt Cosentino]

special: _id
json: type, client_id
json: last_payment
json: type
json: date
json: date, time
json: type, client_id, date
json: datetime
json: client_id
json: type, customer_name
json: customer_name

{ selector: { "type": { "$eq": "client" } } }

[Garren Smith]

I think the best next step is to run the query against _explain instead of
_find. It will tell you which index it has selected.

The other option is to add another selector with client_id > null. I'm
guessing it is choosing the type, customer_name index and you want it to
use the type, client_id index

[Matt Cosentino]

Tried that, it is using the type index. Isn't that what I want? That's the only field in my selector.

[Garren Smith]

Do you have any docs with the field type = client?

[Matt Cosentino]

Yes.

[Garren Smith]

Could you supply some example docs so I can try and reproduce it.

[Matt Cosentino]

I didn't want to give you any confidential information, so I created a new client document. Well, now the query works and returns all the client documents. What happened?

[Garren Smith]

I'm not sure. Glad it's working though.

[Matt Cosentino]

Yeah, but not knowing what was wrong doesn't leave me with much confidence in the database going forward.

[Joan Touzet]

Have you checked your couch.log / couchdb.log file for any errors?
It's possible the index needed to be rebuilt, and adding the new
document forced that operation.

[Matt Cosentino]

I don't see any errors. I had tried deleting and recreating the index, thinking that might be the case, but that didn't fix it.

[Matt Cosentino]

Moved my changes over to the live server and this problem is happening again. This is maddening.

[Matt Cosentino]

I created a new client document like before and it did not fix the issue.

[Garren Smith]

Can you run an _explain query and show us the results. That should tell you
which index it is trying to use. The other option is to use the `use_index`
option to specify the exact index you want to use. If possible could you
create a scenario that we can test against and run ourselves? It doesn't
help right now, but we have done a ton of work on Mango around index
selection and querying. So the next release will have a much more
predictable index selector for mango.

On Thu, Sep 21, 2017 at 7:27 PM, Matt Cosentino <mcose...@cacorp.com>