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

          Ascending order - Click to sort in descending order
          View 5 older comments
          oli Oli Sennhauser added a comment -

          Attachment: [^my.cnf]

          oli Oli Sennhauser added a comment - Attachment: [^my.cnf]
          oli Oli Sennhauser added a comment -

          How does this bloody attachment work?

          oli Oli Sennhauser added a comment - How does this bloody attachment work?
          elenst Elena Stepanova added a comment -

          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).

          elenst Elena Stepanova added a comment - 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).
          monty Michael Widenius added a comment -

          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)

          monty Michael Widenius added a comment - 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)
          monty Michael Widenius added a comment -

          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.

          monty Michael Widenius added a comment - 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.

          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.