Bug #511 INSERT as SELECT from the same table must be unlocked
Submitted: 26 May 2003 13:48 Modified: 31 May 2003 16:53
Reporter: Oleg Ivanov Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:4.X OS:Any (ALL)
Assigned to: CPU Architecture:Any

[26 May 2003 13:48] Oleg Ivanov
Description:
In 6.4.3.1 item of documentation there are a statement "it's forbidden in standard SQL to SELECT from the same table into which you are inserting".

It is not correct statement - there is no discrepancies: SELECT just must complete before INSERT begin.
This feature implemented in Oracle database without any error.

Locking insert from the same table force developers to write errorneouse or slow code. For example:
===
@VariableOnClientSide ={SELECT MAX(N) FROM tab};
INSERT INTO tab values (@VariableOnClientSide)
===
much more dangerous than some discrepancies wich may arise if this feature will be unlocked. Because of significant network delay between execution of commands above in comparision with server-side insert-as-select.

Table locking is not possible solution at all due to significant performance degradation.

How to repeat:
"INSERT INTO tab VALUES (1, (SELECT MAX(N) FROM tab))"
and
"INSERT INTO tab SELECT 1, MAX(N) FROM tab"
works correctly in Oracle databse and not on MySQL.

Suggested fix:
remove code checking insert from the same table.
Point dangerouse of useing this feature in documentation.
Single row insert (INSERT INTO tab SELECT 1, MAX(N) FROM tab) 100% will have no error.
[31 May 2003 16:53] MySQL Verification Team
This is the subject of bug #401.