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

MyISAM and InnoDB work differently when comparing a TIME column to an empty string

    XMLWordPrintable

Details

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

    Description

      This bug is related to MDEV-9604.

      All problems described here were fixed in 10.1 under terms of MDEV-9604. We now need to back-port these fixes to 10.0.

      Note, the fix for 10.1 may not work for 10.0 as is, because equal field propagation in 10.1 looks very differently. So some adjusting will most likely be needed.

      SET sql_mode='';
      		 
      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a ENUM('a'), b TIME, c INT, KEY(b)) ENGINE=InnoDB;
      		 
      INSERT INTO t1 VALUES ('','00:00:00',0);
      		 
      SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b;
      		 
      ALTER TABLE t1 ENGINE=MyISAM;
      		 
      SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b;

      • returns 0 rows for InnoDB (this is wrong)
      • returns 1 row for MyISAM

      SET sql_mode='';
      		 
      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a ENUM('a'), b TIME, c INT, KEY(b)) ENGINE=InnoDB;
      		 
      INSERT INTO t1 VALUES ('','00:00:00',0);
      		 
      SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b;
      		 
      ALTER TABLE t1 ENGINE=MyISAM;
      		 
      SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b;

      • returns 0 rows for InnoDB (this is wrong)
      • returns 1 row for MyISAM

      SET sql_mode='';
      		 
      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a ENUM('a'), b TIME, c INT, KEY(b)) ENGINE=INNODB;
      		 
      INSERT INTO t1 VALUES ('','00:00:00',0);
      		 
      SELECT * FROM t1 WHERE b='';
      		 
      ALTER TABLE t1 ENGINE=MyISAM;
      		 
      SELECT * FROM t1 WHERE b='';

      • returns 0 rows for InnoDB (this is wrong)
      • returns 1 row for MyISAM

      SET sql_mode='';
      		 
      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a ENUM('a'), b TIME, c INT, KEY(b)) ENGINE=InnoDB;
      		 
      INSERT INTO t1 VALUES ('','00:00:00',0);
      		 
      SELECT * FROM t1 WHERE a=b;
      		 
      ALTER TABLE t1 ENGINE=MyISAM;
      		 
      SELECT * FROM t1 WHERE a=b;

      • returns 1 row for InnoDB (correctly)
      • returns 1 row for MyISAM (correctly)

      Similar sort of inconsistency is observed with the DATE and DATETIME data types.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-9604 crash in Item::save_in_field with empty enum value

          • Major - Major loss of function.
          • Closed

          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.