Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.13, 10.2(EOL), 10.3(EOL)
-
None
-
linux, n.a.
Description
CREATE TABLE Employees ( |
EmployeeID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY |
, FirstName VARCHAR(30) NOT NULL CHECK (CHAR_LENGTH(FirstName > 2)) |
, LastName VARCHAR(40) NOT NULL |
, Title VARCHAR(50) NOT NULL CHECK (Title != 'God') |
, DeptID SMALLINT UNSIGNED NOT NULL |
, ManagerID INT UNSIGNED NULL |
, Salary DECIMAL(38) DEFAULT 0.0 CHECK (Salary < 10000.0) |
, CONSTRAINT c_deptid CHECK (DeptID > 0) |
);
|
root@ma-102-master [test] SQL> INSERT INTO Employees VALUES |
-> (1, 'Ken', 'Sánchez', 'Chief Executive Officer',16,NULL, 9999) |
-> ,(273, 'Brian', 'Welcker', 'Vice President of Sales',3,1, 8000) |
-> ;
|
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'Ken' |
root@ma-102-master [test] SQL> INSERT INTO Employees VALUES (1, 'Ken', 'Sánchez', 'Chief Executive Officer',16,NULL, 9999) ; |
Query OK, 1 row affected, 1 warning (0.00 sec)
|
|
|
root@ma-102-master [test] SQL> show warnings;
|
+-------+------+-----------------------------------------+ |
| Level | Code | Message | |
+-------+------+-----------------------------------------+ |
| Error | 1292 | Truncated incorrect DOUBLE value: 'Ken' | |
+-------+------+-----------------------------------------+ |
1 row in set (0.00 sec) |
This works without check constraints and I do not see why the insert should fail. And IF, at least the error message is nonsense...
Docu states that most deterministic functions work.
Attachments
Activity
Thanks! Binary logging makes the difference. Maybe there is some historical reason for it, but I can't find it documented so far, so I'll assume for now it's a bug.
--source include/have_log_bin.inc
|
|
|
create table t1 (i int, check(i>'foo')); |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD,ER_TRUNCATED_WRONG_VALUE
|
insert into t1 values (1),(2); |
insert into t1 values (1); |
|
|
drop table t1; |
|
10.2.13 |
create table t1 (i int, check(i>'foo'));
|
insert into t1 values (1),(2);
|
Got one of the listed errors
|
insert into t1 values (1);
|
Warnings:
|
Error 1292 Truncated incorrect DOUBLE value: 'foo'
|
drop table t1;
|
Without binary logging, both INSERTs fail (in strict mode).
The error text is correct as it comes from wrong usage of CHAR_LENGTH()
MariaDB [test]> select CHAR_LENGTH("ken" > 2);
------------------------
| CHAR_LENGTH("ken" > 2) |
------------------------
| 1 |
------------------------
1 row in set, 1 warning (0.000 sec)
MariaDB [test]> show warnings;
----------------------------------------------------
| Level | Code | Message |
----------------------------------------------------
| Warning | 1292 | Truncated incorrect DOUBLE value: 'ken' |
----------------------------------------------------
1 row in set (0.000 sec)
Problem was that verify_constraints() didn't check if
there was an error as part of evaluating constraints (can
happen in strict mode).
In one-row-insert the error was ignored when using binary logging as
binary logging clear errors if insert succeeded. In multi-row-insert
the error was noticed for the second row.
After this fix one will get an error for both one and
multi-row inserts if the constraints generates a warning
in strict mode.
Attachment: [^my.cnf]