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

SELECT DISTINCT...ORDER BY for partitioned tables is not working properly

    XMLWordPrintable

Details

    Description

      When doing SELECT DISTINCT ... ORDER BY on a partitioned table, the ordering is wrong:

      MariaDB [master_biz]> create table bugtest (id int not null, status int not null, descr varchar(255), primary key (id, status)) engine=innodb partition by list(status) (partition s1 values in (0,1,2), partition s2 values in (3));

      MariaDB [master_biz]> insert into bugtest values (1,0,'1/0'), (3,0,'3/0'), (5,0,'5/0'), (2,3,'2/3'), (4,3,'4/3'), (6,3,'6/3');

      Without DISTINCT:
      MariaDB [master_biz]> select id, descr from bugtest order by id limit 0,4;

      id descr
      1 1/0
      2 2/3
      3 3/0
      4 4/3

      With DISTINCT:
      MariaDB [master_biz]> select distinct id, descr from bugtest order by id limit 0,4;

      id descr
      1 1/0
      3 3/0
      5 5/0
      2 2/3

      The order of id's works only within a partition; When we do DESC sorting then things appear to be fine:

      mysql [localhost]

      {msandbox}

      (test) > select distinct id, descr from bugtest order by id desc limit 0,6;

      id descr
      6 6/3
      5 5/0
      4 4/3
      3 3/0
      2 2/3
      1 1/0

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              marcos.albe Marcos Albe
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.