MySQL Bugs: #68769: REPLACE/INSERT INTO fails for MyISAM if truncated is in the first row

Bug #68769	REPLACE/INSERT INTO fails for MyISAM if truncated is in the first row
Submitted:	25 Mar 2013 22:42	Modified:	26 Mar 2013 3:37
Reporter:	cresley battlelite	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.5 or greater	OS:	Any (Linux and Windows)
Assigned to:		CPU Architecture:	Any
Tags:	insert, REPLACE, truncation

Description:
REPLACE/INSERT INTO will truncate data if it is too big for the datatype, and this is correct behaviour, it will then insert the rows into the table.

If, however, the first row requires the data truncation all rows will fail to insert.

Incorrect may be a bit harsh, but inconsistent is fitting.

Tested on 5.6 GA, Linux
Tested on 5.5.27, Windows

How to repeat:
drop table myTable;

-- create table myTable

CREATE TABLE myTable (
  `ID` int(11) NOT NULL ,
  `parentID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM ;

-- CORRECT BEHAVIOUR

replace into myTable (ID, parentID) 
values 
( 1, 2),
( 3, -1875802725),
( 4, 5);

-- returns 3 rows, with -1875802725 truncated to 0.
select * from myTable ;

-- clear the table 
delete from myTable;

insert into myTable (ID, parentID) 
values 
( 1, 2),
( 3, -1875802725),
( 4, 5);

-- returns 3 rows, with -1875802725 truncated to 0.
select * from myTable ;

-- clear the table for the second part of the test (use this or re-create the table if you deem this necessary)
delete from myTable;

-- INCORRECT BEHAVIOUR, BELOW

-- change order of inserts such that truncated data is in first row 
replace into myTable (ID, parentID) 
values 
( 3, -1875802725),
( 1, 2),
( 4, 5);

-- incorrectly returns 0 rows
select * from myTable ;

insert into myTable (ID, parentID) 
values 
( 3, -1875802725),
( 1, 2),
( 4, 5);

-- incorrectly returns 0 rows
select * from myTable ;

Forgot to add INSERT in the tag, as I initially found this via REPLACE. Peter Brawley pointed out that INSERT fails in the same way.

OK, not a bug, just a conflagration of settings need to be harmonised.

see Mark Matthews comment on 
http://bugs.mysql.com/bug.php?id=23371

basically your session settings will determine the behaviour.

See last comment