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

InnoDB does not produce warnings when doing WHERE int_column=varchar_column

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 10.2.0
    • Optimizer
    • None

    Description

      DROP TABLE IF EXISTS t1,t2;
      		 
      CREATE TABLE t1 (
      		 
        a INT, b VARCHAR(1), c INT,
      		 
        KEY(a), KEY(b)
      		 
      ) ENGINE=MyISAM;
      		 
      INSERT INTO t1 VALUES
      		 
      (1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5),
      		 
      (6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9),
      		 
      (11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4),
      		 
      (16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0);
      		 
      CREATE TABLE t2 (
      		 
        pk INT, d VARCHAR(1), e INT,
      		 
        PRIMARY KEY(pk), KEY(d,e)
      		 
      ) ENGINE=MyISAM;
      		 
      INSERT INTO t2 VALUES
      		 
      (1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5),
      		 
      (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1),
      		 
      (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5),
      		 
      (15,'g',6),(16,'x',7),(17,'f',8);
      		 
      SELECT * FROM t1,t2 WHERE a=d;
      		 
      SHOW WARNINGS;

      returns warnings as expected:

      +---------+------+---------------------------------------+
      		 
      | Level   | Code | Message                               |
      		 
      +---------+------+---------------------------------------+
      		 
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'x' |
      		 
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'd' |
      		 
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'r' |
      		 
      ....

      If I change the engine to InnoDB, the warnings are not displayed any more:

      DROP TABLE IF EXISTS t1,t2;
      		 
      CREATE TABLE t1 (
      		 
        a INT, b VARCHAR(1), c INT,
      		 
        KEY(a), KEY(b)
      		 
      ) ENGINE=InnoDB;
      		 
      INSERT INTO t1 VALUES
      		 
      (1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5),
      		 
      (6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9),
      		 
      (11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4),
      		 
      (16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0);
      		 
      CREATE TABLE t2 (
      		 
        pk INT, d VARCHAR(1), e INT,
      		 
        PRIMARY KEY(pk), KEY(d,e)
      		 
      ) ENGINE=InnoDB;
      		 
      INSERT INTO t2 VALUES
      		 
      (1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5),
      		 
      (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1),
      		 
      (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5),
      		 
      (15,'g',6),(16,'x',7),(17,'f',8);
      		 
      SELECT * FROM t1,t2 WHERE a=d;
      		 
      SHOW WARNINGS;

      The problem happens because in case of InnoDB, execution goes through do_field_string():

      #0  Field_num::get_int (this=0x7fff8e8dbe20, 
          cs=0x555556b78c80 <my_charset_latin1>, from=0x7fff8e81c68e "d\002", len=1, 
          rnd=0x7ffff7f7c768, unsigned_max=4294967295, signed_min=-2147483648, 
          signed_max=2147483647) at /home/bar/maria-git/server.10.2/sql/field.cc:1585
      		 
      #1  0x0000555555c405e1 in Field_long::store (this=0x7fff8e8dbe20, 
          from=0x7fff8e81c68e "d\002", len=1, cs=0x555556b78c80 <my_charset_latin1>)
      		 
          at /home/bar/maria-git/server.10.2/sql/field.cc:4027
      		 
      #2  0x0000555555c5de78 in do_field_string (copy=0x7fff8e8dbda0)
      		 
          at /home/bar/maria-git/server.10.2/sql/field_conv.cc:372

      Field_num::get_int() does not produce the warnings because count_cuted_field is equal to CHECK_FIELD_IGNORE.

      In case of MyISAM the optimizer chooses a different plan, execution goes through this code:

      #3  0x00005555559864a4 in Value_source::Converter_strntod_with_warn::Converter_strntod_with_warn (this=0x7ffff7f33a30, thd=0x7fff98709070, filter=..., 
          cs=0x555556b78c80 <my_charset_latin1>, str=0x7fff9866db8e "x\001", 
          length=1) at /home/bar/maria-git/server.10.2/sql/field.h:202
      		 
      #4  0x0000555555c4c137 in Field_varstring::val_real (this=0x7fff9866dc88)
      		 
          at /home/bar/maria-git/server.10.2/sql/field.cc:7437
      		 
      #5  0x0000555555c7f6ea in Item_field::val_real (this=0x7fff8d422f98)
      		 
          at /home/bar/maria-git/server.10.2/sql/item.cc:2463
      		 
      #6  0x0000555555c9c96c in Arg_comparator::compare_real (this=0x7fff8d423140)
      		 
          at /home/bar/maria-git/server.10.2/sql/item_cmpfunc.cc:864
      		 
      #7  0x0000555555caeec0 in Arg_comparator::compare (this=0x7fff8d423140)
      		 
          at /home/bar/maria-git/server.10.2/sql/item_cmpfunc.h:87

      The warnings ARE displayed, because Converter_strntod_with_warn checks for thd->no_errors rather than thd->count_cuted_fields.

      The execution path for InnoDB should be fixed to call do_field_int() instead of do_field_string().
      This will force Field_long to produce warnings on Field_long::store("d", 1, &my_charset_latin1).

      Attachments

        Issue Links

          blocks

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-4912 Data type plugin API version 1

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov added a comment -

            If I change the data type from INT to DECIMAL(10,0):

            DROP TABLE IF EXISTS t1,t2;
            		 
            CREATE TABLE t1 (
            		 
              a DECIMAL(10,0), b VARCHAR(1), c INT,
            		 
              KEY(a), KEY(b)
            		 
            ) ENGINE=InnoDB;
            		 
            INSERT INTO t1 VALUES
            		 
            (1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5),
            		 
            (6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9),
            		 
            (11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4),
            		 
            (16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0);
            		 
            CREATE TABLE t2 (
            		 
              pk INT, d VARCHAR(1), e INT,
            		 
              PRIMARY KEY(pk), KEY(d,e)
            		 
            ) ENGINE=InnoDB;
            		 
            INSERT INTO t2 VALUES
            		 
            (1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5),
            		 
            (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1),
            		 
            (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5),
            		 
            (15,'g',6),(16,'x',7),(17,'f',8);
            		 
            SELECT * FROM t1,t2 WHERE a=d;
            		 
            SHOW WARNINGS;

            It returns warnings with InnoDB:

            +---------+------+---------------------------------------------------------+
            		 
            | Level   | Code | Message                                                 |
            		 
            +---------+------+---------------------------------------------------------+
            		 
            | Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
            		 
            | Warning | 1292 | Truncated incorrect DECIMAL value: 'd'                  |
            		 
            | Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
            		 
            | Warning | 1292 | Truncated incorrect DECIMAL value: 'd'                  |
            		 
            ....

            bar Alexander Barkov added a comment - If I change the data type from INT to DECIMAL(10,0): DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 ( a DECIMAL(10,0), b VARCHAR(1), c INT, KEY(a), KEY(b) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5), (6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9), (11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4), (16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0); CREATE TABLE t2 ( pk INT, d VARCHAR(1), e INT, PRIMARY KEY(pk), KEY(d,e) ) ENGINE=InnoDB; INSERT INTO t2 VALUES (1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5), (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1), (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5), (15,'g',6),(16,'x',7),(17,'f',8); SELECT * FROM t1,t2 WHERE a=d; SHOW WARNINGS; It returns warnings with InnoDB: +---------+------+---------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------+ | Warning | 1918 | Encountered illegal value '' when converting to DECIMAL | | Warning | 1292 | Truncated incorrect DECIMAL value: 'd' | | Warning | 1918 | Encountered illegal value '' when converting to DECIMAL | | Warning | 1292 | Truncated incorrect DECIMAL value: 'd' | ....
            bar Alexander Barkov added a comment -

            If I change the data type to DOUBLE, it does not return warnings again:

            DROP TABLE IF EXISTS t1,t2;
            		 
            CREATE TABLE t1 (
            		 
              a DOUBLE, b VARCHAR(1), c INT,
            		 
              KEY(a), KEY(b)
            		 
            ) ENGINE=InnoDB;
            		 
            INSERT INTO t1 VALUES
            		 
            (1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5),
            		 
            (6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9),
            		 
            (11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4),
            		 
            (16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0);
            		 
            CREATE TABLE t2 (
            		 
              pk INT, d VARCHAR(1), e INT,
            		 
              PRIMARY KEY(pk), KEY(d,e)
            		 
            ) ENGINE=InnoDB;
            		 
            INSERT INTO t2 VALUES
            		 
            (1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5),
            		 
            (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1),
            		 
            (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5),
            		 
            (15,'g',6),(16,'x',7),(17,'f',8);
            		 
            SELECT * FROM t1,t2 WHERE a=d;
            		 
            SHOW WARNINGS;

            bar Alexander Barkov added a comment - If I change the data type to DOUBLE, it does not return warnings again: DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 ( a DOUBLE, b VARCHAR(1), c INT, KEY(a), KEY(b) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5), (6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9), (11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4), (16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0); CREATE TABLE t2 ( pk INT, d VARCHAR(1), e INT, PRIMARY KEY(pk), KEY(d,e) ) ENGINE=InnoDB; INSERT INTO t2 VALUES (1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5), (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1), (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5), (15,'g',6),(16,'x',7),(17,'f',8); SELECT * FROM t1,t2 WHERE a=d; SHOW WARNINGS;

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.