MySQL Bugs: #25065: INSERT DELAYED ... SELECT ... does not give an error on InnoDB

Bug #25065	INSERT DELAYED ... SELECT ... does not give an error on InnoDB
Submitted:	14 Dec 2006 11:22	Modified:	14 Dec 2006 13:46
Reporter:	Gianluca Gimigliano	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.27	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any
Tags:	DELAYED, innodb, insert, MySQL

Description:
The "insert delayed" option is not available on innodb tables,
but i found an insert usage that doesn't give error.
Is it a real delayed insert or the error massage is not displayed for a bug?

How to repeat:
CREATE TABLE `test` (  `field` int(11) NOT NULL) ENGINE=InnoDB;

insert delayed into test values(1); 
/* naturally this insert is not allowed ...*/

insert delayed into test select 1; 
/* .. but this is performed; in which modality? */

Gianluca,

there should not be any DELAYED insert to InnoDB tables.

The DELAYED option was introduced for MyISAM to relieve the table locking problem.

An error message should be returned.

Regards,

Heikki

It's just that if there is a SELECT clause, INSERT DELAYED is converted to INSERT:
http://dev.mysql.com/doc/refman/5.0/en/insert-delayed.html
"INSERT DELAYED should be used only for INSERT statements that specify value lists. The server ignores DELAYED for INSERT ... SELECT or INSERT ... ON DUPLICATE KEY UPDATE statements."
And, in this page
http://dev.mysql.com/doc/refman/5.0/en/insert.html
we see that DELAYED is not present in the INSERT SELECT syntax.
DELAYED *used* to be effective in INSERT SELECT, but it had logical problems (regarding replication for example), but it was changed (two years ago maybe).

So, currently, the MySQL layer above storage engines converts INSERT DELAYED SELECT to INSERT SELECT, thus no error from InnoDB.