Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15461

Check Constraints with binary logging makes insert inconsistent

    Details

      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

            People

            • Assignee:
              monty Michael Widenius
              Reporter:
              oli Oli Sennhauser
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: