Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 10.11.19, 11.4.13, 11.8.9, 12.3.3, 13.0.2, 13.1.1
-
None
-
Official Docker images mariadb:10.11.19, 11.4.13, 11.8.9, 12.3.3, 13.0.2, 13.1.1, x86_64, default configuration
Description
In strict mode, an INSERT that omits a NOT NULL column without a default fails with error 1364. If the statement has an ON DUPLICATE KEY UPDATE clause that assigns that column, the VALUES form succeeds and inserts 0, although the table is empty and no duplicate key occurs. The INSERT ... SELECT form of the same statement fails with 1364.
SET sql_mode = 'STRICT_TRANS_TABLES'; |
CREATE TABLE t (k INT PRIMARY KEY, a INT NOT NULL, b INT); |
INSERT INTO t (k, b) VALUES (1, 5) ON DUPLICATE KEY UPDATE a = 3; |
INSERT INTO t (k, b) SELECT 2, 5 ON DUPLICATE KEY UPDATE a = 3; |
INSERT INTO t (k, b) VALUES (3, 5) ON DUPLICATE KEY UPDATE b = a; |
INSERT INTO t (k, b) VALUES (4, 5); |
SELECT * FROM t; |
INSERT INTO t (k, b) VALUES (1, 5) ON DUPLICATE KEY UPDATE a = 3;
|
Query OK, 1 row affected
|
INSERT INTO t (k, b) SELECT 2, 5 ON DUPLICATE KEY UPDATE a = 3;
|
ERROR 1364 (HY000): Field 'a' doesn't have a default value
|
INSERT INTO t (k, b) VALUES (3, 5) ON DUPLICATE KEY UPDATE b = a;
|
ERROR 1364 (HY000): Field 'a' doesn't have a default value
|
INSERT INTO t (k, b) VALUES (4, 5);
|
ERROR 1364 (HY000): Field 'a' doesn't have a default value
|
SELECT * FROM t;
|
k a b
|
1 0 5
|
Expected: the first INSERT fails with ERROR 1364, like the other three.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Confirmed
-