MySQL Bugs: #27358: INSERT DELAYED does not honour SQL

Bug #27358	INSERT DELAYED does not honour SQL_MODE of the client
Submitted:	21 Mar 2007 20:18	Modified:	23 Oct 2007 19:48
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0-bk	OS:	Linux (linux)
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any

Description:
If a client does
SET SESSION SQL_MODE=some_mode;
INSERT DELAYED something;
then the "something" is queued into the queue of the delayed system thread, without the "some_mode" information.
So if the interpretation of "something" depends on "some_mode", INSERT DELAYED will not insert what was intended.
Example:
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
CREATE TABLE `t1` (
  `id` int(11) PRIMARY KEY auto_increment,
  `f1` varchar(10) NOT NULL UNIQUE
);
insert delayed into t1 values(0,"test1");

How to repeat:
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
CREATE TABLE `t1` (
  `id` int(11) PRIMARY KEY auto_increment,
  `f1` varchar(10) NOT NULL UNIQUE
);
insert delayed into t1 values(0,"test1");
sleep 1;
SELECT * FROM t1;
In t1 I see:
1       test1
i.e. the sql_mode value was not honoured.
While if I replace INSERT DELAYED by a plain INSERT, in t1 I see
0       test1
as expected.
Can happen if you have a MyISAM table with an autoincrement column, containing (0,"test1") and you dump it with
mysqldump --delayed-insert
then the dump will look like my example above and so will insert (1,"test1") if executed.
Other example:
set sql_mode=pipes_as_concat;
insert delayed into t1 values(0, "a" || "b");

Suggested fix:
long fix: add "sql_mode" to the delayed_row class, save it when the row is queued, use it when the row is actually inserted later.
short fix: document the problem.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32929

ChangeSet@1.2512, 2007-08-23 10:22:20+02:00, thek@adventure.(none) +3 -0
  Bug#27358 INSERT DELAYED does not honour SQL_MODE of the client
  
  SQL_MODE was ignored when a client issued INSERT DELAYED.
  
  Some system settings weren't copied as intended when a record was saved for
  a delayed insert.

Pushed into 5.1.23-beta

Noted in 5.1.23 changelog.