#17952: Sqlite and mysql generating different queries with the same code
----------------------------------------------+--------------------
Reporter: glen.nelson.1@… | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: 1.3
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/17952>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
