Opened 13 years ago

Closed 12 years ago

#17952 closed Uncategorized (worksforme)

Sqlite and mysql generating different queries with the same code

Reported by: glen.nelson.1@… 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 Aymeric Augustin, 12 years ago

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!

comment:2 by Anssi Kääriäinen, 12 years ago

Resolution: worksforme
Status: newclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top