unique_together on SQLite uses 'iexact' matching, I expected this to be 'exact'

[scanner@…]

I have a model that has:

    class Meta:
        unique_together = (('gecko_engine_instance', 'name'), ('gecko_engine_instance', 'dhcp_client_identifier'), )

For the 'dhcp_client_identifier' "foo" is different from "Foo."

However the query that was enforcing uniqueness ends up looking line:

SELECT "dcs_client"."id" ... blah blah ..  WHERE ("dcs_client__gecko_engine_instance"."id" ILIKE '1' AND  dcs_client"."dhcp_client_identifier" ILIKE 'blarg')

which will fail if I have another client object with a dhcp_client_identifier of 'Blarg.' I figure there must be a reason for this although it causes me problems. Perhaps it is some strange compatibility with sqlite3.

I need to know if this is a bug, a workaround, or working as intended so I can modify my code appropriately?

[scanner@…]

Oh, another note - this can be related to ticket #2417 - when I define a binary type field (in my own project subclassing models.CharField, and defining the appropriate db type in the db creation.py module, 'ilike' is not a valid operator for on such a field for obvious reasons whereas 'like' is.

[mir@…]

removed myself from Cc since ticket starts to get spammed :-(

[Robert Myers <myer0052@…>]

SQLite doesn't support case-sensitive LIKE statements. From the Django documentation itself. This ticket should be closed.

[Robert Myers <myer0052@…>]

Should have provided a handy link, here goes: ​http://www.djangoproject.com/documentation/db_api/#iexact maybe the text below contains should be copied to exact as well.

• SQLite doesn't support case-sensitive LIKE statements; exact acts like iexact for SQLite.

[Gary Wilson <gary.wilson@…>]

What Robert said.

[Bastian Kleineidam <calvin@…>]

I encountered the same bug today as the original submitter: I specified uniqueness, but django complained about duplicate field values.

I don't use sqlite, but postgresql, and I fixed the bug by replacing all 'iexact' with 'exact' in db/models/manipulators.py:manipulator_validator_unique_together().

[Simon G. <dev@…>]

Hi Bastian, I don't think these issues are related other than the case sensitivity. The original bug here was to do with SQLite's lack of a case sensitive match, whilst your problem is in the manipulators (which directly specifies iexact). This is a design issue I think, IMO case insensitive matches are the most common idea (i.e. we don't want a "Simon" and a "SImon" in the database), but this might be nice to have as an option.

Long story short - Can you create a new ticket for this issue?

Thanks :)
Simon