Bug #17048 CREATE TABLE ... SELECT truncate values
Submitted: 2 Feb 2006 15:41 Modified: 15 May 2006 19:28
Reporter: Magnus Blåudd Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.19 OS:Linux (Linux)
Assigned to: Sergey Glukhov CPU Architecture:Any

[2 Feb 2006 15:41] Magnus Blåudd
Description:
When executing "select cast(5 as unsigned) -6.0;" as a normal query -1.0 is returned as expected.
mysql> select cast(5 as unsigned) - 6.0;
cast(5 as unsigned) -6.0
 -1.0

But when executing the same query to create a new table, the fields of that table is selected so that it's not allowed to insert negative values into the field. Thus the value is truncated.

mysql> create table t1 as select cast(5 as unsigned) - 6.0;
Warnings:
Warning       1264    Out of range value adjusted for column 'cast(5 as unsigned) -6.0' at row 1
mysql> select * from t1;
cast(5 as unsigned) -6.0
0.0

The same thing happens when executing a prepared statement as a server side cursor, since it will create a temporary table on the server to store the data for the cursor.

How to repeat:
See above.

Ex:
select cast(5 as unsigned) -6.0;

Suggested fix:
Create a field in the new table that allows a negative value to be inserted.
[3 Feb 2006 17:27] Calvin Sun
In this case, it creates unsigned decimal(3,1) by default.
[27 Mar 2006 15:06] Konstantin Osipov
Brian, I believe this is a problem of the DECIMAL data type.
Reggie, would you please tak it over?
[5 Apr 2006 13:07] Bugs System
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/4502
[12 May 2006 16:21] Bugs System
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/6312
[13 May 2006 8:10] Sergey Glukhov
Fixed in 5.0.22
[13 May 2006 9:27] Bugs System
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/6336
[15 May 2006 19:28] Paul Dubois
Noted in 5.0.22 changelog.

For certain <literal>CREATE TABLE ... SELECT</literal>
statements, the selected values were truncated when inserted
into the new table.