Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 10.11.19, 11.4.13, 11.8.9, 12.3.3
Description
MariaDB accepts a fractional-seconds precision greater than 6 in a column's DEFAULT NOW(p) and ON UPDATE NOW(p) clauses. The precision is silently replaced by the column's precision instead of being rejected with ER_TOO_BIG_PRECISION (1426), even though SELECT NOW(7) is rejected.
In addition, the ON UPDATE precision is truncated to 16 bits, so its result depends on the value modulo 65536 (the same wrap-around as MDEV-41968).
Reproduction
MariaDB [test]> SELECT NOW(7);
|
ERROR 1426 (42000): Too big precision specified for 'current_timestamp'. Maximum is 6
|
MariaDB [test]> CREATE TABLE a1 (a TIMESTAMP(6) DEFAULT NOW(7));
|
Query OK, 0 rows affected (0.010 sec)
|
|
|
MariaDB [test]> SHOW CREATE TABLE a1;
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
|
| a1 | CREATE TABLE `a1` (
|
`a` timestamp(6) NULL DEFAULT current_timestamp(6)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> CREATE TABLE a2 (a DATETIME(6) DEFAULT NOW(65536));
|
Query OK, 0 rows affected (0.011 sec)
|
|
|
MariaDB [test]> SHOW CREATE TABLE a2;
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
|
| a2 | CREATE TABLE `a2` (
|
`a` datetime(6) DEFAULT current_timestamp(6)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> CREATE TABLE a3 (a TIMESTAMP(6) ON UPDATE NOW(7));
|
Query OK, 0 rows affected (0.009 sec)
|
|
|
MariaDB [test]> SHOW CREATE TABLE a3;
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| a3 | CREATE TABLE `a3` (
|
`a` timestamp(6) NULL DEFAULT NULL ON UPDATE current_timestamp(6)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> CREATE TABLE a4 (a TIMESTAMP(6) ON UPDATE NOW(65542));
|
Query OK, 0 rows affected (0.011 sec)
|
|
|
MariaDB [test]> SHOW CREATE TABLE a4;
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| a4 | CREATE TABLE `a4` (
|
`a` timestamp(6) NULL DEFAULT NULL ON UPDATE current_timestamp(6)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> CREATE TABLE a5 (a TIMESTAMP(6) ON UPDATE NOW(65536));
|
ERROR 1294 (HY000): Invalid ON UPDATE clause for 'a' column
|
MariaDB [test]> DROP TABLE IF EXISTS a1, a2, a3, a4, a5;
|
Query OK, 0 rows affected, 1 warning (0.024 sec)
|
- a1, a2, a3: a precision greater than 6 is accepted and replaced by 6.
- a4: 65542 wraps to 6 and is accepted.
- a5: 65536 wraps to 0, which is smaller than the column precision, so the statement fails with a misleading ER_INVALID_ON_UPDATE (1294) instead of ER_TOO_BIG_PRECISION.
Expected result
Every CREATE TABLE above should fail with ER_TOO_BIG_PRECISION (1426), the same as SELECT NOW(7) and CREATE TABLE t (a DATETIME(7)).
Reference: https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/microseconds-in-mariadb
Attachments
Issue Links
- relates to
-
-
- In Review
-