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

ORDER BY DESC and LIMIT produces wrong results

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.0.9, 10.0.15, 10.1.2
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Labels:
    • Environment:
      ubuntu 12.04 x64, ubuntu 10.04 x64

      Description

      When SELECT using ORDER BY DESC and LIMIT if the sorting column has the same value would produce unexpected results on MariaDB 10.0 and 10.1 series;

      Reproducible on MariaDB version: 10.0.15, 10.1.2.
      Was NOT able to reproduce on MariaDB
      versions: 5.5.33a.

      I just tested on the above versions.

      How to reproduce:
      1. on MariaDB version: 10.1.2, 10.0.15, 10.0.9(unexpected results)

      -- create table 
      CREATE TABLE `zzz` (
        `a` int(11) DEFAULT NULL,
        `b` int(11) DEFAULT NULL
      ) ENGINE=InnoDB
       
      -- insert some data
      insert into zzz(a,b) values
      (101679,1395219593),
      (103040,1395219593),
      (12375542,1395219593),
      (29263588,1395219593),
      (30408843,1395219593),
      (50000010,1395219593),
      (55555544,1395219593),
      (10000,1395219593);
      (75188381,1395219593),
      (85651228,1395219593);
       
      -- query and results set
      > select * from zzz order by b desc ;
      +----------+------------+
      | a        | b          |
      +----------+------------+
      | 85651228 | 1395219593 |
      | 75188381 | 1395219593 |
      | 55555544 | 1395219593 |
      | 50000010 | 1395219593 |
      | 30408843 | 1395219593 |
      | 29263588 | 1395219593 |
      | 12375542 | 1395219593 |
      |   103040 | 1395219593 |
      |   101679 | 1395219593 |
      |    10000 | 1395219593 |
      +----------+------------+
       
      > select * from zzz order by b desc limit 0,6;
      +----------+------------+
      | a        | b          |
      +----------+------------+
      |    10000 | 1395219593 |
      |   101679 | 1395219593 |
      |   103040 | 1395219593 |
      | 12375542 | 1395219593 |
      | 29263588 | 1395219593 |
      | 30408843 | 1395219593 |
      +----------+------------+
       
      > select * from zzz order by b desc limit 6,6;
      +----------+------------+
      | a        | b          |
      +----------+------------+
      | 12375542 | 1395219593 |
      |   103040 | 1395219593 |
      |   101679 | 1395219593 |
      |    10000 | 1395219593 |
      +----------+------------+
       
      Here the last two query produce some duplicate data.

      2. do the same on MariaDB 5.5.33a( expected results)

      -- query and results set
      >  select * from zzz order by b desc ;
      +----------+------------+
      | a        | b          |
      +----------+------------+
      | 85651228 | 1395219593 |
      | 75188381 | 1395219593 |
      | 55555544 | 1395219593 |
      | 50000010 | 1395219593 |
      | 30408843 | 1395219593 |
      | 29263588 | 1395219593 |
      | 12375542 | 1395219593 |
      |   103040 | 1395219593 |
      |   101679 | 1395219593 |
      |    10000 | 1395219593 |
      +----------+------------+
       
      > select * from zzz order by b desc limit 0,6;
      +----------+------------+
      | a        | b          |
      +----------+------------+
      | 85651228 | 1395219593 |
      | 75188381 | 1395219593 |
      | 55555544 | 1395219593 |
      | 50000010 | 1395219593 |
      | 30408843 | 1395219593 |
      | 29263588 | 1395219593 |
      +----------+------------+
       
      > select * from zzz order by b desc limit 6,6;
      +----------+------------+
      | a        | b          |
      +----------+------------+
      | 12375542 | 1395219593 |
      |   103040 | 1395219593 |
      |   101679 | 1395219593 |
      |    10000 | 1395219593 |
      +----------+------------+
      result is as expected.

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              zvictorino Zhixin Zhang
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: