Inconsistent Query result when I add an additional sort

Hi,

I’m running into a strange result based on the query plan that gets chosen for my query.

My query is a combination of several $or and $and conditions, but when I add a $sort the query returns zero documents, whereas it should return 24.

Adding an $exists: true for the field I’m sorting by makes it return the correct number of documents, however this isn’t a solution, as I expect that occasionally this value might not exist.

Here is the query

db.getCollection('records').find({
    "project": "test",
    "dataType": "table1",
    "$or": [
      {
        "fields.polizaAndReferencia": {
          "$regex": "Soluc",
          "$options": "i"
        }
      },
      {
        "fields.contratanteNombre": {
          "$regex": "Soluc",
          "$options": "i"
        }
      },
      {
        "fields.aseguradoActivo": {
          "$regex": "Soluc",
          "$options": "i"
        }
      },
      {
        "fields.nameFromCategoriasYPlanes": {
          "$regex": "Soluc",
          "$options": "i"
        }
      }
    ],
 
    "$and": [
      {
          "$or": [
          { "fields.polizaAndReferencia": { $exists: true  }, },
                    { "fields.polizaAndReferencia": { $exists: false  } }
          ]
      },
      {
        "$or": [
          {
            "$and": [
              {
                "fields.empresaFromContratante": {
                  "$in": [
                    64
                  ]
                }
              }
            ]
          }
        ]
      }
    ]
    })
    .limit(51)
    .sort({
      "id": -1,
    })

The above returns 24 records

db.getCollection('records').find({
    "project": "test",
    "dataType": "table1",
    "$or": [
      {
        "fields.polizaAndReferencia": {
          "$regex": "Soluc",
          "$options": "i"
        }
      },
      {
        "fields.contratanteNombre": {
          "$regex": "Soluc",
          "$options": "i"
        }
      },
      {
        "fields.aseguradoActivo": {
          "$regex": "Soluc",
          "$options": "i"
        }
      },
      {
        "fields.nameFromCategoriasYPlanes": {
          "$regex": "Soluc",
          "$options": "i"
        }
      }
    ],
 
    "$and": [
      {
          "$or": [
          { "fields.polizaAndReferencia": { $exists: true  }, },
                    { "fields.polizaAndReferencia": { $exists: false  } }
          ]
      },
      {
        "$or": [
          {
            "$and": [
              {
                "fields.empresaFromContratante": {
                  "$in": [
                    64
                  ]
                }
              }
            ]
          }
        ]
      }
    ]
    })
    .limit(51)
    .sort({
      "fields.polizaAndReferencia": -1,
      "id": -1,
    })

This query returns 0 records, but the only difference is the sort
Adding this “fixes” the query:

 "fields.polizaAndReferencia": { $exists: true  },

We have a (relatively new) wildcard compound index, which I reckon is the culprit.

project_1_dataType_1_fields.$**_1_id_-1

If anyone has any suggestions I would really appreciate it.

Explain results using allPlansExecution

I had to use Pastbin because the outputs were so large

Query 1 - without $exists

Query 2 - with $exists

Sample document with most of the fields redacted and some values changed to reflect the data:

{
    "_id" : ObjectId("754a6f5c55913826df623d08"),
    "project" : "test",
    "dataType" : "table1",
    "id" : 1234,
    "uuid" : "rec38383838383838",
    "createdAt" : ISODate("2023-11-07T17:09:48.000Z"),
    "externalHash" : "0ee737hddhdh",
    "fields" : {
        "poliza" : "0971175027-22",
       
        "nameFromCategoriasYPlanes" : "Auto",
       
        "created" : ISODate("2023-11-07T17:09:48.000Z"),
        "contratanteNombre" : "Soluciones TEXT TEXT TEXT",
       
        "aseguradoActivo" : "TEXT TEXT TEXT",
      
        "agente" : [ 
            51
        ],
        "empresaFromContratante" : [ 
            64
        ],
        "polizaAndReferencia" : "0971175027-22",
        "documentos" : [ 
            1922, 
            1923
        ]
    },
    "updatedAt" : ISODate("2024-01-08T16:04:43.596Z")
}

Index definitions for

project_1_dataType_1_fields.$**_1_id_-1
{
    project: 1,
    dataType: 1,
    'fields.$**': 1,
    id: -1,
 }