MySQL Forums
Forum List  »  Quality Assurance

REPLACE INTO fails when data to be truncated is in the first row
Posted by: cresley battlelite
Date: March 25, 2013 01:01AM

REPLACE 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.

Tested on 5.6 GA, Linux
Tested on 5.5.27, Windows

(Should I raise a bug report?)


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 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 ;

Options: ReplyQuote


Subject
Views
Written By
Posted
REPLACE INTO fails when data to be truncated is in the first row
3093
March 25, 2013 01:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.