Info

When both $options and $regex are BSON strings the order they appear in the query does not seem to matter. However $options appears to be ignored when it appears first and $regex is a BSON regular expression. import pymongo import re from bson import SON client = pymongo.MongoClient() coll = client.test.test coll.drop() coll.insert_one({'array': [re.compile(b'62', 0), 'no options']}) coll.insert_one({'array': [re.compile(b'62', re.IGNORECASE), 'IGNORECASE']}) for q in [ {'array': SON([('$options', 'i'), ('$regex', '62')])}, {'array': SON([('$regex', '62'), ('$options', 'i')])}, {'array': SON([('$regex', re.compile(b'62', re.IGNORECASE))])}, {'array': SON([('$regex', re.compile(b'62')), ('$options', 'i')])}, {'array': SON([('$options', 'i'), ('$regex', re.compile(b'62'))])}]: res = list(coll.find(q, projection={'_id': False})) print('>>> list(coll.find(%r)):\n%r' % (q, res)) Expected output: >>> list(coll.find({'array': SON([('$options', 'i'), ('$regex', '62')])})): [{'array': [Regex('62', 2), 'IGNORECASE']}] >>> list(coll.find({'array': SON([('$regex', '62'), ('$options', 'i')])})): [{'array': [Regex('62', 2), 'IGNORECASE']}] >>> list(coll.find({'array': SON([('$regex', re.compile(b'62', re.IGNORECASE))])})): [{'array': [Regex('62', 2), 'IGNORECASE']}] >>> list(coll.find({'array': SON([('$regex', re.compile(b'62')), ('$options', 'i')])})): [{'array': [Regex('62', 2), 'IGNORECASE']}] >>> list(coll.find({'array': SON([('$options', 'i'), ('$regex', re.compile(b'62'))])})): [{'array': [Regex('62', 0), 'IGNORECASE']}] Actual output (notice the difference in the final query): >>> list(coll.find({'array': SON([('$options', 'i'), ('$regex', '62')])})): [{'array': [Regex('62', 2), 'IGNORECASE']}] >>> list(coll.find({'array': SON([('$regex', '62'), ('$options', 'i')])})): [{'array': [Regex('62', 2), 'IGNORECASE']}] >>> list(coll.find({'array': SON([('$regex', re.compile(b'62', re.IGNORECASE))])})): [{'array': [Regex('62', 2), 'IGNORECASE']}] >>> list(coll.find({'array': SON([('$regex', re.compile(b'62')), ('$options', 'i')])})): [{'array': [Regex('62', 2), 'IGNORECASE']}] >>> list(coll.find({'array': SON([('$options', 'i'), ('$regex', re.compile(b'62'))])})): [{'array': [Regex('62', 0), 'no options']}]

Top User Comments

xgen-internal-githook commented on Tue, 12 Feb 2019 23:26:58 +0000: Author: {'name': 'Evan Nixon', 'email': 'evan.nixon@10gen.com', 'username': 'navenoxin'} Message: SERVER-38621 Do not ignore regex options when specified first Branch: master https://github.com/mongodb/mongo/commit/613454eb99abe25682f9a50d93ee04e7e90ba314 david.storch commented on Mon, 14 Jan 2019 20:55:04 +0000: The flawed parsing logic is here: https://github.com/mongodb/mongo/blob/cf6e22331a81dac4e3c3800c9b94c0df1b439737/src/mongo/db/matcher/expression_parser.cpp#L536-L569 The parsing logic handles $regex and $options independently, so the options associated with whichever keyword comes second overwrites the options associated with the first. Instead, of just taking the second set of options, I propose that we should throw an error if options are specified in both places and then use whichever options set is non-empty. (To avoid a backwards compatibility break we can neither prevent $options from being specified before $regex, or require the BSON type of $regex to be a string.) I'm moving this back to "Needs Scheduling" state so that the query team can re-triage it. asya commented on Mon, 17 Dec 2018 20:39:20 +0000: Could this be the same underlying reason as mentioned here?

Steps to Reproduce