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
17952	Sqlite and mysql generating different queries with the same code	glen.nelson.1@…	nobody	"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."	Uncategorized	closed	Database layer (models, ORM)	1.3	Normal	worksforme			Unreviewed	0	0	0	0	0	0