| Bug #65756 | Aliasing same TEMPORARY table in query causes 'ERROR 1137: Can't reopen | ||
|---|---|---|---|
| Submitted: | 27 Jun 2012 15:53 | Modified: | 27 Jul 2012 16:41 |
| Reporter: | Mark Rouse | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Workbench: SQL Editor | Severity: | S4 (Feature request) |
| Version: | 5.2.40 | OS: | Windows (Windows XP 32-bit) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | performance, self referencing, temporary tables | ||
[27 Jun 2012 15:54]
Mark Rouse
Uopdated Windows Version.
[27 Jun 2012 16:41]
Valeriy Kravchuk
Does the same error happens with normal, not temporary table? I think this is the case described at http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html: "In general, you cannot modify a table and select from the same table in a subquery."
[28 Jul 2012 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: UPDATE tmp1 t1 JOIN (SELECT Value_date,Currency, @s:=IF(Running_Balance IS NULL, @s, Running_Balance) Running_Balance FROM tmp1, (SELECT @s:=NULL) t) t2 ON t1.Value_date=t2.Value_Date and t1.Currency=t2.Currency SET t1.Running_Balance=t2.Running_Balance+t2.Movement_Amount where Currency = 'XYX'; tmp1 is a temporary table. Can't reference a temporary table to itself. How to repeat: Reference a temporary table to itself.