|
CREATE OR REPLACE TABLE t1 AS SELECT
|
TIMESTAMP'2004-01-01 10:20:30.123'+0 AS c1,
|
TIME'10:20:30.123'+0 AS c2,
|
DATE'2001-01-01'+0 AS c3;
|
SHOW CREATE TABLE t1;
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`c1` decimal(24,3) NOT NULL DEFAULT '0.000',
|
`c2` decimal(15,3) NOT NULL DEFAULT '0.000',
|
`c3` bigint(12) NOT NULL DEFAULT '0'
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
The above types are excessive. This happens because Item::decimal_precision() treats temporal types as usual strings and uses max_length to calculate precision. This is wrong, because it does not exclude the formatting characters (dashes and semicolons).
The correct integer part precision would be:
- 14 for DATETIME and TIMESTAMP (YYYYMMDDhhmmss)
- 7 for TIME (hhhmmss)
- 8 for DATE (YYYYMMDD)
Plus adds one digit to the integer part.
The expected result for the above query would be:
- 15 integer part digits for c1, which gives decimal(18,3)
- 8 integer part digits for c2, which gives decimal(11,3)
- 9 integer part digits for c3, which should give int(9), but DATE expression also do not set unsigned_flag properly, so int(10) would be ok until the unsigned_flag is fixed.
|