Sqlite and mysql generating different queries with the same code

[glen.nelson.1@…]

I happened to hit a case where mysql and sqlite generated very similiar queries. However, I would claim that sqlite is 'wrong':

With mysql (note, I use 'run_hours', but I should be using 'run_seconds'):

>>> queryset = models.TestCaseRun.objects.filter(run_time__gte=300, submitted_date__range=(start, end)
           ).values('testcase__category__category').annotate(run_hours=Sum('run_time'))

>>> print queryset
[{'testcase__category__category': u'TestCat1', 'run_hours': 33139},
{'testcase__category__category': u'TestCat2', 'run_hours': 723550},
{'testcase__category__category': u'TestCat3', 'run_hours': 777324},
{'testcase__category__category': u'TestCat4', 'run_hours': 212719},
{'testcase__category__category': u'TestCat5', 'run_hours': 85552}]

>>> print queryset.query
# split for readability
SELECT `metrics_testcasecategory`.`category`, SUM(`metrics_testcaserun`.`run_time`) AS `run_hours`
FROM `metrics_testcaserun`
INNER JOIN `metrics_testcase` ON (`metrics_testcaserun`.`testcase_id` = `metrics_testcase`.`id`) 
LEFT OUTER JOIN `metrics_testcasecategory` ON (`metrics_testcase`.`category_id` = `metrics_testcasecategory`.`id`)
WHERE (`metrics_testcaserun`.`run_time` >= 300
AND `metrics_testcaserun`.`submitted_date` BETWEEN 2012-03-01 00:00:00 and 2012-03-22 13:02:30) 
GROUP BY `metrics_testcasecategory`.`category`, `metrics_testcasecategory`.`category`
ORDER BY `metrics_testcaserun`.`submitted_date` DESC, `metrics_testcaserun`.`start_date` DESC

With sqlite3:

>>> queryset = models.TestCaseRun.objects.filter(run_time__gte=300, submitted_date__range=(start, end)
           ).values('testcase__category__category').annotate(run_hours=Sum('run_time'))

>>> print queryset
[{'testcase__category__category': u'TestCat1', 'run_hours': 3211},
{'testcase__category__category': u'TestCat2', 'run_hours': 319},
{'testcase__category__category': u'TestCat3', 'run_hours': 331},
{'testcase__category__category': u'TestCat1', 'run_hours': 358},
{'testcase__category__category': u'TestCat2', 'run_hours': 360},
{'testcase__category__category': u'TestCat2', 'run_hours': 364},
{'testcase__category__category': u'TestCat2', 'run_hours': 375},
'...(remaining elements truncated)...']

>>> print queryset.query
# split for readability
SELECT "metrics_testcasecategory"."category", SUM("metrics_testcaserun"."run_time") AS "run_hours"
FROM "metrics_testcaserun"
INNER JOIN "metrics_testcase" ON ("metrics_testcaserun"."testcase_id" = "metrics_testcase"."id") 
LEFT OUTER JOIN "metrics_testcasecategory" ON ("metrics_testcase"."category_id" = "metrics_testcasecategory"."id")
WHERE ("metrics_testcaserun"."run_time" >= 300
AND "metrics_testcaserun"."submitted_date" BETWEEN 2012-03-01 00:00:00 and 2012-03-22 13:02:55.825543)
GROUP BY "metrics_testcasecategory"."category", "metrics_testcasecategory"."category", "metrics_testcaserun"."submitted_date", "metrics_testcaserun"."start_date"
ORDER BY "metrics_testcaserun"."submitted_date" DESC, "metrics_testcaserun"."start_date" DESC

Looks to me like it is just the group by that is different.

[Aymeric Augustin]

Could you provide your model definition so we can reproduce the bug?

Could you also check if this isn't fixed in master, or at least 1.4?

Thanks!

[Anssi Kääriäinen]

I tested this with a trivial test in regressiontests/aggregation_regress:

    def test_range_filtering(self):
        start = datetime.datetime(1900, 1, 1)
        end = datetime.datetime(2100, 1, 1)
        qs = Book.objects.filter(pubdate__range=(start, end)).values(
            'contact').annotate(Count('id')).order_by()
        print qs
        print qs.query

I could not reproduce the reported error in HEAD. It might be I am missing some key piece of the puzzle. That piece should be provided by the original reporter. Closing as worksforme, please reopen with additional information about the used models if the error is still present for you in 1.4 or HEAD.