Misbehaving Q objects
|Reported by:||Ben Khoo||Owned by:||nobody|
|Component:||Database layer (models, ORM)||Version:||master|
|Severity:||Keywords:||Q Oracle sqlite OR qs-rf-fixed|
|Has patch:||no||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
Description (last modified by bouldersprinters)
The following ticket will try to highlight a problem that I have found when using the 'OR' operator on Q objects in the boulder-oracle-sprint branch.
My model looks like this.
class Test(models.Model): num = models.IntegerField() def __str__(self): return 'num=%d'%self.num
The following code will highlight the bug:
In : from testapp.models import * In : from django.db.models import Q In : for i in [4,8,12]: ...: Test(num=i).save() ...: In : Test.objects.filter(num__lt=4) Out:  In : Test.objects.filter(num__gt=8, num__lt=12) Out:  In : Test.objects.filter(Q(num__lt = 4) | Q(num__gt=8, num__lt=12)) Out: [<Test: num=12>, <Test: num=4>, <Test: num=8>] In : Test.objects.filter(Q(num__gt=8, num__lt=12) | Q(num__lt = 4)) Out: [<Test: num=12>, <Test: num=4>, <Test: num=8>] In : Test.objects.filter(Q(num__gt=8) & Q(num__lt=12) | Q(num__lt = 4)) Out: 
Lines 6 and 7 illustrate the bug.
The query appears to indicate that there are three Test objects where 'num' is less than 4 or, greater than 8 and less than 12.
Lines 4 and 5 show that no such object should exist.
Line 8 shows how I would expect the query to run.
The following is the formated (but otherwise unmodified) SQL query produced by django.
SELECT * FROM "TESTAPP_TEST" WHERE (("TESTAPP_TEST"."NUM" < 4 OR "TESTAPP_TEST"."NUM" > 8 OR "TESTAPP_TEST"."NUM" < 12))
The issue is that the second 'OR' operator should be an AND operator. Also for the sake of safety/sanity I feel that queries specified within the Q objects should also be surrounded by a bracket.
The corrected query should read
SELECT * FROM "TESTAPP_TEST" WHERE ((("TESTAPP_TEST"."NUM" < 4) OR ("TESTAPP_TEST"."NUM" > 8 AND "TESTAPP_TEST"."NUM" < 12)))
Change History (10)
comment:1 follow-up: ↓ 3 Changed 7 years ago by mtredinnick
- Needs documentation unset
- Needs tests unset
- Patch needs improvement unset
comment:4 Changed 7 years ago by bouldersprinters
- Component changed from Uncategorized to Database wrapper
- Description modified (diff)
- Keywords sqlite added
- Owner changed from jacob to adrian
- Version changed from other branch to SVN
comment:7 Changed 6 years ago by mtredinnick
- Summary changed from Misbehaving Q objects in boulder-oracle-sprint branch to Misbehaving Q objects
- Triage Stage changed from Unreviewed to Accepted