| Bug #108407 | UNIX_TIMESTAMP() returns a DECIMAL when argument does not include a fsp | ||
|---|---|---|---|
| Submitted: | 6 Sep 2022 19:20 | Modified: | 7 Sep 2022 10:10 |
| Reporter: | William Chiquito | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.30 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | date_format, unix_timestamp | ||
[7 Sep 2022 6:04]
MySQL Verification Team
Hello William Chiquito, Thank you for the report and test case. regards, Umesh
[7 Sep 2022 8:46]
Roy Lyseng
Posted by developer: This is not a bug. In order to determine the exact number of decimals (zero in case of an integer), we need the expression to be consisting of only literal values. In this case, it is only cases f and g that qualifies for that. Cases d, e and h contains expressions that may vary at execution time and hence are assigned type DECIMAL and with 6 decimals. It is indeed the UNIX_TIMESTAMP function that returns the numeric type. DATE_FORMAT always returns a character string. If you really need the result to be an integer, you can add a CAST(<expression> AS SIGNED).
[7 Sep 2022 9:46]
William Chiquito
Has that behavior changed from version 8.0.x? because in 5.7.x the behavior is as expected (according to the documentation). Perhaps the documentation for version 8.0.x should be more explicit and include the explanation of these cases. Thanks.
[7 Sep 2022 10:10]
William Chiquito
However, it is confusing because in all cases (`d`, `e`, `f`, `g` and `h`) the UNIX_TIMESTAMP function receives as an argument the same character string returned by the DATE_FORMAT function. Thanks.
[7 Sep 2022 17:48]
Roy Lyseng
It changed in 8.0.22. Before that, types could be determined more specifically based on literal values, user variables and parameter values. Starting with 8.0.22, such specific typing is only possible for literal values. The reason was to get more predictable types for prepared statements.
Description: MySQL 8.0 Reference Manual: "The return value is an integer if no argument is given or the argument does not include a fractional seconds part, or DECIMAL if an argument is given that includes a fractional seconds part.". Why are columns `d`, `e` and `h` DECIMAL? Does the data type of the argument influence (only literal string returns the expected value)? Which function converts to DECIMAL: DATE_FORMAT or UNIX_TIMESTAMP?. How to repeat: mysql> DROP TABLE IF EXISTS `tbl_test`; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS `tbl_test` ( -> `datetime` DATETIME NOT NULL, -> `date` DATE NOT NULL, -> `time` TIME NOT NULL -> ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO `tbl_test` ( -> `datetime`, -> `date`, -> `time` -> ) VALUES ( -> '2020-12-20 16:42:27', -> '2020-12-20', -> '16:42:27' -> ); Query OK, 1 row affected (0.00 sec) mysql> SET @`datetime` := '2020-12-20 16:42:27'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT -> `datetime`, -> DATE_FORMAT(`datetime`, '%Y-%m-%d %H') `a`, -> DATE_FORMAT(@`datetime`, '%Y-%m-%d %H') `b`, -> DATE_FORMAT('2020-12-20 16:42:27', '%Y-%m-%d %H') `c`, -> UNIX_TIMESTAMP(DATE_FORMAT(`datetime`, '%Y-%m-%d %H')) `d`, -> UNIX_TIMESTAMP(DATE_FORMAT(@`datetime`, '%Y-%m-%d %H')) `e`, -> UNIX_TIMESTAMP(DATE_FORMAT('2020-12-20 16:42:27', '%Y-%m-%d %H')) `f`, -> UNIX_TIMESTAMP(DATE_FORMAT(CONCAT('2020-12-20', SPACE(1), '16:42:27'), '%Y-%m-%d %H')) `g`, -> UNIX_TIMESTAMP(DATE_FORMAT(CONCAT(`date`, SPACE(1), `time`), '%Y-%m-%d %H')) `h` -> FROM -> `tbl_test`\G Field 1: `datetime` Catalog: `def` Database: `test` Table: `tbl_test` Org_table: `tbl_test` Type: DATETIME Collation: binary (63) Length: 19 Max_length: 19 Decimals: 0 Flags: NOT_NULL BINARY NO_DEFAULT_VALUE Field 2: `a` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: utf8mb4_0900_ai_ci (255) Length: 72 Max_length: 13 Decimals: 31 Flags: Field 3: `b` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: utf8mb4_0900_ai_ci (255) Length: 72 Max_length: 13 Decimals: 31 Flags: Field 4: `c` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: utf8mb4_0900_ai_ci (255) Length: 72 Max_length: 13 Decimals: 31 Flags: Field 5: `d` Catalog: `def` Database: `` Table: `` Org_table: `` Type: NEWDECIMAL Collation: binary (63) Length: 19 Max_length: 17 Decimals: 6 Flags: BINARY NUM Field 6: `e` Catalog: `def` Database: `` Table: `` Org_table: `` Type: NEWDECIMAL Collation: binary (63) Length: 19 Max_length: 17 Decimals: 6 Flags: BINARY NUM Field 7: `f` Catalog: `def` Database: `` Table: `` Org_table: `` Type: LONGLONG Collation: binary (63) Length: 21 Max_length: 10 Decimals: 0 Flags: BINARY NUM Field 8: `g` Catalog: `def` Database: `` Table: `` Org_table: `` Type: LONGLONG Collation: binary (63) Length: 21 Max_length: 10 Decimals: 0 Flags: BINARY NUM Field 9: `h` Catalog: `def` Database: `` Table: `` Org_table: `` Type: NEWDECIMAL Collation: binary (63) Length: 19 Max_length: 17 Decimals: 6 Flags: BINARY NUM *************************** 1. row *************************** datetime: 2020-12-20 16:42:27 a: 2020-12-20 16 b: 2020-12-20 16 c: 2020-12-20 16 d: 1608480000.000000 e: 1608480000.000000 f: 1608480000 g: 1608480000 h: 1608480000.000000 1 row in set (0.00 sec) See dbfiddle (MySQL 8.0) -> https://dbfiddle.uk/tvpKiWHC See dbfiddle (MySQL 5.7) -> https://dbfiddle.uk/OiUJjqID (Everything works as expected).