#10942: Using `select` and `select_params` fails, but using only `select` works,
and the SQL generated for both is identical.
------------------------------------------+---------------------------------
Reporter: mrmachine | Owner: nobody
Status: new | Milestone:
Component: Database layer (models, ORM) | Version: SVN
Keywords: extra select select_params | Stage: Unreviewed
Has_patch: 0 |
------------------------------------------+---------------------------------
As per the [http://groups.google.com/group/django-
users/browse_thread/thread/50c1877fe8339609 google groups discussion], I'm
seeing inconsistent results when I use `extra()` with `select` and
`select_params` arguments compared to injecting my params directly into
the `select` argument.
As per Malcolm's suggestion, I've now also tried using
`django.db.connection` and `pysqlite2` to execute the raw SQL, as well as
copy & pasting the generated SQL directly into the sqlite3 prompt.
Everything works except using `extra()` with `select` and `select_params`
arguments.
{{{
>>> from django.contrib.auth.models import User
>>> # using `select_params` fails, with both the specified users being
returned with `featured` as 0.
>>> q1 = User.objects.extra(
... select={'featured': 'auth_user.id IN (%s)'},
... select_params=['1,2']
... ).values('featured', 'pk', 'username').order_by('-featured', 'pk',
'username')
>>> print list(q1)
[{'username': u'admin', 'pk': 1, 'featured': 0}, {'username': u'manager',
'pk': 2, 'featured': 0}]
>>> # using only `select` works, with both the specified users being
returned with `featured` as 1.
>>> q2 = User.objects.extra(
... select={'featured': 'auth_user.id IN (%s)' % '1,2'}
... ).values('featured', 'pk', 'username').order_by('-featured', 'pk',
'username')
>>> print list(q2)
[{'username': u'admin', 'pk': 1, 'featured': 1}, {'username': u'manager',
'pk': 2, 'featured': 1}]
>>> # the sql generated for both querysets is identical.
>>> str(q1.query) == str(q2.query)
True
>>> # ths sql generated looks correct.
>>> print q1.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
>>> # using django to execute raw sql works as expected.
>>> from django.db import connection
>>> cursor = connection.cursor()
>>> cursor.execute(str(q1.query)).fetchall()
[(1, 1, u'admin'), (1, 2, u'manager')]
>>> # using pysqlite2 to execute raw sql works as expected.
>>> from django.conf import settings
>>> from pysqlite2 import dbapi2
>>> connection = dbapi2.Connection(settings.DATABASE_NAME)
>>> cursor = dbapi2.Cursor(connection)
>>> cursor.execute(str(q1.query)).fetchall()
[(1, 1, u'admin'), (1, 2, u'manager')]
# using sqlite3 to execute raw sql works as expected.
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
}}}
--
Ticket URL: <http://code.djangoproject.com/ticket/10942>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
