django discarding MySQL results for "in" operator
JonUK
I'm using django 1.0.2 and the tagging app to retrieve a tag via a
database "in" query, and something is causing the query results to be
discarded.
In particular, this line of code from tagging.utils.get_tag_list()
executes:
return Tag.objects.filter(name__in=[force_unicode(tag)
for tag in tags])
and at this point:
(Pdb) p tags
[u'sea']
The thread of execution can be traced into the method
django.db.models.sql.query.execute_sql(self, result_type=MULTI), and
to this line of code:
cursor.execute(sql, params)
and over here:
>
c:\python26\lib\site-packages\django\db\models\sql\query.py(1735)
execute_sql()
-> cursor.execute(sql, params)
(Pdb) p sql
'SELECT "tagging_tag"."id", "tagging_tag"."name" FROM "tagging_tag"
WHERE "tagging_tag"."name" IN (%s) ORDER BY "tagging_tag"."name" ASC'
(Pdb) p params
(u'sea',)
(Pdb)
If I audit what's submitted to MySQL via MySQL logging, it reports:
SELECT `tagging_tag`.`id`, `tagging_tag`.`name` FROM `tagging_tag`
WHERE `tagging_tag`.`name` IN ('sea') ORDER BY `tagging_tag`.`name`
ASC
which looks correct - however django returns an empty list.
If I execute the query interactively in MySQL, I get the expected
(correct) result:
+----+------+
| id | name |
+----+------+
| 28 | Sea |
+----+------+
1 row in set (0.00 sec)
I suspect this is a configuration problem but have no idea where to
look - can anyone help?
Thanks
Karen Tracey
I can't recreate with a similar (though without tagging, just doing a similar in query for one of my models) query on my MySQL DB/django app. The .filter(xxxx__in=[...]) returns case-insensitive results as expected. As you've traced it down to where the SQL executes, have you gone farther to determine if somehow that SQL query when issued by Django isn't returning any results while the one you issue in the mysql command does? It'd be pretty bizarre, but maybe the SQL is returning the same result but somehow it's getting lost before the .filter() is returning? The alternative seems to be that the same SQL query is returning different results, which seems equally bizarre. (There is no configuration option to change what collation is used for the query, which is all I could think would change the result as you have described -- Django always uses the default collation.)
Karen
Malcolm Tredinnick
[...]
First off, let's be clear: Django isn't throwing away results or
anything like. The database simply isn't returning them. So we need to
work out why that is happening.
One thing that immediately jumps out is that the "name" column is "Sea",
not "sea" (different capitalisation). Have you configured your database
to be case-sensitive in comparisons (and perhaps the MySQL shell is
case-insensitive or something?). Rule that in or out as a start.
A second step would be to start simplifying the query to work out how
simple it has to be before it starts working. For example, remove the
dynamically generated "in" list and replace it with a hard-coded string
(e.g. "sea"). That shouldn't change anything, but it removes one layer
of complexity and is the first step of many towards creating the
simplest possible example. Test that out and see if it still fails.
What happens when you evaluate that queryset from the "manage.py shell"
prompt? That is, import the Tag model and type in the
Tag.objects.filter(...) line and see what happens. If you don't see the
same result as when the code is executed in the program, you now have a
difference to work with. Again, that's trying to simplify things as much
as possible -- we're now down to just the line in question.
If that still doesn't generate any results, see if the most basic
queryset Tag.objects.all() returns something. Then make a much simple
filter -- such as Tag.objects.filter(id=28), which should return the row
you indicate. Then try Tag.objects.filter(name="sea") and
Tag.objects.filter(name="Sea").
Hopefully you can see where this is going: start from something that
doesn't work and remove bits. Start from something that works and add
bits. Eventually you'll meet in the middle and we'll have a better idea
of what the critical piece is.
Regards,
Malcolm
Jon
>
>
> On Mon, Jan 5, 2009 at 3:37 PM, JonUK <jdoull <at> flinttechnology.co.uk>
> --~--~---------~--~----~------------~-------~--~----~
> You received this message because you are subscribed to the Google Groups
"Django users" group. To post to this group, send email to django-users <at>
googlegroups.com To unsubscribe from this group, send email to django-
users+unsubscribe <at> googlegroups.com For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
> -~----------~----~----~----~------~----~------~--~---
>
Apologies for the long silence before responding. Although I do not recall if
this was the precisely the issue (it was a long time ago), I did eventually hunt
down an accepted bug in Mysql that makes the "in" clause unreliable,
particularly when used in subqueries. The recommended workaround was to replace
with joins, however my preferred solution was to use Postgres, which solved this
problem and worked a treat. I appreciated your responses - so thank you.