[SERVER-13779] Allow $not to be applied to $regex (currently only /regex/ syntax is allowed) Created: 28/Apr/14  Updated: 19/Mar/19  Resolved: 31/Jan/19

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.4.10, 2.6.0
Fix Version/s: 4.0.7, 4.1.8

Type: Improvement Priority: Major - P3
Reporter: Norberto Fernando Rocha Leite (Inactive) Assignee: James Wahlin
Resolution: Done Votes: 8
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Depends
is depended on by SERVER-39019 $elemMatch $ne serialization is incor... Closed
Documented
is documented by DOCS-12420 Docs for SERVER-13779: Allow $not to ... Closed
Related
related to SERVER-4805 query { $not : { $regex : /x/ } } doe... Closed
related to COMPASS-3527 Add support for $not with $regex Closed
is related to SERVER-14595 Allow {$regex: /pattern/} syntax insi... Backlog
Backwards Compatibility: Fully Compatible
Backport Requested:
v4.0
Sprint: Query 2019-02-11
Participants:

 Description   

Executing the following queries I was expecting the same result but apparently they are dealt differently:

2 scenarios

  • matching

    nair(mongod-2.7.0-pre-) test> db.system.namespaces.find({ name:/\$/} )
    {
      "name": "test.data.$_id_"
    }
    {
      "name": "test.difference.$_id_"
    }
    {
      "name": "test.posts.$_id_"
    }
    {
      "name": "test.kunal.$_id_"
    }
    {
      "name": "test.maha_example.$_id_"
    }

    has the same result has using $regex operator

    nair(mongod-2.7.0-pre-) test> db.system.namespaces.find({ name:{ $regex: "\\$"}} )
    {
      "name": "test.data.$_id_"
    }
    {
      "name": "test.difference.$_id_"
    }
    {
      "name": "test.posts.$_id_"
    }
    {
      "name": "test.kunal.$_id_"
    }
    {
      "name": "test.maha_example.$_id_"
    }

  • not matching query

    nair(mongod-2.7.0-pre-) test> db.system.namespaces.find({ name: {$not: /\$/}} )
    {
      "name": "test.system.indexes"
    }
    {
      "name": "test.data"
    }
    {
      "name": "test.difference"
    }
    {
      "name": "test.posts"
    }
    {
      "name": "test.kunal"
    }
    {
      "name": "test.maha_example"
    }
    {
      "name": "test.system.profile",
      "options": {
        "capped": true,
        "size": 1048576
      }
    }

    But using $regex operator fails

    nair(mongod-2.7.0-pre-) test> db.system.namespaces.find({ name: {$not:  { $regex: "\\$"}}} )
    error: {
      "$err": "Can't canonicalize query: BadValue $not cannot have a regex",
      "code": 17287
    }

Is this the expected behaviour ? and if so why ?

Looking deeper in the code I can also see the following comment:
mongo/db/matcher/expression_parser_tree.cpp:103

...
         // TODO: this seems arbitrary?
         // tested in jstests/not2.js
         for ( unsigned i = 0; i < theAnd->numChildren(); i++ )
             if ( theAnd->getChild(i)->matchType() == MatchExpression::REGEX )
                 return StatusWithMatchExpression( ErrorCodes::BadValue, "$not cannot have a regex" );
...

and looking int the jstests it makes even less sense:

jstests/core/not2.js

  49 check( {i:{$not:/a/}}, "b" );
  50 check( {i:{$not:/(a|b)/}}, "", 0 );
  51 check( {i:{$not:/a/,$regex:"a"}}, "", 0 );
  52 check( {i:{$not:/aa/}}, "a", 2 );
  53 fail( {i:{$not:{$regex:"a"}}} );
  54 fail( {i:{$not:{$options:"a"}}} );



 Comments   
Comment by Githook User [ 19/Feb/19 ]

Author:

{'name': 'James Wahlin', 'email': 'james@mongodb.com', 'username': 'jameswahlin'}

Message: SERVER-39019 Fix incorrect $elemMatch $ne serialization

(cherry picked from commit 4677879eb75934ee3cd76c9e9807f6892ac17400)

SERVER-13779 Allow $not to be applied to $regex

(cherry picked from commit a2c0f15d6dc9fcda389b18b54287c4fcb5be44cd)
Branch: v4.0
https://github.com/mongodb/mongo/commit/a973d4f651a4e0f5a19c2edb2de2e6e53ee5e212

Comment by Githook User [ 31/Jan/19 ]

Author:

{'name': 'James Wahlin', 'email': 'james@mongodb.com', 'username': 'jameswahlin'}

Message: SERVER-13779 Allow $not to be applied to $regex
Branch: master
https://github.com/mongodb/mongo/commit/a2c0f15d6dc9fcda389b18b54287c4fcb5be44cd

Comment by Jason R. Coombs [ 20/Mar/17 ]

This issue seems pretty serious to me as the $regex is the only syntax allowed in the Extended JSON Syntax (strict) as required by tools like Compass, mongodump, and REST interfaces (including third-party tools like Mongs). Lack of support for this feature is comparable to a SQL database not having support for "NOT LIKE" in queries.

Comment by J. Cardina [ 01/Aug/14 ]

Came here to ask the same question, why on earth is this not supported? It's required because you can't use the /something/ format with other operators you have to use the $regex format so there is very limited functionality as a result of this when you want to do anything significantly complex.

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