Code

Opened 2 years ago

Closed 2 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.

Attachments (0)

Change History (2)

comment:1 Changed 2 years ago by aaugustin

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 Changed 2 years ago by akaariai

  • Resolution set to worksforme
  • Status changed from new to 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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.