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

Check Constraints with binary logging makes insert inconsistent

    XMLWordPrintable

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

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.