MySQL Bugs: #27129: Error converting VARCHAR to INT in INSERT INTO SELECT query

Bug #27129	Error converting VARCHAR to INT in INSERT INTO SELECT query
Submitted:	14 Mar 2007 13:00	Modified:	14 Apr 2007 13:15
Reporter:	Harald Groven	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.19	OS:	Windows (win/osx)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
I have a table ("oldtable") with a ID field that unfortunately is stored by using VARCHAR. The ID column is only containing numeric data. I want to transfer it a table where the ID column is an INT instead of VARCHAR. 

I copied the structure of the "oldtable" to "newtable" and changed the ID colunm to INT(17). I then tried to populate the table with data from the "oldtabel" using an INSERT INTO SELECT. But the type conversion goes wrong! Only one record is copied, and the rest are ignored bqz of duplicate keys. 

INSERT IGNORE INTO newtable (id, content) 
SELECT CAST(id AS UNSIGNED INT), content
FROM oldtable 

If I do the same steps but remove the UNIQUE constraint the ID field of "newtable", the records are imported, but all IDs are set to '4294967295'

How to repeat:

CREATE TABLE `oldtable` (
  `id` varchar(20) collate latin1_general_ci NOT NULL default '',
  `content` text collate latin1_general_ci,
  PRIMARY KEY  (`id`),
  KEY `sid` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO `oldtable` VALUES ('20070101000000000', 'test1');
INSERT INTO `oldtable` VALUES ('20070101000000001', 'test2');
INSERT INTO `oldtable` VALUES ('20070101000000002', 'test3');

CREATE TABLE `newtable` (
  `id` int(17) unsigned NOT NULL auto_increment,
  `content` text collate latin1_general_ci,
  PRIMARY KEY  (`id`),
  KEY `sid` (`id`)
) ;

INSERT IGNORE INTO newtable (id, content) 
SELECT CAST(id AS UNSIGNED INT), content
FROM oldtable ; 

-- inserted rows 1! should have been 3!

SELECT * FROM newtable; 

id content
4294967295 test1

Thank you for the bug report. Please see the warnings (show warnings)
after the insert command. I changed column definition from int to
bigint. Thanks in advance.

mysql> use test
Database changed
mysql> CREATE TABLE `oldtable` (
    ->   `id` varchar(20) collate latin1_general_ci NOT NULL default '',
    ->   `content` text collate latin1_general_ci,
    ->   PRIMARY KEY  (`id`),
    ->   KEY `sid` (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> INSERT INTO `oldtable` VALUES ('20070101000000000', 'test1');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `oldtable` VALUES ('20070101000000001', 'test2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `oldtable` VALUES ('20070101000000002', 'test3');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> CREATE TABLE `newtable` (
    ->   `id` bigint unsigned NOT NULL auto_increment,
    ->   `content` text collate latin1_general_ci,
    ->   PRIMARY KEY  (`id`),
    ->   KEY `sid` (`id`)
    -> ) ;
Query OK, 0 rows affected (0.10 sec)

mysql>
mysql> INSERT IGNORE INTO newtable (id, content)
    -> SELECT CAST(id AS UNSIGNED INT), content
    -> FROM oldtable ;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM newtable;
+-------------------+---------+
| id                | content |
+-------------------+---------+
| 20070101000000000 | test1   |
| 20070101000000001 | test2   |
| 20070101000000002 | test3   |
+-------------------+---------+
3 rows in set (0.00 sec)

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.37-community-nt |
+---------------------+
1 row in set (0.00 sec)

mysql>

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".