Code

Opened 4 years ago

Closed 3 years ago

Last modified 3 years ago

#14423 closed (fixed)

Wrong SQL on exclude with inheritance

Reported by: PhiR_42 Owned by: nobody
Component: Database layer (models, ORM) Version: 1.1
Severity: Keywords: orm exclude inheritance
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: UI/UX:

Description

I came across this on my inherited models. When I exclude using the base model qs, I get the expected results but when I do it from the derived model the null clause is left out. See below.

class TestBase(models.Model):
        test_field = models.DateField(null = True)

class TestDerived(TestBase):
        pass
>>> TestDerived.objects.create()
<TestDerived: TestDerived object>
>>> TestDerived.objects.create(test_field=datetime.date.today())
<TestDerived: TestDerived object>
>>> TestDerived.objects.exclude(test_field__lt = datetime.date.today())
[<TestDerived: TestDerived object>]
>>> TestBase.objects.exclude(test_field__lt = datetime.date.today())
[<TestBase: TestBase object>, <TestBase: TestBase object>]
>>> TestBase.objects.exclude(test_field__lt = datetime.date.today()).query.as_sql()
('SELECT `signets_testbase`.`id`, `signets_testbase`.`test_field` FROM `signets_testbase` WHERE NOT ((`signets_testbase`.`test_field` < %s  AND NOT (`signets_testbase`.`test_field` IS NULL)))', ('2010-10-08',))
>>> TestDerived.objects.exclude(test_field__lt = datetime.date.today()).query.as_sql()
('SELECT `signets_testbase`.`id`, `signets_testbase`.`test_field`, `signets_testderived`.`testbase_ptr_id` FROM `signets_testderived` INNER JOIN `signets_testbase` ON (`signets_testderived`.`testbase_ptr_id` = `signets_testbase`.`id`) WHERE NOT (`signets_testbase`.`test_field` < %s )', ('2010-10-08',))

Attachments (3)

14423_1.1.diff (579 bytes) - added by PhiR_42 4 years ago.
Patch for django 1.1.2
14423_trunk.diff (576 bytes) - added by PhiR_42 4 years ago.
patch for trunk
test_14423_trunk.diff (943 bytes) - added by PhiR_42 4 years ago.
regression test

Download all attachments as: .zip

Change History (10)

comment:1 Changed 4 years ago by PhiR_42

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

I've nailed it to this particular piece of code from query.py:

1073        if negate:
1074           self.promote_alias_chain(join_list)
1075           if lookup_type != 'isnull':
1076	                if len(join_list) > 1:
1077	                    for alias in join_list:
1078	                        if self.alias_map[alias][JOIN_TYPE] == self.LOUTER:
1079	                            j_col = self.alias_map[alias][RHS_JOIN_COL]
1080	                            entry = self.where_class()
1081	                            entry.add((Constraint(alias, j_col, None), 'isnull', True), AND)
1082	                            entry.negate()
1083	                            self.where.add(entry, AND)
1084	                            break
1085	                elif not (lookup_type == 'in'
1086	                            and not hasattr(value, 'as_sql')
1087	                            and not hasattr(value, '_as_sql')
1088	                            and not value) and field.null:
1089	                    # Leaky abstraction artifact: We have to specifically
1090	                    # exclude the "foo__in=[]" case from this handling, because
1091	                    # it's short-circuited in the Where class.
1092	                    # We also need to handle the case where a subquery is provided
1093	                    self.where.add((Constraint(alias, col, None), 'isnull', False), AND)

Basically what happens is that when you query from a derived class you fall into the first if len(join_list) > 1 but since the following part is a elif you never get there. Hence when a nullable derived field is excluded (negated in the code) the null case is simply ignored. I believe transforming the elif into a if fixes the problem (it does for me at least!). This code is from trunk but this bug has been there for some time (I'm using 1.1.2).

Changed 4 years ago by PhiR_42

Patch for django 1.1.2

comment:2 Changed 4 years ago by PhiR_42

  • Has patch set

Changed 4 years ago by PhiR_42

patch for trunk

comment:3 Changed 4 years ago by Alex

  • Needs tests set
  • Triage Stage changed from Unreviewed to Accepted

FTR I'm almost positive this is a dupe. In any event the change looks plausible, it needs a test though.

Changed 4 years ago by PhiR_42

regression test

comment:4 Changed 4 years ago by PhiR_42

  • Needs tests unset

I did give a pretty good look at the DB tickets and couldn't find it, though there are some pretty similar ones (#12823 and #13937 for example).

comment:5 Changed 3 years ago by gvangool

  • Patch needs improvement set

Can you move it into a single patch file?

comment:6 Changed 3 years ago by Alex

  • Resolution set to fixed
  • Status changed from new to closed

(In [14600]) Fixed #14423 -- corrected incorrect SQL being generated when a nullable, inherited field was used in an exclude. Thanks to PhiR_42 for the patch.

comment:7 Changed 3 years ago by Alex

(In [14601]) [1.2.X] Fixed #14423 -- corrected incorrect SQL being generated when a nullable, inherited field was used in an exclude. Thanks to PhiR_42 for the patch. Backport of [14600].

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.