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

InnoDB does not produce warnings when doing WHERE int_column=varchar_column

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5, 10.0, 10.1, 10.2
    • 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

          Activity

            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.