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
| Field | Original Value | New Value |
|---|---|---|
| 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. |
{code:sql}
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) ); {code} {code:sql} 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) {code} 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. |
| Labels | need_feedback |
| Labels | need_feedback |
| Labels | need_feedback |
| Labels | need_feedback |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.3 [ 22126 ] | |
| Assignee | Michael Widenius [ monty ] |
| Summary | Check Constraints makes insert invalid | Check Constraints with binary logging makes insert inconsistent |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| issue.field.resolutiondate | 2018-05-15 14:14:09.0 | 2018-05-15 14:14:09.83 |
| Component/s | Data Manipulation - Insert [ 10101 ] | |
| Component/s | Data Definition - Alter Table [ 10114 ] | |
| Fix Version/s | 10.2.15 [ 23006 ] | |
| Fix Version/s | 10.3.7 [ 23005 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 85817 ] | MariaDB v4 [ 153893 ] |