order_with_respect_to doesn't work with MySQL

[Christopher Lenz <cmlenz@…>]

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.

[Christopher Lenz <cmlenz@…>]

Hack to workaround the problem

[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?)

[anonymous]

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

[Andy Dustman <farcepest@…>]

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?

[James Turnbull <james@…>]

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

[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.

[Marc Fargas <telenieko@…>]

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?

[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.

[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.

[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.

[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

[Malcolm Tredinnick]

(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.