Thanks Karen, Tom, I thought I understood collations and rechecking the MySQL docs I think that is still the case. I'm more than happy with __exact using the DB collation even when it is case insensitive.
What I certainly didn't grasp until now was the SQL standard's (SQL-92, specifically) comparison predicate requirements: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt --> 8.2 <comparison predicate> General Rules 3) <quote> The comparison of two character strings is determined as fol- lows: a) If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad char- acters, where the pad character is chosen based on CS. If CS has the NO PAD attribute, then the pad character is an implementation-dependent character different from any char- acter in the character set of X and Y that collates less than any string under CS. Otherwise, the pad character is a <space>. etc. </quote> What this means is that if Django __exact is using '=' at the SQL level, a SQL-92 compliant database without 'NO PAD' will return TRUE for 'a' = 'a '. (To me this is non-intuitive but is how MySQL behaves.) If Django __iexact is using 'LIKE', a SQL-92 compliant database will return FALSE for 'a' LIKE 'a ' and also for 'a ' LIKE 'a' (from my reading of the <like predicate> spec, ibid.) I would contend that in the specific case of trailing spaces, this leads to an inconsistency between __exact and __iexact on any RDBMS that behaves in this (compliant, padding with spaces) way: even if the collation is Case Sensitive. So does that mean the other backends are non-compliant with SQL-92 or do they define the NO PAD attribute? Does anyone here know? Sorry if this is obvious to everyone and I'm just missing something (please point it out to me if you can)! If anyone who has read later SQL specifications can indicate whether this behaviour has changed in later versions, that would be helpful too. Best wishes, Steven. On 8 June 2010 15:24, Karen Tracey <kmtra...@gmail.com> wrote: > On Tue, Jun 8, 2010 at 10:00 AM, Tom Evans <tevans...@googlemail.com>wrote: > >> This may have other unintended side effects, certainly it will make >> the match case sensitive, I'm not sure if __exact is supposed to be >> case sensitive. >> > > Yes, it will have side-effects, bad enough that it's not worth doing. It > was once done, so as to make the exact comparison case-sensitive like the > other Django-supported DBs: > > http://code.djangoproject.com/changeset/7798 > > and eventually reverted: > > http://code.djangoproject.com/changeset/8319 > > after considerable debate and investigation into possible ways to make > __exact on MySQL behave more like other Django DBs. > > In the end (at least for now) the solution is to document that the results > for comparisons on MySQL are governed by the MySQL collation setting: > http://docs.djangoproject.com/en/dev/ref/databases/#collation-settings. > > Karen > -- > http://tracey.org/kmt/ > > > -- > You received this message because you are subscribed to the Google Groups > "Django users" group. > To post to this group, send email to django-us...@googlegroups.com. > To unsubscribe from this group, send email to > django-users+unsubscr...@googlegroups.com<django-users%2bunsubscr...@googlegroups.com> > . > For more options, visit this group at > http://groups.google.com/group/django-users?hl=en. > -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-us...@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
