Opened 6 years ago

Closed 6 years ago

#11437 closed (invalid)

[soc2009/multidb] M2M Relationship with through=[model] whose primary key is renamed fails with wrong column name

Reported by: anonymous Owned by: nobody
Component: Database layer (models, ORM) Version: soc2009/multidb
Severity: Keywords: multidb manytomany db_column
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Deleting an object that is involved in a many to many relationship using an intermediary model whose primary key is renamed at the db level fails because the generated SELECT statement is wrong.

In the example below, clicking on 'Delete' of an 'AdSalesAccount' results in "OperationalError: (1054, "Unknown column 'ad_order_listing_bridge.id' in 'field list'")"

Error occurs in django\db\models\sql\query.py in execute_sql on line 2370:

Traceback:
File "C:\Python25\lib\site-packages\django\core\handlers\base.py" in get_response
  87.                 response = middleware_method(request, callback, callback_args, callback_kwargs)
File "build\bdist.win32\egg\firepython\middleware.py" in process_view
  305.         return self._profile_wrap(callback)(*args, **callback_kwargs)
File "C:\Python25\lib\site-packages\django\contrib\admin\sites.py" in root
  480.                 return self.model_page(request, *url.split('/', 2))
File "C:\Python25\lib\site-packages\django\views\decorators\cache.py" in _wrapped_view_func
  44.         response = view_func(request, *args, **kwargs)
File "C:\Python25\lib\site-packages\django\contrib\admin\sites.py" in model_page
  499.         return admin_obj(request, rest_of_url)
File "C:\Python25\lib\site-packages\django\contrib\admin\options.py" in __call__
  1092.             return self.delete_view(request, unquote(url[:-7]))
File "C:\Python25\lib\site-packages\django\contrib\admin\options.py" in delete_view
  1009.         get_deleted_objects(deleted_objects, perms_needed, request.user, obj, opts, 1, self.admin_site)
File "C:\Python25\lib\site-packages\django\contrib\admin\util.py" in get_deleted_objects
  125.             for sub_obj in getattr(obj, rel_opts_name).all():
File "C:\Python25\lib\site-packages\django\db\models\query.py" in _result_iter
  110.                 self._fill_cache()
File "C:\Python25\lib\site-packages\django\db\models\query.py" in _fill_cache
  707.                     self._result_cache.append(self._iter.next())
File "C:\Python25\lib\site-packages\django\db\models\query.py" in iterator
  242.         for row in self.query.results_iter():
File "C:\Python25\lib\site-packages\django\db\models\sql\query.py" in results_iter
  287.         for rows in self.execute_sql(MULTI):
File "C:\Python25\lib\site-packages\django\db\models\sql\query.py" in execute_sql
  2370.         cursor.execute(sql, params)

The reason is invalid SQL is generated here:

sql = u'SELECT `ad_order_listing_bridge`.`id`, #####Should be ad_orderId
`ad_order_listing_bridge`.`ad_order_listing_bridgeId`, `ad_order_listing_bridge`.`ad_orderId`, `ad_order_listing_bridge`.`listingId` FROM `ad_order_listing_bridge` WHERE `ad_order_listing_bridge`.`ad_orderId` = %s '}}}

{{{
#!python
AdSalesModel = models.Model
class AdSalesAccount(AdSalesModel):
    id = models.AutoField(primary_key=True, db_column='accountId')
    class Meta:
        db_table = u'account'
        ordering = ('-date_created', 'name')
        using = 'adsales'

class AdSalesAdOrder(models.Model):
    id = models.AutoField(primary_key=True, db_column='ad_orderId')
    account = models.ForeignKey(AdSalesAccount, db_column='accountId')
    listings = models.ManyToManyField('AdSalesListing', through='AdSalesAdOrderListingBridge')
    class Meta:
        db_table = u'ad_order'
        ordering = ('-date_created', )
        using = 'adsales'

class AdSalesAdOrderListingBridge(models.Model):
    id = models.IntegerField(db_column='ad_order_listing_bridgeId')
    adorder = models.ForeignKey(AdSalesAdOrder, db_column='ad_orderId')
    listing = models.ForeignKey('AdSalesListing', db_column='listingId')
    class Meta:
        using = 'adsales'
        db_table = u'ad_order_listing_bridge'

class AdSalesListing(AdSalesModel):
    id = models.AutoField(primary_key=True, db_column='listingId')
    class Meta:
        db_table = u'listing'
        ordering = ("-date_created", "descriptive_line")
        using = 'adsales'
}}}

Attachments (1)

m2m-regress-tests.diff (2.0 KB) - added by Alex 6 years ago.
I'm unable to reproduce with these tests, can you let me know what else needs to be done to reproduce this issue?

Download all attachments as: .zip

Change History (5)

comment:1 Changed 6 years ago by Alex

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Version changed from SVN to soc2009/multidb

Changed 6 years ago by Alex

I'm unable to reproduce with these tests, can you let me know what else needs to be done to reproduce this issue?

comment:2 follow-up: Changed 6 years ago by anonymous

I'll try to reproduce it with the models you added (in the diff), but I can't try it out right now. A quick glance shows that you don't have the using='otherdb' in all of the meta classes, but I'm not sure if that's the reason I'm encountering this.

Further info: This occurs when the admin tries to get a list of objects to delete. That is, I go to edit a particular instance of the model (in your example, it'd be the Author model) to view the admin's object_detail page, and then click delete there, I get the OperationalError. The query happens when getting a list of objects to delete, but does not happen if the object i selected has no entries in the M2M bridge table.

Again, i'll check it out in a bit.

PS: Great work on the new multidb branch, almost all of it is working seamlessly.

comment:3 in reply to: ↑ 2 Changed 6 years ago by anonymous

Replying to anonymous:

I'll try to reproduce it with the models you added (in the diff), but I can't try it out right now. A quick glance shows that you don't have the using='otherdb' in all of the meta classes, but I'm not sure if that's the reason I'm encountering this.

A second glance also shows that the table names are not renamed with db_table. I'll check this out too.

comment:4 Changed 6 years ago by anonymous

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

I'm sorry, was my fault. I didn't run manage.py validate, which helpfully let me know:

Error: One or more models did not validate:
adsales.adsalesadorderlistingbridge: "id": You can't use "id" as a field name, because each model au
tomatically gets an "id" field if none of the fields have primary_key=True. You need to either remov
e/rename your "id" field or add primary_key=True to a field.

I had declared my model like this:

class AdSalesAdOrderListingBridge(models.Model):
    id = models.IntegerField(db_column='ad_order_listing_bridgeId')

And just forgot to add the keyword argument primary_key=True.

I can only assume that django would parse my model, not find a pk, and then overwrite my id field definition with the automatic id field (which of course wouldn't have db_column set, but would have primary_key set)

Thanks for looking at this, even though it was my fault :(

Perhaps a startup run-time model parsing exception (ImproperlyConfigured) should be generated if django is about to overwrite a field?

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