id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
9358	.dates(...) only spitting out a single date, bug in queryset order	Mathijs de Bruin	Malcolm Tredinnick	"Due to a bug in the add_date_select function (db/models/sql/subqueries.py:400) I am only seeing the latest date (when using SQLite).

{{{
In [2]: Event.objects.all()
Out[2]: [<Event: fsddf on 2009-01-03>, <Event: Fietsen on 2008-10-12>, <Event: Kaas on 2008-10-11>, <Event: Woei on 2008-06-03>]
}}}
{{{
In [3]: Event.objects.dates('event_date', 'day')
Out[3]: [datetime.datetime(2008, 6, 3, 0, 0)]
}}}


Where the latter produces the following SQL code:
{{{
SELECT DISTINCT django_date_trunc(""day"", ""agenda_event"".""event_date"") FROM ""agenda_event"" ORDER BY 1 ASC LIMIT 21
}}}

Here, the mysterious ORDER BY 1 clause gets SQLite in confusion. It comes straight from:
{{{
    def add_date_select(self, field, lookup_type, order='ASC'):
        <snap>
        self.distinct = True
        self.order_by = order == 'ASC' and [1] or [-1]
}}}

We can see that this is indeed the cause by running:
{{{
In [31]: Event.objects.dates('event_date', 'day').order_by('event_date')
Out[31]: [datetime.datetime(2008, 6, 3, 0, 0), datetime.datetime(2008, 10, 11, 0, 0), datetime.datetime(2008, 10, 12, 0, 0), datetime.datetime(2009, 1, 3, 0, 0)]
}}}

I propose to change the named line into:
{{{
        self.order_by = order == 'ASC' and [field.name] or ['-%s' % field.name]
}}}

After which the code is working just fine:

{{{
In [3]: Event.objects.all().dates('event_date', 'day')
Out[3]: [datetime.datetime(2008, 6, 3, 0, 0), datetime.datetime(2008, 10, 11, 0, 0), datetime.datetime(2008, 10, 12, 0, 0), datetime.datetime(2009, 1, 3, 0, 0)]
}}}

FYI: Django version 1.1 pre-alpha, r9231 of trunk


"		closed	Database layer (models, ORM)	dev		fixed	dates order .dates sqlite	drbob@…	Accepted	1	0	0	1	0	0