MySQL Bugs: #3951: Can't perform a simple update with subquery

Bug #3951	Can't perform a simple update with subquery
Submitted:	1 Jun 2004 17:27	Modified:	7 Jun 2004 8:47
Reporter:	Azza Azza69	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.1.	OS:	Windows (Win2k)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
INSERT INTO frontpageh
SET Position=(SELECT MAX(Position)+1 FROM frontpageh)

gives the error:
"You can't specify target table 'frontpageh' for update in FROM clause"

and I don't understand why?  With such a simple query.  I have also tried to alias the 2nd reference to frontpageh but it still complains.  Have I completely lost the plot or is this not such a simple thing to ask the SQL server?

NB: Closest pre-reported bug incident I could find was Bug #39, but this was closed with no fix or resolution.

How to repeat:
CREATE TABLE frontpageh (
  UID int(11) unsigned NOT NULL auto_increment,
  Position tinyint(3) unsigned default NULL,
  Title varchar(50) default NULL,
  PRIMARY KEY  (UID),
  UNIQUE KEY ixPosition (Position)
) TYPE=MyISAM DEFAULT CHARSET=latin1

INSERT INTO frontpageh
SET Position=(SELECT MAX(Position)+1 FROM frontpageh)

Suggested fix:
I suppose the only thing I can do is perform the select, get the value and then execute another statement to perform the insert, but I was hoping to perform it all in one to minimize clashes.

I verified this using 4.1.2 on Red Hat 9 2.4.21 #12 SMP

Thank you for bugreport, but it is current limitation of implementation. 
 
Subquery executes on demand, in case of several values for inserting we can't 
garanty that subquery will be executed on table as it was before comand 
started as standard require.