babonk babonk - 3 months ago 14
SQL Question

Django seems to be falsely claiming an SQL syntax error

Django seems to be falsely claiming that I have an error in my SQL syntax. The query runs fine (returning the intended results) in the django dbshell but spawns an error when the query is run through Django. Here is the code (tracebacks follow):

#this code is inside the models.Customer.display_sharers() function
sharers_by_action_count = Sharer.objects.raw('''
SELECT wordout_sharer.id, COUNT(actions_of_type.id) AS action_count
FROM
wordout_customer
INNER JOIN wordout_sharer
ON wordout_sharer.customer_id = wordout_customer.id
LEFT JOIN wordout_click
ON wordout_sharer.id = wordout_click.sharer_id
LEFT JOIN
(SELECT wordout_action.id, wordout_action.click_id
FROM wordout_action
WHERE
wordout_action.action_type_id = %s) as actions_of_type
ON actions_of_type.click_id = wordout_click.id
WHERE wordout_customer.id = %s
GROUP BY wordout_sharer.id
ORDER BY action_count %s
''', (action_type_id, self.id, direction))

force_execution = list(sharers_by_action_count) #force the query to run by converting it to a list.. this is to trigger the error.


Here is the traceback:

Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/Users/me/sources/django_wordout/../django_wordout/wordout/models.py", line 106, in display_sharers
if order_by == 'action_count': #we have to make a special query for when they want to sort by the count of a specific action
File "/Users/me/sources/django_wordout/../django_wordout/wordout/models.py", line 92, in sharers_by_action_count_with_total_clicks
force_exec = list(sharers_by_action_count)
File "/Library/Python/2.7/site-packages/django/db/models/query.py", line 1324, in __iter__
query = iter(self.query)
File "/Library/Python/2.7/site-packages/django/db/models/sql/query.py", line 67, in __iter__
self._execute_query()
File "/Library/Python/2.7/site-packages/django/db/models/sql/query.py", line 81, in _execute_query
self.cursor.execute(self.sql, self.params)
File "/Library/Python/2.7/site-packages/django/db/backends/util.py", line 34, in execute
return self.cursor.execute(sql, params)
File "/Library/Python/2.7/site-packages/django/db/backends/sqlite3/base.py", line 234, in execute
return Database.Cursor.execute(self, query, params)
DatabaseError: near "?": syntax error


When
Database.Cursor.execute(self, query, params)
is executed, here are the values of all the parameters:

self:

<django.db.backends.sqlite3.base.SQLiteCursorWrapper object at 0x10c477180>


query:

SELECT wordout_sharer.id, COUNT(actions_of_type.id) AS action_count
FROM
wordout_customer
INNER JOIN wordout_sharer
ON wordout_sharer.customer_id = wordout_customer.id
LEFT JOIN wordout_click
ON wordout_sharer.id = wordout_click.sharer_id
LEFT JOIN
(SELECT wordout_action.id, wordout_action.click_id
FROM wordout_action
WHERE
wordout_action.action_type_id = ?) as actions_of_type
ON actions_of_type.click_id = wordout_click.id
WHERE wordout_customer.id = ?
GROUP BY wordout_sharer.id
ORDER BY action_count ?


params:

(1, 1, 'DESC')


Have I found a bug in Django? Is it not possible for it to deal with certain types of queries correctly?

My configuration: I am running a pretty vanilla development configuration. For the db engine, I'm using sqlite. For migrations (which are up to date), I'm using South.

Update



Just found that replacing the
%s
's with their values makes the query work.. What could be going on to make the
%s
's problematic for Django?

Answer

According to this page, sqlite treats parameterized values as literals, which means that they insert constants of the type given instead of doing regular "text replacement".

Since ASC or DESC in an order by is a keyword, not a constant, it can't be replaced by a parameter.

This actually gives some possibly unexpected behavior, if you for example do

ORDER BY ? DESC

and you give it a column name (say column1), it actually runs but doesn't sort in your expected order. The reason being that it actually sorts by the string "column1" - which is the same for every row - instead of the actual column content.

Comments