Django fails to do .dates query on sqlite

[Vladimir Pouzanov <farcaller@…>]

In [12]: d=Post.objects.dates('date_published', 'month')
In [14]: d
Out[14]: [datetime.datetime(2007, 9, 1, 0, 0)]
In [13]: d[0]
---------------------------------------------------------------------------
<class 'sqlite3.OperationalError'>        Traceback (most recent call last)

/home/farcaller/develop/web/fw/<ipython console> in <module>()

/usr/lib/python2.5/site-packages/django/db/models/query.py in __getitem__(self, k)
    155             else:
    156                 try:
--> 157                     return list(self._clone(_offset=k, _limit=1))[0]
    158                 except self.model.DoesNotExist, e:
    159                     raise IndexError, e.args

/usr/lib/python2.5/site-packages/django/db/models/query.py in __iter__(self)
    112
    113     def __iter__(self):
--> 114         return iter(self._get_data())
    115
    116     def __getitem__(self, k):

/usr/lib/python2.5/site-packages/django/db/models/query.py in _get_data(self)
    480     def _get_data(self):
    481         if self._result_cache is None:
--> 482             self._result_cache = list(self.iterator())
    483         return self._result_cache
    484

/usr/lib/python2.5/site-packages/django/db/models/query.py in iterator(self)
    661             qn(self._field.column))), sql, group_by, self._order)
    662         cursor = connection.cursor()
--> 663         cursor.execute(sql, params)
    664
    665         has_resolve_columns = hasattr(self, 'resolve_columns')

/usr/lib/python2.5/site-packages/django/db/backends/util.py in execute(self, sql, params)
     17         start = time()
     18         try:
---> 19             return self.cursor.execute(sql, params)
     20         finally:
     21             stop = time()

/usr/lib/python2.5/site-packages/django/db/backends/sqlite3/base.py in execute(self, query, params)
    127     def execute(self, query, params=()):
    128         query = self.convert_query(query, len(params))
--> 129         return Database.Cursor.execute(self, query, params)
    130
    131     def executemany(self, query, param_list):

<class 'sqlite3.OperationalError'>: near "GROUP": syntax error

the resulting query "SELECT django_date_trunc("month", "blog_post"."date_published") FROM "blog_post" LIMIT 1 GROUP BY 1 ORDER BY 1 ASC" is wrong according to sqlite docs, LIMIT should go after GROUP BY (in fact - mostly in the end).

[Malcolm Tredinnick]

Fixed on the queryset-refactor branch. Will be closed when that branch is merged into trunk.

[anonymous]

Actually, this problem is not limited to sqlite. Limiting a dates() query fails on both MySQL and Postgres as well.

[Malcolm Tredinnick]

(In [7477]) Merged the queryset-refactor branch into trunk.

This is a big internal change, but mostly backwards compatible with existing
code. Also adds a couple of new features.

Fixed #245, #1050, #1656, #1801, #2076, #2091, #2150, #2253, #2306, #2400, #2430, #2482, #2496, #2676, #2737, #2874, #2902, #2939, #3037, #3141, #3288, #3440, #3592, #3739, #4088, #4260, #4289, #4306, #4358, #4464, #4510, #4858, #5012, #5020, #5261, #5295, #5321, #5324, #5325, #5555, #5707, #5796, #5817, #5987, #6018, #6074, #6088, #6154, #6177, #6180, #6203, #6658