Bug #17051 CREATE TABLE ... SELECT value overflow
Submitted: 2 Feb 2006 16:00 Modified: 13 Apr 2006 0:58
Reporter: Magnus Blåudd Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.19 OS:Linux (Linux)
Assigned to: Assigned Account CPU Architecture:Any

[2 Feb 2006 16:00] Magnus Blåudd
When creating a table from a select differs from the output generated by the output generated from the select query itself. As too small field type is selected in the new table.

How to repeat:
mysql> create table t1(s1 time);
mysql> insert into t1 values ('11:11:11');
mysql> select cast(s1 as decimal(7,2)) from t1;
cast(s1 as decimal(7,2))

Here we get the value 111111.00 but when the same query is used to create a table we get a truncated result.

mysql: create table t2 as select cast(s1 as decimal(7,2)) from t1;
Warning       1264    Out of range value adjusted for column 'cast(   s1 as decimal(7,2))' at row 1
mysql: select * from t2;
cast( s1 as decimal(7,2))

mysql> drop table t1;

Suggested fix:
Make the created table have a field type large enough so that the value will fit.
[2 Feb 2006 16:01] Magnus Blåudd
This can also been seen when executing the query as a server side cursor.
[3 Feb 2006 17:28] Calvin Sun
In this case, it creates unsigned decimal(3,1) by default.
[11 Feb 2006 21:41] Brian Aker
Kostja believe the bug to be in the decimal type.
[27 Mar 2006 15:07] Konstantin Osipov
Brian, I believe this is a problem of the DECIMAL data type. 
Reggie, Holyfoot: would you please take over?
[13 Apr 2006 0:58] Jim Winstead
This is a duplicate of Bug #16172. The cast of a TIME of '11:11:11' to a DECIMAL(7,2) should be an overflow, since 111111.00 really only fits in a DECIMAL(8,2).