Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
Description
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.
Attachments
Issue Links
- blocks
-
MDEV-4912 Data type plugin API version 1
-
- Closed
-
- relates to
-
MDEV-12770 Add Type_handler::decimal_precision()
-
- Closed
-