Opened 9 years ago

Closed 8 years ago

#2422 closed defect (invalid)

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

Reported by: scanner@… Owned by: adrian
Component: contrib.admin Version:
Severity: normal Keywords: SQlite
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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?

Change History (8)

comment:1 Changed 9 years ago by mir@…

  • Cc mir@… added

comment:2 Changed 9 years ago by 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.

comment:3 Changed 9 years ago by mir@…

  • Cc mir@… removed

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

comment:4 Changed 8 years ago by Robert Myers <myer0052@…>

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

comment:5 Changed 8 years ago by 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.

comment:6 Changed 8 years ago by Gary Wilson <gary.wilson@…>

  • Resolution set to invalid
  • Status changed from new to closed

What Robert said.

comment:7 Changed 8 years ago by Bastian Kleineidam <calvin@…>

  • Resolution invalid deleted
  • Status changed from closed to reopened

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().

comment:8 Changed 8 years ago by Simon G. <dev@…>

  • Keywords SQlite added
  • Resolution set to invalid
  • Status changed from reopened to closed
  • Summary changed from unique_together uses 'iexact' matching, I expected this to be 'exact' to unique_together on SQLite uses 'iexact' matching, I expected this to be 'exact'

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

Note: See TracTickets for help on using tickets.
Back to Top