MySQL Bugs: #17048: CREATE TABLE ... SELECT truncate values

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:	Sergei Glukhov	CPU Architecture:	Any

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.

In this case, it creates unsigned decimal(3,1) by default.

Brian, I believe this is a problem of the DECIMAL data type.
Reggie, would you please tak it over?

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

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

Fixed in 5.0.22

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

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.