Code

Opened 2 years ago

Last modified 14 months ago

#17695 new Bug

model grandchild joins to top instead of the middle first

Reported by: phowe Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4-alpha-1
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I create a blank app with these models:

class Base( models.Model ):
  b_id = models.AutoField( primary_key=True )
  b_desc = models.CharField( max_length=100 )

class Middle( Base ):
  m_id = models.AutoField( primary_key=True )
  m_desc = models.CharField( max_length=100 )

class Top( Middle ):
  t_id = models.AutoField( primary_key=True )
  t_desc = models.CharField( max_length=100 )

I run this:

d = Top.objects.all()
print d.query
SELECT "example2_base"."b_id", "example2_base"."b_desc", "example2_middle"."base_ptr_id", "example2_middle"."m_id", "example2_middle"."m_desc", "example2_top"."middle_ptr_id", "example2_top"."t_id", "example2_top"."t_desc"
  FROM "example2_top"
  INNER JOIN "example2_base" ON ("example2_top"."middle_ptr_id" = "example2_base"."b_id")
  INNER JOIN "example2_middle" ON ("example2_top"."middle_ptr_id" = "example2_middle"."m_id")

The INNER JOIN links the Base model to the Top, I would expect the joins to look like this:

 INNER JOIN "example2_middle" ON ("example2_top"."middle_ptr_id" = "example2_middle"."m_id")
 INNER JOIN "example2_base" ON ("example2_middle"."base_ptr_id" = "example2_base"."b_id")


As long as the id's in the 3 tables stay in sync, the issue is not noticed, however if one of the id's gets incremented a head of one of the others, the query returns the wrong base parts of the model.

I found this while running 1.2, I also downloaded the 1.4 alpha and noticed the same results.

Attachments (0)

Change History (4)

comment:1 Changed 2 years ago by akaariai

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

I can see your point here. There is no guarantee that b_id == m_id which is assumed by the generated code. The bug is not present if you don't have separate primary keys for each of the models. In general I would suggest not defining the additional primary keys, I can't see them serving any purpose.

I am marking this as accepted, but I don't think this will be high on the list of things to fix, just because your use case (each model having a new primary key) isn't that common.

comment:2 Changed 2 years ago by phowe

Thanks for the pointer, I should be able to adapt my code in that direction.

Just an FYI I have noticed if I take all of the *_id fields, I this for the joins:

INNER JOIN "example3_base" ON ("example3_top"."middle_ptr_id" = "example3_base"."id")
INNER JOIN "example3_middle" ON ("example3_top"."middle_ptr_id" = "example3_middle"."base_ptr_id")

I guessing that the middle_ptr_id field in the top table should have been created as base_ptr_id (there is no middle id any more).

comment:3 Changed 20 months ago by phowe

fixed by #18174

comment:4 Changed 14 months ago by akaariai

  • Type changed from Uncategorized to Bug

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from nobody to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


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

 
Note: See TracTickets for help on using tickets.