[bug] 'duplicate index key' errors for unique lowercase fields when using RegExp filter

[jbemmel]

Fields with 'unique: true' create an index in MongoDB.
However, if a field also has 'lowercase: true', the index results in a 'duplicate key' error when updating an object using a different case

Schema: Person
name: { type: String, unique: true, lowercase: true }

create Person( name="Mike" ) => ok, name converted to 'mike'
findOneAndUpdate( { name: "Mike" }, { upsert: true } ) => Fails, duplicate key "mike"

The issue can be solved by enabling collation for the index, strength=1 to ignore case

[jbemmel]

Unfortunately it looks like even enabling collation on the index doesn't fix it.
Can be considered as a MongoDB semantics issue - perhaps 'unique' and 'lowercase' are incompatible.

One could argue there should be an implicit conversion to lowercase for the input to findOneAndUpdate

[AbdelrahmanHafez]

When you set

const personSchema = new Schema({
  name: { type: String, unique: true, lowercase: true}
});

This casts the name to lowercase before sending any operations to the database. That includes your findOneAndUpdate.

Try setting mongoose in debug mode to see what exactly gets sent to the database.

const mongoose = require('mongoose');
const { Schema } = mongoose;

const personSchema = new Schema({
  name: { type: String, unique: true, lowercase: true }
});
const Person = mongoose.model('Person', personSchema);
mongoose.connect('mongodb://127.0.0.1:27017/test');

async function run () {
  await Person.remove();

  mongoose.set('debug', true);

  const person = await Person.create({ name: 'Hafez' });

  await Person.findOneAndUpdate({ _id: person._id }, { name: 'HAFEZ' }, { upsert: true });

  mongoose.set('debug', false);
}
run().catch(console.error);

Your logs should look something like this, notice how the name is lowercase in both operations:

Mongoose: people.insertOne({ _id: ObjectId("5dd197f620cf21191c25d526"), name: 'hafez', __v: 0 }, { session: null })
Mongoose: people._findAndModify({ _id: ObjectId("5dd197f620cf21191c25d526") }, [], { '$setOnInsert': { __v: 0 }, '$set': { name: 'hafez' } }, { upsert: true, new: false, remove: false, projection: {} })

Which doesn't throw any duplicate keys errors. Hope this helps explain how lowercase and indexes play together. If that doesn't help with your problem please provide a reproduction script.

[jbemmel]

Thanks for looking into this. The issue occurs when I do this:

const person2 = await Person.findOneAndUpdate({ name: new RegExp('^Hafez$') }, 
                                     { name: 'HAFEZ' }, { upsert: true });

I guess this would qualify as a user error, although you could argue that 'lowercase' should modify the RegExp and set it to be case insensitive, just like it converts a String to lowerCase.

Like I said, a semantics/consistency issue - a String filter gets modified, a RegExp filter does not

[vkarpov15]

Thanks for looking into this @AbdelrahmanHafez . I don't think there is much Mongoose can do in this case. Setting the regexp to case insensitive might be the wrong behavior in certain cases - in your case, it might modify an existing document where 'hafez' isn't all lowercase.

We could modify the regexp, but I think that's a bit too error prone. We'd need to be smart about avoiding escape sequences and I'm not sure it is worth it for this case.

[jbemmel]

I'm ok with that, perhaps you could update https://mongoosejs.com/docs/tutorials/query_casting.html and add a section about 'String' query parameters getting converted to lowercase for fields with 'lowercase: true', but not RegExp