Opened 13 years ago
Closed 12 years ago
#17952 closed Uncategorized (worksforme)
Sqlite and mysql generating different queries with the same code
Reported by: | Owned by: | nobody | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.3 |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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.
Change History (2)
comment:1 by , 13 years ago
comment:2 by , 12 years ago
Resolution: | → worksforme |
---|---|
Status: | new → closed |
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.
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!