Description:
An INSERT query fails when a table includes a virtual column that references a datetime column with DEFAULT CURRENT_TIMESTAMP configuration.
Table structure:
CREATE TABLE `Test` (
`Test` int(11) NOT NULL,
`dateCreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`dateCreatedAsDate` date GENERATED ALWAYS AS (DATE(`dateCreated`)) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;
mysql [localhost:8039] {msandbox} (test) > INSERT INTO `Test` (`Test`) VALUES (1);
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'dateCreatedAsDate' at row 1
The same insert works without the virtual column:
CREATE TABLE `Test2` (
`Test` int(11) NOT NULL,
`dateCreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;
mysql [localhost:8039] {msandbox} (test) > INSERT INTO `Test2` (`Test`) VALUES (1);
Query OK, 1 row affected (0.01 sec)
mysql [localhost:8039] {msandbox} (test) > select * from Test2;
+------+---------------------+
| Test | dateCreated |
+------+---------------------+
| 1 | 2024-10-10 18:49:44 |
+------+---------------------+
1 row in set (0.00 sec)
Expected behaviour:
The first insert into the Test table should be successful, similar to the successful insert into the Test2 table, without generating an error.
How to repeat:
- Setup MySQL 8.0.39 server
mysql [localhost:8039] {msandbox} (test) > select @@version,@@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment |
+-----------+------------------------------+
| 8.0.39 | MySQL Community Server - GPL |
+-----------+------------------------------+
1 row in set (0.00 sec)
- Use default sql_mode
mysql [localhost:8039] {msandbox} (test) > select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)
- Create a table with one `DATETIME` column that defaults to `CURRENT_TIMESTAMP` and one virtual `DATE` column derived from the `DATETIME` column.
CREATE TABLE `Test` (
`Test` int(11) NOT NULL,
`dateCreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`dateCreatedAsDate` date GENERATED ALWAYS AS (DATE(`dateCreated`)) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;
- Insert a row without specifying an explicit value for the `DATETIME` column, as it is set to default to `CURRENT_TIMESTAMP`.
INSERT INTO `Test` (`Test`) VALUES (1);
- It will fail with error
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'dateCreatedAsDate' at row 1
Description: An INSERT query fails when a table includes a virtual column that references a datetime column with DEFAULT CURRENT_TIMESTAMP configuration. Table structure: CREATE TABLE `Test` ( `Test` int(11) NOT NULL, `dateCreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `dateCreatedAsDate` date GENERATED ALWAYS AS (DATE(`dateCreated`)) VIRTUAL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED; mysql [localhost:8039] {msandbox} (test) > INSERT INTO `Test` (`Test`) VALUES (1); ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'dateCreatedAsDate' at row 1 The same insert works without the virtual column: CREATE TABLE `Test2` ( `Test` int(11) NOT NULL, `dateCreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED; mysql [localhost:8039] {msandbox} (test) > INSERT INTO `Test2` (`Test`) VALUES (1); Query OK, 1 row affected (0.01 sec) mysql [localhost:8039] {msandbox} (test) > select * from Test2; +------+---------------------+ | Test | dateCreated | +------+---------------------+ | 1 | 2024-10-10 18:49:44 | +------+---------------------+ 1 row in set (0.00 sec) Expected behaviour: The first insert into the Test table should be successful, similar to the successful insert into the Test2 table, without generating an error. How to repeat: - Setup MySQL 8.0.39 server mysql [localhost:8039] {msandbox} (test) > select @@version,@@version_comment; +-----------+------------------------------+ | @@version | @@version_comment | +-----------+------------------------------+ | 8.0.39 | MySQL Community Server - GPL | +-----------+------------------------------+ 1 row in set (0.00 sec) - Use default sql_mode mysql [localhost:8039] {msandbox} (test) > select @@sql_mode\G *************************** 1. row *************************** @@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 1 row in set (0.00 sec) - Create a table with one `DATETIME` column that defaults to `CURRENT_TIMESTAMP` and one virtual `DATE` column derived from the `DATETIME` column. CREATE TABLE `Test` ( `Test` int(11) NOT NULL, `dateCreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `dateCreatedAsDate` date GENERATED ALWAYS AS (DATE(`dateCreated`)) VIRTUAL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED; - Insert a row without specifying an explicit value for the `DATETIME` column, as it is set to default to `CURRENT_TIMESTAMP`. INSERT INTO `Test` (`Test`) VALUES (1); - It will fail with error ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'dateCreatedAsDate' at row 1