Django

Code

Ticket #1760 (closed: fixed)

Opened 2 years ago

Last modified 1 year ago

order_with_respect_to doesn't work with MySQL

Reported by: Christopher Lenz <cmlenz@gmx.de> Assigned to: nobody
Milestone: Component: Database wrapper
Version: SVN Keywords:
Cc: farcepest@gmail.com, simon@quo.com.au Triage Stage: Ready for checkin
Has patch: 1 Needs documentation: 0
Needs tests: 0 Patch needs improvement: 0

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

mysql_order_wrt.diff (1.8 kB) - added by Christopher Lenz <cmlenz@gmx.de> on 05/08/06 05:55:32.
Hack to workaround the problem
mysql_order_wrt_2.diff (1.3 kB) - added by James Turnbull <james@lewis.co.uk> on 04/04/07 04:42:50.
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@quo.com.au> on 08/29/07 07:16:06.
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

Change History

05/08/06 05:55:32 changed by Christopher Lenz <cmlenz@gmx.de>

  • attachment mysql_order_wrt.diff added.

Hack to workaround the problem

05/08/06 05:57:13 changed by Christopher Lenz <cmlenz@gmx.de>

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

09/04/06 14:02:45 changed by anonymous

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

09/05/06 20:34:45 changed 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.

11/01/06 20:14:24 changed by Andy Dustman <farcepest@gmail.com>

  • cc set to farcepest@gmail.com.

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?

01/22/07 19:57:22 changed by SmileyChris

  • needs_better_patch set to 1.
  • stage changed from Unreviewed to Accepted.

04/04/07 04:42:50 changed by James Turnbull <james@lewis.co.uk>

  • attachment mysql_order_wrt_2.diff added.

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

04/04/07 04:45:07 changed by James Turnbull <james@lewis.co.uk>

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.

05/19/07 19:34:30 changed by Marc Fargas <telenieko@telenieko.com>

  • 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?

05/23/07 09:01:28 changed by James Turnbull <django@jamesturnbull.org>

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.

07/15/07 00:03:25 changed by django@catchall.getdown.org

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.

07/17/07 18:18:20 changed by Simon G. <dev@simon.net.nz>

  • stage changed from Accepted to Ready for checkin.

08/27/07 11:45:58 changed by django@catchall.getdown.org

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.

08/29/07 07:04:58 changed by anonymous

  • cc changed from farcepest@gmail.com to farcepest@gmail.com, simon@quo.com.au.

08/29/07 07:16:06 changed by Simon Litchfield <simon@quo.com.au>

  • attachment mysql_order_wrt_3.diff added.

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

08/29/07 07:16:57 changed by anonymous

  • needs_better_patch deleted.

09/14/07 08:56:36 changed by mtredinnick

  • status changed from new to closed.
  • resolution set to fixed.

(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/Change #1760 (order_with_respect_to doesn't work with MySQL)




Change Properties
Action