Bug #5265 Using CAST to set/preserve data precision in CREATE as SELECT

• Submitted: 27 Aug 2004 20:17
• Modified: 23 Jan 2006 12:24
• Reporter: R Otillar
• Status: Closed
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: 4.1.3
• OS: Linux (Linux IA32)
• CPU Architecture: Any

[27 Aug 2004 20:17] R Otillar

Description:
There seems to be no way to use CAST in CREATE AS SELECT
to set the precision of a column, and precisions seem not preserved.
E.g.

create table someWidths
select a.start, a.end, (1 + a.end - a.start)  width
from myIntervals a

for myIntervals types 
start          int(10)
end           int(10)

gives someWidths types
start          int(10)
end           int(10)
width        bigint(17)
....
It would be useful if MySQL allowed
create table someWidths
as
select a.start, a.end, cast( (1 + a.end - a.start)  as int(10)) width
from myIntervals a
giving someWidths types
start          int(10)
end           int(10)
width        int(10)
(this is not a way-out-thre request; Oracle supports this type of conversion)

How to repeat:
See example above.

Suggested fix:
Expand CAST to cover the full breadth of data-type conversions MySQL already supports for comparing/converting datatypes.
Expand CAST so that the type of a column in a create-as-select table can be set to control precision.

[23 Jan 2006 12:24] Valeriy Kravchuk

There is a way to do what you want in MySQL already. Please, read the manul (http://dev.mysql.com/doc/refman/5.0/en/create-table.html). Look:

mysql> create table myIntervals (start int(10), end int(10));
Query OK, 0 rows affected (0.24 sec)

mysql> create table someWidths (start int(10), end int(10), width int(10))
    -> select a.start, a.end, (1 + a.end - a.start)  width
    -> from myIntervals a;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc someWidths;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| start | int(10) | YES  |     | NULL    |       |
| end   | int(10) | YES  |     | NULL    |       |
| width | int(10) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)