Opened 16 years ago

Closed 16 years ago

Last modified 13 years ago

#7302 closed (fixed)

Missed quoting in SQL ordering fragment

Reported by: ygpark2@… Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Keywords: sqlite, db, wrapper 1.0-blocker
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description (last modified by Ramiro Morales)

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?

Attachments (2)

7302.diff (651 bytes ) - added by toke-djangp@… 16 years ago.
Simple fix without tests
7302.1.diff (1.2 KB ) - added by Ivan Sagalaev 16 years ago.
Possible fix with tests

Download all attachments as: .zip

Change History (22)

comment:1 by ygpark2@…, 16 years ago

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

comment:2 by Russell Keith-Magee, 16 years ago

Resolution: invalid
Status: newclosed

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

comment:3 by anonymous, 16 years ago

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.

by toke-djangp@…, 16 years ago

Attachment: 7302.diff added

Simple fix without tests

comment:4 by Michael Axiak, 16 years ago

Resolution: invalid
Status: closedreopened
Triage Stage: UnreviewedAccepted

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.

comment:5 by Ramiro Morales, 16 years ago

Description: modified (diff)

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

comment:6 by anonymous, 16 years ago

Has patch: set

comment:7 by anonymous, 16 years ago

I confirm that the fix is always working with the revision 8335.

the line affected is the following :

objs = HierarchicalObject.objects.filter(content_type=ctype, nodein=nodes).order_by('hierarchical_hierarchicalnode.order').select_related()

comment:8 by Thomas Kerpe, 16 years ago

milestone: 1.0
Triage Stage: AcceptedReady for checkin

comment:9 by Russell Keith-Magee, 16 years ago

Resolution: invalid
Status: reopenedclosed

First off - there's no way in hell that this is ready for checkin. It may well be a simple fix, but there is no test case, and the discussion is so horribly confused it's impossible to tell what actually causes the problem. In all the discussion we have stack traces, example code, but no actual models with corresponding queries that fail.

What what I can glean, this isn't a bug - It's a usage of a syntax that has been deprecated.

If you disagree, you need to provide:

  • A test case that demonstrates the problem - preferably one that is integrated with the regressiontests/queries suite.
  • A link to the documentation that says that the old syntax should actually work.

comment:10 by Thomas Kerpe, 16 years ago

News.objects.order_by('news_news.category_id')
resulting in:
SELECT "news_news"."id", "news_news"."title", "news_news"."category_id" FROM "news_news" ORDER BY "news_news".category_id ASC

which leads to wrong SQL. Every developer is strongly advised not to allow users input the value for order_by() unchecked or at least filter the "." out to force the new syntax. It has some other issues too but these are out of scope of this ticket. This is in my opinion definitively a bug as long as this table.field syntax exists in that way (documented or not).

comment:11 by Thomas Kerpe, 16 years ago

Resolution: invalid
Status: closedreopened
Triage Stage: Ready for checkinAccepted

For completeness to reproduce:

bug/models.py

from django.db import models

class MyModel(models.Model):
    """
    >>> MyModel(title="test",order=3).save()
    >>> MyModel.objects.order_by('order')
    [<MyModel: test>]
    >>> MyModel.objects.order_by('bug_mymodel.title')
    [<MyModel: test>]
    >>> MyModel.objects.order_by('bug_mymodel.order')
    [<MyModel: test>]
    """
    title = models.CharField(max_length=200)
    order = models.IntegerField()
    
    def __unicode__(self):
        return self.title

comment:12 by Thomas Kerpe, 16 years ago

Owner: changed from nobody to Thomas Kerpe
Status: reopenednew

comment:13 by Thomas Kerpe, 16 years ago

Owner: changed from Thomas Kerpe to nobody

comment:14 by Malcolm Tredinnick, 16 years ago

Summary: Bug in the query statementMissed quoting in SQL ordering fragment

This is a bug, but not for the reasons you claim (we aren't going to fix bugs that just exist for that old-style ordering, since it is bug-for-bug compatible with old code at the moment and the way to fix those bugs is not to use it). It's a bug because it bites users of extra_order_by.

So we need a test that exercises the bug using extra_order_by, probably by creating a model with a field called order and including that model's table in an extra() block. Then we can look at including it.

In future, however, please do not reopen tickets that have been closed by core developers. We have a process for what to do if you don't like the closure.

comment:15 by Thomas Kerpe, 16 years ago

Patch needs improvement: set

The patch does not solve the whole issue. The "bad" parts still exists.

comment:16 by Malcolm Tredinnick, 16 years ago

But you're not going to help by providing a test case or any further explanation of what you mean? How are we going to be able to fix the problem then?

by Ivan Sagalaev, 16 years ago

Attachment: 7302.1.diff added

Possible fix with tests

comment:17 by Ivan Sagalaev, 16 years ago

I've made a minimal test that fails with current trunk upon not quoting the word "order" in order_by. It's in the patch 7302.1.diff along with a possible fix. It's "possible" because I'm not sure that quoting with qn2 there is the right thing for all cases.

comment:18 by Jacob, 16 years ago

Keywords: 1.0-blocker added

comment:19 by Russell Keith-Magee, 16 years ago

Resolution: fixed
Status: newclosed

(In [8794]) Fixed #7302: Corrected quoting of columns in extra_group_by. Thanks to Ivan Sagalaev for the patch and initial test.

comment:20 by Jacob, 13 years ago

milestone: 1.0

Milestone 1.0 deleted

Note: See TracTickets for help on using tickets.
Back to Top