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

Wrong result upon query with condition on indexed ENUM column

    XMLWordPrintable

    Details

      Description

      CREATE TABLE t1 (f enum('foo','bar',''), KEY (f));
      INSERT IGNORE INTO t1 VALUES ('foo'),('qux');
       
      CREATE TABLE t2 (f enum('foo','bar',''));
      INSERT IGNORE INTO t2 VALUES ('foo'),('qux');
       
      SELECT MIN(f) FROM t1 WHERE f = 'x';
      SELECT MIN(f) FROM t2 WHERE f = 'x';
       
      # Cleanup
      DROP TABLE t1, t2;
      

      The difference between the tables is that t1 has a key while t2 doesn't; otherwise the queries and data are identical.
      Note that the inserted data is out of the ENUM range, hence INSERT IGNORE.
      The values end up looking like empty strings.

      For the table with a key, the query returns an empty string.
      For the table without a key, the query returns NULL.
      One of them must be wrong. The first one seems to be more likely to be wrong.

      10.2 96de6bfd

      EXPLAIN EXTENDED SELECT MIN(f) FROM t1 WHERE f = 'x';
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
      Warnings:
      Note	1003	select min(`test`.`t1`.`f`) AS `MIN(f)` from `test`.`t1` where multiple equal('x', `test`.`t1`.`f`)
      SELECT MIN(f) FROM t1 WHERE f = 'x';
      MIN(f)
      
      

      XPLAIN EXTENDED SELECT MIN(f) FROM t2 WHERE f = 'x';
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      Warnings:
      Note	1003	select min(`test`.`t2`.`f`) AS `MIN(f)` from `test`.`t2` where `test`.`t2`.`f` = 'x'
      SELECT MIN(f) FROM t2 WHERE f = 'x';
      MIN(f)
      NULL
      

      Reproducible on all current 10.2+, with at least MyISAM, InnoDB, Aria.
      Also reproducible on MySQL 5.7, 8.0.

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.