MySQL Bugs: #17051: CREATE TABLE ... SELECT value overflow

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:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.19	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
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))
111111.00

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;
Warnings:
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))
99999.99

mysql> drop table t1;

Suggested fix:
Make the created table have a field type large enough so that the value will fit.

This can also been seen when executing the query as a server side cursor.

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

Kostja believe the bug to be in the decimal type.

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

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