Details
-
Bug
-
Status: In Progress (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.6.2, 10.5, 10.6, 10.11, 11.4, 11.8
-
Windows 11 Terminal
Description
A CHECK constraint that compares a DATE column to an unquoted date-like expression such as 2020-01-01, MariaDB interprets it as an arithmetic operation instead of a date literal (i.e., 2020 - 1 - 1 = 2018). This causes an 'truncated incorrect datetime value' error when attempting to INSERT date values in string format. However, if the same INSERT statement is retried immediately with no changes, the insertion succeeds without error, despite the same incorrect CHECK definition being in place.
This behavior is inconsistent and may result in constraints being ignored or unpredictably enforced.
Example:
MariaDB [hw_6_7]> CREATE TABLE Passport (passport_number INTEGER, country VARCHAR(255), expirationDate DATE CHECK (expirationDate > 2020-01-01), holderName VARCHAR(255), PRIMARY KEY (passport_number, country));
|
Query OK, 0 rows affected (0.019 sec)
|
|
|
MariaDB [hw_6_7]> INSERT INTO Passport (passport_number, country, expirationDate, holderName) VALUES
|
-> (3001, 'USA', '2025-11-30', 'John Doe'),
|
-> (3002, 'Canada', '2026-08-20', 'Alice Brown'),
|
-> (3003, 'UK', '2024-09-15', 'Mike Johnson'),
|
-> (3004, 'Australia', '2027-02-10', 'Lisa Turner'),
|
-> (3005, 'France', '2023-12-05', 'Sarah Connor');
|
ERROR 1292 (22007): Truncated incorrect datetime value: '2018'
|
MariaDB [hw_6_7]> INSERT INTO Passport (passport_number, country, expirationDate, holderName) VALUES
|
-> (3001, 'USA', '2025-11-30', 'John Doe'),
|
-> (3002, 'Canada', '2026-08-20', 'Alice Brown'),
|
-> (3003, 'UK', '2024-09-15', 'Mike Johnson'),
|
-> (3004, 'Australia', '2027-02-10', 'Lisa Turner'),
|
-> (3005, 'France', '2023-12-05', 'Sarah Connor');
|
Query OK, 5 rows affected (0.007 sec)
|
Records: 5 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [hw_6_7]> select * from passport;
|
+-----------------+-----------+----------------+--------------+
|
| passport_number | country | expirationDate | holderName |
|
+-----------------+-----------+----------------+--------------+
|
| 3001 | USA | 2025-11-30 | John Doe |
|
| 3002 | Canada | 2026-08-20 | Alice Brown |
|
| 3003 | UK | 2024-09-15 | Mike Johnson |
|
| 3004 | Australia | 2027-02-10 | Lisa Turner |
|
| 3005 | France | 2023-12-05 | Sarah Connor |
|
+-----------------+-----------+----------------+--------------+
|
5 rows in set (0.001 sec)
|
|
|
MariaDB [hw_6_7]> UPDATE Passport SET expirationDate = '2018-12-01' WHERE passport_number = 3001;
|
Query OK, 1 row affected (0.009 sec)
|
Rows matched: 1 Changed: 1 Warnings: 0
|
|
|
MariaDB [hw_6_7]> select * from passport;
|
+-----------------+-----------+----------------+--------------+
|
| passport_number | country | expirationDate | holderName |
|
+-----------------+-----------+----------------+--------------+
|
| 3001 | USA | 2018-12-01 | John Doe |
|
| 3002 | Canada | 2026-08-20 | Alice Brown |
|
| 3003 | UK | 2024-09-15 | Mike Johnson |
|
| 3004 | Australia | 2027-02-10 | Lisa Turner |
|
| 3005 | France | 2023-12-05 | Sarah Connor |
|
+-----------------+-----------+----------------+--------------+
|
5 rows in set (0.001 sec)
|
Attachments
Issue Links
- relates to
-
-
- Closed
-