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

ER_TRUNCATED_WRONG_VALUE produced inconsistently upon UPDATE/DELETE

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.2, 10.3, 10.4, 10.5, 10.6
    • 10.4, 10.5, 10.6
    • Server
    • None

    Description

      SET SQL_MODE=CONCAT(@@sql_mode,',STRICT_ALL_TABLES');
       
      CREATE OR REPLACE TABLE t (a INT, b INT, c INT, KEY(a));
       
      INSERT INTO t VALUES (1,1,1),(2,2,2);
       
      UPDATE t SET c = 10 WHERE a = 'foo';
      UPDATE t SET c = 20 WHERE b = 'foo';
       
      DELETE FROM t WHERE a = 'foo';
      DELETE FROM t WHERE b = 'foo';
       
      INSERT INTO t VALUES (0,0,0);
       
      UPDATE t SET c = 10 WHERE a = 'foo';
      UPDATE t SET c = 20 WHERE b = 'foo';
       
      DELETE FROM t WHERE a = 'foo';
       
      INSERT INTO t VALUES (0,0,0);
       
      DELETE FROM t WHERE b = 'foo';
       
       
      DROP TABLE t;
      

      10.2 ceb40ef4

      MariaDB [test]> INSERT INTO t VALUES (1,1,1),(2,2,2);
      Query OK, 2 rows affected (0.024 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> 
      MariaDB [test]> UPDATE t SET c = 10 WHERE a = 'foo';
      Query OK, 0 rows affected (0.020 sec)
      Rows matched: 0  Changed: 0  Warnings: 0
       
      MariaDB [test]> UPDATE t SET c = 20 WHERE b = 'foo';
      ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'foo'
      MariaDB [test]> 
      MariaDB [test]> DELETE FROM t WHERE a = 'foo';
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> DELETE FROM t WHERE b = 'foo';
      Query OK, 0 rows affected, 1 warning (0.000 sec)
       
      MariaDB [test]> 
      MariaDB [test]> INSERT INTO t VALUES (0,0,0);
      Query OK, 1 row affected (0.008 sec)
       
      MariaDB [test]> 
      MariaDB [test]> UPDATE t SET c = 10 WHERE a = 'foo';
      ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'foo'
      MariaDB [test]> UPDATE t SET c = 20 WHERE b = 'foo';
      ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'foo'
      MariaDB [test]> 
      MariaDB [test]> DELETE FROM t WHERE a = 'foo';
      Query OK, 1 row affected, 1 warning (0.007 sec)
       
      MariaDB [test]> 
      MariaDB [test]> INSERT INTO t VALUES (0,0,0);
      Query OK, 1 row affected (0.007 sec)
       
      MariaDB [test]> 
      MariaDB [test]> DELETE FROM t WHERE b = 'foo';
      Query OK, 1 row affected, 1 warning (0.007 sec)
      

      So, first updates and deletes are performed when there is no matching row.

      • UPDATE/DELETE with search by indexed column doesn't produce warnings or errors;
      • UPDATE with search by non-indexed column produces an error;
      • DELETE with search by non-indexed column produces a warning;

      Then updates and deletes are performed when there is a matching row.

      • UPDATE produces an error, regardless whether the search column is indexed or not;
      • DELETE produces a warning, regardless whether the search column is indexed or not.

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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