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

Auto-increment on secondary column increments unexpectedly with DESC key

    XMLWordPrintable

    Details

      Description

      In all 4 cases below values 'a' and 'ä' (C3A4) are inserted into a char column with latin1_swedish_ci collation. The symbols are not equal according to this collation.
      The column is a first part of a 2-part key, while the second part is an auto-incremented integer. Since the symbols in the first column differ, the value of the second one is not expected to be incremented.

      In one case, when 'ä' is inserted first and 'a' next and the table has a descending index, the value is however incremented. In other three cases it is not.

      set names latin1 collate latin1_swedish_ci;
       
      select 'ä' = 'a', 'ä' > 'a';
       
      # Table with DESC
       
      create table t1 (
        c char(16),
        i int auto_increment,
        index (c,i desc)
      ) engine=MyISAM collate latin1_swedish_ci;
      insert into t1 (c) values ('a'),('ä');
      select * from t1 order by c, i;
       
      # Same table with DESC, reverse order of INSERT
       
      create table t2 (
        c char(16),
        i int auto_increment,
        index (c,i desc)
      ) engine=MyISAM collate latin1_swedish_ci;
      insert into t2 (c) values ('ä'),('a');
      select * from t2 order by c, i;
       
      # Table with ASC
       
      create table t3 (
        c char(16),
        i int auto_increment,
        index (c,i)
      ) engine=MyISAM collate latin1_swedish_ci;
      insert into t3 (c) values ('a'),('ä');
      select * from t3 order by c, i;
       
      # Same table with ASC, reverse order of INSERT
       
      create table t4 (
        c char(16),
        i int auto_increment,
        index (c,i)
      ) engine=MyISAM collate latin1_swedish_ci;
      insert into t4 (c) values ('ä'),('a');
      select * from t4 order by c, i;
       
      drop table t1, t2, t3, t4;
      

      preview-10.8-MDEV-13756-desc-indexes c10e10c6

      insert into t2 (c) values ('ä'),('a');
      select * from t2 order by c, i;
      c	i
      a	2
      ä	1
      

      Not reproducible with Aria.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.