Bug in the query statement

[ygpark2@…]

Lately, I got this error

Environment:

Request Method: GET
Request URL: http://127.0.0.1:8000/pages/
Django Version: 0.97-pre-SVN-7548
Python Version: 2.5.2
Installed Applications:
['django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.admin',
 'pages',
 'hierarchical']
Installed Middleware:
('django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.locale.LocaleMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.middleware.doc.XViewMiddleware')


Traceback:
File "/Users/ygpark/lib/django_trunk/django/core/handlers/base.py" in get_response
  82.                 response = callback(request, *callback_args, **callback_kwargs)
File "/Users/ygpark/lib/django-page-cms/utils.py" in _dec
  9.         response = func(request, *args, **kwargs)
File "/Users/ygpark/lib/django-page-cms/pages/views.py" in details
  12.     pages = HierarchicalNode.get_first_level_objects(Page)
File "/Users/ygpark/lib/django-page-cms/hierarchical/models.py" in get_first_level_objects
  98.         return [obj.object for obj in objs]
File "/Users/ygpark/lib/django_trunk/django/db/models/query.py" in _result_iter
  78.                 self._fill_cache()
File "/Users/ygpark/lib/django_trunk/django/db/models/query.py" in _fill_cache
  490.                     self._result_cache.append(self._iter.next())
File "/Users/ygpark/lib/django_trunk/django/db/models/query.py" in iterator
  162.         for row in self.query.results_iter():
File "/Users/ygpark/lib/django_trunk/django/db/models/sql/query.py" in results_iter
  200.         for rows in self.execute_sql(MULTI):
File "/Users/ygpark/lib/django_trunk/django/db/models/sql/query.py" in execute_sql
  1466.         cursor.execute(sql, params)
File "/Users/ygpark/lib/django_trunk/django/db/backends/util.py" in execute
  18.             return self.cursor.execute(sql, params)
File "/Users/ygpark/lib/django_trunk/django/db/backends/sqlite3/base.py" in execute
  136.         return Database.Cursor.execute(self, query, params)

Exception Type: OperationalError at /pages/
Exception Value: near "order": syntax error

I think this kind of sql syntax error is caused in the django framework.

So can you check whether this is bug or not?

[ygpark2@…]

I omit something.

while I look up the debug page, I can find the final statement is supposed to execute.

SELECT "hierarchical_hierarchicalobject"."id", "hierarchical_hierarchicalobject"."node_id", "hierarchical_hierarchicalobject"."content_type_id", "hierarchical_hierarchicalobject"."object_id", "hierarchical_hierarchicalnode"."id", "hierarchical_hierarchicalnode"."name", "hierarchical_hierarchicalnode"."parent_id", "hierarchical_hierarchicalnode"."order", "django_content_type"."id", "django_content_type"."name", "django_content_type"."app_label", "django_content_type"."model" FROM "hierarchical_hierarchicalobject" INNER JOIN "django_content_type" ON ("hierarchical_hierarchicalobject"."content_type_id" = "django_content_type"."id") INNER JOIN "hierarchical_hierarchicalnode" ON ("hierarchical_hierarchicalobject"."node_id" = "hierarchical_hierarchicalnode"."id") WHERE "hierarchical_hierarchicalobject"."content_type_id" = ? AND "hierarchical_hierarchicalobject"."node_id" IN (?, ?, ?) ORDER BY "hierarchical_hierarchicalnode".order ASC

In this statement,

ORDER BY "hierarchical_hierarchicalnode".order ASC == should be

ORDER BY "hierarchical_hierarchicalnode"."order" ASC

, I think

[Russell Keith-Magee]

Please use the mailing lists to answer 'what is wrong?' questions. The ticket database is for tracking known problems.

[anonymous]

To reproduce use:
MyModel.objects.select_related().order_by('related_table.order')

This is not affected:
MyModel.objects.select_related().order_by('related_table.notorderfield')

It seems to produce wrong SQL when the field "order" is used in "table.order" syntax.

Only sqlite is affected. The same code runs in PostgreSQL fine.

[toke-djangp@…]

Simple fix without tests

[Michael Axiak]

It seems that this is a valid bug -- that the column name in an ORDER BY clause is not quoted (at least in SQLite).

Am I wrong? Also, is the patch correct? I don't know the DB API all that much, but it seems too obvious to have not been there before.

[Ramiro Morales]

Seems to be a valid bug regarding the 0.96 backwards compatible, abstraction-leaking syntax for specifying ordering by a related model's field (namely, using <name of the related model table>.<field name>. Since queryset-refactor (that the OP seems to be running as he is using r7548) was merged, it is also possible (recommended?) to use 'relatedmodel__fieldname'. See ​http://www.djangoproject.com/documentation/db-api/#order-by-fields