Code

Opened 8 years ago

Closed 7 years ago

#1760 closed defect (fixed)

order_with_respect_to doesn't work with MySQL

Reported by: Christopher Lenz <cmlenz@…> Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: normal Keywords:
Cc: farcepest@…, simon@… Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Inserting/updating an inline-edited object that has order_with_respect_to enabled results in the following traceback:

Traceback (most recent call last):
[snip]
File "lib/django/django/db/models/base.py" in save
File "lib/django/django/db/backends/util.py" in execute
File "lib/django/django/db/backends/mysql/base.py" in execute
File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py" in execute
  137. self.errorhandler(self, exc, value)
File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py" in defaulterrorhandler
  33. raise errorclass, errorvalue

  OperationalError at /admin/products/productlocalization/4445/
  (1093, "You can't specify target table 'products_download' for update in FROM clause")

The SQL query causing this problem is:

  INSERT INTO `products_download` (`product_id`,`title`,`file`,`type`,`_order`)
  VALUES (%s,%s,%s,%s,(SELECT COUNT(*) FROM `products_download` WHERE `product_id` = %s))

Apparently this is due to a limitation of MySQL regarding subqueries, documented on the page describing the UPDATE statement:

Currently, you cannot update a table and select from the same table in a subquery.

Attachments (3)

mysql_order_wrt.diff (1.8 KB) - added by Christopher Lenz <cmlenz@…> 8 years ago.
Hack to workaround the problem
mysql_order_wrt_2.diff (1.3 KB) - added by James Turnbull <james@…> 7 years ago.
Patch file for SVN version, removes MYSQL check and applies to correct lines
mysql_order_wrt_3.diff (1.2 KB) - added by Simon Litchfield <simon@…> 7 years ago.
Patch against rev 6022 (uses new qn() function) Surely it's about time to commit this small patch? It's critical for MySQL users and been waiting over a year now

Download all attachments as: .zip

Change History (17)

Changed 8 years ago by Christopher Lenz <cmlenz@…>

Hack to workaround the problem

comment:1 Changed 8 years ago by Christopher Lenz <cmlenz@…>

I've attached a patch that works around the problem by moving the subselect into a separate statement performed before the INSERT. This is rather ugly, but maybe others are find this useful until we find a proper solution. (suggestions?)

comment:2 Changed 8 years ago by anonymous

Could someone (anyone) post as to what still needs to be done for this bug to get resolved?

comment:3 Changed 8 years ago by anonymous

  • Component changed from Core framework to Database wrapper
  • Summary changed from order_with_respect_to doesn't work with MySQL to [patch] order_with_respect_to doesn't work with MySQL

comment:4 Changed 8 years ago by Andy Dustman <farcepest@…>

  • Cc farcepest@… added

I would suggest having some flag on the connection or cursor, such as can_update_from_self_subquery, or something like that. Eventually I would expect that restriction to go away in a future version of MySQL. Or otherwise, get rid of the subquery version; does it actually make any sort of performance improvement on other platforms?

comment:5 Changed 7 years ago by SmileyChris

  • Patch needs improvement set
  • Triage Stage changed from Unreviewed to Accepted

Changed 7 years ago by James Turnbull <james@…>

Patch file for SVN version, removes MYSQL check and applies to correct lines

comment:6 Changed 7 years ago by James Turnbull <james@…>

I've created a new patch that removes the subselect entirely, and patches against more recent django versions.

After a bit of testing it turned out that order_with_respect_to was not the feature I expected it to be, but I thought someone else might appreciate the patch.

comment:7 Changed 7 years ago by Marc Fargas <telenieko@…>

  • Summary changed from [patch] order_with_respect_to doesn't work with MySQL to order_with_respect_to doesn't work with MySQL

The last patch in here removes the line "# TODO: This assumes the database supports subqueries." but, doesn't it still assume that the database supports subqueries?

comment:8 Changed 7 years ago by James Turnbull <django@…>

No, the subquery has been removed for all database backends. It first executes what was the subquery, and then uses that result in the main query. I suppose this could be ever so slightly slower on databases where subqueries are supported and can be optimised.

comment:9 Changed 7 years ago by django@…

I get the same error message on the latest svn. Has the patch not been applied to the svn?

To wit:
en@klepto:~/devel/spackle/spackle$ ./manage.py loaddata spackle1/fixtures/spackle1.json
Loading 'spackle1/fixtures/spackle1' fixtures...
Installing json fixture 'spackle1/fixtures/spackle1' from absolute path.
Problem installing fixture 'spackle1/fixtures/spackle1.json': (1093, "You can't specify target table 'spackle1_studentproject' for update in FROM clause")

This is with MySQL 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04.

comment:10 Changed 7 years ago by Simon G. <dev@…>

  • Triage Stage changed from Accepted to Ready for checkin

comment:11 Changed 7 years ago by django@…

Note that the patch now needs to be patched. The line numbers are off.
But, more significantly, you need to change the references to backend to connection.ops.

comment:12 Changed 7 years ago by anonymous

  • Cc simon@… added

Changed 7 years ago by Simon Litchfield <simon@…>

Patch against rev 6022 (uses new qn() function) Surely it's about time to commit this small patch? It's critical for MySQL users and been waiting over a year now

comment:13 Changed 7 years ago by anonymous

  • Patch needs improvement unset

comment:14 Changed 7 years ago by mtredinnick

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

(In [6187]) Fixed #1760 -- Unwound a subselect in an update for order_with_respect_to handling. Required for MySQL and doesn't hurt too much for other platforms. thanks, Christopher Lenz, James Turnbull and Simon Litchfield.

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.