Strange inconsistency between SQL and ORM with SQLite.

[Tai Lee]

I'm trying to add an extra selection to a queryset, `featured`, which
should be True (or 1) if the primary key matches a number of known
values.

It appears to work as expected both in SQL and the ORM when matching
against a single value. When I try matching against multiple values, I
get the expected results in SQL (`featured` is set to `1` for matching
rows), but in the ORM all rows set `featured` to 0.

I've used the User model in an example, below. There's also a pretty
version at http://dpaste.com/hold/34599/

# interactive shell.

>>> from django.contrib.auth.models import User

# the specified user (1) is returned with `featured` as 1.

>>> q = User.objects.extra(
... select={'featured': 'auth_user.id IN (%s)'},
... select_params=['1']
... ).values('featured', 'pk', 'username').order_by('-featured', 'pk',
'username')
>>> print q.query
SELECT (auth_user.id IN (1)) AS "featured", "auth_user"."id",
"auth_user"."username" FROM "auth_user" ORDER BY "featured" DESC,
"auth_user"."id" ASC, "auth_user"."username" ASC
>>> print list(q)
[{'username': u'admin', 'pk': 1, 'featured': 1}, {'username':
u'manager', 'pk': 2, 'featured': 0}]

# both of the specified users (1,2) are both incorrectly returned with
`featured` as 0.

>>> q = User.objects.extra(
... select={'featured': 'auth_user.id IN (%s)'},
... select_params=['1,2']
... ).values('featured', 'pk', 'username').order_by('-featured', 'pk',
'username')
>>> print q.query
SELECT (auth_user.id IN (1,2)) AS "featured", "auth_user"."id",
"auth_user"."username" FROM "auth_user" ORDER BY "featured" DESC,
"auth_user"."id" ASC, "auth_user"."username" ASC
>>> print list(q)
[{'username': u'admin', 'pk': 1, 'featured': 0}, {'username':
u'manager', 'pk': 2, 'featured': 0}]

# the sql statements generated for both querysets are valid and work
as expected when executed from the sqlite shell directly.

sqlite> SELECT (auth_user.id IN (1)) AS "featured", "auth_user"."id",
"auth_user"."username" FROM "auth_user" ORDER BY "featured" DESC,
"auth_user"."id" ASC, "auth_user"."username" ASC
1|1|admin
0|2|manager

sqlite> SELECT (auth_user.id IN (1,2)) AS "featured",
"auth_user"."id", "auth_user"."username" FROM "auth_user" ORDER BY
"featured" DESC, "auth_user"."id" ASC, "auth_user"."username" ASC
1|1|admin
1|2|manager
sqlite>

[Malcolm Tredinnick]

The more relevant test here is what happens when you remove only Django
from the equation and use pysqlite or the sqlite Python module to do the
query. It could be something happening at that level. Off the top of my
head, I can't think of anything Django's doing here that would be
changing things.

Realise, too, that pysqlite/sqlite module version, sqlite binary version
and Django version are all likely to be relevant variables here
(although less so for the Django version). SQLite changes fairly rapidly
in terms of fixing bugs and its weak-typing behaviour does lead to
strange things like this from time to time.

Regards,
Malcolm

[Tai Lee]

Thanks for your feedback Malcolm. I will try to test without Django
tomorrow.

Since my original post I have noticed that inserting my params
directly into the `select` argument with the string formatting
operator works, but passing them in with the `select_params` argument
causes a failure as described in my original post. The SQL statements
generated are identical.

>>> from django.contrib.auth.models import User

>>> q = User.objects.extra(
... select={'featured': 'auth_user.id IN (%s)'},
... select_params=['1,2']
... ).values('featured', 'pk', 'username').order_by('-featured', 'pk',
'username')

>>> print q.query
SELECT (auth_user.id IN (1,2)) AS "featured", "auth_user"."id",
"auth_user"."username" FROM "auth_user" ORDER BY "featured" DESC,
"auth_user"."id" ASC, "auth_user"."username" ASC

# both of the specified users (1,2) are incorrectly returned with
`featured` as 0.

>>> print list(q)
[{'username': u'admin', 'pk': 1, 'featured': 0}, {'username':
u'manager', 'pk': 2, 'featured': 0}]

>>> q = User.objects.extra(
... select={'featured': 'auth_user.id IN (%s)' % '1,2'}

... ).values('featured', 'pk', 'username').order_by('-featured', 'pk',
'username')

>>> print q.query
SELECT (auth_user.id IN (1,2)) AS "featured", "auth_user"."id",
"auth_user"."username" FROM "auth_user" ORDER BY "featured" DESC,
"auth_user"."id" ASC, "auth_user"."username" ASC

# both of the specified users (1,2) are correctly returned with
`featured` as 1.

>>> print list(q)
[{'username': u'admin', 'pk': 1, 'featured': 1}, {'username':

u'manager', 'pk': 2, 'featured': 1}]



On Apr 26, 4:37 am, Malcolm Tredinnick <malc...@pointy-stick.com>
wrote:

> On Thu, 2009-04-16 at 23:11 -0700, Tai Lee wrote:
> > I'm trying to add an extra selection to a queryset, `featured`, which
> > should be True (or 1) if the primary key matches a number of known
> > values.
>
> > It appears to work as expected both in SQL and the ORM when matching
> > against a single value. When I try matching against multiple values, I
> > get the expected results in SQL (`featured` is set to `1` for matching
> > rows), but in the ORM all rows set `featured` to 0.
>
> > I've used the User model in an example, below. There's also a pretty

> > version athttp://dpaste.com/hold/34599/

[Tai Lee]

I've tested with `django.db.connection` and `pysqlite2` in python and
the sqlite3 prompt, all work as expected. Only using `extra()` with
both `select` and `select_params` doesn't work.

I've opened up a ticket [1] for this. After discussion with Alex
Gaynor on IRC, it looks like the problem is that Django cannot accept
a string of comma separated values OR an iterator as a param to `extra
()` as it will not be properly escaped. Hopefully this can be fixed as
a feature enhancement, or the documentation changed to make this
limitation clear.

[1] http://code.djangoproject.com/ticket/10942

Cheers.
Tai.