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

Inconsistent behavior on bad query between MariaDB 10.0 and 10.1

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • 10.0(EOL)
    • N/A
    • Data types
    • None

    Description

      The following bad UPDATE leads to a "warning" and truncated data on 10.0, but it fails on 10.1.

      UPDATE `t1` SET `txt`='' AND `status`='b' WHERE `id`=1;
      

      I say it is "bad" because in theory, there should be a "," in place of "AND". However, perhaps it is possible that this is valid SQL, due to "AND".

      I'm not sure what change led to this, if it is expected, if 10.0 is correct in allowing this as SQL, or is 10.1 correct in blocking this?

      Note that in 10.0, the query throws a "warning", but partially changes the row.

      In 10.1, the query fails, and thus the partial update does not occur.

      Also, in 10.1, the SHOW WARNINGS after the failed query results in 2 results (warning for "Truncated incorrect INTEGER" and error for "Truncated incorrect DOUBLE") as opposed to only the 1 row in 10.0 (warning for "Truncated incorrect INTEGER").

      Please advise which is actually correct. And assuming 10.1 is correct behavior, please advise which change caused this. And could this be considered an incompatibility between 10.0 and 10.1 that needs documented?

      Test Case:

      CREATE TABLE `t1` (
      `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `status` ENUM('a','b') NOT NULL DEFAULT 'a',
      `txt` TEXT) engine=InnODB;
      INSERT INTO `t1` (`txt`) VALUES ('hello world');
      SELECT * FROM `t1`;
      UPDATE `t1` SET `txt`='' AND `status`='b' WHERE `id`=1;
      SHOW WARNINGS;
      SELECT * FROM `t1`;
      

      Output for 10.0:

      mysql> SELECT @@SQL_MODE, @@VERSION;
      +--------------------------------------------+-----------------+
      | @@SQL_MODE                                 | @@VERSION       |
      +--------------------------------------------+-----------------+
      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | 10.0.29-MariaDB |
      +--------------------------------------------+-----------------+
      1 row in set (0.00 sec)
      

      mysql> use test;
      Database changed
      mysql> CREATE TABLE `t1` (
          -> `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
          -> `status` ENUM('a','b') NOT NULL DEFAULT 'a',
          -> `txt` TEXT) engine=InnODB;
      Query OK, 0 rows affected (0.13 sec)
      

      mysql> INSERT INTO `t1` (`txt`) VALUES ('hello world');
      Query OK, 1 row affected (0.02 sec)
      

      mysql> SELECT * FROM `t1`;
      +----+--------+-------------+
      | id | status | txt         |
      +----+--------+-------------+
      |  1 | a      | hello world |
      +----+--------+-------------+
      1 row in set (0.00 sec)
      

      mysql> UPDATE `t1` SET `txt`='' AND `status`='b' WHERE `id`=1;
      Query OK, 1 row affected, 1 warning (0.00 sec)
      Rows matched: 1  Changed: 1  Warnings: 1
      

      mysql> SHOW WARNINGS;
      +---------+------+---------------------------------------+
      | Level   | Code | Message                               |
      +---------+------+---------------------------------------+
      | Warning | 1292 | Truncated incorrect INTEGER value: '' |
      +---------+------+---------------------------------------+
      1 row in set (0.00 sec)
      

      mysql> SELECT * FROM `t1`;
      +----+--------+------+
      | id | status | txt  |
      +----+--------+------+
      |  1 | a      | 0    |
      +----+--------+------+
      1 row in set (0.00 sec)
      

      Output for 10.1:

      mysql> SELECT @@SQL_MODE, @@VERSION;
      +--------------------------------------------+-----------------+
      | @@SQL_MODE                                 | @@VERSION       |
      +--------------------------------------------+-----------------+
      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | 10.1.20-MariaDB |
      +--------------------------------------------+-----------------+
      1 row in set (0.00 sec)
      

      mysql> CREATE TABLE `t1` (
          -> `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
          -> `status` ENUM('a','b') NOT NULL DEFAULT 'a',
          -> `txt` TEXT) engine=InnODB;
      Query OK, 0 rows affected (0.05 sec)
      

      mysql> INSERT INTO `t1` (`txt`) VALUES ('hello world');
      Query OK, 1 row affected (0.02 sec)
      

      mysql> SELECT * FROM `t1`;
      +----+--------+-------------+
      | id | status | txt         |
      +----+--------+-------------+
      |  1 | a      | hello world |
      +----+--------+-------------+
      1 row in set (0.00 sec)
      

      mysql> UPDATE `t1` SET `txt`='' AND `status`='b' WHERE `id`=1;
      ERROR 1292 (22007): Truncated incorrect DOUBLE value: ''
      

      mysql> SHOW WARNINGS;
      +---------+------+---------------------------------------+
      | Level   | Code | Message                               |
      +---------+------+---------------------------------------+
      | Warning | 1292 | Truncated incorrect INTEGER value: '' |
      | Error   | 1292 | Truncated incorrect DOUBLE value: ''  |
      +---------+------+---------------------------------------+
      2 rows in set (0.00 sec)
      

      mysql> SELECT * FROM `t1`;
      +----+--------+-------------+
      | id | status | txt         |
      +----+--------+-------------+
      |  1 | a      | hello world |
      +----+--------+-------------+
      1 row in set (0.00 sec)
      

      Attachments

        Activity

          Perhaps the fixes for the following should be back-ported to 10.0, so replication from 10.0 to 10.1 does no break on such a statement:

          MDEV-8466 CAST works differently for DECIMAL/INT vs DOUBLE for empty strings
          MDEV-8468 CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces

          ccalender Chris Calender (Inactive) added a comment - Perhaps the fixes for the following should be back-ported to 10.0, so replication from 10.0 to 10.1 does no break on such a statement: MDEV-8466 CAST works differently for DECIMAL/INT vs DOUBLE for empty strings MDEV-8468 CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces
          bar Alexander Barkov added a comment - - edited

          We discussed it with Serg and think it's not desirable to backport this to 10.0. It's too risky.

          bar Alexander Barkov added a comment - - edited We discussed it with Serg and think it's not desirable to backport this to 10.0. It's too risky.
          avasru Alexander Vasilkov added a comment - - edited

          I've some problem with exucute script

          use rea;
          SET @no:=0;
          SET @pre:=0;
           
          DROP VIEW IF EXISTS hirPat_98_2xxx;
          CREATE OR REPLACE view hirPat_98_2xxx AS
            SELECT COUNT(*) cnt, `year`, nHist
                  FROM hirpat h
                  WHERE nHist<>0 AND h.dateEn>='2017-07-01' AND h.dateEn<'2017-08-01' AND h.depCode=98
                  GROUP BY `year`, depCode, nHist, surname, `name`, patronymic
                  HAVING cnt>1;
                  
          DROP TABLE IF EXISTS hirPat_98_2yyy;
          CREATE TABLE hirPat_98_2yyy
          SELECT IF(@pre=h.nHist, @no:=@no+1, @no:=1) `no`, IF(@pre=h.nHist, h.nHist, @pre:=h.nHist) nHist, h.hirPatId, h.dateEn, h.dateEx, h.year
                  FROM hirpat h
                  INNER JOIN hirPat_98_2xxx x ON h.year=x.year AND h.nHist=x.nHist
                  WHERE h.depCode=98;
          

          Table hirPat_98_2yyy not created with message

          Error( 1292 ) 22007: "Truncated incorrect DOUBLE value: ''"
          

          But request below executed successful!

          SELECT IF(@pre=h.nHist, @no:=@no+1, @no:=1) `no`, IF(@pre=h.nHist, h.nHist, @pre:=h.nHist) nHist, h.hirPatId, h.dateEn, h.dateEx, h.year
                  FROM hirpat h
                  INNER JOIN hirPat_98_2xxx x ON h.year=x.year AND h.nHist=x.nHist
                  WHERE h.depCode=98;
          

          avasru Alexander Vasilkov added a comment - - edited I've some problem with exucute script use rea; SET @ no :=0; SET @pre:=0;   DROP VIEW IF EXISTS hirPat_98_2xxx; CREATE OR REPLACE view hirPat_98_2xxx AS SELECT COUNT (*) cnt, ` year `, nHist FROM hirpat h WHERE nHist<>0 AND h.dateEn>= '2017-07-01' AND h.dateEn< '2017-08-01' AND h.depCode=98 GROUP BY ` year `, depCode, nHist, surname, ` name `, patronymic HAVING cnt>1; DROP TABLE IF EXISTS hirPat_98_2yyy; CREATE TABLE hirPat_98_2yyy SELECT IF (@pre=h.nHist, @ no :=@ no +1, @ no :=1) ` no `, IF (@pre=h.nHist, h.nHist, @pre:=h.nHist) nHist, h.hirPatId, h.dateEn, h.dateEx, h. year FROM hirpat h INNER JOIN hirPat_98_2xxx x ON h. year =x. year AND h.nHist=x.nHist WHERE h.depCode=98; Table hirPat_98_2yyy not created with message Error( 1292 ) 22007: "Truncated incorrect DOUBLE value: ''" But request below executed successful! SELECT IF (@pre=h.nHist, @ no :=@ no +1, @ no :=1) ` no `, IF (@pre=h.nHist, h.nHist, @pre:=h.nHist) nHist, h.hirPatId, h.dateEn, h.dateEx, h. year FROM hirpat h INNER JOIN hirPat_98_2xxx x ON h. year =x. year AND h.nHist=x.nHist WHERE h.depCode=98;

          This whole script exec successful on MySQL.
          I've try exec script on windows MariaDB server 10.2.7 Community

          avasru Alexander Vasilkov added a comment - This whole script exec successful on MySQL. I've try exec script on windows MariaDB server 10.2.7 Community

          Closing since 10.0 has been EOL for a long time.

          elenst Elena Stepanova added a comment - Closing since 10.0 has been EOL for a long time.

          People

            bar Alexander Barkov
            ccalender Chris Calender (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            7 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.